Jestli v Excelu pořád používáte funkci SVYHLEDAT neboli funkci VLOOKUP, tak po dnešním videu je dost pravděpodobné, že s ní definitivně skončíte. Protože funkce XLOOKUP není jen modernější náhrada za funkci SVYHLEDAT. XLOOKUP je vyhledávání na úplně jiné úrovni. V tomhle videu vám ukážu pět reálných situací z praxe, kde XLOOKUP zvládne věci, na které ostatní vyhledávací funkce jednoduše nestačí. Nečekejte žádné školní příklady – tohle je Excel z reálného života.
Ukážeme si vyhledávání bez třídění, hledání poslední hodnoty, návrat celého profilu jedním vzorcem, chytré vyhledávání se záložní logikou a na závěr i vyhledávání do kříže. Věci, které buď dřív nešly vůbec, nebo byly zbytečně složité. Po tomhle videu přestanete používat XLOOKUP jen jako „lepší SVYHLEDAT“ a začnete využívat potenciál této funkce naplno.
Excelový soubor ke stažení:
Vyhledávání s podmínkami
Obrovskou výhodou funkce XLOOKUP je to, že dovoluje vyhledávat dle více podmínek bez tvorby pomocných sloupců. V příkladu máme zdrojovou tabulku, ze které potřebujeme dohledat tržby dle zadaných kritérií, a to dle kombinace produktu, typu produktu a pobočky. S funkcí XLOOKUP nemusíme tvořit pomocné sloupce, jelikož funkce XLOOKUP umí v parametru prohledat pracovat s podmínkami. Potřebujeme dohledat tržbu pro vybraný produkt, typ a pobočku, takže hledáme řádek, kde se všechna tři kritéria vyskytují najednou. Všechny podmínky tedy musí být splněné zároveň, takže pracujeme s logickým vyjádřením A. Začneme s funkcí XLOOKUP, ve které nejprve označujeme co hledáme. V případě, že vyhledáváme s více podmínkami, které jsou v logickém vyjádření A, tak hledáme jedničku neboli pravdu. Tedy řádek, na kterém budou všechny podmínky splněné.
V parametru prohledat můžeme zadat podmínky. Každá podmínka musí být v samostatných závorkách. První podmínka je, že se sloupec s produkty bude rovnat vybranému produktu.
A tuto podmínku vynásobíme druhou podmínkou, že se typ produktu bude rovnat vybranému produktu.
Máme ještě třetí podmínku, takže to vynásobíme třetí podmínkou, že se pobočka bude rovnat vybrané pobočce. Toto je celý parametr prohledat. Na každém řádku dojde k ověření podmínky a na řádku, kde budou všechny tři podmínky splněné se vrátí jednička.
A co chceme vrátit? Sloupec s tržbami. Další parametry v tomto případě vyplňovat nemusíme.
Funkci potvrdíme a vrátí se správný výsledek, který funkce XLOOKUP dohledala na základě tří podmínek.
Funkce samozřejmě reaguje na změny, takže při změně podmínek funkce XLOOKUP dohledá správný výsledek.
Vyhledání poslední shody
Další velmi častý problém v Excelu je práce s opakujícími se hodnotami. Máte data, kde se jedno jméno, jeden kód nebo jeden zákazník vyskytuje víckrát a vy potřebujete zjistit aktuální stav, ne ten úplně první, ale naopak ten poslední. Jenže klasické vyhledávání v Excelu vždycky vrací pouze první shodu. Nikoliv však XLOOKUP. Funkce XLOOKUP je jedinou vyhledávací funkcí, která umí vyhledávat od konce.
Z následující tabulky potřebujeme zjistit poslední záznam k vybranému jménu. U každého jména máme vývoj statusu a nás zajímá poslední status. Použijeme funkci XLOOKUP. Nejprve označíme, co hledáme. Hledáme jméno.
Následuje parametr prohledat, což je sloupec se jmény ve zdrojové tabulce, který plně zafixujeme klávesou F4.
A jako parametr vrátit označíme sloupec s našimi odpověďmi, tedy sloupec se statusy, který opět plně zafixujeme klávesou F4.
Abychom vyhledávali od konce, tak musíme vyplnit ale i nepovinný parametr režim vyhledávání, kde vyplníme mínus jedna, tedy vyhledávání od konce.
Potvrdíme funkci, stáhneme ji dolů a máme u každého jména doplněný poslední zaznamenaný status z tabulky a je to proto, že funkce XLOOKUP při svém vyhledávání nepostupovala od shora tabulky, ale od spodu.
Dynamický návrat celého řádku
V dalším příkladu chceme ke kódu zákazníka dohledat nejen jméno firmy, ale i kontaktní osobu, email a telefon. Pokud bychom toto řešili pomocí klasických vyhledávacích funkcí jako je SVYHLEDAT, tak bychom skončili u kopírování vzorce, ve kterém bychom ručně měnily pořadová čísla sloupců nebo odkazy na sloupce. Funkce XLOOKUP vrátí vícero hodnot z jedné buňky.
Napíšeme funkci XLOOKUP, kde označíme co hledáme, což je kód zákazníka. A jako oblast prohledat označíme sloupec s kódy zákazníka.
A co chceme vrátit? Chceme vrátit více než jen jeden sloupec, chceme vrátit informace z jednoho řádku ze čtyř sloupců, takže tuto celou oblast označíme a zafixujeme.
Potvrdíme funkci a funkce XLOOKUP z jedné buňky doplní všechny údaje. Stáhneme funkci dolů a máme z jedné buňky dohledáno vícero výsledků. Samozřejmě, že základním pravidlem je, že spolu tyto sloupce, ze kterých chceme vrátit odpovědi sousedí.
Vyhledávání se záložní logikou
V dalším příkladu využijeme nepovinný parametr pokud nenalezeno v trochu kreativním případě. V příkladu máme tabulku, do které potřebujeme dohledat ceny k produktům. Nicméně ceníky máme dva. V prvním ceníku máme uvedené standardní ceny ke všem produktům dle jejich kódu. Ale pak existuje ještě druhý ceník s individuálními cenami. A tyto individuální ceny jsou určené jen pro vybrané zákazníky u vybraných produktů. Logika je následující. Pokud se kombinace produktu a zákazníka vyskytuje v individuálním ceníku, tak chceme doplnit tuto individuální cenu, v opačném případě chceme dohledat standardní cenu.
V tomto případě musíme začít vyhledávat nejprve od individuálního ceníku, a až když se cena pro kombinaci v individuálním ceníku nenajde, tak začneme vyhledávat ve standardním ceníku. A v individuálním ceníku musíme ověřit kombinaci zákazníka a produktu. Takže napíšeme funkci XLOOKUP, kde hledáme co? Pravdu neboli jedničku, jelikož hledáme podle vícenásobné podmínky.
A v parametru prohledat ověříme dvě podmínky, opět musí být každá podmínka v samostatných závorkách. První podmínka je, že se kód zákazníka bude rovnat sloupci se zákazníky v individuálním ceníku. A druhá podmínka je, že se kód produktu bude rovnat sloupci s kódy produktu v individuálním ceníku.
A co hledáme? Cenu, takže ji označíme. Když tuto funkci ukončíme a pošleme dolů, tak se dohledají všechny individuální ceny a na ostatních řádcích se vrátí chyby, jelikož pro tyto kombinace neexistuje cena v individuálním ceníku. To znamená, že na tyto řádky chceme doplnit ceny ze standardního ceníku. V parametru prohledat označíme sloupec s kódy produktů ze standardního ceníku a jako co hledáme je sloupec s cenou ze standardního ceníku.
A k doplnění těchto cen použijeme parametr pokud nenalezeno. A do parametru pokud nenalezeno napíšeme druhou funkci XLOOKUP. V této funkci už hledáme pouze kód produktu, jelikož ve standardním ceníku vyhledáváme jen podle produktu.
Potvrdíme funkci a pošleme ji dolů a máme všude doplněné ceny, které jsme dohledaly dle vícenásobné podmínky ze dvou různých ceníků.
Vyhledávání do kříže
Věděli jste, že XLOOKUP umí vyhledávat ve dvou směrech? Máte tabulku, kde jsou hodnoty uspořádané do řádků a sloupců. Na řádcích máme kódy produktů a regiony máme ve sloupcích. Z této tabulky potřebujeme najít jednu jedinou hodnotu – podle dvou různých kritérií najednou. Použijeme funkci XLOOKUP, kde jako co nejprve označíme kód produktu. Následuje parametr prohledat, který je v prvním sloupci tabulky.
Když potřebujeme vyhledávat dvěma směry, tak se do parametru vrátit v první funkci XLOOKUP napíše druhá funkce XLOOKUP. A v této druhé funkci budeme hledat vybraný region a budeme ho hledat v záhlaví tabulky, kde máme regiony uvedené. A až teprve v tomto druhém parametru vrátit označíme hodnoty, ze kterých chceme vrátit správnou hodnotu. Což jsou všechny hodnoty v tabulce. Správně zafixujeme buňky a ukončíme obě funkce a funkci pošleme dolů.
Pomocí kombinace dvou funkcí XLOOKUP jsme dohledali správné hodnoty pro kombinaci produktu a regionu, kde jsme vyhledávali dvěma směry.
