Excel – Lookup, Vlookup, Hlookup

Često nam se dešava da imamo tabele koje su u međusobnoj vezi, tj. da imamo tabele u kojoj se nalaze određeni podaci koje treba da iskoristimo u nekoj drugoj tabeli. Jedan od mnogobrojnih primera je kursna lista, primera radi imamo tabelu sa kursnom listom, a u nekoj drugoj tabeli imamo oznake valuta i iznose i treba da dobijemo sume za (npr.) plaćanje. Ono što bi u ovom slučaju trebali da uradimo je da pretražimo tabelu sa kursnim listama (možemo je nazvati pomoćna tabela), da za određenu šifru valute dobijemo koliki je kurs i taj podatak da pomnožimo sa iznosom.

Sličan primer je sa bazama podataka u kojima vrednost kolone jedne tabele može da bude ključ kojim pristupamo kolonama neke druge tabele. U ovom tekstu ćemo pokazati kako se koriste tri funkcije koje nam pomažu da pretražujemo nizove podataka: Lookup, Vlookup i Hlookup.

Lookup

Koristite ovu funkciju kada treba da pretražite jedan red ili kolonu i pronađete vrednost iz iste pozicije u drugom redu ili koloni.

LOOKUP možete koristiti na dva načina:

  • Vektorski oblik
  • oblik niza

Vektorski oblik

1

Vektorski oblik funkcije LOOKUP traži vrednosti u opsegu koji čini jedan red ili jedna kolona (poznatom pod nazivom vektor) i daje vrednost sa iste pozicije u drugom opsegu koji čini jedan red ili jedna kolona. Bitno je napomenuti da vektor_za_pronalaženje mora biti sortiran od najmanje do najveće vrednosti, u suprotnom ovaj funkcija neće raditi kako treba.

Sintaksa

LOOKUP(vrednost_za_pronalaženje, vektor_za_pronalaženje, [vektor_rezultata])

Primeri funkcije LOOKUP:

Kopirajte ovu formulu u kolonu D Šta radi ova formula Evo rezultata koji ćete videti
=LOOKUP(4.19, A2:A6, B2:B6) Traži 4,19 u koloni A i daje vrednost iz kolone B koja je u istom redu. Narandžasta
=LOOKUP(5.75, A2:A6, B2:B6) Traži 5,75 u koloni A, podudara se sa najbližom najmanjom vrednošću (5,17) i daje vrednost iz kolone B koja je u istom redu. Žuta
=LOOKUP(7.66, A2:A6, B2:B6) Traži 7,66 u koloni A, podudara se sa najbližom najmanjom vrednošću (6,39) i daje vrednost iz kolone B koja je u istom redu. Plava
=LOOKUP(0, A2:A6, B2:B6) Traži 0 u koloni A i daje grešku, jer je 0 manja od najmanje vrednosti (4,14) u koloni A. Greška

Oblik niza

Oblik niza funkcije LOOKUP obezbeđen je zbog kompatibilnosti sa drugim programima za unakrsne tabele, ali njegova funkcionalnost je ograničena. Oblik niza funkcije LOOKUP određenu vrednost traži u prvom redu ili koloni za niz i vraća vrednost sa iste pozicije u poslednjem redu ili koloni tog niza. Koristite ovaj oblik funkcije LOOKUP kada se vrednosti koje želite da uporedite nalaze u prvom redu ili u prvoj koloni tog niza.

Sintaksa

LOOKUP(vrednost_za_pronalaženje, niz)

Hlookup

Traži vrednost u gornjem redu tabele ili niz vrednosti, a zatim je vraća u istoj koloni, iz reda koji navedete u tabeli ili nizu. Funkciju HLOOKUP koristite kada se vrednosti za poređenje nalaze u redu na vrhu tabele podataka i kada želite da pregledate određeni broj redova. Upotrebite funkciju VLOOKUP kada se vrednosti za poređenje nalaze u koloni levo od podataka koje želite da pronađete.

Sintaksa

HLOOKUP(vrednost_za_pronalaženje, niz_tabele, indeksni_broj_reda, [opseg_za_pronalaženje])

Imamo datu sledeću tabelu iz koje želimo da dobijemo traženi podatak:

Osovine Ležajevi Zavrtnji
4 4 9
5 7 10
6 8 11

Primeri funkcije HLookup:

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 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 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(„Zavrtnji“, 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

Vlookup

Koristite VLOOKUP, jednu od funkcija za pretraživanje i reference, kada treba da pronađete nešto u tabeli ili u opsegu koji je filtriran po redu. Na primer, potražite prezime zaposlenog po njenom broju ili pronađite njen broj telefona tako što ćete potražiti njeno prezime (baš kao u imeniku). Tajna funkcije VLOOKUP je da organizujete podatke tako da vrednost koju tražite (prezime zaposlenog) bude sa leve strane povratne vrednosti koju želite da pronađete (broj telefona zaposlenog).

Sintaksa

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Imamo datu sledeću tabelu iz koje želimo da dobijemo traženi podatak:

ID Prezime Ime Pozicija Datum rođenja
101 Davidović Sara Predstavnik prodaje 08.12.1968
102 Fontić Oliver Zamenik direktora prodaje 19.02.1952.
103 Lalić Katarina Predstavnik prodaje 30.08.1963.
104 Patović Mihailo Predstavnik prodaje 19.09.1958
105 Burkanović Boris Menadžer prodaje 04.03.1955
106 Sovrlić Luka Predstavnik prodaje 02.07.1963.

Primeri funkcije VLookup:

Formula Opis
=VLOOKUP(„Fontić“,B2:E7,2,FALSE) Traži vrednost Fontana u prvu kolonu (kolona B) niz_tabele B2:E7 i daje vrednost Olivier nalazi u drugoj koloni (Koloni C) od table_array. range_lookup FALSE vraća potpuno podudaranje
=VLOOKUP(102,A2:C7,2,FALSE) Pretražuje potpuno podudaranje prezimena za lookup_value102 u koloni A. Fontana se vraća. Ako je lookup_value105, biće vraćena vrednost Burke.
=IF(VLOOKUP(103,A1:E7,2,FALSE)=„Sousa“,„Pronađeno“,„Nije pronađeno“) Proverava da li je prezime zaposlenog sa ID 103 je Susa. Da biste vratili jednu vrednost ako je uslov ispunjen, Funkcija IF koristi ili neku drugu vrednost ako je false. Pošto je 103 zapravo Leal, rezultat je da nije pronađena. Ako se „Susa“ da „Leal“ u formuli, rezultat je koji je smesten.
=INT(YEARFRAC(DATE(2014,6,30), VLOOKUP(105,A2:E7,5, FALSE), 1)) Za fiskalnu godinu 2014 pronalazi starost zaposlenog sa ID-om 105. Koristi funkciju YEARFRAC za oduzimanje datuma rođenja od datuma završetka fiskalne godine i prikazuje rezultat 59 kao ceo broj pomoću funkcije INT.
=IF(ISNA(VLOOKUP(105,A2:E7,2,FALSE)) = TRUE, „Zaposleni nije pronađen“, VLOOKUP(105,A2:E7,2,FALSE)) Ako postoji zaposleni čiji je ID 105, prikazuje prezime zaposlenog, koje glasi Burke. U suprotnom, prikazuje poruku Zaposleni nije pronađen. Funkcija ISNA (pokazuje IS funkcije daje vrednost TRUE kada funkcija VLOOKUP daje vrednost greške #N/A.
=VLOOKUP(104,A2:E7,3,FALSE) & “ “ & VLOOKUP(104,A2:E7,2,FALSE) & “ is a “ & VLOOKUP(104,A2:E7,4,FALSE) Za zaposlenog koji ima ID 104, povezuje (kombinuje) vrednosti tri ćelije u potpunu rečenicu Mihailo Patović je predstavnik prodaje.

4125-xa-excel-lookup-vlookup-hlookup-xa