Загальна формула
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Резюме
Щоб отримати ім'я n-го найбільшого значення з критеріями, ви можете використовувати INDEX і MATCH, функцію LARGE та фільтр, створений за допомогою функції IF. У наведеному прикладі формула в комірці G5, скопійована вниз, має вигляд:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
де ім'я (B5: B16), група (C5: C16) та оцінка (D5: D16) називаються діапазонами. Формула повертає ім'я, пов'язане з 1-м, 2-м і 3-м найвищими значеннями в групі А.
Примітка: Це формула масиву, яку потрібно вводити за допомогою control + shift + enter, за винятком Excel 365.
Пояснення
Функція LARGE - це простий спосіб отримати n-те найбільше значення в діапазоні:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
У цьому прикладі ми можемо використовувати функцію LARGE, щоб отримати найвищий бал, а потім використовувати оцінку як "ключ", щоб отримати пов'язане ім'я з INDEX і MATCH. Зверніть увагу, що ми підбираємо значення n з діапазону F5: F7, щоб отримати 1-й, 2-й і 3-й найвищі бали.
Однак поворот у цьому випадку полягає в тому, що нам потрібно розрізняти бали в групі A та групі B. Іншими словами, нам потрібно застосовувати критерії. Ми робимо це за допомогою функції IF, яка використовується для "фільтрування" значень перед їх обчисленням за значенням LARGE. Як загальний приклад, щоб отримати найбільше значення (тобто 1-е значення) в діапазоні2, де діапазон 1 = "A", ви можете використати формулу, подібну до цієї:
LARGE(IF(range="A",range2),1)
Примітка: використання IF таким чином робить це формулою масиву.
Працюючи зсередини, першим кроком є отримання «1-го» найбільшого значення в даних, пов’язаних з групою A з функцією LARGE:
LARGE(IF(group="A",score),F5)
У цьому випадку значення в F5 дорівнює 1, тому ми просимо отримати найвищий бал у групі А. Коли функція IF оцінюється, вона перевіряє кожне значення в названій групі діапазонів . Зазначена оцінка діапазону надається для value_if_true. Це генерує новий масив, який повертається безпосередньо до функції LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Зверніть увагу, що єдині оцінки, які переживають фільтр, належать до групи А. LARGE потім повертає найвищий результат, що залишився, 93, безпосередньо до функції MATCH як значення пошуку. Тепер ми можемо спростити формулу, щоб:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Тепер ми бачимо, що функція MATCH налаштована на використання того самого відфільтрованого масиву, який ми бачили вище. Функція IF знову фільтрує небажані значення, і частина MATCH формули вирішує:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Оскільки 93 з'являється на 3-й позиції, MATCH повертає 3 безпосередньо до функції INDEX:
=INDEX(name,3) // Hannah
Нарешті, функція INDEX повертає ім'я у 3-му рядку "Ханна".
З XLOOKUP
Функція XLOOKUP також може бути використана для вирішення цієї проблеми, використовуючи той же підхід, що описаний вище:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Як і вище, LARGE налаштовано на роботу з масивом, відфільтрованим IF, і повертає результат 93 до XLOOKUP як значення пошуку:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Масив підстановки також створюється за допомогою IF як фільтра для оцінок з групи A. З поверненим масивом, вказаним як ім'я (B5: B16). XLOOKUP повертає "Ханну" як остаточний результат.
Примітки
- Щоб отримати назву n-го значення з критеріями (тобто обмежити результати групою A або B), вам потрібно буде розширити формулу, щоб використовувати додаткову логіку.
- В Excel 365 функція ФІЛЬТР є кращим способом динамічного переліку результатів зверху або знизу. Цей підхід автоматично обробляє зв'язки.
