VLOOKUP

Näpunäide.: Proovige kasutada uut funktsiooni XLOOKUP , mis on funktsiooni VLOOKUP täiustatud versioon, mis töötab mis tahes suunas ja tagastab vaikimisi täpsed vasted, mis muudab selle kasutamise lihtsamaks ja mugavamaks kui eelkäija.

Kasutage funktsiooni VLOOKUP, kui teil on vaja 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.

Kõige lihtsamal kujul ütleb VLOOKUP-funktsioon järgmist:

=VLOOKUP(Mida soovite otsida, kust soovite seda otsida, tagastatavat väärtust sisaldava veeru number vahemikus, tagastab ligikaudse või täpse vaste – tähistatud kui 1/TRUE või 0/FALSE).

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

Näpunäide.: Funktsiooni VLOOKUP saladuseks on andmete korraldamine nii, et otsitav väärtus (Fruit) jääb otsitavast tagastusväärtusest (amount) vasakule.

Funktsiooni VLOOKUP abil saate otsida tabelist väärtust.

Süntaks

VLOOKUP (otsitav_väärtus; tabeli_massiiv; veeru_indeks; [vastendustüüp])

Siin on mõned näited.

=VLOOKUP(A2;A10:C20;2;TRUE)
=VLOOKUP("Fontana",B2:E7,2,FALSE)
=VLOOKUP(A2,'Kliendi üksikasjad'! A:F,3;FALSE)

Argumendi nimi	Kirjeldus
otsitav_väärtus (nõutav)	Väärtus, mida soovite otsida. Väärtus, mida soovite otsida, peab olema argumendis *table_array* määratud lahtrivahemiku esimeses veerus. Näiteks kui *tabelimassiiv* hõlmab lahtreid B2:D7, peab teie lookup_value olema veerus B. *Otsitav_väärtus* võib olla väärtus või viide lahtrile.
tabeli_massiiv (nõutav)	Lahtrivahemik, kust VLOOKUP otsib ja kus paiknevad soovitud *otsitav_väärtus* ning tagastatav väärtus. Võite kasutada nimega vahemikku või tabelit ja kasutada argumendis lahtriviidete asemel nimesid. Lahtrivahemiku esimene veerg peab sisaldama *lookup_value*. Lahtrivahemik peab sisaldama ka tagastatavat väärtust, mida soovite leida. Lugege teavet selle kohta, kuidas valida vahemikke töölehel.
veeru_indeks (nõutav)	Tagastatavat väärtust sisaldav veerunumber (alates *table_array* vasakpoolseimast veerust 1).
range_lookup (valikulne)	Loogikaväärtus, mis määrab, kas funktsioon VLOOKUP leiab ligikaudse või täpse vaste. Ligikaudne vaste – 1/TRUE eeldab, et tabeli esimene veerg on sorditud numbriliselt või tähestikuliselt ja otsib siis lähimat väärtust. See on vaikimisi kohaldatav meetod, kui jätate meetodi määratlemata. Näiteks =VLOOKUP(90;A1:B100;2;TRUE). Täpne vaste – 0/FALSE otsib esimesest veerust täpset väärtust. Näiteks =VLOOKUP("Kask";A1:B100;2;FALSE).

Alustamine

Funktsiooni VLOOKUP süntaksi loomiseks on vaja nelja liiki teavet.

Väärtus, mida soovite otsida (seda nimetatakse ka otsinguväärtuseks).
Vahemik, kus otsinguväärtus asub. Pidage meeles, et funktsiooni VLOOKUP sujuvaks toimimiseks peaks otsinguväärtus olema alati vahemiku esimeses veerus. Näiteks kui teie otsinguväärtus on lahtris C2, peaks vahemik algama C-ga.
Tagastatavat väärtust sisaldava veeru number vahemikus. Näiteks kui määrate vahemikuks B2:D11, tuleks B loendada esimeseks veeruks, C teiseks jne.
Soovi korral saate määrata väärtuseks TRUE, kui soovite tagastatava väärtuse ligikaudset vastet, või FALSE, kui soovite täpset vastet. Kui jätate väärtuse määramata, on vaikeväärtus alati TRUE ehk ligikaudne vaste.

Kui kõik need argumendid kokku panna, saate järgmise valemi:

=VLOOKUP(otsinguväärtus; otsinguväärtust sisaldav vahemik; tagastatavat väärtust sisaldava veeru number vahemikus, ligikaudne vaste (TRUE) või täpne vaste (FALSE)).

Näited

Siin on mõned näited funktsioonist VLOOKUP.

Näide 1

=VLOOKUP (B3;B2:E7;2;FALSE)

Funktsioon VLOOKUP otsib table_array B2:E7 esimesest veerust (veerust B) Fontanat ja tagastab table_array teise veeru (veeru C) väärtuse Olivier. False tagastab täpse vaste.

Näide 2

=VLOOKUP (102;A2:C7;2;FALSE)

Funktsioon VLOOKUP otsib vahemiku A2:C7 teisest veerust (veerust B) 102 (lookup_value) perekonnanime täpset vastet (FALSE) ja tagastab väärtuse Fontana.

Näide 3

=IF(VLOOKUP(103;A1:E7;2;FALSE)="Souse";"Located";"Not found")

IF kontrollib, kas funktsioon VLOOKUP tagastab väärtuse Sousa töötaja korrespondeerimise perekonnanimena lahtrisse A1:E7 (lookup_value) (table_array). Kuna 103-le vastav perekonnanimi on Leal, on IF-tingimus väär ja kuvatakse ei leitud.

Näide 4

=INT(YEARFRAC(DATE(2014;6;30);VLOOKUP(105;A2:E7;5;FLASE);1))

Funktsioon VLOOKUP otsib vahemikust A2:E7 töötaja sünnikuupäeva, mis vastab väärtusele 109 (lookup_value) (table_array) ja tagastab väärtuse 03/04/1955. Seejärel lahutab YEARFRAC sünnikuupäeva 2014/6/30 ja tagastab väärtuse, mille INY teisendab seejärel täisarvuks 59.

Näide 5

IF(ISNA(VLOOKUP(105;A2:E7;2;FLASE))=TRUE;"Töötajat ei leitud";VLOOKUP(105;A2:E7;2;FALSE))

IF kontrollib, kas funktsioon VLOOKUP tagastab 105 (lookup_value) veerust B perekonnanime väärtuse. Kui funktsioon VLOOKUP leiab perekonnanime, kuvab funktsioon IF perekonnanime, vastasel juhul tagastab funktsioon IF väärtuse Töötaja ei leitud. ISNA tagab, et kui funktsioon VLOOKUP tagastab #N/A, asendatakse viga #N/A asemel töötajaga, keda ei leitud.

Selles näites on tagastusväärtus Burke, mis on 105-le vastav perekonnanimi.

Funktsiooni VLOOKUP saate kasutada mitme tabeli ühendamiseks üheks tabeliks, kui ühel tabelil on kõigi teistega ühised väljad. See võib olla eriti kasulik siis, kui peate töövihikut ühiselt kasutama inimestega, kellel on Exceli varasemad versioonid, mis ei toeta andmeallikatena mitme tabeli andmefunktsioone– kui kombineerite allikad üheks tabeliks ja muudate andmefunktsiooni andmeallika uueks tabeliks, saab andmefunktsiooni kasutada Exceli vanemates versioonides (eeldusel, et varasem versioon toetab andmefunktsiooni ise).

Veergudega tööleht, mis kasutab andmete toomiseks muudest tabelitest funktsiooni VLOOKUP

Siin sisaldavad veerud A–F ja H väärtusi või valemeid, mis kasutavad ainult töölehel olevaid väärtusi, ülejäänud veerud kasutavad funktsiooni VLOOKUP ning veeru A (kliendikood) ja veeru B (Attorney) väärtusi muudest tabelitest andmete toomiseks.

Kopeerige üldväljadega tabel uuele töölehele ja pange sellele nimi.
Dialoogiboksi seoste haldamine avamiseks klõpsake nuppu Andmed > andmeriistad > seosed.
Pange tähele iga loetletud seose kohta järgmist.
- Tabeleid lingiv väli (dialoogiboksis sulgudes). See on teie funktsiooni VLOOKUP valemi lookup_value .
- Seotud otsingutabeli nimi. See on teie funktsiooni VLOOKUP valemi table_array .
- Väli (veerg) seotud otsingutabelis, mis sisaldab uues veerus soovitud andmeid. Seda teavet ei kuvata seoste haldamine dialoogiboksis. Toodava välja vaatamiseks peate vaatama seostuva otsingu tabelit. Soovite märkida veerunumbri (A=1) – see on teie valemis col_index_num .
Uude tabelisse välja lisamiseks sisestage oma VLOOKUP-valem esimesse tühja veergu, kasutades 3. toimingu käigus kogutud teavet.

Meie näites kasutab veerg G funktsiooni Attorney ( lookup_value), et tuua arvemäära andmed neljandast veerust (col_index_num = 4) töölehe attorneys tabelist tblAttorneys ( the table_array) valemiga =VLOOKUP([@Attorney],tbl_Attorneys;4,FALSE).

Valem võib kasutada ka lahtriviidet ja vahemikuviidet. Meie näites oleks selleks =VLOOKUP(A2,'Attorneys'! A:D,4;FALSE).
Jätkake väljade lisamist, kuni teil on kõik vajalikud väljad. Kui proovite ette valmistada töövihikut, mis sisaldab mitut tabelit kasutavaid andmefunktsioone, muutke andmefunktsiooni andmeallikas uueks tabeliks.

Probleem	Mis läks valesti
Tagastati vale väärtus	Kui *vastendustüüp* on TRUE või välja jäetud, peab esimene veerg olema sorditud tähestikuliselt või arvuliselt. Kui esimene veerg on sortimata, võidakse tagastada väärtus, mida te ei eeldanud. Peate kas veeru sortima või kasutama täpse vaste saamiseks argumenti FALSE.
Lahtris on veaväärtus #N/A	Kui *vastendustüüp* on TRUE, siis juhul, kui *otsitav_väärtus* on väiksem kui vähim väärtus *tabelimassiivi* esimeses veerus, kuvatakse tulemuseks veaväärtus #N/A. Kui *vastendustüüp* on FALSE, näitab veaväärtuse #N/A, et täpset arvu ei leitud. Lisateavet #N/A vigade parandamise kohta funktsioonis VLOOKUP leiate teemast Vea #N/A parandamine funktsioonis VLOOKUP.
#REF! lahtris	Kui *col_index_num* on suurem kui *veergude arv tabelimassiivis*, saate #REF! #NUM!. Lisateavet #REF! vigade parandamise kohta funktsioonis VLOOKUP leiate teemast Vea #REF! parandamine.
#VALUE! lahtris	Kui *table_array* on väiksem kui 1, saate #VALUE! #NUM!. Lisateavet #VALUE! vigade parandamise kohta funktsioonis VLOOKUP leiate teemast Vea #VALUE! parandamine funktsioonis VLOOKUP.
#NAME? lahtris	Veaväärtus #NAME? tähendab tavaliselt, et valemis puuduvad jutumärgid. Kui otsite isiku nime, veenduge, et kasutate valemis nime ümber jutumärke. Sisestage näiteks nimi"Fontana" valemisse = VLOOKUP("Fontana";B2:E7;2;FALSE). Lisateavet leiate teemast Vea #NAME! parandamine.
#SPILL! lahtris	See konkreetne #SPILL! tavaliselt tähendab see, et valem tugineb otsinguväärtuse ilmutamatule ühisosale ja kasutab viitena kogu veergu. Näiteks =VLOOKUP(A:A;A:C;2;FALSE). Probleemi lahendamiseks ankurdage otsinguviide tehtemärgiga @: =VLOOKUP(@A:A;A:C;2;FALSE). Teine võimalus on kasutada traditsioonilist meetodit VLOOKUP ja viidata terve veeru asemel ühele lahtrile: =VLOOKUP(A2;A:C;2;FALSE).

Toiming	Miks seda tegema peaks?
Kasutage argumendi vastendustüüp korral absoluutviiteid.	Absoluutviidete kasutamise korral saate allpool olevad külgnevad lahtrid täita valemiga nii, et see on alati suunatud täpselt samasse otsinguvahemikku. Lugege teavet selle kohta, kuidas kasutada absoluutseid lahtriviiteid.
Ärge salvestage arv- ega kuupäevaväärtusi tekstina.	Arv- või kuupäevaväärtusi otsides veenduge, et *tabelimassiivi* esimese veeru andmed poleks talletatud tekstväärtustena. Muidu võib funktsioon VLOOKUP tagastada vale või mitte-eeldatud väärtuse.
Sortige esimene veerg	Kui *vastendustüüp* on TRUE, sortige enne funktsiooni VLOOKUP kasutamist tabelimassiivi esimene veerg.
Kasutage metamärke	Kui *range_lookup* on FALSE ja *lookup_value* on tekst, saate *lookup_value* kasutada metamärke – küsimärki (?) ja tärni (). Küsimärk vastab mis tahes üksikmärgile. Tärn vastab mis tahes märgijadale. Kui soovite otsida tegelikku küsimärki või tärni, tippige märgi ette tilde (~). Näiteks valem =VLOOKUP("Fontan?";B2:E7;2;FALSE) otsib kõiki Fontana* eksemplare, mille viimane täht võib muutuda.
Veenduge, et teie andmed ei sisaldaks vigaseid märke.	Esimesest veerust tekstväärtusi otsides veenduge, et esimese veeru andmed ei sisaldaks algus- ega lõputühikuid, ebaühtlast sirgete (' või ") ja kõverate (‘ või “) ülakomade ning jutumärkide kasutust ega mitteprinditavaid märke. Muidu võib funktsioon VLOOKUP tagastada vale või mitteootuspärase väärtuse. Õigete tulemuste saamiseks proovige kasutada funktsiooni CLEAN või funktsiooni TRIM, et eemaldada lahtriväärtuste järelt lõputühikud.

Kas vajate rohkem abi?

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