Nový Excelový nástroj XLOOKUP přichází jako silná náhrada za VLOOKUP, jednu z nejpoužívanějších funkcí. Přináší s sebou vylepšení, která odstraňují omezení VLOOKUP a navíc nabízí nové možnosti. Pojďme se na to podívat detailněji.
Co vlastně XLOOKUP je?
XLOOKUP je nová funkce, která překonává největší nedostatky funkce VLOOKUP. Navíc nahrazuje i funkci HLOOKUP. XLOOKUP umí vyhledávat i nalevo, standardně používá přesnou shodu a umožňuje vybrat rozsah buněk namísto čísla sloupce. To vše VLOOKUP nedokáže. Podíváme se, jak to funguje v praxi.
V tuto chvíli je XLOOKUP dostupný pouze pro uživatele v programu Insiders. Pokud se chcete zapojit do programu Insiders a získat přístup k nejnovějším funkcím Excelu, můžete se zaregistrovat. Microsoft plánuje brzké zavedení této funkce pro všechny uživatele Office 365.
Jak používat funkci XLOOKUP
Pojďme se podívat na praktický příklad použití funkce XLOOKUP. Představme si, že máme data, kde pro každé ID (sloupec A) chceme zjistit odpovídající oddělení (sloupec F).
Jedná se o typický příklad vyhledávání přesné shody. Pro funkci XLOOKUP jsou potřebné pouze tři základní informace.
Následující obrázek ukazuje XLOOKUP se šesti argumenty, ale pro přesnou shodu stačí jen první tři:
Lookup_value: Hodnota, kterou hledáte.
Lookup_array: Oblast, ve které hledáte.
Return_array: Oblast obsahující hodnotu, kterou chcete získat.
V našem případě použijeme tento vzorec: =XLOOKUP(A2;$E$2:$E$8;$F$2:$F$8)
Nyní se podívejme na hlavní výhody, které XLOOKUP nabízí oproti VLOOKUP.
Odpadá číslo indexu sloupce
Jeden z největších problémů funkce VLOOKUP byl nutnost zadávat číslo sloupce, ze kterého se má vrátit hodnota. XLOOKUP tento problém řeší tím, že umožňuje jednoduše vybrat rozsah (v tomto případě sloupec F).
Důležité je, že XLOOKUP umí zobrazit data i nalevo od vybrané buňky, což VLOOKUP nedokázal. Více o tom níže.
Další výhodou je, že vzorec se automaticky upraví, i když vložíte nové sloupce, což u VLOOKUP způsobovalo problémy.
Přesná shoda je výchozí
U VLOOKUP bylo vždy matoucí, proč je nutné explicitně nastavit přesnou shodu.
XLOOKUP má přesnou shodu jako výchozí nastavení, což je nejčastější důvod použití vyhledávacího vzorce. Tím se minimalizuje nutnost zadávat tento argument a snižuje se pravděpodobnost chyb.
Stručně řečeno, XLOOKUP je intuitivnější, uživatelsky přívětivější a robustnější než VLOOKUP.
XLOOKUP umí vyhledávat i nalevo
Díky možnosti vybrat vyhledávací rozsah je XLOOKUP flexibilnější než VLOOKUP. U XLOOKUP nezáleží na pořadí sloupců v tabulce.
VLOOKUP byl omezen vyhledáváním v nejlevějším sloupci tabulky a následným vrácením hodnoty z daného počtu sloupců doprava.
V následujícím příkladu chceme vyhledat ID (sloupec E) a vrátit jméno osoby (sloupec D).
Použijeme tento vzorec: =XLOOKUP(A2;$E$2:$E$8;$D$2:$D$8)
Co dělat, když se hodnota nenajde
Uživatelé vyhledávacích funkcí jsou zvyklí na chybovou zprávu #N/A, která se objeví, když funkce VLOOKUP nebo MATCH nemohou nalézt požadovanou hodnotu.
Často je nutné tyto chyby skrýt, protože nejsou relevantní ani užitečné. Naštěstí existuje mnoho způsobů, jak toho dosáhnout.
XLOOKUP má vestavěný argument „pokud nenalezeno“, který umožňuje elegantně řešit takovéto situace. Podívejme se na to na příkladu s chybně zadaným ID.
Následující vzorec zobrazí text „Nesprávné ID“ namísto chybové zprávy: =XLOOKUP(A2;$E$2:$E$8;$D$2:$D$8;“Nesprávné ID“).
XLOOKUP pro vyhledávání v rozsazích
I když to není tak běžné jako přesná shoda, efektivní využití vyhledávacích vzorců je hledání hodnot v určitých rozsazích. Podívejme se na následující příklad. Chceme vrátit slevu v závislosti na utracené částce.
Tentokrát nehledáme konkrétní hodnotu. Potřebujeme zjistit, kam spadají hodnoty ve sloupci B v rámci rozsahu ve sloupci E. To určí odpovídající slevu.
XLOOKUP má volitelný pátý argument (připomínám, že jeho výchozí hodnota je přesná shoda) nazvaný režim shody.
XLOOKUP nabízí více možností pro přibližné shody než VLOOKUP.
Můžeme najít nejbližší menší shodu (-1) nebo nejbližší větší shodu (1) než hledaná hodnota. Máme také možnost používat zástupné znaky (2) jako ? nebo *. Tato nastavení nejsou ve výchozím nastavení zapnutá, jak tomu bylo u VLOOKUP.
Vzorec v tomto příkladu vrátí nejbližší menší hodnotu, než je hledaná hodnota, pokud se nenajde přesná shoda: =XLOOKUP(B2;$E$3:$E$7;$F$3:$F$7;;-1)
V buňce C7 je však chyba #N/A (chybí argument ‚if not found‘). Správně by měla být vrácena 0% sleva, protože útrata 64 nesplňuje kritéria pro žádnou slevu.
Další výhodou funkce XLOOKUP je, že nevyžaduje, aby byl vyhledávací rozsah seřazen vzestupně, jako tomu bylo u VLOOKUP.
Přidejte nový řádek na konec vyhledávací tabulky a upravte vzorec. Roztáhněte použitý rozsah pomocí kliknutí a tažení za rohy.
Vzorec okamžitě chybu opraví. Není problém mít „0“ na konci rozsahu.
Osobně bych tabulku seřadil podle vyhledávacího sloupce. Ale to, že vzorec funguje i tak, je skvělé.
XLOOKUP nahrazuje i funkci HLOOKUP
Jak už bylo zmíněno, funkce XLOOKUP nahrazuje i funkci HLOOKUP. Jedna funkce tak nahrazuje dvě.
HLOOKUP je horizontální vyhledávání, které se používá pro hledání v řádcích.
Není tak známá jako VLOOKUP, ale je užitečná v případech, kdy jsou záhlaví ve sloupci A a data v řádcích 4 a 5.
XLOOKUP dokáže vyhledávat oběma směry – dolů ve sloupcích i podél řádků. Už tedy nepotřebujeme dvě rozdílné funkce.
V tomto příkladu použijeme vzorec pro vrácení hodnoty prodeje související s názvem v buňce A2. Vyhledá název v řádku 4 a vrátí hodnotu z řádku 5: =XLOOKUP(A2;B4:E4;B5:E5)
XLOOKUP dokáže vyhledávat zdola nahoru
Často je potřeba v seznamu najít první (často jediný) výskyt hodnoty. XLOOKUP má šestý argument nazvaný režim vyhledávání. Ten umožňuje přepnout vyhledávání, aby začínalo od konce a hledalo tak poslední výskyt hodnoty.
V následujícím příkladu chceme zjistit stav zásob pro každý produkt ve sloupci A.
Vyhledávací tabulka je seřazena podle data a pro každý produkt existuje několik záznamů. Chceme získat stav zásob z posledního záznamu (posledního výskytu ID produktu).
Šestý argument funkce XLOOKUP nabízí čtyři možnosti. V tomto případě nás zajímá možnost „Hledat od posledního k prvnímu“.
Dokončený vzorec vypadá takto: =XLOOKUP(A2;$E$2:$E$9;$F$2:$F$9;;;-1)
V tomto vzorci jsme ignorovali čtvrtý a pátý argument. Jsou volitelné a my jsme chtěli zachovat výchozí nastavení přesné shody.
Závěr
Funkce XLOOKUP je očekávaná náhrada funkcí VLOOKUP a HLOOKUP.
V tomto článku jsme představili různé příklady, které demonstrují výhody XLOOKUP. Jednou z nich je možnost používat XLOOKUP napříč listy, sešity i tabulkami. Příklady byly zjednodušené pro lepší pochopení.
Díky dynamickým polím, která se zavádějí do Excelu, bude funkce XLOOKUP brzy schopna vracet i rozsah hodnot. To je něco, co stojí za další prozkoumání.
Dny VLOOKUP jsou sečteny. XLOOKUP přichází a brzy se stane de facto standardem pro vyhledávací vzorce.