Генеричка формула
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Резиме
Да бисте добили име н-те највеће вредности са критеријумима, можете да користите ИНДЕКС и МАТЦХ, функцију ЛАРГЕ и филтер креиран помоћу функције ИФ. У приказаном примеру, формула у копији ћелије Г5 је:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
где су имена (Б5: Б16), група (Ц5: Ц16) и резултат (Д5: Д16) именовани опсези. Формула враћа име повезано са 1., 2. и 3. највишом вредношћу у групи А.
Напомена: Ово је формула низа која се мора унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Објашњење
Функција ЛАРГЕ је једноставан начин за добијање н-те највеће вредности у опсегу:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
У овом примеру можемо да користимо функцију ЛАРГЕ да бисмо добили највишу оцену, а затим да користимо резултат као „кључ“ да бисмо добили повезано име са ИНДЕКС и МАТЦХ. Приметићемо да прикупљамо вредности за н из опсега Ф5: Ф7, да бисмо добили 1., 2. и 3. највишу оцену.
Међутим, у овом случају преокрет је у томе што морамо разликовати резултате у групи А и групи Б. Другим речима, морамо применити критеријуме. То радимо са функцијом ИФ која се користи за „филтрирање“ вредности пре него што се вреднују са ЛАРГЕ. Као генерички пример, да бисте добили највећу вредност (тј. 1. вредност) у опсегу2 где је опсег 1 = "А", можете користити формулу попут ове:
LARGE(IF(range="A",range2),1)
Напомена: Коришћење ИФ на овај начин чини ову формулу низа.
Радећи изнутра, први корак је добити „1.“ највећу вредност у подацима повезаним са Групом А са функцијом ЛАРГЕ:
LARGE(IF(group="A",score),F5)
У овом случају, вредност у Ф5 је 1, па тражимо најбољи резултат у групи А. Када се процени функција ИФ, она тестира сваку вредност у именованој групи опсега . Именовани резултат опсега је предвиђен за валуе_иф_труе. Ово генерише нови низ који се враћа директно у функцију ЛАРГЕ:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Приметите да су једине оцене које преживе филтар из групе А. ЛАРГЕ затим враћа највиши преостали резултат, 93, директно у функцију МАТЦХ као вредност претраживања. Сада формулу можемо поједноставити на:
=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
Коначно, функција ИНДЕКС враћа име у 3. реду „Ханнах“.
Уз КСЛООКУП
Функција КСЛООКУП се такође може користити за решавање овог проблема, користећи исти претходно објашњени приступ:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Као и горе, ЛАРГЕ је конфигурисан за рад са низом који филтрира ИФ и враћа резултат 93 у КСЛООКУП као вредност претраживања:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Прегледни низ се такође креира коришћењем ИФ као филтра за оцене из групе А. Са повратним низом наведеним као име (Б5: Б16). КСЛООКУП враћа „Ханнах“ као коначни резултат.
Напомене
- Да бисте добили име н-те вредности са критеријумима (тј. Ограничите резултате на групу А или Б), мораћете да проширите формулу тако да користи додатну логику.
- У програму Екцел 365, функција ФИЛТЕР је бољи начин да динамички наведете горње или доње резултате. Овај приступ ће аутоматски поднијети везе.
