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

AVERAGEIFS вычисляет среднее значение чисел, одновременно соответствующих нескольким условиям. Вместо усреднения всех продаж можно спросить: Какова была средняя продажа в регионе Восток за январь?

Эта функция завершает тройку: SUMIFS суммирует, COUNTIFS подсчитывает, а AVERAGEIFS вычисляет среднее значение — всё в одном стиле работы с несколькими критериями. Если Вы знаете одну из них, то почти знаете и все три.

AVERAGEIFS в точности повторяет структуру SUMIFS. Сначала указываются значения для усреднения, затем пары условий.

• average_range — числа, по которым вычисляется среднее
• criteria_range1, criteria1
• criteria_range2, criteria2

Внутри функции совпавшие значения суммируются и делятся на количество совпадений — по сути, вычисляется SUMIFS, делённая на COUNTIFS, всё в одной простой функции.

Если в столбце A указать Регион, в столбце B — Месяц, а в столбце C — Сумма, можно вычислить среднюю сумму продаж в восточном регионе за январь.

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

Можно отфильтровать усредняемые значения по их собственному размеру. Допустим, нужно вычислить среднее только для крупных продаж в восточном регионе — то есть для продаж на сумму свыше 100.

Здесь столбец C используется и как диапазон усреднения, и как диапазон условий. AVERAGEIFS без проблем использует один и тот же столбец в обеих ролях.

Чтобы создать отчёт, которым могут управлять пользователи, ссылайтесь на ячейки вместо ввода значений. Поместите регион в F1, а минимальную сумму — в F2.

Для сравнения объедините оператор с ячейкой: ">"&F2 означает больше любого значения, хранящегося в F2. Для обычного текстового совпадения, например региона, оператор не нужен — достаточно ссылки на ячейку.

Самая частая проблема с AVERAGEIFS: если ни одна строка не соответствует всем условиям, усреднять нечего, поэтому функция возвращает ошибку #DIV/0!.

Это отличается от SUMIFS, которая возвращает 0, и COUNTIFS, которая также возвращает 0. Среднее для нулевого количества элементов не определено, поэтому Excel выдаёт ошибку, а не пытается угадать результат.

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

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

Важная особенность: AVERAGEIFS игнорирует пустые ячейки в диапазоне усреднения — они не учитываются ни в общей сумме, ни в делителе. А ячейка, содержащая 0, является настоящим числом и участвует в вычислении.

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

Добавляйте столько условий, сколько нужно. Если в столбце D указан Продавец, найдите среднюю сумму продажи, которая относится к восточному региону, приходится на январь и была оформлена Марией.

Каждая добавленная пара делает фильтр точнее. Поскольку AVERAGEIFS использует логику AND, в среднее попадают только строки, соответствующие всем трём условиям.

Не путайте порядок двух наборов аргументов:

• AVERAGEIF: range, criteria, [average_range] — сначала указывается диапазон проверки, а необязательный диапазон усреднения — последним.
• AVERAGEIFS: average_range, range1, crit1, ... — диапазон усреднения всегда указывается первым.

Как и в остальных случаях, использование версии с окончанием ...IFS по умолчанию даёт единый шаблон для SUM, COUNT и AVERAGE.

AVERAGEIFS используется для многих сводок, расположенных рядом. Перечислите названия регионов в столбце F, а затем вычислите среднюю сумму продажи для каждого региона с помощью одной формулы, которую можно протянуть, закрепив столбец условия.

Если $C:$C используется как диапазон усреднения, $A:$A — как диапазон условий, а $F2 — как относительная ячейка региона, копирование формулы вниз мгновенно даст среднее значение для каждого региона.

Теперь Вы умеете вычислять среднее значение по нескольким условиям:

• AVERAGEIFS(average_range, range1, crit1, ...) — диапазон усреднения указывается первым.
• При отсутствии совпадений возвращается #DIV/0! — обработайте это с помощью IFERROR.
• Пустые ячейки пропускаются, но нули учитываются; при необходимости исключите их с помощью "<>0".
• Используйте ">"&F1 для динамических числовых ограничений.

Далее: работа с диапазонами дат внутри этих функций с условиями.