Kako se koristi funkcija Excel XLOOKUP -

Sažetak

Funkcija Excel XLOOKUP moderna je i fleksibilna zamjena za starije funkcije poput VLOOKUP, HLOOKUP i LOOKUP. XLOOKUP podržava približno i točno podudaranje, zamjenske znakove (*?) Za djelomična podudaranja i traženje u vertikalnom ili vodoravnom rasponu.

Svrha

Vrijednosti pretraživanja u rasponu ili polju

Povratna vrijednost

Podudarne vrijednosti (vrijednosti) iz povratnog niza

Sintaksa

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

Argumenti

  • lookup - vrijednost pretraživanja.
  • lookup_array - niz ili raspon za pretraživanje.
  • return_array - niz ili raspon koji treba vratiti.
  • not_found - (neobavezno) Vrijednost koju treba vratiti ako nije pronađeno podudaranje.
  • match_mode - (neobavezno) 0 = točno podudaranje (zadano), -1 = točno podudaranje ili sljedeće najmanje, 1 = točno podudaranje ili sljedeće veće, 2 = zamjenski znak.
  • search_mode - (neobavezno) 1 = traži od prvog (zadano), -1 = traži od zadnjeg, 2 = binarno pretraživanje uzlazno, -2 = binarno pretraživanje silazno.

Verzija

Excel 365

Napomene o upotrebi

XLOOKUP je moderna zamjena za funkciju VLOOKUP. To je fleksibilna i svestrana funkcija koja se može koristiti u raznim situacijama.

XLOOKUP može pronaći vrijednosti u vertikalnom ili vodoravnom rasponu, može izvršiti približna i točna podudaranja i podržava zamjenske znakove (*?) Za djelomična podudaranja. Osim toga, XLOOKUP može pretraživati ​​podatke počevši od prve vrijednosti ili posljednje vrijednosti (pogledajte dolje detalje o vrsti podudaranja i načinu pretraživanja). U usporedbi sa starijim funkcijama poput VLOOKUP, HLOOKUP i LOOKUP, XLOOKUP nudi nekoliko ključnih prednosti.

Poruka nije pronađena

Kada XLOOKUP ne može pronaći podudaranje, vraća pogrešku # N / A, poput ostalih funkcija podudaranja u Excelu. Za razliku od ostalih funkcija podudaranja, XLOOKUP podržava neobavezni argument nazvan not_found koji se može koristiti za nadjačavanje pogreške # N / A kada bi se inače pojavila. Tipične vrijednosti za not_found mogu biti "Nije pronađeno", "Nema podudaranja", "Nema rezultata" itd. Kada dajete vrijednost za not_found, tekst priložite dvostrukim navodnicima ("").

Napomena: Budite oprezni ako unesete prazan niz ("") za not_found. Ako se ne pronađe podudaranje, XLOOKUP neće prikazati ništa umjesto # N / A. Ako želite vidjeti pogrešku # N / A kada podudaranje nije pronađeno, izostavite argument u potpunosti.

Vrsta podudaranja

Prema zadanim postavkama, XLOOKUP će izvršiti točno podudaranje. Ponašanjem podudaranja upravlja neobavezni argument nazvan match_type, koji ima sljedeće mogućnosti:

Vrsta podudaranja Ponašanje
0 (zadano) Točno podudaranje. Vratit će # N / A ako nema podudaranja.
-1 Točno podudaranje ili sljedeća manja stavka.
1 Točno podudaranje ili sljedeća veća stavka.
2 Podudarni znak (*,?, ~)

Način pretraživanja

Prema zadanim postavkama, XLOOKUP će se početi podudarati od prve vrijednosti podataka. Ponašanjem pretraživanja upravlja neobavezni argument nazvan search_mode , koji pruža sljedeće mogućnosti:

Način pretraživanja Ponašanje
1 (zadano) Traži od prve vrijednosti
-1 Pretraži posljednju vrijednost (obrnuto)
2 Vrijednosti binarnog pretraživanja poredane u rastućem redoslijedu
-2 Vrijednosti binarnog pretraživanja poredane u opadajućem redoslijedu

Binarna pretraživanja vrlo su brza, ali podaci se moraju sortirati prema potrebi. Ako se podaci ne sortiraju pravilno, binarno pretraživanje može vratiti nevaljane rezultate koji izgledaju sasvim normalno.

Primjer # 1 - osnovno točno podudaranje

Prema zadanim postavkama, XLOOKUP će izvršiti točno podudaranje. U donjem primjeru XLOOKUP se koristi za dohvaćanje prodaje na temelju točnog podudaranja u filmu. Formula u H5 je:

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

Ovdje detaljnije objašnjenje.

Primjer # 2 - osnovno približno podudaranje

Da biste omogućili približno podudaranje, navedite vrijednost za argument "match_mode". U donjem primjeru XLOOKUP se koristi za izračunavanje popusta na temelju količine, što zahtijeva približno podudaranje. Formula u F5 sadrži -1 za match_mode kako bi se omogućilo približno podudaranje s ponašanjem "točno podudaranje ili sljedeće najmanje":

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

Ovdje detaljnije objašnjenje.

Primjer # 3 - višestruke vrijednosti

XLOOKUP može istovremeno vratiti više od jedne vrijednosti za isto podudaranje. Primjer u nastavku pokazuje kako se XLOOKUP može konfigurirati tako da vraća tri podudarne vrijednosti s jednom formulom. Formula u C5 je:

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

Primijetite da povratni niz (C8: E15) uključuje 3 stupca: Prvi, Zadnji, Odjel. Sve tri vrijednosti se vraćaju i prelijevaju u raspon C5: E5.

Primjer # 4 - dvosmjerno traženje

XLOOKUP se može koristiti za dvosmjerno traženje, gniježđenjem jednog XLOOKUP-a unutar drugog. U donjem primjeru, "unutarnji" XLOOKUP dohvaća cijeli redak (sve vrijednosti za Glass), koji se predaje "vanjskom" XLOOKUP-u kao povratni niz. Vanjski XLOOKUP pronalazi odgovarajuću grupu (B) i vraća odgovarajuću vrijednost (17.25) kao konačni rezultat.

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

Više detalja ovdje.

Primjer # 5 - poruka nije pronađena

Kao i ostale funkcije pretraživanja, ako XLOOKUP ne pronađe vrijednost, vraća pogrešku # N / A. Da biste prikazali prilagođenu poruku umjesto # N / A, navedite vrijednost za neobavezni argument "nije pronađen", zatvoren u dvostruke navodnike (""). Na primjer, za prikaz "Nije pronađeno" kada nije pronađen odgovarajući film, na temelju radnog lista u nastavku, upotrijebite:

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

Ovu poruku možete prilagoditi kako želite: "Nema podudaranja", "Film nije pronađen" itd.

Primjer # 6 - složeni kriteriji

Uz mogućnost nativnog rukovanja nizovima, XLOOKUP se može koristiti sa složenim kriterijima. U primjeru u nastavku, XLOOKUP podudara se s prvim zapisom, gdje: račun počinje s "x", a regija je "istok", a mjesec nije travanj:

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

Pojedinosti: (1) jednostavan primjer, (2) složeniji primjer.

Prednosti XLOOKUP-a

XLOOKUP nudi nekoliko važnih prednosti, posebno u usporedbi s VLOOKUP-om:

  • XLOOKUP može pretraživati ​​podatke desno ili lijevo od vrijednosti pretraživanja
  • XLOOKUP može vratiti više rezultata (primjer br. 3 gore)
  • XLOOKUP zadane vrijednosti točno se podudaraju (VLOOKUP zadane vrijednosti približne vrijednosti)
  • XLOOKUP može raditi s vertikalnim i vodoravnim podacima
  • XLOOKUP može izvršiti obrnuto pretraživanje (od zadnjeg do prvog)
  • XLOOKUP može vratiti cijele retke ili stupce, a ne samo jednu vrijednost
  • XLOOKUP može izvorno raditi s nizovima kako bi primijenio složene kriterije

Bilješke

  1. XLOOKUP može raditi s vertikalnim i vodoravnim nizovima.
  2. XLOOKUP će vratiti # N / A ako vrijednost pretraživanja nije pronađena.
  3. Polje_pregleda mora imati dimenziju kompatibilan s return_array argument, inače XLOOKUP će se vratiti #VALUE!
  4. Ako se XLOOKUP koristi između radnih knjiga, obje radne knjige moraju biti otvorene, inače će XLOOKUP vratiti #REF !.
  5. Kao i funkcija INDEX, XLOOKUP kao rezultat vraća referencu.

Slični Videi

Primjer osnovnog XLOOKUP-a U ovom ćemo videozapisu postaviti osnovni primjer funkcije XLOOKUP. Podudarajući se s nazivom grada, dobit ćemo zemlju i stanovništvo. Osnovno približno podudaranje XLOOKUP-a U ovom ćemo videozapisu postaviti funkciju XLOOKUP za izvršavanje približnog podudaranja kako bismo izračunali količinski popust. XLOOKUP s logičkom logikom U ovom ćemo videu pogledati kako koristiti XLOOKUP funkciju s logičkom logikom za primjenu višestrukih kriterija. XLOOKUP s višestrukim vrijednostima pretraživanja U ovom ćemo videozapisu postaviti XLOOKUP za vraćanje više vrijednosti u dinamičkom nizu, pružajući raspon vrijednosti pretraživanja umjesto jedne vrijednosti pretraživanja.

Zanimljivi članci...