- Игнорировать пустые значения с помощью функции AVERAGE
- Считайте текстовые значения нулевыми с помощью функции AVERAGEA
- Игнорировать значения пустых категорий при использовании СРЗНАЧЕСЛИМН
- Игнорировать пустые значения категории - массив
- Игнорировать пустые значения категории и обрабатывать текст как 0
- Среднее значение, если не пусто в Google Таблицах
Загрузите образец книги
В этом руководстве будет показано, как вычислить среднее значение набора чисел, игнорируя пустые значения или категории в Excel и Google Таблицах.
Игнорировать пустые значения с помощью функции AVERAGE
Функция AVERAGE автоматически игнорирует все пустые или содержащие текст ячейки.
В этом примере функция AVERAGE используется для вычисления Средний балл игнорируя текстовое значение Счет из Команда B и пустое значение Счет из Команда C:
| 1 | = СРЕДНИЙ (C3: C7) |
Функция AVERAGE использует только числовые значения, связанные с Команды A, D а также E
Считайте текстовые значения нулевыми с помощью функции AVERAGEA
Чтобы рассматривать текстовые значения как ноль при вычислении, можно использовать функцию AVERAGEA. Значения пустых ячеек игнорируются этой функцией.
В этом примере функция AVERAGEA используется для вычисления Средний балл игнорируя пробел Счет значение Команда C и обработка текста Счет значение Команда B как 0:
| 1 | = СРЕДНЕЕ (C3: C7) |
В этом примере вычисляется среднее значение 4, 0, 5 и 3.
Игнорировать значения пустых категорий при использовании СРЗНАЧЕСЛИМН
В этом примере функция AVERAGEIFS используется для вычисления Средний балл для команд с непустыми именами и игнорирует Очки это текстовые значения
| 1 | = СРЕДНЕЛИМН (C3: C7; B3: B7; "") |
Игнорировать пустые значения категории - массив
Если у вас нет доступа к функции СРЗНАЧЕСЛИ, вы можете использовать вложенные функции СРЗНАЧ и ЕСЛИ.
В этом примере вычисляется Средний балл для команд с непустыми именами и игнорирует любые Очки это текстовое значение
| 1 | = СРЕДНИЙ (ЕСЛИ (B3: B7 ""; C3: C7)) |
В этой формуле используется функция ЕСЛИ, чтобы оценить, соответствует ли имя каждого из них. Команда не пусто, а использует только Очки из Команды с непустыми именами в функции AVERAGE. Оценка 100 игнорируется как Команда имя пусто. Оценка 'Нет данных' игнорируется, поскольку это не числовое значение.
В версиях Excel от 2022 года и ранее эту формулу необходимо вводить как формулу массива, нажимая CTRL + SHIFT + ENTER. Это не требуется в более поздних версиях
Этот пример также можно вычислить с помощью функции AVERAGEIFS, как показано ниже.
Игнорировать пустые значения категории и обрабатывать текст как 0
Для обработки текстового значения Очки как 0, все еще игнорируя Команды с пустыми именами мы можем использовать вложенные функции AVERAGEA и IF:
| 1 | = СРЕДНЕЕ (ЕСЛИ (B3: B7 ""; C3: C7)) |
В этой формуле используется функция ЕСЛИ, чтобы оценить, соответствует ли имя каждого из них. Команда не пусто, а использует только Очки из Команды с непустыми именами в функции AVERAGEA. Оценка 100 игнорируется как Команда имя пусто. Оценка 'Нет данных' обрабатывается функцией AVERAGEA как 0, поэтому рассчитывается среднее значение 3,0.
В версиях Excel от 2022 года и ранее эту формулу необходимо вводить как формулу массива, нажимая CTRL + SHIFT + ENTER. Это не требуется в более поздних версиях
Среднее значение, если не пусто в Google Таблицах
Большинство этих примеров формул работают в Google Таблицах так же, как и в Excel, но с некоторыми исключениями:
Всякий раз, когда функция ЕСЛИ вложена в другую функцию и ссылается на диапазон ячеек, Google Таблицы должны обрабатывать формулу как формулу массива с помощью функции ARRAYFORMULA:
| 1 | = ArrayFormula (СРЕДНЕЕ (ЕСЛИ (B3: B7 "", C3: C7))) |
К сожалению, функция AVERAGEA в Google Таблицах работает иначе, чем в Excel, поэтому пример = AVERAGEA (IF (B3: B7 ””, C3: C7)) использовать нельзя. Это связано с тем, что, когда функция ЕСЛИ находит пустое значение в диапазоне B3: B7, она создает нулевое значение, но затем функция AVERAGEA в Google Таблицах засчитывает это значение как 0 вместо того, чтобы игнорировать это значение, как это было бы в случае Excel.
