Savet: Pokušajte da koristite nove XLOOKUP i XMATCH funkcije, poboljšane verzije funkcija opisanih u ovom članku. Ove nove funkcije podrazumevano rade u bilo kom smeru i vraćaju tačna podudaranja, što ih čini lakšim i praktičnijim za korišćenje od prethodnika.
Pretpostavimo da imate listu brojeva lokacija kancelarije i treba da znate koji zaposleni se nalaze u svakoj kancelariji. Unakrsna tabela je ogromna, pa možete da mislite da je to izazovan zadatak. To je zapravo prilično lako uraditi sa funkcijom pronalaženja.
VLOOKUP i HLOOKUP, zajedno sa funkcijama INDEX i MATCH, su neke od najkorisnijih funkcija u programu Excel.
Napomena: Funkcija čarobnjaka za pronalaženje više nije dostupna u programu Excel.
Evo primera korišćenja funkcije VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TAČNO)
U ovom primeru, B2 je prvi argument – element podataka koji je potreban funkciji da bi funkcionisala. Za funkciju VLOOKUP, ovaj prvi argument je vrednost koju želite da pronađete. Ovaj argument može biti referenca na ćeliju ili fiksna vrednost kao što je "simić" ili 21.000. Drugi argument je opseg ćelija C2-:E7 u kojem se traži vrednost koju želite da pronađete. Treći argument je kolona u tom opsegu ćelija koja sadrži vrednost koju tražite.
Četvrti argument je opcionalan. Unesite TRUE ili FALSE. Ako unesete vrednost „TAČNO“ ili ostavite argument prazan, funkcija vraća vrednost koja se približno poklapa sa vrednošću koju ste naveli u prvom argumentu. Ako unesete vrednost FALSE, funkcija će se podudarati sa vrednošću navedenom u prvom argumentu. Drugim rečima, ostavljanje četvrtog argumenta praznog – ili unosa TRUE – daje vam veću fleksibilnost.
Ovaj primer prikazuje kako funkcija radi. Kada unesete vrednost u ćeliju B2 (prvi argument), funkcija VLOOKUP pretražuje ćelije u opsegu C2:E7 (2. argument) i vraća najbliže približno podudaranje iz treće kolone u opsegu, kolone E (treći argument).
Četvrti argument je prazan, tako da funkcija vraća približno podudaranje. U suprotnom, morali biste da unesete neku od vrednosti u kolonama C ili D da biste uopšte dobili rezultat.
Kada se dobro osećate uz funkciju VLOOKUP, podjednako je laka za korišćenje funkcije HLOOKUP. Unose se isti argumenti, ali se pretražuju u redovima umesto u kolonama.
Korišćenje funkcija INDEX i MATCH umesto funkcije VLOOKUP
Postoje određena ograničenja korišćenja funkcije VLOOKUP – funkcija VLOOKUP može samo da potraži vrednost sleva nadesno. To znači da kolona koja sadrži vrednost koju tražite uvek treba da se nalazi sa leve strane kolone koja sadrži povratnu vrednost. Ako unakrsna tabela nije izgrađena na ovaj način, nemojte koristiti funkciju VLOOKUP. Umesto toga koristite kombinaciju funkcija INDEX i MATCH.
Ovaj primer pokazuje malu listu na kojoj se vrednost po kojoj želimo da pretražimo Čačak nalazi u krajnje levoj koloni. Dakle, ne možemo da koristimo VLOOKUP. Umesto toga koristimo funkciju MATCH da pronađemo Čikago u opsegu B1:B11. Naрemo ga u redu 4. Zatim funkcija INDEX koristi tu vrednost kao argument za pronalaženje i pronalazi populaciju za Čikago u 4. koloni (koloni D). Upotrebljena formula je prikazana u ćeliji A14.
Za više primera korišćenja funkcija INDEX i MATCH umesto funkcije VLOOKUP, pogledajte članak https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ je Bil Jelen, Microsoft MVP.
Pokušaj
Ako želite da eksperimentišete sa funkcijama za pronalaženje pre nego što ih isprobate sa sopstvenim podacima, evo nekih uzoraka podataka.
VLOOKUP Primer na poslu
Kopirajte sledeće podatke u praznu unakrsnu tabelu.
Savet: Pre nego što nalepite podatke u Excel, podesite širinu kolona za kolone od A do C na 250 piksela i izaberite stavku Prelomi tekst (karticaPočetak, grupa Poravnavanje).
|
Gustina |
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 vrednost 1 u koloni A, pronalazi najveću vrednost manju ili jednaku 1 u koloni A koja je 0,946, a zatim vraća vrednost iz kolone B u isti red. |
2,17 |
|
=VLOOKUP(1,A2:C10,3,TAČNO) |
Pomoću približnog podudaranja traži vrednost 1 u koloni A, pronalazi najveću vrednost manju ili jednaku 1 u koloni A koja je 0.946, a zatim vraća vrednost iz kolone C u isti red. |
100 |
|
=VLOOKUP(0.7,A2:C10,3,NETAČNO) |
Pomoću potpunog podudaranja pretražuje vrednost 0.7 u koloni A. Pošto ne postoji potpuno podudaranje u koloni A, dobija se greška. |
#N/A |
|
=VLOOKUP(0.1,A2:C10,2,TAČNO) |
Pomoću približnog podudaranja pretražuje vrednost 0.1 u koloni A. Pošto je vrednost 0,1 manja od najmanje vrednosti u koloni A, dobija se greška. |
#N/A |
|
=VLOOKUP(2,A2:C10,2,TAČNO) |
Pomoću približnog podudaranja traži vrednost 2 u koloni A, pronalazi najveću vrednost manju ili jednaku 2 u koloni A koja je 1,29, a zatim vraća vrednost iz kolone B u isti red. |
1,71 |
HLOOKUP primer
Kopirajte sve ćelije iz ove tabele i nalepite ih u ćeliju A1 u praznoj radnoj svesci programa Excel.
Savet: Pre nego što nalepite podatke u Excel, podesite širinu kolona za kolone od A do C na 250 piksela i izaberite stavku Prelomi tekst (karticaPočetak, grupa Poravnavanje).
|
Osovine |
Ležajevi |
Zavrtnji |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Formula |
Opis |
Rezultat |
|
=HLOOKUP("Osovine", A1:C4, 2, TRUE) |
Traži „Osovine“ u redu 1 i daje vrednost iz reda 2 koja je u istoj koloni (koloni A). |
4 |
|
=HLOOKUP("Ležajevi", A1:C4, 3, FALSE) |
Traži „Ležajevi“ u redu 1 i daje vrednost iz reda 3 koja je u istoj koloni (koloni B).. |
7 |
|
=HLOOKUP("B", A1:C4, 3, TRUE) |
Traži „B“ u redu 1 i daje vrednost iz reda 3 koja je u istoj koloni. Pošto nije pronađena tražena podudarnost, koristi se najveća vrednost u redu 1 koja je manja od „B“: „Osovine“, u koloni A. |
5 |
|
=HLOOKUP("Bolts", A1:C4, 4) |
Traži „Zavrtnji“ u redu 1 i daje vrednost iz reda 4 koja je u istoj koloni (koloni C). |
11 |
|
=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) |
Traži broj 3 u konstanti niza koja sadrži tri reda i daje vrednost iz reda 2 u istoj (u ovom slučaju trećoj) koloni. U konstanti niza nalaze se tri reda vrednosti, a svaki red odvojen je tačkom i zarezom (;). Pošto je „c“ pronađeno u redu 2 i u istoj koloni kao i 3, dobija se „c“. |
c |
Primeri INDEX i MATCH
Ovaj poslednji primer zajedno koristi funkcije INDEX i MATCH kako bi se vratio najraniji broj fakture i odgovarajući datum za svaki od pet gradova. Pošto se datum vraća kao broj, koristimo funkciju TEXT da bismo ga oblikovali kao datum. Funkcija INDEX zapravo koristi rezultat funkcije MATCH kao argument. Kombinacija funkcija INDEX i MATCH se dvaput koristi u svakoj formuli – prvi put da bi se dobio broj fakture, a drugi put da bi se dobio datum.
Kopirajte sve ćelije iz ove tabele i nalepite ih u ćeliju A1 u praznoj radnoj svesci programa Excel.
Savet: Pre nego što nalepite podatke u Excel, podesite širinu kolona za kolone od A do D na 250 piksela i izaberite stavku Prelomi tekst (karticaPočetak, grupa Poravnavanje).
|
Faktura |
Grad |
Datum fakture |
Najranija faktura po gradovima sa datumom |
|
3115 |
Aranđelovac |
07.04.12. |
="Aranđelovac = "&INDEX($A$2:$C$33,MATCH("Aranđelovac",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Aranđelovac",$B$2:$B$33,0),3),"m/d/yy") |
|
3137 |
Aranđelovac |
09.04.12. |
="Apatin = "&INDEX($A$2:$C$33,MATCH("Apatin",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Apatin",$B$2:$B$33,0),3),"m/d/yy") |
|
3154 |
Aranđelovac |
11.04.12. |
="Beograd = "&INDEX($A$2:$C$33,MATCH("Beograd",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Beograd",$B$2:$B$33,0),3),"m/d/yy") |
|
3191 |
Aranđelovac |
21.04.12. |
="Novi Sad = "&INDEX($A$2:$C$33,MATCH("Novi Sad",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Novi Sad",$B$2:$B$33,0),3),"m/d/yy") |
|
3293 |
Aranđelovac |
25.04.12. |
="Valjevo = "&INDEX($A$2:$C$33,MATCH("Valjevo",$B$2:$B$33,0),1)& ", Datum fakture: " & TEXT(INDEX($A$2:$C$33,MATCH("Valjevo",$B$2:$B$33,0),3),"m/d/yy") |
|
3331 |
Aranđelovac |
27.04.12. |
|
|
3350 |
Aranđelovac |
28.04.12. |
|
|
3390 |
Aranđelovac |
01.05.12. |
|
|
3441 |
Aranđelovac |
02.05.12. |
|
|
3517 |
Aranđelovac |
08.05.12. |
|
|
3124 |
Apatin |
09.04.12. |
|
|
3155 |
Apatin |
11.04.12. |
|
|
3177 |
Apatin |
19.04.12. |
|
|
3357 |
Apatin |
28.04.12. |
|
|
3492 |
Apatin |
06.05.12. |
|
|
3316 |
Beograd |
25.04.12. |
|
|
3346 |
Beograd |
28.04.12. |
|
|
3372 |
Beograd |
01.05.12. |
|
|
3414 |
Beograd |
01.05.12. |
|
|
3451 |
Beograd |
02.05.12. |
|
|
3467 |
Beograd |
02.05.12. |
|
|
3474 |
Beograd |
04.05.12. |
|
|
3490 |
Beograd |
05.05.12. |
|
|
3503 |
Beograd |
08.05.12. |
|
|
3151 |
Novi Sad |
09.04.12. |
|
|
3438 |
Novi Sad |
02.05.12. |
|
|
3471 |
Novi Sad |
04.05.12. |
|
|
3160 |
Valjevo |
18.04.12. |
|
|
3328 |
Valjevo |
26.04.12. |
|
|
3368 |
Valjevo |
29.04.12. |
|
|
3420 |
Valjevo |
01.05.12. |
|
|
3501 |
Valjevo |
06.05.12. |
