Общая формула
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Резюме
Чтобы получить имя n-го по величине значения, вы можете использовать ИНДЕКС и ПОИСКПОЗ с функцией НАИБОЛЬШИЙ. В показанном примере формула в ячейке H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
где имя (B5: B16) и оценка (D5: D16) являются именованными диапазонами.
Объяснение
Вкратце, эта формула использует функцию НАИБОЛЬШИЙ для нахождения n-го наибольшего значения в наборе данных. Получив это значение, мы вставляем его в стандартную формулу ИНДЕКС и ПОИСКПОЗ, чтобы получить связанное имя. Другими словами, мы используем n-е по величине значение как «ключ» для получения связанной информации.
Функция НАИБОЛЬШИЙ - это простой способ получить n-е наибольшее значение в диапазоне. Просто укажите диапазон для первого аргумента (массива) и значение для n в качестве второго аргумента (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Первый шаг - получить «1-е» наибольшее значение в данных с помощью функции НАИБОЛЬШИЙ:
LARGE(score,F5) // returns 93
В этом случае значение в F5 равно 1, поэтому мы запрашиваем 1-й по величине балл (т. Е. Наивысший балл), который равен 93. Теперь мы можем упростить формулу до:
=INDEX(name,MATCH(93,score,0))
Внутри функции ИНДЕКС функция ПОИСКПОЗ настроена для определения положения 93 в рейтинге именованного диапазона (D5: D16):
MATCH(93,score,0) // returns 3
Поскольку 93 появляется в 3-й строке, ПОИСКПОЗ возвращает 3 непосредственно в ИНДЕКС как номер строки с именем в виде массива:
=INDEX(name,3) // Hannah
Наконец, функция ИНДЕКС возвращает имя в третьей строке «Ханна».
Обратите внимание, что мы выбираем значения для n из диапазона F5: F7, чтобы получить 1-й, 2-й и 3-й наивысшие оценки по мере копирования формулы.
Получить группу
Та же основная формула будет работать для получения любой связанной информации. Чтобы получить группу для самых больших значений, вы можете просто изменить массив, передаваемый в INDEX, с именованной группой диапазонов :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
При значении 1 в F5, НАИБОЛЬШИЙ получит наивысший балл, а формула вернет «А».
Примечание. В 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 вернет одинаковое значение для каждого из них. Когда это значение передается в функцию ПОИСКПОЗ, ПОИСКПОЗ вернет позицию первого совпадения, поэтому вы увидите то же самое (первое) возвращенное имя.
Если есть вероятность ничьей, вы можете реализовать какую-то стратегию разрешения ничей. Один из подходов - создать новый вспомогательный столбец значений, который был скорректирован для разрыва связей. Затем используйте значения вспомогательных столбцов для ранжирования и получения информации. Это делает логику разрыва связей ясной и явной.
Другой подход состоит в том, чтобы разорвать ничью только на основе позиции (т.е. первая ничья «побеждает»). Вот формула, которая использует этот подход:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Здесь мы используем MATCH, чтобы найти число 1, и мы создаем поисковый массив, используя логическую логику, которая (1) сравнивает все оценки со значением, возвращаемым LARGE:
score=LARGE(score,F5)
и (2) использует расширяющуюся проверку диапазона, если имя уже находится в ранжированном списке:
COUNTIF(H$4:H4,name)=0
Когда имя уже есть в списке, оно «отменяется» логикой, и сопоставляется следующее (повторяющееся) значение. Обратите внимание, что расширяющийся диапазон начинается с предыдущей строки, чтобы избежать циклической ссылки.
Этот подход работает в этом примере, потому что в столбце имени нет повторяющихся имен. Однако, если повторяющиеся имена встречаются в ранжированных значениях, подход необходимо скорректировать. Самое простое решение - убедиться, что имена уникальны.
Заметки
- Чтобы получить имя n-го значения с критериями (т. Е. Ограничить результаты до группы A или B), вам нужно будет расширить формулу, чтобы использовать дополнительную логику.
- В Excel 365 функция ФИЛЬТР - лучший способ динамически отображать верхние или нижние результаты. Этот подход автоматически обрабатывает связи.
