Jak vyhledat více hodnot v Excelu: Krok za krokem

Při práci s velkými sadami dat v aplikaci Microsoft Excel může být obtížné najít více hodnot, které odpovídají několika podmínkám. Některé vestavěné funkce, např. VLOOKUP, byly původně navrženy pro práci s jednotlivými hodnotami.

Několik lze zkombinovat a vyhledat více hodnot. Zahrnují funkce VLOOKUP, INDEX, MATCH a IF. Aktuální verze aplikace Excel mohou používat dynamická pole, zatímco starší verze používají maticové vzorce.

Tento článek vám přesně ukáže, jak tyto funkce používat ve vzorcích, které ve vašich datech nacházejí více hodnot.

Pojďme!

Obsah

• Jak používat SVYHLEDAT s více hodnotami
  • Co jsou vzorce pole?
  • Příklad VLOOKUP
  • Nejnovější verze Excelu
  • Starší verze
  • Přesná shoda vs. přibližná shoda
  • Více o funkci VLOOKUP
• Jak používat INDEX a MATCH k vyhledání více hodnot
  • Jak používat INDEX a MATCH společně v Excelu 365
  • Práce se staršími verzemi Excelu
• Jak používat funkci IF k nalezení více hodnot
  • Aktuální verze Excelu
  • Starší verze Excelu
• Jak používat SUMPRODUCT pro více kritérií
• 3 Běžné chyby s vyhledávacími funkcemi
  • 1. #N/A Chyby
  • 2. #REF! Chyby
  • 3. Kruhové chyby
• Jak používat IFERROR s vyhledávacími funkcemi
• 7 Pokročilé techniky vyhledávání
  • 1. Vyhledávání relativní pozice
  • 2. Funkce MALÉ
  • 3. Funkce ROW
  • 4. Funkce FILTR
  • 5. Pomocné sloupce
  • 6. Dynamická pole
  • 7. Power Query
• Závěrečné myšlenky

Jak používat SVYHLEDAT s více hodnotami

Funkce se často používá k nalezení jednotlivých hodnot v rozsahu dat. Pomocí tohoto vyhledávacího vzorce však můžete také vyhledat více shod v Excelu.

Ve výchozím nastavení vrátí pouze první odpovídající hodnotu, kterou najde. Funkci však můžete upravit tak, aby vracela více hodnot pomocí maticového vzorce.

Co jsou vzorce pole?

Maticový vzorec je vzorec, který může provádět výpočty s poli dat. Říká se tomu maticový vzorec, protože může vrátit pole výsledků, nikoli jednu hodnotu.

Při vytváření maticového vzorce existuje několik jednoduchých kroků:

1. Vyberte rozsah buněk, které chcete prohledat.

2. Zadejte vzorec do řádku vzorců.

3. Dokončete to stisknutím Ctrl + Shift + Enter.

Syntaxe maticového vzorce je podobná syntaxi běžného vzorce, ale obsahuje složené závorky {} kolem vzorce. Složené závorky označují, že vzorec je maticový vzorec a že vrátí pole hodnot.

Příklady v tomto článku vám ukážou, jak správně používat maticové vzorce.

Příklad VLOOKUP

Náš příklad má ve sloupci A listu pět položek:

• Jablko

• Banán

• Chléb

• Mrkev

• Třešeň

Úkolem je zkontrolovat, zda jsou v tomto seznamu tři konkrétní druhy ovoce: jablko, banán a třešeň.

Nejnovější verze Excelu

Syntaxe SVYHLEDAT závisí na verzi Excelu, kterou používáte.

Nejnovější verze ( Excel 365 nebo Excel 2021 ) podporují dynamická pole. Tato funkce umožňuje vzorcům vracet více výsledků, které se „rozsypou“ do sousedních buněk.

Toto je syntaxe (rovná se začíná vzorec):

=VLOOKUP(vyhledávací_hodnota, pole_tabulky, číslo indexu_sloupce, [vyhledání_rozsahu])

• lookup_value: Hodnota, kterou chcete vyhledat.

• table_array: Celá datová tabulka, kterou chcete prohledávat.

• col_index_num: Číslo sloupce tabulky v poli table_array, který obsahuje data, která chcete vrátit.

• range_lookup: Volitelné. Určuje, zda chcete přesnou shodu nebo přibližnou shodu.

Náš konkrétní příklad používá tento vzorec:

=VLOOKUP({"jablko","banán","třešeň"},A1:A5,1,NEPRAVDA)

Po zadání vzorce do buňky B1 se výsledky rozsypou do buněk C1 a D1. Tento obrázek ukazuje příklad v akci:

Starší verze

Pokud používáte starší verzi Excelu, která nepodporuje dynamická pole (např. Excel 2019 nebo starší), budete muset použít trochu jiný přístup s maticovým vzorcem.

Následuj tyto kroky:

1. Klikněte na buňku, kde chcete zobrazit výsledky pro první položku (např. sloupec B).

2. Zadejte následující vzorec bez stisknutí klávesy Enter: =SVYHLEDAT({“jablko“,“banán“,“třešeň”}, A1:A5, 1, FALSE)

3. Stisknutím kláves Ctrl + Shift + Enter z toho uděláte maticový vzorec.

4. Zkopírujte buňku B1 a vložte ji do buňky níže (nebo použijte úchyty výplně).

Když použijete Ctrl + Shift + Enter, Excel přidá kolem vzorce složené závorky. To znamená, že se jedná o maticový vzorec.

Přesná shoda vs. přibližná shoda

Ve výchozím nastavení používá funkce SVYHLEDAT přibližnou shodu. To znamená, že vrátí nejbližší shodu, kterou dokáže najít, i když se hodnoty buněk přesně neshodují.

Pokud chcete provést přesnou shodu, můžete nastavit argument range_lookup na FALSE.

Mějte na paměti, že přibližné shody nejlépe fungují s uspořádanými číselnými hodnotami. Obvykle není vhodné, když je hodnotou buňky text.

Více o funkci VLOOKUP

Pokud se chcete o této všestranné funkci dozvědět více, přečtěte si tyto články:

Nyní, když jste nastaveni na funkci SVYHLEDAT, pojďme se podívat na dvě další funkce, které mohou dělat to, co dělá, jiným způsobem: INDEX a MATCH.

Jak používat INDEX a MATCH k vyhledání více hodnot

Můžete kombinovat funkce INDEX a MATCH a najít více hodnot ve více řádcích.

Funkce INDEX v aplikaci Excel vrátí hodnotu nebo odkaz na buňku v zadaném rozsahu.

=INDEX(pole; číslo_řádku; [číslo_sloupce])

• pole: Rozsah buněk, ve kterých má být prohledána hodnota.

• row_num: Číslo řádku v poli, ze kterého se má vrátit hodnota.

• column_num: (Volitelné) Číslo sloupce v poli, ze kterého se má vrátit hodnota. Pokud je vynechán, funkce vrátí celý řádek.

Funkce MATCH v aplikaci Excel vrátí pozici hodnoty v zadaném rozsahu.

=MATCH(hledaná_hodnota, vyhledávací_pole, [typ_shody])

• lookup_value: Hodnota, která se má hledat v rámci vyhledávacího pole.

• lookup_array: Rozsah buněk, které mají být prohledány pro lookup_value.

• match_type: (Volitelné) Typ shody, která má být provedena. Pokud je vynechán, funkce provede přesnou shodu.

Jak používat INDEX a MATCH společně v Excelu 365

Chcete-li použít INDEX a MATCH společně k vyhledání více hodnot v Excelu, musíte použít maticový vzorec.

Při práci s dřívějšími ukázkovými daty je to vzorec v Excelu 365:

=INDEX(A1:A5, SHODA({“jablko“,”banán”,”třešeň”}, A1:A5; 0))

Výše uvedený příklad se rozpadá takto:

• INDEX: vrací hodnotu buňky v určeném rozsahu na základě daného čísla řádku a sloupce. V tomto případě vrátí hodnotu z rozsahu A1:A5.

• A1:A5: Toto je definovaný rozsah tabulky, kde hledáte hodnotu a ze kterého bude vrácen výsledek.

• MATCH: vyhledá zadanou položku v rozsahu buněk a vrátí relativní pozici této položky v rozsahu.

• {“apple”,”banana”,”třešeň”}: toto je konstanta pole obsahující hodnoty, které chcete vyhledat.

• A1:A5: toto je rozsah, kde MATCH bude hledat hodnoty z konstanty pole.

• 0: toto je typ shody pro funkci MATCH. V tomto případě je to 0, což znamená, že hledáte přesnou shodu namísto těsné shody.

 Tento obrázek ukazuje vzorec v akci:

Práce se staršími verzemi Excelu

Pokud používáte starší soubor Excelu, který nepodporuje dynamická pole (např. Excel 2019 nebo starší), budete muset použít jiný přístup.

Protože starší verze nepodporují „rozlévání“ vzorců do sousedních buněk, budete muset použití rozdělit do tří samostatných vzorců.

Následuj tyto kroky:

1. Klikněte na buňku, kde chcete získat výsledek pro první položku (např. buňka B1)

2. Zadejte níže uvedený vzorec:

3. =INDEX(A1:A5; SHODA(“jablko“; A1:A5; 0))

4. Stisknutím klávesy Enter spusťte vzorec.

5. Do buňky B2 zadejte tento vzorec: =INDEX(A1:A5, MATCH(“banán”, A1:A5, 0))

6. Do buňky B3 zadejte tento vzorec: =INDEX(A1:A5, MATCH(“třešeň”, A1:A5, 0))

Tento obrázek ukazuje odkaz na buňku B3:

Funkce INDEX a MATCH nejsou jediné, které vám umožňují najít více hodnot. V další části se podíváme na to, jak můžete použít funkci KDYŽ jako alternativu.

Jak používat funkci IF k nalezení více hodnot

Dalším způsobem, jak vyhledat více hodnot buněk na základě určitých kritérií, je použití funkce IF s jinými funkcemi.

Funkce IF umožňuje testovat více podmínek a vracet různé výsledky v závislosti na výsledku těchto testů.

Řekněme například, že máte tabulku prodejních dat se sloupci pro Produkt a Prodej. Chcete vyhledat a sečíst částku prodeje za dva ze tří produktů.

Aktuální verze Excelu

Chcete-li najít součet ve sloupci Prodej, kde je produktem buď „Jablko“ nebo „Banán“, pomocí funkce IF, můžete použít maticový vzorec s funkcemi IF, SUM a OR.

Za předpokladu, že vaše data začínají v buňce A1, použijte následující vzorec:

=SUM(KDYŽ((A2:A4=”Jablko”)+(A2:A4=”Banán”), B2:B4; 0))

Sekce (A2:A4=”Jablko”)+(A2:A4=”Banán”) vytvoří pole, které má hodnotu 1, pokud buňka v rozsahu A2:A4 obsahuje “Jablko” nebo “Banán”, a v opačném případě 0 .

Příkaz IF kontroluje každý prvek argumentu pole. Pokud je hodnota 1 (tj. produkt je buď „Jablko“ nebo „Banán“), má odpovídající hodnotu ve sloupci Prodej (rozsah B2:B4); jinak to trvá 0.

Funkce SUM sčítá hodnoty z funkce KDYŽ a efektivně sečte hodnoty prodeje pro „Jablko“ i „Banán“.

Tento obrázek ukazuje vzorec v akci ve vyhledávacím rozsahu:

Starší verze Excelu

V Excelu 2019 nebo starším musíte použít maticový vzorec. Následuj tyto kroky:

1. Zadejte vzorec, ale nemačkejte Enter.

2. Stisknutím Ctrl + Shift + Enter vytvoříte maticový vzorec.

Excel přidá kolem vzorce složené závorky {}, což znamená, že se jedná o maticový vzorec.

Dále se podíváme na to, jak byste mohli použít SUMPRODUCT k vyhledání několika hodnot na základě vašich kritérií. Pojďme!

Jak používat SUMPRODUCT pro více kritérií

Funkce SUMPRODUCT také umožňuje vyhledávat více hodnot na základě více kritérií.

Protože nevyžaduje použití maticového vzorce, syntaxe je stejná bez ohledu na verzi aplikace Excel.

S použitím stejných dat jako v předchozím příkladu vypadá vzorec takto:

=SOUČETNÝ PRODUKT((A2:A4=”Jablko”)+(A2:A4=”Banán”), B2:B4)

Sekce (A2:A4=”Jablko”)+(A2:A4=”Banán”) vytvoří pole, které má hodnotu 1, pokud buňka v rozsahu A2:A4 obsahuje “Jablko” nebo “Banán”, a v opačném případě 0 .

Funkce SUMPRODUCT vynásobí prvky pole odpovídajícími prvky ve sloupci Prodej (rozsah B2:B4). Poté sečte výsledné hodnoty a efektivně sečte hodnoty prodeje pro „Jablko“ i „Banán“.

Níže uvedený vzorec to ukazuje v akci:

Funkce Excelu jsou úžasné, když fungují podle očekávání, ale někdy můžete narazit na chyby. V další části se podíváme na některé běžné chyby a na to, jak se s nimi vypořádat.

3 Běžné chyby s vyhledávacími funkcemi

Funkce vyhledávání mohou někdy vracet chyby, jejichž odstraňování může být frustrující a časově náročné. Tři nejčastější chyby, se kterými se setkáte, jsou:

1. #N/A Chyby

2. #REF! Chyby

3. Kruhové chyby

1. #N/A Chyby

Chyba #N/A nastane, když vyhledávací hodnotu nelze najít ve vyhledávacím poli.

K této chybě může dojít z několika důvodů, včetně:

• vyhledávací hodnota je nesprávně napsaná nebo nesprávná.

• vyhledávací pole není seřazeny vzestupně.

• vyhledávací hodnota není v sadě dat.

Když vyhledávací hodnota není v sadě dat, je to užitečná informace. Nezkušení uživatelé Excelu si však mohou myslet, že #N/A znamená, že se ve vzorci něco pokazilo. Následující část ukazuje, jak to udělat uživatelsky přívětivější.

2. #REF! Chyby

#REF! dojde k chybě , když je vyhledávací pole nebo pole návratu odstraněno nebo přesunuto.

Tuto chybu lze opravit aktualizací odkazů na buňky ve vyhledávací funkci.

3. Kruhové chyby

Když kombinujete funkce ve složitých vzorcích, Excel vám může říct, že máte kruhový odkaz.

Snáze je prozkoumáte pomocí našeho průvodce .

Jak používat IFERROR s vyhledávacími funkcemi

Funkce IFERROR je užitečný nástroj pro řešení chyb ve vyhledávacích funkcích. Umožňuje zadat hodnotu nebo vzorec, který se má vrátit, pokud vyhledávací funkce vrátí chybu.

Syntaxe funkce IFERROR je následující:

=IFERROR(hodnota, hodnota_pokud_chyba)

· hodnota: hodnota nebo vzorec, který chcete vyhodnotit.

· Value_if_error: hodnota nebo vzorec, který se má vrátit, pokud první argument vrátí chybu.

Řekněme například, že máte funkci SVYHLEDAT, která vyhledává více hodnot v tabulce. Na obrázku níže jedna z hodnot v hledaném datovém rozsahu neexistuje.

Jak vidíte, zobrazí se chyba #N/A, což může být pro nezkušené uživatele Excelu matoucí.

Místo toho můžete použít IFERROR k zobrazení prázdné buňky nebo zprávy, která říká „Nenalezeno“ s touto syntaxí:

=IFERROR(SVYHLEDAT(vyhledávací_hodnota, pole_tabulky, index_sloupce, NEPRAVDA), “Nenalezeno”)

V tomto příkladu, pokud funkce VLOOKUP vrátí chybu, funkce IFERROR místo toho vrátí zprávu „Nenalezeno“.

Tento obrázek ukazuje vzorec v akci. Sloupec B má chybějící hodnotu, zatímco ve sloupci C a D byly nalezeny shody.

Dosud jsme prošli hodně věcí a vy jste konečně připraveni naučit se pokročilejší techniky vyhledávání, což je téma další části.

7 Pokročilé techniky vyhledávání

Vyhledání více hodnot v Excelu může být náročný úkol, zejména při práci s velkými datovými sadami. Při pomalém zpracování můžete narazit na problémy s výkonem.

Existuje sedm pokročilých vyhledávacích technik, které můžete použít ke zjednodušení a zefektivnění procesu.

• Vyhledávání relativní polohy

• Funkce SMALL

• Funkce ROW

• Funkce FILTR

• Pomocné sloupce

• Dynamická pole

• Power Query

1. Vyhledávání relativní pozice

Jedním z nejjednodušších je použití vyhledávání relativní polohy. To zahrnuje určení posunů řádků a sloupců z dané buňky k nalezení požadované hodnoty.

Pokud máte například tabulku dat a chcete vyhledat hodnotu o dva řádky dolů a tři sloupce napravo od dané buňky, můžete použít následující vzorec:

=POSUN(buňka; 2; 3)

2. Funkce MALÉ

Další užitečnou technikou pro vyhledávání více hodnot je použití funkce SMALL. Tato funkce vrací n-tou nejmenší hodnotu v rozsahu buněk.

Kombinací této funkce s dalšími vyhledávacími funkcemi, jako je INDEX a MATCH, můžete vyhledávat více hodnot na základě specifických kritérií.

Například následující vzorec vyhledá druhou nejmenší hodnotu v rozsahu buněk, které splňují určitou podmínku:

=INDEX(data, MATCH(SMALL(IF(kritéria, rozsah); 2); rozsah; 0))

3. Funkce ROW

Funkci ROW lze také použít pro vyhledávání více hodnot v Excelu. Tato funkce vrací číslo řádku dané buňky, které lze použít k odkazování na buňky v tabulce dat.

Například následující vzorec vyhledá hodnotu v tabulce na základě jedinečného identifikátoru:

=INDEX(data, MATCH(jedinečný_identifikátor, data[jedinečný_identifikátor], 0), číslo_indexu_sloupce)

4. Funkce FILTR

Funkce FILTR není ve starších verzích Excelu dostupná.

V Excelu 365 jej můžete použít k filtrování rozsahu buněk na základě určitých kritérií a vrátit pouze hodnoty, které tato kritéria splňují. Toto je syntaxe a tři argumenty:

=FILTER(pole, zahrnout, [pokud_prázdný])

• pole: Konkrétní data, která chcete filtrovat.

• include: Argument include jsou kritéria nebo podmínky, které chcete na pole použít.

• [if_empty] (volitelné): Hodnota, která se má vrátit, pokud žádné řádky nebo sloupce nesplňují kritéria zadaná v argumentu include.

Například následující vzorec pracuje s ukázkovými daty a hledá shody pro dvě ze tří položek v prvním sloupci a sečte jejich odpovídající hodnoty ve druhém sloupci.

=SUM(FILTR(B2:B4, (A2:A4=”Jablko”)+(A2:A4=”Banán”)))

Tento obrázek ukazuje, kolik řádků se shoduje a součet různých hodnot:

5. Pomocné sloupce

Pomocí pomocného sloupce můžete spojit více polí dohromady v rámci funkce, jako je SVYHLEDAT.

Předpokládejme, že pracujete se jménem a příjmením na samostatném listu. Můžete je zřetězit v pomocném sloupci, na který se odkazuje v konečném vzorci.

6. Dynamická pole

Jak jste se dozvěděli v předchozích příkladech, uživatelé Microsoft 365 mohou využít výhod dynamických polí pro vyhledávání více hodnot v Excelu.

Dynamická pole umožňují vracet více hodnot z jednoho vzorce, což usnadňuje vyhledávání velkého množství dat.

7. Power Query

Power Query je výkonný nástroj v Excelu, který lze použít k vrácení hodnot na základě více kritérií.

Toto video například najde chaotická data v tabulce a vyčistí je.

Závěrečné myšlenky

To uzavírá náš hluboký ponor do umění vyhledávání více hodnot v Excelu! Jakmile si osvojíte vzorce VLOOKUP, INDEX, MATCH a pole, zjistíte, že procházíte složitými datovými sadami jako horký nůž máslem.

Klíčem je pochopit syntaxi a logiku každého vzorce. Mějte na paměti, že tyto vzorce mohou být složité, takže je důležité nespěchat a důkladně je otestovat, než se na ně budete spoléhat při analýze důležitých dat.

Excel je mocný nástroj, ale je jen tak dobrý jako uživatel. Takže nepřestávejte tyto dovednosti zdokonalovat, neustále experimentujte a brzy budete mistry ve vyhledávání více hodnot. Až do příště pracujte s těmito čísly a nechte Excel dělat těžkou práci za vás!