S VLOOKUP získáme vždy první zápas. Totéž se stane s funkcí INDEX MATCH. Jak tedy VYHLEDÁME druhý zápas nebo 3. nebo N -tý? V tomto článku se naučíme, jak získat N -tý výskyt hodnoty v rozsahu.
Obecný vzorec
{= MALÝ (IF (rozsah = hodnota,ŘÁDEK(rozsah)-ŘÁDEK(first_cell_in_range)+1),n)}
Poznámka: toto je maticový vzorec. Musíte jej zadat pomocí kláves CTRL + SHIFT + ENTER.
Rozsah: rozsah, ve kterém chcete hledat nth pozice hodnota.
Hodnota: hodnota, kterou hledáte nth pozice vrozsah.
First_cell_in_range: první buňka vrozsah. Pokud je rozsah A2: A10, pak první buňka v rozsahu je A2.
n: the výskyt počet hodnoty.
Podívejme se na příklad, aby bylo vše jasné.
Příklad: Najděte druhou shodu v Excelu
Takže tady mám tento seznam jmen v excelovém rozsahu A2: A10. Tento rozsah jsem pojmenoval jako jména. Nyní chci získat pozici druhého výskytu „Rony“ v jména.
Na obrázku výše vidíme, že je na 7. pozici v rozsahu A2: A10 (jména). Nyní musíme získat jeho pozici pomocí vzorce Excel.
Použijte výše uvedený obecný vzorec v C2 k vyhledání druhého výskytu Rony v seznamu.
{= MALÝ (IF (names = „Rony“ ,ŘÁDEK(jména)-ŘÁDEK(A2)+1),2)}
Zadejte jej pomocí kláves CTRL + SHIFT + ENTER…
A máme odpověď. Zobrazuje 7, což je správné. Pokud změníte hodnotu n na 3, získáte 8. Pokud změníte hodnotu n větší, než je výskyt hodnoty v rozsahu, vrátí chybu #ČÍSLO.
Jak to funguje?
No, je to docela snadné. Pojďme se podívat na každý díl jeden po druhém.
LI(names = „Rony“ ,ŘÁDEK(jména)-ŘÁDEK(A2)+1) :
V případě IF, names = “Rony” vrací pole PRAVDA a NEPRAVDA. PRAVDA vždy, když je buňka v dosahu jména (A2: A10) odpovídá „Rony“. {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}.
Další ŘÁD (jména)-ŘÁDEK(A2)+1:
ŘÁDEK(jména): zde funkce ROW vrací číslo řádku každé buňky v názvech. {2; 3; 4; 5; 6; 7; 8; 9; 10}.
ŘÁDEK(jména)-ŘÁDEK(A2)Poté od každé hodnoty v daném poli odečteme číslo řádku A2. To nám dává řadu pořadových čísel od 0. {0; 1; 2; 3; 4; 5; 6; 7; 8}.
ŘÁDEK(jména)-ŘÁDEK(A2)+1: Abychom získali sériová čísla od 1, přidáme 1 ke každé hodnotě v tomto poli. To nám dává pořadové číslo od 1. {1; 2; 3; 4; 5; 6; 7; 8; 9}.
Nyní máme IF ({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9}). To řeší na {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}.
Nyní máme vzorec vyřešen na MALÝ ({1; FALSE; FALSE; FALSE; FALSE; FALSE;7;8;NEPRAVDIVÉ},2). Nyní SMALL vrací druhou nejmenší hodnotu v rozsahu, což je 7.
Jak to používáme?
Přichází otázka: jaký je přínos získání hrubého indexu n -tého zápasu? Bylo by užitečnější, kdybyste mohli získat související informace z n -té hodnoty. No i to se dá zvládnout. Pokud chceme získat hodnotu z hodnoty sousední buňky n -té shody v rozsahu jména (A2: A10).
{= INDEX (B2: B10, SMALL (IF (names = „Rony“ ,ŘÁDEK(jména)-ŘÁDEK(A2)+1),2))}
Takže jo, lidi, takhle můžete získat n -tý zápas v řadě. Doufám, že jsem to vysvětlil dostatečně. Máte -li jakékoli pochybnosti týkající se tohoto článku nebo jiného tématu souvisejícího s Excelem/VBA, napište níže do sekce komentářů.
Jak získat sekvenční číslo řádku v aplikaci Excel
Vlookup Top 5 hodnot s duplicitními hodnotami pomocí INDEX-MATCH v Excelu
VLOOKUP Více hodnot
K vyhledání hodnoty použijte INDEX a MATCH
Vyhledávací hodnota s více kritérii
Oblíbené články:
Funkce VLOOKUP v Excelu
COUNTIF v aplikaci Excel 2016
Jak používat funkci SUMIF v aplikaci Excel