Excel 2020: Dvanáct výhod XLOOKUPU - Excel Tipy

Nová funkce XLOOKUP se na Office 365 zavádí od listopadu 2019. Joe McDaid z týmu Excel vytvořil XLOOKUP, aby sjednotil lidi, kteří používají VLOOKUP, a lidi, kteří používají INDEX / MATCH. Tato část pojednává o 12 výhodách XLOOKUPU:

  1. Přesná shoda je výchozí.
  2. Celé číslo založené na třetím argumentu VLOOKUP je nyní správným odkazem.
  3. IFNA je integrovaná pro zpracování chybějících hodnot.
  4. XLOOKUP nemá problém jít doleva.
  5. Najděte další menší nebo další větší shodu bez třídění tabulky.
  6. XLOOKUP může dělat HLOOKUP.
  7. Najděte poslední zápas hledáním zdola.
  8. Zástupné znaky jsou ve výchozím nastavení „vypnuté“, ale můžete je znovu zapnout.
  9. Vrátit všech 12 měsíců v jednom vzorci.
  10. Může vrátit odkaz na buňku, pokud je XLOOKUP vedle dvojtečky, například XLOOKUP (); XLOOKUP ()
  11. Může dělat obousměrnou shodu jako INDEX (, MATCH, MATCH).
  12. Dokáže shrnout všechna vyhledávání v jednom vzorci, jaký by mohl VYHLEDAT.

Zde je syntaxe: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Výhoda XLOOKUP 1: Výchozí přesná shoda

99% mých vzorců VLOOKUP končí na FALSE nebo 0, což znamená přesnou shodu. Pokud vždy používáte verzi VLOOKUP s přesnou shodou, můžete začít nechat match_mode vypnutou funkci XLOOKUP.

Na následujícím obrázku vyhledáváte W25-6 z buňky A4. Chcete vyhledat tuto položku v L8: L35. Když je nalezena, chcete odpovídající cenu ze sloupce N. Není nutné specifikovat False jako match_mode, protože XLOOKUP je výchozí pro přesnou shodu.

XLOOKUPUJTE hodnotu v A4. Podívejte se na L8: L35. Vraťte odpovídající cenu z N8: N35.

Výhoda XLOOKUP 2: Pole Results_Array je odkaz místo celého čísla

Přemýšlejte o vzorci VLOOKUP, který byste použili před XLOOKUP. Třetí argument by byl číslo 3, což znamená, že chcete vrátit 3. sloupec. Vždy existovalo nebezpečí, že by bezradný spolupracovník vložil (nebo odstranil) sloupec do vaší tabulky. S dalším sloupcem v tabulce by VLOOKUP, který vracel cenu, začal vracet popis. Protože XLOOKUP ukazoval na odkaz na buňku, přepíše se vzorec sám, aby ukazoval na cenu, která je nyní ve sloupci O.

Starý VLOOKUP by selhal, kdyby někdo vložil nový sloupec do vyhledávací tabulky. XLOOKUP stále funguje.

Výhoda XLOOKUP 3: IFNA je integrována jako volitelný argument

Když se v tabulce nenachází vaše vyhledávací hodnota, vrátí se obávaná chyba # N / A. V minulosti, abyste nahradili # N / A něčím jiným, museli byste použít IFERROR nebo IFNA zabalené kolem VLOOKUP.

Pokud položka není nalezena, vrátí # N / A z VLOOKUP nebo XLOOKUP…

Díky návrhu od Rica na mém kanálu YouTube začlenil tým Excel volitelný čtvrtý argument pro if_not_found. Pokud chcete tyto chyby # N / A nahradit nulou, jednoduše přidejte 0 jako čtvrtý argument. Nebo můžete použít nějaký text, například „Hodnota nenalezena“.

Volitelný čtvrtý argument v XLOOKUP je „pokud nebyl nalezen“. Vložte tam 0 nebo „Nenalezeno“.

Výhoda XLOOKUP 4: Žádný problém při pohledu nalevo od pole klíče

VLOOKUP se nemůže dívat nalevo od pole klíče, aniž by se uchýlil k VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). S XLOOKUPem není problém mít Results_array nalevo od Lookup_array.

S XLOOKUP není problém vrátit kategorii ze sloupce F při vyhledávání čísel dílů ve sloupci G. To byla vždy slabost VLOOKUP: nemohla vypadat nalevo.

Výhoda XLOOKUP 5: Další menší nebo další větší shoda bez třídění

VLOOKUP měl možnost hledat přesnou shodu nebo jen menší hodnotu. Můžete buď vynechat čtvrtý argument z VLOOKUP, nebo změnit False na True. Aby to fungovalo, musela být vyhledávací tabulka řazena vzestupně.

Příklad verze přibližné shody VLOOKUP. Jakýkoli prodej od 10 do 20 tisíc dostane bonus 12 dolarů.

Ale VLOOKUP neměl schopnost vrátit přesnou shodu nebo další větší položku. K tomu jste museli přepnout na použití MATCH s -1 jako match_mode a museli jste dávat pozor, aby byla vyhledávací tabulka tříděna sestupně.

Volitelný pátý argument XLOOKUP match_mode může hledat pouze přesnou shodu, rovnou nebo jen menší, rovnou nebo jen větší. Všimněte si, že hodnoty v XLOOKUPu dávají větší smysl než v MATCH:

  • -1 najde hodnotu rovnou nebo jen menší
  • 0 najít přesnou shodu
  • 1 najde hodnotu rovnou nebo jen větší.

Ale nejúžasnější část: vyhledávací tabulka nemusí být tříděna a jakýkoli match_mode bude fungovat.

Níže, match_mode -1 najde další menší položku.

Pátým argumentem XLOOKUP je Match_Mode. 0 je pro přesnou shodu. Negativní se používá pro přesnou shodu nebo další menší položku. Kladná 1 je pro přesnou shodu nebo další větší položku. 2 je pro zápas se zástupnými znaky. Chcete-li zrcadlit, co by VLOOKUP s True ve čtvrtém argumentu udělal, vložte negativní jako argument match_mode do XLOOKUP.

Zde, match_mode 1, najde, jaké vozidlo je potřeba v závislosti na počtu lidí na večírku. Upozorňujeme, že vyhledávací tabulka není tříděna podle cestujících a název vozidla je nalevo od klíče.

XLOOKUP dokáže něco, co VLOOKUP nedokáže: najít přesnou shodu nebo jen větší. V takovém případě má cestovní agentura seznam rezervací. Na základě počtu cestujících vyhledávací tabulka ukazuje, jaké vozidlo pro tyto lidi potřebujete.

Tabulka říká:

  • Autobus pojme 64 osob
  • Auto pojme 4 osoby
  • Motocykl pojme 1 osobu
  • Tour Van pojme 12 osob
  • Van má 6 osob.

Jako bonus jsou data tříděna podle vozidla (ve starém řešení, pomocí MATCH, by se tabulka musela třídit sestupně podle kapacity. Také: vozidlo je nalevo od kapacity.

Výhoda XLOOKUP 6: Boční XLOOKUP nahrazuje HLOOKUP

Lookup_array a results_array mohou být horizontální s XLOOKUP, což usnadňuje nahrazení HLOOKUP.

Zde je vyhledávací tabulka vodorovná. V minulosti by to vyžadovalo HLOOKUP, ale XLOOKUP si poradí s tabulkou, která jde do strany.

Výhoda XLOOKUP 7: Vyhledejte poslední shodu zdola

Mám staré video na YouTube, které odpovídá na otázku z britské koňské farmy. Měli flotilu vozidel. Pokaždé, když vozidlo přišlo pro palivo nebo servis, zaznamenali vozidlo, datum a počet najetých kilometrů do tabulky. Chtěli najít nejnovější známý počet najetých kilometrů pro každé vozidlo. Zatímco MAXIFS z éry Excel-2017 to dnes může vyřešit, řešením před mnoha lety byl tajemný vzorec využívající LOOKUP a podílející se na dělení nulou.

Dnes volitelný šestý argument XLOOKUPu vám umožňuje určit, že hledání by mělo začít od spodní části datové sady.

Najděte poslední zápas v seznamu.

Poznámka

I když se jedná o velké vylepšení, umožní vám najít pouze první nebo poslední zápas. Někteří lidé doufali, že vám to umožní najít druhou nebo třetí shodu, ale to není záměr argumentu search_mode.

Pozor

Obrázek výše ukazuje, že existují režimy vyhledávání využívající staré binární vyhledávání. Joe McDaid nedoporučuje je používat. Za prvé, vylepšený vyhledávací algoritmus z roku 2018 je dostatečně rychlý, takže zde není žádný významný rychlostní přínos. Zadruhé riskujete, že bezradný spolupracovník roztřídí vyhledávací tabulku a zavede nesprávné odpovědi.

Výhoda XLOOKUP 8: Zástupné znaky jsou ve výchozím nastavení „vypnuty“

Většina lidí si neuvědomila, že VLOOKUP zachází s hvězdičkou, otazníkem a vlnovkou jako se zástupnými znaky, jak je popsáno v části „# 51 Použití zástupných znaků ve VLOOKUP“ na straně 143. U XLOOKUPu jsou ve výchozím nastavení zástupné znaky vypnuty. Pokud chcete, aby XLOOKUP považoval tyto znaky za zástupné znaky, použijte 2 jako Match_Mode.

Velmi málo lidí si uvědomilo, že VLOOKUP zachází s hvězdičkami ve vyhledávací hodnotě jako se zástupným znakem. Ve výchozím nastavení XLOOKUP nepoužívá zástupné znaky, ale můžete jej vynutit, aby se choval jako VLOOKUP, pokud použijete režim shody 2: Zástupný znak.

Výhoda XLOOKUP 9: Vraťte všech 12 měsíců v jednom vzorci!

To je opravdu výhoda Dynamic Arrays, ale je to můj oblíbený důvod, proč milovat XLOOKUP. Když musíte při vyhledávání vrátit všech 12 měsíců, jeden vzorec zadaný v B6 s obdélníkovým return_array vrátí více výsledků. Tyto výsledky se rozlijí do sousedních buněk.

Na následujícím obrázku jediný vzorec zadaný v B7 vrátí všech 12 odpovědí zobrazených v B7: M7.

Jeden XLOOKUP ve sloupci Leden vrátí čísla za leden až prosinec. To se provádí zadáním pole results_array s 12 sloupci.

Výhoda XLOOKUP 10: Může vrátit odkaz na buňku, pokud sousedí s dvojtečkou

Tenhle je složitý, ale krásný. V minulosti existovalo sedm funkcí, které by se změnily z vrácení hodnoty buňky na vrácení odkazu na buňku, pokud by se funkce dotýkala dvojtečky. Příklad najdete v části Použít A2: INDEX () jako energeticky nezávislý OFSET. XLOOKUP je funkce osmiček, která nabízí toto chování a připojuje se k CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET a SWITCH.

Zvažte následující obrázek. Někdo vybere Cherry v E4 a Fig v E5. Chcete vzorec, který shrnuje vše od B6 do B9.

Obrázek ukazuje dva vzorce XLOOKUP ve dvou buňkách. První vrátí 15 z buňky B6. Druhý retrunuje 30 z B9. Ale pak ve třetí buňce existuje vzorec, který spojuje dva vzorce XLOOKUP s dvojtečkou a poté jej zabalí do funkce SUM. Výsledkem je SUM B6: B9, protože XLOOKUP může vrátit odkaz na buňku, pokud se funkce objeví vedle operátoru, jako je dvojtečka. Abychom prokázali, že to funguje, na následujících několika obrázcích se tento vzorec zobrazí v dialogovém okně Vyhodnotit vzorec.

Na obrázku výše vidíte, že XLOOKUP E4 vrátí 15 z buňky B6. XLOOKUP E5 vrátí 30 z B9. Pokud však vezmete dvě funkce XLOOKUP z buněk D9 a D10 a spojíte je s dvojtečkou mezi nimi, chování XLOOKUPu se změní. Místo vrácení 15 vrátí první XLOOKUP adresu buňky B6!

Abych to dokázal, vybral jsem D7 a používám vzorce, vyhodnotit vzorec. Po dvojím stisknutí tlačítka Vyhodnotit je další část k výpočtu XLOOKUP („Cherry“, A4: A29, B4: B29), jak je znázorněno zde.

Zobrazí se dialog Vyhodnotit vzorec těsně před hodnocením prvního XLOOKUPU. Tento XLOOKUP se zobrazí těsně před dvojtečkou.

Stiskněte Vyhodnotit znovu a úžasně, vzorec XLOOKUP vrací $ B $ 6 namísto 15 uložených v B6. K tomu dochází, protože za tímto vzorcem XLOOKUP bezprostředně následuje dvojtečka.

Klikněte na Vyhodnotit a první XLOOKUP vrátí $ B $ 6 namísto 15.

Stiskněte Vyhodnotit ještě dvakrát a prozatímní vzorec bude = SUM (B6: B9).

Po vyhodnocení druhého XLOOKUPU je průběžný vzorec = SUM (B6: B9).

To je úžasné chování, o kterém většina lidí neví. Charles MVP pro Excel mi říká, že jej lze spustit u kteréhokoli z těchto tří operátorů vedle XLOOKUP:

  • Dvojtečka
  • Vesmír (operátor křižovatky)
  • Čárka (provozovatel Unie)

Výhoda XLOOKUPu 11: Obousměrná shoda jako INDEX (, MATCH, MATCH)

Na všechny mé přátele VLOOKUP čekali lidé INDEX / MATCH, zda XLOOKUP zvládne oboustranný zápas. Skvělá zpráva: dokáže to. Špatná zpráva: metodika je trochu jiná, než by fanoušci INDEX / MATCH očekávali. Mohlo by to být trochu nad jejich hlavami. Ale jsem si jistý, že k této metodě mohou přijít.

U obousměrné shody chcete zjistit, který řádek obsahuje číslo účtu A621 zobrazené v J3. Takže XLOOKUP začíná dostatečně snadno: = XLOOKUP (J3, A5: A15. Ale pak musíte poskytnout pole results_array. Můžete použít stejný trik jako v XLOOKUP Výhoda 9: Vrátit všech 12 měsíců v jednom vzorci výše, ale použijte jej k vrácení svislého vektoru. Vnitřní XLOOKUP hledá měsíc J4 v záhlavích měsíců v B4: G4. Return_array je zadán jako B5: G15. Výsledkem je, že vnitřní XLOOKUP vrací pole podobné tomu, které je uvedeno v I10 : I20 níže. Protože A621 se nachází v páté buňce lookup_array a 104 se nachází v páté buňce results_array, získáte správnou odpověď ze vzorce. Níže J6 ukazuje starou cestu. J7 vrací novou cestu.

XLookup J3 v seznamu účtů v A5: A15. Pro pole výsledků použijte XLOOKUP (J4, B4: G4, B5: G15). V tomto vzorci je B4: G4 seznam měsíců. B5: G15 je obdélníkové pole hodnot pro všechny účty po všechny měsíce. V jiné buňce pouze vnitřní XLOOKUP ukazuje, jak vrací celý sloupec hodnot za květen.

Výhoda XLOOKUP 12: Součet všech hodnot vyhledávání v jednom vzorci

Starodávná funkce VYHLEDÁVÁNÍ nabídla dva podivné triky. Nejprve, pokud se snažíte zjistit celkovou částku bonusových výdajů, které se budou hromadit, můžete požádat LOOKUP o vyhledání všech hodnot v jednom vzorci. Na obrázku níže LOOKUP (C4: C14 provádí 11 vyhledávání. Funkce LOOKUP však nenabídla přesnou shodu a vyžadovala seřazení vyhledávací tabulky.

Vyhledejte 13 hodnot a sečtěte je. Toto dříve fungovalo s VYHLEDÁVÁNÍM, ale funguje také s XLOOKUPEM. Jako první argument zadejte všechny vyhledávací hodnoty C4: C14. Zabalte XLOOKUP do funkce SUMA.

S XLOOKUP můžete určit rozsah jako lookup_value a XLOOKUP vrátí všechny odpovědi. Výhodou je, že XLOOKUP dokáže provádět přesné shody.

Trik s použitím LOOKUP k shrnutí všech výsledků vyhledávání fungoval pouze s verzí vyhledávání s přibližnou shodou. Zde XLOOKUP provádí přesnou shodu se všemi jmény v L4: L14 a získává celkem všechny výsledky.

Bonusový tip: A co Twisted LOOKUP?

Excel MVP Mike Girvin často ukazuje trik funkce LOOKUP, kde je Lookup_Vector vertikální a Result_Vector horizontální. XLOOKUP tento trik nativně nepodporuje. Ale pokud trochu podvádíte a zabalíte results_array do funkce TRANSPOSE, můžete spravovat zkroucené vyhledávání.

Zde je vyhledávací pole svislé a pole výsledků vodorovné. Stará funkce LOOKUP to zvládne, ale abyste to mohli udělat s XLOOKUP, musíte zabalit každé pole do TRANSPOSE.

Zajímavé články...