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