Sisu
- VLOOKUP funktsioon Excelis – üldine kirjeldus ja süntaks
- VLOOKUP Näited
- Täpne või ligikaudne vaste funktsioonis VLOOKUP
- VLOOKUP Excelis – peate seda meeles pidama!
Täna alustame artiklite sarja, mis kirjeldab ühte Exceli kõige kasulikumat funktsiooni VPR (VLOOKUP). See funktsioon on samal ajal üks keerukamaid ja kõige vähem mõistetavaid.
Selles õpetuses teemal VPR Püüan põhitõed võimalikult lihtsalt paika panna, et kogenematutele kasutajatele oleks õppeprotsess võimalikult selge. Lisaks uurime mitmeid näiteid Exceli valemitega, mis näitavad funktsiooni levinumaid kasutusjuhtumeid VPR.
VLOOKUP funktsioon Excelis – üldine kirjeldus ja süntaks
Mis see siis on VPR? Esiteks on see Exceli funktsioon. Mida ta teeb? See otsib teie määratud väärtuse ja tagastab vastava väärtuse teisest veerust. Tehniliselt öeldes VPR otsib väärtuse antud vahemiku esimesest veerust ja tagastab tulemuse sama rea teisest veerust.
Kõige tavalisemas rakenduses on funktsioon VPR otsib andmebaasist etteantud unikaalset identifikaatorit ja võtab andmebaasist välja sellega seotud teabe.
Funktsiooni nime esimene täht VPR (VLOOKUP) tähendab Вvertikaalne (Vvertikaalne). Selle järgi saate eristada VPR Alates GPR (HLOOKUP), mis otsib väärtust vahemiku ülemisest reast − Гhorisontaalne (Hhorisontaalselt).
funktsioon VPR saadaval programmides Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP ja Excel 2000.
Funktsiooni VLOOKUP süntaks
funktsioon VPR (VLOOKUP) on järgmise süntaksiga:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Nagu näete, funktsioon VPR Microsoft Excelis on 4 valikut (või argumenti). Esimesed kolm on kohustuslikud, viimane on vabatahtlik.
- lookup_value (lookup_value) – otsitav väärtus. See võib olla väärtus (arv, kuupäev, tekst) või lahtriviide (sisaldab otsinguväärtust) või mõne muu Exceli funktsiooni tagastatud väärtus. Näiteks otsib see valem väärtust 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Kui otsinguväärtus on väiksem kui otsitava vahemiku esimese veeru väikseim väärtus, siis funktsioon VPR teatab veast # AT (#N/A).
- table_array (tabel) – kaks või enam veergu andmeid. Pea meeles, funktsioon VPR otsib alati väärtust argumendis antud vahemiku esimesest veerust table_array (tabel). Vaadatav vahemik võib sisaldada mitmesuguseid andmeid, näiteks teksti, kuupäevi, numbreid, tõeväärtusi. Funktsioon on tõstutundlik, mis tähendab, et suur- ja väiketähti peetakse samadeks. Nii et meie valem otsib väärtust 40 rakkudes alates A2 et A15, sest A on argumendis antud vahemiku A2:B15 esimene veerg table_array (tabel):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (veeru_number) on antud vahemikus oleva veeru number, kust leitud rea väärtus tagastatakse. Antud vahemiku vasakpoolseim veerg on 1, teine veerg on 2, kolmas veerg on 3 ja nii edasi. Nüüd saate lugeda kogu valemit:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Väärtust otsiv valem 40 vahemikus A2: A15 ja tagastab vastava väärtuse veerust B (kuna B on teine veerg vahemikus A2:B15).
Kui argumendi väärtus col_index_num (veeru_number) väiksem kui 1SIIS VPR teatab veast #VALUE! (#VALUE!). Ja kui see on suurem kui veergude arv vahemikus table_array (tabel), tagastab funktsioon veateate #REF! (#LINK!).
- vahemiku_otsing (range_lookup) – määrab, mida otsida:
- täpne vaste, argument peab olema võrdne FALSE (VALE);
- ligikaudne vaste, argument võrdub TÕELINE KOOD (TRUE) või pole üldse täpsustatud.
See parameeter on valikuline, kuid väga oluline. Hiljem selles õpetuses VPR Näitan teile mõnda näidet, mis selgitavad, kuidas kirjutada valemeid täpsete ja ligikaudsete vastete leidmiseks.
VLOOKUP Näited
Loodan funktsiooni VPR saada teile natuke selgemaks. Vaatame nüüd mõningaid kasutusjuhtumeid VPR reaalsete andmetega valemites.
Kuidas kasutada VLOOKUP-i teiselt Exceli lehelt otsimiseks
Praktikas funktsiooniga valemid VPR kasutatakse harva andmete otsimiseks samalt töölehel. Enamasti otsite üles ja hankite vastavad väärtused teiselt lehelt.
Selleks, et kasutada VPR, otsige teiselt Microsoft Exceli lehelt, peate argumendis table_array (tabel) määrake lehe nimi hüüumärgiga, millele järgneb lahtrivahemik. Näiteks näitab järgmine valem, et vahemik A2: B15 on lehel nimega Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Loomulikult ei pea lehe nime käsitsi sisestama. Alustage lihtsalt valemi tippimist ja kui tegemist on argumendiga table_array (tabel), lülituge soovitud lehele ja valige hiirega soovitud lahtrivahemik.
Alloleval ekraanipildil olev valem otsib töölehel veerust A teksti „Toode 1” (see on vahemiku A1:B2 esimene veerg) Hinnad.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Pidage meeles, et tekstiväärtuse otsimisel peate selle lisama jutumärkidesse (“”), nagu tavaliselt tehakse Exceli valemites.
Vaidluse jaoks table_array (tabel) on soovitav alati kasutada absoluutviiteid ($-märgiga). Sel juhul jääb otsinguvahemik valemi teistesse lahtritesse kopeerimisel muutumatuks.
Otsige teisest töövihikust rakendusega VLOOKUP
Funktsioneerima VPR töötas kahe Exceli töövihiku vahel, peate lehe nime ette määrama töövihiku nime nurksulgudes.
Näiteks allpool on valem, mis otsib väärtust 40 lehel Sheet2 raamatus Numbers.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Siin on lihtsaim viis Excelis valemi loomiseks VPRmis lingib teisele töövihikule:
- Avage mõlemad raamatud. Seda ei nõuta, kuid nii on valemit lihtsam luua. Te ei taha ju töövihiku nime käsitsi sisestada? Lisaks kaitseb see teid juhuslike kirjavigade eest.
- Alustage funktsiooni tippimist VPRja kui rääkida vaidlusest table_array (tabel), lülituge teisele töövihikule ja valige selles vajalik otsinguvahemik.
Alloleval ekraanipildil on näha valem, mille otsinguks on määratud töövihiku vahemik Hinnakiri.xlsx lehel Hinnad.
funktsioon VPR töötab isegi siis, kui sulgete otsitava töövihiku ja töövihiku faili täielik tee kuvatakse valemiribal, nagu allpool näidatud:
Kui töövihiku või lehe nimi sisaldab tühikuid, tuleb see märkida apostroofidega:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Kuidas kasutada nimega vahemikku või tabelit valemites funktsiooniga VLOOKUP
Kui kavatsete kasutada sama otsinguvahemikku mitmes funktsioonis VPR, saate luua nimega vahemiku ja sisestada selle nime valemisse argumendina table_array (tabel).
Nimega vahemiku loomiseks valige lihtsalt lahtrid ja sisestage väljale sobiv nimi Eesnimi, valemiribast vasakul.
Nüüd saad toote hinna leidmiseks üles kirjutada järgmise valemi Toode 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Enamik vahemiku nimesid töötab kogu Exceli töövihiku jaoks, seega pole argumendi lehe nime vaja määrata table_array (tabel), isegi kui valem ja otsinguvahemik on erinevatel töölehtedel. Kui need on erinevates töövihikutes, peate enne vahemiku nime määrama töövihiku nime, näiteks järgmiselt:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Nii et valem tundub palju selgem, nõustute? Nimega vahemike kasutamine on ka hea alternatiiv absoluutsetele viidetele, kuna nimega vahemik ei muutu, kui kopeerite valemi teistesse lahtritesse. See tähendab, et võite olla kindel, et otsinguvahemik valemis jääb alati õigeks.
Kui teisendate lahtrivahemiku täieõiguslikuks Exceli arvutustabeliks, kasutades käsku Tabel (Tabel) vahekaart sisestamine (Sisesta), siis kui valite hiirega vahemiku, lisab Microsoft Excel automaatselt veergude nimed (või tabeli nime, kui valite terve tabeli) valemile.
Valmis valem näeb välja umbes selline:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Või äkki isegi nii:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Nimega vahemike kasutamisel osutavad lingid samadele lahtritele olenemata sellest, kuhu funktsiooni kopeerite VPR töövihiku sees.
Metamärkide kasutamine VLOOKUP-i valemites
Nagu paljude muude funktsioonide puhul, VPR Võite kasutada järgmisi metamärke:
- Küsimärk (?) – asendab iga üksiku tähemärgi.
- Tärn (*) – asendab mis tahes märgijada.
Metamärkide kasutamine funktsioonides VPR võib olla kasulik paljudel juhtudel, näiteks:
- Kui te ei mäleta täpselt teksti, mida peate leidma.
- Kui soovite leida sõna, mis on osa lahtri sisust. Tean, et VPR otsib lahtri kui terviku sisu järgi, nagu valik oleks lubatud Sobitage kogu lahtri sisu (Terve lahter) tavalises Exceli otsingus.
- Kui lahter sisaldab lisatühikuid sisu alguses või lõpus. Sellises olukorras võite pikka aega oma ajusid ragistada, püüdes aru saada, miks valem ei tööta.
Näide 1: Otsige teksti, mis algab või lõpeb teatud tähemärkidega
Oletame, et soovite allpool näidatud andmebaasist otsida konkreetset klienti. Te ei mäleta tema perekonnanime, kuid teate, et see algab tähega "ack". Siin on valem, mis saab selle tööga suurepäraselt hakkama:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Nüüd, kui olete kindel, et leidsite õige nime, saate sama valemi abil leida selle kliendi makstud summa. Selleks muutke lihtsalt funktsiooni kolmandat argumenti VPR soovitud veeru numbrile. Meie puhul on see veerg C (vahemikus 3.):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Siin on veel mõned metamärkide näited:
~ Leidke nimi, mis lõpeb tähega "mees":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Leidke nimi, mis algab sõnaga "ad" ja lõpeb sõnaga "son":
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Leiame eesnime loendist, mis koosneb 5 tähemärgist:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Funktsioneerima VPR metamärkidega töötas õigesti, neljanda argumendina peaksite alati kasutama FALSE (VALE). Kui otsinguvahemik sisaldab rohkem kui ühte väärtust, mis vastavad metamärkidega otsinguterminitele, tagastatakse esimene leitud väärtus.
Näide 2: Kombineeri VLOOKUP valemites metamärgid ja lahtriviited
Vaatame nüüd veidi keerukamat näidet selle kohta, kuidas funktsiooni abil otsida VPR väärtuse järgi lahtris. Kujutage ette, et veerg A on litsentsivõtmete loend ja veerg B on litsentsi omavate nimede loend. Lisaks on sul lahtris C1 mingi osa (mitu tähemärki) mingist litsentsivõtmest ja sa tahad leida omaniku nime.
Seda saab teha järgmise valemi abil:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
See valem otsib väärtuse lahtrist C1 antud vahemikus ja tagastab vastava väärtuse veerust B. Pange tähele, et esimeses argumendis kasutame tekstistringi linkimiseks enne ja pärast lahtri viidet ampersand (&).
Nagu näete alloleval joonisel, funktsioon VPR tagastab "Jeremy Hill", kuna tema litsentsivõti sisaldab lahtri C1 tähemärkide jada.
Pange tähele, et argument table_array (tabel) sisaldab ülaloleval ekraanipildil lahtrite vahemiku määramise asemel tabeli nime (tabel 7). Seda tegime eelmises näites.
Täpne või ligikaudne vaste funktsioonis VLOOKUP
Ja lõpuks, vaatame lähemalt viimast funktsiooni jaoks määratud argumenti VPR - vahemiku_otsing (intervall_vaade). Nagu õppetunni alguses mainitud, on see argument väga oluline. Selle väärtusega saate samas valemis saada täiesti erinevaid tulemusi TÕELINE KOOD (TRUE) või FALSE (VALE).
Kõigepealt uurime, mida Microsoft Excel täpsete ja ligikaudsete vastete all tähendab.
- Kui argument vahemiku_otsing (vahemiku_otsing) on võrdne FALSE (FALSE), otsib valem täpset vastet, st täpselt sama väärtust, mis argumendis on antud lookup_value (otsingu_väärtus). Kui vahemiku t esimeses veerusvõimeline_massiiv (tabel) leiab kaks või enam väärtust, mis vastavad argumendile lookup_value (otsingu_väärtus), siis valitakse esimene. Kui vasteid ei leita, teatab funktsioon veast # AT (#N/A). Näiteks järgmine valem teatab veast # AT (#N/A), kui vahemikus A2:A15 pole väärtust 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Kui argument vahemiku_otsing (vahemiku_otsing) on võrdne TÕELINE KOOD (TRUE), otsib valem ligikaudset vastet. Täpsemalt, kõigepealt funktsioon VPR otsib täpset vastet ja kui seda ei leita, valib ligikaudse vaste. Ligikaudne vaste on suurim väärtus, mis ei ületa argumendis määratud väärtust. lookup_value (otsingu_väärtus).
Kui argument vahemiku_otsing (vahemiku_otsing) on võrdne TÕELINE KOOD (TRUE) või määramata, tuleks vahemiku esimeses veerus olevad väärtused sortida kasvavas järjekorras, st väikseimast suurimani. Muidu funktsioon VPR võib anda vale tulemuse.
Et mõista paremini valiku tähtsust TÕELINE KOOD (TÕDE) või FALSE (FALSE), vaatame veel mõnda funktsiooniga valemit VPR ja vaata tulemusi.
Näide 1: VLOOKUPiga täpse vaste leidmine
Nagu mäletate, on täpse vaste otsimiseks funktsiooni neljas argument VPR peaks olema oluline FALSE (VALE).
Lähme tagasi tabeli juurde kohe esimesest näitest ja uurime, milline loom suudab kiirusega liikuda 50 miili tunnis. Usun, et see valem ei tekita teile raskusi:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Pange tähele, et meie otsinguvahemik (veerg A) sisaldab kahte väärtust 50 – rakkudes A5 и A6. Valem tagastab lahtri väärtuse B5. Miks? Sest täpset vastet otsides funktsioon VPR kasutab esimest leitud väärtust, mis ühtib otsitavaga.
Näide 2: VLOOKUP-i kasutamine ligikaudse vaste leidmiseks
Kui kasutate funktsiooni VPR ligikaudse vaste otsimiseks, st kui argument vahemiku_otsing (vahemiku_otsing) on võrdne TÕELINE KOOD (TRUE) või välja jäetud, peate esimese asjana sorteerima vahemiku esimese veeru järgi kasvavas järjekorras.
See on funktsiooni tõttu väga oluline VPR tagastab antud väärtuse järel suuruselt järgmise väärtuse ja seejärel otsing peatub. Kui jätate õige sorteerimise tähelepanuta, saate väga kummaliste tulemuste või veateate. # AT (#N/A).
Nüüd saate kasutada ühte järgmistest valemitest:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Nagu näete, tahan välja selgitada, kummale loomale on kõige lähem kiirus 69 miili tunnis. Ja siin on tulemus, mille funktsioon mulle tagastas VPR:
Nagu näete, andis valem tulemuse Antiloop (Antelope), kelle kiirus 61 miili tunnis, kuigi loend sisaldab ka Gepard (gepard), kes jookseb kiirusega 70 miili tunnis ja 70 on lähemal 69-le kui 61, kas pole? Miks see juhtub? Kuna funktsioon VPR ligikaudse vaste otsimisel tagastab suurima väärtuse, mis ei ole suurem kui otsitav väärtus.
Loodan, et need näited valgustavad funktsiooniga töötamist VPR Excelis ja te ei vaata teda enam kui autsaiderit. Nüüd ei tee paha korrata põgusalt uuritud materjali põhipunkte, et see paremini mällu kinnistada.
VLOOKUP Excelis – peate seda meeles pidama!
- funktsioon VPR Excel ei saa vaadata vasakule. See otsib alati väärtust argumendi antud vahemiku vasakpoolsemast veerust table_array (tabel).
- Funktsioonis VPR kõik väärtused ei ole tõstutundlikud, st väikesed ja suured tähed on samaväärsed.
- Kui otsitav väärtus on väiksem kui otsitava vahemiku esimeses veerus olev minimaalne väärtus, funktsioon VPR teatab veast # AT (#N/A).
- Kui 3. argument col_index_num (veeru_number) väiksem kui 1funktsioon VPR teatab veast #VALUE! (#VALUE!). Kui see on suurem kui veergude arv vahemikus table_array (tabel), teatab funktsioon veast #REF! (#LINK!).
- Kasutage argumendis absoluutseid lahtriviiteid table_array (tabel), et valemi kopeerimisel säiliks õige otsinguvahemik. Proovige alternatiivina kasutada Excelis nimega vahemikke või tabeleid.
- Ligikaudse vaste otsingu tegemisel pidage meeles, et otsitava vahemiku esimene veerg tuleb sortida kasvavas järjekorras.
- Lõpuks pidage meeles neljanda argumendi tähtsust. Kasutage väärtusi TÕELINE KOOD (TÕDE) või FALSE (VALE) teadlikult ja saad lahti paljudest peavaludest.
Meie funktsiooniõpetuse järgmistes artiklites VPR Excelis õpime täpsemaid näiteid, nagu näiteks erinevate arvutuste tegemine kasutades VPR, väärtuste eraldamine mitmest veerust ja palju muud. Täname, et lugesite seda õpetust ja loodan, et kohtume järgmisel nädalal jälle!