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

Kasutage funktsiooni VLOOKUP, kui peate tabelist või vahemikust rea järgi asju otsima. Näiteks saate otsida autotööstuse osa hinda osanumbri järgi või leida töötaja nime töötaja ID põhjal.

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

=VLOOKUP(Mida soovite otsida, kust soovite seda otsida, tagastamist sisaldava vahemiku veerunumber, tagastab ligikaudse või täpse vaste , mis on tähistatud väärtusega 1/TRUE või 0/FALSE).

Teie brauser ei toeta videot.

Näpunäide.: Funktsiooni VLOOKUP saladus on korraldada andmed nii, et otsitav väärtus (puuvili) jääb otsitavast tagastatavast väärtusest (summast) 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;'Client Details'! 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 lahtrivahemiku esimeses veerus.

Näiteks kui tabelimassiiv hõlmab lahtreid B2:D7, peab 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. Saate kasutada nimega vahemikku või tabelit ja lahtriviidete asemel argumendis nimesid. 

Lahtrivahemiku esimene veerg peab sisaldama lookup_value. Lahtrivahemik peab sisaldama ka otsimist vajava tagastusväärtuse.

Lugege teavet selle kohta, kuidas valida vahemikke töölehel.

veeru_indeks    (nõutav)

Tagastamisväärtust sisaldav veerunumber (alates 1-st table_arrayveerust).

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 arvuliselt või tähestikuliselt ning otsib seejärel 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 esimese veeru täpset väärtust. Näiteks =VLOOKUP("Smith";A1:B100;2;FALSE).

Alustamine

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

  1. Väärtus, mida soovite otsida (seda nimetatakse ka otsinguväärtuseks).

  2. 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.

  3. Tagastatavat väärtust sisaldava veeru number vahemikus. Näiteks kui määrate vahemikuks B2:D11, peaksite loendama B esimese veeruna, C teisena jne.

  4. 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, tagastamisväärtust sisaldav veerunumber, ligikaudne vaste (TRUE) või Täpne vaste (FALSE)).

Näited

Siin on mõned VLOOKUP-i näited.

Näide 1

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

Funktsioon VLOOKUP otsib funktsiooni table_array B2:E7 esimesest veerust (veerus B) olevat Fontanat ja tagastab table_array.  False tagastab täpse vaste.

Näide 2

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

Funktsioon VLOOKUP otsib vahemikus A2:C7 teise veeru (veerg B) perekonnanime 102 (lookup_value) 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 Sousa töötaja perekonnanimena, mis on korrelatsioonis arvuga 103 (lookup_value) väärtuses A1:E7 (table_array). Kuna 103-le vastav perekonnanimi on Leal, on IF-tingimus väär ja kuvatakse Not Found (Ei leitud).

Näide 4

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

Funktsioon VLOOKUP otsib töötaja sünnikuupäeva, mis vastab vahemikus A2:E7 (lookup_value) arvule 109 (table_array) ja tagastab 1955. aasta 03.04.1955. Seejärel lahutab YEARFRAC selle sünnikuupäeva 2014/6/30 ja tagastab väärtuse, mille INY teisendab 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 veeru B perekonnanime väärtuse (lookup_value). Kui funktsioon VLOOKUP leiab perekonnanime, kuvab FUNKTSIOON IF perekonnanime, vastasel juhul tagastab funktsioon IF väärtuse Töötajat ei leitud. ISNA tagab, et kui funktsioon VLOOKUP tagastab #N/A, asendatakse tõrge funktsiooni #N/A asemel töötajaga.



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

Funktsiooni VLOOKUP abil saate ühendada mitu tabelit üheks, kui ühel tabelist on kõigi teistega ühised väljad. See võib olla eriti kasulik, kui soovite töövihiku ühiskasutusse anda inimestega, kellel on vanemad Excel-i versioonid, mis ei toeta andmeallikana mitme tabeli andmefunktsioone – kombineerides allikad ühte tabelisse ja muutes andmefunktsiooni andmeallika uueks tabeliks, saab andmefunktsiooni kasutada vanemates Excel-versioonides (eeldusel, et vanem versioon toetab andmefunktsiooni ennast).

Tööleht veergudega, mis kasutavad andmete toomiseks muudest tabelitest funktsiooni VLOOKUP

Siin on veergudel A-F ja H väärtused või valemid, mis kasutavad ainult töölehel väärtusi, ülejäänud veergudes kasutatakse andmete toomiseks muudest tabelitest VLOOKUP-i ning veeru A (Kliendikood) ja veeru B (Attorney) väärtusi.

  1. Kopeerige tabel, kus on levinud väljad uuele töölehele, ja andke sellele nimi.

  2. Dialoogiboksi Seoste > avamiseks klõpsake > andmeriistad nuppu Andmeriistad ja seosed.

    Dialoogiboks Seoste haldamine
  3. Pange iga loetletud seose kohta tähele järgmist.

    • Tabeleid linkiv väli (loetletud dialoogiboksis sulgudes). See on lookup_value VLOOKUP-valemi jaoks.

    • Seotud otsingutabeli nimi. See on table_array VLOOKUP-valemis.

    • Väli (veerg) seostuvas otsingutabelis, kus on uues veerus soovitud andmed. Seda teavet ei kuvata dialoogiboksis Seoste haldamine – selle välja kuvamiseks, mida soovite tuua, peate vaatama seostuvat otsingutabelit. Soovite märkida veerunumbri (A=1) – see on col_index_num valem.

  4. Välja lisamiseks uude tabelisse sisestage oma VLOOKUP-i valem esimesse tühja veergu, kasutades 3. juhises kogutud teavet.

    Meie näites kasutab veerg G töölehetabeliSt Attorney (lookup_value) neljanda veeru(col_index_num = 4) arvemäära andmete toomiseks tabelist Attorneys (tblAttorneys( table_array)valemiga =VLOOKUP([@Attorney],tbl_Attorneys;4;FALSE).

    Valem võib kasutada ka lahtriviidet ja vahemikuviidet. Meie näites oleks see =VLOOKUP(A2;'Attorneys'! A:D;4;FALSE).

  5. Jätkake väljade lisamist, kuni teil on kõik vajaminevad väljad. Kui proovite ette valmistada mitut tabelit kasutav andmefunktsioone sisaldav töövihik, 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 tabelimassiivi veergude arv,saate #REF! #NUM!.

Lisateavet #REF! vigade parandamise kohta funktsioonis VLOOKUP leiate teemast Vea #REF! parandamine.

Veaväärtus #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 #SPILL! Tavaliselt tähendab see, et valem tugineb otsinguväärtuse kaudsele ristumiskohale ja kasutab viitena tervet veergu. Näiteks =VLOOKUP(A:A;A:C;2;FALSE). Probleemi lahendamiseks ankurdage otsinguviide @-tehtemärki järgmisega: =VLOOKUP(@A:A,A:C;2;FALSE). Teise võimalusena saate kasutada traditsioonilist meetodit VLOOKUP ja viidata terve veeru asemel ühelelahtrile: =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 kasutada metamärke ( küsimärki (?) ja tärni (*)– lookup_value. 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 =VLOOKUP("Fontan?";B2:E7;2;FALSE) otsib kõiki Fontana eksemplare viimase tähega, mis võib erineda.

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 Answersi kogukonnafoorumis.

Lisateave

Kiirülevaate kaart: VLOOKUP-i värskendaja
Kiirülevaate kaart: VLOOKUP-i tõrkeotsingu näpunäited
Funktsiooni VLOOKUP vea #VALUE!
Funktsiooni VLOOKUP #N/A vea parandamiseks
Ülevaade valemitest Excel
Vigaste valemite vältimiseks
Valemivigade tuvastamine
Excel (tähestikuline)
Excel funktsioonid (kategooriate kaupa)
VLOOKUP (tasuta eelvaade)

Kas vajate veel abi?

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega

Kas sellest teabest oli abi?

Kui rahul te keelekvaliteediga olete?
Mis mõjutas teie hinnangut?

Täname tagasiside eest!

×