S VLOOKUP získame vždy prvý zápas. To isté sa stane s funkciou INDEX MATCH. Ako teda VYHĽADÁM druhý alebo tretí alebo deviaty zápas? V tomto článku sa naučíme, ako získať N -tý výskyt hodnoty v rozsahu.
Generický vzorec
{= MALÉ (AK (rozsah = hodnota, RIADOK (rozsah)-ROW (first_cell_in_range)+1),n)}
Poznámka: toto je an maticový vzorec. Musíte ho zadať pomocou klávesov CTRL + SHIFT + ENTER.
Rozsah: rozsah, v ktorom chcete hľadať nth pozícia hodnotu.
Hodnota: hodnota, ktorú hľadáte nth pozícia vrozsah.
Prvá_cell_in_range: prvá bunka vrozsah. Ak je rozsah A2: A10, prvá bunka v rozsahu je A2.
n: the výskyt počet hodnoty.
Pozrime sa na príklad, aby bolo všetko jasné.
Príklad: Nájdite druhú zhodu v programe Excel
Takže tu mám tento zoznam mien v rozsahu Excel A2: A10. Tento rozsah som pomenoval ako mená. Teraz chcem získať pozíciu druhého výskytu „Rony“ v mená.
Na obrázku vyššie vidíme, že je na 7. pozícii v rozsahu A2: A10 (mená). Teraz musíme získať jeho pozíciu pomocou vzorca Excel.
Vyššie uvedený generický vzorec v C2 vyhľadajte druhý výskyt Ronyho v zozname.
{= MALÉ (AK (names = „Rony“ , RIADOK (mená)-ROW (A2)+1),2)}
Zadajte ho CTRL + SHIFT + ENTER…
A máme odpoveď. Ukazuje 7, čo je správne. Ak zmeníte hodnotu n na 3, poskytne 8. Ak zmeníte hodnotu n väčšiu, ako je výskyt hodnoty v rozsahu, vráti chybu #ČÍSLO.
Ako to funguje?
No je to celkom jednoduché. Pozrime sa na každú časť jednu po druhej.
IF (names = „Rony“ , RIADOK (mená)-ROW (A2)+1) :
V prípade IF, names = “Rony” vráti pole TRUE a FALSE. PRAVDA vždy, keď je bunka v dosahu mená (A2: A10) zodpovedá „Rony“. {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}.
Ďalší ROW (mená)-ROW (A2)+1:
RIADOK (mená): tu funkcia ROW vráti číslo riadka každej bunky v názvoch. {2; 3; 4; 5; 6; 7; 8; 9; 10}.
RIADOK (mená)-ROW (A2)Potom od každej hodnoty v danom poli odpočítame číslo riadku A2. Získame tak rad sériových čísel začínajúcich od 0. {0; 1; 2; 3; 4; 5; 6; 7; 8}.
RIADOK (mená)-ROW (A2)+1: Aby sme dostali sériové čísla od 1, pridáme 1 ku každej hodnote v tomto poli. To nám dáva poradové číslo od 1. {1; 2; 3; 4; 5; 6; 7; 8; 9}.
Teraz tu máme IF ({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9}). Rieši sa to na {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}.
Teraz máme vzorec vyriešený na MALÝ ({1; FALSE; FALSE; FALSE; FALSE; FALSE;7;8; FALSE},2). Teraz funkcia SMALL vráti druhú najmenšiu hodnotu v rozsahu, ktorá je 7.
Ako ho používame?
Prichádza otázka: aký je prínos získania hrubého indexu n -tého zápasu? Bolo by užitočnejšie, keby ste mohli získať súvisiace informácie z n -tej hodnoty. No aj to sa dá. Ak chceme získať hodnotu z hodnoty susednej bunky n -tého zápasu v rozsahu mená (A2: A10).
{= INDEX (B2: B10, SMALL (IF (names = „Rony“ , RIADOK (mená)-ROW (A2)+1),2))}
Takže áno, chlapci, takto môžete získať n -tý zápas v rozmedzí. Dúfam, že som bol dostatočne vysvetľujúci. Ak máte akékoľvek pochybnosti týkajúce sa tohto článku alebo akejkoľvek inej témy súvisiacej s programom Excel/VBA, napíšte do sekcie komentárov nižšie.
Ako získať sekvenčné číslo riadka v programe Excel
Vlookup Najlepších 5 hodnôt s duplicitnými hodnotami pomocou INDEX-MATCH v programe Excel
VLOOKUP Viacnásobné hodnoty
Na vyhľadanie hodnoty použite INDEX a MATCH
Hodnota vyhľadávania s viacerými kritériami
Populárne články:
Funkcia VLOOKUP v programe Excel
COUNTIF v Exceli 2016
Ako používať funkciu SUMIF v programe Excel