Funktsiooni XLOOKUP kasutamine Excelis

The VLOOKUP funktsioon on alati olnud üks Exceli võimsamaid funktsioone. See võimaldab teil otsida väärtusi tabeli esimesest veerust ja tagastada väärtusi parempoolsetelt väljadelt. Kuid Excelil on ka funktsioon nimega XLOOKUP, mis võimaldab otsida väärtust mis tahes veerus või reas ja tagastada andmeid mis tahes muust veerust.

Kuidas XLOOKUP töötab

Funktsiooni XLOOKUP on palju lihtsam kasutada kui funktsiooni VLOOKUP, kuna tulemuste veeru väärtuse määramise asemel saate määrata kogu vahemiku.

Funktsioon võimaldab ka otsida nii veerust kui ka reast, leides väärtuse ristuvas lahtris.

Funktsiooni XLOOKUP parameetrid on järgmised:

=XLOOKUP (otsingu_väärtus, otsingu_massiiv, tagastamise_massiiv, [match_mode], [Search_mode])

• otsingu_väärtus: väärtus, mida soovite otsida
• otsingu_massiiv: massiiv (veerg), mida soovite otsida
• return_massiiv: tulemus (veerg), millest soovite väärtuse tuua
• match_mode (valikuline): valige täpne vaste (0), täpne vaste või järgmine väikseim väärtus (-1) või metamärgi vaste (2).
• search_mode (valikuline): valige, kas otsida alustades veeru esimesest üksusest (1), veeru viimasest üksusest (-1), kahendotsingust kasvavalt (2) või kahanevalt (-2).

Järgmised on mõned kõige levinumad otsingud, mida saate funktsiooniga XLOOKUP teha.

Kuidas otsida ühte tulemust XLOOKUPi abil

Lihtsaim viis XLOOKUP-i kasutamiseks on otsida ühte tulemust ühest veerust pärineva andmepunkti abil.

1. See näidisarvutustabel on müügiesindajate esitatud tellimuste loend, mis sisaldab kauba, ühikute arvu, maksumuse ja kogumüügi.

   

2. Kui soovite leida konkreetse müügiesindaja esitatud loendist esimest müüki, võite luua funktsiooni XLOOKUP, mis otsib veerust Esindaja nime. Funktsioon tagastab tulemuse veerust Kokku. Selle funktsioon XLOOKUP on:

   =XLOOKUP(I2,C2:C44,G2:G44,0,1)
   

   • I2: osutab Esindaja nimi otsi lahtrit
   • C2:C44: see on veerg Rep, mis on otsingu massiiv
   • G2:G33: see on veerg Kokku, mis on tagastamise massiiv
   • 0: valib täpse vaste
   • 1: valib tulemustes esimese vaste

3. Kui vajutate Sisenema ja tippige müügiesindaja nimi, kuvatakse lahter Kokku tulemus tabelis selle müügiesindaja kohta esimene tulemus.

   

4. Kui soovite otsida viimast müüki (kuna tabel on järjestatud kuupäeva järgi vastupidises järjekorras), muutke viimane XLOOKUP argument väärtuseks -1, mis alustab otsingut otsingumassiivi viimasest lahtrist ja annab teile selle asemel selle tulemuse.

   

5. See näide näitab sarnast otsingut, mida võiksite teha esineda VLOOKUP-iga funktsiooni, kasutades otsingutabeli esimese veeruna veergu Rep. Kuid XLOOKUP võimaldab teil otsida mis tahes veergu mõlemas suunas. Näiteks kui soovite leida müügiesindajat, kes müüs aasta esimese Binderi tellimuse, kasutage järgmist funktsiooni XLOOKUP:

   =XLOOKUP(I2,D2:D44,C2:C44,0,1)
   

   • D2: osutab üksuse otsingu lahtrile
   • D2:D44: see on veerg Üksus, mis on otsingumassiiviks
   • C2:C44: see on veerg Rep, mis on otsingumassiivist vasakul olev tagastusmassiiviks
   • 0: valib täpse vaste
   • 1: valib tulemustes esimese vaste

6. Seekord on tulemuseks selle müügiesindaja nimi, kes müüs selle aasta esimese köitjatellimuse.

   

Tehke vertikaalne ja horisontaalne vaste rakendusega XLOOKUP

Teine XLOOKUP-i võimalus, milleks VLOOKUP ei ole võimeline, on nii vertikaal- kui ka horisontaalotsingu sooritamine, mis tähendab, et saate otsida üksust veerust allapoole ja ka üle rea.

1. Järgmises arvutustabeli näites on iga müügiesindaja müük jagatud kvartalite kaupa. Kui soovite näha konkreetse müügiesindaja kolmanda kvartali müüki ilma XLOOKUP-funktsioonita, oleks selline otsing keeruline.

   

2. Funktsiooniga XLOOKUP on selline otsing lihtne. Kasutades järgmist funktsiooni XLOOKUP, saate otsida konkreetse müügiesindaja kolmanda kvartali müüke:

   =XLOOKUP(J2,B2:B42,XLOOKUP(K2,C1:H1,C2:H42))
   

   • J2: osutab esindaja otsingulahtrile
   • B2:B42: see on veerg Üksus, mis on veeruotsingu massiiv
   • K2: osutab kvartaliotsingu lahtrile
   • C1:H1: see on reaotsingu massiiv
   • C2:H42: see on iga kvartali dollarisumma otsingu massiiv

   See pesastatud XLOOKUP-funktsioon tuvastab esmalt müügiesindaja ja järgmine XLOOKUP-funktsioon identifitseerib soovitud kvartali. Tagastusväärtus will on lahter, kus need kaks lõikuvad.

3. Selle valemi tulemuseks on Thompsoni-nimelise esindaja kvartali üks tulu.

   

Funktsiooni XLOOKUP kasutamine

Funktsioon XLOOKUP on saadaval ainult Office Insideri tellijatele, kuid peagi avaldatakse see kõigile Microsoft 365 tellijatele.

Kui soovite funktsiooni ise testida, võite saada Office Insideriks. Valige Fail > Konto, seejärel valige Office Insider tellimiseks rippmenüüst.

Kui olete Office Insideri programmiga liitunud, saab teie installitud Exceli versioon kõik uusimad värskendused ja saate hakata kasutama funktsiooni XLOOKUP.