Funkce Excel XLOOKUP: Vše, co potřebujete vědět (10 příkladů)

Sledovat video - Funkce Excel XLOOKUP (10 příkladů XLOOKUP)

Vynikat Funkce XLOOKUP konečně dorazil.

Pokud používáte VLOOKUP nebo INDEX/MATCH, jsem si jist, že se vám bude líbit flexibilita, kterou funkce XLOOKUP poskytuje.

V tomto tutoriálu pokryju vše, co je třeba vědět o funkci XLOOKUP, a několik příkladů, které vám pomohou vědět, jak ji nejlépe používat.

Začněme tedy!

Co je XLOOKUP?

XLOOKUP je nová funkce Office 365 a je novou a vylepšenou verzí funkce VLOOKUP/HLOOKUP.

Dělá vše, co dříve používal VLOOKUP, a mnoho dalšího.

XLOOKUP je funkce, která vám umožní rychle vyhledat hodnotu v datové sadě (svislé nebo vodorovné) a vrátit odpovídající hodnotu v jiném řádku/sloupci.

Pokud například máte skóre pro studenty na zkoušce, můžete pomocí XLOOKUP rychle zkontrolovat, jak mnoho studentů dosáhlo, pomocí jména studenta.

Síla této funkce bude ještě jasnější, když se do některých ponořím Příklady XLOOKUP později v tomto tutoriálu.

Než se ale pustím do příkladů, vyvstává velká otázka - jak získám přístup k XLOOKUP?

Jak získat přístup k XLOOKUP?

Nyní je XLOOKUP k dispozici pouze pro uživatele Office 365.

Pokud tedy používáte předchozí verze Excelu (2010/2013/2016/2019), tuto funkci nebudete moci použít.

Také si nejsem jistý, zda to někdy bude vydáno pro předchozí verze nebo ne (možná může Microsoft vytvořit doplněk tak, jak to udělali pro Power Query). Nyní ji však můžete používat pouze v případě, že používáte Office 365.

Kliknutím sem provedete upgrade na Office 365

Pokud již používáte Office 365 (edice Home, Personal nebo University) a nemáte k němu přístup, můžete přejít na kartu Soubor a poté kliknout na Účet.

Bude existovat program Office Insider a můžete kliknout a připojit se k programu Office Insider. Získáte tak přístup k funkci XLOOKUP.

Očekávám, že XLOOKUP bude brzy k dispozici pro všechny verze Office 365.

Poznámka: XLOOKUP je k dispozici také pro Office 365 pro Mac a Excel pro web (Excel online)

Syntaxe funkce XLOOKUP

Níže je syntaxe funkce XLOOKUP:

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Pokud jste použili VLOOKUP, všimnete si, že syntaxe je docela podobná, samozřejmě s některými úžasnými dalšími funkcemi.

Pokud syntaxe a argument vypadají příliš mnoho, nedělejte si starosti. Pokrývám je několika snadnými příklady XLOOKUP později v tomto tutoriálu, díky nimž bude vše jasné.

Funkce XLOOKUP může vyprávět 6 argumentů (3 povinné a 3 volitelné):

1. lookup_value - hodnota, kterou hledáte
2. lookup_array - pole, ve kterém hledáte vyhledávací hodnotu
3. return_array - pole, ze kterého chcete načíst a vrátit hodnotu (odpovídající pozici, kde se nachází vyhledávací hodnota)
4. [if_not_found] - hodnota, která se má vrátit v případě, že není nalezena hodnota vyhledávání. V případě, že tento argument nezadáte, bude vrácena chyba #N/A
5. [match_mode] - Zde můžete určit požadovaný typ shody:
   • 0 - Přesná shoda, kde by lookup_value měla přesně odpovídat hodnotě v lookup_array. Toto je výchozí možnost.
   • -1 - Hledá přesnou shodu, ale pokud je nalezena, vrátí další menší položku/hodnotu
   • 1 - Hledá přesnou shodu, ale pokud je nalezena, vrátí další větší položku/hodnotu
   • 2 - Chcete -li provést částečnou shodu pomocí zástupných znaků (* nebo ~)
6. [režim hledání] - Zde určujete, jak má funkce XLOOKUP prohledávat lookup_array
   • 1 - Toto je výchozí možnost, kde funkce začne hledat hodnotu lookup_value od shora (první položka) po spodní část (poslední položka) v lookup_array
   • -1 - Provádí vyhledávání zdola nahoru. Užitečné, když chcete najít poslední odpovídající hodnotu v lookup_array
   • 2 - Provede binární vyhledávání, kde je potřeba data seřadit vzestupně. Pokud není seřazeno, může dojít k chybným nebo špatným výsledkům
   • -2 - Provádí binární vyhledávání, kde je potřeba data seřadit sestupně. Pokud není seřazeno, může dojít k chybným nebo špatným výsledkům

Příklady funkcí XLOOKUP

Nyní se dostaneme k zajímavé části - několika praktickým příkladům XLOOKUP.

Tyto příklady vám pomohou lépe pochopit, jak funguje XLOOKUP, jak se liší od VLOOKUP a INDEX/MATCH a některá vylepšení a omezení této funkce.

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Příklad 1: Načíst vyhledávací hodnotu

Předpokládejme, že máte následující datovou sadu a chcete načíst matematické skóre pro Grega (vyhledávací hodnota).

Níže je vzorec, který to dělá:

= XLOOKUP (F2, A2: A15, B2: B15)

Ve výše uvedeném vzorci jsem právě použil povinné argumenty, kde hledá jméno (shora dolů), najde přesnou shodu a vrátí odpovídající hodnotu z B2: B15.

Jedním zjevným rozdílem funkcí XLOOKUP a VLOOKUP je způsob, jakým zpracovávají vyhledávací pole. Ve VLOOKUP máte celé pole, kde je vyhledávací hodnota ve sloupci zcela vlevo, a poté zadáte číslo sloupce, ze kterého chcete výsledek načíst. XLOOKUP vám naopak umožňuje zvolit lookup_array a return_array samostatně

Jedna okamžitá výhoda mít oddělené argumenty lookup_array a return_array znamená, že nyní můžete podívej se doleva. VLOOKUP měl toto omezení, kdy můžete pouze vyhledat a najít hodnotu, která je napravo. Ale s XLOOKUP, toto omezení je pryč.

Zde je příklad. Mám stejnou datovou sadu, kde je název vpravo a return_range je vlevo.

Níže je vzorec, který mohu použít k získání skóre pro Greg v matematice (což znamená dívat se nalevo od lookup_value)

= XLOOKUP (F2, D2: D15, A2: A15)

XLOOKUP řeší další zásadní problém - V případě, že vložíte nový sloupec nebo sloupce přesunete, budou výsledná data stále správná. VLOOKUP by se pravděpodobně zlomil nebo by poskytl nesprávný výsledek v takových případech, kdy je většinou hodnota indexu sloupce pevně zakódována.

Příklad 2: Vyhledejte a načtěte celý záznam

Vezměme si stejná data jako příklad.

V tomto případě nechci jen načíst Gregovo skóre v matematice. Chci získat skóre ve všech předmětech.

V tomto případě mohu použít následující vzorec:

= XLOOKUP (F2, A2: A15, B2: D15)

Výše uvedený vzorec používá rozsah return_array, který je více než sloupec (B2: D15). Takže když je vyhledávací hodnota nalezena v A2: A15, vzorec vrátí celý řádek z return_array.

Rovněž nemůžete odstranit pouze buňky, které jsou součástí pole, které byly automaticky naplněny. V tomto případě nemůžete odstranit H2 nebo I2. Pokud to zkusíte, nic se nestane. Pokud vyberete tyto buňky, vzorec na řádku vzorců bude šedý (což znamená, že jej nelze změnit)

Vzorec můžete smazat v buňce G2 (kde jsme jej původně zadali), smaže celý výsledek.

Toto je užitečné vylepšení, protože v případě VLOOKUP budete muset pro každý vzorec zadat číslo sloupce samostatně.

Příklad 3: Obousměrné vyhledávání pomocí XLOOKUP (horizontální a vertikální vyhledávání)

Níže je soubor dat, kde chci znát skóre Grega v matematice (předmět v buňce G2).

To lze provést pomocí obousměrného vyhledávání, kde hledám jméno ve sloupci A a název předmětu v řádku 1. Výhodou tohoto obousměrného vyhledávání je, že výsledek je nezávislý na jméně studenta názvu předmětu. Pokud změním název předmětu na Chemie, tento obousměrný vzorec XLOOKUP bude stále fungovat a poskytne mi správný výsledek.

Níže je vzorec, který provede obousměrné vyhledávání a poskytne správný výsledek:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Tento vzorec používá vnořený XLOOKUP, kde nejprve pomocí něj načtu všechny známky studenta v buňce F2.

Výsledek XLOOKUP (F2, A2: A15, B2: D15) je {21,94,81}, což je řada známek, které v tomto případě zaznamenal Greg.

To se pak použije znovu v rámci vnějšího vzorce XLOOKUP jako návratové pole. Ve vnějším vzorci XLOOKUP hledám název subjektu (který je v buňce G1) a vyhledávací pole je B1: D1.

Pokud je název subjektu Math, tento vnější vzorec XLOOKUP načte první hodnotu z návratového pole - což je v tomto případě {21,94,81}.

To dělá to samé, čehož bylo dosud dosaženo pomocí kombinace INDEX a MATCH

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Příklad 4: Když není vyhledávací hodnota nalezena (zpracování chyb)

Do vzorce XLOOKUP bylo nyní přidáno zpracování chyb.

Čtvrtým argumentem funkce XLOOKUP je [if_not_found], kde můžete určit, co chcete v případě, že vyhledávání nelze nalézt.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, kde chcete získat matematické skóre v případě shody, a v případě, že název není nalezen, chcete se vrátit - „Nezobrazil se“

Následující vzorec to udělá:

= XLOOKUP (F2, A2: A15, B2: B15, "Nezobrazil se")

V tomto případě jsem pevně zakódoval, co chci získat v případě, že nedojde k shodě. Můžete také použít odkaz na buňku na buňku nebo vzorec.

Příklad 5: Vnořený XLOOKUP (vyhledávání ve více rozsazích)

Genius argumentu [if_not_found] spočívá v tom, že vám umožňuje použít vnořený vzorec XLOOKUP.

Předpokládejme například, že máte dva samostatné seznamy, jak je uvedeno níže. I když mám tyto dvě tabulky na stejném listu, můžete je mít v samostatných listech nebo dokonce v sešitech.

Níže je vnořený vzorec XLOOKUP, který zkontroluje název v obou tabulkách a vrátí odpovídající hodnotu ze zadaného sloupce.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

Ve výše uvedeném vzorci jsem použil argument [if_not_found] k použití jiného vzorce XLOOKUP. To vám umožní přidat druhý XLOOKUP do stejného vzorce a skenovat dvě tabulky s jediným vzorcem.

Nejsem si jistý, kolik vnořených XLOOKUPů můžete použít ve vzorci. Zkoušel jsem do 10 a šlo to, pak jsem to vzdal 🙂

Příklad 6: Najděte poslední odpovídající hodnotu

Tenhle byl velmi potřebný a XLOOKUP to umožnil. Nyní nemusíte hledat spletité způsoby, jak získat poslední odpovídající hodnotu v rozsahu.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete zkontrolovat, kdy byl v každém oddělení najat poslední člověk a jaké bylo datum přijetí.

Níže uvedený vzorec vyhledá poslední hodnotu pro každé oddělení a poskytne název posledního pronájmu:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)

A níže uvedený vzorec poskytne datum pronájmu posledního pronájmu pro každé oddělení:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)

Protože XLOOKUP má vestavěnou funkci určující směr vyhledávání (první na poslední nebo poslední na první), provádí se to pomocí jednoduchého vzorce. S vertikálními daty vypadají VLOOKUP a INDEX/MATCH vždy shora dolů, ale s XLOOKUP a mohou také určit směr jako zdola nahoru.

Příklad 7: Přibližná shoda s XLOOKUP (Najít daňovou sazbu)

Další pozoruhodné vylepšení s XLOOKUP je, že nyní existují čtyři režimy shody (VLOOKUP má 2 a MATCH má 3).

Můžete určit libovolný ze čtyř argumentů, abyste rozhodli, jak by měla být vyhledávací hodnota spárována:

• 0 - Přesná shoda, kde by lookup_value měla přesně odpovídat hodnotě v lookup_array. Toto je výchozí možnost.
• -1 - Hledá přesnou shodu, ale pokud je nalezena, vrátí další menší položku/hodnotu
• 1 - Hledá přesnou shodu, ale pokud je nalezena, vrátí další větší položku/hodnotu
• 2 - Chcete -li provést částečnou shodu pomocí zástupných znaků (* nebo ~)

Nejlepší na tom však je, že si nemusíte dělat starosti, zda jsou vaše data řazena vzestupně nebo sestupně. I když data nejsou tříděna, XLOOKUP se o to postará.

Níže mám datový soubor, kde chci najít provizi každé osoby - a provizi je třeba vypočítat pomocí tabulky vpravo.

Níže je uveden vzorec, který to provede:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2

To jednoduše použije prodejní hodnotu jako vyhledávání a prohledá vyhledávací tabulku vpravo. V tomto vzorci jsem použil -1 jako pátý argument ([match_mode]), což znamená, že bude hledat přesnou shodu, a když ji nenajde, vrátí hodnotu menší než je hodnota vyhledávání. .

A jak jsem řekl, nemusíte si dělat starosti, zda vaše data nejsou zařazena.

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Příklad 8: Horizontální vyhledávání

XLOOKUP umí vertikální i horizontální vyhledávání.

Níže mám datovou sadu, kde mám v řádcích jména studentů a jejich skóre, a chci načíst skóre pro jméno v buňce B7.

Následující vzorec to udělá:

= XLOOKUP (B7, B1: O1, B2: O2)

Toto není nic jiného než jednoduché vyhledávání (podobné tomu, co jsme viděli v příkladu 1), ale horizontální.

Všechny příklady, které popisuji ohledně vertikálního vyhledávání, lze také provést pomocí horizontálního vyhledávání pomocí XLOOKUP (rozloučení s VLOOKUP a HLOOKUP).

Příklad 9: Podmíněné vyhledávání (použití XLOOKUP s jinými vzorci)

Toto je mírně pokročilý příklad a také ukazuje sílu XLOOKUP, když potřebujete provádět složité vyhledávání.

Níže je uveden soubor dat, kde mám jména studentů a jejich skóre, a chci znát jméno studenta, který v každém předmětu získal maximum, a počet studentů, kteří v každém předmětu získali více než 80 bodů.

Níže je uveden vzorec, který poskytne jméno studenta s nejvyššími známkami v každém předmětu:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

Vzhledem k tomu, že XLOOKUP lze použít k vrácení celého pole, použil jsem jej nejprve k získání všech známek pro požadovaný předmět.

Například pro matematiku, když používám XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), dává mi všechna skóre v matematice. Potom mohu použít funkci MAX k nalezení maximálního skóre v tomto rozsahu.

Toto maximální skóre se pak stane mojí vyhledávací hodnotou a rozsahem vyhledávání by bylo pole vrácené XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Používám to v rámci jiného vzorce XLOOKUP k načtení jména studenta, který získal maximální počet bodů.

A abyste spočítali počet studentů, kteří získali více než 80 bodů, použijte následující vzorec:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Ten jednoduše používá vzorec XLOOKUP k získání rozsahu všech hodnot pro daný předmět. Poté jej zabalí do funkce COUNTIF, aby získal počet skóre více než 80.

Příklad 10: Použití zástupných znaků v XLOOKUP

Stejně jako můžete používat zástupné znaky ve VLOOKUP a MATCH, můžete to také udělat s XLOOKUP.

Ale je tu rozdíl.

V XLOOKUP musíte zadat, že používáte zástupné znaky (v pátém argumentu). Pokud to neuvedete, XLOOKUP vám zobrazí chybu.

Níže je soubor dat, kde mám názvy společností a jejich tržní kapitalizaci.

Chci ve sloupci D vyhledat název společnosti a z tabulky vlevo načíst tržní kapitalizaci. A protože jména ve sloupci D nejsou přesná shoda, budu muset použít zástupné znaky.

Níže je uveden vzorec, který to provede:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

Ve výše uvedeném vzorci jsem použil zástupný znak hvězdička (*) předtím jako po D2 (musí být v uvozovkách a spojen s D2 pomocí ampersandu).

To říká vzorci, aby prohlédl všechny buňky, a pokud obsahuje slovo v buňce D2 (což je Apple), považujte to za přesnou shodu. Bez ohledu na to, kolik a jaké znaky jsou před a za textem v buňce D2.

A aby XLOOKUP akceptoval zástupné znaky, byl pátý argument nastaven na 2 (shoda zástupných znaků).

Příklad 11: Najděte poslední hodnotu ve sloupci

Protože XLOOKUP umožňuje vyhledávání zdola nahoru, můžete snadno najít poslední hodnotu v seznamu a také načíst odpovídající hodnotu ze sloupce.

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete vědět, jaká je poslední společnost a jaká je tržní kapitalizace této poslední společnosti.

Následující vzorec vám poskytne název poslední společnosti:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

A níže uvedený vzorec poskytne tržní limit poslední společnosti v seznamu:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Tyto vzorce opět používají zástupné znaky. V těchto jsem jako vyhledávací hodnotu použil hvězdičku (*), což znamená, že by to považovalo první buňku, se kterou se setká, za přesnou shodu (jako hvězdička může být jakýkoli znak a libovolný počet znaků).

A protože směr je zdola nahoru (pro vertikálně uspořádaná data), vrátí poslední hodnotu v seznamu.

A druhý vzorec od té doby používá samostatný return_range k získání tržní kapitalizace příjmení v seznamu.

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Co když nemáte XLOOKUP?

Protože XLOOKUP bude pravděpodobně k dispozici pouze uživatelům Office 365, je jedním ze způsobů, jak jej získat, upgrade na Office 365.

Pokud již máte edici Office 365 Home, Personal nebo University, již máte přístup k XLOOKUP. Vše, co musíte udělat, je připojit se k programu Office Insider.

Chcete -li to provést, přejděte na kartu Soubor, klikněte na Účet a poté klikněte na možnost Office insider. Existovala by možnost připojit se k zasvěcenému programu.

V případě, že máte další předplatné Office 365 (například Enterprise), jsem si jistý, že brzy bude k dispozici XLOOKUP a další úžasné funkce (například dynamická pole, vzorce jako SORT a FILTER).

V případě, že používáte Excel 2010/2013/2016/2019, nebudete mít XLOOKUP a budete muset i nadále používat kombinaci VLOOKUP, HLOOKUP a INDEX/MATCH, abyste získali z vyhledávacích vzorců to nejlepší.

XLOOKUP Zpětná kompatibilita

Na jednu věc si musíte dát pozor - XLOOKUP je NENÍ zpětně kompatibilní.

To znamená, že pokud vytvoříte soubor a použijete vzorec XLOOKUP a poté jej otevřete ve verzi, která nemá XLOOKUP, zobrazí se chyby.

Vzhledem k tomu, že XLOOKUP je obrovským krokem vpřed správným směrem, věřím, že se to stane výchozím vzorkem vyhledávání, ale bude určitě trvat několik let, než se začne široce přijímat. Koneckonců stále vidím některé lidi, kteří používají Excel 2003.

Jedná se tedy o 11 příkladů XLOOKUP, které vám mohou pomoci rychleji provést všechny vyhledávací a referenční činnosti a také usnadnit používání.

Doufám, že jste našli tento návod užitečný!