Razgovarali smo sa našim cenjenim predavačem Davidom Vundukom na temu napredne upotrebe programa MS Excel. Tom prilikom objasnio nam je kako funkcioniše VLOOKUP funkcija, za vertikalno pretraživanje podataka na zadatu temu i baš to bi želeli da podelimo sa vama.

Kada koristimo VLOOKUP?

VLOOKUP funkciju koristimo za prikazivanje podataka iz velike baze (tabele) podataka, radi preglednijih i detaljnijih izveštaja o konkretno traženom pojmu iz tih velikih tabela, spiskova.

Na primer, ukoliko želite da proverite brojno stanje, boju, veličinu i objekat gde se određeni proizvod nalazi, možete uneti samo šifru proizvoda i upotrebom VLOOKUP funkcije dobićete sve tražene podatke.

Drugi primer bi bio kada imate veliku bazu zaposlenih i potrebno vam je baš za Peru Perića da dobijete sve njegove dostupne podatke (adresa, kontakt telefon, dužina staža, stručna sprema…), to možete dobiti unosom njegovog JMBG ili dodeljenog jedinstvenog kompanijskog ID-ja i svi podaci će vam biti prezentovani.

Funkcija je izuzetno korisna za pretrage podataka u velikim bazama i ima široku primenu.

Kako se koristi VLOOKUP?

Da bi koristili VLOOKUP funkciju, neophodno je da otvorite osnovnu bazu podataka (Slika 1) i da napravite jednu pomoćnu tabelu u kojoj će se prikazivati svi željeni podaci (Slika 2). Pomoćna tabela može biti kreirana na istom sheet-u na kom se nalazi i velika tabela sa podacima, ili na posebnom sheet-u.

Slika 1. Baza podataka polaznika unutar koje ćemo vršiti pretragu

Slika 2. Pomoćna tabela otvorena u posebnom sheet-u

Da bi naglasili, posebno smo obojili površinu tabele da bi bila uočljivija radnja.

U prvoj koloni ove naše pomoćne tabele nalaze se nazivi kolona iz baze podataka polaznika koja se pretražuje. Unosite samo kolone za koje želite da dobijete podatak.

Slika 3. Naslovi kolona iz baze podataka polaznika

Treba posebno da obratite pažnju na tip polja u koje ćete uneti vrednost. Ukoliko se traži datum ili brojčana vrednost sa decimalama i valutom, to polje možete odmah formatirati preko Format Cells… opcije.

U prvo polje (B2) pomoćne tabele ćemo definisati ono po čemu želimo da se pretraga i filtriranje izvrši. Mora biti jedinstvene vrednosti, kao što su šifra proizvoda, broj zdravstvenog kartona, ID dobavljača, JMBG, broj registarske tablice… U ovo polje se ručno unosi vrednost kada se definišu svi ostali parametri za izvršavanje VLOOKUP funkcije.

Slika 4. Lookup vrednosno polje

Znači, zvanično počinjemo od drugog polja (B3) pomoćne tabele u koje unosimo VLOOKUP funkciju. Funkcija se unosi preko Formula Bar-a (fx).VLOOKUP sintaksa ima sledeći oblik:

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

i potrebno je da definišete 4 polja da bi dobili željene rezultate. Sve započinjemo odabirom VLOOKUP funkcije.

Slika 5. Definisanje VLOOKUP sintakse za željenu pretragu

Ukoliko se ova funkcija ne nalazi među prvih 10 ponuđenih, u polje Search for a function unosimo VLOOKUP. Potvrdom na Go, VLOOKUP će biti u ponuđenim funkcijama. Potvrdimo sve sa OK.

Slika 6. Unos i odabir VLOOKUP funkcije

Slika 7. Odabir vrednosti za VLOOKUP funkciju

Prvo polje Lookup_value je definisana vrednost po osnovu koje ćemo vršiti pretragu. U našem slučaju to je ID polje (B2).

Slika 8. Odabrana Lookup vrednost po kojoj će se vršiti pretraga

Kako u našem slučaju izlistavamo više podataka, potrebno je da zaključamo polje koje definišemo kao Lookup vrednost za ostala polja. To ćemo uraditi ako stanemo mišem na B2 i pritisnemo funkcionalni taster u gornjem redu tastature F4. Tada će se pojaviti dodatni karakteri $ uz oznaku polja, što bi značili da smo zaključali/fiksirali polje.

Slika 9. Zaključavanje polja Lookup vrednosti

U drugom polju VLOOKUP sintakse, Table-array definiše se raspon tabele sa podacima iz kojih će se pretraživati željeni pojmovi.

Slika 10. Odabir baze podataka polaznika za pretragu

Slika 11. Odabran raspon podataka polaznika za pretragu

Prema savetu našeg predavača, uvek obuhvatite i prazne redove vaše velike tabele (baze podataka polaznika u našem primeru), kako bi se u narednim pretragama obuhvatali i novo uneti podaci.

Takođe zaključajte polja raspona sa funkcionalnim tasterom F4.

Slika 12. Zaključana polja raspona pretrage

Treće polje za definisanje u ovoj sintaksi je Col_index_num i predstavlja redni broj kolone iz koje nam treba traženi pojam. U našem slučaju, mi želimo da saznamo Ime i prezime polaznika sa nekim konkretnim ID brojem. To je kolona broj 2 iz Baze podataka polaznika i to je vrednost koja nam treba za Col_indew_num.

Slika 13. Određivanje broja kolone (Col_index_num) iz Baze podataka polaznika

Slika 14. Unos broja kolone za pretraživanje

I za kraj, ostalo nam je četvrto polje za definisanje VLOOKUP funkcije, Range_lookup. U ovo polje se unose jedinice 0 ili 1. 0 predstavlja FALSE, odnosno potpuno podudaranje, a 1 predstavlja TRUE odnosno približno podudaranje. Šta ovo znači? Ukoliko unesete 0 kao vrednost, tada će vam prikazivati isključivo traženu vrednost. Međutim ukoliko unesete 1 kao vrednost, prikazivaće vam sve podatke koji su manji od željene vrednosti u pretrazi. 1 se retko koristi.

Slika 15. Unos TRUE/FALSE vrednosti u Range_lookup

Definisanjem svih vrednosti u VLOOKUP poljima, dobija se sledeći prikaz:

Slika 16. Definisana VLOOKUP funkcija za polje B2

Ova poruka ne bi trebala da vas brine. Pokazuje da postoji greška. Greške nema 😊, samo nedostaje podatak u polju B2 (ID polje) na osnovu kojeg će se vršiti pretraga.

Za sva ostala polja (Adresu, Grad, Datum rođenja i Pol – u našem primeru), možete uraditi automatsko popunjavanje kolona, dvoklikom na + u donjem desnom uglu B3 definisanog polja.

Slika 17. Automatsko popunjavanje kolona

Na ovaj način, moraju se samo izvršiti korekcije broja kolone u kojoj će se vršiti pretraga sledećeg pojma. U našem slučaju za Adresu (B4), kolona koja će biti pretraživana iz Baze podataka polaznika je šesta po redu (videti na Slici 13).

Slika 18. Unos novih vrednosti kolona u kojima ćemo vršiti pretragu

Tako uneti i ostale brojeve kolona za pretraživane pojmove. Za Grad je kolona 7, za Datum rođenja je kolona 3, a za Pol je kolona 4.

Kada smo uneli sve vrednosti, u polje B2 (ID) unosimo željeni kriterijum za pretragu. Na primer ID broj polaznika – 50002, i uz pomoć VLOOKUP-a dobijamo sve željene podatke vezano za ovu našu pretragu.

Slika 19. Prikaz pretrage prema traženom ID broju

Ukoliko vaša inicijalna baza podataka ima puuuno kolona i podataka, naš predavač David je dao još jedan savet. Pre popunjavanja pomoćne tabele, možete napisati van same tabele, brojeve kolona u kojima se nalaze traženi pojmovi. U našem slučaju bi to izgledalo ovako:

Slika 20. Priprema podataka pre definisanja VLOOKUP sintakse. Upis brojeva kolona koje će biti pretraživane

Tada u treće polje VLOOKUP funkcije unosimo C3 polje (koje sadrži vrednost koja nam treba, a to je broj kolone iz velike Baze podataka polaznika).

Slika 21. Olakšani način definisanja VLOOKUP sintakse

Potom automatski popunjavamo ostatak kolona (dvoklikom na + u donjem desnom uglu). Tada ćete primetiti da je VLOOKUP sintaksa popunjena sa kolonama koje odgovaraju traženom pojmu tj. polju.

Slika 22. Automatski popunjeni ažurirani podaci – provera 😊

Nadamo se da su vam ova uputstva bila od pomoći. Ako želite da naučite kako da koristite i druge napredne funkcije MS Excel programa, najavili smo kurs Napredne tehnike rada u programu MS Excel za 6. avgust. Dođite i ovladajte tabelama, podacima i pravljenjem kvalitetnih izveštaja.

Tutorijal možete preuzeti u pdf formatu – VLOOKUP u vašoj službi – napredni MS Excel tips&tricks