Funktsioon XLOOKUP

Kasutage funktsiooni XLOOKUP , kui teil on vaja otsida asju tabelist või vahemikust realt. 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.

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

Märkus.: 25. november 2019: XLOOKUP on praegu beetaversioon ja saadaval ainult osa Office Insideri programmis osalejatest. Jätkame selle optimeerimist järgmise mitme kuu jooksul. Kui XLOOKUP on valmis, vabastame selle kõigile Office Insideri programmis osalejatele ja office 365 tellijatele.

Funktsioon XLOOKUP otsib vahemikku või massiivi ja annab tulemiks 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

Nõutav

Otsingu väärtus

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, siis 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

See näide on ülaltoodud videost ja kasutab riigi nime otsimiseks lihtsat XLOOKUP ning tagastab selle telefoninumbri koodi. See sisaldab ainult lookup_value (lahter F2), lookup_array (vahemik B2: B11) ja return_array (vahemik D2: D11) argumendid. See ei sisalda argumendi match_mode argumenti, kuna XLOOKUP vaikeväärtus on täpne vaste.

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 erineb funktsioonist VLOOKUP, sest see kasutab eraldi otsingu-ja tagastamise massiive, kus 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äide 2

Selles näites Otsime töötajate andmeid töötaja ID-numbri alusel. Erinevalt funktsioonist VLOOKUP saab XLOOKUP tagastada mitme üksusega massiivi, mis võimaldab ühe valemi abil tagastada nii töötaja nime kui ka osakonna alates lahtrid 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äide 3

Selles näites lisatakse ülaltoodud näitele if_not_found argument.

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äide 4

Järgmises näites vaadeldakse lahtris E2 sisestatud isiklike sissetulekute veergu C ja leitakse veeru B vastava maksumäära. See määrab IF-not_found argumendi 0 tagastamiseks, 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.: Erinevalt funktsioonist VLOOKUP on lookup_array veerg return_array veerust paremal, kus VLOOKUP saab vaadata ainult vasakult paremale.

Näide 5

Järgmiseks kasutame pesastatud XLOOKUP funktsiooni nii vertikaalseks kui ka horisontaalseks sobitamiseks. Sellisel juhul otsib see esmalt veeru B brutokasum , seejärel otsib Kv1 tabeli ülemises reas (vahemik C5: F5) ja tagastab väärtuse kahe ristumiskohas. See sarnaneb funktsioonide index ja Match kasutamisele koostoimes. 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))

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

Selles näites kasutatakse funktsiooni SUMja kahe vahemiku vahel kõigi väärtuste summeerimiseks pesastatud XLOOKUP funktsioone. 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 > valemi auditeerimine > väärtusta valemitja vajutage 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)

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.

×