Savet
Pokušajte da koristite nove XLOOKUP i XMATCH funkcije, poboljšane verzije funkcija opisanih u ovom članku. Ove nove funkcije rade u bilo kom smeru i podrazumevano 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 potrebno je da znate koji zaposleni se nalaze u kojoj kancelariji. Unakrsna tabela je ogromna pa možda mislite da je to izazovan zadatak. To je zapravo prilično lako uraditi pomoću funkcije pronalaženja.
Funkcije 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 kako da koristite funkciju 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 predstavlja vrednost koju želite da pronađete. Ovaj argument može biti referenca na ćeliju ili fiksna vrednost, poput "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 traženu vrednost.
Četvrti argument je opcionalan. Unesite ili 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 "NETAČNO", funkcija će se podudarati sa vrednošću koju ste naveli u prvom argumentu. Drugim rečima, ako ostavite četvrti argument prazan – ili unesete vrednost "TAČNO" – imaćete 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 vrednost koja se najbliže podudara iz treće kolone u opsegu, kolone E (treći argument).
Četvrti argument je prazan, pa funkcija vraća vrednost koja se približno podudara. U suprotnom, morali biste da unesete neku od vrednosti u kolonama C ili D da biste uopšte dobili rezultat.
Kada se dobro upoznate sa funkcijom VLOOKUP, funkcija HLOOKUP je podjednako laka za korišćenje. Unose se isti argumenti, samo što pretražuje u redovima umesto kolonama.
Korišćenje funkcija INDEX i MATCH umesto funkcije VLOOKUP
Postoje određena ograničenja pri korišćenju funkcije VLOOKUP – funkcija VLOOKUP može da traži vrednost samo sleva nadesno. To znači da kolona koja sadrži vrednost koju tražite treba uvek da se nalazi levo od 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 prikazuje kratku listu gde vrednost po kojoj želimo da pretražujemo, Čačak, nije u krajnjoj levoj koloni. Zato ne možemo da koristimo funkciju VLOOKUP. Umesto toga, koristićemo funkciju MATCH da bismo pronašli Čačak u opsegu B1:B11. Nalazi se u 4. redu. Funkcija INDEX zatim koristi tu vrednost kao argument za pretraživanje i pronalazi broj stanovnika za Čačak u 4četvrtoj koloni (kolona D). Formula koja se koristi prikazana je u ćeliji A14.
Pokušaj
Ako želite da eksperimentišete sa funkcijama za pretraživanje pre nego što ih isprobate na sopstvenim podacima, ovde možete pronaći probne podatke.
VLOOKUP primer na delu
Kopirajte sledeće podatke u praznu unakrsnu tabelu.
Savet
Pre nego što nalepite podatke u Excel, podesite širinu kolona na 250 piksela za kolone od A do C, a zatim izaberite stavku Prelomi tekst (kartica "Poč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 na 250 piksela za kolone od A do C, a zatim izaberite stavku Prelomi tekst (kartica "Poč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 funkcija INDEX i MATCH
Poslednji primer koristi funkcije INDEX i MATCH zajedno kako bi se dobio najraniji broj fakture i odgovarajući datum za svaki od pet gradova. Funkciju TEXT koristimo za oblikovanje datuma, jer je datum izražen kao broj. 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 na 250 piksela za kolone od A do D i izaberite stavku Prelomi tekst (kartica "Poč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. |
Takođe pogledajte
Kartica sa kratkim uputstvima: podsetnik za korišćenje funkcije VLOOKUP
Funkcije za pretraživanje i reference (referenca)