V dnešním videu si ukážeme, jak v Excelu vyhledávat ve více zdrojových tabulkách. Jedná se o klasický případ v Excelu, kdy do tabulky musíte dohledat podle položek hodnoty, ale přitom máte několik zdrojových tabulek a nevíte, ve které tabulce je vaše správná odpověď. Ukážeme si dnes několik příkladů. Začneme klasickým způsobem, což je pomocí funkce SVYHLEDAT a IFERROR, tento způsob je ovšem v mnoha praktických příkladech nepoužitelný. Proto si dnes ve druhé polovině videa ukážeme i mnohem flexibilnější způsoby.
Excelový soubor ke stažení
Jak vyhledávat ve více tabulkách v Excelu
V dnešním Excelu máme tři zdrojové tabulky s produkty. Do koncové tabulky chceme k jednotlivým produktům doplnit jejich ceny. Problémem je, že nevíme, v jaké tabulce máme produkt hledat. Zkusíme to dnes vyřešit několika různými způsoby.
Jak vyhledávat ve více tabulkách – funkce SVYHLEDAT / VLOOKUP a IFERROR
Začneme tím nejjednodušším a nejklasičtějším způsobem. A tím je vyhledávání pomocí několika funkcí SVYHLEDAT a funkcí IFERROR. V buňce B4 začneme první funkcí SVYHLEDAT, anglicky funkce VLOOKUP, kde jako co hledáme označíme první produkt v tabulce (A4), následuje parametr tabulka, což je první zdrojová tabulka ($G$4:$H$8). Nezapomeneme buňky zafixovat klávesou F4. Cenu hledáme ve druhém sloupci zdrojové tabulky a hledáme přesnou shodu. Funkci potvrdíme a pošleme dolů pro všechny buňky.
Vidíme, že produkty, které jsou v první tabulce se doplnily, u ostatních položek se objevila chybová hláška. Funkci SVYHLEDAT proto zabalíme do funkce IFERROR, kde specifikujeme, co se má stát, pokud první funkce SVYHLEDAT produkt nenajde. Zabalíme proto funkci do IFERROR, kde hodnotou je celá funkce SVYHLEDAT, následuje parametr hodnota v případě chyby. A zde napíšeme druhou funkci SVYHLEDAT. Jako co hledáme označíme opět první produkt (A4), jako tabulku označíme tentokrát druhou zdrojovou tabulku ($G$11:$H$15), nezapomene buňky zafixovat, opět hledáme cenu ve druhém sloupci a hledáme přesnou shodu. Ukončíme závorky a funkci potvrdíme a pošleme pro všechny řádky dolů. Teď už se doplnily i hodnoty z druhé tabulky.
Zbývá doplnit třetí zdrojovou tabulku. Celou tuto funkci bychom tedy zabalili ještě do jedné funkce IFERROR. Kde parametr hodnota bude celá tato funkce a hodnota v případě chyby bude poslední funkce SVYHLEDAT. Kde hledaná hodnota je první buňka v seznamu (A4), tabulka je třetí tabulka ($G$18:$H$23), plně zafixovaná, cenu hledáme ve druhém sloupci zdrojové tabulky a hledáme přesnou shodu. Ukončíme závorky, potvrdíme funkci a pošleme ji pro všechny řádky dolů. Teď máme správně doplněnou celou tabulku.
Výhodou tohoto přístupu je, že se jedná o poměrně snadnou metodu, problém by byl, pokud byste měli spoustu zdrojových tabulek. Mohli byste skončit s desítkou kombinovaných funkcí.
Alternativně byste v tomto případě mohli použít i kombinace funkcí INDEX a POZVYHLEDAT a IFERROR.
=IFERROR(IFERROR(INDEX($H$4:$H$8;POZVYHLEDAT(A4;$G$4:$G$8;0));INDEX($H$11:$H$15;POZVYHLEDAT(A4;$G$11:$G$15;0)));
INDEX($H$18:$H$23;POZVYHLEDAT(A4;$G$18:$G$23;0)))
Vyhledání zdrojové tabulky
Abychom mohli k vyřešení úkolu použít jiné funkce, tak by se nám v tabulce hodilo vědět, z jaké zdrojové tabulky produkt pochází. To znamená, že bychom zde potřebovali ke každému produktu přiřadit v jaké zdrojové tabulce se nachází. Pokusíme se tedy přiřadit správný název zdrojové tabulky ke každému produktu.
Začneme podobně jako v minulém příkladu, tedy funkcí SVYHLEDAT. Do buňky C4 napíšeme funkci SVYHLEDAT, kde jako co hledáme označíme první produkt v tabulce (A4). Tento produkt hledáme v první zdrojové tabulce, ale nehledáme ho v celé tabulce, ale jen v prvním sloupci. Označíme buňky prvního sloupce a buňky zafixujeme ($G$4:$G$8). Naše odpověď se nachází v prvním sloupci, takže napíšeme jedničku a hledáme přesnou shodu. Funkci potvrdíme a pošleme ji cvičně dolů. V případě, že funkce SVYHLEDAT produkty v první tabulce našla, tak vrátila název produktu. My ovšem nepotřebujeme vrátit název produktu ale potřebujeme vrátit název tabulky.
Celou funkci SVYHLEDAT tak můžeme zabalit do podmínky KDYŽ, funkce IF. Než to ovšem uděláme, tak ještě potřebujeme vyřešit to, co bude vlastně podmínka ve funkci KDYŽ. Nejjednodušší by bylo funkci KDYŽ říct, že pokud výsledkem funkce SVYHLEDAT bude text, tak že chceme vrátit název tabulky, v opačném případě chceme, aby funkce dál hledala. Naštěstí pro nás existuje funkce JE.TEXT, v angličtině funkce ISTEXT. Funkci SVYHLEDAT tedy zabalíme do funkce JE.TEXT. Funkci potvrdíme a pošleme ji dolu pro všechny buňky. Na řádcích kde byl název produktu teď máme pravdu a na ostatních řádcích nepravdu.
Teď to zabalíme do funkce KDYŽ. Podmínka bude, pokud je výsledkem funkce JE.TEXT pravda, tak chceme vrátit název tabulky, který máme v záhlaví, nezapomeneme název plně zafixovat. A pokud podmínka splněná nebude, tak chceme hledat v další tabulce. Už víme, že bude jako první funkce KDYŽ, ve které bude funkce JE.TEXT, ve které bude funkce SVYHLEDAT, kde označíme jako co hledáme první produkt, jako tabulku označíme druhou tabulku, kterou plně zafixujeme, hledáme opět v prvním sloupci a hledáme přesnou shodu. Ukončíme závorku u funkce SVYHLEDAT a přepneme se do parametru ano u funkce KDYŽ. Pokud funkce produkt najde v druhé tabulce, tak chceme vrátit název druhé tabulky. Pokud ani jedna podmínka splněná nebude, tak se odpověď musí nacházet ve třetí tabulce, takže do parametru ne stačí vyplnit název třetí tabulky. Nezapomeneme názvy tabulek plně zafixovat. Funkci potvrdíme a pošleme dolů. U každého produktu se správně doplnila zdrojová tabulka.
Teď když víme, v jaké tabulce se produkt nachází, můžeme být flexibilnější v tom, jakou funkci k vyhledání použijeme.
Jak vyhledávat ve více tabulkách – funkce NEPŘÍMÝ.ODKAZ / INDIRECT
Nejprve to zkusíme pomocí funkce NEPŘÍMÝ.ODKAZ, v angličtině funkce INDIRECT. Začneme tím, že si jednotlivé tabulky pojmenujeme. Označíme první tabulku s produkty bez záhlaví a do této buňky napíšeme název tabulky. První tabulka je pojmenovaná jako Tabulka A. Jelikož v názvech oblastí nemůžeme použít mezeru, tak jsem pro oddělení použila podtržítko. Napíšeme tedy Tabulka_A a potvrdíme. Rovnou pojmenuje i druhou tabulku, tabulku označíme a pojmenujeme ji jako Tabulka_B. Ještě označíme třetí tabulku a pojmenujeme ji jako Tabulka_C.
Vrátíme se k tabulce a v buňce D4 použijeme funkci NEPŘÍMÝ.ODKAZ. Funkce NEPŘÍMÝ.ODKAZ nám pomůže s doručením správné tabulky, ve které potom vyhledáme příslušný produkt. Napíšeme funkci NEPŘÍMÝ.ODKAZ, kde jako odkaz vyplníme první název tabulky. Pokud bychom funkci potvrdili, tak se uživatelům Office 365 a Excel 2021 vrátí celá tabulka A. Těm z vás, co používáte starší Excely se vrátí chybová hláška hodnota.
Nicméně neznamená to, že byste ve vzorci měli chybu. Znamená to, že se Excel snaží celou tabulku vrátit do jedné buňky, což není technicky možné. Pokud si ovšem vzorec označíte a zmáčknete klávesu F9, tak zjistíte, že funkce vrací správný výsledek. Nezapomeňte se přepnout z tohoto zobrazení zase zpátky k funkci pomocí kláves CTRL+Z.
Pro ty z vás, kteří nepoužíváte Office 365 a Excel 2021 a vrací se vám chybová hláška, tak může být těžší si celý proces za funkcí představit. Celá pointa spočívá v tom, že funkce NEPŘÍMÝ.ODKAZ dostala jako odkaz název Tabulka_A, což je rovněž pojmenovaná oblast v první tabulce. Funkce NEPŘÍMÝ.ODKAZ tak po potvrzení doručí celou tabulku, do které náleží vybraný produkt.
A toho využijeme ve funkci SVYHLEDAT. Funkce SVYHLEDAT totiž jako parametr tabulka potřebuje zdrojovou tabulku, ale tato zdrojová tabulka může být doručená prostřednictvím jiných funkcí. Funkci NEPŘÍMÝ.ODKAZ tak zabalíme do funkce SVYHLEDAT. Co hledáme? Hledáme tento produkt. Kde ho hledáme? Hledáme ho v tabulce, kterou nám vrátí funkce NEPŘÍMÝ.ODKAZ. Hledáme cenu, což je hodnota ve druhém sloupci a hledáme přesnou shodu. Funkci potvrdíme a pošleme dolů. Zkontrolujeme a máme to správně.
Jak vyhledávat ve více tabulkách – funkce ZVOLIT / CHOOSE
Pro vyhledávání ve více tabulkách se vám bude hodit i funkce ZVOLIT, anglicky CHOOSE. Jak už název napovídá, tak funkce ZVOLIT dovoluje podle indexového čísla zvolit hodnotu, která se vrátí. Funkce ZVOLIT ovšem neumí vrátit pouze hodnotu, ale je schopná vrátit i celou tabulku, podobně jako funkce NEPŘÍMÝ.ODKAZ.
Pro ty z vás, kteří s funkcí ZVOLIT nikdy nepracovali, tak funkce ZVOLIT má jako první parametr index, což je číslo. Následují parametry hodnota, kterých může být přes 200. Funkce ZVOLIT tedy funguje tak, že zobrazí hodnotu podle pořadového neboli indexového čísla.
Zkusíme s pomocí funkce ZVOLIT vyřešit následující zadání. Abychom byli ale schopní indexové číslo přiřadit, tak si pomůžeme pomocnou tabulkou. Pomocnou tabulku mám předpřipravenou v buňkách K5:K7 a jedná se v podstatě jen o soupis tabulek.
Začneme tvořit funkci ZVOLIT. Ve funkci ZVOLIT je jako první parametr právě indexové číslo, zde musíme funkci doručit číslo 1 až 3. K tomu poslouží pomocná tabulka, použijeme funkci POZVYHLEDAT, kde jako co hledáme označíme první název tabulky, kde tento název hledáme, hledáme ho v této pomocné tabulce, nezapomeneme buňky zafixovat a hledáme přesnou shodu. Funkce POZVYHLEDAT doručí pořadové číslo tabulky, což bude naše indexové číslo. A teď už je to snadné, následují pouze hodnoty. Pokud funkce POZVYHLEDAT vrátí jedničku, znamená to první tabulku a tedy tyto hodnoty, pokud vrátí funkce dvojku, znamená to druhou tabulku a tedy tyto hodnoty, jako třetí je pak třetí tabulka a tyto hodnoty. Funkce ZVOLIT je hotová. Stejně jako předtím funkce NEPŘÍMÝ.ODKAZ nám i tato funkce doručí celou tabulku. Teď už stačí použít funkci SVYHLEDAT. Hledáme první produkt, tabulku doručí funkce ZVOLIT, cenu hledáme ve druhém sloupci a hledáme přesnou shodu. Potvrdíme a pošleme pro všechny řádky dolů.
