Расчёт среднего по условию с помощью AVERAGEIF

Вы уже вычисляли итоги с помощью SUMIF и выполняли подсчёт с помощью COUNTIF. Третий представитель этого семейства — AVERAGEIF, который находит среднее значение только тех значений, которые соответствуют условию.

Вместо усреднения всех продаж можно усреднить только продажи региона «Восток» или только оценки выше 50. Эта функция объединяет фильтрацию и вычисление среднего в один понятный шаг.

AVERAGEIF использует такую же структуру из трёх частей, как и SUMIF:

• диапазон — ячейки для проверки
• условие — условие отбора
• диапазон усреднения — ячейки для усреднения

Функция просматривает диапазон, находит совпадения, а затем усредняет соответствующие ячейки в диапазоне усреднения.

Если регионы находятся в A2:A10, а суммы — в B2:B10, средняя сумма продаж для региона «Восток» проверяет столбец A на наличие «Востока» и усредняет соответствующие значения в столбце B.

Внутри выполняется деление результата SUMIF на результат COUNTIF, но AVERAGEIF объединяет эти действия в одну понятную формулу.

Если столбец, который Вы проверяете, одновременно является столбцом для усреднения, третий аргумент можно не указывать. Чтобы усреднить только оценки в B2:B10, превышающие 70:

Здесь столбец B и проверяется, и усредняется. Если не указывать диапазон усреднения, электронная таблица использует диапазон и для проверки, и для усреднения.

Как и в других функциях IF, храните условие в ячейке, чтобы формулу было легко изменять. Если D1 содержит название региона, укажите D1 в качестве условия.

Теперь одна ячейка управляет средним значением. При изменении D1 с «Востока» на «Запад» результат пересчитывается мгновенно, что особенно удобно для сводной таблицы или выбора на панели мониторинга.

AVERAGEIF принимает операторы в кавычках, как и родственные ей функции. Чтобы усреднить только продажи не менее 100:

• ">=100" 100 или больше
• "<50" ниже 50
• "<>0" исключая нули

Последний вариант особенно полезен: он вычисляет среднее, игнорируя нулевые записи, которые иначе занизили бы результат.

Чтобы использовать пороговое значение из ячейки, объедините оператор с ней с помощью амперсанда. Если D1 содержит порог, усредните все значения выше него.

& формирует текст условия из ">" и значения в D1. Это тот же приём объединения, который Вы использовали с SUMIF и COUNTIF.

У AVERAGEIF есть одна особенность, которой нет у остальных функций. Если нет совпавших строк, делить не на что, и Вы получите ошибку #DIV/0!.

Например, усреднение региона, которого нет в данных, возвращает эту ошибку. Электронная таблица сообщает, что фильтр не нашёл ни одной ячейки, а не о том, что формула неверна.

Оберните формулу в IFERROR, чтобы при отсутствии совпадений показывать понятное сообщение вместо #DIV/0!.

Теперь для региона без данных отображается текст «Нет данных», а не тревожная ошибка. Благодаря этому отчёты выглядят аккуратно, даже если некоторые категории отсутствуют.

AVERAGEIF усредняет только ячейки с числами среди найденных совпадений. Действительно пустые ячейки в диапазоне усреднения игнорируются, поэтому не считаются нулевыми.

Это важно: отсутствующее значение не тянет среднее к нулю, как это сделал бы настоящий 0. Если Вы хотите исключить и нули, добавьте условие "<>0" для столбца со значениями.

Создайте аккуратную сводку: укажите каждую категорию в ячейках D2, D3 и D4, затем напишите одну AVERAGEIF со ссылкой на ячейку с названием категории и протяните её вниз.

Теперь в каждой строке показано среднее значение для собственной категории рядом с итогом SUMIF и количеством COUNTIF из предыдущих уроков. Вместе они образуют компактный отчёт, полностью построенный на формулах.

Проверьте, насколько хорошо Вы понимаете работу AVERAGEIF.

Теперь Вы умеете вычислять условное среднее с помощью AVERAGEIF. Главное:

• Порядок такой: диапазон, условие, диапазон усреднения; последний аргумент можно опустить, чтобы усреднять сам диапазон проверки.
• Функция поддерживает текст, числа и операторы, например ">=100".
• При отсутствии совпадений возникает ошибка #DIV/0!, поэтому используйте IFERROR.
• Пустые ячейки пропускаются, а не считаются нулевыми.

Далее Вы научитесь находить частичные совпадения текста с помощью подстановочных знаков.