Valemid ja funktsioonid

Funktsioon XLOOKUP

Funktsioon XLOOKUP

Kasutage funktsiooni XLOOKUP , et leida ridu tabelist või vahemikust. Näiteks leiate lisateavet autovaruosade hinnast osa numbri järgi või leida töötaja nime põhjal töötaja ID. Funktsiooni XLOOKUP abil saate otsida ühe veeruga otsingusõna ja tagastada tulemi samast reast teise veeru kaudu, sõltumata sellest, milline pool on tagastatava veeru sees.

Surface Booki seadme foto

Süntaks

Funktsioon XLOOKUP otsib vahemikku või massiivi ja seejärel annab esimesele vastele vastava üksuse. Kui vastet ei ole, 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

Vaja

Otsingu väärtus

* Kui argument on tühi, kasutatakse sobitamiseks tühja lahtrit.   

Märkus.: Tühi string pole sama, mis tühi lahter, mis ei sobi lookup_value ja tekitab tõrke.

Massiiv

Nõutav

Otsitav massiiv või vahemik

return_array

Nõutav

Massiiv või vahemik, mida soovite tagastada

[if_not_found]

Valikuline

Kui sobivat vastet ei leita, tagastab teile tarnitava teksti [if_not_found].

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

[match_mode]

Valikuline

Määrake vaste tüüp:

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

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

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

2-metamärkide vaste kus *,? ja ~ on eriline tähendus.

[search_mode]

Valikuline

Kasutatava otsingu režiimi määramine.

1 – otsingu sooritamine esimesest üksusest alates. – see on vaikesäte.

-1 – viimasest üksusest algab vastupidine otsing.

2 – saate teha binaarse otsingu, mis tugineb lookup_array sorditakse tõusvas järjestuses. Kui seda ei sordita, tagastatakse sobimatud tulemid.

-2 – binaarne otsing, mis sõltub lookup_array sorditakse laskuvas järjestuses. Kui seda ei sordita, tagastatakse sobimatud tulemid.

Näited

Näide 1    kasutab XLOOKUP, et otsida vahemikust riigi nimi ja tagastada selle telefoninumbri kood. See sisaldab lookup_value (lahter F2), Lookup_array (vahemik B2: B11) ja Return_array (vahemik D2: D11) argumendid. See ei sisalda argumenti match_mode , sest XLOOKUP toodab vaikimisi täpset vastet.

Töötaja ID-l põhineva töötaja nime ja osakonna tagastamiseks kasutatava funktsiooni XLOOKUP näide. Valem on = XLOOKUP (B2; B5: B14; C5: C14).

Märkus.: XLOOKUP kasutab nii otsinguveeru kui ka tagastatavat massiivi, samal ajal kui funktsioon VLOOKUP kasutab ühte tabeli massiivi, millele järgneb veeru indeksi number. Sel juhul oleks samaväärne VLOOKUP valem: = VLOOKUP (F2; B2: D11; 3; FALSE)

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

Näites 2    vaadeldakse töötajate andmeid töötaja ID-numbri alusel. Erinevalt funktsioonist VLOOKUP võivad XLOOKUP tagastada massiivid mitme üksusega, nii et ühe valemi abil saab tagastada nii töötaja nime kui ka osakonna lahtritest C5: D14.

Näide töötaja IDt alusel töötaja nime ja osakonna tagastamiseks kasutatavast XLOOKUP. Valem on: = XLOOKUP (B2; B5: B14; C5: D14; 0; 1)

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

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

Näide funktsiooni XLOOKUP, mille abil tagastatakse töötaja ID-l põhinev töötaja ID if_not_found argumendiga. Valem on = XLOOKUP (B2; B5: B14; C5: D14; 0; 1; "töötajat ei leitud")

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

Näites 4    vaadeldakse lahtris E2 sisestatud isiklike sissetulekute veergu C ja leitakse veeru B vastava maksumäära. See seab argumendi if_not_found tagastamiseks 0 (null), kui midagi ei leita. Argumendi match_mode väärtuseks on seatud 1, mis tähendab, et funktsioon otsib täpset vastet ja kui seda ei leita, tagastatakse järgmine suurem üksus. Lõpetuseks on argumendi search_mode väärtuseks 1, mis tähendab, et funktsioon otsib esimesest üksusest viimaseni.

Pilt XLOOKUP, mida kasutatakse maksumäära tagastamiseks maksimaalse sissetuleku alusel. See on ligikaudne vaste. Valem on: = XLOOKUP (E2; C2: C7; B2: B7; 1; 1)

Märkus.: XARRAY ' i lookup_array veerg on return_array veerust paremal, samas kui funktsioon VLOOKUP saab otsida ainult vasakult paremale.

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

Näide 5    kasutab pesastatud XLOOKUP funktsiooni nii vertikaalseks kui ka horisontaalseks sobitamiseks. Kõigepealt otsitakse brutokasum veerus B, seejärel otsitakse Kv1 tabeli ülemises reas (vahemik C5: F5) ja lõpuks tagastatakse selle kahe ristumiskohas olev väärtus. See on sarnane funktsioonide index ja Match kasutamisest koos.

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

Pilt XLOOKUP funktsioonist, mida kasutatakse tabeli horisontaalsete andmete tagastamiseks, pesastades 2 XLOOKUPs. 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 funktsiooni SUMja kahe pesastatud XLOOKUP funktsioone kõigi kahe vahemiku vahel olevate väärtuste summeerimiseks. Sellisel juhul soovime liita viinamarjade, banaanide ja nende kahe vahel olevate pirnide väärtused.

XLOOKUP kasutamine koos SUMMAga, mis summeerib kahe valiku vahele jäävad väärtuste vahemiku.

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 kui see arvutatakse, jõuab valem järgmisega: = SUM ($E $7: $E $9). Saate vaadata, kuidas see toimib ise, kui valite lahtri, millel on sama XLOOKUP valem, ja seejärel valige valemid > valemite auditeerimine > väärtusta valemitja seejärel klõpsake arvutuse tegemiseks nuppu väärtusta .

Märkus.: Tänu Microsoft Exceli MVP-ile, Bill Jelen, selle näite soovitamiseks.

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

Kas vajate rohkem abi?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Lisateave

Funktsioon XMATCH

Exceli funktsioonid (tähestikuliselt)

Exceli funktsioonid (kategooriate kaupa)

Kas vajate veel abi?

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×