Funktsioon VLOOKUP 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, sest tulemuste veeru väärtuse määramise asemel saate määrata kogu vahemiku.
Samuti võimaldab see funktsioon 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])
- lookup_value: väärtus, mida soovite otsida
- lookup_array: massiiv (veerg), mida soovite otsida
- return_array: tulemus (veerg), mille kohta soovite väärtuse hankida
- 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 kasvav alt (2) või binaarne otsing kahanev alt (-2).
Järgmised on mõned kõige levinumad otsingud, mida saate funktsiooniga XLOOKUP teha.
Kuidas otsida ühte tulemust kasutades XLOOKUP
Lihtsaim viis XLOOKUP-i kasutamiseks on otsida ühte tulemust, kasutades andmepunkti ühest veerust.
-
See arvutustabeli näide on müügiesindajate esitatud tellimuste loend, mis sisaldab ühikut, ühikute arvu, maksumust ja kogu müüki.
Image -
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 Rep Name otsingulahtrile
- C2:C44: see on veerg Rep, mis on otsingumassiiviks
- G2:G33: see on veerg Kokku, mis on tagastusmassiiviks
- 0: valib täpse vaste
- 1: valib tulemustes esimese vaste
-
Kui vajutate Sisestus ja sisestate müügiesindaja nime, kuvatakse lahtris Kokku tulemus tabelis selle müügiesindaja esimene tulemus.
Image -
Kui soovite otsida viimast müüki (kuna tabel on järjestatud kuupäeva järgi vastupidises järjekorras), muutke viimase XLOOKUP argumendiks - 1, mis algab otsige otsingumassiivi viimasest lahtrist ja esitage teile selle asemel selle tulemuse.
Image -
See näide näitab sarnast otsingut, mida saate teha funktsiooniga VLOOKUP, 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 selle aasta esimese Binderi tellimuse, kasutage järgmist funktsiooni XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: osutab üksuse otsingulahtrile
- D2:D44: see on veerg Üksus, mis on otsingumassiiviks
- C2:C44: see on veerg Rep, mis on otsingumassiivist vasakul olev tagastusmassiv
- 0: valib täpse vaste
- 1: valib tulemustes esimese vaste
-
Seekord on tulemuseks selle müügiesindaja nimi, kes müüs aasta esimese köitjatellimuse.
Image
Sooritage vertikaalne ja horisontaalne vaste funktsiooniga XLOOKUP
Teine XLOOKUP-i võimalus, milleks VLOOKUP ei ole võimeline, on võimalus sooritada nii vertikaalset kui ka horisontaalset otsingut, mis tähendab, et saate otsida üksust veerust allapoole ja ka üle rea.
See topeltotsingu funktsioon asendab tõhus alt teisi Exceli funktsioone, nagu INDEX, MATCH või HLOOKUP.
-
Järgmises näitearvutustabelis 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.
Image -
Funktsiooni XLOOKUP abil 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 esindajate otsingulahtrile
- B2:B42: see on veerg Üksus, mis on veeruotsingu massiiv
- K2: osutab kvartali otsingulahtrile
- C1:H1: see on reaotsingu massiiv
- C2:H42: see on iga kvartali dollarisumma otsingu massiiv
See pesastatud XLOOKUP-funktsioon tuvastab esm alt müügiesindaja ja järgnev XLOOKUP-funktsioon identifitseerib soovitud kvartali. Tagastusväärtus on lahter, kus need kaks lõikuvat.
-
Selle valemi tulemus on Thompsoni-nimelise esindaja kvartali esimese tulu.
Image
Funktsiooni XLOOKUP kasutamine
Funktsioon XLOOKUP on saadaval ainult Office Insideri tellijatele, kuid peagi avaldatakse see kõigile Microsoft 365 tellijatele.
Kui soovite funktsiooni ise katsetada, võite saada Office Insideriks. Valige File > Account, seejärel valige tellimiseks rippmenüü Office Insider.
Kui liitute Office Insideri programmiga, saab teie installitud Exceli versioon kõik uusimad värskendused ja saate hakata kasutama funktsiooni XLOOKUP.
