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á vám umožňuje vyhledat hodnotu v libovolném sloupci nebo řádku a vrátit data z jakéhokoli jiného sloupce.
Jak funguje XLOOKUP
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 vám také umožňuje prohledávat sloupec i řádek a najít hodnotu v protínající buňce.
Parametry funkce XLOOKUP jsou následující:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: Hodnota, kterou chcete vyhledat
- lookup_array: 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á vyhledávat od první položky ve sloupci (1), od 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.
-
Tato ukázková tabulka je seznam objednávek odeslaných obchodními zástupci, včetně položky, počtu jednotek, ceny a celkového prodeje.
Image -
Pokud chcete najít první prodej v seznamu předloženém konkrétním obchodním zástupcem, můžete vytvořit funkci XLOOKUP, která ve sloupci Zástupce hledá jméno. Funkce vrátí výsledek ze sloupce Celkem. Funkce XLOOKUP pro toto je:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Ukazuje na Jméno zástupce
- C2:C44: Toto je sloupec Rep, což je vyhledávací pole
- G2:G33: Toto je sloupec Celkem, což je pole návratů
- 0: Vybere přesnou shodu
- 1: Vybere první zápas ve výsledcích
-
Když stisknete Enter a zadáte 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.
Image -
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ý se spustí vyhledávání z poslední buňky ve vyhledávacím poli a místo toho vám poskytne tento výsledek.
Image -
Tento příklad ukazuje podobné vyhledávání, které byste mohli provést pomocí funkce SVYHLEDAT s použitím sloupce Rep jako prvního sloupce vyhledávací tabulky. XLOOKUP však umožňuje vyhledávat libovolný sloupec v obou směrech. Pokud například chcete najít obchodního zástupce, který prodal první objednávku Binder 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 pro návrat nalevo od vyhledávacího pole
- 0: Vybere přesnou shodu
- 1: Vybere první zápas ve výsledcích
-
Tentokrát bude výsledkem jméno obchodního zástupce, který prodal první objednávku pořadače v tomto roce.
Image
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.
Tato funkce duálního vyhledávání je účinnou náhradou za jiné funkce aplikace Excel, jako je INDEX, MATCH nebo HLOOKUP.
-
V následující ukázkové tabulce 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é.
Image -
S funkcí XLOOKUP je tento druh vyhledávání snadný. Pomocí následující funkce 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 vyhledávací buňku 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 bude je buňka, kde se tyto dvě zachytí.
-
Výsledkem tohoto vzorce je čtvrtletní výdělek zástupce jménem Thompson.
Image
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. Vyberte File > Účet a poté vyberte rozevírací seznam Office Insider pro přihlášení.
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.
