Obecný vzorec
=INDEX(names,MATCH(LARGE(values,F5),values,0))
souhrn
Chcete-li získat název n-té největší hodnoty, můžete použít INDEX a MATCH s funkcí LARGE. V zobrazeném příkladu je vzorec v buňce H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
kde název (B5: B16) a skóre (D5: D16) jsou pojmenované rozsahy.
Vysvětlení
Stručně řečeno, tento vzorec používá funkci LARGE k vyhledání n-té největší hodnoty v sadě dat. Jakmile máme tuto hodnotu, připojíme ji do standardního vzorce INDEX a MATCH, abychom načetli přidružený název. Jinými slovy, k získání přidružených informací používáme n-tou největší hodnotu jako „klíč“.
Funkce LARGE je přímý způsob, jak získat n-tou největší hodnotu v rozsahu. Jednoduše zadejte rozsah pro první argument (pole) a hodnotu pro n jako druhý argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Při práci zevnitř ven je prvním krokem získání „1.“ největší hodnoty v datech pomocí funkce LARGE:
LARGE(score,F5) // returns 93
V tomto případě je hodnota v F5 1, takže žádáme o 1. největší skóre (tj. Nejvyšší skóre), což je 93. Nyní můžeme vzorec zjednodušit na:
=INDEX(name,MATCH(93,score,0))
Uvnitř funkce INDEX je nastavena funkce MATCH k vyhledání pozice 93 v pojmenovaném skóre rozsahu (D5: D16):
MATCH(93,score,0) // returns 3
Protože 93 se objeví ve 3. řádku, MATCH vrací 3 přímo na INDEX jako číslo řádku s názvem jako pole:
=INDEX(name,3) // Hannah
Nakonec funkce INDEX vrátí název ve 3. řádku „Hannah“.
Všimněte si, že vyzvedáváme hodnoty pro n z rozsahu F5: F7, abychom získali 1., 2. a 3. nejvyšší skóre při kopírování vzorce.
Načíst skupinu
Stejný základní vzorec bude fungovat k načtení všech souvisejících informací. Chcete-li získat skupinu pro největší hodnoty, můžete jednoduše změnit pole dodávané na INDEX s pojmenovanou skupinou rozsahů :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
S hodnotou 1 v F5 získá LARGE nejvyšší skóre a vzorec vrátí „A“.
Poznámka: V aplikaci Excel 365 můžete pomocí funkce FILTER dynamicky vypsat horní nebo dolní výsledky.
S XLOOKUPEM
Funkci XLOOKUP lze také použít k vrácení názvu n-té největší hodnoty, jako je tato:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE vrací největší hodnotu 93 přímo do XLOOKUP jako vyhledávací hodnotu:
=XLOOKUP(93,score,name) // Hannah
S pojmenovaným skóre rozsahu (D5: D16) jako vyhledávacím polem a názvem (B5: B16) jako návratovým polem vrátí XLOOKUP „Hannah“ jako dříve.
Manipulace s vazbami
Duplicitní hodnoty v číselných datech vytvoří „kravatu“. Pokud dojde ke shodě v hodnotách, které jsou hodnoceny, například pokud jsou první a druhá největší hodnota stejné, vrátí LARGE pro každou stejnou hodnotu. Když je tato hodnota předána do funkce MATCH, MATCH vrátí pozici první shody, takže uvidíte vrácené stejné (první) jméno.
Pokud existuje možnost vazeb, možná budete chtít implementovat nějakou strategii rozbíjení vazeb. Jedním z přístupů je vytvoření nového pomocného sloupce hodnot, které byly upraveny tak, aby přerušovaly vazby. Potom použijte hodnoty sloupce pomocníka k seřazení a načtení informací. Díky tomu je logika použitá k přerušení vazeb jasná a explicitní.
Dalším přístupem je přerušení vazeb pouze na základě pozice (tj. První remíza „vyhrává“). Zde je vzorec, který využívá tento přístup:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Poznámka: Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter, kromě Excel 365.
Tady použijeme MATCH k nalezení čísla 1 a vytvoříme vyhledávací pole pomocí logické logiky, která (1) porovná všechna skóre s hodnotou vrácenou LARGE:
score=LARGE(score,F5)
a (2) použije kontrolu rozšiřujícího se rozsahu, pokud je název již v seznamu seřazených:
COUNTIF(H$4:H4,name)=0
Pokud je jméno již v seznamu, je logikou „zrušeno“ a odpovídá další (duplicitní) hodnotě. Všimněte si, že rozšiřující se rozsah začíná na předchozím řádku, aby se zabránilo cyklickému odkazu.
Tento přístup funguje v tomto příkladu, protože ve sloupci názvu nejsou žádné duplicitní názvy. Pokud se však v hodnocených hodnotách vyskytnou duplicitní názvy, je třeba přístup upravit. Nejjednodušším řešením je zajistit, aby jména byla jedinečná.
Poznámky
- Chcete-li získat název n-té hodnoty s kritérii (tj. Omezit výsledky na skupinu A nebo B), budete muset rozšířit vzorec tak, aby používal další logiku.
- V aplikaci Excel 365 je funkce FILTER lepší způsob, jak dynamicky vypsat horní nebo dolní výsledky. Tento přístup automaticky zvládne vazby.
