Обща формула
=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-ти най-високи резултати.
Обратът обаче в този случай е, че трябва да разграничим резултатите в група А и група Б. С други думи, трябва да приложим критерии. Правим това с функцията IF, която се използва за „филтриране“ на стойности, преди те да бъдат оценени с LARGE. Като общ пример, за да получите най-голямата стойност (т.е. 1-ва стойност) в диапазон2, където диапазон 1 = "A", можете да използвате формула като тази:
LARGE(IF(range="A",range2),1)
Забележка: използването на IF по този начин прави това формула на масив.
Работейки отвътре навън, първата стъпка е да се получи „1-вата“ най-голяма стойност в данните, свързани с група А с функцията 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 като филтър за резултати от група А. С връщащия масив, предоставен като име (B5: B16). XLOOKUP връща "Хана" като краен резултат.
Бележки
- За да получите името на n-та стойност с критерии (т.е. ограничете резултатите до група A или B), ще трябва да разширите формулата, за да използвате допълнителна логика.
- В Excel 365 функцията ФИЛТЪР е по-добър начин за динамично изброяване на резултатите отгоре или отдолу. Този подход автоматично ще се справи с връзките.
