Dohvaćanje vrijednosti pomoću funkcija VLOOKUP, INDEX i MATCH

Važno :  Ovaj je članak strojno preveden. Pogledajteizjavu o odricanju od odgovornosti. Verziju ovog članka na engleskom potražite ovdje.

Pretpostavimo da imate popis s brojevima ureda i morate saznati koji se zaposlenici nalaze u kojem uredu. No proračunska je tablica golema i ne znate što učiniti. Iskoristite funkciju za dohvaćanje vrijednosti. Najkorisnije su funkcije VLOOKUP i HLOOKUP, no mogu poslužiti i funkcije INDEX te MATCH.

Napomena :  Ako pokušavate pronaći čarobnjak za dohvaćanje vrijednosti, ta značajka više nije dio programa Excel.

Evo kratkog podsjetnika na korištenje funkcije VLOOKUP.

=VLOOKUP(B2;C2:E7;3;TRUE)

Prvi argument – podatak potreban za funkcioniranje funkcije – predstavlja vrijednost koju tražite. To može biti referenca na ćeliju ili konkretna vrijednost, npr. "makovac" ili 21 000. Drugi je argument raspon ćelija koji vjerojatno sadrži traženu vrijednost. U ovom je primjeru to C2 – E7. Treći je argument stupac u tom rasponu ćelija u kojem se nalazi vrijednost koju želite vidjeti.

Četvrti argument nije obavezan. Možete unijeti True ili False. Ako unesete TRUE ili izostavite taj argument, funkcija će vratiti vrijednost koja je približna prvom argumentu. Ako unesete FALSE, funkcija će vratiti vrijednost koja je ista kao ona navedena u prvom argumentu. Drugim riječima, ako izostavite četvrti argument ili unesete TRUE, postići ćete veću fleksibilnost.

U ovom se primjeru prikazuje funkcioniranje funkcije. Kad u ćeliju B2 (prvi argument) unesete vrijednost, VLOOKUP pretražuje ćelije od C2 do E7 (drugi argument) te vraća najbliže približno podudaranje iz trećeg stupca u rasponu, stupca E (trećeg argumenta).

Tipična upotreba funkcije VLOOKUP

Četvrti je argument izostavljen pa funkcija vraća približno podudaranje. Da ga nismo izostavili, u stupce C ili D morali bismo unijeti neku od vrijednosti da bismo uopće dobili neki rezultat.

Kad se upoznate s funkcijom VLOOKUP, ni funkcija HLOOKUP ne bi vam smjela biti komplicirana. Unosite jednake argumente, ali funkcija pronalazi vrijednosti u recima, a ne u stupcima.

Isprobajte sami

Ako se prije primjene funkcija na vlastite podatke želite pozabaviti funkcijama za dohvaćanje podataka, evo oglednih podataka. Neki se korisnici služe funkcijama VLOOKUP i HLOOKUP dok je drugima draža kombinacija funkcija INDEX i MATCH. Isprobajte svaku metodu pa sami odlučite koja vam se najviše sviđa.

VLOOKUP u akciji

Kopirajte sve ćelije iz ove tablice i zalijepite ih u ćeliju A1 na praznom radnom listu programa Excel.

Savjet    Prije nego što zalijepite podatke u Excel, postavite širinu stupaca od A do C na 250 piksela pa kliknite Prelamanje teksta (grupa Poravnanje na kartici Polazno).

Gustoća

Viskoznost

Temperatura

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Formula

Opis

Rezultat

'=VLOOKUP(1;A2:C10;2)

Pomoću približnog podudaranja traži vrijednost 1 u stupcu A, pronalazi sljedeću najveću vrijednost manju od 1 ili jednaku 1 u stupcu A, koja je 0,946, a zatim vraća vrijednost iz stupca B u istom retku.

=VLOOKUP(1;A2:C10;2)

'=VLOOKUP(1;A2:C10;3;TRUE)

Pomoću približnog podudaranja traži vrijednost 1 u stupcu A, pronalazi sljedeću najveću vrijednost manju od 1 ili jednaku 1 u stupcu A, koja je 0,946, a zatim vraća vrijednost iz stupca C u istom retku.

=VLOOKUP(1;A2:C10;3;TRUE)

'=VLOOKUP(0,7;A2:C10;3;FALSE)

Pomoću točnog podudaranja traži vrijednosti 0,7 u stupcu A. Budući da u stupcu A ne postoji vrijednost točnog podudaranja, vraća se pogreška.

=VLOOKUP(0,7;A2:C10;3;FALSE)

'=VLOOKUP(0,1;A2:C10;2;TRUE)

Pomoću približnog podudaranja traži vrijednosti 0,1 u stupcu A. Budući da je 0,1 manje od najmanje vrijednosti u stupcu A, vraća se pogreška.

=VLOOKUP(0,1;A2:C10;2;TRUE)

'=VLOOKUP(2;A2:C10;2;TRUE)

Pomoću približnog podudaranja traži vrijednost 2 u stupcu A, pronalazi sljedeću najveću vrijednost manju od 2 ili jednaku 2 u stupcu A, koja je 1,29, a zatim vraća vrijednost iz stupca B u istom retku.

=VLOOKUP(2;A2:C10;2;TRUE)

HLOOKUP u akciji

Kopirajte sve ćelije iz ove tablice i zalijepite ih u ćeliju A1 na praznom radnom listu programa Excel.

Savjet    Prije nego što zalijepite podatke u Excel, postavite širinu stupaca od A do C na 250 piksela pa kliknite Prelamanje teksta (grupa Poravnanje na kartici Polazno).

Osovine

Ležajevi

Vijci

4

4

9

5

7

10

6

8

11

Formula

Opis

Rezultat

'=HLOOKUP("Osovine"; A1:C4; 2; TRUE)

Traži riječ "Osovine" u retku 1 i vraća vrijednost iz retka 2 koji se nalazi u istom stupcu (stupac A).

=HLOOKUP("Vijci";A1:C4;2;TRUE)

'=HLOOKUP("Ležajevi"; A1:C4; 3; FALSE)

Traži riječ "Ležajevi" u retku 1 i vraća vrijednost iz retka 3 koji se nalazi u istom stupcu (stupac B).

=HLOOKUP("Ležajevi";A1:C4;3;FALSE)

'=HLOOKUP("B"; A1:C4; 3; TRUE)

Traži "B" u retku 1 i vraća vrijednost iz retka 3 koji se nalazi u istom stupcu. S obzirom na to da ne postoji "B", koristi se najveća vrijednost u retku 1 koja je manja od "B": "Osovine" u stupcu A.

=HLOOKUP("B";A1:C4;3;TRUE)

'=HLOOKUP("Vijci"; A1:C4; 4)

Traži riječ "Vijci" u retku 1 i vraća vrijednost iz retka 4 koji se nalazi u istom stupcu (stupac C).

=HLOOKUP("Vijci";A1:C4;4)

'=HLOOKUP(3;{1;2;3|"a";"b";"c"|"d";"e";"f"}; 2; TRUE)

Traži broj 3 u konstanti polja s tri retka i vraća vrijednost iz retka 2 u istom (u ovom slučaju, trećem) stupcu. U konstanti polja postoje tri retka vrijednosti, svaki redak odijeljen je ravnom crtom (|). S obzirom na to da se "c" nalazi u retku 2 i u istom stupcu kao i 3, vraća se "c".

=HLOOKUP(3;{1;2;3|"a";"b";"c"|"d";"e";"f"}; 2; TRUE)

INDEX i MATCH na djelu

U ovom se primjeru koristi kombinacija funkcija INDEX i MATCH radi dohvaćanja najstarijeg broja fakture i odgovarajućeg datuma za svaki od pet gradova. Budući da se datum vraća kao broj, kao datum ga oblikujemo pomoću funkcije TEXT. U funkciji INDEX zapravo se kao argument koristi rezultat funkcije MATCH. U svakoj se formuli dvaput koristi kombinacija funkcija INDEX i MATCH – najprije za dohvaćanje broja fakture, a zatim za dohvaćanje datuma.

Kopirajte sve ćelije iz ove tablice i zalijepite ih u ćeliju A1 na praznom radnom listu programa Excel.

Savjet    Prije nego što zalijepite podatke u Excel, postavite širinu stupaca od A do D na 250 piksela pa kliknite Prelamanje teksta (grupa Poravnanje na kartici Polazno).

Faktura

Grad

Datum fakture

Najstarija faktura po gradu uz datum

3115

Osijek

07.04.12.

="Osijek= "&INDEX($A$2:$C$33;MATCH("Osijek";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Osijek";$B$2:$B$33;0);3);"d. m. gg.")

3137

Osijek

09.04.12.

="Rijeka = "&INDEX($A$2:$C$33;MATCH("Rijeka";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Rijeka";$B$2:$B$33;0);3);"d. m. gg.")

3154

Osijek

11.04.12.

="Šibenik = "&INDEX($A$2:$C$33;MATCH("Šibenik";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Šibenik";$B$2:$B$33;0);3);"d. m. gg.")

3191

Osijek

21.04.12.

="Dubrovnik = "&INDEX($A$2:$C$33;MATCH("Dubrovnik";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Dubrovnik";$B$2:$B$33;0);3);"d. m. gg.")

3293

Osijek

25.04.12.

="Zagreb = "&INDEX($A$2:$C$33;MATCH("Zagreb";$B$2:$B$33;0);1)& ", datum fakture: " & TEXT(INDEX($A$2:$C$33;MATCH("Zagreb";$B$2:$B$33;0);3);"d. m. gg.")

3331

Osijek

27.04.12.

3350

Osijek

28.04.12.

3390

Osijek

01.05.12.

3441

Osijek

02.05.12.

3517

Osijek

08.05.12.

3124

Rijeka

09.04.12.

3155

Rijeka

11.04.12.

3177

Rijeka

19.04.12.

3357

Rijeka

28.04.12.

3492

Rijeka

06.05.12.

3316

Šibenik

25.04.12.

3346

Šibenik

28.04.12.

3372

Šibenik

01.05.12.

3414

Šibenik

01.05.12.

3451

Šibenik

02.05.12.

3467

Šibenik

02.05.12.

3474

Šibenik

04.05.12.

3490

Šibenik

05.05.12.

3503

Šibenik

08.05.12.

3151

Dubrovnik

09.04.12.

3438

Dubrovnik

02.05.12.

3471

Dubrovnik

04.05.12.

3160

Zagreb

18.04.12.

3328

Zagreb

26.04.12.

3368

Zagreb

29.04.12.

3420

Zagreb

01.05.12.

3501

Zagreb

06.05.12.

Dodatne informacije o funkcijama za dohvaćanje podataka

Vrh stranice

Napomena : Izjava o odricanju od odgovornosti za strojni prijevod: ovaj je članak preveo računalni sustav bez ljudske intervencije. Microsoft nudi strojne prijevode da bi korisnicima koji ne razumiju engleski omogućio čitanje sadržaja o Microsoftovim proizvodima, uslugama i tehnologijama. Budući da je preveden strojno, članak možda sadrži pogreške u vokabularu, sintaksi ili gramatici.

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×