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.: XLOOKUP pole Excel 2016 ja Excel 2019-s saadaval, kuid võib juhtuda, et kasutate töövihikut rakenduses Excel 2016 või Excel 2019 funktsiooniga XLOOKUP, mille on loonud keegi teine, kes kasutab Exceli uuemat versiooni.
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 |
|
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.
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ä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 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 .
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.
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.
Kas vajate veel abi?
Kas soovite rohkem valikuvariante?
Siin saate tutvuda tellimusega kaasnevate eelistega, sirvida koolituskursusi, õppida seadet kaitsma ja teha veel palju muud.
Kogukonnad aitavad teil küsimusi esitada ja neile vastuseid saada, anda tagasisidet ja saada nõu rikkalike teadmistega asjatundjatelt.
