Ako používať funkciu Excel XLOOKUP -

Zhrnutie

Funkcia Excel XLOOKUP je moderná a flexibilná náhrada starších funkcií, ako sú VLOOKUP, HLOOKUP a LOOKUP. XLOOKUP podporuje približné a presné priraďovanie, zástupné znaky (*?) Pre čiastočné zhody a vyhľadávanie vo zvislom alebo vodorovnom rozsahu.

Účel

Vyhľadajte hodnoty v rozsahu alebo poli

Návratová hodnota

Zhodné hodnoty z návratového poľa

Syntax

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Argumenty

  • lookup - vyhľadávacia hodnota.
  • lookup_array - pole alebo rozsah, ktorý sa má vyhľadať.
  • return_array - pole alebo rozsah, ktorý sa má vrátiť.
  • not_found - (voliteľné) Hodnota, ktorá sa má vrátiť, ak sa nenájde zhoda.
  • match_mode - (voliteľné) 0 = presná zhoda (predvolené), -1 = presná zhoda alebo najbližšia najmenšia, 1 = presná zhoda alebo ďalšia väčšia, 2 = zástupná zhoda.
  • search_mode - (voliteľné) 1 = hľadanie od prvého (predvolené), -1 = hľadanie od posledného, ​​2 = binárne hľadanie vzostupne, -2 = binárne hľadanie zostupne.

Verzia

Excel 365

Poznámky k použitiu

XLOOKUP je moderná náhrada za funkciu VLOOKUP. Jedná sa o flexibilnú a všestrannú funkciu, ktorú je možné použiť v najrôznejších situáciách.

XLOOKUP dokáže vyhľadať hodnoty vo zvislom alebo vodorovnom rozsahu, môže vykonávať približné a presné zhody a pre čiastočné zhody podporuje zástupné znaky (*?). Okrem toho XLOOKUP môže vyhľadávať údaje začínajúce od prvej hodnoty alebo od poslednej hodnoty (pozri typ zhody a podrobnosti režimu vyhľadávania nižšie). V porovnaní so staršími funkciami ako VLOOKUP, HLOOKUP a LOOKUP ponúka XLOOKUP niekoľko kľúčových výhod.

Správa sa nenašla

Keď program XLOOKUP nenájde zhodu, vráti chybu # N / A, podobne ako iné funkcie zhody v programe Excel. Na rozdiel od ostatných funkcií zhody podporuje XLOOKUP voliteľný argument s názvom not_found, ktorým je možné potlačiť chybu # N / A, keď by sa inak objavila. Typické hodnoty pre not_found môžu byť „Nenašli sa“, „Žiadna zhoda“, „Žiadny výsledok“ atď. Pri zadávaní hodnoty pre not_found vložte text do dvojitých úvodzoviek („“).

Poznámka: Buďte opatrní, ak zadáte prázdny reťazec ("") pre not_found. Ak nenájdete žiadnu zhodu, XLOOKUP namiesto # N / A nezobrazí nič. Ak chcete vidieť chybu # N / A, keď sa nenájde zhoda, argument úplne vynechajte.

Typ zhody

V predvolenom nastavení vykoná XLOOKUP presnú zhodu. Správanie zhody je riadené voliteľným argumentom s názvom match_type, ktorý má nasledujúce možnosti:

Typ zhody Správanie
0 (predvolené) Presná zhoda. Vráti číslo N / A, ak nebude zápas.
-1 Presná zhoda alebo ďalšia menšia položka.
1 Presná zhoda alebo ďalšia väčšia položka.
2 Zástupný znak (*,?, ~)

Režim vyhľadávania

V predvolenom nastavení sa XLOOKUP začne porovnávať od prvej hodnoty údajov. Správanie pri vyhľadávaní je riadené voliteľným argumentom s názvom search_mode , ktorý poskytuje nasledujúce možnosti:

Režim vyhľadávania Správanie
1 (predvolené) Hľadajte od prvej hodnoty
-1 Hľadať od poslednej hodnoty (vzad)
2 Binárne hodnoty vyhľadávania zoradené vzostupne
-2 Binárne hodnoty vyhľadávania zoradené zostupne

Binárne vyhľadávania sú veľmi rýchle, ale údaje je potrebné triediť podľa potreby. Ak údaje nie sú zoradené správne, binárne vyhľadávanie môže vrátiť neplatné výsledky, ktoré vyzerajú úplne normálne.

Príklad č. 1 - základná presná zhoda

V predvolenom nastavení vykoná XLOOKUP presnú zhodu. V nasledujúcom príklade sa XLOOKUP používa na načítanie predaja na základe presnej zhody vo filme. Vzorec v H5 je:

=XLOOKUP(H4,B5:B9,E5:E9)

Podrobnejšie vysvetlenie tu.

Príklad č. 2 - základná približná zhoda

Ak chcete povoliť približnú zhodu, zadajte hodnotu pre argument „match_mode“. V nasledujúcom príklade sa program XLOOKUP používa na výpočet zľavy na základe množstva, ktoré vyžaduje približnú zhodu. Vzorec vo F5 dodáva -1 pre match_mode, aby umožnil približnú zhodu s chovaním „presná zhoda alebo najbližšia najmenšia“:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Podrobnejšie vysvetlenie tu.

Príklad č. 3 - viacnásobné hodnoty

Program XLOOKUP môže pre rovnakú zhodu vrátiť súčasne viac ako jednu hodnotu. Nasledujúci príklad ukazuje, ako je možné nakonfigurovať XLOOKUP tak, aby vrátil tri zhodné hodnoty pomocou jedného vzorca. Vzorec v C5 je:

=XLOOKUP(B5,B8:B15,C8:E15)

Všimnite si, že návratové pole (C8: E15) obsahuje 3 stĺpce: Prvý, Posledný, Oddelenie. Všetky tri hodnoty sa vrátia a vylejú sa do rozsahu C5: E5.

Príklad č. 4 - obojsmerné vyhľadávanie

XLOOKUP možno použiť na vykonanie obojsmerného vyhľadávania vložením jedného XLOOKUPu do druhého. V príklade nižšie „vnútorný“ XLOOKUP načíta celý riadok (všetky hodnoty pre Glass), ktorý sa odovzdáva „vonkajšiemu“ XLOOKUPU ako návratové pole. Vonkajší XLOOKUP nájde príslušnú skupinu (B) a vráti zodpovedajúcu hodnotu (17,25) ako konečný výsledok.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Viac podrobností tu.

Príklad č. 5 - správa sa nenašla

Rovnako ako ostatné vyhľadávacie funkcie, aj keď XLOOKUP nenájde hodnotu, vráti chybu # N / A. Ak chcete namiesto # N / A zobraziť vlastnú správu, zadajte hodnotu voliteľného argumentu „nenájdené“, ktorý je uvedený v úvodzovkách („“). Ak chcete napríklad na základe nižšie uvedeného pracovného hárka zobraziť text „Nenašiel sa“, keď sa nenájde žiadny zodpovedajúci film, použite:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Túto správu môžete ľubovoľne upraviť: „Žiadna zhoda“, „Film sa nenašiel“ atď.

Príklad č. 6 - komplexné kritériá

Vďaka schopnosti natívne spracovať polia je možné XLOOKUP používať s komplexnými kritériami. V nasledujúcom príklade sa XLOOKUP zhoduje s prvým záznamom, kde: účet začína na „x“ a región je „východ“ a mesiac nie je apríl:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Podrobnosti: (1) jednoduchý príklad, (2) zložitejší príklad.

Výhody XLOOKUPU

XLOOKUP ponúka niekoľko dôležitých výhod, najmä v porovnaní s VLOOKUP:

  • Program XLOOKUP môže vyhľadávať údaje napravo alebo naľavo od vyhľadávacích hodnôt
  • XLOOKUP môže vrátiť viac výsledkov (príklad č. 3 vyššie)
  • XLOOKUP predvolené na presnú zhodu (VLOOKUP predvolené na približnú hodnotu)
  • XLOOKUP môže pracovať s vertikálnymi a horizontálnymi údajmi
  • XLOOKUP môže vykonávať spätné vyhľadávanie (od prvého po prvý)
  • XLOOKUP môže vrátiť celé riadky alebo stĺpce, nielen jednu hodnotu
  • XLOOKUP môže natívne pracovať s poľami na uplatnenie zložitých kritérií

Poznámky

  1. XLOOKUP môže pracovať s vertikálnym aj horizontálnym poľom.
  2. Ak nenájdete vyhľadávaciu hodnotu, XLOOKUP vráti # N / A.
  3. Pole lookup_array musí mať dimenziu kompatibilnú s argumentom return_array , inak funkcia XLOOKUP vráti #VALUE!
  4. Ak sa medzi zošitmi používa XLOOKUP, musia byť oba zošity otvorené, inak XLOOKUP vráti #REF !.
  5. Rovnako ako funkcia INDEX, aj XLOOKUP vo výsledku vráti referenciu.

Podobné videá

Základný príklad XLOOKUPU V tomto videu nastavíme funkciu XLOOKUP so základným príkladom. Podľa názvu mesta nájdeme krajinu a populáciu. Základná približná zhoda XLOOKUPU V tomto videu nastavíme funkciu XLOOKUP na vykonanie približnej zhody s cieľom vypočítať zľavu na základe množstva. XLOOKUP s logickou logikou V tomto videu sa pozrieme na to, ako používať funkciu XLOOKUP s logickou logikou na použitie viacerých kritérií. XLOOKUP s viacerými vyhľadávacími hodnotami V tomto videu nastavíme XLOOKUP na vrátenie viacerých hodnôt v dynamickom poli tým, že namiesto jednej vyhľadávacej hodnoty poskytneme celý rad vyhľadávacích hodnôt.

Zaujímavé články...