Общая формула
(=MEDIAN(IF(criteria,range)))
Резюме
Для вычисления условной медианы на основе одного или нескольких критериев можно использовать формулу массива, в которой вместе используются функции МЕДИАНА и ЕСЛИ. В показанном примере формула в F5:
=MEDIAN(IF(group=E5,data))
где «группа» - это именованный диапазон B5: B14, а «данные» - именованный диапазон C5: C14.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Функция МЕДИАНА не имеет встроенного способа применения критериев. Учитывая диапазон, он вернет МЕДИАНУ (среднее) число в этом диапазоне.
Чтобы применить критерии, мы используем функцию ЕСЛИ внутри МЕДИАНЫ для «фильтрации» значений. В этом примере функция ЕСЛИ фильтрует по группам следующим образом:
IF(group=E5,data)
Это выражение сравнивает каждое значение в названном диапазоне "group" со значением в E5 ("A"). Поскольку критерии применяются к массиву с несколькими значениями, результатом является массив ИСТИННЫХ ЛОЖНЫХ значений, подобных этому:
(TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE)
В этом массиве каждое ИСТИНА соответствует значению в группе А. Функция ЕСЛИ оценивает эти результаты и возвращает соответствующее значение из именованного диапазона «данные». Окончательный результат IF:
(1;2;3;3;5;FALSE;FALSE;FALSE;FALSE;FALSE)
Обратите внимание, что только значения в группе A сохранились, а значения группы B теперь FALSE. Этот массив возвращается в функцию MEDIAN, которая автоматически игнорирует значения FALSE и возвращает медианное значение 3.
Примечание: когда IF используется таким образом для фильтрации значений с помощью операции с массивом, формулу необходимо вводить с помощью Ctrl + Shift + Enter.
Дополнительные критерии
Чтобы применить более одного критерия, вы можете вложить другой IF в первый IF:
(=MEDIAN(IF(criteria1,IF(criteria2,data))))
Чтобы избежать дополнительной вложенности, вы также можете использовать логическую логику в критериях.
