Jak používat funkci XLOOKUP v Excelu

The Funkce VLOOKUP byla vždy jednou z nejvýkonnějších funkcí Excelu. Umožňuje vám vyhledávat hodnoty v prvním sloupci tabulky a vracet hodnoty z polí vpravo. Excel má ale také funkci zvanou XLOOKUP, která umožňuje vyhledávat hodnotu v libovolném sloupci nebo řádku a vracet data z libovolného jiného sloupce.

Jak XLOOKUP funguje

Použití funkce XLOOKUP je mnohem jednodušší než funkce SVYHLEDAT, protože místo zadání hodnoty pro sloupec výsledků můžete zadat celý rozsah.

Funkce také umožňuje prohledávat sloupec i řádek a najít hodnotu v protínající se buňce.

Parametry funkce XLOOKUP jsou následující:

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

• vyhledávací_hodnota: Hodnota, kterou chcete vyhledat
• vyhledávací_pole: Pole (sloupec), které chcete prohledat
• return_array: Výsledek (sloupec), ze kterého chcete získat hodnotu
• match_mode (volitelné): Vyberte přesnou shodu (0), přesnou shodu nebo nejbližší nejmenší hodnotu (-1) nebo zástupnou shodu (2).
• search_mode (volitelné)
  : Vyberte, zda se má hledat od první položky ve sloupci (1), poslední položky ve sloupci (-1), binární vyhledávání vzestupně (2) nebo binární vyhledávání sestupně (-2).

Následuje několik nejběžnějších vyhledávání, která můžete provádět pomocí funkce XLOOKUP.

Jak vyhledat jeden výsledek pomocí XLOOKUP

Nejjednodušší způsob použití XLOOKUP je vyhledat jeden výsledek pomocí datového bodu z jednoho sloupce.

1. Tato ukázková tabulka je seznam objednávek odeslaných obchodními zástupci, včetně položky, počtu jednotek, nákladů a celkového prodeje.

   

2. Pokud chcete najít první prodej v seznamu odeslaném konkrétním obchodním zástupcem, můžete vytvořit funkci XLOOKUP, která hledá název ve sloupci Zástupce. Funkce vrátí výsledek ze sloupce Celkem. Funkce XLOOKUP pro to je:

   =XLOOKUP(I2;C2:C44;G2:G44;0;1)
   

   • I2: Ukazuje na Jméno zástupce vyhledávací buňku
   • C2:C44: Toto je sloupec Rep, což je vyhledávací pole
   • G2:G33: Toto je sloupec Celkem, což je návratové pole
   • 0: Vybere přesnou shodu
   • 1: Vybere první zápas ve výsledcích

3. Když stisknete Vstupte a zadejte jméno obchodního zástupce, buňka Celkový výsledek vám zobrazí první výsledek v tabulce pro daného obchodního zástupce.

   

4. Pokud chcete vyhledat nejnovější prodej (protože tabulka je uspořádána podle data v opačném pořadí), změňte poslední argument XLOOKUP na -1, která zahájí vyhledávání od poslední buňky ve vyhledávacím poli a místo toho vám poskytne tento výsledek.

   

5. Tento příklad ukazuje podobné vyhledávání, jaké byste mohli vy provést pomocí VLOOKUP pomocí sloupce Rep jako prvního sloupce vyhledávací tabulky. XLOOKUP vám však umožňuje hledat jakýkoli sloupec v obou směrech. Pokud například chcete najít obchodního zástupce, který prodal první objednávku Binderu v roce, použili byste následující funkci XLOOKUP:

   =XLOOKUP(I2;D2:D44;C2:C44;0;1)
   

   • D2: Ukazuje na buňku hledání položky
   • D2:D44: Toto je sloupec Položka, což je vyhledávací pole
   • C2:C44: Toto je sloupec Rep, což je pole návratu nalevo od vyhledávacího pole
   • 0: Vybere přesnou shodu
   • 1: Vybere první zápas ve výsledcích

6. Tentokrát bude výsledkem jméno obchodního zástupce, který prodal první zakázku na pořadač v tomto roce.

   

Proveďte vertikální a horizontální shodu pomocí XLOOKUP

Další funkcí XLOOKUP, kterou VLOOKUP neumí, je schopnost provádět jak vertikální, tak horizontální vyhledávání, což znamená, že můžete vyhledávat položku ve sloupci i přes řádek.

1. V následujícím příkladu tabulky jsou tržby každého obchodního zástupce rozděleny podle čtvrtletí. Pokud byste chtěli vidět tržby za třetí čtvrtletí pro konkrétního obchodního zástupce, bez funkce XLOOKUP by bylo toto vyhledávání obtížné.

   

2. S funkcí XLOOKUP je tento druh vyhledávání snadný. Pomocí následující funkce nexted XLOOKUP můžete vyhledat prodeje za třetí čtvrtletí pro konkrétního obchodního zástupce:

   =XLOOKUP(J2;B2:B42,XLOOKUP(K2;C1:H1,C2:H42))
   

   • J2: Ukazuje na buňku hledání zástupce
   • B2:B42: Toto je sloupec Položka, což je pole pro vyhledávání sloupců
   • K2: Ukazuje na buňku hledání čtvrtletí
   • C1:H1: Toto je pole pro vyhledávání řádků
   • C2:H42: Toto je vyhledávací pole pro částku v dolarech v každém čtvrtletí

   Tato vnořená funkce XLOOKUP nejprve identifikuje obchodního zástupce a další funkce XLOOKUP identifikuje požadované čtvrtletí. Vrácená hodnota will je buňka, kde se tyto dva protínají.

3. Výsledkem tohoto vzorce je čtvrtletní zisk reprezentanta jménem Thompson.

   

Použití funkce XLOOKUP

Funkce XLOOKUP je dostupná pouze pro předplatitele Office Insider, ale brzy bude zavedena pro všechny předplatitele Microsoft 365.

Pokud si chcete funkci vyzkoušet sami, můžete se stát Office Insider. Vybrat Soubor > Účeta poté vyberte Office Insider rozevírací seznam k odběru.

Jakmile se připojíte k programu Office Insider, vaše nainstalovaná verze Excelu obdrží všechny nejnovější aktualizace a můžete začít používat funkci XLOOKUP.