Jednou z vůbec nejpoužívanějších funkcí Excelu je bezesporu vyhledávací funkce SVYHLEDAT (anglicky VLOOKUP). V sadě funkcí, dostupných nově předplatitelům Office 365, se nachází i její vylepšená verze s názvem XLOOKUP. Vylepšené verze se dočkala i funkce POZVYHLEDAT (anglicky MATCH) – nazývá se XMATCH.
NahoruZákladní provedení
Ukažme si funkci XLOOKUP nejdříve v nejzákladnějším provedení. V oblasti A1:D5 máme připravenou tabulku s cenami výrobků a chceme k názvu výrobku uvedeného v buňce F2 (aktuálně „ocel“) dohledat jeho cenu.
Pokud bychom chtěli použít funkci SVYHLEDAT, tak by příslušný vzorec měl následující podobu:
=SVYHLEDAT(F2;B2:D5;3;NEPRAVDA)
Pokud znáte funkci SVYHLEDAT, tak vám budou parametry funkce XLOOKUP velmi povědomé, ale je potřeba respektovat, že se jejich zadání očekává přece jen v trochu jiné (a troufnu si říci, že pohodlnější) formě.
Začneme zapisovat funkci XLOOKUP. Jak vidíme, tak má hned šest parametrů, ale většinou si vystačíme s prvními třemi parametry. Další tři parametry jsou nepovinné.
První parametr funkce XLOOKUP s názvem co má stejný význam jako první parametr u funkce SVYHLEDAT – označíme tedy buňku F2, ve které se nachází název hledaného materiálu. Zatímco druhým parametrem funkce SVYHLEDAT musí být vždy celá tabulka od prohledávaného sloupce až minimálně po sloupec, jehož hodnoty chceme zobrazit (s následným určením čísla tohoto sloupce třetím parametrem), tak u funkce XLOOKUP se druhým parametrem s názvem prohledat určí pouze sloupec, který se bude prohledávat, a třetím parametrem s názvem vrátit pak sloupec, jehož hodnoty chceme zobrazit. V našem případě tedy jako druhý parametr označíme sloupec s názvy materiálů a jako třetí parametr sloupec s cenami.
Po potvrzení se zobrazí očekávaná cena oceli 540 Kč.
Zvláště u rozsáhlých tabulek s mnoha sloupci je zadání druhého sloupce jeho přímým označením mnohem pohodlnější a přímočařejší než odpočítávání čísla sloupce nutné ve funkci SVYHLEDAT.
Stejně jako funkce SVYHLEDAT podporuje i funkce XLOOKUP vyhledávaní v celých sloupcích.
Velkou výhodou funkce XLOOKUP je možnost zobrazení údajů i ze sloupců, které se nacházejí vlevo od prohledávaného sloupce, což funkce SVYHLEDAT neumí. Nevyžaduje to žádný speciální postup. Prostě jen jako třetí parametr označíme sloupec, který se nachází vlevo od sloupce, který jsme použili jako druhý parametr. Na dalším screenu hledáme k názvu výrobku jeho evidenční číslo.
Vraťme se ale ještě k prvnímu způsobu označení sloupců tabulky, kdy jsme neoznačovali celé sloupce listu, ale jen části původního seznamu. Při tomto způsobu by mohlo dojít ke dvěma chybám. Pokud bychom ve druhém a třetím parametru označili jiný počet buněk, tak by funkce vůbec nefungovala a vrátila by chybu #HODNOTA!
Pokud by obě oblasti měli stejný počet buněk, ale byly vůči sobě posunuté, tak by funkce výsledek zobrazila, ale ten by byl také posunutý (v našem případě by v první oblasti našla ocel na druhé pozici, a tak by vrátila druhou hodnotu z druhé oblasti, tedy cenu 300 Kč).
NahoruDalší možnosti použití
Pokud funkce XLOOKUP nenajde hledanou hodnotou, tak se standardně zachová stejně jako funkce SVYHLEDAT – tedy vrátí chybu #NENÍ_K_DISPOZICI!
U funkce XLOOKUP se ale můžeme vyhnout obvyklému zachytávání chyb např. pomocí funkce IFERROR, protože funkce XLOOKUP umí chybovou hodnotou nahradit uživatelem zadanou hodnotou sama, a to prostřednictvím čtvrtého parametru s názvem pokud_nenalezeno. Jednoduše do něj zapíšeme text či číslo, který se má objevit v případě, že funkce v zadané oblasti hledaný údaj nenajde. Funkce pak bude mít např. následující podobu:
=XLOOKUP(F2;B2:B5;D3:D6;"nenalezeno")
Další výhodou funkce XLOOKUP je možnost vracení údajů z celých řádků původního seznamu. Tak např. budeme chtít k evidenčnímu číslu dohledat najednou materiál, dodavatele i cenu za kus. Pokud bychom využívali funkci SVYHLEDAT, tak bychom museli sestavit tři vzorce lišící se vždy jen v čísle vraceného sloupce. Při použití funkce XLOOKUP stačí jen parametrem vrátit říci, že máme zájem o údaje z více sloupců.
Vzorec vytvořený v jediné buňce se po potvrzení sám jako dynamické…
