Trikovi u Excelu 11. deo: VLOOKUP, druga najkorištenija Excel funkcija ili: Šta povezuje Excel, biologiju i gospodina Bulajića

Ukoliko te zanima Excel, pogledaj i sledeće tekstove. Dovoljno je da klikneš na link:

Trikovi u Excelu, 1. deo

Trikovi u Excelu, 2. deo

Trikovi u Excelu 3. deo (shortcuts 1/3)

Trikovi u Excelu 4. deo (Custom Sort)

Trikovi u Excelu, 5. deo

Trikovi u Excelu 6. deo (Custom View)

Trikovi u Excelu 7. dio: Sekundarna osa, ili kako prikazati podatke u dvije skale na chartu

Trikovi u Excelu 8. deo (shortcuts u Excelu 3/3)

Trikovi u Excelu 9. deo: Print Titles ili kako da se prvi red ponavlja na svakom listu u Excelu

Trikovi u Excelu 10. deo: Sparklines ili grafikon unutar jedne ćelije

PROLOG

Prije petnaestak godina, baš negdje u ovo doba, dođe meni dobra prijateljica i – umjesto „dobar dan“ ili nečeg sličnog osu paljbu još sa vrata: „J... te tvoj Excel šugavi, da te j...! Umalo da izgubim posao zbog njega! Poslušavši tvoj savjet, gospodina 'Bulajića' sam prekrstila u 'Biologic' a, kako je jako sujetan i još više uticajan, malo je nedostajalo da dobijem otkaz.“

Pitate me šta se desilo? Pa, kad već navaljujete, evo...

Čin prvi

Daleko najkorištenija funkcija u Excelu je sigurno suma. A znate li koja je druga? Ne znam ni ja, ali gajim određene sumnje. Nakon više od 15 godina korištenja Excela jedna funkcija „odskače“ po učestalosti korištenja.

Šta je zajedničko izdavačkim kućama, vojsci, bankama, javnom sektoru, privatnim, međunarodnim i nevladinim organizacijama? Osim činjenice da je moja malenkost radila u bar po dva predstavnika svakog od njih? E, pa... zajedničko im je da svi pri korištenju Excela osim funkcije za zbir najčešće koriste funkciju LOOKUP. Postoje VLOOKUP i HLOOKUP, uz napomenu da se VLOOKUP daleko češće koristi pa ćemo se ovde baviti samo njim.

Čemu služi VLOOKUP? Prosto rečeno, ova funkcija koristi vrijednost iz jedne tabele, nalazi tu vrijednost u drugoj tabeli i daje vrijednost određene kolone u istom redu. Ovako vjerovatno zvuči komplikovano pa ćemo demonstrirati na jednostavnom primjeru.

Čin drugi

Recimo da u jednoj tabeli imate spisak osoba sa podacima o JMBG, imenu i prezimenu.  

[caption id="" align="aligncenter" width="400"] Slika 1. Tabela s imenima u koju treba dodati adrese iz druge tabele[/caption]

Potrebni su vam podaci o adresi tih osoba. Nažalost, adrese se nalaze u drugoj tabeli koja ne sadrži imena i prezimena već samo njihove JMBG-ove s adresama.

 Potrebno je na neki način povezati ove dvije tabele, tako da svakom JMBG-u iz prve tabele pridružite odgovarajuću adresu iz druge tabele. Za one koji se bave bazama podataka ovo ne predstavlja problem jer koriste „join“. Ovaj tekst pokazuje kako se to radi u Excelu.

Šta je potrebno za povezivanje ove dvije tabele? Zajednički podatak, odnosno kolona koja se nalazi u obje tabele. U našem slučaju to je kolona JMBG.  

Da bi se dobio podatak o adresi za svaki JMBG i dodao u prvu tabelu, VLOOKUP funkcija koristi vrijednost u prvoj tabeli (JMBG) (korak 1), nalazi istu vrijednost (odabrani JMBG) u drugoj tabeli  s adresama (korak 2) i očitava sadržaj određene kolone (u našem slučaju adresu odgovarajućeg JMBG-a) - korak 3. Konačno, vraća rezultat u ćeliju u koju ste ubacili funkciju (korak 4).

    Ovo je najvažnije u vezi LOOKUP-a. Bitno je da shvatite kako funkcija radi, sve ostalo je tehnika.

Ima još nekoliko sitnica na koje morate obratiti pažnju pri korištenju funkcije VLOOKUP, ali više o njima na kraju.

Dakle,  odaberite ćeliju u koju želite dodati adresu. Ubacite funkciju (Insert=>Function, ili kombinacija tipki SHIFT+F3). Ukucajte „VLOOKUP“ i pritisnite dugme „Go“. U listi odaberite „VLOOKUP“ i kliknite na „OK“. Pojavljuje se prozor za definisanje argumenata funkcije.

 

[caption id="" align="aligncenter" width="570"] Slika 5. Prozor u kome se definišu argumenti funkcije VLOOKUP[/caption]

 Intermezzo

Ovde moram napraviti digresiju i iskoristiti trenutak da dam malo objašnjenje o funkcijama uopšte.

Mada Excel ima preko 300 ugrađenih funkcija, gotovo sve rade na manje-više isti način: odaberete funkciju, nahranite je („definišete argumente“) i dobijete rezultat. Argumenti su parametri koje funkcija koristi da bi radila. Funkcije imaju različit broj argumenata: od jednog ili nijednog pa do 3-4 ili čak i više. Npr. za funkciju sabiranja morate definisati brojeve koje sabirate. Ti brojevi su argumenti funkcije sabiranja.

Čin treći

Dakle, VLOOKUP ima 3+1 argument: 1.lookup_value (vrijednost iz prve tabele, u našem slučaju to je JMBG čiju adresu tražimo), 2.table_array (lookup tabela, ili raspon ćelija u drugoj tabeli u kome tražimo vrijednost), 3.col_index_num ili redni broj kolone/stupca iz kojeg tražimo rezultat (kolona s adresom), ali brojano od kolone koja je zajednička objema tabelama (JMBG) udesno.

Kada sam naveo 3+1 argument, to znači da su 3 argumenta obavezna a jedan opcioni (neobavezan). U gornjem prozoru (važi za sve funkcije) obavezni argumenti su označeni podebljanim slovima a opcioni običnim tekstom. Ipak, bolje je definisati i neobavezne argumente a vidjećete i zašto.

Usput, vjerovatno se već pitate kakve veze sve ovo ima sa gospodinom Bulajićem sa početka? Hehehe, bez brige, uskoro dolazimo i do toga.

Dakle, idemo definisati argumente funkcije VLOOKUP (stručni termin), ili „nahraniti funkciju“ (moj izraz).

Lookup_value je vrijednost iz prve tabele s imenima za koju tražimo adresu u drugoj tabeli. Kliknite u ovo polje a zatim na JMBG u tabeli s imenima. Dakle, tražimo taj JMBG.

 

Table_array je raspon ćelija u koji gledamo kako bismo pronašli odgovarajući JMBG i željenu adresu. Kliknite u ovo polje i odaberite raspon ćelija u tabeli s adresama (lookup tabeli),ali tako da selektujete prvo zajedničku kolonu (u ovom slučaju JMBG) i kolone desno od nje. Ovo je bitno jer možda kolona JMBG nije prva kolona u lookup tabeli. Pri odabiru obavezno uključite i imena kolona. Pazite na to da raspon ćelija sadrži dolarske oznake ($) ispred imena kolona i broja redova (npr. $A$1:$D$8). Ovim ste definisali raspon ćelija u kome tražite JMBG-ove.

   

 Col_index_num je redni broj kolone koja sadrži vrijednost koju tražimo (adresa), brojano od zajedničke vrijednosti (JMBG) u lookup tabeli. Zajednička kolona (JMBG) u ovom računanju je uvijek prva a ako je adresa 1 kolonu udesno, onda ovde unesite broj 2. Znači, ovo je redni broj stupca s traženom vrijednošću (adresom) u odabranom rasponu („table_array“). Ovim ste definisali redni broj kolone čiji sadržaj tražite u odabranom rasponu.

 

 Range_Lookup definiše šta funkcija da radi ukoliko ne pronađe odgovarajuću vrijednost u lookup tabeli (ukoliko traženi JMBG ne postoji u tabeli s adresama). „False“ znači da traži tačnu vrijednost (odgovarajući JMBG) a ako ga ne pronađe, da javi grešku, dok „True“ znači da u slučaju nepostojanja odgovarajućeg JMBG-a kao rezultat vrati vrijednost najsličnijeg JMBG-a. Ukoliko ostavite ovo polje prazno, Excel će se ponašati kao da ste unijeli „True“, odnosno, ako ne pronađe traženi JMBG, vratiće adresu JMBG-a koji je najsličniji traženom. U ovom slučaju (odabrali ste "True", ili ostavili prazno), čak i ako se traženi JMBG nalazi u tabeli a neki sličan njemu je zbog sortiranja bliži početku tabele, funkcija će odabrati pogrešan JMB. Preporučujem da koristite „False“, osim ukoliko vam je baš svejedno hoćete li dobiti tačan ili približan rezultat. Ovim ste definisali da funkcija traži izričito identične JMBG-ove.

 

[caption id="" align="aligncenter" width="624"] Slika 9. Podešavanje da li je prihvatljiva tačno odabrana vrijednost, ili je prihvatljiva i približna ili slična[/caption]

 

Ukoliko ste pravilno unijeli argumente, u ovom prozoru ćete već vidjeti rezultat za odabrani JMBG.

Kliknite na dugme OK, kopirajte formulu u ćelije ispod i provjerite rezultate.

Eto, nadam se da nisam previše zapetljao.

EPILOG

Ah, da... Kao što vjerovatno pretpostavljate (a ukoliko ne pretpostavljate, znači da sam loše objasnio), prijateljica iz prologa nije unijela „FALSE“ u argumentu „Range_Lookup“ a funkcija (pošto nije pronašla gospodina Bulajića) „pokupila“ je najsličniju riječ („Biologic“).

Važne napomene (gotovo da ima više napomena nego osnovnog teksta, ali takva je funkcija a bojim se da nešto ne izostavim):

-Kada selektujete lookup tabelu („table_array“ argument), uvijek selektujte od kolone koja je zajednička objema tabelama udesno, tako da  je zajednička kolona prva u odabranom rasponu ćelija. Ovo je bitno jer nekada zajednička kolona možda neće biti prva kolona u lookup tabeli.

- Vrijednost koju treba da dobijete treba da je u lookup tabeli u koloni koja je desno od zajedničke kolone u istoj tabeli. VLOOKUP funkcioniše samo udesno, ali ne i ulijevo.

- Kada selektujete table_array, pazite na to da u prozoru definisanja argumenata funkcije raspon ćelija sadrži dolarske oznake ($) ispred imena kolona i broja redova (npr. $G$1:$H$25). Najlakše  ćete to uraditi ako u nakon odabira Table_Array-a selektujete raspon i pritisnete tipku F4 dok se ne pojave dolarske oznake ispred oznake kolone i broja reda.

Ovo (apsolutna referenca) je bitno zbog kopiranja formule: ukoliko ne postavite dolarske oznake, raspon će se pri kopiranju promjeniti i funkcija neće dati tačne rezultate (prosto je nevjerovatno koliko se ovo često javlja). O apsolutnim i relativnim referencama nekom drugom prilikom.

-Toplo preporučujem da definišete argument Range_Lookup (TRUE/FALSE). Ako ostavite prazno a Excel ne nađe traženu vrijednost u lookup tabeli (ili je u poretku slična vrijednost bliža vrhu table), onda će uzeti najsličniju vrijednost, što najčešće ne želite (sjetite se gospodina Bulajića).

-Negdje se navodi da lookup tabela mora biti sortirana po zajedničkoj vrijednosti obje tabele, inače funkcija neće dati tačan rezultat. Po mom iskustvu, nije obavezno da tabela bude sortirana, ali je bitno da sadrži jedinstvene vrijednosti (vidi dalje).

-Za pravilan rad LOOKUP funkcije obavezno je da lookup tabela sadrži jedinstvene vrijednosti zajedničke kolone. U našem primjeru to znači da se jedan JMBG  u tabeli s adresama ne smije ponavljati, odnosno  može se pojaviti samo jednom. Kada bi se pojavio više puta i to sa različitim adresama, funkcija LOOKUP bi „pokupila“ onu adresu koja je prva u tabeli a ostale zanemarila.

-Jako često se dešava da funkcija javi da nema odgovarajuće vrijednosti u lookup tabeli, mada ona tamo postoji. Najčešće se to dešava u slučajevima gdje su brojevi u jednoj od tabela formatirani kao tekst. Takvi brojevi izgledaju jednako, ali ih Excel tretira kao tekst i ne može da poveže „babe i žabe“. U takvim slučajevima je potrebno brojeve unesene kao tekst konvertovati u brojeve i funkcija će dati pravi rezultat. Ovo je također jedan od kandidata za najčešći uzrok greške u rezultatu funkcije VLOOKUP.

 U idućem broju: imenovani rasponi, ili kako komplikovane formule napraviti kratkim i jasnim