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. Kui XLOOKUP, saate otsida ühest veerust otsingusõna ja tagastada tulemi samast reast teises veerus, olenemata sellest, millisel küljel tagastusveerg asub.
Märkus.: XLOOKUP pole rakendustes Excel 2016 ja Excel 2019 saadaval. Kui töövihiku on loonud keegi teine, kes kasutab Exceli uuemat versiooni, võib juhtuda, et kasutate töövihikut rakenduses Excel 2016 või Excel 2019 koos funktsiooniga XLOOKUP.
Süntaks
Funktsioon XLOOKUP otsib vahemikust või massiivi ja tagastab siis esimesele leitud vastele vastava üksuse. Kui vastet pole, võib 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 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 XLOOKUP riigi nime otsimiseks vahemikust 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.
Märkus.: XLOOKUP kasutab otsingumassiivi ja tagastusmassiivi, 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 kui ka osakonna.
———————————————————————————
Näites 3 lisatakse eelmisele näitele argument if_not_found .
———————————————————————————
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.
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 XLOOKUP funktsiooni. 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 saate asendada ka XLOOKUP abil.
Märkus.: Lahtrites D3:F3 on valem: =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.
Valem lahtris E3 on järgmine: =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 ise toimib, kui valite lahtri, millel on selle valemiga 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.
