Формула Excel: Назва n-го найбільшого значення -

Зміст

Загальна формула

=INDEX(names,MATCH(LARGE(values,F5),values,0))

Резюме

Щоб отримати назву n-го найбільшого значення, ви можете використовувати INDEX і MATCH з функцією LARGE. У наведеному прикладі формула в комірці H5 має вигляд:

=INDEX(name,MATCH(LARGE(score,F5),score,0))

де ім'я (B5: B16) та бал (D5: D16) називаються діапазонами.

Пояснення

У двох словах, ця формула використовує функцію LARGE для пошуку n-го найбільшого значення в наборі даних. Отримавши це значення, ми підключаємо його до стандартної формули INDEX і MATCH для отримання відповідного імені. Іншими словами, ми використовуємо n-те найбільше значення як "ключ" для отримання пов'язаної інформації.

Функція LARGE - це простий спосіб отримати n-те найбільше значення в діапазоні. Просто надайте діапазон для першого аргументу (масиву) та значення для n як другого аргументу (k):

 =LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest

Працюючи зсередини, першим кроком є ​​отримання «1-го» найбільшого значення в даних за допомогою функції LARGE:

LARGE(score,F5) // returns 93

У цьому випадку значення в F5 дорівнює 1, тому ми просимо 1-й найбільший бал (тобто найвищий бал), який становить 93. Тепер ми можемо спростити формулу, щоб:

=INDEX(name,MATCH(93,score,0))

Усередині функції INDEX, функція MATCH встановлюється для визначення місця розташування 93 в названому діапазоні балів (D5: D16):

MATCH(93,score,0) // returns 3

Оскільки 93 з'являється в 3-му рядку, MATCH повертає 3 безпосередньо до INDEX як номер рядка, а ім'я - як масив:

=INDEX(name,3) // Hannah

Нарешті, функція INDEX повертає ім'я у 3-му рядку "Ханна".

Зверніть увагу, що ми підбираємо значення n з діапазону F5: F7, щоб отримати 1-й, 2-й і 3-й найвищі бали при копіюванні формули.

Отримати групу

Ця ж основна формула буде працювати для отримання будь-якої пов'язаної інформації. Щоб отримати групу для найбільших значень, ви можете просто змінити масив, що подається на INDEX, із зазначеною групою діапазонів :

=INDEX(group,MATCH(LARGE(score,F5),score,0))

При значенні 1 у F5, LARGE отримає найвищий бал, а формула поверне "A".

Примітка: в Excel 365 ви можете використовувати функцію ФІЛЬТР для динамічного переліку результатів зверху або знизу.

З XLOOKUP

Функцію XLOOKUP також можна використовувати для повернення імені n-го найбільшого значення, наприклад:

=XLOOKUP(LARGE(score,F5),score,name)

LARGE повертає найбільше значення, 93, безпосередньо до XLOOKUP як значення пошуку:

=XLOOKUP(93,score,name) // Hannah

З названою оцінкою діапазону (D5: D16) як масивом підстановки та ім’ям (B5: B16) як масивом повернення, XLOOKUP повертає "Ханну", як і раніше.

Обробка зв'язків

Повторювані значення в числових даних створять "зв’язок". Якщо при ранжируванні значень відбувається зв’язок, наприклад, якщо перше та друге найбільші значення однакові, функція LARGE поверне однакове значення для кожного. Коли це значення передається у функцію MATCH, MATCH повертає позицію першого збігу, тому ви побачите те саме (перше) ім'я, що повертається.

Якщо існує можливість зав’язок, можливо, ви захочете реалізувати якусь стратегію розриву зв’язку. Одним із підходів є створення нового допоміжного стовпця значень, які були скориговані для розриву зв'язку. Потім використовуйте допоміжні значення стовпців для ранжування та отримання інформації. Це робить логіку, яка використовується для розриву зв’язків, чіткою та явною.

Інший підхід полягає в розриві зв'язків лише на основі позиції (тобто перша нічия "виграє"). Ось формула, яка використовує такий підхід:

INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))

Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter, за винятком Excel 365.

Тут ми використовуємо MATCH, щоб знайти число 1, і ми створюємо масив підстановки, використовуючи логічну логіку, яка (1) порівнює всі оцінки зі значенням, яке повертається LARGE:

score=LARGE(score,F5)

та (2) використовує перевірку діапазону, що розширюється, якщо ім’я вже є в списку рейтингу:

COUNTIF(H$4:H4,name)=0

Коли ім'я вже є у списку, воно "скасовується" за логікою, і наступне (повторюване) значення відповідає. Зверніть увагу, що діапазон розширення починається з попереднього рядка, щоб уникнути кругового посилання.

Цей підхід працює в цьому прикладі, оскільки в стовпці імен немає повторюваних імен. Однак, якщо повторювані імена трапляються у рангованих значеннях, підхід потрібно скорегувати. Найпростішим рішенням є переконатися, що імена унікальні.

Примітки

  1. Щоб отримати назву n-го значення з критеріями (тобто обмежити результати групою A або B), вам потрібно буде розширити формулу, щоб використовувати додаткову логіку.
  2. В Excel 365 функція ФІЛЬТР є кращим способом динамічного переліку результатів зверху або знизу. Цей підхід автоматично обробляє зв'язки.

Цікаві статті...