Excel

Excel XLOOKUP funkcija

Excel Xlookup Function

Excel XLOOKUP funkcijaPovzetek

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 iskanja v navpičnem ali vodoravnem obsegu.



Namen Iskalne vrednosti v razponu ali nizu Vrnjena vrednost Ujemajoče se vrednosti iz vrnilne matrike Sintaksa = XLOOKUP (iskanje, iskalni_masiv, vrnilni_masik, [ne_našli], [način_ ujemanja], [iskalni_mod]] Argumenti
  • Poglej gor - Iskalna vrednost.
  • lookup_array - Polje ali obseg za iskanje.
  • return_array - Vrsta ali obseg za vrnitev.
  • ni najdeno - [neobvezno] Vrednost za vrnitev, če ni najdenega 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 = nadomestno ujemanje.
  • iskalni_mod -[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 sodoben nadomestek za funkcijo VLOOKUP. Je prilagodljiva in vsestranska funkcija, ki se lahko uporablja v najrazličnejših situacijah.

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





Sporočilo ni najdeno

Ko XLOOKUP ne najde ujemanja, vrne napako #N/A, tako kot druge funkcije ujemanja v Excelu. Za razliko od drugih funkcij ujemanja XLOOKUP podpira izbirni argument, imenovan ni najdeno ki se lahko uporabi za premostitev napake #N/A, kadar bi se sicer pojavila. Tipične vrednosti za ni najdeno so lahko »Ni najdeno«, »Ni ujemanja«, »Ni rezultatov« itd. Pri podajanju vrednosti za ni najdeno , besedilo vstavite v dvojne narekovaje ('').

Opomba: Bodite previdni, če vnesete prazen niz ('') za not_found. Če ni najdenega ujemanja, XLOOKUP namesto #N/A ne prikaže ničesar. Če želite videti napako #N/A, če ujemanja ne najdete, argument v celoti izpustite.



Vrsta ujemanja

XLOOKUP privzeto izvede datoteko natančno ujemanje . Obnašanje ujemanja nadzira izbirni argument, imenovan match_type, ki ima naslednje možnosti:

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

Način iskanja

XLOOKUP se bo privzeto začel ujemati od prve vrednosti podatkov. Iskanje obvladuje izbirni argument, imenovan iskalni_mod , ki ponuja naslednje možnosti:

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

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

Primer #1 - osnovno natančno ujemanje

XLOOKUP privzeto izvede natančno ujemanje. V spodnjem primeru se XLOOKUP uporablja za pridobivanje prodaje na podlagi natančnega ujemanja v Movie. Formula v H5 je:

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

XLOOKUP - osnovni primer natančnega ujemanja

Podrobnejša razlaga tukaj .

Primer #2 - 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, kar zahteva približno ujemanje. Formula v F5 ponuja -1 za match_mode, ki omogoča približno ujemanje z vedenjem 'natančno ujemanje ali naslednja najmanjša':

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

XLOOKUP - osnovni približni primer ujemanja

Podrobnejša razlaga tukaj .

Primer #3 - več vrednosti

XLOOKUP lahko za isto ujemanje hkrati vrne več kot eno vrednost. 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)

XLOOKUP - primer več vrednosti

Upoštevajte, da matrika vračila (C8: E15) vsebuje 3 stolpce: prvi, zadnji, oddelek. Vse tri vrednosti se vrnejo in igro v območje C5: E5.

Primer #4 - dvosmerno iskanje

XLOOKUP lahko uporabite za dvosmerno iskanje po gnezdenje en XLOOKUP v drugem. V spodnjem primeru 'notranji' XLOOKUP prikliče celotno vrstico (vse vrednosti za Glass), ki se izroči 'zunanjemu' XLOOKUP kot vrnitev matrike. Zunanji XLOOKUP najde ustrezno skupino (B) in kot končni rezultat vrne ustrezno vrednost (17,25).

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

XLOOKUP - primer dvosmernega iskanja

Več podrobnosti tukaj .

Primer #5 - sporočilo ni najdeno

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 izbirni argument 'not found', ki je obdan z dvojnimi narekovaji (''). Če želite na primer prikazati »Ni najdeno«, ko ni najdenega ustreznega filma, na podlagi spodnjega delovnega lista uporabite:

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

XLOOKUP - primer ni najden

To sporočilo lahko poljubno prilagodite: 'Ni ujemanja', 'Film ni bil najden' itd.

kaj od naštetega ni besedilna funkcija?

Primer #6 - zapletena merila

Z možnostjo ročnega ravnanja z matrikami lahko XLOOKUP uporabljate s kompleksnimi merili. V spodnjem primeru se XLOOKUP ujema s prvim zapisom, kjer se: račun začne z 'x' in regija je 'vzhodna' in mesec ni april:

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

XLOOKUP - primer zapletenih meril

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

Prednosti XLOOKUP

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

  • XLOOKUP lahko poišče podatke na desni ali levo iskalnih vrednosti
  • XLOOKUP lahko vrne več rezultatov (primer št. 3 zgoraj)
  • Privzeto XLOOKUP za natančno ujemanje (VLOOKUP privzeto za približek)
  • XLOOKUP lahko deluje z navpičnimi in vodoravnimi podatki
  • XLOOKUP lahko izvede obratno iskanje (od zadnjega do prvega)
  • XLOOKUP se lahko vrne v celoti vrstice ali stolpce , ne samo ena vrednost
  • XLOOKUP lahko dela z matrike izvorno za uporabo kompleksnih meril

Opombe

  1. XLOOKUP lahko deluje z navpičnimi in vodoravnimi matrikami.
  2. XLOOKUP vrne #N/A, če iskalne vrednosti ni mogoče najti.
  3. The lookup_array mora imeti dimenzijo, ki je združljiva z return_array argument, sicer bo XLOOKUP vrnil #VALUE!
  4. Če se XLOOKUP uporablja med delovnimi zvezki, morata biti oba delovna zvezka odprta, sicer bo XLOOKUP vrnil #REF !.
  5. Kot INDEX funkcija , XLOOKUP vrne a referenca kot rezultat.
XLOOKUP je nova funkcija, ki je na voljo v Excel 365 samo.


^