Общая формула
(=LARGE(IF(criteria,values),n))
Резюме
Чтобы вернуть наибольшие значения в наборе данных с критериями, вы можете использовать формулу, основанную на функциях НАИБОЛЬШИЙ и ЕСЛИ.
В показанном примере формула в F5:
(=LARGE(IF(B5:B11="B",C5:C11),2))
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Функцию НАИБОЛЬШИЙ можно использовать для получения "n-го" наибольшего значения в числовых данных следующим образом:
=LARGE(values,n)
В этом примере нам нужно включить только значения, связанные с группой B. Для этого мы используем функцию ЕСЛИ для фильтрации:
IF(B5:B11="B",C5:C11)
Поскольку мы запускаем логический тест для диапазона ячеек, мы получаем массив результатов:
(ЛОЖЬ; 98; ЛОЖЬ; 60; ЛОЖЬ; 95; ЛОЖЬ)
Обратите внимание, что только значения из группы B попадают в массив. Значения группы A становятся ЛОЖНЫМИ, поскольку они не проходят логический тест. Этот массив возвращается внутри функции НАИБОЛЬШИЙ, где 2 жестко запрограммированы как "nth" (аргумент "k" в НАИБОЛЕЕ):
=LARGE((FALSE;98;FALSE;60;FALSE;95;FALSE),2)
Затем LARGE возвращает 95, второе по величине значение в группе B, в качестве окончательного результата.
Несколько критериев
Чтобы учесть несколько критериев, вы можете расширить формулу с помощью логической логики в такой форме:
=LARGE(IF((criteria1)*(criteria2),values),n)
Где критерии1 и критерии2 и представляют выражение для проверки значений в диапазоне критериев, как показано в исходном примере выше.
