logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali - VLOOKUP funkcija u Excelu 2013, Sintaksa funkcije Vlookup, Primjeri upotrebe Vlookup funkcije

VLOOKUP funkcija u Excelu 2013




sponzor



Za pretraživanje raspona ćelija ili tablice baze podataka Excel sadrži nekoliko funkcija

Ja ću ovdje prikazati neke od njih a ostale funkcije pretraživanje u Excelu možete vidjeti na linku

  • LOOKUP - (range, row)
    - vraća rezultat iz prvog desnog stupca u istom redu gdje se nalazi uvjet ili traženi podatak, uobičajeno se koriste dva stupca jedan do drugoga,
    =LOOKUP(lookup_value;array)
  • HLOOKUP - (header row, row)
    - Horizontalno pretraživanje, koristi redove za pretraživanje, podataka u naslovnom ili prvom redu raspona ćelija je uvjet a vraća podatak iz broja reda koji postavimo kao argument, raspon podataka uključuje naslovni redak (Header)
    =HLOOKUP(lookup_value;table_array;row_index_num;range_lookup)
  • VLOOKUP (column1, column?)
    - Vertikalno pretraživanje, koristi prvi stupac za pretraživanje a vraća podatak iz stupca s desne strane i istog reda u kojem pronađe uvjet. Traženi podatak ili uvjet nalazi se uvijek u prvom stupcu, sintaksta za Vlookup je,
    =VLOOKUP(lookup_value;table_array;col_index_num;range_lookup
    )

Osnove Microsoft Excel VLOOKUP funkcije - Sintaksa Vlookup funkcije

Slovo 'V' u nazivu Excel funkcije VLOOKUP čitajte kao "okomito" (Vertical).

Sintaksa funkcije =VLOOKUP(prvi argument;drugi argument;treći argument;četvrti argument)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP(traženi-podatak;raspon-podataka-u-kojem-tražimo;broj-stupca-iz-kojeg-vraćamo-rezultat;identična-ili-približna-vrijednost)

Funkciju Vlookup koristimo kada u rasponu podataka želimo kao rezultat vratiti podatak koji se nalazi u istom redu desno od uvjeta (lookup_value). Vlookup uspoređuje uvjet (lookup_value) i traži ga u prvom stupcu raspona podataka, a vraća rezultate iz drugog, trećeg... stupca. Dakle poželjno je da je uvjet (lookup_value) jedinstven podatak bez ponavljanja (distinct data). U koliko naiđe na više istih podataka Vlookup vraća prvi po redoslijedu iz sprvog stupca pretraživanja , tako da je ponekada potrebno koristiti sortiranje ako koristite ovu funkciju.

Lookup_value 
Lookup_value je argument tj. podatak koji se pretražuje (uvjet ili kriterij) u prvom stupcu raspona podataka ili tablice. Lookup_value može biti vrijednost ili referenca. Ako je lookup_value manja od najmanje vrijednosti u prvom stupcu table_array, VLOOKUP vraća pogrešku #N/D.

Table_array 
Table_array argument funkcije Vlookup je raspon podataka ili tablica u kojoj tražimo zadani uvjet ili kriterij. Ovaj raspon podataka može se sastojati od dva ili više stupaca podataka a broj redova je nebitan. Podatak koji tražimo (uvjet ili kriterij - lookup_value) uvijek se nalazi u prvom stupcu raspona podataka (table_array). Uvjet ili kriterij može biti tekstualni, brojčani/brojevni ili logički podatak. Ne razlikuju se velika i mala slova.

Col_index_num 
Argument Col_index_num je redni broj stupca u rasponu podataka ili tablici koju pretražujemo (table_array) a iz kojeg se mora vratiti pronađena vrijednost. Ako za Col_index_num postavite broj 1 tada funkcija Vlookup vraća podatak ili vrijednost iz prvog stupca raspona podataka (table_array), ako za Col_index_num postavite broj 2 tada funkcija Vlookup vraća podatak ili vrijednost iz drugog stupca raspona podataka ( table_array) i tako dalje.

Ako je argument col_index_num manji od 1, VLOOKUP vraća pogrešku #VRIJ! (#VALUE!).
Ako je argument col_index_num veći od broja stupaca u table_array, VLOOKUP vraća pogrešku #REF!.

Range_lookup 
Logička vrijednost koja određuje želite li da VLOOKUP pronađe identičnu ili približnu vrijednost.

Ako u formuli Vloookup-a na kraju upište (za range_lookup):
- TRUE => tada podaci u prvom stupcu moraju biti sortirani uzlazno (ascending)
- FALSE => tada podaci u prvom stupcu ne moraju biti sortirani

Ako upišete TRUE ili izostavite ovaj četvrti po redu argument,
VLOOKUP vraća identičnu ili približnu vrijednost. Ako nije pronađena identična vrijednost, vraća se sljedeća najveća vrijednost koja je manja od lookup_value.

Ako koristite TRUE kao četvrti argument funkcije Vlookup, Vrijednosti/podaci u prvom stupcu table_array moraju biti poredane uzlaznim redoslijedom. U suprotnom VLOOKUP možda neće dati ispravnu vrijednost. Da biste poredali vrijednosti uzlaznim redoslijedom koristite Sortiranje u Excelu

Ako upišete FALSE kao četvrti argument,
VLOOKUP će pronaći točno identičnu vrijednost ili podatak tj. uvjet ili kriterij. U tom slučaju nije potrebno sortirati vrijednosti u prvom stupcu table_array. Ako u prvom stupcu table_array postoje dvije ili više vrijednosti koje odgovaraju lookup_value, Vlookup kao rezultat vraća prvu pronađenu vrijednost ili podata. Ako nije pronađena identična vrijednost, vraća se pogreška #N/D (#N/A!).

Apsolutne i relativne adrese u funkciji VLOOKUP

Kada koristite funkciju VLOOKUP samo u jednoj ćeliji i nemate namjeru kopirati je dovoljno je pisati relativne adrese za argument table_array. U slučaju da želite KOPIRATI kreiranu formulu koja sadrži funkciju Vlookup tada obavezno za drugi po redu argument table_array koristite Apsolutne Adrese raspona podataka.

Moj savjet je da uvijek imenujete raspone ćelija ili raspon podataka koje pretražujete. Kada imenujete raspon ćelija u Excelu lakše će vam biti kreirati formulu sa funkcijama a osobito sa Vlookup funkcijom.

Osnove korištenja VLOOKUP funkcije

Ova jednostavna formula na slici ispod koja koristi funkciju Vlookup prikazuje osnove upotrebe Vlookup funkcije. Uočite raspon podataka kao i redove i stupce. Redni broj stupca je jedan od argumenata Vlookup funkcije pa obratite na raspon podataka koji koristite upotrebom Vlookup funkcije. Ja ću prikazati nekoliko primjera sa različitim rasponom podataka zbog toga jer želim da shvatite (kao početnik) kako upotrebljavati redni broj stupca kao treći argument i obratiti pažnju na raspone podataka ili ćelija.

Primjer 1 - Vlookup funkcija

U ovom primjeru na slici ispod uočite kako koristiti argumente Vlookup funkcije u Excelu. Na slici uočite koji je raspon podataka ili ćelija koje pretražujemo, stupac u kojem se nalazi uvjet koji tražimo i stupac iz kojeg Vlookup treba vratiti rezultat iz istog reda ali stupca koji smo odredili upisom broja kao treći argument. Kada koristimo Vlookup funkciju u raspon podataka ne uključujemo naslovni redak već samo podatke. Dakle Vlookup funkcija vraća rezultat sa desne strane iz rednog broja stupca raspona podataka koji smo postavili. Umjesto ovog broja možemo koristit i funkciju COLUMN() koju možemo ugnijezditi unutar Vlookup funkcije ali o tome ću kasnije u nekom drugom tutorijalu.
Također uočite na slici da sam kreirao formulu sa funkcijom Vlookup koja sadrži relativne i apsolutne adrese raspona ćelija tablice baze podataka kao i redne brojeve stupaca u rasponu podataka.

VLOOKUP funkcija u Excelu

Dakle formula za ovaj primjer može biti u slijedećem obliku

=VLOOKUP(I2;B2:G9;3;FALSE)
=VLOOKUP(I2;$B$2:$G$9;
3;FALSE) => obavezno kada kopirate formulu
=VLOOKUP(I2;baza;
3;FALSE) => poželjno/obavezno kada kopirate formulu
=VLOOKUP($I$2;$B$2:$G$9;COLUMN(C1);FALSE) => Zato što je stupac 'C' treći po redu (3)
=VLOOKUP($I$2;$B$2:$G$9;COLUMN(D1)-1;FALSE) => Zato što je stupac 'D' četvrti po redu (4-1=3)

Uočite formulu iznad sa imenovanim rasponom ćelija (baza). Također uočite da umjesto trećeg argumenta tj. broja koji određuje redni broj stupca raspona podataka možemo koristiti funkciju COLUMN() kako sam rekao na početku tutorijala. Funkciju COLUMN() je poželjno koristiti kada imamo potrebu kopirati formulu u desnu stranu i vratiti podatke iz više stupaca.

Za usporedbu ovaj problem možemo riješiti i alternativnim formulama

Koristeći ugniježđenu IF funkciju

=IF(I2=101;"crvena";IF(I2=201;"zelena";IF(I2=301;"žuta";IF(I2=202;"crvena"; IF(I2=102;"žuta";IF(I2=203;"žuta"
;IF(I2=103;"zelena";IF(I2=302;"crvena";""))))))))
=> (formula mora biti u jednoj liniji reda)

Koristeći INDEX - MATCH funkcije

=INDEX(D2:D9;MATCH(I2;B2:B9;0))

Kada ne trebamo koristiti funkciju VLOOKUP

Excel funkciju VLOOKUP koristimo kada pretražujemo raspone podataka i želimo vratiti podatak za uvjet koji je jedinstven (unique). Dakle na slici iznad ne bi mogli koristiti Vlookup funkciju kada bi postavili uvjet "mandarina" ili "crvena" jer nije jedinstven bez obzira što bi postavili raspon podataka da prvi stupac bude onaj u u kojem se nalazi traženi podatak ili uvjet. U ovom slučaju Vlookup funkcija bi nam kao rezultat vratila prvi podatak koji pronađe jer ne može odrediti koji podatak tražimo zato što ima više istih podataka (uvjeta).

Primjer 2 - Vlookup funkcija

U ovom primjeru na slici ispod želim pokazati kako obratiti pažnju na redni broj stupca raspona podataka koji pretražujemo. Na početku tutorijala sam rekao da VLOOKUP funkcija radi sa stupcima, stoga je to važno shvatiti.

U slici ispod uočite zadatak koji trebamo riješiti. Mi trebamo pretražiti bazu podataka koja je u ovom slučaju tablica raspona podataka 'C2:F5' i za postavljeni uvjet tvrtke 'C' pronaći rezultat koji se nalazi u 'Final' stupcu a to je četvrti stupac po redu.

Vlookup funkcija u Excelu primjer

Formula je slijedeća:

=VLOOKUP(H2;C2:F5;4;FALSE)
=VLOOKUP(H2;$C$2:$F$5;4;FALSE)
=VLOOKUP(H2;company;4;FALSE)
=VLOOKUP($H$2;$C$2:$F$5;COLUMN(D1);FALSE)
=> Zato što je stupac 'D' četvrti po redu (4)
=VLOOKUP($H$2;$C$2:$F$5;COLUMN(F1)-2;FALSE) => Zato što je stupac 'F' šesti po redu (6-2=4)

sponzor


Primjer 3 - Vlookup funkcija traži podatak u prvom stupcu i vraća rezultat iz više stupaca

U slijedećem primjeru uočite da se tablica raspona podataka nalazi u drugačijem rasponu ćelija. U ovom slučaju korištenje funkcije VLOOKUP u kombinaciji sa funkcijom COLUMN je poželjno pogotovo ako moramo kopirati formulu u desnu stranu. Na slici ispod uočite da u istom momentu poslije upisa uvjeta (Condition) Excel automatski vraća rezultate koje smo postavili u stupcima K i L.

Kombinacija COLUMN funkcije sa VLOOKUP funkcijom u Excelu

Formule su slijedeće:

Formula u ćeliji K2: =VLOOKUP($J$2;$E$2:$H$5;3;FALSE)
=VLOOKUP($J$2;$E$2:$H$5;COLUMN()-8;FALSE)
=> Trebamo za uvjet vratiti rezultat iz trećeg stupca raspona podataka (3), s obzirom da je stupac 'K' u kojem se nalazi formula jedanaesti po redu (11-8=3)

Formula u ćeliji L2: =VLOOKUP($J$2;$E$2:$H$5;4;FALSE)
=VLOOKUP($J$2;$E$2:$H$5;COLUMN()-8;FALSE)
=> Trebamo za uvjet vratiti rezultat iz četvrtog stupca raspona podataka (4), s obzirom da je stupac 'L' u kojem se nalazi formula dvanaesti po redu (12-8=4)

Formula u ćeliji K4: =VLOOKUP($J$2;$E$2:$H$5;COLUMN(C1);FALSE)
=> Trebamo za uvjet vratiti rezultat iz trećeg stupca raspona podataka (3), s obzirom da je stupac 'C' treći po redu (3)

Formula u ćeliji L4: =VLOOKUP($J$2;$E$2:$H$5;COLUMN(D1);FALSE)
=> Trebamo za uvjet vratiti rezultat iz četvrtog stupca raspona podataka (4), s obzirom da je stupac 'D' četvrti po redu (4)

U koliko želite pogledajte tutorijal Kopiranje formule VLOOKUP u desnu stranu




     

sponzor



COPYRIGHT © - 2005 - 2019 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela