Kako uporabljati funkcijo Excel XLOOKUP -

Povzetek

Funkcija Excel XLOOKUP je sodobna in prilagodljiva zamenjava za starejše funkcije, kot so VLOOKUP, HLOOKUP in LOOKUP. XLOOKUP podpira približno in natančno ujemanje, nadomestne znake (*?) Za delna ujemanja in iskanje v navpičnem ali vodoravnem obsegu.

Namen

Iskalne vrednosti v obsegu ali matriki

Vrnjena vrednost

Ujemajoče se vrednosti z vrnjene matrike

Sintaksa

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

Argumenti

  • lookup - vrednost iskanja.
  • lookup_array - matrika ali obseg za iskanje.
  • return_array - matrika ali obseg, ki se vrne.
  • not_found - (neobvezno) Vrednost, ki jo je treba vrniti, če ni bilo mogoče najti nobenega ujemanja.
  • match_mode - (neobvezno) 0 = natančno ujemanje (privzeto), -1 = natančno ujemanje ali naslednje najmanjše, 1 = natančno ujemanje ali naslednje večje, 2 = nadomestni znak.
  • search_mode - (neobvezno) 1 = iskanje od prvega (privzeto), -1 = iskanje od zadnjega, 2 = binarno iskanje naraščajoče, -2 = binarno iskanje padajoče.

Različica

Excel 365

Opombe o uporabi

XLOOKUP je sodobna zamenjava za funkcijo VLOOKUP. Je prilagodljiva in vsestranska funkcija, ki jo je mogoče uporabiti v najrazličnejših situacijah.

XLOOKUP lahko najde vrednosti v navpičnem ali vodoravnem obsegu, lahko izvede približno in natančno ujemanje ter podpira nadomestne znake (*?) Za delna ujemanja. Poleg tega lahko XLOOKUP išče podatke po prvi vrednosti ali zadnji vrednosti (glejte vrsto ujemanja in podrobnosti načina iskanja spodaj). V primerjavi s starejšimi funkcijami, kot so VLOOKUP, HLOOKUP in LOOKUP, XLOOKUP ponuja več ključnih prednosti.

Sporočilo ni bilo mogoče najti

Ko XLOOKUP ne najde zadetka, vrne napako # N / A, kot druge funkcije ujemanja v Excelu. Za razliko od drugih funkcij ujemanja XLOOKUP podpira izbirni argument, imenovan not_found, ki ga lahko uporabimo za preglasitev napake # N / A, kadar bi se sicer pojavila. Tipične vrednosti za not_found so lahko "Not found", "No match", "No result" itd. Ko podajate vrednost za not_found, besedilo zaprite v dvojne narekovaje ("").

Opomba: Bodite previdni, če vnesete prazen niz ("") za not_found. Če ne najdete nobenega ujemanja, XLOOKUP namesto # N / A ne bo prikazal ničesar. Če želite videti napako # N / A, ko ujemanja ni mogoče najti, argument v celoti izpustite.

Vrsta ujemanja

Privzeto bo XLOOKUP izvedel natančno ujemanje. Vedenje ujemanja nadzoruje neobvezni argument z imenom match_type, ki ima naslednje možnosti:

Vrsta ujemanja Vedenje
0 (privzeto) Natančno ujemanje. Vrne # N / A, če se ne ujema.
-1 Natančno ujemanje ali naslednji manjši element.
1. Natančno ujemanje ali naslednji večji element.
2. Nadomestni znak (*,?, ~)

Način iskanja

Privzeto se XLOOKUP začne ujemati od prve vrednosti podatkov. Vedenje iskanja nadzoruje neobvezni argument z imenom search_mode , ki ponuja naslednje možnosti:

Način iskanja Vedenje
1 (privzeto) Iskanje po prvi vrednosti
-1 Iskanje po zadnji vrednosti (obratno)
2. Vrednosti binarnega iskanja so razvrščene po naraščajočem vrstnem redu
-2 Vrednosti binarnega iskanja so razvrščene po padajočem vrstnem redu

Binarna iskanja so zelo hitra, vendar je treba podatke po potrebi razvrstiti. Če podatki niso pravilno razvrščeni, lahko binarno iskanje vrne neveljavne rezultate, ki so videti povsem normalno.

Primer # 1 - osnovno natančno ujemanje

Privzeto bo XLOOKUP izvedel natančno ujemanje. V spodnjem primeru se XLOOKUP uporablja za pridobivanje prodaje na podlagi natančnega ujemanja v filmu. Formula v H5 je:

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

Podrobnejša razlaga tukaj.

2. primer - osnovno približno ujemanje

Če želite omogočiti približno ujemanje, podajte vrednost za argument "match_mode". V spodnjem primeru se XLOOKUP uporablja za izračun popusta glede na količino, ki zahteva približno ujemanje. Formula v F5 vsebuje -1 za match_mode, da omogoči približno ujemanje z vedenjem "natančno ujemanje ali naslednje najmanjše":

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

Podrobnejša razlaga tukaj.

Primer # 3 - več vrednosti

XLOOKUP lahko istočasno vrne več kot eno vrednost za isto ujemanje. Spodnji primer prikazuje, kako je mogoče XLOOKUP konfigurirati tako, da vrne tri ujemajoče se vrednosti z eno samo formulo. Formula v C5 je:

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

Upoštevajte, da vrnitvena matrika (C8: E15) vključuje 3 stolpce: Prvi, Zadnji, Oddelek. Vse tri vrednosti se vrnejo in prelijejo v obseg C5: E5.

Primer # 4 - dvosmerno iskanje

XLOOKUP lahko uporabimo za dvosmerno iskanje, tako da enega XLOOKUP ugnezdi v drugega. V spodnjem primeru "notranji" XLOOKUP pridobi celotno vrstico (vse vrednosti za Glass), ki se preda "zunanji" XLOOKUP kot vrnilno polje. Zunanji XLOOKUP najde ustrezno skupino (B) in vrne ustrezno vrednost (17.25) kot končni rezultat.

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

Več podrobnosti tukaj.

Primer # 5 - sporočila ni mogoče najti

Tako kot druge iskalne funkcije, če XLOOKUP ne najde vrednosti, vrne napako # N / A. Če želite prikazati sporočilo po meri namesto # N / A, podajte vrednost za neobvezni argument "ni mogoče najti", zaprt z dvojnimi narekovaji (""). Na primer, če želite na podlagi spodnjega delovnega lista za prikaz "Ne najdem", ko ni mogoče najti ustreznega filma, uporabite:

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

To sporočilo lahko prilagodite po želji: "Ni ujemanja", "Filma ni mogoče najti" itd.

Primer # 6 - zapletena merila

Z zmožnostjo naravnega obvladovanja nizov lahko XLOOKUP uporabljamo s kompleksnimi merili. V spodnjem primeru se XLOOKUP ujema s prvim zapisom, kjer: račun se začne z "x", regija pa "vzhod" in mesec ni april:

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

Podrobnosti: (1) preprost primer, (2) bolj zapleten primer.

Prednosti XLOOKUP-a

XLOOKUP ponuja več pomembnih prednosti, zlasti v primerjavi z VLOOKUP:

  • XLOOKUP lahko išče podatke desno ali levo od vrednosti iskanja
  • XLOOKUP lahko vrne več rezultatov (primer št. 3 zgoraj)
  • Privzeto XLOOKUP privzeto ustreza (VLOOKUP privzeto približno)
  • XLOOKUP lahko deluje z navpičnimi in vodoravnimi podatki
  • XLOOKUP lahko izvede obratno iskanje (od zadnjega do prvega)
  • XLOOKUP lahko vrne celotne vrstice ali stolpce, ne samo ene vrednosti
  • XLOOKUP lahko v osnovi uporablja matrike za uporabo zapletenih meril

Opombe

  1. XLOOKUP lahko deluje tako z navpičnimi kot z vodoravnimi nizi.
  2. XLOOKUP vrne # N / A, če iskalne vrednosti ni mogoče najti.
  3. Vpogledna_matrika mora imeti dimenzijo združljiv z return_array argument, sicer bo XLOOKUP vrnil #VALUE!
  4. Če se XLOOKUP uporablja med delovnimi zvezki, morata biti obe delovni zvezki odprti, sicer bo XLOOKUP vrnil #REF !.
  5. Tako kot funkcija INDEX, XLOOKUP kot rezultat vrne referenco.

Sorodni videoposnetki

Primer osnovnega XLOOKUP-a V tem videoposnetku bomo z osnovnim primerom nastavili funkcijo XLOOKUP. Glede na ime mesta bomo poiskali državo in prebivalstvo. Osnovno približno ujemanje XLOOKUP V tem videoposnetku bomo nastavili funkcijo XLOOKUP za izvedbo približnega ujemanja za izračun količinskega popusta. XLOOKUP z logično logiko V tem videu si bomo ogledali, kako uporabiti funkcijo XLOOKUP z logično logiko za uporabo več meril. XLOOKUP z več iskalnimi vrednostmi V tem videoposnetku bomo nastavili XLOOKUP za vrnitev več vrednosti v dinamičnem polju, tako da namesto ene iskalne vrednosti zagotovimo obseg iskalnih vrednosti.

Zanimive Članki...