Funkcia VLOOKUP bola vždy jednou z najvýkonnejších funkcií Excelu. Umožňuje vám vyhľadávať hodnoty v prvom stĺpci tabuľky a vracať hodnoty z polí napravo. Excel má však aj funkciu s názvom XLOOKUP, ktorá vám umožňuje vyhľadať hodnotu v ľubovoľnom stĺpci alebo riadku a vrátiť údaje z akéhokoľvek iného stĺpca.
Ako funguje XLOOKUP
Funkcia XLOOKUP sa používa oveľa jednoduchšie ako funkcia VLOOKUP, pretože namiesto zadania hodnoty pre stĺpec výsledkov môžete zadať celý rozsah.
Funkcia vám tiež umožňuje prehľadávať stĺpec aj riadok a nájsť hodnotu v pretínajúcej sa bunke.
Parametre funkcie XLOOKUP sú nasledovné:
=XLOOKUP (vyhľadávacia_hodnota, vyhľadávacie pole, návratové pole, [režim zhody], [režim_vyhľadávania])
- lookup_value: Hodnota, ktorú chcete vyhľadať
- lookup_array: Pole (stĺpec), v ktorom chcete hľadať
- return_array: Výsledok (stĺpec), z ktorého chcete získať hodnotu
- match_mode (voliteľné): Vyberte presnú zhodu (0), presnú zhodu alebo najbližšiu najmenšiu hodnotu (-1) alebo zhodu so zástupným znakom (2).
- search_mode (voliteľné): Vyberte, či sa má vyhľadávať od prvej položky v stĺpci (1), od poslednej položky v stĺpci (-1), binárne vyhľadávanie vzostupne (2) alebo binárne vyhľadávanie zostupne (-2).
Nasleduje niekoľko najbežnejších vyhľadávaní, ktoré môžete vykonať pomocou funkcie XLOOKUP.
Ako vyhľadať jeden výsledok pomocou XLOOKUP
Najjednoduchší spôsob použitia XLOOKUP je vyhľadať jeden výsledok pomocou dátového bodu z jedného stĺpca.
-
Tento príklad tabuľky je zoznam objednávok odoslaných obchodnými zástupcami vrátane položky, počtu jednotiek, nákladov a celkového predaja.
Image -
Ak chcete nájsť prvý predaj v zozname odoslanom konkrétnym obchodným zástupcom, môžete vytvoriť funkciu XLOOKUP, ktorá vyhľadá meno v stĺpci Zástupca. Funkcia vráti výsledok zo stĺpca Total. Funkcia XLOOKUP na to je:
=XLOOKUP(I2; C2:C44, G2:G44; 0, 1)
- I2: Ukazuje na Meno zástupcu
- C2:C44: Toto je stĺpec Rep, čo je pole vyhľadávania
- G2:G33: Toto je stĺpec Celkom, ktorý predstavuje pole návratov
- 0: Vyberie presnú zhodu
- 1: Vyberie prvý zápas vo výsledkoch
-
Keď stlačíte Enter a napíšete meno obchodného zástupcu, bunka Celkový výsledok vám zobrazí prvý výsledok v tabuľke pre daného obchodného zástupcu.
Image -
Ak chcete vyhľadať najnovší predaj (keďže tabuľka je zoradená podľa dátumu v opačnom poradí), zmeňte posledný argument XLOOKUP na - 1, čím sa spustí vyhľadávanie z poslednej bunky vo vyhľadávacom poli a namiesto toho vám poskytne tento výsledok.
Image -
Tento príklad ukazuje podobné vyhľadávanie, ktoré by ste mohli vykonať pomocou funkcie VLOOKUP s použitím stĺpca Rep ako prvého stĺpca vyhľadávacej tabuľky. XLOOKUP vám však umožňuje vyhľadávať ľubovoľný stĺpec v oboch smeroch. Napríklad, ak chcete nájsť obchodného zástupcu, ktorý predal prvú objednávku Binder v roku, použili by ste nasledujúcu funkciu XLOOKUP:
=XLOOKUP(I2; D2:D44, C2:C44; 0; 1)
- D2: Ukazuje na bunku vyhľadávania položky
- D2:D44: Toto je stĺpec Položka, ktorý predstavuje pole vyhľadávania
- C2:C44: Toto je stĺpec Rep, ktorý je návratovým poľom naľavo od vyhľadávacieho poľa
- 0: Vyberie presnú zhodu
- 1: Vyberie prvý zápas vo výsledkoch
-
Tentoraz bude výsledkom meno obchodného zástupcu, ktorý predal prvú objednávku viazača v roku.
Image
Vykonajte vertikálnu a horizontálnu zhodu pomocou XLOOKUP
Ďalšia funkcia XLOOKUP, ktorú funkcia VLOOKUP nedokáže, je schopnosť vykonávať vertikálne aj horizontálne vyhľadávanie, čo znamená, že môžete vyhľadávať položky v stĺpci aj v riadku.
Táto funkcia duálneho vyhľadávania je efektívnou náhradou za iné funkcie Excelu, ako je INDEX, MATCH alebo HLOOKUP.
-
V nasledujúcom príklade tabuľky sú tržby každého obchodného zástupcu rozdelené podľa štvrťrokov. Ak by ste chceli vidieť predaje za tretí štvrťrok pre konkrétneho obchodného zástupcu, bez funkcie XLOOKUP by bol tento druh vyhľadávania zložitý.
Image -
S funkciou XLOOKUP je tento druh vyhľadávania jednoduchý. Pomocou nasledujúcej funkcie XLOOKUP môžete vyhľadať predaje za tretí štvrťrok pre konkrétneho obchodného zástupcu:
=XLOOKUP(J2; B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Ukazuje na bunku vyhľadávania zástupcu
- B2:B42: Toto je stĺpec Položka, čo je pole vyhľadávania stĺpcov
- K2: Ukazuje na bunku vyhľadávania štvrťroku
- C1:H1: Toto je pole vyhľadávania riadkov
- C2:H42: Toto je pole vyhľadávania pre sumu v dolároch v každom štvrťroku
Táto vnorená funkcia XLOOKUP najprv identifikuje obchodného zástupcu a ďalšia funkcia XLOOKUP identifikuje požadovaný štvrťrok. Návratová hodnota bude je bunka, kde sa tieto dve zasahujú.
-
Výsledkom tohto vzorca je štvrťročný zárobok zástupcu s menom Thompson.
Image
Používanie funkcie XLOOKUP
Funkcia XLOOKUP je k dispozícii iba pre predplatiteľov Office Insider, ale čoskoro bude sprístupnená všetkým predplatiteľom Microsoft 365.
Ak si chcete funkciu vyskúšať sami, môžete sa stať členom Office Insider. Vyberte File > Účet, potom vyberte rozbaľovaciu ponuku Office Insider na odber.
Keď sa pripojíte k programu Office Insider, vaša nainštalovaná verzia Excelu dostane všetky najnovšie aktualizácie a môžete začať používať funkciu XLOOKUP.
