Kuidas kasutada funktsiooni Excel XLOOKUP -

Kokkuvõte

Exceli XLOOKUP funktsioon on kaasaegne ja paindlik asendus vanematele funktsioonidele nagu VLOOKUP, HLOOKUP ja LOOKUP. XLOOKUP toetab ligikaudset ja täpset sobitamist, metamärke (*?) Osaliste vastete jaoks ja otsinguid vertikaalsetes või horisontaalsetes vahemikes.

Eesmärk

Otsinguväärtused vahemikus või massiivis

Tagastusväärtus

Tagasimassiivi väärtus (ed)

Süntaks

= XLOOKUP (otsingu, otsingu_juure, tagastuse_juure, (mitte_leitud), (mängu_režiim), (otsingu_režiimi))

Argumendid

• otsing - otsingu väärtus.
• lookup_array - otsitav massiiv või vahemik.
• return_array - tagastatav massiiv või vahemik.
• not_found - (valikuline) Väärtus, mis tagastatakse, kui vastet ei leitud.
• match_mode - (valikuline) 0 = täpne vaste (vaikimisi), -1 = täpne vaste või järgmine väikseim, 1 = täpne vaste või järgmine suurem, 2 = metamärgi vaste.
• otsingu_režiim - (valikuline) 1 = otsing esimesest (vaikimisi), -1 = otsing viimasest, 2 = kahendotsing kasvavalt, -2 = kahendotsing kahanevalt.

Versioon

Excel 365

Kasutusjuhised

XLOOKUP on funktsioon VLOOKUP kaasaegne asendaja. See on paindlik ja mitmekülgne funktsioon, mida saab kasutada väga erinevates olukordades.

XLOOKUP suudab leida väärtusi vertikaalsetes või horisontaalsetes vahemikes, saab teha ligikaudseid ja täpseid vasteid ning toetab osaliste vastete korral metamärke (*?) Lisaks saab XLOOKUP otsida andmeid esimesest või viimasest väärtusest (vt allpool vastetüübi ja otsingurežiimi üksikasju). Võrreldes vanemate funktsioonidega nagu VLOOKUP, HLOOKUP ja LOOKUP, pakub XLOOKUP mitmeid peamisi eeliseid.

Sõnumit ei leitud

Kui XLOOKUP ei leia vastet, tagastab vea # N / A, nagu ka teised Exceli vastefunktsioonid. Erinevalt teistest mängufunktsioonidest toetab XLOOKUP valikulist argumenti nimega not_found, mida saab kasutada vea # N / A tühistamiseks, kui see muidu ilmub. Not_found'i tüüpilised väärtused võivad olla "Not found", "No match", "No result" jne. Kui märkite not_foundi väärtuse, lisage tekst topelt jutumärkidesse ("").

Märkus. Olge ettevaatlik, kui sisestate not_found jaoks tühja stringi (""). Kui vastet ei leita, ei kuva XLOOKUP # N / A asemel midagi. Kui soovite näha viga # N / A, kui vastet ei leita, jätke argument täielikult välja.

Vaste tüüp

Vaikimisi täidab XLOOKUP täpse vaste. Vaste käitumist kontrollib valikuline argument nimega match_type, millel on järgmised valikud:

Vaste tüüp	Käitumine
0 (vaikimisi)	Täpne vaste. Tagastab # N / A, kui vastet pole.
-1	Täpne vaste või järgmine väiksem üksus.
1	Täpne vaste või järgmine suurem üksus.
2	Metamärgi vaste (*,?, ~)

Otsingurežiim

Vaikimisi hakkab XLOOKUP sobima esimesest andmeväärtusest. Otsingu käitumist kontrollib valikuline argument nimega search_mode , mis pakub järgmisi valikuid:

Otsingurežiim	Käitumine
1 (vaikimisi)	Otsige esimesest väärtusest
-1	Otsi viimasest väärtusest (vastupidine)
2	Binaarotsingu väärtused on järjestatud kasvavas järjekorras
-2	Binaarotsingu väärtused on järjestatud kahanevas järjekorras

Binaarotsingud on väga kiired, kuid andmed tuleb vastavalt vajadusele sortida. Kui andmeid pole õigesti sorteeritud, võib binaarotsing anda kehtetu tulemuse, mis näeb välja täiesti normaalne.

Näide 1 - täpne täpne vaste

Vaikimisi täidab XLOOKUP täpse vaste. Allpool toodud näites kasutatakse XLOOKUPi müügi hankimiseks filmi täpse vaste põhjal. H5 valem on:

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

Täpsem selgitus siin.

Näide # 2 - põhiline ligikaudne vaste

Ligikaudse vaste lubamiseks sisestage argumenti "match_mode" väärtus. Allpool toodud näites kasutatakse XLOOKUPi, et arvutada allahindlus koguse põhjal, mis nõuab ligikaudset vastet. F5 valem annab mängu match_mode jaoks -1, et võimaldada ligikaudset vastet käitumisele "täpne vaste või järgmine väikseim":

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

Täpsem selgitus siin.

Näide 3 - mitu väärtust

XLOOKUP võib sama vaste jaoks tagastada korraga rohkem kui ühe väärtuse. Allpool toodud näide näitab, kuidas XLOOKUP-i saab konfigureerida tagastama ühe valemiga kolm sobitamisväärtust. C5 valem on:

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

Pange tähele, et tagasimassiiv (C8: E15) sisaldab 3 veergu: Esimene, Viimane, Osakond. Kõik kolm väärtust tagastatakse ja levivad vahemikku C5: E5.

Näide 4 - kahesuunaline otsing

XLOOKUPi saab kasutada kahesuunalise otsingu tegemiseks, pesastades ühe XLOOKUPi teise sisse. Allpool toodud näites saab "sisemine" XLOOKUP terve rea (kõik klaasi Glass väärtused), mis antakse tagasimassiivina "välisele" XLOOKUP-le. Välimine XLOOKUP leiab sobiva rühma (B) ja tagastab lõpptulemuseks vastava väärtuse (17.25).

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

Täpsem teave siin.

Näide # 5 - sõnumit ei leitud

Sarnaselt teiste otsingufunktsioonidega tagastab XLOOKUP väärtuse ka # N / A tõrke. Kohandatud sõnumi kuvamiseks # N / A asemel esitage valikulise "not found" argumendi väärtus, mis on topelt jutumärkides (""). Näiteks kui valikut „Ei leitud” kuvatakse, kui sobivat filmi ei leita, kasutage alloleva töölehe põhjal järgmist:

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

Saate seda sõnumit oma äranägemise järgi kohandada: "Vastet pole", "Filmi ei leitud" jne.

Näide # 6 - keerulised kriteeriumid

Võimalusega massiive naturaalselt käsitleda, saab XLOOKUP-i kasutada keerukate kriteeriumidega. Allpool toodud näites sobib XLOOKUP esimese kirjega, kus: konto algab tähega "x" ja piirkond on "ida" ja kuu pole aprill:

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

Üksikasjad: (1) lihtne näide, (2) keerulisem näide.

XLOOKUP eelised

XLOOKUP pakub mitmeid olulisi eeliseid, eriti võrreldes VLOOKUPiga:

• XLOOKUP saab otsinguandmeid otsinguväärtustest paremale või vasakule
• XLOOKUP võib anda mitu tulemust (näide 3 ülal)
• XLOOKUP vaikimisi täpne vaste (VLOOKUP vaikimisi ligikaudne)
• XLOOKUP saab töötada vertikaalsete ja horisontaalsete andmetega
• XLOOKUP saab teha vastupidise otsingu (viimane kuni esimene)
• XLOOKUP võib tagastada terved read või veerud, mitte ainult ühe väärtuse
• XLOOKUP saab keeruliste kriteeriumide rakendamiseks töötada massiividega natiivselt

Märkused

1. XLOOKUP võib töötada nii vertikaalsete kui ka horisontaalsete massiividega.
2. Kui otsingu väärtust ei leita, tagastab XLOOKUP # N / A.
3. Otsingu_massiiv peab olema mõõde kooskõlas return_array argument, vastasel XLOOKUP naaseb #VALUE!
4. Kui töövihikute vahel kasutatakse XLOOKUP, peavad mõlemad töövihikud olema avatud, vastasel juhul tagastab XLOOKUP #REF !.
5. Sarnaselt funktsioonile INDEX tagastab ka XLOOKUP selle tulemusena viite.

Seotud videod

XLOOKUPi põhinäide Selles videos seadistame funktsiooni XLOOKUP põhinäite abil. Vastab linna nimele, leiame riigi ja rahvastiku. Põhiline XLOOKUP ligikaudne vaste Selles videos seadistame XLOOKUP funktsiooni ligikaudse vaste sooritamiseks, et arvutada kogusel põhinev allahindlus. Boolean loogikaga XLOOKUP Selles videos uurime, kuidas kasutada funktsiooni XLOOKUP koos Boole'i ​​loogikaga mitme kriteeriumi rakendamiseks. XLOOKUP mitme otsingu väärtusega Selles videos seadistame XLOOKUP dünaamilise massiivi mitme väärtuse tagastamiseks, pakkudes ühe otsinguväärtuse asemel otsinguväärtuste vahemiku.