Игнорировать нулевые значения в данных, когда они сбрасывают среднее
Функция 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 примера, полная функция появится на панели формул над рабочим листом.
