Содержание
Функция AVERAGEIF упрощает поиск среднего значения в диапазоне данных, который соответствует заданному критерию. Одним из применений этой функции является игнорирование нулевых значений в данных, которые отбрасывают среднее или арифметическое среднее при использовании обычной функции AVERAGE. Помимо данных, добавляемых на лист, нулевые значения могут быть результатом вычислений по формулам, особенно в неполных листах.
Информация в этой статье относится к версиям Excel 2019, 2016, 2013, 2010 и Excel для Mac.
Игнорировать нули при поиске среднего
Изображение ниже содержит пример функции, которая использует AVERAGEIF для игнорирования всех нулевых значений. Все показанные функции используют одну и ту же базовую формулу с изменением только диапазона между примерами. Различные результаты обусловлены различными данными, используемыми в формуле.
Критерий в формуле, позволяющий игнорировать нули:
"0"
Синтаксис функции AVERAGEIF и дополнения
Синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы. Синтаксис для функции AVERAGEIF:
= AVERAGEIF (диапазон, критерии, средний диапазон)
Аргументы для функции AVERAGEIF:
- Ассортимент (обязательно): группа ячеек, которую ищет функция, чтобы найти совпадения для аргумента Критерии.
- критерии (обязательно): Определяет, должны ли данные в ячейке быть усредненными или нет.
- диапазон_усреднения (необязательно): диапазон данных, который усредняется, если первый диапазон соответствует заданным критериям. Если этот аргумент опущен, данные в аргументе 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 примера, полная функция появится на панели формул над рабочим листом.
