Funkce VLOOKUP byla vždy jednou z nejvýkonnějších funkcí aplikace Excel. Umožní vám vyhledat hodnoty v prvním sloupci tabulky a vrátit hodnoty z polí vpravo. Ale Excel má také funkci nazvanou XLOOKUP, která vám umožní vyhledat hodnotu v libovolném sloupci nebo řádku a vrátit data z kteréhokoli jiného sloupce.
Jak XLOOKUP funguje
Funkce XLOOKUP je mnohem jednodušší než funkce VLOOKUP, protože místo určení hodnoty pro sloupec výsledků můžete zadat celý rozsah. Funkce také umožňuje prohledávat sloupec i řádek a vyhledávat 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 načíst hodnotu
- match_mode (volitelný): Vyberte přesnou shodu (0), přesnou shodu nebo další nejmenší hodnotu (-1) nebo shodu se zástupnými znaky (2).
- režim vyhledávání (volitelný): Vyberte, zda chcete hledat počínaje první položkou ve sloupci (1), poslední položkou ve sloupci (-1), binárním vyhledáváním vzestupně (2) nebo binárním vyhledáváním sestupně (-2).
Následuje několik nejběžnějších vyhledávání, která můžete pomocí funkce XLOOKUP provést.
Jak vyhledat jeden výsledek pomocí XLOOKUPU
Nejjednodušší způsob, jak použít XLOOKUP, je vyhledat jeden výsledek pomocí datového bodu z jednoho sloupce.
-
V tomto příkladu tabulky je seznam objednávek odeslaných obchodními zástupci, včetně položky, počtu jednotek, nákladů a celkového prodeje.
-
Pokud chcete najít první prodej v seznamu předloženém konkrétním prodejním zástupcem, můžete vytvořit funkci XLOOKUP, která vyhledá název ve sloupci Rep. 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 vyhledávací buňka
- 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
-
Když stisknete Enter a zadejte název obchodního zástupce, v buňce Celkový výsledek se zobrazí první výsledek v tabulce daného obchodního zástupce.
-
Pokud chcete vyhledat nejnovější prodej (protože tabulka je seřazená podle data v opačném pořadí), změňte poslední argument XLOOKUP na -1, který zahájí vyhledávání z poslední buňky ve vyhledávacím poli a místo toho vám poskytne tento výsledek.
-
Tento příklad ukazuje podobné hledání, které byste mohli provést pomocí funkce VLOOKUP pomocí sloupce Rep jako prvního sloupce vyhledávací tabulky. XLOOKUP vám však umožňuje vyhledat libovolný sloupec v obou směrech. Například pokud chcete najít obchodního zástupce, který prodal první objednávku pořadače roku, použijete následující funkci XLOOKUP: = XLOOKUP (I2, D2: D44, C2: C44,0,1)
- D2: Ukazuje na buňku vyhledávání položek
- D2: D44: Toto je sloupec Položka, což je vyhledávací pole
- C2: C44: Toto je sloupec Rep, což je návratové pole 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 roku.
Proveďte vertikální a horizontální shodu s XLOOKUP
Další možností XLOOKUP, kterou VLOOKUP není schopen, je schopnost provádět jak vertikální, tak horizontální vyhledávání, což znamená, že můžete hledat položku dolů ve sloupci a také přes řádek. Tato funkce duálního hledání je účinnou náhradou za další funkce aplikace Excel, jako je INDEX, MATCH nebo HLOOKUP.
-
V následující ukázkové tabulce jsou prodeje pro každého obchodního zástupce rozděleny podle čtvrtletí. Pokud byste chtěli vidět prodeje ve třetím čtvrtletí pro konkrétního obchodního zástupce, bez funkce XLOOKUP, tento druh vyhledávání by byl obtížný.
-
S funkcí XLOOKUP je tento druh vyhledávání snadný. Pomocí funkce XLOOKUP s následným spojením můžete vyhledat prodeje ve třetím č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 Rep
- B2: B42: Toto je sloupec Položka, což je vyhledávací pole sloupce
- K2: Ukazuje na vyhledávací buňku Quarter
- C1: H1: Toto je pole pro vyhledávání řádků
- C2: H42: Toto je vyhledávací pole pro částku dolaru v každém čtvrtletí
Tato vnořená funkce XLOOKUP nejprve identifikuje obchodního zástupce a propojená funkce XLOOKUP identifikuje požadovanou čtvrtinu. Návratová hodnota bude buňka, kde se tyto dva zachytí.
-
Výsledkem tohoto vzorce jsou čtvrtletní výdělky zástupce se jménem Thompson.
Pomocí funkce XLOOKUP
Funkce XLOOKUP je k dispozici pouze předplatitelům Office Insider, ale brzy bude k dispozici všem předplatitelům Microsoft 365. Pokud si chcete funkci sami vyzkoušet, můžete se stát Office Insider. Vybrat Soubor > Účet, poté vyberte Office Insider rozevírací seznam k odběru. Jakmile se připojíte k programu Office Insider, vaše nainstalovaná verze aplikace Excel obdrží všechny nejnovější aktualizace a můžete začít používat funkci XLOOKUP.
