Funkcija VLOOKUP u Excelu – Vodič za početnike: Sintaksa i primjeri

Sadržaj

Funkcija VLOOKUP u Excelu – opći opis i sintaksa
- Sintaksa funkcije VLOOKUP
VLOOKUP Primjeri
Točno ili približno podudaranje u funkciji VLOOKUP
- Primjer 1: Pronalaženje točnog podudaranja pomoću VLOOKUP-a
- Primjer 2: Korištenje VLOOKUP-a za pronalaženje približnog podudaranja
VLOOKUP u Excelu – ovo morate zapamtiti!

Danas započinjemo seriju članaka koji opisuju jednu od najkorisnijih značajki programa Excel − VPR (VLOOKUP). Ova je funkcija, ujedno, jedna od najsloženijih i najmanje shvaćenih.

U ovom vodiču o VPR Pokušat ću izložiti osnove što je jednostavnije moguće kako bi proces učenja bio što jasniji neiskusnim korisnicima. Osim toga, proučit ćemo nekoliko primjera s Excel formulama koji će pokazati najčešće slučajeve upotrebe funkcije VPR.

Funkcija VLOOKUP u Excelu – opći opis i sintaksa

Pa što je to? VPR? Pa, prije svega, to je Excel funkcija. Što ona radi? Traži vrijednost koju navedete i vraća odgovarajuću vrijednost iz drugog stupca. Tehnički govoreći, VPR traži vrijednost u prvom stupcu zadanog raspona i vraća rezultat iz drugog stupca u istom retku.

U najčešćoj primjeni, funkcija VPR pretražuje bazu podataka u potrazi za danim jedinstvenim identifikatorom i iz baze podataka izvlači neke informacije povezane s njim.

Prvo slovo u nazivu funkcije VPR (VLOOKUP) znači Вokomito (Vvertikalno). Po njemu možete razlikovati VPR iz GPR (HLOOKUP), koji traži vrijednost u gornjem redu raspona − Гvodoravno (Hvodoravno).

funkcija VPR dostupno u Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP i Excel 2000.

Sintaksa funkcije VLOOKUP

funkcija VPR (VLOOKUP) ima sljedeću sintaksu:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Kao što vidite, funkcija VPR u Microsoft Excelu ima 4 opcije (ili argumente). Prva tri su obavezna, posljednja je izborna.

tražena_vrijednost (lookup_value) – Vrijednost koju treba tražiti. To može biti vrijednost (broj, datum, tekst) ili referenca na ćeliju (koja sadrži traženu vrijednost) ili vrijednost koju vraća neka druga funkcija programa Excel. Na primjer, ova formula će tražiti vrijednost 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Ako je tražena vrijednost manja od najmanje vrijednosti u prvom stupcu raspona koji se traži, funkcija VPR će prijaviti grešku #AT (#N/A).

table_array (tablica) – dva ili više stupaca podataka. Upamtite, funkcija VPR uvijek traži vrijednost u prvom stupcu raspona navedenog u argumentu table_array (stol). Vidljivi raspon može sadržavati različite podatke, poput teksta, datuma, brojeva, Booleovih vrijednosti. Funkcija ne razlikuje velika i mala slova, što znači da se velika i mala slova smatraju istima. Dakle, naša formula će tražiti vrijednost 40 u stanicama iz A2 do A15, jer je A prvi stupac raspona A2:B15 danog u argumentu table_array (stol):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (broj_stupca) je broj stupca u zadanom rasponu iz kojeg će biti vraćena vrijednost u pronađenom retku. Krajnji lijevi stupac u zadanom rasponu je 1, drugi stupac je 2, treći stupac je 3 i tako dalje. Sada možete pročitati cijelu formulu:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formula koja traži vrijednost 40 u rasponu A2: A15 i vraća odgovarajuću vrijednost iz stupca B (jer je B drugi stupac u rasponu A2:B15).

Ako vrijednost argumenta col_index_num (broj_stupca) manje od 1tada VPR će prijaviti grešku #VRIJEDNOST! (#VRIJEDNOST!). A ako je veći od broja stupaca u rasponu table_array (tablica), funkcija će vratiti pogrešku #REF! (#VEZA!).

raspon_potraga (range_lookup) – određuje što tražiti:
- točno podudaranje, argument mora biti jednak FALSE (NETOČNO);
- približno podudaranje, argument je jednak PRAVI KOD (TRUE) ili uopće nije navedeno.
Ovaj parametar nije obavezan, ali je vrlo važan. Kasnije u ovom vodiču VPR Pokazat ću vam neke primjere koji objašnjavaju kako napisati formule za pronalaženje točnih i približnih podudaranja.

VLOOKUP Primjeri

Nadam se funkciji VPR postane ti malo jasnije. Sada pogledajmo neke slučajeve upotrebe VPR u formulama sa stvarnim podacima.

Kako koristiti VLOOKUP za pretraživanje u drugom Excel listu

U praksi formule s funkcijom VPR rijetko se koriste za traženje podataka na istom radnom listu. Češće nego ne, tražit ćete i dohvaćati odgovarajuće vrijednosti s drugog lista.

Da biste mogli koristiti VPR, potražite u drugom Microsoft Excel listu, Morate u argumentu table_array (tablica) navedite naziv lista s uskličnikom iza kojeg slijedi niz ćelija. Na primjer, sljedeća formula pokazuje da raspon A2: B15 nalazi se na listu pod nazivom Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Naravno, naziv lista ne mora se unositi ručno. Samo počnite upisivati formulu, a kada je u pitanju argument table_array (tablica), prijeđite na željeni list i mišem odaberite željeni raspon ćelija.

Formula prikazana na snimci zaslona u nastavku traži tekst "Proizvod 1" u stupcu A (to je prvi stupac raspona A1:B2) na radnom listu Cijene.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Imajte na umu da kada tražite tekstualnu vrijednost, morate je staviti u navodnike (“”), kao što se obično radi u Excel formulama.

Za argument table_array (tablica) poželjno je uvijek koristiti apsolutne reference (sa znakom $). U tom će slučaju raspon pretraživanja ostati nepromijenjen prilikom kopiranja formule u druge ćelije.

Pretražujte u drugoj radnoj knjizi s VLOOKUP-om

Funkcionirati VPR radili između dvije Excel radne knjige, trebate navesti naziv radne knjige u uglatim zagradama prije naziva lista.

Na primjer, ispod je formula koja traži vrijednost 40 na plahti Sheet2 u knjizi Brojevi.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Ovdje je najlakši način za stvaranje formule u Excelu VPRkoji povezuje drugu radnu knjigu:

Otvori obje knjige. Ovo nije potrebno, ali je lakše izraditi formulu na ovaj način. Ne želite ručno unijeti naziv radne knjige, zar ne? Osim toga, zaštitit će vas od slučajnih tipfelera.
Počnite upisivati funkciju VPRa kad je riječ o argumentu table_array (tablica), prijeđite na drugu radnu knjigu i u njoj odaberite traženi raspon pretraživanja.

Snimka zaslona u nastavku prikazuje formulu s pretraživanjem postavljenim na raspon u radnoj knjizi Cjenik.xlsx na plahti Cijene.

funkcija VPR radit će čak i kada zatvorite traženu radnu knjigu i puni put do datoteke radne knjige pojavi se na traci formule, kao što je prikazano u nastavku:

Ako naziv radne knjige ili lista sadrži razmake, mora biti omeđen apostrofom:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Kako koristiti imenovani raspon ili tablicu u formulama s VLOOKUP-om

Ako planirate koristiti isti raspon pretraživanja u više funkcija VPR, možete stvoriti imenovani raspon i unijeti njegovo ime u formulu kao argument table_array (stol).

Da biste stvorili imenovani raspon, jednostavno odaberite ćelije i unesite odgovarajući naziv u polje Ime, lijevo od trake formule.

Sada možete zapisati sljedeću formulu za pronalaženje cijene proizvoda Proizvod 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Većina naziva raspona radi za cijelu Excel radnu knjigu, tako da nema potrebe za navođenjem naziva lista za argument table_array (tablica), čak i ako su formula i raspon pretraživanja na različitim radnim listovima. Ako su u različitim radnim knjigama, tada prije naziva raspona morate navesti naziv radne knjige, na primjer, ovako:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Dakle, formula izgleda puno jasnije, slažete se? Također, korištenje imenovanih raspona dobra je alternativa apsolutnim referencama jer se imenovani raspon ne mijenja kada kopirate formulu u druge ćelije. To znači da možete biti sigurni da će raspon pretraživanja u formuli uvijek ostati točan.

Ako raspon ćelija pretvorite u cjelovitu Excel proračunsku tablicu pomoću naredbe Stol (Tablica) kartica Umetanje (Umetni), tada kada odaberete raspon mišem, Microsoft Excel će automatski dodati nazive stupaca (ili naziv tablice ako odaberete cijelu tablicu) u formulu.

Gotova formula izgledat će otprilike ovako:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Ili možda čak ovako:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Kada koristite imenovane raspone, veze će upućivati na iste ćelije bez obzira gdje kopirate funkciju VPR unutar radne knjižice.

Korištenje zamjenskih znakova u VLOOKUP formulama

Kao i kod mnogih drugih funkcija, VPR Možete koristiti sljedeće zamjenske znakove:

Upitnik (?) – zamjenjuje bilo koji pojedinačni znak.
Zvjezdica (*) – zamjenjuje bilo koji niz znakova.

Korištenje zamjenskih znakova u funkcijama VPR može biti korisno u mnogim slučajevima, na primjer:

Kada se ne sjećate točno teksta koji trebate pronaći.
Kada želite pronaći neku riječ koja je dio sadržaja ćelije. Znam da VPR pretražuje po sadržaju ćelije kao cjeline, kao da je opcija uključena Podudaranje cijelog sadržaja ćelije (Cijela ćelija) u standardnoj Excel pretrazi.
Kada ćelija sadrži dodatne razmake na početku ili kraju sadržaja. U takvoj situaciji možete dugo razbijati glavu pokušavajući shvatiti zašto formula ne djeluje.

Primjer 1: Traženje teksta koji počinje ili završava određenim znakovima

Recimo da želite potražiti određenog kupca u bazi podataka prikazanoj u nastavku. Ne sjećate se njegovog prezimena, ali znate da počinje na “ack”. Evo formule koja će savršeno obaviti posao:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Sada kada ste sigurni da ste pronašli točan naziv, možete upotrijebiti istu formulu da biste pronašli iznos koji je ovaj kupac platio. Da biste to učinili, samo promijenite treći argument funkcije VPR na željeni broj stupca. U našem slučaju, ovo je stupac C (3. u rasponu):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Evo još nekoliko primjera sa zamjenskim znakovima:

~ Pronađite ime koje završava na "man":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Pronađite ime koje počinje s "ad" i završava sa "son":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Nalazimo prvo ime na popisu, koje se sastoji od 5 znakova:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Funkcionirati VPR sa zamjenskim znakovima radi ispravno, kao četvrti argument koji uvijek trebate koristiti FALSE (NETOČNO). Ako raspon pretraživanja sadrži više od jedne vrijednosti koja odgovara pojmovima za pretraživanje sa zamjenskim znakovima, vratit će se prva pronađena vrijednost.

Primjer 2: Kombinirajte zamjenske znakove i reference ćelija u VLOOKUP formulama

Sada pogledajmo malo složeniji primjer kako pretraživati pomoću funkcije VPR po vrijednosti u ćeliji. Zamislite da je stupac A popis licencnih ključeva, a stupac B popis imena koja posjeduju licencu. Osim toga, u ćeliji C1 imate dio (nekoliko znakova) nekakvog licencnog ključa i želite pronaći ime vlasnika.

To se može učiniti pomoću sljedeće formule:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Ova formula traži vrijednost iz ćelije C1 u zadanom rasponu i vraća odgovarajuću vrijednost iz stupca B. Imajte na umu da u prvom argumentu koristimo znak ampersand (&) prije i nakon reference ćelije za povezivanje tekstualnog niza.

Kao što možete vidjeti na donjoj slici, funkcija VPR vraća "Jeremy Hill" jer njegov licencni ključ sadrži niz znakova iz ćelije C1.

Imajte na umu da argument table_array (tablica) na gornjoj snimci zaslona sadrži naziv tablice (tablica7) umjesto navođenja raspona ćelija. To je ono što smo učinili u prethodnom primjeru.

Točno ili približno podudaranje u funkciji VLOOKUP

I na kraju, pogledajmo pobliže posljednji argument koji je naveden za funkciju VPR - raspon_potraga (interval_view). Kao što je spomenuto na početku lekcije, ovaj argument je vrlo važan. Možete dobiti potpuno različite rezultate u istoj formuli s njezinom vrijednošću PRAVI KOD (ISTINA) ili FALSE (NETOČNO).

Prvo, saznajmo što Microsoft Excel podrazumijeva pod točnim i približnim podudaranjem.

Ako argument raspon_potraga (range_lookup) jednako je FALSE (FALSE), formula traži točno podudaranje, tj. točno istu vrijednost kao što je navedena u argumentu tražena_vrijednost (vrijednost_traženja). Ako je u prvom stupcu raspona tsposobni_niz (tablica) nailazi na dvije ili više vrijednosti koje odgovaraju argumentu tražena_vrijednost (search_value), tada će biti odabran prvi. Ako nema rezultata, funkcija će prijaviti pogrešku #AT (#N/A). Na primjer, sljedeća formula će prijaviti pogrešku #AT (#N/A) ako nema vrijednosti u rasponu A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Ako argument raspon_potraga (range_lookup) jednako je PRAVI KOD (TRUE), formula traži približno podudaranje. Točnije, prvo funkcija VPR traži točno podudaranje, a ako nije pronađeno, odabire približno. Približno podudaranje je najveća vrijednost koja ne prelazi vrijednost navedenu u argumentu. tražena_vrijednost (vrijednost_traženja).

Ako argument raspon_potraga (range_lookup) jednako je PRAVI KOD (TRUE) ili nije navedeno, tada bi vrijednosti u prvom stupcu raspona trebale biti poredane uzlaznim redoslijedom, odnosno od najmanje prema najvećoj. Inače, funkcija VPR može vratiti pogrešan rezultat.

Kako bismo bolje razumjeli važnost izbora PRAVI KOD (ISTINA) ili FALSE (FALSE), pogledajmo još neke formule s funkcijom VPR i pogledajte rezultate.

Primjer 1: Pronalaženje točnog podudaranja pomoću VLOOKUP-a

Kao što se sjećate, za traženje točnog podudaranja, četvrti argument funkcije VPR trebalo bi biti važno FALSE (NETOČNO).

Vratimo se tablici iz prvog primjera i otkrijmo koja se životinja može kretati brzinom 50 milja na sat. Vjerujem da vam ova formula neće stvarati poteškoće:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Imajte na umu da naš raspon pretraživanja (stupac A) sadrži dvije vrijednosti 50 – u ćelijama A5 и A6. Formula vraća vrijednost iz ćelije B5. Zašto? Jer kada tražite točno podudaranje, funkcija VPR koristi prvu pronađenu vrijednost koja odgovara onoj koja se traži.

Primjer 2: Korištenje VLOOKUP-a za pronalaženje približnog podudaranja

Kada koristite funkciju VPR za traženje približnog podudaranja, tj. kada argument raspon_potraga (range_lookup) jednako je PRAVI KOD (TRUE) ili izostavljeno, prva stvar koju trebate učiniti je sortirati raspon prema prvom stupcu uzlaznim redoslijedom.

Ovo je vrlo važno jer funkcija VPR vraća sljedeću najveću vrijednost nakon dane, a zatim se pretraga zaustavlja. Ako zanemarite ispravno sortiranje, dobit ćete vrlo čudne rezultate ili poruku o pogrešci. #AT (#N/A).

Sada možete koristiti jednu od sljedećih formula:

=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)

Kao što vidite, želim otkriti koja od životinja ima najbližu brzinu 69 milja na sat. I evo rezultata koji mi je funkcija vratila VPR:

Kao što vidite, formula je vratila rezultat Antilopa (Antilopa), čija je brzina 61 milja na sat, iako popis također uključuje Cheetah (Cheetah) koji trči velikom brzinom 70 milja na sat, a 70 je bliže 69 nego 61, zar ne? Zašto se ovo događa? Budući da funkcija VPR kada traži približno podudaranje, vraća najveću vrijednost koja nije veća od one koja se traži.

Nadam se da ovi primjeri bacaju malo svjetla na rad s funkcijom VPR u Excelu i na nju više ne gledate kao na autsajdera. Sada ne boli ukratko ponoviti ključne točke materijala koji smo proučavali kako bismo ga bolje popravili u pamćenju.

VLOOKUP u Excelu – ovo morate zapamtiti!

funkcija VPR Excel ne može gledati lijevo. Uvijek traži vrijednost u krajnjem lijevom stupcu raspona zadanog argumentom table_array (stol).
U funkciji VPR sve vrijednosti su neosjetljive na velika i mala slova, tj. mala i velika slova su ekvivalentna.
Ako je vrijednost koju tražite manja od minimalne vrijednosti u prvom stupcu raspona koji se traži, funkcija VPR će prijaviti grešku #AT (#N/A).
Ako je 3. argument col_index_num (broj_stupca) manje od 1funkcija VPR će prijaviti grešku #VRIJEDNOST! (#VRIJEDNOST!). Ako je veći od broja stupaca u rasponu table_array (tablica), funkcija će prijaviti pogrešku #REF! (#VEZA!).
Koristite apsolutne reference ćelija u argumentu table_array (tablica) tako da se prilikom kopiranja formule sačuva točan raspon pretraživanja. Pokušajte koristiti imenovane raspone ili tablice u Excelu kao alternativu.
Kada tražite približno podudaranje, zapamtite da prvi stupac u rasponu koji tražite mora biti poredan uzlaznim redoslijedom.
Na kraju, sjetite se važnosti četvrtog argumenta. Koristite vrijednosti PRAVI KOD (ISTINA) ili FALSE (LAŽ) namjerno i riješit ćete se mnogih glavobolja.

U sljedećim člancima našeg vodiča za funkcije VPR u Excelu naučit ćemo naprednije primjere, kao što je izvođenje raznih izračuna pomoću VPR, izdvajanje vrijednosti iz više stupaca i više. Hvala vam što ste pročitali ovaj vodič i nadam se da se ponovno vidimo sljedeći tjedan!