Ako používať funkciu XLOOKUP v Exceli 2016

Excel 2016 pre predplatiteľov Office 365 v systémoch Windows a Mac teraz podporuje novú funkciu XLOOKUP, ktorá je ponúkaná ako podstatne jednoduchšia a všestrannejšia náhrada za veľmi populárnu (ale často falšovanú) funkciu vertikálneho vyhľadávania VLOOKUP (neviem, čo X v XLOOKUP). znamená; eXtensive, možno?).

Pre tých z vás, ktorí ešte nie sú oboznámení s funkciou VLOOKUP (považovanou za tretiu najpoužívanejšiu funkciu hneď po SUM a AVERAGE), táto funkcia vyhľadáva vertikálne podľa riadkov v ľavom stĺpci určenej vyhľadávacej tabuľky zhora nadol, kým nenájde hodnotu v vyhľadávací stĺpec označený číslom posunu, ktoré sa zhoduje s tým, ktoré hľadáte, alebo ho prevyšuje. Aj keď je funkcia VLOOKUP mimoriadne užitočná na vyhľadávanie konkrétnych položiek v dlhom zozname alebo stĺpci tabuľky údajov vo vašom pracovnom hárku, má niekoľko obmedzení, ktoré táto nová funkcia vyhľadávania nezdieľa, ako napríklad XLOOKUP:

• Predvolené nastavenie na nájdenie presných zhôd pre vašu vyhľadávaciu hodnotu v rozsahu vyhľadávania
• Dokáže v tabuľke vyhľadávať vertikálne (podľa riadka) aj horizontálne (podľa stĺpca), čím nahrádza potrebu použitia funkcie HLOOKUP pri horizontálnom vyhľadávaní podľa stĺpcov
• Dokáže vyhľadávať vľavo alebo vpravo, takže rozsah vyhľadávania vo vašej vyhľadávacej tabuľke nemusí byť umiestnený v stĺpci naľavo od stĺpca označeného ako návratový rozsah, aby funkcia fungovala
• Keď sa použije predvolená presná zhoda, funguje to aj vtedy, keď hodnoty v rozsahu vyhľadávania nie sú zoradené v určitom poradí
• Môže vyhľadávať od spodného riadku po horný v rozsahu vyhľadávacieho poľa pomocou voliteľného argumentu režimu vyhľadávania

Funkcia XLOOKUP má päť možných argumentov, z ktorých prvé tri sú povinné a posledné dva voliteľné, s použitím nasledujúcej syntaxe:

XLOOKUP ( lookup_value , prohledávané_pole , return_array , [ match_mode ], [ search_mode ])

Požadovaný argument lookup_value označuje hodnotu alebo položku, ktorú hľadáte. Požadovaný argument poľa look_up určuje rozsah buniek, ktoré sa majú vyhľadať pre túto vyhľadávaciu hodnotu, a argument return_array určuje rozsah buniek obsahujúcich hodnotu, ktorú chcete vrátiť, keď Excel nájde presnú zhodu.

* Majte na pamäti, že pri určovaní argumentov lookup_array a return_array vo vašej funkcii XLOOKUP musia mať oba rozsahy rovnakú dĺžku, inak Excel vráti #HODNOTA! chyba vo vašom vzorci. To je ďalší dôvod, prečo by ste mali pri definovaní týchto argumentov používať názvy rozsahov alebo názvy stĺpcov určených údajových tabuliek namiesto toho, aby ste na ne poukazovali alebo zadávali odkazy na bunky .

Voliteľný argument match_mode môže obsahovať ktorúkoľvek z nasledujúcich štyroch hodnôt:

• 0 pre presnú zhodu (predvolená hodnota, rovnaká, ako keď nie je určený žiadny argument match_mode )
• -1 pre presnú zhodu alebo nasledujúcu nižšiu hodnotu
• 1 pre presnú zhodu alebo ďalšiu vyššiu hodnotu
• 2 pre čiastočnú zhodu s použitím zástupných znakov pripojený k referenčnej bunky v lookup_value argumentu

Voliteľný argument search_mode môže obsahovať ktorúkoľvek z nasledujúcich štyroch hodnôt:

• 1 na vyhľadávanie od prvého po posledného, ​​to znamená zhora nadol (predvolené, rovnaké, ako keď nie je určený žiadny argument search_mode )
• -1 na vyhľadávanie od posledného k prvému, teda zdola nahor
• 2 pre binárne vyhľadávanie vo vzostupnom poradí
• -2 pre binárne vyhľadávanie v zostupnom poradí

Najlepší spôsob, ako pochopiť silu a všestrannosť novej funkcie XLOOKUP, je vidieť ju v akcii v hárku programu Excel. Na nasledujúcom obrázku mám pracovný hárok s jednoduchou tabuľkou s údajmi o predaji za rok 2019 usporiadanou podľa krajín. Ak chcete použiť XLOOKUP na vrátenie celkového predaja z tejto tabuľky v bunke E4 na základe krajiny, ktorú zadáte do bunky D4 hárka, vykonajte tieto kroky:

Umiestnite bunkový kurzor do bunky E4 hárka

Kliknite na možnosť Vyhľadať a referencie na karte Vzorce a potom na položku XLOOKUP v dolnej časti rozbaľovacej ponuky, čím otvoríte dialógové okno Argumenty funkcií.

Kliknite na bunku D4 v hárku a zadajte odkaz na bunku do textového poľa argumentu Lookup_value.

Stlačením klávesu Tab vyberte textové pole argumentu Lookup_array, potom kliknite na bunku A4 a podržte stlačený kláves Shift a súčasne stláčajte kláves Ctrl-šípka nadol, aby ste vybrali A4:A8 ako rozsah na vyhľadávanie (pretože rozsah A3:B8 je definovaný ako tabuľka údajov programu Excel, Tabuľka1[Krajina] sa zobrazí v textovom poli namiesto rozsahu A4:A8).

Stlačením klávesu Tab vyberte textové pole argumentu Return_array, potom kliknite na bunku B4 a podržte stlačený kláves Shift a súčasne stláčajte kláves Ctrl-šípka nadol, aby ste vybrali B4:B8 ako rozsah obsahujúci hodnoty, ktoré sa majú vrátiť na základe výsledkov vyhľadávania (zobrazí sa ako Tabuľka1 [Celkový predaj] v textovom poli).

Kliknutím na tlačidlo OK zadajte vzorec XLOOKUP do bunky E4.

Vytvorenie vzorca s XLOOKUP v bunke E4, ktorý vráti tržby na základe krajiny zadanej v bunke D4.

Excel zadá vzorec XLOOKUP do bunky E4 pracovného hárka a vráti 4900 ako výsledok, pretože Kostarika je momentálne zadaná do vyhľadávacej bunky D4 a ako môžete vidieť v tabuľke predajov za rok 2019, toto je skutočne celkový predaj uskutočnený pre túto krajinu.

Pretože XLOOKUP funguje sprava doľava rovnako dobre ako zľava doprava, môžete túto funkciu rovnako dobre použiť na vrátenie krajiny z tejto predajnej tabuľky na základe konkrétneho čísla predaja. Nasledujúci obrázok ukazuje, ako to urobiť. Tentoraz vytvoríte vzorec XLOOKUP v bunke D4 a označíte hodnotu zadanú v bunke E4 (v tomto prípade 11 000) ako argument vyhľadávacej_hodnoty.

Okrem toho zadáte -1 ako argument match_mode, aby ste prepísali predvolenú presnú zhodu funkcie, takže Excel vráti krajinu s presnou zhodou s hodnotou predaja zadanou vo vyhľadávacej bunke E4 alebo tej s najbližším nižším celkovým predajom (Mexiko s 10 000 USD v tomto prípade, pretože v tejto tabuľke nie je žiadna krajina s celkovým predajom 11 000 USD). Bez určenia argumentu match_mode pre tento vzorec by Excel vrátil ako výsledok #NA, pretože v tejto tabuľke predajov neexistuje presná zhoda s hodnotou 11 000 USD.

Vytvorenie vzorca s XLOOKUP v bunke D4, ktorý vráti krajinu na základe predaja zadaného v bunke E4

Pretože funkcia XLOOKUP je rovnako pohodlná pri horizontálnom vyhľadávaní podľa stĺpca ako pri zvislom po riadku, môžete ju použiť na vytvorenie vzorca, ktorý vykoná obojsmerné vyhľadávanie (nahradením potreby vytvoriť vzorec, ktorý kombinuje funkcie INDEX a MATCH ako v minulosti). Nasledujúci obrázok, ktorý obsahuje tabuľku plánu výroby na rok 2019 pre čísla dielov, AB-100 až AB-103 na mesiace apríl až december, ukazuje, ako sa to robí.

Vytvorenie vzorca s vnorenými funkciami XLOOKUP na vrátenie počtu jednotiek vyrobených pre diel v konkrétnom mesiaci

V bunke B12 som vytvoril nasledujúci vzorec:

=XLOOKUP(vyhľadávanie_časti,$A$3:$A$6,XLOOKUP(vyhľadávanie_dátumov,$B$2:$J$2,$B$3:$J$6))

Tento vzorec začína definovaním funkcie XLOOKUP, ktorá vertikálne hľadá podľa riadka presnú zhodu so záznamom dielu vykonaným v bunke s názvom part_lookup (v tomto prípade bunka B10) v rozsahu buniek $A$3:$A$6 produkčnej tabuľky. . Všimnite si však, že argument return_array pre túto pôvodnú funkciu LOOKUP je sám o sebe druhou funkciou XLOOKUP.

Táto druhá, vnorená funkcia XLOOKUP vyhľadáva v rozsahu buniek $B$2:$J$2 horizontálne podľa stĺpca, aby sa presne zhodovala s dátumom zadaným v bunke s názvom date_lookup (v tomto prípade bunka B11). Argument return_array pre túto druhú, vnorenú funkciu XLOOKUP je $B$3:$J$6, rozsah buniek všetkých produkčných hodnôt v tabuľke.

Tento vzorec funguje tak, že Excel najprv vypočíta výsledok druhej, vnorenej funkcie XLOOKUP vykonaním horizontálneho vyhľadávania, ktoré v tomto prípade vráti pole v rozsahu buniek D3: D6 stĺpca Jun-19 (s hodnotami: 438, 153, 306 a 779) ako jeho výsledok. Tento výsledok sa zas stane argumentom return_array pre pôvodnú funkciu XLOOKUP, ktorá vykoná vertikálne vyhľadávanie podľa riadka na presnú zhodu so záznamom čísla dielu vykonaným v bunke B11 (s názvom part_lookup). Pretože v tomto príklade táto bunka part_lookup obsahuje AB-102, vzorec vráti iba hodnotu produkcie Jun-19, 306, z výsledku druhej, ďalšej funkcie XLOOKUP.

Tu to máte! Prvý pohľad na XLOOKUP, výkonnú, všestrannú a pomerne jednoducho použiteľnú novú vyhľadávaciu funkciu, ktorá dokáže nielen vyhľadávať jednu hodnotu vykonávanú funkciami VLOOKUP a HLOOKUP, ale aj obojsmerné vyhľadávanie hodnôt vykonávané kombináciou Funkcie INDEX a MATCH.

* Funkcia XLOOKUP bohužiaľ nie je spätne kompatibilná so staršími verziami programu Microsoft Excel, ktoré podporujú iba funkcie VLOOKUP a HLOOKUP, alebo kompatibilná s aktuálnymi verziami, ktoré ju ešte neobsahujú ako jednu zo svojich vyhľadávacích funkcií, ako napríklad Excel 2019 a Excel Online . To znamená, že ak zdieľate zošit obsahujúci vzorce XLOOKUP so spolupracovníkmi alebo klientmi, ktorí používajú verziu Excelu, ktorá neobsahuje túto novú funkciu vyhľadávania, všetky tieto vzorce vrátia #NAME? chybové hodnoty, keď otvoria jeho pracovný hárok.