Excel

Funkcia Excel XLOOKUP

Excel Xlookup Function

Funkcia Excel XLOOKUPZhrnutie

Funkcia Excel XLOOKUP je modernou a flexibilnou náhradou za staršie funkcie ako VLOOKUP, HLOOKUP a LOOKUP. XLOOKUP podporuje približné a presné párovanie, zástupné znaky (*?) Pre čiastkové zhody a vyhľadávania vo vertikálnych alebo horizontálnych rozsahoch.



Účel Hodnoty vyhľadávania v rozsahu alebo poli Návratová hodnota Zodpovedajúce hodnoty z návratového poľa Syntax = XLOOKUP (vyhľadávanie, hľadanie_pole, návratové_polo, [not_found], [match_mode], [search_mode]) Argumenty
  • vyhľadať - vyhľadávacia hodnota.
  • lookup_array - Pole alebo rozsah, ktorý sa má vyhľadať.
  • return_array - Pole alebo rozsah, ktorý sa má vrátiť.
  • nenájdené - [voliteľné] Hodnota na vrátenie, ak sa nenašla žiadna zhoda.
  • režim_zhody - [voliteľné] 0 = presná zhoda (predvolené), -1 = presná zhoda alebo najbližšia najmenšia, 1 = presná zhoda alebo najbližšia väčšia, 2 = zhoda so zástupnými znakmi.
  • režim hľadania -[voliteľné] 1 = vyhľadávanie od prvého (predvolené), -1 = vyhľadávanie od posledného, ​​2 = binárne vyhľadávanie vzostupne, -2 = binárne vyhľadávanie zostupné.
Verzia Excel 365 Poznámky k použitiu

XLOOKUP je modernou náhradou funkcie VLOOKUP. Je to flexibilná a všestranná funkcia, ktorú je možné použiť v rôznych situáciách.

XLOOKUP dokáže nájsť hodnoty vo vertikálnych alebo horizontálnych rozsahoch, dokáže vykonávať približné a presné zhody a podporuje zástupné znaky (*?) Pre čiastočné zhody. XLOOKUP môže navyše vyhľadávať údaje od prvej hodnoty alebo od poslednej hodnoty (pozri typ zhody a podrobnosti o režime vyhľadávania nižšie). V porovnaní so staršími funkciami ako VLOOKUP , HLOOKUP a VYHĽADAŤ , Ponúka XLOOKUP niekoľko kľúčových výhod .





Správa sa nenašla

Keď XLOOKUP nemôže nájsť zhodu, vráti chybu #N/A, podobne ako ostatné funkcie zhody v programe Excel. Na rozdiel od ostatných funkcií zhody XLOOKUP podporuje voliteľný argument s názvom nenájdené ktoré je možné použiť na odstránenie chyby #N/A, keď by sa inak zobrazila. Typické hodnoty pre nenájdené môže byť „Nenašlo sa“, „Žiadna zhoda“, „Žiadny výsledok“ atď. Pri zadávaní hodnoty pre nenájdené , uzavrite text do dvojitých úvodzoviek ('').

Poznámka: Buďte opatrní, ak zadáte prázdny reťazec ('') pre not_found. Ak sa nenájde žiadna zhoda, 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í XLOOKUP vykoná súbor presná zhoda . Správanie zhody je riadené voliteľným argumentom s názvom typ_zhody, ktorý má nasledujúce možnosti:

ako triediť najväčšie od najmenších v programe Excel
Typ zhody Správanie
0 (predvolené) Presná zhoda. Ak nedôjde k zhode, vráti sa #N/A.
-1 Presná zhoda alebo nasledujúca menšia položka.
1 Presná zhoda alebo nasledujúca väčšia položka.
2 Zástupný znak (*,?, ~)

Režim vyhľadávania

V predvolenom nastavení začne XLOOKUP zodpovedať od prvej hodnoty údajov. Správanie pri vyhľadávaní je riadené voliteľným argumentom s názvom režim hľadania , ktorý ponúka nasledujúce možnosti:

Režim vyhľadávania Správanie
1 (predvolené) Hľadať od prvej hodnoty
-1 Hľadať podľa poslednej hodnoty (obrátiť)
2 Hodnoty binárneho vyhľadávania zoradené vzostupne
-2 Hodnoty binárneho vyhľadávania zoradené zostupne

Binárne vyhľadávanie je veľmi rýchle, ale údaje je potrebné zoradiť 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í XLOOKUP vykoná presnú zhodu. V nižšie uvedenom príklade sa XLOOKUP používa na získavanie tržieb na základe presnej zhody vo filme. Vzorec v H5 je:

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

XLOOKUP - príklad základnej presnej zhody

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 nižšie uvedenom príklade sa XLOOKUP používa na výpočet zľavy na základe množstva, ktoré vyžaduje približnú zhodu. Vzorec v F5 dodáva -1 pre režim match_mode na umožnenie približnej zhody so správaním „presná zhoda alebo najbližšia najmenšia“:

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

XLOOKUP - základný približný príklad zhody

Podrobnejšie vysvetlenie tu .

Príklad č. 3 - viac hodnôt

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

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

XLOOKUP - príklad s viacerými hodnotami

Všimnite si, že návratové pole (C8: E15) obsahuje 3 stĺpce: prvý, posledný, oddelenie. Vrátia sa všetky tri hodnoty a hra do rozsahu C5: E5.

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

XLOOKUP je možné použiť na obojsmerné vyhľadávanie, hniezdenie jeden XLOOKUP vo vnútri druhého. V nižšie uvedenom príklade „vnútorný“ XLOOKUP získa celý riadok (všetky hodnoty pre Glass), ktorý sa odovzdá „vonkajšiemu“ XLOOKUP 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))

XLOOKUP - príklad obojsmerného vyhľadávania

Viac podrobností tu .

Príklad č. 5 - správa nenájdená

Rovnako ako ostatné vyhľadávacie funkcie, ak XLOOKUP nenájde hodnotu, vráti chybu #N/A. Ak chcete namiesto #N/A zobraziť vlastnú správu, zadajte hodnotu pre voliteľný argument „nenájdený“ uzavretý do dvojitých úvodzoviek (''). Ak chcete napríklad zobraziť „Nenašlo sa“, ak sa nenašiel zodpovedajúci film, na základe nižšie uvedeného pracovného hárka použite:

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

XLOOKUP - príklad nebol nájdený

Túto správu si môžete prispôsobiť, ako sa vám páči: „Žiadna zhoda“, „Film sa nenašiel“ atď.

ako urobiť koláčový graf v programe Excel

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

Vďaka schopnosti natívnej manipulácie s poľami je možné XLOOKUP používať so zložitými kritériami. V nižšie uvedenom príklade XLOOKUP zodpovedá prvému záznamu, kde: účet začína znakom „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)

XLOOKUP - príklad komplexných kritérií

Podrobnosti: (1) jednoduchý príklad , (2) komplexnejší príklad .

Výhody XLOOKUP

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

  • XLOOKUP môže vyhľadávať údaje napravo alebo vľavo vyhľadávacích hodnôt
  • XLOOKUP môže vrátiť viacero výsledkov (príklad č. 3 vyššie)
  • Predvolená hodnota pre XLOOKUP je presná zhoda (predvolená hodnota pre funkciu VLOOKUP je približná)
  • XLOOKUP môže pracovať s vertikálnymi aj horizontálnymi údajmi
  • XLOOKUP môže vykonávať spätné vyhľadávanie (od prvého po prvé)
  • XLOOKUP môže byť vrátený celý riadky alebo stĺpce , nielen jedna hodnota
  • XLOOKUP môže pracovať s pole natívne uplatňovať komplexné kritériá

Poznámky

  1. XLOOKUP môže pracovať s vertikálnymi aj horizontálnymi poľami.
  2. Ak nenájdete hodnotu vyhľadávania, XLOOKUP vráti #N/A.
  3. The lookup_array musí mať rozmer kompatibilný s formátom return_array argument, inak XLOOKUP vráti #HODNOTU!
  4. Ak sa medzi zošitmi používa XLOOKUP, oba zošity musia byť otvorené, inak XLOOKUP vráti #REF !.
  5. Ako Funkcia INDEX , XLOOKUP vráti a odkaz ako výsledok.
XLOOKUP je nová funkcia dostupná v Excel 365 iba.


^