Функция СРЗНАЧЕСЛИ упрощает поиск среднего значения в диапазоне данных, который соответствует заданному критерию. Одним из способов использования этой функции является игнорирование нулевых значений в данных, которые отбрасывают среднее или среднее арифметическое при использовании обычной функции СРЗНАЧ. В дополнение к данным, которые добавляются на рабочий лист, нулевые значения могут быть результатом вычислений по формуле, особенно в неполных рабочих листах.
Информация в этой статье относится к версиям Excel 2019, 2016, 2013, 2010 и Excel для Mac.
Игнорировать нули при нахождении среднего значения
На изображении ниже приведен пример функции, которая использует СРЗНАЧЕСЛИ для игнорирования всех нулевых значений. Все показанные функции используют одну и ту же базовую формулу с изменением только диапазона между примерами. Разные результаты связаны с разными данными, использованными в формуле.
Критерий в формуле, позволяющий игнорировать нули:
"0"
AVERAGEIF Синтаксис функции и дополнения
Синтаксис функции относится к структуре функции и включает в себя имя функции, квадратные скобки и аргументы. Синтаксис функции СРЗНАЧЕСЛИ:
=СРЗНАЧЕСЛИ (Диапазон, Критерии, Среднее_диапазон)
Аргументы функции СРЗНАЧЕСЛИ:
- Range (обязательно): группа ячеек, в которой функция ищет совпадения для аргумента Criteria.
- Criteria (обязательно): определяет, должны ли данные в ячейке усредняться или нет.
- Average_range (необязательно): диапазон данных, который усредняется, если первый диапазон соответствует указанным критериям. Если этот аргумент опущен, вместо этого усредняются данные в аргументе Range.
Функция СРЗНАЧЕСЛИ игнорирует:
- Ячейки в аргументе Average_range, содержащие логические значения (ИСТИНА или ЛОЖЬ).
- Ячейки в диапазоне Average_range, которые пусты.
Если ни одна из ячеек в диапазоне не соответствует указанным критериям, функция СРЗНАЧЕСЛИ возвращает значение ДЕЛ/0! значение ошибки, где все ячейки в Range равны нулю. Если аргумент Диапазон полностью пуст или содержит только текстовые значения, СРЗНАЧСЛИ также возвращает ДЕЛ/0! значение ошибки.
Игнорировать нули Пример
Варианты ввода функции СРЗНАЧЕСЛИ и ее аргументов включают:
- Ввод полной функции в ячейку листа.
- Выбор функции и ее аргументов с помощью построителя формул.
Хотя можно ввести всю функцию вручную, проще использовать диалоговое окно. Диалоговое окно позаботится о вводе синтаксиса функции, такого как скобки и разделители запятых, необходимые между аргументами.
Также, если функция и ее аргументы вводятся вручную, аргумент Criteria должен быть заключен в кавычки, например, " 0". Если для ввода функции используется построитель формул, он добавляет кавычки за вас.
Открытие построителя формул
Вот шаги, используемые для ввода СРЗНАЧЕСЛИ в ячейку D3 примера изображения с помощью построителя формул.
- Выберите ячейку D3, чтобы сделать ее активной ячейкой. Это место, где отображаются результаты функции.
- Выбрать Формулы.
- Выберите Дополнительные функции > Статистические, чтобы открыть раскрывающийся список функций.
- Выберите СРЗНАЧЕСЛИ в списке, чтобы вызвать построитель формул.
- Выберите строку Range.
- Выделите ячейки с A3 по C3 на листе, чтобы ввести этот диапазон.
- В строке Criteria введите 0. Среднее_диапазон остается пустым, потому что вы находите среднее значение для тех же ячеек, которые введены для аргумента Диапазон.
-
Выберите Готово для завершения функции. Ответ 5 появится в ячейке D3.
Image
=СРЗНАЧЕСЛИ(A3:C3, "0")
Поскольку функция игнорирует нулевое значение в ячейке B3, среднее значение оставшихся двух ячеек равно 5 ((4+6)/2=10). Если вы выберете ячейку D8 примера, полная функция появится в строке формул над рабочим листом.
