Jak používat funkci XLOOKUP v aplikaci Microsoft Excel

Nový XLOOKUP aplikace Excel nahradí VLOOKUP a poskytne tak výkonnou náhradu jedné z nejpopulárnějších funkcí aplikace Excel. Tato nová funkce řeší některá omezení VLOOKUPu a má další funkce. Tady je to, co potřebujete vědět.

Co je XLOOKUP?

Nová funkce XLOOKUP obsahuje řešení pro některá z největších omezení VLOOKUP. Navíc také nahrazuje HLOOKUP. Například XLOOKUP může vypadat nalevo, má výchozí přesnou shodu a umožňuje vám místo čísla sloupce určit rozsah buněk. VLOOKUP není tak snadno použitelný nebo všestranný. Ukážeme vám, jak to všechno funguje.

V tuto chvíli je XLOOKUP k dispozici pouze uživatelům programu Insider. Kdokoli se může připojit k programu Insider a získat přístup k nejnovějším funkcím aplikace Excel, jakmile budou k dispozici. Microsoft to brzy začne zavádět všem uživatelům Office 365.

Jak používat funkci XLOOKUP

Pojďme se ponořit přímo do příkladu XLOOKUP v akci. Vezměte níže uvedená příkladová data. Chceme vrátit oddělení ze sloupce F pro každé ID ve sloupci A.

Toto je klasický příklad vyhledávání v přesné shodě. Funkce XLOOKUP vyžaduje pouze tři informace.

Obrázek níže ukazuje XLOOKUP se šesti argumenty, ale pro přesnou shodu jsou nutné pouze první tři. Zaměřme se tedy na ně:

  • Lookup_value:  Co hledáte.
  • Lookup_array:  Kam hledat.
  • Return_array:  rozsah obsahující hodnotu, která se má vrátit.

V tomto příkladu bude fungovat následující vzorec: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Pojďme nyní prozkoumat několik výhod, které má XLOOKUP oproti VLOOKUP zde.

Žádné další indexové číslo sloupce

Neslavným třetím argumentem VLOOKUP bylo zadat číslo sloupce informací, které se mají vrátit z pole tabulky. Toto již není problém, protože XLOOKUP vám umožňuje vybrat rozsah, ze kterého se chcete vrátit (sloupec F v tomto příkladu).

A nezapomeňte, XLOOKUP může na rozdíl od VLOOKUP zobrazit data vlevo od vybrané buňky. Více o tom níže.

Také již nebudete mít problém s nefunkčním vzorcem při vkládání nových sloupců. Pokud k tomu došlo v tabulce, rozsah návratnosti by se automaticky upravil.

Výchozí je přesná shoda

Při učení VLOOKUP bylo vždy matoucí, proč jste museli zadat přesnou shodu.

Naštěstí XLOOKUP standardně obsahuje přesnou shodu - mnohem častější důvod pro použití vyhledávacího vzorce). To snižuje potřebu odpovídat na tento pátý argument a zajišťuje méně chyb uživatelů, kteří jsou ve vzorci noví.

Stručně řečeno, XLOOKUP se ptá méně otázek než VLOOKUP, je uživatelsky přívětivější a je také odolnější.

XLOOKUP se může dívat doleva

Díky možnosti výběru rozsahu vyhledávání je XLOOKUP univerzálnější než VLOOKUP. U XLOOKUPu nezáleží na pořadí sloupců tabulky.

VLOOKUP byl omezen prohledáním sloupce nejvíce vlevo v tabulce a poté návratem ze zadaného počtu sloupců doprava.

V níže uvedeném příkladu musíme vyhledat ID (sloupec E) a vrátit jméno osoby (sloupec D).

Toho lze dosáhnout pomocí následujícího vzorce: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Co dělat, pokud nebyl nalezen

Uživatelé vyhledávacích funkcí jsou velmi dobře obeznámeni s chybovou zprávou # N / A, která je pozdraví, když jejich funkce VLOOKUP nebo jejich MATCH nemohou najít to, co potřebuje. A často to má logický důvod.

Uživatelé proto rychle prozkoumají, jak tuto chybu skrýt, protože není správná nebo užitečná. A samozřejmě existují způsoby, jak toho dosáhnout.

XLOOKUP je dodáván s vlastním vestavěným argumentem „pokud nebyl nalezen“, který tyto chyby zpracovává. Podívejme se na to v akci s předchozím příkladem, ale s chybně zadaným ID.

Následující vzorec zobrazí místo chybové zprávy text „Nesprávné ID“: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Použití XLOOKUPU k vyhledání rozsahu

I když to není tak běžné jako přesná shoda, velmi efektivním použitím vyhledávacího vzorce je hledat hodnotu v rozmezí. Vezměte si následující příklad. Chceme vrátit slevu v závislosti na vynaložené částce.

Tentokrát nehledáme konkrétní hodnotu. Potřebujeme vědět, kde hodnoty ve sloupci B spadají do rozmezí ve sloupci E. To určí získanou slevu.

XLOOKUP má volitelný pátý argument (pamatujte, že má výchozí přesnou shodu) režim pojmenovaného shody.

Vidíte, že XLOOKUP má s přibližnými shodami lepší možnosti než VLOOKUP.

Existuje možnost najít nejbližší shodu menší než (-1) nebo nejbližší větší než (1) hledaná hodnota. Existuje také možnost použít zástupné znaky (2), například? nebo *. Toto nastavení není ve výchozím nastavení zapnuto, jako tomu bylo u VLOOKUP.

Vzorec v tomto příkladu vrátí nejbližší menší než hledanou hodnotu, pokud není nalezena přesná shoda: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Existuje však chyba v buňce C7, kde je vrácena chyba # N / A (argument „pokud nebyl nalezen“ nebyl použit). To by mělo vrátit slevu 0%, protože útrata 64 nesplňuje kritéria pro jakoukoli slevu.

Další výhodou funkce XLOOKUP je, že nevyžaduje, aby byl rozsah vyhledávání ve vzestupném pořadí jako VLOOKUP.

V dolní části vyhledávací tabulky zadejte nový řádek a poté otevřete vzorec. Rozbalte použitý rozsah kliknutím a tažením rohů.

Vzorec chybu okamžitě opraví. Není problém mít „0“ ve spodní části rozsahu.

Osobně bych stále třídil tabulku podle vyhledávacího sloupce. Mít „0“ dole by mě vedlo k šílenství. Ale skutečnost, že se vzorec neporušil, je skvělá.

XLOOKUP Nahradí také funkci HLOOKUP

Jak již bylo zmíněno, funkce XLOOKUP je zde také k nahrazení HLOOKUP. Jedna funkce k nahrazení dvou. Vynikající!

Funkce HLOOKUP je horizontální vyhledávání, které se používá pro vyhledávání po řádcích.

Není tak dobře známý jako jeho sourozenec VLOOKUP, ale je užitečný pro příklady jako níže, kde jsou záhlaví ve sloupci A a data jsou podél řádků 4 a 5.

XLOOKUP se může dívat oběma směry - dolů do sloupců a také podél řádků. Už nepotřebujeme dvě různé funkce.

V tomto příkladu se vzorec používá k vrácení prodejní hodnoty týkající se názvu v buňce A2. Prohledá řádek 4, aby našel název, a vrátí hodnotu z řádku 5:=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP se může dívat zdola nahoru

Typicky je třeba vyhledat seznam, abyste našli první (často jediný) výskyt hodnoty. XLOOKUP má šestý argument s názvem režim vyhledávání. To nám umožňuje přepnout vyhledávání tak, aby začalo od dolní části a vyhledat seznam a místo toho najít poslední výskyt hodnoty.

V níže uvedeném příkladu bychom chtěli ve sloupci A najít úroveň zásob každého produktu.

Vyhledávací tabulka je v pořadí podle data a u každého produktu existuje několik kontrol zásob. Chceme vrátit úroveň zásob od poslední kontroly (poslední výskyt ID produktu).

Šestý argument funkce XLOOKUP poskytuje čtyři možnosti. Máme zájem o použití možnosti „Hledat od začátku k prvnímu“.

Zde je uveden vyplněný vzorec: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

V tomto vzorci byl čtvrtý a pátý argument ignorován. Je volitelný a chtěli jsme výchozí nastavení přesné shody.

Zátah

Funkce XLOOKUP je netrpělivě očekávaným nástupcem funkcí VLOOKUP i HLOOKUP.

V tomto článku byla použita celá řada příkladů k prokázání výhod XLOOKUPU. Jedním z nich je, že XLOOKUP lze použít napříč listy, sešity a také s tabulkami. Příklady byly v článku jednoduché, aby nám pomohly porozumět.

Vzhledem k tomu, že se do aplikace Excel brzy zavedou dynamická pole, může také vracet řadu hodnot. To je určitě něco, co stojí za prozkoumání dále.

Dny SVYHLEDÁVÁNÍ jsou sečteny. XLOOKUP je tady a brzy bude de facto vyhledávací vzorec.