IZBORNIK HOME FORUM ACCESS EXCEL WORD KAZALO
 

HOME

1 ADDRESS
2 AREAS
3 CHOOSE
4 COLUMN
5 COLUMNS
6 HLOOKUP
7 HYPERLINK
8 INDEX
9 INDIRECT
10 LOOKUP
11 MATCH
12 OFFSET
13 ROW
14 ROWS
15 RTD
16 TRANSPOSE
17 VLOOKUP
18 GETPIVOTDATA
   
   
 
 

MICROSOFT EXCEL for dummy - za početnike - Vlookup

FUNKCIJE ZA PRETRAŽIVANJE I REFERENCE
U  MS EXCEL-u 2003

Search This Web Site




PRIMJERI KORIŠTENJA FUNKCIJE VLOOKUP

Kako dijeliti podatke između dvije Excel datoteke

U koliko imate potrebu i želite podijeliti podatke između dvije Excelove datoteke, osim linkanih podataka (sa izborom promjenjivosti) isto možete pomoću funkcije VLOOKUP. Dajte si truda i uvijek imenujte tj. definirajte ime ili naziv za raspon podataka ili ćeliju.

Za ovaj primjer simulirat ću dvije datoteke (OBJE DATOTEKE TREBAJU BITI OTVORENE, prilikom rada)

  1. baza.xls
  2. faktura.xls

Uzmimo za primjer da Vaš radni kolega/kolegica radi na datoteci baza.xls i popunjava je svakodnevno sa podacima kupaca koji kupuju robu u firmi u kojoj radite. Vi u drugoj kancelariji radite fakture dijelite datoteku baza.xls i potrebni su Vam dotični podaci. (ili jednostavno imate dvije odvojene datoteke i želite da tako bude)

Polazimo od prve osnovne datoteke baza.xls iz koje vučemo podatke o kupcima i ostale njihove parametre. Dakle nju popunimo podacima

Ona izgleda ovako kao na slici 1. ispod

Slika 1. Kako dijeliti podatke između dvije Excel datoteke

U drugoj datoteci faktura.xls u kojoj ćemo simulirati Fakturu / Račun (ja ću samo par detalja sa fakture - računa)., kao na slici2 ispod

Slika 2.

Pojasnit ću što sam uradio i kako iskoristiti funkciju VLOOKUP.

Dakle imamo osnovnu bazu podataka (Vi možete imati bilo kakvu drugu datoteku na koju ćete primijeniti funkciju.)
Ta baza podataka nalazi se u samostalnoj datoteci, a nama je cilj u drugoj datoteci izvlačiti podatke po izboru iz ove datoteke baza.xls

FORMIRANJE datoteke baza.xls

Prvo idemo na izradu baza.xls datoteke. Ovdje moramo voditi računa da su nam podaci sortirani (poredani) uzlaznim redoslijedom pa ispred naziva kupca postavimo redne brojeve od 1,2,3,.....1000. Zašto baš do 1000, pa i ne moramo možemo do 500 ali eto neka se nađe, nikada ne znamo koliko možemo imati kupaca.
Prilikom korištenja funkcije Vlookup vodit ćemo računa o ovom kompletnom rasponu ćelija koji nam se proteže od B2 do E1000, u kojem će se nalaziti naši podaci. Zašto od B2 ? Zato što nam se u prvom redu nalazi naslov stupaca koji nam sad nije bitan a redni brojevi također su nama nevažni (ali moraju postojati) .

Odmah moram napomenuti da funkcija VLOOKUP može raditi sa poljem Table_Array  (polje raspona tablice)
bilo da napišemo raspon ćelija B2:E1000 ili da taj isti raspon imenujemo kao skup ćelija npr: "kupci".
Za više detalja o imenovanju raspona ćelija. pogledajtena linku.

Za detalje što je što tj. sintaksu Vlookup funkcije pogledajte na vrhu web stranice linka u ovoj rečenici. Ja sam za ovaj primjer imenovao skup ćelija u dotičnom rasponu pod nazivom "kupci" da bi mi bilo lakše raditi sa funkcijom, što možete vidjeti na slici3 ispod.

Slika 3.

FORMIRANJE (oblikovanje) datoteke faktura.xls (izrada oblika računa)

Vama ću ostaviti da sami oblikujete izgled računa (fakture) sa svim ostalim pripadajućim podacima.
Na računu se moraju nalaziti (a i ne moraju) četiri osnovna podatka

- Kupac
- Šifra kupca
- Mjesto
- Adresa

Ovi podaci (polja) su identični sa podacima u datoteci "baza.xls" inače ne bi imalo smisla, iako u datoteci baza.xls može biti i više podataka ali ih mi nećemo koristiti, poput: telefona, mobitela za kontakt, faxa itd itd...)
Dotična polja postavimo (ugradimo) u našu fakturu, (ja sam za primjer naveo samo te podatke) pogledaj sliku 4 ispod.
Također uočite na istoj slici da sam spojio tri ćelije u jednu (što nije pravilo a Vi to ne morate) no zbog podataka koji eventualno trebaju biti ispod ovih postojat će potreba da su Vam ćelije iste širine, pa zato oblikujte fakturu sa svim podacima a na kraju ugradite (formirajte) ova polja.

NAPOMENA: Ovaj primjer tutoriala je baziran na početni podatak koji moramo znati a to je "Kupac". (Vi možete promijeniti redoslijed pa ići sa Šifra, Kupac, Mjesto, Adresa) ali tada se bazirate na "Šifru" i potrebno je promijeniti organizaciju izvornih podataka u datoteci "baza.xls", tj zamijeniti mjesta stupcima Kupac i Šifra.

Sada, netko može postaviti pitanje. Kako ću znati kakav je točan naziv Kupca od njih 300 ? (mislim na upisanu riječ, jer ako pogriješite tada nema rezultata) ili Točnu Šifru za kupca (ako ste zamijenili mjesta).
Nikako, trebate pogledati u šifarnik tj. baza.xls ili imati negdje popis ispred sebe ;-)
AKo ste mislili na to da imate mogućnost izbora sa Padajuće liste popisa kupaca, zaboravite na to u ovom slučaju. Padajući popis se može izraditi samo ako se podaci "vuku" sa istog Sheeta (Lista) ili drugog Sheeta (u istoj Woorkbook) u kojem radite. Ovdje je poželjno imenovati listu/popis a ako vas to više zanima pogledajte link: http://www.elitesecurity.org/t345750-0#2122661.

Slika 4.

KREIRANJE FORMULE VLOOKUP

Pozicionirani smo na datoteci faktura.xls
Dakle, oblikovali smo fakturu, ugradili naša polja i sada idemo kreirati.
Formulu kreiramo za slijedeće potrebe: (za ovaj primjer to izgleda ovako)

- U ćeliju B2 upisujemo polazni podatak TOČAN naziv kupca (ako ste zamijenili mjesta Kupac i Šifra i želite se bazirati na Šifru kao osnovni podatak) tada morate znati točnu šifru)
- U ćeliji B3 kreiramo formulu VLOOKUP
- U ćeliji B4 kreiramo formulu VLOOKUP (ili jednostavno kopiramo iz ćelije B2, ali PAZITE, trebate zaključati raspon ćelija ako ne koristite imenovani skup)
- U ćeliji B4 kreiramo formulu VLOOKUP (ili jednostavno kopiramo iz ćelije B3, ali PAZITE, trebate zaključati raspon ćelija ako ne koristite imenovani skup)

Korak 1.

U ćeliju B1 upišite naziv prvog kupca iz baze (Kupac A) da bi lakše mogli pratiti rezultat formule.
Kliknite (selektirajte) ćeliju B2 a potom kliknite na gumb za aktiviranje dijalog prozora funkcije (fx) . Za kategoriju (Category) odaberite sa padajućeg izbornika Reference i pretraživanje (Lookup & Reference) a potom pronađite funkciju VLOOKUP. Kliknite na gumb OK

Slika 5. KREIRANJE FORMULE VLOOKUP

Korak 2.

Sada imate ovakav dijalog prozor kao na slici 6 gdje je potrebno upisati argumente funkcije.
Prvi argument funkcije Vlookup je Lookup_value . Kliknite u prazno polje pored naziva argumenta. Tu je potrebno upisati adresu ćelije (ili kliknuti na dotičnu ćeliju B1) u kojoj će se nalaziti podatak na osnovu kojega će funkcija ostale podatke automatski upisati (u ovom slučaju će to biti rezultat za ćeliju B2). Kada mi u ćeliju B1 upišemo naziv kupca npr: "Kupac A" na osnovu tog naziva funkcija Vlookup će tražiti ostale parametre u datoteci baza.xls

Slika 6.

Korak 3.

Slijedeći korak je kliknuti u prazno polje pored argumenta Table_array (polje raspona ćelija).
Na početku ovog tutoriala napomenuo sam da obje datoteke (baza.xls i faktura.xls) trebaju biti pokrenute (otvorene)
U nastavku trebamo kliknuti na TaskBar traci na datoteku baza.xls da je prebacimo u prvi plan (valjda se znate prebacivati sa prozora na prozor) (istodobno nam se dijalog prozor "Argumenti funkcije" nalazi aktivan i na slijedećem prozoru.

Sada pažljivo proučite ovaj dio. 

Kako povući podatke iz druge datoteke u Excelu

Rekao sam da funkcija Vlookup može koristiti dva načina za Table_array. Jedan je raspon ćelija a drugi imenovani skup ćelija.

Primjer 1.
Rad sa rasponom ćelija (selektiranjem)

Ako želimo raditi sa rasponom ćelija tj. apsolutnim adresama onda nakon prebacivanja u prvi plan datoteke baza.xls trebamo selektirati taj raspon ćelija $B$2:$E$1000, pa shodno tome imamo ovakav prikaz kao na slici 7. Excel će automatski upisati naziv datoteke u kojoj selektiramo podatke a isto tako će Excel selektirani raspon napisati kao apsolutne adrese.
Dakle [baza.xls]List1!$B$2:$E$1000  funkciji određuje da se traženi podatak traži
u datoteci baza.xls na Listu1 u rasponu ćelija $B$2:$E$1000

Slika 7.

Primjer 2.
Rad sa imenovanim skupom ćelija

Ako radimo sa imenovanim skupom ćelija (Za više detalja o imenovanju raspona ćelija), skup ćelija (tj. raspon) moramo prethodno prije ove radnje imenovati. Ja sam ga imenovao pod nazivom "kupci" bez navodnika.
Jednostavno selektirani dio ćelija zamijenim sa imenom kupci pa imam formulu [baza.xls]List1!kupci
Dakle [baza.xls]List1!kupci  funkciji određuje da se traženi podatak traži  u datoteci baza.xls na Listu1 u skupu imenovanih ćelija pod nazivom kupci (a taj skup je raspon ćelija od B2:E1000) Pa shodno navedenome imamo ovakav prikaz kao na slici 8. (usput pogledajte sliku 3.)

Slika 8.

Dakle VLOOKUP funkciju tj. argument Table_array  možemo pisati kao =VLOOKUP(B1;$B$2:$E$1000;2;FALSE) ili VLOOKUP(B1;kupci;2;FALSE) , gdje dobivamo isti rezultat ali u drugom slučaju moramo imati imenovani skup ćelija.

Korak 4.

Dolazimo do važnog argumenta tj. određivanja Rednog stupca (Col_index_number) iz kojeg se treba vratiti podatak u odnosu na naš traženi upis (naziv kupca). Kliknite u polje pored ovog argumenta i upišite broj stupca iz kojeg se treba vratiti podatak. Dakle ja ću upisati broj 2 (dva).
Zašto baš dva (2) ? Zato što je to DRUGI stupac po redu od našeg zadanog podatka (naziva kupca je prvi stupac)
a to je stupac "C" u datoteci baza.xls)

Zašto je to drugi stupac a ne treći ? Zato što je prvi stupac u ovom slučaju stupac B (u datoteci baza.xls)
jer smo tako odredili rasponom ćelija od stupca B do stupca E, u kojem funkcija Vlookup uspoređuje podatke
a tu se i nalazi naziv Kupca koji upisujemo u ćeliju B1 (datoteke faktura.xls). Ovdje ne računamo stupac A  (u baza.xls) jer ga nismo obuhvatili selektiranjem raspona ćelija)

Pa prema tome prikaz dijalog prozora za ovaj argument je ovakav kao na slici 9.

Slika 9.

Korak 5.

Argument Range_Lookup (pogledaj vrh ove web stranice) nam služi ako želimo vratiti kao rezultat  točno određene IDENTIČNE podatke (FALSE) ili ako želimo IDENTIČNE ili PRIBLIŽNE podatke tada upišemo (TRUE).
Ako izostavimo ovaj podatak tada će funkcija koristiti kao da smo upisali TRUE.
Ja sam postavio FALSE jer želim identičan podatak koji tražim a to je točno onaj koji odgovara Kupcu A u drugom stupcu.

Slika 10.

I na kraju naša formula izgleda ovako: =VLOOKUP(B1;baza.xls!kupci;2;FALSE)
Kada tu formulu želimo kopirati u redove ispod, moramo za ćeliju B1 postaviti apsolutnu adresu inače će nam se promijeniti,
dakle kliknite mišem u formuli polja (traka formule polja, polje koje se nalazi odmah iza gumba fx , za detalje pogledajte OVDJE) između B i 1 i pritisnite tipku F4 (ili upišite znak $ (dolar) ispred slova i broja.)
Sada naša formula izgleda ovako: =VLOOKUP($B$1;baza.xls!kupci;2;FALSE)

Kopirajte formulu u redove ispod. To izgleda kao na slici 11.

Slika 11.

Naravno da to nije ono što smo željeli, formulu smo kopirali i dobili jedan te isti rezultat. Zašto ?
Zato što nismo promijenili (a nismo ni mogli kada smo kopirali) broj stupca iz kojega nam se mora vratiti rezultat (vidi Korak 4). Dakle, idemo kliknuti na ćeliju B3 i u formuli promijeniti broj stupca 2 (dva) u broj stupca 3 (tri) jer nam se podatak o Mjestu nalazi u trećem stupcu (tj. stupcu D u datoteci baza.xls)
Isto tako i u ćeliji B4 promijenimo broj stupca u broj 4 (četiri).

NAPOMENA: Nemojte da Vas ovdje zbunjuje to što se poklapaju brojevi ćelija
B2, B3 i B4 u faktura.xls i brojevi stupaca 2, 3 i 4  u baza.xls. To je slučajnost (vidi formule ispod).

SADA IMAMO SLIJEDEĆE FORMULE u datoteci faktura.xls :

- U ćeliji B1 => unosimo naziv kupca sami
- U ćeliji B2 => =VLOOKUP($B$1;baza.xls!kupci;2;FALSE)
- U ćeliji B3 => =VLOOKUP($B$1;baza.xls!kupci;3;FALSE)
- U ćeliji B4 => =VLOOKUP($B$1;baza.xls!kupci;4;FALSE)

Ovdje također moramo biti oprezni ako moramo kopirati kreiranu formulu u više redova ispod ili stupaca udesno.
(a to je ovaj B1 u formuli =VLOOKUP(B1;A2:E1000;2)). Tada trebamo "zaključati" ili odrediti apsolutni stupac ili red (apsolutna adresa).
To vršimo tako da dodamo znak dolara "$" ispred znaka stupca ili znaka reda. Tako možemo imati $B (zaključan stupac A) ili $1  (zaključan red 1) ili $B$1 (zaključana ćelija B1 što je u stvari apsolutna adresa dotične ćelije, i prilikom kopiranja u redove ispod ta adresa se neće mijenjati.)
Kod kopiranja formule ako ne koristite naziv skupa ćelija OBAVEZNO zaključajte raspon ćelija
tj. postavite apsolutne adrese ($A$2:$E$1000) iz kojeg vučete podatke jer će Vam se u protivnom prilikom kopiranja formule sve poremetiti.

Kada sve ispravimo imamo rezultat kakav smo željeli, vid sliku 12.

Slika 12.

Zadovoljni napravili smo ono što želimo. ALI !

Što ako nema nikakvog podatka upisanog u ćeliju B1 u datoteci faktura.xls (ovo napominjem samo radi upotrebe funkcije VLOOKUP u drugačijim situacijama). Onda imamo grešku #N/D u ćelijama koje sadrže funkciju Vlookup. (vidi sliku 13.)
BTW: Ovakva greška se može pojaviti i ako ne upišete ispravan naziv Kupca.


Slika 13.

Da bi izbjegli dotičnu grešku možemo funkciju Vlookup kombinirati (ugraditi/ugnijezditi) u funkciju IF.

Ovo je jedan od načina kako se može funkcija Vlookup "ugraditi" (ugnijezditi) u funkciju IF.
=IF($B$1>0;VLOOKUP($B$1;baza.xls!kupci;2;FALSE);"") =>>> ako B1 nije veći od nule tada je rezultat prazna ćelija

Što nam govori ova formula koja se sastoji od funkcija IF i VLOOKUP ?

Funkcija IF sadrži tri argumenta:
- uvjet (ako je B1 veće od nula)
- rezultat ako JE ISTINA - True (ovaj dio sam zamijenio sa funkcijom Vllokup)
- rezultat ako NIJE ISTINA - False (tada ostavi ćeliju praznu, "" dva navodnika znače da će biti prazna ćelija)

Laički rečeno:
Ako je vrijednost u ćeliji B1 veća od nule (B1>0) tada kao rezultat vrati funkciju Vlookup (VLOOKUP($B$1;baza.xls!kupci;2;FALSE)) a ako nije kao rezultat vrati praznu ćeliju (to su ovi navodnici "")

ili =IF([baza.xls]List1!kupci="";"";[baza.xls]List1!kupci) =>>> ako je destinacijska ćelija prazna rezultat je prazna ćelija

Tako da sada imamo ovakvu situaciju kao na slici 14.

Slika 14.

NAPOMENA:

Kod kreiranja ove formule =IF($B$1>0;VLOOKUP($B$1;baza.xls!kupci;2;FALSE);"") opet moramo voditi računa o ćeliji B1 i njenoj apsolutnoj adresi.
U ovom slučaju sam prilikom kopiranja morao postaviti apsolutnu adresu kao prvi argument funkcije IF (da mi se adresa ne bi promijenila)

Postoje situacije kada ova IZVORNA ćelija (dakle ćelija u kojoj se nalazi Lokup_value) nema potrebu za apsolutnom adresom ćelije, već možda samo njenim dijelom (npr: $B1 ili B$1) ili čak ne treba nikako.

I na kraju, OVO je bio tutorial za korištenje (dijeljenje) podataka između dvije Excel datoteke.

Ove dvije datoteke su se nalazile u folderu (mapi) C: primjer
U koliko mijenjate folder C: primjer u neki drugi naziv ili mjesto datotekai pokrenite datoteku faktura.xls
Pojavit će Vam se dijalog prozor u kojem od Vas traži da ažurirate datoteku. Kliknite na gumb Ažuriraj.

Adsense sponzor



Slika 15.

Ako kreirate folder "primjer"  i premjestite datoteke u My Documents tada prilikom promjene mjesta datoteka, nakon otvaranja morate ažurirati (promijeniti) Path Stazu (link) do datoteke baza.xls

Kada otvorite datoteku faktura.xls kliknite "Ne Ažuriraj" a po otvaranju na tekstualnom izborniku na Uređivanje (Edit) => Veze (Link) i promijenite put do izvorne datoteke tj. Path stazu do nje. Potom ažurirajte datoteku. vidi sliku 16.

Slika 16.

Uređivanje veza u Excelu

Na slijedećem linku možete pogledati tutorijal Kako kreirati obrazac Fakture za izdavanje robe i automatizirati skidanje sa stanja skladišta


Pretraživanje vremena (sati i minuta) pomoću funkcije VLOOKUP

Ako imate potrebu pretraživati raspon podataka neke Excel tablice i želite pomoću VLOOKUP funkcije vratiti pripadajuću vrijednost traženom vremenu tada iskoristite kombinaciju formula poput ove situacije na slici ispod.
Funkcija IFERROR (u Excelu 2007) sprječava prikazivanje greške #N/A

Za Excel 2007 formula glasi
=IFERROR(VLOOKUP(TIME(HOUR(D1);MINUTE(D1););$A$1:$B$4;2;FALSE);"")
=> uključen raspon podataka
=IFERROR(VLOOKUP(TIME(HOUR(D1);MINUTE(D1););podaci;2;FALSE);"")    
 => uključen definirani naziv raspona podataka

Za Excel 2003 formula glasi:
=IF(ISERROR(VLOOKUP(TIME(HOUR(D1);MINUTE(D1););$A$1:$B$4;2;FALSE));"";VLOOKUP(TIME(HOUR(D1);MINUTE(D1););$A$1:$B$4;2;FALSE))

Adsense sponzor




Lookup Time with Vlookup function in Excel

Primjer VLOOKUP funkcije gdje podaci nisu sortirani

Ako imate različite podatke koje pretražujete a sastoje se od teksta i brojeva možete ih pretraživati a da ne sortirate raspon podataka koje pretražujete. U primjeru ispod uočite gornju tablicu u kojoj se nalaze podaci. U donjoj tablici u stupcu "B" upisujemo podatak (B18:B21) po želji koji funkcija Vlookup traži u gornjoj tablici i stupcu "B" i vraća kao rezultat vrijednosti pripadajućem pronađenom podatku u stupcima C18:C21, D18:D21 i E18:D21 .

Uočite da podaci u gornjoj tablici nisu sortirani. Uspješan rezultat funkcija Vlookup vraća zbog zadnjeg parametra FALSE.

VLOOKUP u nesortiranim podacima


PRIMJER VLOOKUP funkcije gdje koristimo dva Lista (Sheeta)

Podaci su na Listu1 a pretraživanje vršimo na Listu2. Poanta ovog primjera je pokazati kako se vrši pretraživanje Lista1 i podataka na njemu a raspon podataka nije imenovan.

- Na listu 1 u rasponu podataka u ovom primjeru A2:C3 imamo našu "bazu"
- Na Listu2 u stupcu A upisujemo šifre firmi (ovo je naš uvjet)
- Nakon upisane šifre automatski nam se pojavljuju podaci u stupcima B i C koji su relevantni šifri iz baze podataka na Listu1

Formula za ćelije u stupcu B => =VLOOKUP(A2;List1!$A$2:$C$3;2;FALSE)
Formula za ćelije u stupcu C => =VLOOKUP(A2;List1!$A$2:$C$3;3;FALSE)

U koliko Vam se pojavljuje ovakav znak u ćelijama u koje ste unijeli formulu Vlookup-a a u stupcu "A" nema podatka znači da Vlookup ne može izračunati i javlja se GREŠKA #N/A (#N/D). Za eventualno otklanjanje ovih grešaka pogledajte link za funkcije ISERROR ili IF ili ISNA

Zato je potrebno funkcijom IF prvo provjeriti ćeliju u stupcu "A", a potom pomoću Vlookup-a tražiti rezultat. Dakle u tom slučaju u ćeliju B2 (stupcu B) treba unijeti ovakvu formulu

=IF(A2<>"";VLOOKUP(A2;List1!$A$2:$C$4;2;FALSE);"")

a u ćeliju C2 (stupcu C) ovu formulu

=IF(A2<>"";VLOOKUP(A2;List1!$A$2:$C$4;3;FALSE);"")

Što radi ova formula. IF prvo provjerava je li ćelija A2 prazna tj. različita od "Prazno" a ako ima podatak u njoj tada će Vlookup biti rezultat a ako je ćelija prazna tada će i rezultat biti prazna ćelija. Dajte si truda i uvijek imenujte tj. definirajte ime ili naziv za raspon podataka ili ćeliju.



   

Adsense sponzor



Za povratak na baznu stranicu funkcije VLOOKUP kliknite na link u izborniku lijevo.
Također pogledajte kako možete kopirati funkciju Vlookup u desno. Dinamično kopiranje funkcije Vlookup u desno


Adsense sponzor



Ova web stranica koristi COOKIES - COPYRIGHT 2008 - 2020 - IvanC - ic.ims.hr