VLOOKUP funktsioon Excelis otsib väärtust lahtrivahemikus, seejärel tagastab väärtuse, mis asub otsitava väärtusega samal real.
VLOOKUP ehk vertikaalotsing on otsingufunktsioon, mis otsib väärtust vahemiku kõige vasakpoolsemast veerust (esimesest veerust) ja tagastab paralleelväärtuse sellest paremal asuvast veerust. Funktsioon VLOOKUP otsib vertikaalselt paigutatud tabelis ainult väärtust üles (ülevalt alla).
Näiteks oletame, et töölehel on varude loend, kus on tabel, mis näitab kaupade nimesid, ostukuupäeva, kogust ja hinda. Seejärel saaksime kasutada VLOOKUP-i mõnel teisel töölehel, et eraldada varude töölehelt teatud kaubanime kogus ja hind.
Funktsioon VLOOKUP võib alguses tunduda hirmutav, kuid tegelikult on seda üsna lihtne kasutada, kui mõistate, kuidas see töötab. Siin, selles õpetuses, näitame teile, kuidas kasutada Excelis funktsiooni VLOOKUP.
VLOOKUP Süntaks ja argumendid
Kui kavatsete kasutada funktsiooni VLOOKUP, peate teadma selle süntaksit ja argumente.
Funktsiooni VLOOKUP süntaks:
=VLOOKUP(otsingu_väärtus,tabeli_massiiv,veeruindeksi_arv,[vahemiku_otsing])See funktsioon koosneb neljast parameetrist või argumendist:
- lookup_value: See määrab väärtuse, mida otsite antud tabelimassiivi esimesest veerust. Otsingu väärtus peab alati asuma kõige vasakpoolsemas (otsingutabeli veerus.
- table_array: See on tabel (lahtrivahemik), millest soovite väärtust otsida. See tabel (otsingutabel) võib olla samal töölehel või erineval töölehel või isegi erineval töövihikul.
- col_index_num: See määrab tabelimassiivi veeru numbri, millel on väärtus, mida soovite ekstraktida.
- [range_lookup]: See parameeter määrab, kas soovite ekstraktida täpse või ligikaudse vaste. See on kas TRUE või FALSE, sisestage "FALSE", kui soovite täpset väärtust, või sisestage "TRUE", kui olete ligikaudse väärtusega korras.
Funktsiooni VLOOKUP kasutamine Excelis
Uurime, kuidas VLOOKUP-i Microsoft Excelis kasutada.
Põhinäide
VLOOKUP-i kasutamiseks peate esmalt looma andmebaasi või tabeli (vt allpool).
Seejärel looge tabel või vahemik, kust soovite otsida, ja eraldage väärtused otsingutabelist.
Järgmisena valige lahter, kuhu soovite ekstraheeritud väärtuse, ja sisestage järgmine VLOOKUP-valem. Näiteks tahame otsida 'Ena' telefoninumbrit, siis peame sisestama otsinguväärtuseks B13, tabelimassiiviks A2:E10, telefoninumbri veeru numbriks 5 ja täpse väärtuse tagastamiseks FALSE. väärtus. Seejärel vajutage valemi lõpetamiseks sisestusklahvi.
=VLOOKUP(B13,A2:E10,5,FALSE)Te ei pea tabelivahemikku käsitsi tippima, saate lihtsalt valida vahemiku või tabeli, kasutades argumendi table_array jaoks hiirt. Ja see lisatakse argumendile automaatselt.
Pidage meeles, et selle toimimiseks peab otsinguväärtus olema meie otsingutabeli vasakpoolsemas servas (A2:E10). Samuti ei pea Otsingu_väärtus tingimata olema töölehe veerus A, see peab lihtsalt olema otsitava vahemiku vasakpoolseim veerg.
Vlookup näeb õige välja
Funktsioon VLOOKUP saab vaadata ainult tabelist paremale. See otsib väärtust tabeli või vahemiku esimesest veerust ja eraldab sobiva väärtuse paremal asuvast veerust.
Täpne sobivus
Exceli VLOOKUP funktsioonil on kaks sobitamise meetodit, need on: täpne ja ligikaudne. Funktsiooni VLOOKUP parameeter „range_lookup” määrab, millist tüüpi te otsite, kas täpset või ligikaudset.
Kui sisestate vahemiku_otsing väärtuseks "FALSE" või "0", otsib valem väärtust, mis on täpselt võrdne otsinguväärtusega (see võib olla number, tekst või kuupäev).
=VLOOKUP(A9;A2:D5;3;FALSE)
Kui täpset vastet tabelist ei leita, tagastab see veateate #N/A. Kui proovisime otsida jaotist „Jaapan” ja tagastada sellele vastav väärtus veerus 4, ilmneb tõrge #N/A, kuna tabeli esimeses veerus puudub „Jaapan”.
Viimases argumendis võite sisestada kas numbri 0 või FALSE. Mõlemad tähendavad Excelis sama asja.
Ligikaudne sobivus
Mõnikord pole vaja täpset vastet, piisab parimast vastest. Sellistel juhtudel saate kasutada ligikaudse sobitamise režiimi. Ligikaudse vaste leidmiseks määrake funktsiooni viimaseks argumendiks 'TRUE'. Vaikeväärtus on TRUE, mis tähendab, et kui te viimast argumenti ei lisa, kasutab funktsioon vaikimisi ligikaudset sobitamist.
=VLOOKUP(B10,A2:B7,2,TÕENE)Selles näites ei vaja me sobiva hinde leidmiseks täpset punktisummat. Kõik, mida vajame, on hinded, et need oleksid selles punktivahemikus.
Kui VLOOKUP leiab täpse vaste, tagastab see selle väärtuse. Kui ülaltoodud näites ei leia valem esimesest veerust look_up väärtust 89, tagastab see suuruselt järgmise väärtuse (80).
Esimene matš
Kui tabeli vasakpoolses veerus on duplikaate, otsib VLOOKUP esimese vaste ja tagastab selle.
Näiteks VLOOKUP on konfigureeritud otsima eesnime "Mia" perekonnanime. Kuna eesnimega "Mia" on kaks kirjet, tagastab funktsioon esimese kirje "Bena" perekonnanime.
Metamärgi vaste
Funktsioon VLOOKUP võimaldab teil metamärke kasutades leida määratud väärtusele osalise vaste. Kui soovite leida väärtuse, mis sisaldab otsinguväärtust mis tahes positsioonis, lisage ampersand (&), et ühendada meie otsinguväärtus metamärgiga (*). Kasutage märke „$”, et teha absoluutseid lahtriviiteid ja lisada otsinguväärtuse ette või järele metamärki „*”.
Näites on meil lahtris B13 ainult osa otsinguväärtusest (Vin). Seega, selleks, et sooritada osaline vaste antud tähemärkidele, ühendage pärast lahtri viidet metamärk "*".
=VLOOKUP($B$13&"*",$A$2:$E$10,3,FALSE)
Mitu otsingut
Funktsioon VLOOKUP võimaldab teil luua dünaamilise kahesuunalise otsingu, mis sobib nii ridadele kui ka veergudele. Järgmises näites on VLOOKUP seadistatud otsima eesnime (Mayra) ja linna põhjal. B14 süntaks on:
=VLOOKUP(B13,A2:E10,VASTA(A14,A1:E1,0),0)
Kuidas otsida Excelis teiselt lehelt VLOOKUP
Tavaliselt kasutatakse funktsiooni VLOOKUP eraldi töölehelt vastavate väärtuste tagastamiseks ja seda kasutatakse harva sama töölehe andmetega.
Teiselt Exceli lehelt, kuid samas töövihikus asuva Vlookupi jaoks sisestage lehe nimi enne table_array koos hüüumärgiga (!).
Näiteks töölehe „Tooted” lahtri A2 väärtuse otsimiseks töölehe „ItemPrices” vahemikust A2:B8 ja vastava väärtuse tagastamiseks veerust B:
=VLOOKUP(A2,kaupade hinnad!$A$2:$C$8,2,FALSE)Alloleval pildil on tabel 'ItemPrices' töölehel.
Kui sisestame VLOOKUP-i valemi töölehe „Tooted” veergu C, tõmbab see töölehelt „ItemPrices” sobivad andmed.
Kuidas teha VLOOKUP Excelis teisest töövihikust
Väärtust saate otsida ka täiesti erinevast töövihikust. Kui soovite teha VLOOKUP-i teisest töövihikust, peate lisama töövihiku nime nurksulgudesse, millele järgneb lehe nimi enne sõna table_array koos hüüumärgiga (!) (nagu allpool näidatud).
Näiteks kasutage seda valemit, et otsida töövihiku "Item.xlsx" töölehelt nimega "ItemPrices" erineva töölehe lahtri A2 väärtust:
=VLOOKUP(A2,[Item.xls]Kaubahinnad!$A$2:$B$8,2,FALSE)Kõigepealt avage mõlemad töövihikud, seejärel alustage valemi sisestamist töölehe (toote töölehe) lahtrisse C2 ja kui jõuate argumendini table_array, minge põhiandmete töövihikusse (Item.xlsx) ja valige tabelivahemik. Nii ei pea te töövihiku ja töölehe nime käsitsi tippima. Sisestage ülejäänud argumendid ja vajutage funktsiooni lõpetamiseks sisestusklahvi.
Isegi kui sulgete otsingutabelit sisaldava töövihiku, jätkab VLOOKUP valem tööd, kuid nüüd näete suletud töövihiku täielikku teed, nagu on näidatud järgmisel ekraanipildil.
Kasutage Exceli lindilt funktsiooni VLOOKUP
Kui te ei mäleta valemeid, pääsete alati Exceli lindilt juurde funktsioonile VLOOKUP. VLOOKUP-i avamiseks minge Exceli lindi vahekaardile „Valemid” ja klõpsake ikooni „Otsing ja viide”. Seejärel valige rippmenüü allservas suvand VLOOKUP.
Seejärel sisestage argumendid dialoogiboksi Funktsiooni argumendid. Seejärel klõpsake nuppu "OK".
Näites otsisime tabelist eesnime 'Sherill', et tagastada selle vastav olek veerus D.
Loodame, et õppisite sellest artiklist, kuidas kasutada Excelis funktsiooni VLOOKUP. Kui soovite Exceli kasutamise kohta lisateavet, vaadake meie teisi Exceliga seotud artikleid.