Pozrite si video - vyhľadajte druhú, tretiu alebo deviatu zodpovedajúcu hodnotu
Pokiaľ ide o vyhľadávanie údajov v programe Excel, existujú dve úžasné funkcie, ktoré často používam - VLOOKUP a INDEX (väčšinou v spojení s funkciou MATCH).
Tieto vzorce sú však navrhnuté tak, aby našli iba prvú inštanciu vyhľadávanej hodnoty.
Ale čo keď chcete vyhľadať druhú, tretiu, štvrtú alebo deviatu hodnotu.
S trochou práce navyše sa to dá zvládnuť.
V tomto tutoriále vám ukážem rôzne spôsoby (s príkladmi), ako vyhľadať druhú alebo deviatu hodnotu v programe Excel.
Vyhľadajte druhú, tretiu alebo deviatu hodnotu v programe Excel
V tomto návode sa budem zaoberať dvoma spôsobmi vyhľadania druhej alebo deviatej hodnoty v programe Excel:
- Použitie pomocného stĺpca.
- Použitie maticových vzorcov.
Začnime a ponorme sa.
Použitie pomocného stĺpca
Predpokladajme, že ste koordinátorom školenia v organizácii a máte množinu údajov, ako je uvedené nižšie. Chcete uviesť všetky školenia pred menom zamestnanca.
V uvedenom súbore údajov absolvovali zamestnanci školenie o rôznych nástrojoch balíka Microsoft Office (Excel, PowerPoint a Word).
Teraz môžete použiť funkciu VLOOKUP alebo kombináciu INDEX/MATCH na nájdenie školenia, ktoré zamestnanec absolvoval. Vráti však iba prvú zhodnú inštanciu.
Napríklad v prípade Johna absolvoval všetky tri školenia, ale keď vyhľadám jeho meno pomocou príkazu VLOOKUP alebo INDEX/MATCH, vždy sa mi vráti „Excel“, čo je prvé školenie pre jeho meno v zozname. .
Aby sme to dosiahli, môžeme použiť pomocný stĺpec a vytvoriť v ňom jedinečné vyhľadávacie hodnoty.
Tu sú kroky:
- Vložte stĺpec pred stĺpec, ktorý obsahuje zoznam školení.
- Do bunky B2 zadajte nasledujúci vzorec:
= A2 a COUNTIF ($ A $ 2: $ A2, A2)
- Do bunky F2 zadajte nasledujúci vzorec a skopírujte a prilepte všetky ostatné bunky:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Vyššie uvedený vzorec by vrátil školenie pre každého zamestnanca v poradí, v akom je uvedené v zozname. V prípade, že pre zamestnanca nie je uvedené žiadne školenie, vráti prázdne miesto.
Ako tento vzorec funguje?
Vzorec COUNTIF v stĺpci pomocníka robí meno každého zamestnanca jedinečným pridaním čísla. Napríklad prvá inštancia John sa stane John1, druhá inštancia sa stane John2 a tak ďalej.
Vzorec VLOOKUP teraz používa tieto jedinečné mená zamestnancov na nájdenie zodpovedajúceho školenia.
Všimnite si toho, že $ E2 & COLUMNS ($ F $ 1: F1) je vyhľadávacia hodnota vo vzorci. To by pridalo číslo k názvu zamestnanca na základe čísla stĺpca. Keď sa napríklad tento vzorec použije v bunke F2, vyhľadávacia hodnota sa zmení na „John1“. V bunke G2 sa stáva „John2“ a podobne.
Použitie vzorca Array
Ak nechcete pôvodný súbor údajov meniť pridaním pomocných stĺpcov, môžete na vyhľadanie druhej, tretej alebo n -tej hodnoty použiť aj vzorec poľa.
Predpokladajme, že máte rovnaký súbor údajov, ako je uvedené nižšie:
Tu je vzorec, ktorý vráti správnu vyhľadávaciu hodnotu:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14)) -1, ""), COLUMNS ($ E $ 1 : E1))), "")
Skopírujte tento vzorec a vložte ho do bunky E2.
Upozorňujeme, že toto je vzorec poľa a namiesto stlačenia klávesu Enter musíte použiť kombináciu klávesov Ctrl + Shift + Enter (podržte klávesy Control a Shift a stlačte kláves Enter).
Kliknutím sem stiahnete ukážkový súbor.
Ako tento vzorec funguje?
Rozdeľme tento vzorec na časti a uvidíme, ako funguje.
$ A $ 2: $ A $ 14 = $ D2
Vyššie uvedená časť vzorca porovnáva každú bunku v A2: A14 s hodnotou v D2. V tejto množine údajov kontroluje, či bunka obsahuje meno „John“ alebo nie.
Vráti pole TRUE of FALSE. Ak má bunka meno „John“, bolo by to pravda, v opačnom prípade by to bola nepravda.
Nasleduje pole, ktoré by ste získali v tomto príklade:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Všimnite si toho, že má TRUE na 1., 7. a 111. pozícii, pretože v množine údajov sa nachádza meno John.
IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Vyššie uvedený vzorec IF používa pole TRUE a FALSE a nahrádza hodnotu TRUE pozíciou jeho výskytu v zozname (dané riadkom ($ A $ 2: $ A $ 14) -1) a FALSE znakom „“ (prázdne miesta). Nasledujúce je výsledné pole, ktoré získate pomocou tohto vzorca IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Poznatky 1, 7 a 11 sú pozícia výskytu Johna v zozname.
MALÉ (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), STĹPCE ($ E $ 1: E1))
Funkcia SMALL teraz vyberie prvé najmenšie, druhé najmenšie a tretie najmenšie číslo z tohto poľa. Všimnite si toho, že používa funkciu COLUMNS na generovanie čísla stĺpca. V bunke E2 funkcia COLUMNS vráti hodnotu 1 a funkcia SMALL vráti hodnotu 1. V bunke F2 funkcia COLUMNS vráti hodnotu 2 a funkcia SMALL vráti hodnotu 7.
INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)), SLOUPKY ($ E $ 1: E1) )))
Funkcia INDEX teraz vráti hodnotu zo zoznamu v stĺpci B na základe polohy vrátenej funkciou SMALL. V bunke E2 teda vráti „Excel“, čo je prvá položka v B2: B14. V bunke F2 vráti PowerPoint, čo je 7. položka v zozname.
Pretože existujú prípady, keď pre niektorých zamestnancov prebieha iba jedno alebo dve školenia, funkcia INDEX vráti chybu. Funkcia IFERROR sa používa na vrátenie prázdneho miesta namiesto chyby.
Všimnite si toho, že v týchto príkladoch som použil odkazy na rozsah. V praktických príkladoch je však výhodné previesť údaje do tabuľky programu Excel. Konvertovaním na tabuľku programu Excel môžete používať štruktúrované odkazy, čo uľahčuje vytváranie vzorcov. Tabuľka programu Excel môže tiež automaticky účtovať všetky nové položky školenia, ktoré sú pridané do zoznamu (takže vzorce nemusíte upravovať zakaždým).
Čo urobíte, keď musíte vyhľadať druhú, tretiu alebo deviatu hodnotu? Som si istý, že existuje viac spôsobov, ako to urobiť. Ak používate niečo jednoduchšie, ako je tu uvedené, podeľte sa s nami všetkými v sekcii komentárov.
Kliknutím sem stiahnete ukážkový súbor.
