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

sadržaj

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

Danas počinjemo seriju članaka koji opisuju jednu od najkorisnijih značajki Excela − VPR (VLOOKUP). Ova funkcija je istovremeno jedna od najsloženijih i najmanje razumljivih.

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

VLOOKUP funkcija u Excelu – opći opis i sintaksa

Pa šta je to VPR? Pa, prije svega, to je Excel funkcija. Šta ona radi? Traži vrijednost koju navedete i vraća odgovarajuću vrijednost iz druge kolone. tehnički gledano, VPR traži vrijednost u prvoj koloni datog raspona i vraća rezultat iz druge kolone u istom redu.

U najčešćoj aplikaciji, funkcija VPR pretražuje bazu podataka za dati jedinstveni identifikator i izdvaja neke informacije vezane za njega iz baze podataka.

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

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 Excel-u ima 4 opcije (ili argumenta). Prva tri su obavezna, zadnja je opciona.

lookup_value (lookup_value) – Vrijednost koju treba tražiti. To može biti vrijednost (broj, datum, tekst) ili referenca ćelije (koja sadrži traženu vrijednost) ili vrijednost koju vraća neka druga Excel funkcija. 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 prvoj koloni raspona koji se traži, funkcija VPR prijavit će grešku #AT (#N / A).

table_array (tabela) – dvije ili više kolona podataka. Zapamtite, funkciju VPR uvijek traži vrijednost u prvoj koloni raspona datog u argumentu table_array (tabela). Opseg koji se može videti može sadržati različite podatke, kao što su tekst, datumi, brojevi, logičke vrednosti. Funkcija ne razlikuje velika i mala slova, što znači da se znakovi velikih i malih slova smatraju istim. Dakle, naša formula će tražiti vrijednost 40 u ćelijama iz A2 to A15, jer je A prvi stupac raspona A2:B15 dat u argumentu table_array (tabela):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (broj_kolone) je broj kolone u datom rasponu iz kojeg će biti vraćena vrijednost u pronađenom redu. Krajnja lijeva kolona u datom rasponu je 1, druga kolona je 2, treća kolona 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 je vrijednost argumenta col_index_num (broj_kolone) manji od 1onda VPR prijavit će grešku # VALUE! (#VRIJEDNOST!). I ako je veći od broja kolona u rasponu table_array (tabela), funkcija će vratiti grešku #REF! (#VEZA!).

range_lookup (range_lookup) – određuje šta treba tražiti:
- tačno podudaranje, argument mora biti jednak FALSE (FALSE);
- približno podudaranje, argument jednak TRUE CODE (TRUE) ili uopće nije navedeno.
Ovaj parametar nije obavezan, ali veoma važan. Kasnije u ovom tutorijalu VPR Pokazat ću vam nekoliko primjera koji objašnjavaju kako napisati formule za pronalaženje tačnih i približnih podudaranja.

VLOOKUP Primjeri

Nadam se da je funkcija VPR postati ti malo jasniji. Pogledajmo sada neke slučajeve upotrebe VPR u formulama sa stvarnim podacima.

Kako koristiti VLOOKUP za pretraživanje u drugom Excel listu

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

Da bi koristili VPR, potražite u drugom Microsoft Excel listu, Morate u argumentu table_array (tabela) navedite naziv lista sa znakom uzvika iza kojeg slijedi raspon ćelija. Na primjer, sljedeća formula pokazuje da je raspon A2: B15 je na listu pod nazivom List2.

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

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

Naravno, naziv lista ne mora se unositi ručno. Samo počnite kucati formulu, a kada dođe do argumenta table_array (tabela), prebacite se na željeni list i mišem odaberite željeni raspon ćelija.

Formula prikazana na slici ispod traži tekst “Proizvod 1” u koloni A (to je 1. stupac raspona A2:B9) 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 (tabela) poželjno je uvijek koristiti apsolutne reference (sa znakom $). U ovom slučaju, raspon pretraživanja će ostati nepromijenjen prilikom kopiranja formule u druge ćelije.

Pretražujte u drugoj radnoj svesci pomoću VLOOKUP-a

Da funkcioniše VPR radili između dvije Excel radne knjige, morate navesti naziv radne knjige u uglastim zagradama prije naziva lista.

Na primjer, ispod je formula koja traži vrijednost 40 na listu List2 u knjizi Numbers.xlsx:

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

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

Evo najlakšeg načina da kreirate formulu u Excelu pomoću VPRkoji povezuje sa drugom radnom sveskom:

Otvori obe knjige. Ovo nije potrebno, ali je lakše kreirati formulu na ovaj način. Ne želite da ručno unesete naziv radne sveske, zar ne? Osim toga, zaštitit će vas od slučajnih grešaka u kucanju.
Počnite kucati funkciju VPRa kada je u pitanju rasprava table_array (tabela), prebacite se na drugu radnu knjigu i u njoj odaberite traženi raspon pretraživanja.

Snimak ekrana ispod prikazuje formulu s pretraživanjem postavljenim na raspon u radnoj knjizi PriceList.xlsx na listu cijene.

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

Ako naziv radne knjige ili lista sadrži razmake, onda se mora staviti u apostrofe:

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

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

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

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

Da biste kreirali imenovani raspon, jednostavno odaberite ćelije i unesite odgovarajuće ime 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 imena raspona funkcionira za cijelu Excel radnu knjigu, tako da nema potrebe za navođenjem naziva lista za argument table_array (tablica), čak i ako se formula i raspon pretraživanja nalaze na različitim radnim listovima. Ako se nalaze 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 mnogo jasnije, slažete se? Također, korištenje imenovanih raspona je dobra 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 tačan.

Ako konvertujete raspon ćelija u punopravnu Excel tabelu pomoću naredbe sto (Tabela) tab umetanje (Insert), onda kada odaberete raspon pomoću miša, Microsoft Excel će automatski dodati nazive stupaca (ili naziv tablice ako odaberete cijelu tablicu) u formulu.

Gotova formula će izgledati otprilike ovako:

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

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

Ili možda čak i ovako:

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

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

Kada koristite imenovane opsege, veze će upućivati na iste ćelije bez obzira gdje kopirate funkciju VPR u okviru radne sveske.

Korištenje zamjenskih znakova u VLOOKUP formulama

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

Znak pitanja (?) – 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 tačno teksta morate pronaći.
Kada želite pronaći neku riječ koja je dio sadržaja ćelije. Znaj to VPR pretražuje po sadržaju ćelije u cjelini, kao da je opcija omogućena Uskladite cijeli sadržaj ćelije (Cela ćelija) u standardnoj Excel pretrazi.
Kada ćelija sadrži dodatne razmake na početku ili na kraju sadržaja. U takvoj situaciji možete se dugo mučiti, pokušavajući shvatiti zašto formula ne funkcionira.

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

Recimo da želite da tražite određenog kupca u bazi podataka prikazanoj ispod. Ne sjećate se njegovog prezimena, ali znate da počinje sa “ack”. Evo formule koja će dobro obaviti posao:

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

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

Sada kada ste sigurni da ste pronašli ispravno ime, možete koristiti istu formulu da pronađete iznos koji je platio ovaj kupac. Da biste to učinili, samo promijenite treći argument funkcije VPR na željeni broj kolone. 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 se završava na "čovjek":

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

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

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

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

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

~ Na listi nalazimo prvo ime koje se sastoji od 5 karaktera:

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

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

Da funkcioniše VPR sa zamjenskim znakovima radi ispravno, kao četvrti argument koji uvijek trebate koristiti FALSE (LAŽNO). Ako raspon pretraživanja sadrži više od jedne vrijednosti koja odgovara pojmovima za pretraživanje sa zamjenskim znakovima, tada će biti vraćena prva pronađena vrijednost.

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

Pogledajmo sada malo složeniji primjer kako pretraživati pomoću funkcije VPR po vrijednosti u ćeliji. Zamislite da je kolona A lista licencnih ključeva, a kolona B lista imena koja posjeduju licencu. Osim toga, u ćeliji C1 imate dio (nekoliko znakova) neke vrste 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 datom rasponu i vraća odgovarajuću vrijednost iz stupca B. Imajte na umu da u prvom argumentu koristimo znak ampersand (&) prije i poslije reference ćelije da povežemo tekstualni niz.

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

Imajte na umu da argument table_array (tabela) na gornjoj slici ekrana sadrži naziv tabele (Tabela 7) umjesto navođenja raspona ćelija. To je ono što smo uradili u prethodnom primjeru.

Tačno ili približno podudaranje u funkciji VLOOKUP

I na kraju, pogledajmo pobliže posljednji argument koji je specificiran za funkciju VPR - range_lookup (interval_view). Kao što je pomenuto na početku lekcije, ovaj argument je veoma važan. Možete dobiti potpuno različite rezultate u istoj formuli s njenom vrijednošću TRUE CODE (TAČNO) ili FALSE (LAŽNO).

Prvo, hajde da saznamo šta Microsoft Excel podrazumeva pod tačnim i približnim podudaranjima.

Ako je argument range_lookup (range_lookup) je jednako FALSE (FALSE), formula traži tačno podudaranje, tj. potpuno istu vrijednost kao da je data u argumentu lookup_value (lookup_value). Ako je u prvoj koloni opsega tsposobni_niz (tabela) nailazi na dvije ili više vrijednosti koje odgovaraju argumentu lookup_value (search_value), tada će biti odabran prvi. Ako nema podudaranja, funkcija će prijaviti grešku #AT (#N / A). Na primjer, sljedeća formula će prijaviti grešku #AT (#N/A) ako nema vrijednosti u rasponu A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Ako je argument range_lookup (range_lookup) je jednako TRUE CODE (TRUE), formula traži približno podudaranje. Tačnije, prvo funkcija VPR traži tačno podudaranje, a ako nije pronađeno, bira približno. Približno podudaranje je najveća vrijednost koja ne prelazi vrijednost navedenu u argumentu. lookup_value (lookup_value).

Ako je argument range_lookup (range_lookup) je jednako TRUE CODE (TRUE) ili nije navedeno, tada bi vrijednosti u prvoj koloni raspona trebale biti sortirane uzlaznim redoslijedom, odnosno od najmanjeg do najvećeg. Inače, funkcija VPR može vratiti pogrešan rezultat.

Da bolje razumemo važnost izbora TRUE CODE (ISTINA) ili FALSE (FALSE), pogledajmo još neke formule s funkcijom VPR i pogledajte rezultate.

Primjer 1: Pronalaženje tačnog podudaranja s VLOOKUP-om

Kao što se sjećate, za traženje tačnog podudaranja, četvrti argument funkcije VPR trebalo bi da bude važno FALSE (LAŽNO).

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

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

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

Imajte na umu da naš opseg pretraživanja (kolona A) sadrži dvije vrijednosti 50 – u ćelijama A5 и A6. Formula vraća vrijednost iz ćelije B5. Zašto? Jer kada se traži tač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 je argument range_lookup (range_lookup) je jednako TRUE CODE (TRUE) ili izostavljeno, prva stvar koju trebate učiniti je sortirati raspon prema prvoj koloni uzlaznim redoslijedom.

Ovo je vrlo važno zbog funkcije VPR vraća sljedeću najveću vrijednost nakon zadane, a zatim se pretraga zaustavlja. Ako zanemarite ispravno sortiranje, na kraju ćete dobiti vrlo čudne rezultate ili poruku o greš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 saznati koja od životinja ima najveću brzinu 69 milja na sat. I evo rezultata koji mi je funkcija vratila VPR:

Kao što vidite, formula je vratila rezultat Antilope (Antelope), čija brzina 61 milja na sat, iako se na listi takođe nalaze Cheetah (Gepard) koji trči na brzinu 70 milja na sat, a 70 je bliže 69 nego 61, zar ne? Zašto se ovo dešava? Jer funkcija VPR kada se traži približno podudaranje, vraća najveću vrijednost koja nije veća od one koja se traži.

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

VLOOKUP u Excelu – ovo morate zapamtiti!

funkcija VPR Excel ne može gledati lijevo. Uvijek traži vrijednost u krajnjoj lijevoj koloni opsega datog argumentom table_array (tabela).
U funkciji VPR sve vrijednosti ne razlikuju velika i mala slova, odnosno mala i velika slova su ekvivalentna.
Ako je vrijednost koju tražite manja od minimalne vrijednosti u prvoj koloni raspona koji se traži, funkcija VPR prijavit će grešku #AT (#N / A).
Ako je 3. argument col_index_num (broj_kolone) manji od 1Funkcija VPR prijavit će grešku # VALUE! (#VRIJEDNOST!). Ako je veći od broja stupaca u rasponu table_array (tabela), funkcija će prijaviti grešku #REF! (#VEZA!).
Koristite apsolutne reference ćelije u argumentu table_array (tablica) tako da se pri kopiranju formule sačuva ispravan raspon pretraživanja. Pokušajte koristiti imenovane raspone ili tablice u Excelu kao alternativu.
Prilikom pretraživanja približnog podudaranja, zapamtite da prva kolona u rasponu koji tražite mora biti sortirana uzlaznim redoslijedom.
Konačno, zapamtite važnost četvrtog argumenta. Koristi vrijednosti TRUE CODE (ISTINA) ili FALSE (NETAČNO) namjerno i riješit ćete se mnogih glavobolja.

U sljedećim člancima našeg tutorijala o funkciji VPR u Excel-u ćemo naučiti naprednije primjere, kao što je izvođenje različitih proračuna pomoću VPR, izdvajanje vrijednosti iz više kolona i još mnogo toga. Hvala vam što ste pročitali ovaj vodič i nadam se da se vidimo ponovo sljedeće sedmice!