Všeobecný vzorec
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Zhrnutie
Ak chcete získať názov n-tej najväčšej hodnoty s kritériami, môžete použiť INDEX a MATCH, funkciu LARGE a filter vytvorený pomocou funkcie IF. V zobrazenom príklade je vzorec v bunke G5, skopírovaný nadol,:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
kde meno (B5: B16), skupina (C5: C16) a skóre (D5: D16) sú pomenované rozsahy. Vzorec vráti názov spojený s 1., 2. a 3. najvyššou hodnotou v skupine A.
Poznámka: Toto je vzorec poľa, ktorý je potrebné zadať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Vysvetlenie
Funkcia LARGE je jednoduchý spôsob, ako získať n-tú najväčšiu hodnotu v rozsahu:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
V tomto príklade môžeme použiť funkciu VEĽKÉ na získanie najvyššieho skóre, potom použiť skóre ako „kľúč“ na získanie súvisiaceho názvu s INDEX a MATCH. Všimnite si, že zbierame hodnoty n z rozsahu F5: F7, aby sme získali 1., 2. a 3. najvyššie skóre.
V tomto prípade však ide o zvrat, že musíme rozlišovať medzi skóre v skupine A a skupine B. Inými slovami, musíme použiť kritériá. Robíme to pomocou funkcie IF, ktorá slúži na „filtrovanie“ hodnôt pred tým, ako sa vyhodnotia pomocou LARGE. Ako všeobecný príklad získate najväčšiu hodnotu (tj. 1. hodnotu) v rozsahu2, kde rozsah 1 = „A“, môžete použiť nasledujúci vzorec:
LARGE(IF(range="A",range2),1)
Poznámka: použitie IF týmto spôsobom z tohto robí maticový vzorec.
Pri práci zvnútra von je prvým krokom získanie „1.“ najväčšej hodnoty v údajoch spojených so skupinou A pomocou funkcie LARGE:
LARGE(IF(group="A",score),F5)
V tomto prípade je hodnota v F5 1, preto žiadame o najvyššie skóre v skupine A. Pri hodnotení funkcie IF testuje každú hodnotu v pomenovanej skupine rozsahov . Pomenované skóre rozsahu je poskytované pre value_if_true. Toto vygeneruje nové pole, ktoré sa vráti priamo do funkcie LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Všimnite si, že jediné skóre, ktoré prežije filter, je zo skupiny A. VEĽKÉ potom vráti najvyššie zostávajúce skóre 93 priamo do funkcie MATCH ako vyhľadávaciu hodnotu. Teraz môžeme vzorec zjednodušiť na:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Teraz vidíme, že funkcia MATCH je nakonfigurovaná pomocou rovnakého filtrovaného poľa, ktoré sme videli vyššie. Funkcia IF opäť odfiltruje nežiaduce hodnoty a MATCH časť vzorca sa vyrieši takto:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Pretože 93 sa objavuje na 3. pozícii, MATCH vráti 3 priamo do funkcie INDEX:
=INDEX(name,3) // Hannah
Nakoniec funkcia INDEX vráti názov v 3. riadku „Hannah“.
S XLOOKUPOM
Na vyriešenie tohto problému je možné použiť aj funkciu XLOOKUP pomocou rovnakého postupu, ktorý je vysvetlený vyššie:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Ako je uvedené vyššie, funkcia LARGE je nakonfigurovaná na prácu s poľom filtrovaným podľa IF a vráti hodnotu 93 do XLOOKUP ako vyhľadávaciu hodnotu:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Vyhľadávacie pole je tiež vytvorené použitím IF ako filtra skóre zo skupiny A. S návratovým poľom ako názov (B5: B16). XLOOKUP vráti ako posledný výsledok výraz „Hannah“.
Poznámky
- Ak chcete získať názov n-tej hodnoty s kritériami (tj. Obmedziť výsledky na skupinu A alebo B), budete musieť rozšíriť vzorec tak, aby používal ďalšiu logiku.
- V Exceli 365 je funkcia FILTER lepším spôsobom, ako dynamicky vypisovať horné alebo spodné výsledky. Tento prístup automaticky zvládne väzby.
