Здравствуйте. В этой статье покажу, как посчитать среднее арифметическое для значений, удовлетворяющих нескольким условиям. Это легко делается с применением функции СРЗНАЧЕСЛИМН:
=СРЗНАЧЕСЛИМН( диапазон_усреднения ; массив_1 ; условие_1 ; … массив_n ; условие_n )
Например, имеется таблица с продажами товаров по группам и регионам. Следует посчитать средние продажи для каждого региона в разрезе группы продукции:
Воспользуемся приведенной выше формулой. Укажем в качестве условий отбора совпадение региона и категории:
Обратите внимание на ссылки в формуле. Знаки $ перед координатами означают, что они не должны изменяться при копировании формулы в остальные строки. Это очень важно и если вам этот прием не знаком, прочтите статью о видах ссылок в Excel.
В результатах работы есть неприятный момент. Функция возвращает ошибку #ДЕЛ/0! в тех строках, где продаж не было. Например, восточный регион не продавал смартфоны и ТВ. Естественно, вместо ошибки должен стоять ноль. Чтобы заменить ошибки на нули, используем функцию обработки ошибок ЕСЛИОШИБКА. Обернём ею ранее написанную формулу:
=ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН($D$3:$D$51;$B$3:$B$51;$F$3;$C$3:$C$51;G3);0)
Теперь вместо ошибок появятся нули. Я отключу отображение нулевых значений, чтобы ячейка выглядела пустой. Так, на мой взгляд, нагляднее. Снимем галку: Файл – Параметры – Дополнительно – Параметры отображения листа – Показывать нули в ячейках, которые содержат нулевые значения. Эта настройка указана для Office 2013.В других версиях расположение галки может немного отличаться.
Полученный результат меня полностью удовлетворяет. Можно было бы его округлить, но я просто задал отображение двух знаков после запятой. На том и остановимся. Спасибо за прочтение статьи, готов отвечать на ваши вопросы!