Všeobecný vzorec
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Zhrnutie
Ak chcete získať názov n-tej najväčšej hodnoty, môžete použiť INDEX a MATCH s funkciou LARGE. V zobrazenom príklade je vzorec v bunke H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
kde meno (B5: B16) a skóre (D5: D16) sú pomenované rozsahy.
Vysvetlenie
Stručne povedané, tento vzorec používa funkciu LARGE na nájdenie n-tej najväčšej hodnoty v množine údajov. Keď máme túto hodnotu, zapojíme ju do štandardného vzorca INDEX a MATCH, aby sme získali priradený názov. Inými slovami, na získanie súvisiacich informácií používame n-tú najväčšiu hodnotu ako „kľúč“.
Funkcia LARGE je priamy spôsob, ako získať n-tú najväčšiu hodnotu v rozsahu. Jednoducho zadajte rozsah pre prvý argument (pole) a hodnotu pre n ako druhý argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Pri práci zvnútra von je prvým krokom získanie „1.“ najväčšej hodnoty v dátach pomocou funkcie LARGE:
LARGE(score,F5) // returns 93
V tomto prípade je hodnota v F5 1, preto požadujeme 1. najväčšie skóre (tj. Najvyššie skóre), ktoré je 93. Vzorec teraz môžeme zjednodušiť na:
=INDEX(name,MATCH(93,score,0))
Vo vnútri funkcie INDEX je nastavená funkcia MATCH na vyhľadanie polohy 93 v pomenovanom skóre rozsahu (D5: D16):
MATCH(93,score,0) // returns 3
Pretože 93 sa zobrazuje v 3. riadku, MATCH vráti 3 priamo na INDEX ako číslo riadku s názvom ako poľom:
=INDEX(name,3) // Hannah
Nakoniec funkcia INDEX vráti názov v 3. riadku „Hannah“.
Všimnite si, že zbierame hodnoty n z rozsahu F5: F7, aby sme pri kopírovaní vzorca dostali 1., 2. a 3. najvyššie skóre.
Načítať skupinu
Rovnaký základný vzorec bude fungovať na získanie akýchkoľvek súvisiacich informácií. Ak chcete získať skupinu s najväčšími hodnotami, môžete jednoducho zmeniť pole dodávané na INDEX s pomenovanou skupinou rozsahov :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
S hodnotou 1 v F5 získa VEĽKÉ najvyššie skóre a vzorec vráti hodnotu „A“.
Poznámka: V programe Excel 365 môžete pomocou funkcie FILTER dynamicky vypisovať horné alebo spodné výsledky.
S XLOOKUPOM
Funkciu XLOOKUP možno tiež použiť na vrátenie názvu n-tej najväčšej hodnoty, ako je táto:
=XLOOKUP(LARGE(score,F5),score,name)
VEĽKÉ vráti najväčšiu hodnotu 93 priamo do XLOOKUPU ako vyhľadávaciu hodnotu:
=XLOOKUP(93,score,name) // Hannah
S pomenovaným skóre rozsahu (D5: D16) ako vyhľadávacím poľom a názvom (B5: B16) ako návratové pole vráti XLOOKUP „Hannah“ ako predtým.
Manipulácia s väzbami
Duplicitné hodnoty v číselných údajoch vytvoria „kravatu“. Ak dôjde k zhode v hodnotených hodnotách, napríklad ak sú prvá a druhá najväčšia hodnota rovnaké, funkcia LARGE vráti rovnakú hodnotu pre každú z nich. Keď je táto hodnota odovzdaná do funkcie MATCH, MATCH vráti pozíciu prvej zhody, takže uvidíte vrátené rovnaké (prvé) meno.
Ak existuje možnosť prepojenia, možno budete chcieť implementovať nejaký druh stratégie na prerušenie spojenia. Jedným z prístupov je vytvorenie nového pomocného stĺpca s hodnotami, ktoré boli upravené tak, aby prerušili väzby. Potom použite hodnoty stĺpca pomocníka na zoradenie a získanie informácií. Vďaka tomu je logika použitá na prerušenie väzieb jasná a explicitná.
Ďalším prístupom je prerušenie väzov iba na základe pozície (tj. Prvá remíza „vyhráva“). Tu je vzorec, ktorý využíva tento prístup:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Tu použijeme MATCH na nájdenie čísla 1 a pomocou boolovskej logiky skonštruujeme vyhľadávacie pole, ktoré (1) porovnáva všetky skóre s hodnotou vrátenou LARGE:
score=LARGE(score,F5)
a (2) použije kontrolu rozširujúceho sa rozsahu, ak je názov už v zozname zaradených položiek:
COUNTIF(H$4:H4,name)=0
Ak je meno už v zozname, je logikou „zrušené“ a nasledujúca (duplicitná) hodnota sa zhoduje. Všimnite si, že rozširujúci sa rozsah začína na predchádzajúcom riadku, aby sa zabránilo cyklickému odkazu.
Tento prístup funguje v tomto príklade, pretože v stĺpci mien nie sú duplicitné názvy. Ak sa však v zoradených hodnotách vyskytujú duplicitné názvy, je potrebné prístup upraviť. Najjednoduchším riešením je ubezpečiť sa, že mená sú jedinečné.
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.
