Sledujte video - vyhledejte druhou, třetí nebo devátou odpovídající hodnotu
Pokud jde o vyhledávání dat v Excelu, často používám dvě úžasné funkce - VLOOKUP a INDEX (většinou ve spojení s funkcí MATCH).
Tyto vzorce jsou však navrženy tak, aby vyhledávaly pouze první instanci vyhledávací hodnoty.
Ale co když chcete vyhledat druhou, třetí, čtvrtou nebo N-tu hodnotu.
S trochou práce navíc se to dá zvládnout.
V tomto tutoriálu vám ukážu různé způsoby (s příklady), jak vyhledat druhou nebo N -tu hodnotu v Excelu.
Vyhledejte druhou, třetí nebo devátou hodnotu v aplikaci Excel
V tomto tutoriálu se budu zabývat dvěma způsoby, jak vyhledat druhou nebo N-tu hodnotu v Excelu:
- Pomocí pomocného sloupce.
- Použití maticových vzorců.
Začněme a ponořme se dovnitř.
Pomocí pomocného sloupce
Předpokládejme, že jste koordinátor školení v organizaci a máte datovou sadu, jak je uvedeno níže. Chcete uvést všechna školení před jménem zaměstnance.
Ve výše uvedené datové sadě absolvovali zaměstnanci školení o různých nástrojích Microsoft Office (Excel, PowerPoint a Word).
Nyní můžete k vyhledání školení, které zaměstnanec absolvoval, použít funkci VLOOKUP nebo kombinaci INDEX/MATCH. Vrátí však pouze první odpovídající instanci.
Například v případě Johna absolvoval všechna tři školení, ale když se podívám na jeho jméno pomocí VLOOKUP nebo INDEX/MATCH, vždy se vrátí 'Excel', což je první školení pro jeho jméno v seznamu .
Abychom to mohli udělat, můžeme použít pomocný sloupec a vytvořit v něm jedinečné vyhledávací hodnoty.
Zde jsou kroky:
- Vložte sloupec před sloupec, který uvádí školení.
- Do buňky B2 zadejte následující vzorec:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- Do buňky F2 zadejte následující vzorec a zkopírujte a vložte pro všechny ostatní buňky:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Výše uvedený vzorec by vrátil školení pro každého zaměstnance v pořadí, v jakém je uvedeno v seznamu. V případě, že pro zaměstnance není uvedeno žádné školení, vrátí prázdné místo.
Jak tento vzorec funguje?
Vzorec COUNTIF ve sloupci pomocníka činí jméno každého zaměstnance jedinečným přidáním čísla. Například první instance John se stane John1, druhá instance se stane John2 a tak dále.
Vzorec VLOOKUP nyní používá tato jedinečná jména zaměstnanců k nalezení odpovídajících školení.
Všimněte si, že $ E2 & COLUMNS ($ F $ 1: F1) je vyhledávací hodnota ve vzorci. To by přidalo číslo k jménu zaměstnance na základě čísla sloupce. Když je například tento vzorec použit v buňce F2, vyhledávací hodnota se změní na „John1“. V buňce G2 se stane „John2“ a tak dále.
Pomocí maticového vzorce
Pokud nechcete změnit původní datovou sadu přidáním pomocných sloupců, můžete také použít vzorec pole k vyhledání druhé, třetí nebo n -té hodnoty.
Předpokládejme, že máte stejnou datovou sadu, jak je uvedeno níže:
Zde je vzorec, který vrátí správnou vyhledávací hodnotu:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14)) -1, ""), COLUMNS ($ E $ 1 : E1))), "")
Zkopírujte tento vzorec a vložte jej do buňky E2.
Všimněte si, že toto je maticový vzorec a místo stisknutí klávesy Enter musíte použít Ctrl + Shift + Enter (podržte klávesy Control a Shift a stiskněte klávesu Enter).
Kliknutím sem stáhnete ukázkový soubor.
Jak tento vzorec funguje?
Pojďme tento vzorec rozdělit na části a zjistit, jak funguje.
$ A $ 2: $ A $ 14 = $ D2
Výše uvedená část vzorce porovnává každou buňku v A2: A14 s hodnotou v D2. V této datové sadě kontroluje, zda buňka obsahuje jméno „John“ nebo ne.
Vrací pole TRUE of FALSE. Pokud má buňka jméno „John“, bylo by to True, jinak by to bylo False.
Níže je pole, které byste získali v tomto příkladu:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Všimněte si, že má TRUE na 1., 7. a 111. pozici, protože tam je místo, kde se v datové sadě objevuje jméno John.
IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Výše uvedený vzorec IF používá pole PRAVDA a NEPRAVDA a nahrazuje PRAVDU pozicí jejího výskytu v seznamu (udáváno ŘÁDKEM ($ A $ 2: $ A $ 14) -1) a NEPRAVDA s „“ (prázdná místa). Následuje výsledné pole, které získáte pomocí tohoto vzorce IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Pozice než 1, 7 a 11 jsou pozice výskytu Johna v seznamu.
MALÉ (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), SLOUPKY ($ E $ 1: E1))
Funkce SMALL nyní vybere první nejmenší, druhé nejmenší a třetí nejmenší číslo z tohoto pole. Všimněte si toho, že používá funkci COLUMNS ke generování čísla sloupce. V buňce E2 vrátí funkce COLUMNS hodnotu 1 a funkce SMALL vrátí hodnotu 1. V buňce F2 vrátí funkce COLUMNS hodnotu 2 a funkce SMALL vrátí hodnotu 7.
INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)), SLOUPKY ($ E $ 1: E1) ))
Funkce INDEX nyní vrací hodnotu ze seznamu ve sloupci B na základě polohy vrácené funkcí SMALL. V buňce E2 tedy vrátí „Excel“, což je první položka v B2: B14. V buňce F2 vrátí PowerPoint, což je 7. položka v seznamu.
Protože existují případy, kdy pro některé zaměstnance probíhá pouze jedno nebo dvě školení, funkce INDEX vrátí chybu. Funkce IFERROR slouží k vrácení mezery místo chyby.
Všimněte si, že v těchto příkladech jsem použil odkazy na rozsah. V praktických příkladech je však užitečné převést data do tabulky aplikace Excel. Převedením do tabulky aplikace Excel můžete použít strukturované odkazy, což usnadňuje vytváření vzorců. Tabulka aplikace Excel může také automaticky účtovat všechny nové položky školení, které jsou přidány do seznamu (takže vzorce nemusíte upravovat pokaždé).
Co uděláte, když musíte vyhledat druhou, třetí nebo N-tu hodnotu? Jsem si jistý, že existuje více způsobů, jak to udělat. Pokud používáte něco jednoduššího, než je zde uvedeno, podělte se s námi o všechny v sekci komentáře.
Kliknutím sem stáhnete ukázkový soubor.
