Загальна формула
(=LARGE(IF(criteria,values),n))
Резюме
Щоб повернути найбільші значення в наборі даних із критеріями, ви можете використовувати формулу на основі функцій LARGE і IF.
У наведеному прикладі формула в F5 має вигляд:
(=LARGE(IF(B5:B11="B",C5:C11),2))
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
Функцію LARGE можна використовувати для отримання "n-го" найбільшого значення в числових даних приблизно так:
=LARGE(values,n)
У цьому прикладі нам потрібно включити лише значення, пов'язані з групою B. Для цього ми використовуємо функцію IF для фільтрації:
IF(B5:B11="B",C5:C11)
Оскільки ми запускаємо логічний тест на діапазоні комірок, ми отримуємо масив результатів:
(FALSE; 98; FALSE; 60; FALSE; 95; FALSE)
Зверніть увагу, що в масив потрапляють лише значення групи B. Значення групи A стають FALSE, оскільки вони не проходять логічний тест. Цей масив повертається всередину функції LARGE з 2, закодованими як "n-та" (аргумент "k" у LARGE):
=LARGE((FALSE;98;FALSE;60;FALSE;95;FALSE),2)
Потім LARGE повертає 95, друге за величиною значення в групі B як кінцевий результат.
Кілька критеріїв
Щоб врахувати кілька критеріїв, ви можете розширити формулу за допомогою логічної логіки у такій формі:
=LARGE(IF((criteria1)*(criteria2),values),n)
Де критерії1 та критерії2 і представляють вираз для перевірки значень у діапазоні критеріїв, як показано у вихідному прикладі вище.
