Jak používat funkci XLOOKUP v Excelu 2016

Excel 2016 pro předplatitele Office 365 na Windows a Mac nyní podporuje novou funkci XLOOKUP, nabízenou jako podstatně jednodušší a všestrannější náhrada za velmi populární (přesto často pomlouvanou) funkci vertikálního vyhledávání VLOOKUP (nevím, co X v XLOOKUP znamená; eXtensive, možná?).

Pro ty z vás, kteří ještě neznáte funkci VLOOKUP (považovanou za třetí nejpoužívanější funkci hned po SUM a AVERAGE), tato funkce prohledává svisle podle řádku v levém sloupci určené vyhledávací tabulky shora dolů, dokud nenajde hodnotu v vyhledávací sloupec označený offsetovým číslem, které odpovídá nebo přesahuje to, které hledáte. Ačkoli je funkce SVYHLEDAT nesmírně užitečná pro vyhledání konkrétních položek v dlouhém seznamu nebo sloupci datové tabulky ve vašem listu, má několik omezení, která tato nová vyhledávací funkce nesdílí, jako XLOOKUP:

• Výchozí nastavení pro nalezení přesných shod pro vaši vyhledávanou hodnotu ve vyhledávacím rozsahu
• Může v tabulce vyhledávat jak svisle (podle řádku), tak vodorovně (podle sloupce), čímž nahrazuje potřebu použití funkce HLOOKUP při vyhledávání vodorovně podle sloupce
• Může vyhledávat vlevo nebo vpravo, takže rozsah vyhledávání ve vaší vyhledávací tabulce nemusí být umístěn ve sloupci nalevo od sloupce označeného jako návratový rozsah, aby funkce fungovala
• Při použití výchozího nastavení přesné shody funguje, i když hodnoty v rozsahu vyhledávání nejsou seřazeny v určitém pořadí
• Může vyhledávat od spodního řádku k hornímu v rozsahu vyhledávacího pole pomocí volitelného argumentu režimu vyhledávání

Funkce XLOOKUP má pět možných argumentů, z nichž první tři jsou povinné a poslední dva volitelné, s použitím následující syntaxe:

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

Požadovaná lookup_value argumentu označuje hodnotu nebo položku, pro které hledáte. Požadovaný argument pole look_up určuje rozsah buněk, které mají být prohledány pro tuto vyhledávací hodnotu, a argument return_array určuje rozsah buněk obsahujících hodnotu, kterou chcete vrátit, když Excel najde přesnou shodu.

* Při určování argumentů lookup_array a return_array ve funkci XLOOKUP mějte na paměti, že oba rozsahy musí mít stejnou délku, jinak Excel vrátí #HODNOTA! chyba ve vašem vzorci. To je další důvod, proč při definování těchto argumentů používat názvy rozsahů nebo názvy sloupců určené datové tabulky, než je ukazovat nebo zadávat odkazy na jejich buňky .

Volitelný argument match_mode může obsahovat kteroukoli z následujících čtyř hodnot:

• 0 pro přesnou shodu (výchozí, stejná jako když není určen žádný argument match_mode )
• -1 pro přesnou shodu nebo další nižší hodnotu
• 1 pro přesnou shodu nebo další vyšší hodnotu
• 2 pro částečnou shodu s použitím zástupných znaků připojen k referenční buňky v lookup_value argumentu

Volitelný argument search_mode může obsahovat kteroukoli z následujících čtyř hodnot:

• 1 pro vyhledávání od prvního do posledního, tj. shora dolů (výchozí nastavení, stejné, jako když není určen žádný argument search_mode )
• -1 pro vyhledávání od posledního k prvnímu, tedy zdola nahoru
• 2 pro binární vyhledávání ve vzestupném pořadí
• -2 pro binární vyhledávání v sestupném pořadí

Nejlepší způsob, jak porozumět výkonu a všestrannosti nové funkce XLOOKUP, je vidět ji v akci v listu aplikace Excel. Na následujícím obrázku mám list s jednoduchou tabulkou údajů o prodeji za rok 2019 uspořádanou podle zemí. Chcete-li pomocí XLOOKUP vrátit celkový prodej z této tabulky v buňce E4 na základě země, kterou zadáte do buňky D4 listu, postupujte takto:

Umístěte kurzor buňky do buňky E4 listu

Klikněte na možnost Vyhledat a reference na kartě Vzorce a poté na možnost XLOOKUP v dolní části rozevírací nabídky a otevřete dialogové okno Argumenty funkcí.

Klepnutím na buňku D4 v listu zadejte odkaz na buňku do textového pole argumentu Lookup_value.

Stisknutím klávesy Tab vyberte textové pole argumentu Lookup_array, poté klikněte na buňku A4 a podržte klávesu Shift a současně stiskněte Ctrl-šipku dolů, abyste vybrali A4:A8 jako rozsah pro vyhledávání (protože rozsah A3:B8 je definován jako tabulka dat aplikace Excel, Tabulka1 [Země] se zobrazí v textovém poli namísto rozsahu A4:A8).

Stisknutím klávesy Tab vyberte textové pole argumentu Return_array, poté klikněte na buňku B4 a podržte stisknutou klávesu Shift a současně stiskněte Ctrl-šipku dolů, abyste vybrali B4:B8 jako rozsah obsahující hodnoty, které mají být vráceny na základě výsledků hledání (zobrazí se jako Tabulka1 [Celkový prodej] v textovém poli).

Klepnutím na OK zadejte vzorec XLOOKUP do buňky E4.

Vytvoření vzorce s XLOOKUP v buňce E4, který vrátí tržby na základě země zadané v buňce D4.

Excel zadá vzorec XLOOKUP do buňky E4 listu a jako výsledek vrátí 4900, protože Kostarika je aktuálně zadána do vyhledávací buňky D4 a jak můžete vidět v tabulce prodejů za rok 2019, toto je skutečně celkový prodej uskutečněný pro tuto zemi.

Protože XLOOKUP funguje zprava doleva stejně dobře jako zleva doprava, můžete tuto funkci stejně dobře použít k vrácení země z této prodejní tabulky na základě konkrétního čísla prodeje. Následující obrázek ukazuje, jak to udělat. Tentokrát vytvoříte vzorec XLOOKUP v buňce D4 a určíte hodnotu zadanou v buňce E4 (v tomto případě 11 000) jako argument vyhledávací_hodnota.

Kromě toho zadáte -1 jako argument match_mode, abyste přepsali výchozí nastavení přesné shody funkce, takže Excel vrátí zemi s přesnou shodou s hodnotou prodeje zadanou ve vyhledávací buňce E4 nebo zemi s nejbližším nižším celkovým prodejem (Mexiko s 10 000 USD v tomto případě, protože v této tabulce není žádná země s celkovým prodejem 11 000 USD). Bez určení argumentu match_mode pro tento vzorec by Excel vrátil jako výsledek #NA, protože v této prodejní tabulce není žádná přesná shoda s 11 000 $.

Vytvoření vzorce s XLOOKUP v buňce D4, který vrátí zemi na základě prodeje zadaného v buňce E4

Protože funkce XLOOKUP umožňuje stejně pohodlné prohledávání vodorovně podle sloupce i svisle po řádku, můžete ji použít k vytvoření vzorce, který provede obousměrné vyhledávání (nahrazuje potřebu vytvořit vzorec, který kombinuje funkce INDEX a MATCH jako v minulosti). Následující obrázek, který obsahuje tabulku výrobního plánu pro rok 2019 pro čísla dílů, AB-100 až AB-103 pro měsíce duben až prosinec, ukazuje, jak se to dělá.

Vytvoření vzorce s vnořenými funkcemi XLOOKUP pro vrácení počtu jednotek vyrobených pro díl v konkrétním měsíci

V buňce B12 jsem vytvořil následující vzorec:

=XLOOKUP(vyhledat_část,$A$3:$A$6,XLOOKUP(vyhledat_datum,$B$2:$J$2,$B$3:$J$6))

Tento vzorec začíná definováním funkce XLOOKUP, která vertikálně vyhledává podle řádku přesnou shodu se záznamem dílu provedeným v buňce s názvem part_lookup (v tomto případě buňka B10) v rozsahu buněk $A$3:$A$6 produkční tabulky. . Všimněte si však, že argument return_array pro tuto původní funkci LOOKUP je sám o sobě druhou funkcí XLOOKUP.

Tato druhá, vnořená funkce XLOOKUP prohledává rozsah buněk $B$2:$J$2 vodorovně podle sloupců, aby přesně odpovídala zadání data v buňce s názvem date_lookup (v tomto případě buňka B11). Argument return_array pro tuto druhou, vnořenou funkci XLOOKUP je $B$3:$J$6, rozsah buněk všech produkčních hodnot v tabulce.

Tento vzorec funguje tak, že Excel nejprve vypočítá výsledek druhé, vnořené funkce XLOOKUP provedením horizontálního vyhledávání, které v tomto případě vrátí pole v rozsahu buněk D3: D6 sloupce Jun-19 (s hodnotami: 438, 153, 306 a 779) jako jeho výsledek. Tento výsledek se zase stane argumentem return_array pro původní funkci XLOOKUP, která provádí vertikální vyhledávání podle řádku pro přesnou shodu se záznamem čísla dílu provedeným v buňce B11 (pojmenovaný part_lookup). Protože v tomto příkladu tato buňka part_lookup obsahuje AB-102, vzorec vrací pouze produkční hodnotu Jun-19, 306, z výsledku druhé, další funkce XLOOKUP.

Tady to máš! První pohled na XLOOKUP, výkonnou, všestrannou a poměrně snadno použitelnou novou vyhledávací funkci, která dokáže nejen vyhledávat jednu hodnotu pomocí funkcí VLOOKUP a HLOOKUP, ale také obousměrná vyhledávání hodnot prováděná kombinací Funkce INDEX a MATCH také.

* Funkce XLOOKUP bohužel není zpětně kompatibilní s dřívějšími verzemi aplikace Microsoft Excel, které podporují pouze funkce SVYHLEDAT a VVYHLEDAT, nebo kompatibilní s aktuálními verzemi, které ji ještě neobsahují jako jednu ze svých vyhledávacích funkcí, jako jsou Excel 2019 a Excel Online . To znamená, že pokud sdílíte sešit obsahující vzorce XLOOKUP se spolupracovníky nebo klienty, kteří používají verzi Excelu, která tuto novou vyhledávací funkci neobsahuje, všechny tyto vzorce vrátí #NAME? chybové hodnoty, když otevřou jeho list.