Savjet: Pokušajte koristiti nove funkcije XLOOKUP i XMATCH , poboljšane verzije funkcija opisanih u ovom članku. Te nove funkcije po zadanom funkcioniraju u bilo kojem smjeru i vraćaju točne podudaranja, što ih olakšava i praktičnije koristi od prethodnika.
Pretpostavimo da imate popis brojeva lokacija ureda i da morate znati koji se zaposlenici nalaze u svakom uredu. Proračunska tablica je ogromna, pa možda mislite da je to izazovan zadatak. To je zapravo vrlo lako učiniti s pretraživanjem funkcije.
Funkcije VLOOKUP i HLOOKUP , zajedno s funkcijama INDEX i MATCH, neke su od najkorisnijih funkcija u programu Excel.
Napomena: Značajka čarobnjaka za traženje vrijednosti više nije dostupna u programu Excel.
Evo primjera korištenja funkcije VLOOKUP.
=VLOOKUP(B2;C2:E7;3;TRUE)
U ovom je primjeru B2 prvi argument – element podataka koji funkcija mora funkcionirati. U funkciji VLOOKUP taj je prvi argument vrijednost koju želite pronaći. Taj argument može biti referenca ćelije ili fiksna vrijednost kao što je "smith" ili 21 000. Drugi je argument raspon ćelija C2-:E7 u kojem se traži vrijednost koju želite pronaći. Treći je argument stupac u tom rasponu ćelija koji sadrži vrijednost koju tražite.
Četvrti argument nije obavezan. Unesite 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 se podudarati s vrijednošću koju navedite prvim argumentom. Drugim riječima, ostavljanje četvrtog argumenta praznim ili unosom TRUE daje vam veću fleksibilnost.
U ovom se primjeru prikazuje funkcioniranje funkcije. Kada unesete vrijednost u ćeliju B2 (prvi argument), VLOOKUP pretražuje ćelije u rasponu C2:E7 (drugi argument) i vraća najbliže približno podudaranje iz trećeg stupca u rasponu, stupcu E (3. argumentu).
Četvrti je argument prazan, pa funkcija vraća približnu vrijednost. Da ga nismo izostavili, u stupce C ili D morali bismo unijeti neku od vrijednosti da bismo uopće dobili neki rezultat.
Kada ste udobni s funkcijom VLOOKUP, funkcija HLOOKUP jednako je jednostavna za korištenje. Unosite iste argumente, ali pretražuje retke umjesto u stupce.
Korištenje funkcija INDEX i MATCH umjesto funkcije VLOOKUP
Postoje određena ograničenja korištenja funkcije VLOOKUP – funkcija VLOOKUP može tražiti samo vrijednost slijeva nadesno. To znači da se stupac koji sadrži vrijednost koju ste potražili uvijek trebao nalaziti s lijeve strane stupca koji sadrži povratnu vrijednost. Sada, ako proračunska tablica nije ugrađena na taj način, nemojte koristiti VLOOKUP. Umjesto toga koristite kombinaciju funkcija INDEX i MATCH.
U ovom se primjeru prikazuje mali popis na kojem se vrijednost koju želimo pretražiti, Chicago, ne nalazi u krajnjem lijevom stupcu. Stoga ne možemo koristiti VLOOKUP. Umjesto toga, upotrijebit ćemo funkciju MATCH da bismo pronašli Chicago u rasponu B1:B11. Pronađeno je u retku 4. Zatim INDEX tu vrijednost koristi kao argument pretraživanja i pronalazi populaciju za Chicago u 4. stupcu (stupac D). Korištena formula prikazana je u ćeliji A14.
Dodatne primjere korištenja funkcija INDEX i MATCH umjesto funkcije VLOOKUP potražite u članku https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Bill Jelen, Microsoftov MVP.
Isprobajte sami
Ako želite eksperimentirati s funkcijama pretraživanja prije nego što ih isprobate s vlastitim podacima, evo nekih oglednih podataka.
Vlookup Primjer na poslu
Kopirajte sljedeće podatke u praznu proračunsku tablicu.
Savjet: Prije lijepljenja podataka u Excel postavite širine stupaca od A do C na 250 piksela, a zatim kliknite Prelamanje teksta (kartica Polazno, 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/A |
|
=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/A |
|
=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 programa HLOOKUP
Kopirajte sve ćelije iz ove tablice i zalijepite ih u ćeliju A1 na praznom radnom listu programa Excel.
Savjet: Prije lijepljenja podataka u Excel postavite širine stupaca od A do C na 250 piksela, a zatim kliknite Prelamanje teksta (kartica Polazno, 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 |
Primjeri funkcije INDEX i MATCH
U ovom se zadnjem primjeru zajedno koriste funkcije INDEX i MATCH da bi se vratio najstariji broj fakture i odgovarajući datum za svaki od pet gradova. Budući da se datum vraća kao broj, pomoću funkcije TEXT oblikujemo ga kao datum. 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 lijepljenja podataka u Excel postavite širine stupaca od A do D na 250 piksela, a zatim kliknite Prelamanje teksta (kartica Polazno, 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. |
