Savet: Pokušajte da koristite novu Xlookup funkcije , poboljšane verzije funkcija opisanih u ovom članku. Ove nove funkcije funkcionišu u bilo kom pravcu i podrazumevano im daju tačne podudaranja, što ih čini lakšim i prikladnijim za korišćenje od svojih prethodnika.
Pretpostavimo da imate listu brojeva Office lokacija i treba da znate koji zaposleni se nalaze u svakom od njih. Unakrsna tabela je ogromna, pa možda mislite da je to izazovno zadatak. To se u stvari lako radi sa funkcijom pronalaženja.
Funkcije vanlookup i HLOOKUP , zajedno sa indeksom i podudarnosti,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 funkcija vanlookup.
=VLOOKUP(B2,C2:E7,3,TAČNO)
U ovom primeru, B2 je prvi argument– element podataka koji funkcija treba da radi. Za VANLOOKUP, ovaj prvi argument je vrednost koju želite da pronađete. Ovaj argument može da bude referenca na ćeliju ili Fiksna vrednost kao što je "Savić" ili 21.000. Drugi argument je opseg ćelija, C2 –: E7, u kojoj se traži vrednost koju želite da pronađete. Treći argument je kolona u tom opsegu ćelija koje sadrže 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 FALSE, funkcija će se podudarati sa vrednošću koju pruža prvi argument. Drugim rečima, ostavljajući četvrti argument prazno – ili unosite TRUE – to vam daje veću fleksibilnost.
Ovaj primer prikazuje kako funkcija radi. Kada unesete vrednost u ćeliju B2 (prvi argument), VLOOKUP pretražuje ćelije u opsegu C2: E7 (drugi argument) i daje najbližu približno podudaranje iz treće kolone u opsegu, koloni 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 ste zadovoljni sa programom VANLOOKUP, funkcija HLOOKUP se jednako lako koristi. Unosite iste argumente, ali on pretražuje redove umesto kolona.
Korišćenje INDEKSA i podudaranja umesto funkcije VANLOOKUP
Postoje određena ograničenja sa korišćenjem usluge VILOOKUP – funkcija VILOOKUP može da traži samo vrednost sleva nadesno. To znači da kolona koja sadrži vrednost koju ste pronašli treba uvek da se nalazi sa leve strane kolone koja sadrži povratnu vrednost. Ako unakrsna tabela nije napravljena na ovaj način, ne koristite VILOOKUP. Umesto toga koristite kombinaciju funkcija INDEX i MATCH.
Ovaj primer prikazuje malu listu u kojoj se u Chicagu žele tražiti, nije u krajnjoj levoj koloni. Ne možemo da koristimo funkcija vanlookup. Umesto toga, koristite funkciju MATCH da biste pronašli Čikago B1: B11. Nalazi se u 4. Zatim, INDEX koristi tu vrednost kao argument za pronalaženje i pronalazi populaciju za Čikago u иetvrtoj koloni (kolona D). Formula koja se koristi prikazuje se u ćeliji a14.
Više primera korišćenja INDEKSA i podudaranja umesto funkcije VLOOKUP potražite u članku https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-INDEX-MATCH/ po članku jelena, Microsoft MVP.
Pokušaj
Ako želite da eksperimentišete sa funkcijama LOOKUP pre nego što ih isprobate sa sopstvenim podacima, evo nekoliko uzoraka podataka.
Funkcija vanlookup na poslu
Kopirajte sledeće podatke u praznu unakrsnu tabelu.
Savet: Pre nego što nalepite podatke u Excel, Podesi širinu kolone od A do C do 250 piksela i kliknite na dugme prelomi tekst (karticaPočetak , grupa poravnavanja ).
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 |
Primer HLOOKUP
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, Podesi širinu kolone od A do C do 250 piksela i kliknite na dugme prelomi tekst (karticaPočetak , grupa poravnavanja ).
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 INDEKSA i podudaranja
Ovaj poslednji primer koristi funkcije INDEX i MATCH zajedno da biste vratili najraniji broj fakture i odgovarajući datum za svaki od pet gradova. Pošto je datum vraćen 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, Podesi širinu kolone od A do D do 250 piksela i kliknite na dugme prelomi tekst (karticaPočetak , grupa poravnavanja ).
Faktura | Grad | Datum fakture | Najranija faktura po gradovima sa datumom |
|---|---|---|---|
|
3115 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
27.04.12. |
|
|
3350 |
Atlanta |
28.04.12. |
|
|
3390 |
Atlanta |
01.05.12. |
|
|
3441 |
Atlanta |
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 |
Dalas |
25.04.12. |
|
|
3346 |
Dalas |
28.04.12. |
|
|
3372 |
Beograd |
01.05.12. |
|
|
3414 |
Beograd |
01.05.12. |
|
|
3451 |
Dalas |
02.05.12. |
|
|
3467 |
Dalas |
02.05.12. |
|
|
3474 |
Dalas |
04.05.12. |
|
|
3490 |
Dalas |
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. |
