Co potřebujete vědět o XLOOKUP
S XVERWEIS nabízí Microsoft svým uživatelům Excelu novou možnost rychle a snadno prohledávat tabulky a vyhodnocovat data. Tato funkce byla zpočátku k dispozici pouze účastníkům ve fázi testování, ale od začátku roku je k dispozici také pro verze Microsoft 365 pro Windows a Mac.
Ve své podstatě je XLOOKUP pohodlnější verzí VLOOKUP a HLOOKUP, která byla doplněna o další praktické aplikace. Jeho úkolem je tedy také prohledávat tabulky, ale to se již liší od předchozích referencí. Filtrování obsahu již není vázáno na předepsaný kurz, ale nyní může být také zahájeno zprava doleva, shora dolů a naopak. Tato inovace nabízí velkou výhodu, že tabulky aplikace Excel lze nyní navrhovat podle vlastního uvážení a již nejsou vázány na technické specifikace. Díky novým parametrům byly k dispozici další možnosti, které dříve nebyly k dispozici. Užitečnost sahá od malých detailů až po pozoruhodná zjednodušení používání. Konečný účinek XLOOKUP zcela závisí na použitých parametrech.
Jednoduché parametry XLOOKUP
Jednoduché použití XLOOKUP vyžaduje pouze tři parametry. Tyto jsou:
- Kritérium vyhledávání
- Vyhledávací matice
- Návratová matice
Nově získaná svoboda je dána především oddělením matice vyhledávání a návratu. Zatímco VLOOKUP a HLOOKUP stále vyžadovaly, aby uživatel vybral celou matici pro proces vyhledávání, XLOOKUP mu umožňuje oddělit známou a hledanou hodnotu. To znamená, že jeden vyhledávací sloupec nebo nyní také vyhledávací řádek je definován jako požadovaná matice, ve které se nachází vybrané vyhledávací kritérium, zatímco jiný se stane oblastí návratu požadovaného výsledku. Nový vzorec je následující:
= XLOOKUP (vyhledávací kritérium; vyhledávací matice; návratová matice)
Pokud lze například měsíční mzdu pana Wagnera odebrat z excelové tabulky pro zaměstnance, pak jsou důležité dva sloupce: První, který uvádí všechny zaměstnance podle jména a další, do kterého se zapisuje plat zaměstnanců. Protože kritériem vyhledávání je název, přidružený vyhledávací sloupec je vybrán jako matice. Výsledkem, který má být vrácen, je na druhé straně plat, a proto je zde odpovídající sloupec vytvořen jako návratová matice.
Tento příklad ukazuje, že nový vzorec činí předchozí označení konkrétního indexu sloupce v rámci jedné velké matice nadbytečným. To šetří další detailní práci a předchází zbytečným chybám.
Pokud však není známo, zda se hledaný zaměstnanec jmenuje „Wagner“ nebo „Wegner“, může pomoci nový přírůstek do kritéria vyhledávání. Vložením hvězdičky (*) lze vynechat libovolný počet neznámých znaků. V takovém případě má smysl rychle změnit vyhledávací kritérium na „* gner“, abyste se dostali na požadované místo určení. Pokud je však tabulka naplněna mnoha podobnými jmény, takže kolega paní Stegnerová neúmyslně vystupuje, protože její jméno také obsahuje kombinaci písmen, která hledáte, pak lze hledání ještě upřesnit. Zde vstupuje do hry otazník (?), Protože umožňuje uživateli nahradit pouze jeden znak. Kritérium vyhledávání je tedy vyplněno „W? Gner“.
Co se ale stane, když budete muset hledat hvězdičku nebo otazník? V tomto případě má XLOOKUP další vlnovku (~), se kterou je zřejmé, že není míněna funkce vyhledávacího kritéria, ale obsah vyhledávací matice. V důsledku toho dvojitá vlnovka jako vyhledávací kritérium (~~) také umožňuje hledání jediné vlnovky ve vyhledávací matici (~).
Kompletní parametry
XVERWEIS navíc nabízí další funkce, které vstupují do hry, jakmile jsou podle potřeby použity tyto tři další parametry:
- If_not_ found
- Srovnávací režim
- Režim vyhledávání
"If_not_ found"
Kromě kritérií pro vyhledávání tří parametrů, vyhledávací matice a návratové matice má nový XVERWEIS další tři parametry, které nabízejí uživateli mnoho výhod. Jedním z nich je „If_not_ found“, který funguje jako integrovaná funkce if-error.
Pomocí této funkce umožňuje XLOOKUP vyhnout se běžnému problému s předchozími referencemi: Pokud nebyl nalezen hledaný výsledek, byla dosud zobrazena pouze kryptická chybová hodnota („#NV“). Díky novému parametru je nyní možné pojmenovat tuto chybu a tím ji snadněji klasifikovat nahrazením parametru držení místa slovem podle vašeho výběru a umístěným v uvozovkách. Místo hodnoty automatické chyby může Excel indikovat, že výsledek „nebyl nalezen“ nebo že došlo k „chybě při zadávání“. Když vezmeme v úvahu všechny informace, vzorec pro XVERWEIS vypadá takto:
= XLOOKUP (vyhledávací kritérium; vyhledávací matice; návratová matice; pokud_ nenalezeno)
Srovnávací režim
Dalším parametrem je režim porovnání, který má v případě potřeby zvětšit rozsah pro hledání hodnot. Původně VLOOKUP a HLOOKUP znaly pouze zásahy nebo chyby. XLOOKUP však může pružně reagovat a v případě neexistujícího výsledku alternativně použít hodnotu, která je co nejblíže, aby uživateli jednoduše nevypsal chybu, ale místo toho doporučil alternativu. Pokud například hledáte fakturu za 1 500 EUR, kterou nelze najít, pak lze pro parametr porovnávacího režimu místo něj použít hodnotu -1. Může se ukázat, že účet byl od začátku pouze 1450 EUR. Tyto informace bylo možné zjistit pouze s předchozími referencemi prostřednictvím mezikroků. Naopak hodnotu 1 lze použít k získání dalšího většího výsledku.
Tato funkce je obzvláště užitečná, když je hodnota známá jen zhruba. Tímto způsobem lze rámeček zúžit, aby bylo snadnější najít požadovaný výsledek navzdory všemu. Obsah tabulky navíc již nemusí být řazen vzestupně jako u VLOOKUP, protože XLOOKUP je schopen najít další smysluplnou hodnotu i bez pomoci uživatele. To také poskytuje další volnost v individualizaci tabulek.
Pokud však tyto inovace nejsou požadovány, lze jednoduše použít hodnotu 0, aby bylo nadále možné přijímat pouze přesné výsledky jako obvykle. S obecným zástupným symbolem se vzorec rozšíří takto:
= XLOOKUP (vyhledávací kritérium; vyhledávací matice; návratová matice; if_not_ found; srovnávací režim)
Režim vyhledávání
Poslední parametr opět ukazuje nejjednodušší a možná nejnápadnější vylepšení oproti VLOOKUP, protože umožňuje poprvé nastavit směr historie vyhledávání. S vloženou hodnotou 1 můžete vyhledávat přístupy shora dolů, zatímco s hodnotou -1 jde všechno vzhůru nohama. Dále lze zahájit vzestupné binární vyhledávání s hodnotou 2 a sestupné binární vyhledávání s hodnotou -2.
Přestože se tento parametr zpočátku nezdá nijak zvlášť působivý, v kombinaci s rozšířeným kritériem vyhledávání může pravidelně mít pozitivní účinek. Protože pokud vyhledávací matice obsahuje hledané kritérium dvakrát (například dva zaměstnanci se stejným příjmením), pak je ve výchozím nastavení vydána návratová hodnota, která je chronologicky na dřívější pozici. Pokud je však směr historie vyhledávání obrácen, dojde k opačnému efektu a dříve skrytá hodnota se stane viditelnou. Tento parametr je však také užitečný, pokud se používá pro dočasné řízení. Protože pokud vyhledávání shora dolů poskytuje jiný výsledek než vyhledávání zdola nahoru, může to znamenat, že došlo k chybě aplikace, kterou lze nyní v rané fázi opravit. Když vezmeme tuto poznámku v úvahu, konečný vzorec XLOOKUP vypadá takto:
= XLOOKUP (vyhledávací kritérium; matice vyhledávání; matice návratu; if_not_ found; režim porovnání; režim vyhledávání)
Vyplatí se přejít z VLOOKUP na XLOOKUP?
Na závěr je třeba říci, že se zavedením XLOOKUPu společnost Microsoft zpřístupnila uživatelům aplikace Excel nový způsob vyhledávání a analýzy, který může sloužit různým účelům a snadno se používá. Tímto způsobem je splněno vše od rychlého vyhledávání po konkrétní požadavky na výstup. XLOOKUP je tedy jasně před ostatními referencemi, protože nabízí jasné výhody i ve funkcích, které z toho převzal. Je to patrné ze skutečnosti, že zde jsou dvě referenční funkce sloučeny a mimo jiné doplněny integrovanou chybou if.
Každý, kdo byl doposud s VLOOKUP nebo HLOOKUP zcela spokojen a také nemá zájem zvyknout si na nový vzorec, se může s klidným svědomím držet osvědčeného. Pro všechny ostatní, kteří chtějí, aby byl jejich přístup dynamičtější a nekomplikovanější, je XVERWEIS vítanou novinkou. Nové možnosti lze zcela jistě upravit a kombinovat tak, aby další použití Excelu bylo viditelně pohodlnější.
