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

Napomena:  Željeli bismo vam pružiti najnoviji sadržaj pomoći što je brže moguće i to na vašem jeziku. Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Možete li nam pri dnu ove stranice javiti jesu li vam ove informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku .

Pretpostavimo da ste na popisu brojeva mjesto za office i morate znati koji su zaposlenici u svakom sustava office. Proračunska tablica označena je vrlo veliko, tako što mislite ga je izazov zadatka. Zapravo prilično jednostavno je s funkcija lookup.

Funkcija VLOOKUP i HLOOKUP , zajedno s INDEX i MATCH,su neke od najkorisnijih funkcija u programu Excel.

Napomena: Čarobnjak za traženje značajka više nije dostupan u programu Excel.

Evo primjera kako koristiti funkciju VLOOKUP.

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

U ovom se primjeru B2 je prvi argument– element funkciju potrebne za rad podatke. Za VLOOKUP, prvi argument je vrijednost koju želite pronaći. Ovaj argument može biti referenca na ćeliju ili Fiksna vrijednost kao što su "kopač" ili 21.000. Drugi je argument raspon ćelija, C2-:E7, u kojoj se traži vrijednost koju želite pronaći. Treći je argument stupac u tom raspon ćelija koji sadrži vrijednost koju ste rješenja.

Četvrtog argumenta nije obavezno. Unesite TRUE ili FALSE. Ako unesite TRUE ili je argument ostavite prazno, funkcija vraća približna vrijednost koju navedete u prvom argumentu. Ako unesete FALSE, funkcija odgovara vrijednosti navedite tako da je prvi argument. Drugim riječima, ostavite praznima četvrtog argumenta – ili TRUE unosa – daje veću fleksibilnost.

Ovaj primjer pokazuje kako funkcionira funkcija. Kada unesete vrijednost u ćeliji B2 (prvi argument), VLOOKUP pretražuje ćelije u rasponu C2:E7 (2 argument) i vraća najbliže djelomičnog podudaranja iz trećeg stupca u rasponu, stupac E (3 argument).

Tipična upotreba funkcije VLOOKUP

Četvrtog argumenta je prazan, pa funkcija vraća djelomičnog podudaranja. Ako niste, morate želite unijeti jednu od vrijednosti u stupcima C i D da biste dobili rezultat uopće.

Ako ste upoznati s funkcijom VLOOKUP, HLOOKUP (funkcija) je jednako jednostavno je za korištenje. Unesite iste argumente, ali pretražuje u recima umjesto stupaca. "

Isprobajte sami

Ako želite isprobati funkcija lookup prije nego ih isprobate na vlastitim podacima, Evo oglednih podataka. Neki korisnici programa Excel kao što su pomoću funkcija VLOOKUP i HLOOKUP; drugi radije kombinacija funkcija INDEX i MATCH. Svaki način i koje korisnik koji vam se najviše sviđa.

POTVRDNI okvir Ispis u akciji

Kopirajte sljedeće podatke u praznu proračunsku tablicu...

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

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.

2,17

=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.

100

=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.

#N/D

=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.

#N/D

=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.

1,71

Primjer HLOOKUP

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 (karticaPočetna stranica , grupa Poravnanje ).

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).

4

=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).

7

=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.

5

=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).

11

=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".

c

INDEX i MATCH Primjeri

U ovom se primjeru posljednje uključuje funkcije INDEX i MATCH zajedno da biste se vratili najraniji broj računa i njegove odgovarajuće rok za svaki od pet gradova. Budući da se kao broj, vraća se datum, koristimo funkcije TEXT oblikovati kao datum. Kao argument funkcije INDEX zapravo koristi rezultat funkcije MATCH. Kombinacije funkcija INDEX i MATCH koriste dvaput u svakoj formuli – prvi put, da biste se vratili broj računa, a zatim da biste se vratili na datum.

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 (karticaPočetna stranica , grupa Poravnanje ).

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

Proširite svoje vještine korištenja sustava Office
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.

×