Funkcija VLOOKUP v Excelu išče vrednost v obsegu celic, nato vrne vrednost, ki je v isti vrstici kot vrednost, ki jo iščete.
VLOOKUP, ki pomeni 'Vertikalno iskanje', je iskalna funkcija, ki išče vrednost v skrajnem levem stolpcu (prvem stolpcu) obsega in vrne vzporedno vrednost iz stolpca na desni. Funkcija VLOOKUP išče samo vrednost navzgor (od zgoraj navzdol) v tabeli, razporejeni navpično.
Recimo, da imamo na delovnem listu inventarni seznam s tabelo, ki prikazuje imena artiklov, datum nakupa, količino in ceno. Nato lahko uporabimo VLOOKUP na drugem delovnem listu, da iz delovnega lista zalog izvlečemo količino in ceno za določeno ime artikla.
Funkcija VLOOKUP se morda sprva zdi zastrašujoča, vendar je pravzaprav zelo enostavna za uporabo, ko razumete, kako deluje. Tukaj, v tej vadnici, vam bomo pokazali, kako uporabljati funkcijo VLOOKUP v Excelu.
Sintaksa in argumenti VLOOKUP
Če boste uporabljali funkcijo VLOOKUP, morate poznati njeno sintakso in njene argumente.
Sintaksa funkcije VLOOKUP:
=VLOOKUP(iskana_vrednost,matrika_tabel,številka_indeks_stolpca,[iskanje_obsega])Ta funkcija je sestavljena iz 4 parametrov ali argumentov:
- iskalna_vrednost: To določa vrednost, ki jo iščete v prvem stolpcu danega niza tabel. Vrednost iskanja mora biti vedno na skrajnem levem (stolpec iskalne tabele.
- table_array: To je tabela (obseg celic), v kateri želite poiskati vrednost. Ta tabela (tabela za iskanje) je lahko na istem delovnem listu ali drugem delovnem listu ali celo v drugem delovnem zvezku.
- indeks_stolpca: To določa številko stolpca matrike tabele, ki ima vrednost, ki jo želite ekstrahirati.
- [range_lookup]: Ta parameter določa, ali želite izluščiti natančno ali približno ujemanje. Je TRUE ali FALSE, vnesite 'FALSE', če želite natančno vrednost, ali vnesite 'TRUE', če ste v redu s približno vrednostjo.
Uporaba funkcije VLOOKUP v Excelu
Raziščimo, kako uporabljati VLOOKUP v Microsoft Excelu.
Osnovni primer
Če želite uporabiti VLOOKUP, morate najprej ustvariti svojo bazo podatkov ali tabelo (glejte spodaj).
Nato ustvarite tabelo ali obseg, kjer želite poiskati, in izvlecite vrednosti iz iskalne tabele.
Nato izberite celico, v kateri želite ekstrahirano vrednost, in vnesite naslednjo formulo VLOOKUP. Na primer, želimo poiskati telefonsko številko 'Ena', nato moramo vnesti iskalno vrednost kot B13, A2:E10 kot niz tabele, 5 za številko stolpca telefonske številke in FALSE, da vrnemo natančno vrednost. Nato pritisnite "Enter", da dokončate formulo.
=VLOOKUP(B13,A2:E10,5,FALSE)Obsega tabele vam ni treba vnašati ročno, lahko samo izberete obseg ali tabelo z miško za argument table_array. In samodejno bo dodan argumentu.
Ne pozabite, da bi to delovalo, mora biti vrednost iskanja na skrajni levi strani naše iskalne tabele (A2:E10). Prav tako ni nujno, da je Lookup_value v stolpcu A delovnega lista, ampak mora biti le skrajni levi stolpec obsega, ki ga želite iskati.
Vlookup izgleda prav
Funkcija VLOOKUP lahko pogleda le desno od tabele. Išče vrednost v prvem stolpcu tabele ali obsega in izvleče ujemajočo se vrednost iz stolpca na desni.
Natančno ujemanje
Excelova funkcija VLOOKUP ima dva načina ujemanja, to sta: natančno in približno. Parameter 'range_lookup' v funkciji VLOOKUP določa, kakšno vrsto iščete, natančno ali približno.
Če vnesete range_lookup kot »FALSE« ali »0«, formula poišče vrednost, ki je natančno enaka lookup_value (lahko je številka, besedilo ali datum).
=VISK (A9,A2:D5,3,FALSE)
Če v tabeli ni mogoče najti natančnega ujemanja, bo vrnila napako #N/A. Ko smo poskušali poiskati »Japonska« in vrniti ustrezno vrednost v stolpcu 4, se pojavi napaka #N/A, ker v prvem stolpcu tabele ni »Japonska«.
V zadnji argument lahko vnesete številko '0' ali 'FALSE'. Oba pomenita isto stvar v Excelu.
Približno ujemanje
Včasih ni nujno, da se natančno ujema, dovolj je najboljše ujemanje. V takih primerih lahko uporabite način približnega ujemanja. Končni argument funkcije nastavite na 'TRUE', da poiščete približno ujemanje. Privzeta vrednost je TRUE, kar pomeni, da če ne dodate zadnjega argumenta, bo funkcija privzeto uporabila približno ujemanje.
=VISKANJE (B10,A2:B7,2,TRUE)V tem primeru ne potrebujemo natančne ocene, da bi našli ustrezno oceno. Vse, kar potrebujemo, so ocene, da bi bili v tem razponu točk.
Če VLOOKUP najde natančno ujemanje, bo vrnil to vrednost. V zgornjem primeru, če formula ne najde vrednosti look_up 89 v prvem stolpcu, bo vrnila naslednjo največjo vrednost (80).
Prva tekma
Če skrajni levi stolpec tabele vsebuje dvojnike, bo VLOOKUP našel in vrnil prvo ujemanje.
VLOOKUP je na primer konfiguriran tako, da poišče priimek za ime 'Mia'. Ker obstajata 2 vnosa z imenom 'Mia', funkcija vrne priimek za prvi vnos, 'Bena'.
Ujemanje z nadomestnimi znaki
Funkcija VLOOKUP vam omogoča, da poiščete delno ujemanje za določeno vrednost z uporabo nadomestnih znakov. Če želite poiskati vrednost, ki vsebuje iskalno vrednost na katerem koli mestu, dodajte znak ampersand (&), da se naši iskalni vrednosti pridružite z nadomestnim znakom (*). Uporabite znake '$', da naredite absolutne reference celice in dodajte nadomestni znak '*' pred ali za iskalno vrednost.
V primeru imamo samo del iskalne vrednosti (Vin) v celici B13. Torej, da izvedete delno ujemanje za dane znake, povežite nadomestni znak '*' za sklicevanjem na celico.
=VLOOKUP($B$13&"*",$A$2:$E$10,3,FALSE)
Več iskanj
Funkcija VLOOKUP vam omogoča, da ustvarite dinamično dvosmerno iskanje, ki se ujema tako v vrsticah kot stolpcih. V naslednjem primeru je VLOOKUP nastavljen za izvajanje iskanja na podlagi imena (Mayra) in mesta. Sintaksa v B14 je:
=VISKOVANJE(B13,A2:E10,MACH(A14,A1:E1,0),0)
Kako narediti VLOOKUP z drugega lista v Excelu
Običajno se funkcija VLOOKUP uporablja za vrnitev ujemajočih se vrednosti z ločenega delovnega lista in se redko uporablja s podatki na istem delovnem listu.
Če želite iskati z drugega lista Excel, vendar v istem delovnem zvezku, vnesite ime lista pred tabelo_array s klicajem (!).
Če želite na primer poiskati vrednost celice A2 delovnega lista »Izdelki« v obsegu A2:B8 na delovnem listu »ItemPrices« in vrniti ustrezno vrednost iz stolpca B:
=VLOOKUP(A2,Cene artiklov!$A$2:$C$8,2,FALSE)Spodnja slika prikazuje tabelo v delovnem listu 'ItemPrices'.
Ko vnesemo formulo VLOOKUP v stolpec C delovnega lista »Izdelki«, potegne ustrezne podatke iz delovnega lista »ItemPrices«.
Kako narediti VLOOKUP iz drugega delovnega zvezka v Excelu
Vrednost lahko poiščete tudi v povsem drugem delovnem zvezku. Če želite VLOOKUP iz drugega delovnega zvezka, morate ime delovnega zvezka priložiti v oglate oklepaje, ki mu sledi ime lista pred tabele_array s klicajem (!) (kot je prikazano spodaj).
Na primer, uporabite to formulo, da poiščete vrednost celice A2 drugega delovnega lista iz delovnega lista z imenom »ItemPrices« v delovnem zvezku »Item.xlsx«:
=VLOOKUP(A2,[Item.xls]Cene artiklov!$A$2:$B$8,2,FALSE)Najprej odprite oba delovna zvezka, nato začnite vnašati formulo v celico C2 delovnega lista (Product worksheet), in ko pridete do argumenta table_array, pojdite v delovni zvezek z glavnimi podatki (Item.xlsx) in izberite obseg tabele. Tako vam ni treba ročno vnašati imena delovnega zvezka in delovnega lista. Vnesite preostale argumente in pritisnite tipko "Enter", da dokončate funkcijo.
Tudi če zaprete delovni zvezek, ki vsebuje iskalno tabelo, bo formula VLOOKUP še naprej delovala, vendar lahko zdaj vidite celotno pot zaprtega delovnega zvezka, kot je prikazano na naslednjem posnetku zaslona.
Uporabite funkcijo VLOOKUP iz Excelovega traku
Če se ne spomnite formul, lahko vedno dostopate do funkcije VLOOKUP iz Excelovega traku. Za dostop do VLOOKUP pojdite na zavihek »Formule« v Excelovem traku in kliknite ikono »Iskanje in referenca«. Nato izberite možnost »VLOOKUP« na dnu spustnega menija.
Nato vnesite argumente v pogovorno okno »Argumenti funkcije«. Nato kliknite gumb »V redu«.
V primeru smo poiskali ime 'Sherill' v tabeli, da bi vrnili ustrezno stanje v stolpcu D.
Upamo, da ste se iz tega članka naučili uporabljati funkcijo VLOOKUP v Excelu. Če želite izvedeti več o uporabi Excela, si oglejte naše druge članke, povezane z Excelom.