Общая формула
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Резюме
Чтобы получить имя n-го наибольшего значения с критериями, вы можете использовать ИНДЕКС и ПОИСКПОЗ, функцию НАИБОЛЬШИЙ и фильтр, созданный с помощью функции ЕСЛИ. В показанном примере формула в ячейке G5, скопированная вниз, имеет следующий вид:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
где имя (B5: B16), группа (C5: C16) и оценка (D5: D16) являются именованными диапазонами. Формула возвращает имя, связанное с 1-м, 2-м и 3-м высшими значениями в группе A.
Примечание. Это формула массива, которую необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Объяснение
Функция НАИБОЛЬШИЙ - это простой способ получить n-е наибольшее значение в диапазоне:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
В этом примере мы можем использовать функцию НАИБОЛЬШИЙ, чтобы получить наивысший балл, а затем использовать его как «ключ» для получения имени, связанного с ИНДЕКСОМ и ПОИСКПОЗ. Обратите внимание, что мы выбираем значения n из диапазона F5: F7, чтобы получить 1-й, 2-й и 3-й наивысшие оценки.
Однако в этом случае особенность заключается в том, что нам нужно различать оценки в группе A и группе B. Другими словами, нам нужно применять критерии. Мы делаем это с помощью функции ЕСЛИ, которая используется для «фильтрации» значений перед тем, как они будут оценены с помощью НАИБОЛЬШЕГО. В качестве общего примера, чтобы получить наибольшее значение (то есть 1-е значение) в диапазоне 2, где диапазон 1 = "A", вы можете использовать следующую формулу:
LARGE(IF(range="A",range2),1)
Примечание: при таком использовании IF получается формула массива.
Работая изнутри наружу, первый шаг - получить «1-е» наибольшее значение в данных, связанных с Группой A, с помощью функции НАИБОЛЬШИЙ:
LARGE(IF(group="A",score),F5)
В этом случае значение в F5 равно 1, поэтому мы запрашиваем наивысший балл в группе A. Когда функция ЕСЛИ оценивается, она проверяет каждое значение в названной группе диапазонов . Именованный диапазон баллы предоставляются значение_если_истина. Это генерирует новый массив, который возвращается непосредственно в функцию LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Обратите внимание, что единственные оценки, оставшиеся после фильтра, относятся к группе A. LARGE затем возвращает наивысший оставшийся результат, 93, непосредственно в функцию MATCH в качестве значения поиска. Теперь мы можем упростить формулу до:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Теперь мы видим, что функция ПОИСКПОЗ настроена с использованием того же фильтрованного массива, который мы видели выше. Функция ЕСЛИ снова отфильтровывает нежелательные значения, а часть формулы ПОИСКПОЗ разрешается следующим образом:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Поскольку 93 находится на 3-й позиции, ПОИСКПОЗ возвращает 3 непосредственно функции ИНДЕКС:
=INDEX(name,3) // Hannah
Наконец, функция ИНДЕКС возвращает имя в третьей строке «Ханна».
С 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 функция ФИЛЬТР - лучший способ динамически отображать верхние или нижние результаты. Этот подход автоматически обрабатывает связи.
