Funktsiooni XLOOKUP kasutamine Excel 2016-s

Excel 2016 Office 365 tellijatele Windowsis ja Macis toetab nüüd uut XLOOKUP-funktsiooni, mida reklaamitakse kui väga populaarse (kuigi sageli pahatahtliku) vertikaalotsingu funktsiooni VLOOKUP (ei tea, mis XLOOKUPis on X) tunduvalt lihtsam ja mitmekülgsem asendus. tähistab; ekstensiivne ehk?).

Neile, kes pole veel VLOOKUP-iga tuttavad (mida peetakse kolmandaks enimkasutatavaks funktsiooniks kohe pärast SUM ja AVERAGE), otsib see funktsioon vertikaalselt rea järgi määratud otsingutabeli vasakpoolseimas veerus ülalt alla, kuni leiab väärtuse otsinguveerg, mis on tähistatud nihkearvuga, mis vastab teie otsitavale või ületab seda. Kuigi funktsioonil VLOOKUP on see tohutult kasulik konkreetsete üksuste leidmiseks töölehe andmetabeli pikas loendis või veerus, on sellel mitmeid piiranguid, mida see uus otsingufunktsioon ei jaga, nagu XLOOKUP:

• Vaikimisi leitakse otsinguvahemikus teie otsinguväärtusele täpsed vasted
• Saab otsida tabelist nii vertikaalselt (rea järgi) kui ka horisontaalselt (veergude järgi), asendades sellega funktsiooni HLOOKUP kasutamise vajaduse horisontaalsel veeru järgi otsimisel
• Saab otsida vasakule või paremale, nii et funktsiooni toimimiseks ei pea otsingutabeli otsinguvahemik asuma tagastusvahemikuks määratud veerust vasakul
• Kui kasutatakse täpse vaste vaikeseadet, töötab see isegi siis, kui otsinguvahemiku väärtused pole kindlas järjekorras sorteeritud
• Saab otsida otsingumassiivi vahemikus alumisest reast üles, kasutades valikulist otsingurežiimi argumenti

Funktsioonil XLOOKUP on viis võimalikku argumenti, millest kolm esimest on kohustuslikud ja kaks viimast valikulised, kasutades järgmist süntaksit:

XLOOKUP ( lookup_value , otsingu_massiiv , return_array , [ match_mode ] [ search_mode ])

Nõutav argument lookup_value määrab väärtuse või üksuse, mida otsite. Nõutav look_up massiivi argument määrab lahtrivahemik tuleb otsida selle otsingu väärtus ja return_array argument määrab lahtrivahemik, mis sisaldab väärtust soovite tagastada, kui Excel leiab täpse vaste.

* Pidage meeles, kui määrate oma funktsioonis XLOOKUP argumendid lookup_array ja return_array, mõlemad vahemikud peavad olema võrdse pikkusega, vastasel juhul tagastab Excel väärtuse #VALUE! viga teie valemis. See on veelgi enam põhjus, miks peate argumentide määratlemisel kasutama määratud andmetabeli vahemiku- või veerunimesid, selle asemel, et neid esile tuua või nende lahtriviiteid sisestada .

Valikuline argument match_mode võib sisaldada mis tahes järgmist neljast väärtusest.

• 0 täpse vaste jaoks (vaikeväärtus, sama mis juhul, kui match_mode argumenti pole määratud)
• -1 täpse vaste või järgmise väiksema väärtuse jaoks
• 1 täpse vaste või järgmise suurema väärtuse jaoks
• 2 osalise vaste jaoks, kasutades argumendis lookup_value lahtri viitega ühendatud metamärke

Valikuline argument search_mode võib sisaldada mis tahes järgmist neljast väärtusest.

• 1, et otsida esimesest viimaseni, st ülalt alla (vaikeväärtus, sama, mis siis, kui otsingurežiimi argumenti pole määratud)
• -1, et otsida viimasest esimeseni, st alt üles
• 2 binaarseks otsinguks kasvavas järjekorras
• -2 binaarseks otsinguks kahanevas järjekorras

Parim viis uue XLOOKUP-funktsiooni võimsuse ja mitmekülgsuse mõistmiseks on näha seda Exceli töölehel. Järgmisel joonisel on mul tööleht lihtsa 2019. aasta müügiandmete tabeliga riikide kaupa. Kui soovite kasutada XLOOKUP-i selle tabeli kogumüügi tagastamiseks lahtris E4 töölehe lahtrisse D4 sisestatud riigi alusel, toimige järgmiselt.

Asetage lahtri kursor töölehe lahtrisse E4

Dialoogiboksi Funktsiooniargumendid avamiseks klõpsake vahekaardil Valemid valikut Otsimine ja viide, millele järgneb rippmenüü allservas XLOOKUP.

Klõpsake töölehel lahtrit D4, et sisestada selle lahtri viide argumendi Otsingu_väärtus tekstikasti.

Vajutage tabeldusklahvi, et valida argumendi Otsing_massiv, seejärel klõpsake lahtrit A4 ja hoidke all tõstuklahvi, kui vajutate Ctrl-allanoolt, et valida otsinguvahemikuks A4:A8 (kuna vahemik A3:B8 on määratletud Exceli andmetabelina, Tabel1[Riik] kuvatakse tekstikasti vahemiku A4:A8 asemel).

Vajutage Tab, et valida argumendi Return_array tekstiväli, seejärel klõpsake lahtrit B4 ja hoidke all tõstuklahvi, kui vajutate Ctrl-allanoolt, et valida B4:B8 vahemikuks, mis sisaldab otsingutulemuste põhjal tagastatavaid väärtusi (mis kuvatakse kui Tabel 1[Kogumüük] tekstikastis).

Klõpsake nuppu OK, et sisestada XLOOKUP valem lahtrisse E4.

Valemi loomine XLOOKUP-iga lahtris E4, mis tagastab müügi lahtrisse D4 sisestatud riigi alusel.

Excel sisestab XLOOKUP-i valemi töölehe lahtrisse E4 ja tagastab tulemuseks 4900, sest Costa Rica on praegu sisestatud otsingulahtrisse D4 ja nagu näete 2019. aasta müügitabelist, on see tõepoolest selle riigi kogumüük.

Kuna XLOOKUP töötab paremalt vasakule sama hästi kui vasakult paremale, saate seda funktsiooni sama hästi kasutada ka riigi tagastamiseks sellest müügitabelist konkreetse müüginumbri alusel. Järgmine joonis näitab, kuidas seda teha. Seekord loote lahtris D4 valemi XLOOKUP ja määrate lahtrisse E4 sisestatud väärtuse (antud juhul 11 ​​000) argumendiks lookup_value.

Lisaks sisestate match_mode argumendiks -1, et alistada funktsiooni täpse vaste vaikeväärtus, nii et Excel tagastab riigi täpse vastega otsingulahtrisse E4 sisestatud müügiväärtusele või riigile, mille kogumüük on järgmine madalam (Mehhiko koos Sel juhul 10 000 dollarit, kuna selles tabelis pole ühtegi riiki, kus kogumüügist oleks 11 000 dollarit). Ilma selle valemi jaoks argumendi match_mode määramata tagastaks Excel tulemuseks #NA, kuna selles müügitabelis pole täpset vastet 11 000 dollarile.

Valemi loomine XLOOKUP-iga lahtris D4, mis tagastab riigi lahtrisse E4 sisestatud müügi põhjal

Kuna funktsioon XLOOKUP on sama mugav otsida horisontaalselt veeru järgi kui ka vertikaalselt rea järgi, saate seda kasutada valemi loomiseks, mis sooritab kahesuunalise otsingu (asendab vajaduse luua valem, mis ühendab funktsioonid INDEX ja MATCH minevikus). Järgmine joonis, mis sisaldab osanumbrite AB-100 kuni AB-103 2019. aasta tootmisgraafiku tabelit aprillist detsembrini, näitab, kuidas seda tehakse.

Pesastatud XLOOKUP-funktsioonidega valemi loomine, et tagastada detaili konkreetse kuu jooksul toodetud ühikute arv

Lahtris B12 lõin järgmise valemi:

=XLOOKUP(osa_otsing,$A$3:$A$6,XLOOKUP(kuupäeva_otsing,$B$2:$J$2,$B$3:$J$6))

See valem algab funktsiooni XLOOKUP määratlemisega, mis otsib vertikaalselt rea järgi täpset vastet osa kirjele, mis on tehtud tootmistabeli lahtrivahemikus $A$3:$A$6 lahtris nimega part_lookup (antud juhul lahter B10). . Pange tähele, et selle algse funktsiooni LOOKUP argument return_array on ise teine ​​XLOOKUP-funktsioon.

See teine, pesastatud funktsioon XLOOKUP otsib lahtrivahemikust $B$2:$J$2 horisontaalselt veeru järgi, et leida täpne vaste kuupäevakirjele, mis on tehtud lahtris nimega date_lookup (antud juhul lahter B11). Selle teise pesastatud XLOOKUP-funktsiooni return_array argument on $B$3:$J$6, kõigi tabeli tootmisväärtuste lahtrivahemik.

See valem töötab nii, et Excel arvutab esmalt teise, pesastatud funktsiooni XLOOKUP tulemuse, teostades horisontaalotsingu, mis sel juhul tagastab massiivi 19. juuni veeru lahtrivahemikus D3: D6 (väärtustega: 438, 153, 306 ja 779) selle tulemusena. Sellest tulemusest saab omakorda algse XLOOKUP-i funktsiooni return_array argument, mis teostab vertikaalse otsingu rea järgi, et leida täpne vaste lahtris B11 tehtud osanumbri kirjele (nimega osa_otsing). Kuna selles näites sisaldab see osa_otsingu lahter AB-102, tagastab valem teise, järgmise funktsiooni XLOOKUP tulemusest ainult 19. juuni tootmisväärtuse 306.

Siin on see! Esimene pilk XLOOKUP-ile, võimsale, mitmekülgsele ja üsna lihtsalt kasutatavale uuele otsingufunktsioonile, mis ei suuda teha mitte ainult funktsioonide VLOOKUP ja HLOOKUP teostatavaid üheväärtuslikke otsinguid, vaid ka kahesuunalisi väärtusotsinguid, kombineerides INDEX ja MATCH funktsioonid samuti.

* Kahjuks ei ühildu funktsioon XLOOKUP tagasiulatuvalt Microsoft Exceli varasemate versioonidega, mis toetavad ainult funktsioone VLOOKUP ja HLOOKUP, ega ühildu praeguste versioonidega, mis seda veel ühe otsingufunktsioonina ei sisalda, näiteks Excel 2019 ja Excel Online . See tähendab, et kui jagate XLOOKUP-i valemeid sisaldavat töövihikut kaastöötajate või klientidega, kes kasutavad Exceli versiooni, mis seda uut otsingufunktsiooni ei sisalda, tagastavad kõik need valemid #NAME? veaväärtused, kui nad selle töölehe avavad.