Функция AVERAGEIF облегчает поиск среднего значения в диапазоне данных, который соответствует заданному критерию. Одно из применений этой функции — игнорировать нулевые значения в данных, которые отбрасывают среднее или арифметическое среднее при использовании обычной функции AVERAGE . В дополнение к данным, добавляемым на лист, нулевые значения могут быть результатом вычислений по формулам, особенно в неполных листах .
Информация в этой статье относится к версиям Excel 2019, 2016, 2013, 2010 и Excel для Mac.
Игнорировать нули при поиске среднего
Изображение ниже содержит пример функции, которая использует AVERAGEIF для игнорирования всех нулевых значений. Все показанные функции используют одну и ту же базовую формулу с изменением только диапазона между примерами. Различные результаты обусловлены различными данными, используемыми в формуле.
Критерий в формуле, позволяющий игнорировать нули:
"<> 0"
Синтаксис функции AVERAGEIF и дополнения
Синтаксис функции относится к макету функции и включает в себя имя функции , скобки и аргументы . Синтаксис для функции AVERAGEIF:
= AVERAGEIF (диапазон, критерии, средний диапазон)
Аргументы для функции AVERAGEIF:
- Диапазон (обязательный): группа ячеек, которые ищет функция, чтобы найти соответствия для аргумента Критерии .
- Критерии (обязательно): Определяет, должны ли данные в ячейке быть усредненными или нет.
- Average_range (необязательно): диапазон данных, который усредняется, если первый диапазон соответствует заданным критериям. Если этот аргумент опущен, данные в аргументе Range вместо этого усредняются.
Функция AVERAGEIF игнорирует:
- Ячейки в аргументе Average_range, которые содержат логические (TRUE или FALSE) значения.
- Ячейки в Average_range, которые пусты.
Если ни одна ячейка в диапазоне не соответствует указанным критериям, AVERAGEIF возвращает # DIV / 0! значение ошибки, где все ячейки в диапазоне равны нулю. Если аргумент Range полностью пуст или содержит только текстовые значения, AVERAGEIF также возвращает # DIV / 0! значение ошибки.
Пример игнорирования нулей
Опции для входа в функцию AVERAGEIF и ее аргументы включают в себя:
- Ввод полной функции в ячейку листа.
- Выбор функции и ее аргументов с помощью Formula Builder.
Хотя можно ввести полную функцию вручную, проще использовать диалоговое окно. Диалоговое окно позаботится о вводе синтаксиса функции, такого как скобки и разделители запятых, необходимые между аргументами.
Кроме того, если функция и ее аргументы вводятся вручную, аргумент Criteria должен быть заключен в кавычки, например «<> 0» . Если построитель формул используется для входа в функцию, он добавляет кавычки для вас.
Открытие Formula Builder
Вот шаги, используемые для ввода AVERAGEIF в ячейку D3 примера изображения с помощью Formula Builder.
-
Выберите ячейку D3, чтобы сделать ее активной ячейкой . Это место, где отображаются результаты функции.
-
Выберите Формулы .
-
Выберите « Дополнительные функции» > « Статистические данные», чтобы открыть раскрывающийся список функций.
-
Выберите AVERAGEIF в списке, чтобы вызвать Formula Builder.
-
Выберите линию диапазона .
-
Выделите ячейки от A3 до C3 на листе, чтобы ввести этот диапазон.
-
В строке Критерии введите <> 0 . Average_range оставлен пустым, потому что вы находите среднее значение для тех же ячеек, введенных для аргумента Range .
-
Выберите Готово, чтобы завершить функцию. Ответ 5 появляется в ячейке D3.
= AVERAGEIF (A3: C3, "<> 0")
Поскольку функция игнорирует нулевое значение в ячейке B3, среднее значение для оставшихся двух ячеек равно 5 ((4 + 6) / 2 = 10). Если вы выберете ячейку D8 примера, полная функция появится на панели формул над рабочим листом.