Vyhledávání v Excelu je jedním z nejčastějších úkolů, které dříve nebo později každý uživatel Excelu potřebuje. K vyhledávání v Excelu se nejčastěji používají vyhledávací funkce jako jsou SVYHLEDAT, INDEX & POZVYHLEDAT nebo funkce XLOOKUP. V dnešním videu si ukážeme, jak v Excelu vyhledávat do kříže, tedy jak vyhledávat podle řádků a zároveň podle sloupců, abyste našli hodnotu, kde se řádek a sloupec protínají. A aby toho nebylo málo, tak si řešení ukážeme rovnou u všech vyhledávacích funkcí.
Excelový soubor ke stažení
Vyhledávání v Excelu do kříže | Vyhledávání podle řádku a sloupce
V následujícím příkladu máme tabulku, kde na řádcích máme produkty a ve sloupcích máme měsíce. Vedle tabulky máme uvedený produkt a měsíc ve formátu rozbalovacího seznamu. Vedle do buňky chceme, aby se přiřadila příslušná tržba z tabulky a to nejen podle zvoleného produktu, ale i měsíce. Musíme tedy v tabulce vyhledávat nejen podle řádku, kde máme produkty, ale i podle sloupce s měsíci a najít hodnotu, kde se řádek a sloupec protíná. Řešení příkladu si ukážeme pomocí funkce SVYHLEDAT, kombinace funkcí INDEX & POZVYHLEDAT a i pomocí nové funkce XLOOKUP.
Jak vyhledávat do kříže – Funkce INDEX & POZVYHLEDAT
Začneme vyhledávací kombinací funkcí INDEX & POZVYHLEDAT, anglicky kombinace funkcí INDEX & MATCH. Funkce POZVYHLEDAT funguje tak, že najde hledanou hodnotu a přiřadí ji pořadové číslo sloupce nebo řádku, funkce INDEX následně podle tohoto čísla přiřadí hledanou hodnotu.
= INDEX(B4:G14; POZVYHLEDAT(I4;A4:A14;0); POZVYHLEDAT(J4;B3:G3;0))
V buňce K4 začneme nejprve funkcí INDEX. Ve funkci INDEX se nejprve označuje oblast, kde se nacházejí naše odpovědi. Naše odpovědi se v tomto případě nacházejí v celé tabulce s hodnotami, takže označíme všechny hodnoty (B4:G14).
V parametru řádek použijeme funkci POZVYHLEDAT/MATCH. Ve funkci POZVYHLEDAT se nejprve označuje co hledáme. Jelikož jsme v parametru řádek u funkce INDEX, tak začneme s hodnotami na řádcích, což jsou produkty. Hledáme produkt, který je výsledkem rozbalovacího seznamu, takže označíme produkt v buňce I4.
Následuje sloupec, kde se vyskytuje kritérium, tedy sloupec s produkty (A4:A14). Posledním parametrem je shoda, my hledáme přesnou shodu, takže vyplníme nulu. Ukončíme závorku u funkce POZVYHLEDAT. Funkce POZVYHLEDAT teď vrátí pořadové číslo řádku, na kterém se vyskytuje hledaný produkt. Můžeme si to ověřit tak, že v příkazovém řádku označíme funkci POZVYHLEDAT a stiskneme klávesu F9, čímž se funkce POZVYHLEDAT změní ze vzorce na výsledek. Pokud bychom zmáčkli klávesu F9 u Produktu H, tak by se funkce POZVYHLEDAT změnila na číslo 8, což znamená, že se hledaný produkt nachází na osmém řádku v tabulce. Pokud ověřujete výsledek funkce klávesou F9, tak se nezapomeňte vrátit z tohoto zobrazení zase zpět k funkci stisknutím kláves CTRL+Z.
Pokračujeme ve funkci. Napíšeme středník, čímž se přepneme ve funkci INDEX do parametru sloupce. V parametru sloupce musíme stejně dynamicky vyjádřit pořadové číslo sloupce, ve kterém se nachází hledaný měsíc, takže i zde použijeme funkci POZVYHLEDAT. V této funkci označíme jako co hledaný měsíc (J4).
Kde tento měsíc hledáme? Hledáme ho v řádku záhlaví s měsíci, takže označíme celý řádek, ve kterém se tento měsíc nachází (B3:G3), a opět hledáme přesnou shodu. Ukončíme závorky u funkcí. Pokud si chceme správnost výpočtu ověřit, tak označíme druhou funkci POZVYHLEDAT a zmáčkneme klávesu F9. U května vrátí funkce číslo pět, tedy pátý sloupec. Opět se vrátíme k funkci stisknutím klávesy F9.
Funkci potvrdíme a vrátilo se číslo 101 Kč, což odpovídá hledanému číslu v tabulce.
Jak vyhledávat do kříže – Funkce SVYHLEDAT
K vyhledávání do kříže můžeme použít i oblíbenou vyhledávací funkci SVYHLEDAT, anglicky VLOOKUP. U této funkce musíme vyřešit to, jak v parametru sloupec dynamicky vyjádřit pořadové číslo sloupce tak, abychom ho do vzorce nemuseli natvrdo zadávat.
=SVYHLEDAT(I4;A3:G14;POZVYHLEDAT(J4;A3:G3;0);0)
V buňce K4 tedy začneme s funkcí SVYHLEDAT/VLOOKUP. V parametru co označíme hledaný produkt (I4). V druhém parametru následuje tabulka, kde označíme tabulku s hodnotami, a to včetně řádků s produkty a záhlaví s měsíci (A3:G14).
Následuje pořadové číslo sloupce. Při standardním vyhledávání do tohoto parametru natvrdo napíšeme číslo sloupce, ve kterém hledáme naše odpovědi. Nicméně zde se toto číslo musí měnit, a to v závislosti na tom, který měsíc vybereme v rozbalovacím seznamu. Která funkce vrací pořadové číslo hledané hodnoty? Správně, i zde nám poslouží funkce POZVYHLEDAT. Místo čísla sloupce tedy napíšeme funkci POZVYHLEDAT, kde jako co hledáme označíme hledaný měsíc (J4). Kde tento měsíc hledáme? Hledáme ho v řádku záhlaví s měsíci (A3:G3) a hledáme přesnou shodu.
Ukončíme závorku u funkce POZVYHLEDAT a vyplníme ještě poslední parametr ve funkci SVYHLEDAT, tedy jakou shodu hledáme. Hledáme přesnou shodu, takže vyplníme nulu. Funkci potvrdíme a i funkce SVYHLEDAT v kombinaci s funkcí POZVYHLEDAT vrátila správný výsledek.
Jak vyhledávat do kříže – Funkce XLOOKUP
Příklad s vyhledáváním do kříže lze vyřešit i pomocí vyhledávací funkce v Excelu, funkce XLOOKUP, která je dostupná ve verzích Excelu od verze 2016. Jedná se o vylepšenou verzi funkce SVYHLEDAT, která je mnohem flexibilnější a vyhledává mimo jiné i doleva od hledaných hodnot.
= XLOOKUP (I4;A4:A14;XLOOKUP(J4;B3:G3;B4:G14))
Do buňky K4 napíšeme funkci XLOOKUP, kde jako co hledáme vyplníme opět hledaný produkt (I4). Následuje, kde tento parametr hledáme. Hledáme produkt, takže ho hledáme ve sloupci s produkty (A4:A14). Stejně jako u funkce POZVYHLEDAT se i ve funkci XLOOKUP označuje pouze sloupec a nikoliv celá tabulka, jako u funkce SVYHLEDAT.
Následuje parametr vrátit. Pokud bychom jednoduše vyhledávali podle jednoho kritéria, tak bychom zde v tomto parametru označili hodnoty, které chceme vrátit. Nicméně, když máme kritéria dvě, tak přesně sem na toto místo napíšeme druhou funkci XLOOKUP.
Kde jako co hledáme tentokrát označíme hledaný měsíc (J4), následuje řádek, kde tento měsíc hledáme, tedy řádek se záhlavím tabulky (B3:G3). A opět se přepneme do parametru vrátit u druhé funkce XLOOKUP.
Do tohoto druhého parametru vrátit označíme hodnoty, ve kterých se vyskytuje naše odpověď. Tedy celá tabulka s hodnotami (B4:G14). Ukončíme závorky a funkci potvrdíme. Dvojitá kombinace funkcí XLOOKUP vrátila stejný výsledek jako předchozí dvě vyhledávací funkce.
Tak toto byly tři možnosti s třemi vyhledávacími funkcemi, které můžete v Excelu použít, pokud potřebujete vyhledávat do kříže. Pro členy Akademie Excelu máme u nás na webu ještě navazující video, Jak vyhledávat v tabulce třemi směry, tedy pokud máte hledat pomocí třech kritérií.
