V dnešním videu si ukážeme trochu obrácené vyhledávání. Ukážeme si, jak můžeme vyhledávat nadřazenou kategorii podle položky. V příkladu máme zdrojovou tabulku s produkty a tržbami. Vedle máme pomocnou tabulku s produkty a zařazením, do které kategorie produkt spadá. Nicméně tato pomocná tabulka je strukturovaná do sloupců, takže jsou produkty uvedené v řádcích pod sebou, podle toho, do které kategorie patří. Naším úkolem je do zdrojové tabulky dohledat kategorii produktu ze záhlaví.
Excelový soubor ke stažení:
U takto strukturovaných dat je nejlepší pro vyhledání správné kategorie, použít vyhledávací kombinaci INDEX & POZVYHLEDAT.
Nejprve si ukážeme, co ale udělat nemůžeme. Začneme funkcí POZVYHLEDAT, ve které se nejprve označuje, co hledáme, označíme tedy hledaný první produkt. Následuje oblast, kde tento produkt hledáme a zde narazíme na problém, jelikož my nevíme, ve kterém sloupci se produkt nachází a v tomto parametru nemůžeme označit všechny sloupce, kdybychom to udělali, tak se po potvrzení vrátí chyba, jelikož ve funkci POZVYHLEDAT můžeme vždy označit buď jen jeden sloupec nebo jeden řádek.
Pro vyhledání tedy musíme místo funkce POZVYHLEDAT použít trochu jiný postup.
Začneme s podmínkou, že ověříme, v které buňce pomocné tabulky se produkt nachází. Takže jako podmínku ověříme, zda se první produkt v tabulce rovná produktu v pomocné tabulce.
Na výsledek této podmínky se podíváme klávesou F9. Vrátila se série nepravd a jedné pravdy. To, kde se pravda nachází poznáme podle oddělovačů. Lomítko znamená oddělení do sloupců, takže na prvním řádku pomocné tabulky produkt není, následně se středníkem posuneme do druhého řádku a zde máme pravdu jako třetí. Takže víme, že hledaný produkt se nachází na druhém řádku a ve třetím sloupci. A právě informace o třetím sloupci je klíčová.
Vrátíme se nazpět pomocí CTRL+Z.
Teď tedy víme, že se první produkt na řádku nachází ve třetím sloupci.
Abychom pro vyhledání mohli použít funkci INDEX, tak musíme ve funkci INDEX v parametru sloupce vrátit v tomto případě trojku, jelikož se hledaný produkt nachází ve třetím sloupci. K pravdě, která se vrací touto podmínkou tedy musíme přiřadit pořadové číslo sloupce.
To uděláme kombinaci dvou funkcí SLOUPEC. Takže podmínku vynásobíme kombinací funkcí SLOUPEC. V první funkci SLOUPEC označíme celé záhlaví pomocné tabulky a ve druhé funkci SLOUPEC označíme první buňku před záhlavím pomocné tabulky. První funkce SLOUPEC vrátí pořadová čísla sloupců z excelového listu. Druhá funkce SLOUPEC vrátí pořadové číslo sloupce F, a toto pořadové číslo se odečte od pořadových čísel sloupců G, H a I. Takže ve výsledku dostaneme pořadová čísla 1, 2 a 3.
Na výsledek funkce se opět podíváme pomocí klávesy F9. V buňkách, kde byly nepravdy se vrátily nuly a v jediné buňce, kde byla pravda se vrátilo pořadové číslo sloupce.
A teď se musíme zbavit nepotřebných nul a nechat si pouze číslo pořadového číslo sloupce u pravdivé buňky. To uděláme, když to celé zabalíme do funkce SOUČIN.SKALÁRNÍ, neboli funkce SUMPRODUCT.
Po potvrzení funkce SOUČIN.SKALÁRNÍ se vrátí pouze pořadové číslo sloupce s pravdivou buňkou, tedy pořadové číslo sloupce pomocné tabulky, kde se nachází hledaná položka.
A toto číslo můžeme použít jako parametr sloupce ve funkci INDEX. Takže to celé zabalíme do funkce INDEX, kde označíme jako pole záhlaví pomocné tabulky a jako parametr sloupec poslouží funkce SOUČIN.SKALÁRNÍ.
Po potvrzení se vrátí správně dohledaná kategorie produktu ze záhlaví pomocné tabulky.
