Funktsioon XLOOKUP

Funktsiooni XLOOKUP abil saate tabelist või vahemikust otsida ridade järgi. Näiteks saate autoosa hinda otsida osanumbri järgi või otsida töötaja nime töötaja ID järgi. Funktsiooni XLOOKUP abil saate otsida otsinguterminit ühest veerust ja tagastada tulemi samast reast teises veerus, sõltumata sellest, millises tagastusveeru küljes on.

Märkus.: Rakenduses Excel 2016 ja Excel 2019 pole XLOOKUP saadaval. Siiski võib juhtuda, et kasutate töövihikut Excel 2016 või Excel 2019 koos funktsiooniga XLOOKUP, kui selle on loonud keegi teine, kes kasutab Exceli uuemat versiooni.

Teie brauser ei toeta videot. Installige Microsoft Silverlight, Adobe Flash Player või Internet Explorer 9.

Süntaks

Funktsioon XLOOKUP otsib vahemikust või massiivi ja tagastab siis üksuse, mis vastab esimesele leitud vastele. Kui vastet pole, saab XLOOKUP tagastada lähima (ligikaudse) vaste.

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])

Argument	Kirjeldus
Otsitav_väärtus Nõutav*	Väärtus, mida otsida Kui see puudub, tagastab funktsioon XLOOKUP lookup_array* leitud tühjad lahtrid.
Massiiv Nõutav	Otsitav massiiv või vahemik
return_array Nõutav	Tagastatav massiiv või vahemik
[if_not_found] Valikuline	Kui sobivat vastet ei leita, tagastage sisestatud tekst [if_not_found]. Kui sobivat vastet ei leita ja [if_not_found] puudub, tagastatakse #N/A .
[match_mode] Valikuline	Määrake vastendustüüp: 0 – täpne vaste. Kui ühtegi ei leitud, tagastage #N/A. – see on vaikesäte. -1 - Täpne vaste. Kui ühtegi üksust ei leitud, tagastage järgmine väiksem üksus. 1 – täpne vaste. Kui ühtegi üksust ei leitud, tagastage järgmine suurem üksus. 2 – metamärgi vaste, kus *, ?, ja ~ on eritähendus.
[search_mode] Valikuline	Määrake kasutatav otsingurežiim: 1. Sooritage otsing alates esimesest üksusest. – see on vaikesäte. -1 – sooritab pöördotsingu alates viimasest üksusest. 2. Sooritage kahendotsing, mis sõltub sellest, lookup_array sorditakse tõusvas järjestuses. Kui seda ei sordita, tagastatakse sobimatud tulemid. -2 – sooritage kahendotsing, mis tugineb lookup_array sortimisel laskuvas järjestuses. Kui seda ei sordita, tagastatakse sobimatud tulemid.

Argument

Kirjeldus

Otsitav_väärtus

Nõutav*

Väärtus, mida otsida *Kui see puudub, tagastab funktsioon XLOOKUP lookup_array leitud tühjad lahtrid.

Massiiv

Nõutav

Otsitav massiiv või vahemik

return_array

Nõutav

Tagastatav massiiv või vahemik

[if_not_found]

Valikuline

Kui sobivat vastet ei leita, tagastage sisestatud tekst [if_not_found].

Kui sobivat vastet ei leita ja [if_not_found] puudub, tagastatakse #N/A .

[match_mode]

Valikuline

Määrake vastendustüüp:

0 – täpne vaste. Kui ühtegi ei leitud, tagastage #N/A. – see on vaikesäte.

-1 - Täpne vaste. Kui ühtegi üksust ei leitud, tagastage järgmine väiksem üksus.

1 – täpne vaste. Kui ühtegi üksust ei leitud, tagastage järgmine suurem üksus.

2 – metamärgi vaste, kus *, ?, ja ~ on eritähendus.

[search_mode]

Valikuline

Määrake kasutatav otsingurežiim:

1. Sooritage otsing alates esimesest üksusest. – see on vaikesäte.

-1 – sooritab pöördotsingu alates viimasest üksusest.

2. Sooritage kahendotsing, mis sõltub sellest, lookup_array sorditakse tõusvas järjestuses. Kui seda ei sordita, tagastatakse sobimatud tulemid.

-2 – sooritage kahendotsing, mis tugineb lookup_array sortimisel laskuvas järjestuses. Kui seda ei sordita, tagastatakse sobimatud tulemid.

Näited

Näites 1 kasutatakse vahemikust riigi nime otsimiseks funktsiooni XLOOKUP ja seejärel tagastatakse telefoni riigikood. See sisaldab argumente lookup_value (lahter F2), lookup_array (vahemik B2:B11) ja argumente return_array (vahemik D2:D11). See ei sisalda argumenti match_mode , kuna XLOOKUP annab vaikimisi täpse vaste.

Näide funktsioonist XLOOKUP, mida kasutatakse töötaja ID-l põhineva töötaja nime ja osakonna tagastamiseks. Valem on =XLOOKUP(B2;B5:B14;C5:C14)

Märkus.: XLOOKUP kasutab otsingumassiivi ja tagastatavat massiivi, VLOOKUP aga ühte tabelimassiivi, millele järgneb veeruindeksi number. Antud juhul oleks samaväärne valem VLOOKUP järgmine: =VLOOKUP(F2;B2:D11;3;FALSE)

———————————————————————————

Näites 2 otsitakse töötaja teavet töötaja ID-numbri põhjal. Erinevalt funktsioonist VLOOKUP saab XLOOKUP tagastada mitme üksusega massiivi, nii et üks valem saab lahtritest C5:D14 tagastada nii töötaja nime kui osakonna.

Näide funktsioonist XLOOKUP, mida kasutatakse töötaja ID-l põhineva töötaja nime ja osakonna tagastamiseks. Valem on: =XLOOKUP(B2;B5:B14;C5:D14;0;1)

———————————————————————————

Näites 3 lisatakse eelmisele näitele argument if_not_found .

Näide funktsioonist XLOOKUP, mida kasutatakse töötaja ID-l põhineva töötaja nime ja osakonna tagastamiseks argumendiga if_not_found. Valem on =XLOOKUP(B2;B5:B14;C5:D14;0;1;"Töötajat ei leitud")

———————————————————————————

Näide 4 otsib veerust C lahtrisse E2 sisestatud isiklikku sissetulekut ja leiab veerust B vastava maksumäära. Kui midagi ei leita, määratakse argumendi if_not_found väärtuseks 0 (null). Argumendi match_mode väärtuseks on seatud 1, mis tähendab, et funktsioon otsib täpset vastet ja kui funktsiooni ei leita, tagastab funktsioon järgmise suurema üksuse. Lõpuks on argumendi search_mode väärtuseks seatud 1, mis tähendab, et funktsioon otsib esimesest üksusest viimaseni.

Pilt funktsioonist XLOOKUP, mida kasutatakse maksimaalsel tulul põhineva maksumäära tagastamiseks. See on ligikaudne vaste. Valem on: =XLOOKUP(E2;C2:C7;B2:B7;1;1)

Märkus.: XARRAY lookup_array veerg asub veerust return_array paremal, VLOOKUP aga ainult vasakult paremale.

———————————————————————————

Näide 5 kasutab nii vertikaalse kui ka horisontaalse vaste tegemiseks pesastatud funktsiooni XLOOKUP. Esmalt otsib see veerust B kogutulu , seejärel otsib tabeli ülemisest reast (vahemik C5:F5) 1. kvartalit ja tagastab lõpuks väärtuse kahe ristumiskohas. See sarnaneb funktsioonide INDEX ja MATCH koos kasutamisega.

Näpunäide.: Funktsiooni HLOOKUP asendamiseks saate kasutada ka funktsiooni XLOOKUP .

Pilt funktsioonist XLOOKUP, mida kasutatakse horisontaalsete andmete tabelist tagastamiseks, pesastades 2 XLOOKUP-i. Valem on: =XLOOKUP(D2;$B 6:$B 17;XLOOKUP($C 3;$C 5:$G 5;$C 6:$G 17))

Märkus.: Valem lahtrites D3:F3 on: =XLOOKUP(D2;$B 6:$B 17;XLOOKUP($C 3;$C 5:$G 5;$C 6:$G 17)))).

———————————————————————————

Näide 6 kasutab kõigi kahe vahemiku vaheliste väärtuste liitmiseks funktsiooni SUM ja kahte pesastatud XLOOKUP-funktsiooni. Praegusel juhul soovime liita viinamarjade, banaanide ja pirnide väärtused, mis asuvad nende kahe vahele jäävates pirnides.

Funktsiooni XLOOKUP kasutamine funktsiooniga SUM kahe valiku vahele jäävate väärtuste vahemiku liitmiseks

Valem lahtris E3 on: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))

Kuidas see töötab? XLOOKUP tagastab vahemiku, nii et arvutamisel näeb valem välja selline: =SUM($E$7:$E$9). Saate vaadata, kuidas see toimib ise, valides lahtri, kus on sarnane XLOOKUP-valem, seejärel valige Valemid > Valemiaudit > Valemi väärtustamine ja seejärel valige arvutuse läbimiseks Väärtusta.

Märkus.: Aitäh Microsoft Exceli MVP-le , Bill Jelenile selle näite soovitamise eest.

———————————————————————————

Lisateave

Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.

Funktsioon XMATCH

Exceli funktsioonid (tähestikuliselt)

Exceli funktsioonid (kategooriate kaupa)

Süntaks

Näited

Lisateave

Kas vajate veel abi?

Kas soovite rohkem valikuvariante?

Kas sellest teabest oli abi?

Täname tagasiside eest!