Obecný vzorec
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
souhrn
Chcete-li získat název n-té největší hodnoty s kritérii, můžete použít INDEX a MATCH, funkci VELKÝ a filtr vytvořený pomocí funkce IF. V zobrazeném příkladu je vzorec v buňce G5 zkopírovaný dolů:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
kde název (B5: B16), skupina (C5: C16) a skóre (D5: D16) jsou pojmenované rozsahy. Vzorec vrací název přidružený k 1., 2. a 3. nejvyšší hodnotě ve skupině A.
Poznámka: Toto je maticový vzorec, který je nutné zadat pomocí kláves Control + Shift + Enter, s výjimkou Excel 365.
Vysvětlení
Funkce LARGE je snadný způsob, jak získat n-tou největší hodnotu v rozsahu:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
V tomto příkladu můžeme použít funkci VELKÉ k získání nejvyššího skóre, poté použít skóre jako „klíč“ k načtení přidruženého názvu s INDEX a MATCH. Všimněte si, že získáváme hodnoty pro n z rozsahu F5: F7, abychom získali 1., 2. a 3. nejvyšší skóre.
V tomto případě však jde o to, že musíme rozlišovat mezi skóre ve skupině A a skupině B. Jinými slovy, musíme použít kritéria. Děláme to pomocí funkce IF, která se používá k „filtrování“ hodnot před jejich vyhodnocením pomocí VELKÉHO. Jako obecný příklad můžete získat největší hodnotu (tj. 1. hodnotu) v rozsahu2, kde rozsah 1 = "A", můžete použít vzorec takto:
LARGE(IF(range="A",range2),1)
Poznámka: použití IF tímto způsobem z toho dělá maticový vzorec.
Při práci zevnitř ven je prvním krokem získání „první“ největší hodnoty v datech přidružených ke skupině A s funkcí VELKÉ:
LARGE(IF(group="A",score),F5)
V tomto případě je hodnota v F5 je 1, takže se žádá o nejvyšší skóre ve skupině A. Když je funkce IF vyhodnocena testuje každou hodnotu v pojmenovaný rozsah skupiny . Pojmenované skóre rozsahu je poskytováno pro value_if_true. Tím se vygeneruje nové pole, které se vrací přímo do funkce LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Všimněte si, že jediná skóre, která přežijí filtr, jsou ze skupiny A. VELKÁ pak vrátí nejvyšší zbývající skóre 93 přímo do funkce MATCH jako vyhledávací hodnotu. Nyní můžeme vzorec zjednodušit na:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nyní vidíme, že funkce MATCH je nakonfigurována pomocí stejného filtrovaného pole, které jsme viděli výše. Funkce IF opět filtruje nežádoucí hodnoty a MATCH část vzorce se vyřeší na:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Protože 93 se objeví na 3. pozici, MATCH vrací 3 přímo do funkce INDEX:
=INDEX(name,3) // Hannah
Nakonec funkce INDEX vrátí název ve 3. řádku „Hannah“.
S XLOOKUPEM
K vyřešení tohoto problému lze také použít funkci XLOOKUP pomocí stejného postupu, který je vysvětlen výše:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Jak je uvedeno výše, LARGE je nakonfigurován pro práci s polem filtrovaným podle IF a vrací výsledek 93 do XLOOKUP jako vyhledávací hodnotu:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Vyhledávací pole je také vytvořeno pomocí IF jako filtru pro skóre ze skupiny A. S návratovým polem uvedeným jako název (B5: B16). XLOOKUP vrací „Hannah“ jako konečný výsledek.
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.
