1. SoftwareMicrosoft OfficeExcelAko používať funkciu XLOOKUP v programe Excel 2016

Autor: Greg Harvey

Odberatelia programu Excel 2016 pre Office 365 v systémoch Windows a Mac teraz podporujú novú funkciu XLOOKUP, ponúkanú ako podstatne jednoduchšia a univerzálnejšia náhrada za veľmi populárnu (zatiaľ často malignovanú) funkciu vertikálneho vyhľadávania, VLOOKUP (neviem, čo X v XLOOKUPe) znamená, eXtenzívne, možno?).

Pre tých z vás, ktorí ešte nie sú oboznámení s programom VLOOKUP (považovaný za tretiu najpoužívanejšiu funkciu hneď po SUM a AVERAGE), táto funkcia vyhľadáva vertikálne po riadku v ľavom stĺpci určenej vyhľadávacej tabuľky zhora nadol, až kým nenájde hodnotu v vyhľadávací stĺpec určený číslom ofsetu, ktorý sa zhoduje alebo presahuje ten, ktorý hľadáte. Aj keď je to mimoriadne užitočné pri vyhľadávaní konkrétnych položiek v dlhom zozname alebo stĺpci tabuľky s údajmi v pracovnom hárku, funkcia VLOOKUP má niekoľko obmedzení, ktoré táto nová vyhľadávacia funkcia nezdieľa, ako napríklad XLOOKUP:

  • Predvolené nájdenie presných zhôd pre vašu hodnotu vyhľadávania v rozsahu vyhľadávania Dokáže prehľadávať vertikálne (podľa riadku) aj horizontálne (podľa stĺpca) v tabuľke, čím nahrádza potrebu používať funkciu HLOOKUP pri horizontálnom vyhľadávaní podľa stĺpca. Môž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 rozsah návratu, aby funkcia fungovala Pri použití predvoleného nastavenia presnej zhody funguje, aj keď hodnoty v rozsahu vyhľadávania nie sú zoradené v konkrétnom poradí Môže vyhľadávať od dolného riadku po vrchol 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é, pričom používa nasledujúcu syntax:

XLOOKUP (hľadať, prohledávané_pole, return_array, [match_mode], [search_mode])

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

* Nezabudnite pri určovaní argumentov lookup_array a return_array vo funkcii XLOOKUP oba rozsahy musia byť rovnako dlhé, inak program Excel vráti # HODNU! chyba vo vašom vzorci. Toto je ešte viac dôvod na to, aby ste pri definovaní týchto argumentov používali názvy oblastí alebo stĺpcov určených tabuliek údajov namiesto toho, aby ste ich poukazovali alebo písali do ich odkazov 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 najbližšiu menšiu hodnotu 1 pre presnú zhodu alebo nasledujúcu vyššiu hodnotu 2 pre čiastočnú zhodu pomocou zástupných znakov spojených s odkazom na bunku v argumente lookup_value

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ý, tj 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, to znamená 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 univerzálnosť novej funkcie XLOOKUP, je vidieť ju v akcii v pracovnom hárku programu Excel. Na nasledujúcom obrázku mám pracovný hárok s jednoduchou tabuľkou údajov o predaji z roku 2019 usporiadanou podľa krajín. Ak chcete pomocou nástroja XLOOKUP vrátiť celkový predaj z tejto tabuľky v bunke E4 na základe krajiny, ktorú zadáte do bunky D4 pracovného hárka, postupujte takto:

  1. Umiestnite kurzor bunky do bunky E4 pracovného hárka Kliknutím na možnosť Vyhľadať a referenčné na karte Vzorce a potom XLOOKUP v dolnej časti rozbaľovacej ponuky otvorte dialógové okno Argumenty funkcií. Kliknutím na bunku D4 v pracovnom hárku zadáte jej odkaz na bunku do textového poľa Argument Lookup_value. Stlačením klávesu Tab vyberte textové pole argumentu Lookup_array, potom kliknite na bunku A4 a podržte kláves Shift, keď stlačíte klávesovú skratku Ctrl-down, aby ste vybrali A4: A8 ako rozsah na vyhľadávanie (pretože rozsah A3: B8 je definovaný ako tabuľka údajov Excelu, 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 kláves Shift, keď stlačíte klávesovú skratku Ctrl-down, aby ste vybrali B4: B8 ako rozsah obsahujúci hodnoty, ktoré sa majú vrátiť, na základe výsledkov vyhľadávania (ktoré sa zobrazuje ako Tabuľka 1 [Celkový predaj] v textovom poli).

Kliknite na OK a do bunky E4 zadajte vzorec XLOOKUP.

Tvorba vzorca XLOOKUP

Excel zadá vzorec XLOOKUP do bunky E4 pracovného hárka a vráti 4900 ako výsledok, pretože Kostarika je v súčasnosti zadaná do vyhľadávacej bunky D4 a ako vidíte v tabuľke predaja 2019, skutočne ide o celkový predaj uskutočnený pre túto krajinu.

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

Okrem toho zadáte -1 ako argument match_mode na prepísanie predvolenej presnej zhody funkcie tak, aby Excel vrátil krajinu s presnou zhodou s hodnotou predaja zadanou vo vyhľadávacej bunke E4 alebo krajinu s najbližšou nižšou celkovou tržbou (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 sa Excel vrátil ako výsledok #NA, pretože v tejto predajnej tabuľke neexistuje presná zhoda na 11 000 dolárov.

Vzorec XLOOKUP v D4

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

vnorené funkcie XLOOKUP

V bunke B12 som vytvoril nasledujúci vzorec:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6 XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

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

Táto druhá vnorená funkcia XLOOKUP prehľadáva rozsah buniek $ B $ 2: $ J $ 2 horizontálne podľa stĺpca, aby sa presne zhodoval so záznamom dátumu vykonaný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 najskôr 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 v stĺpci Jun-19 (s hodnotami: 438, 153, 306 a 779). Tento výsledok sa zase stáva argumentom return_array pre pôvodnú funkciu XLOOKUP, ktorá vykonáva vertikálne vyhľadávanie podľa riadku na presnú zhodu so záznamom čísla dielu vykonaného v bunke B11 (nazvaný part_lookup). Pretože v tomto príklade táto bunka part_lookup obsahuje AB-102, vzorec vráti iba výrobnú hodnotu 306 Jun-19 z výsledku druhej, nasledujúcej funkcie XLOOKUP.

Tam to máte! Prvý pohľad na XLOOKUP, výkonnú, všestrannú a pomerne ľahko použiteľnú novú vyhľadávaciu funkciu, ktorá dokáže nielen vykonávať jednohodnotové vyhľadávanie vykonávané funkciami VLOOKUP a HLOOKUP, ale aj obojsmerné vyhľadávanie hodnoty vykonávané kombináciou Fungujú tiež INDEX a MATCH.

* Funkcia XLOOKUP bohužiaľ nie je spätne kompatibilná s predchádzajúcimi verziami programu Microsoft Excel, ktoré podporujú iba funkcie VLOOKUP a HLOOKUP, ani kompatibilné s aktuálnymi verziami, ktoré ju zatiaľ 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 programu Excel, ktorá neobsahuje túto novú vyhľadávaciu funkciu, vrátia sa všetky tieto vzorce # NAME? chybové hodnoty pri otvorení jeho pracovného hárka.