Primjena i definicija ove Excel funkcije
VLOOKUP je Excel funkcija s kojom korisnik može pretraživati i procjenjivati sadržaj tablice. Ova je funkcija dostupna u verzijama iz programa Excel 2007 za Windows i Mac.
Što je VLOOKUP?
Moguće uporabe VLOOKUP -a ovdje će se objasniti primjerom: U ovom ste velikom ljubitelju književnosti pa ste stoga stvorili vlastitu Excel proračunsku tablicu u kojoj možete pažljivo sortirati knjige koje ste prikupili. Svaki rad unosi se s podacima o sljedećim kategorijama:
-
Autor
-
titula
-
Broj stranice
-
Godina izdavanja
Sada biste htjeli prijatelju dati savjet za knjigu koji ćete ponijeti sa sobom na sljedeći sastanak. Nažalost, možete misliti samo na autora, ne i na naslov knjige. Ovdje VLOOKUP dolazi u igru, jer može upotrijebiti ovu ulaznu vrijednost za izbacivanje informacija koje tražite jednim potezom.
Kako se koristi VLOOKUP?
Prije nego uopće razmislite o formuliranju formula, potrebno je utvrditi gdje će se kasnije nalaziti polje za unos i različita polja za izlaz. Da biste to učinili, ima smisla stvoriti zasebnu tablicu koja je u početku prazna i tako omogućuje prostor za navedene podatke. Ako ovu novu tablicu osmislite na primjeru postojeće tablice, kasnije ćete imati prednost u uštedi vremena.
Na temelju toga, formula VLOOKUP može se stvoriti ručno ili automatski generirati u Excelu. Za početnike je vrijedno koristiti potonji pristup kako bi postupno upoznali strukturu i učinak formule. Da biste to učinili, gumb za "Umetni funkciju" odabran je na kartici "Formule". VLOOKUP je skriven u prozoru koji se otvori. Nakon potvrde, ponovno se otvara prozor u koji se mogu ispuniti četiri parametra formule. Ovi su:
-
Kriterij pretraživanja
-
matrica
-
Indeks stupca
-
Referenca_područja
Sirovi nacrt formule stoga izgleda ovako:
= VLOOKUP (kriterij pretraživanja, matrica, indeks stupca, veza_ raspona)
i u jednoj od mogućih aplikacija poput ove:
= VLOOKUP (H3; A3: E40; 5)
Kriterij pretraživanja
Kako bi funkcija znala koju vrijednost treba koristiti kao početnu točku, redak koji je odabran kao polje za unos dva koraka ranije zabilježen je u polju "Kriterij pretraživanja". U našem primjeru tu je upisano ime autora knjige “Phillip Pulmann”. To čini formulu fleksibilnom i ne mora se ponovno prilagođavati čim se promijenjena vrijednost promijeni.
matrica
Polje za unos "Matrix" opisuje tablicu u kojoj se mogu pronaći podaci za izlaz. Ova posebna matrica stoga sadrži i stupce za naslov knjige, broj stranice i godinu izdanja.
Matrica se potpuno odabire jednom bez naslova od gornje lijeve do donje desne margine. Na taj način Excel zna koji sadržaj treba uzeti u obzir pri ocjenjivanju.
Indeks stupca
Polje za unos "indeksa stupca" traži od korisnika da definira stupac matrice u kojem je navedena samo tražena vrijednost. Dodjela stupaca numerirana je kronološki. To znači da prvi stupac tablice prima vrijednost 1, drugi vrijednost 2 itd. U našem primjeru to odgovara indeksu stupca 1 za autora, indeksu stupca 2 za naslov, indeksu stupca 3 za broj stranice i indeks stupca 4 za godinu izdanja.
Kako bi tablica bila što fleksibilnija, umjesto broja može se povezati naslov stupca. Prednost je u tome što se formula također može bez problema prenijeti u druge retke jer se naslov stupca može svaki put fleksibilno prilagoditi.
Pažnja: VLOOKUP čita matricu slijeva nadesno, zbog čega se indeks stupca mora postaviti desno od stupca za kriterij pretraživanja kako bi ga funkcija uzela u obzir!
Referenca_područja
Parametar "Range_Lookup" dovršava formulu VLOOKUP određivanjem točnosti s kojom se tablica procjenjuje. Međutim, razlikuje se od prethodno navedenih komponenti formule jer je izborna. Ako se vrijednost 0 unese za "netočno", Excel traži samo vrijednost koja je navedena kao kriterij pretraživanja. S vrijednošću 1 za "true", međutim, nastavlja se potraga za očitim vrijednostima ako se točna vrijednost ne može pronaći.
Određivanje ovog parametra nije obavezno jer je vrijednost 1 zadano zadana. Ova će postavka biti korisna kasnije u naprednom VLOOKUP -u s više kriterija pretraživanja.
Spajanje
Čim se postave svi potrebni parametri, može se koristiti VLOOKUP. Nakon unosa kriterija pretraživanja i potvrde funkcije, vrijednost koju tražite pojavljuje se u retku koji je definiran kao izlazno polje.
U našem primjeru sada je prikazan naslov knjige "Zlatni kompas", što odgovara autoru koji je unijeo. Da biste brzo saznali broj stranice i godinu objavljivanja, ne morate učiniti ništa više nego povući postojeću formulu VLOOKUP u sljedeće ćelije. To je tako jednostavno jer je indeks stupca VLOOKUP -a povezan s zaglavljem stupca prve tablice, a druga tablica također je strukturirana istim redoslijedom.
U slučaju da se tablice međusobno razlikuju ili da se unatoč svemu dogodi pogreška, formula VLOOKUP može se promijeniti i ručno. Da biste to učinili, pretposljednja znamenka za indeks stupca mora biti usklađena sa stupcem nove vrijednosti koju treba ispisati.
VLOOKUP s više kriterija pretraživanja
Često se događa da jedan kriterij pretraživanja nije dovoljan za točnu procjenu velike Excelove tablice. Tada ima smisla pokrenuti VLOOKUP s nekoliko kriterija pretraživanja. Da biste to učinili, postojeću formulu morate nadopuniti dodatnom funkcijom IF. Na taj se način tijekom prijave može uzeti u obzir do osam različitih kriterija pretraživanja.
VLOOKUP u više Excel proračunskih tablica
Ako se kriterij pretraživanja ne može pronaći samo u jednoj tablici, nego moguće i u drugoj, formula VLOOKUP se može prilagoditi u skladu s tim. Za to se i funkcija if i funkcija ISERROR moraju postaviti ispred postojeće formule. Za to je potrebno pet parametara:
-
Kriterij pretraživanja
-
Matrica 1 i Matrica 2
-
Indeks stupca1 i indeks stupca2
Rezultat izgleda ovako:
= IF (ISERROR (VLOOKUP (kriterij pretraživanja, matrica1, stupac-indeks1, 0));
VLOOKUP (kriterij pretraživanja; matrica2; indeks stupca2,0); VLOOKUP (kriterij pretraživanja; matrica1; indeks stupca1;))
i u jednoj od mogućih aplikacija poput ove:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Kriterij pretraživanja koristi se za umetanje vrijednosti koju treba tražiti u dvije tablice. Matrix1 i Matrix2 definiraju odgovarajuća područja ćelija dviju tablica. Indeks stupca 1 i indeks stupca 2 koriste se za detaljnije definiranje stupaca odgovarajućih tablica koje treba pretraživati.
Ako se vrijednost koju tražite pojavi u obje tablice, Excel će ispisati rezultat iz prve tablice. Međutim, ako vrijednost nije pronađena ni u jednoj od dvije tablice, pojavit će se poruka o pogrešci. Prednost formule je u tome što dva popisa ne moraju imati istu strukturu ili biti iste veličine.
Dodijelite vrijednosti kategorijama pomoću VLOOKUP -a
Dodatna funkcija VLOOKUP -a omogućuje da se navedene vrijednosti automatski podijele na slova i predikate po vašem izboru. U našem prethodnom primjeru trebao bi se umetnuti dodatni stupac tablice za vrstu knjige. Knjige duljine do 50 stranica trebale bi spadati u žanr novele, dok se knjige od 51 do 150 stranica dodjeljuju noveli i od 151 stranici romanu. Da bi to bilo moguće, u VLOOKUP -u nije potrebna dodatna formula, samo upotreba uvijenih zagrada “{}”. Gotova formula izgleda ovako:
= VLOOKUP (B1; {1. "Kratka priča"; 51. "Novela"; 151. "Novela"}; 2)
Sadržaj uvijenih zagrada označava matricu koja definira područje odgovarajuće vrste knjige. Dodjela bočne duljine odgovarajućem rodu stoga je smještena unutar kovrčavih zagrada. Formula koristi parove vrijednosti, od kojih je svaka odvojena točkom. Matrica {1. "Kratka priča"; 51. "Novela"; 151. "Novela"} čita se na sljedeći način:
"Od 1 emisije kratka priča, od 51 prikaži novelu, od 151 prikaži roman."
Ova se matrica lako može prilagoditi različitim zadacima. To se s jedne strane tiče veličine i broja matrica, kao i njihovog označavanja. Tako je moguće umjesto pojedinačnih slova ispisati nizove ili brojeve. Sve što trebate učiniti je prilagoditi slova u formuli.
VLOOKUP na više radnih listova
Druga funkcija VLOOKUP -a korisnicima omogućuje povezivanje sadržaja koji se nalazi na različitim proračunskim tablicama. U našem primjeru ova opcija može biti korisna kada se informacije prvo sortiraju na različitim radnim listovima, a zatim ažuriraju u sažetoj tablici.
Zamislite da osim svojih knjiga u Excel proračunskoj tablici navodite i svoje prikupljene filmove. Zatim kombinirate obje zbirke u jednu veliku tablicu.
Prednost ovog postupka nije samo u povećanom redoslijedu, već i u izbjegavanju potencijalnih pogrešaka. Ako želite stvoriti novi unos ili ažurirati postojeći, ne morate tražiti u velikoj tablici, već možete pristupiti manjim. Vrijednosti se tada automatski prenose u zbirnu tablicu programa Excel. Zbog toga je prepisivanje u veliku tablicu suvišno, što u najboljem slučaju izbjegava nesretan potez i kasnije ulančavanje poruka o pogrešci.
Kako izgleda formula?
Ova je funkcija ponovno omogućena umetanjem druge formule. Dok je za pretraživanje s više kriterija potrebna dodatna formula IF, rad s više radnih listova zahtijeva formulu INDIRECT. To omogućuje da se za matricu VLOOKUP navede raspon iz druge proračunske tablice.
= VLOOKUP (kriterij pretraživanja; INDIRECT (matrica); indeks stupca; raspon_veze)
Pažnja: Ova će formula funkcionirati samo ako pojedinačne tablice na različitim listovima imaju iste nazive kao naslovi stupaca opće tablice. Cijele tablice mogu se imenovati u "polju imena" u gornjem lijevom kutu iznad rešetke ćelija. Tablice koje su već imenovane mogu se pregledati kombinacijom tipki Ctrl + F3.
Suočavanje s novim porukama o pogreškama
Rad s povezanim tablicama programa Excel može dovesti do neželjenih problema. To posebno uključuje ispis pogrešnih vrijednosti. U slučaju da se prikaže pogrešna vrijednost 0, postoji mali problem u postavkama programa Excel, koji se može brzo riješiti.
Uobičajena poruka o pogrešci #NV, s druge strane, namjerna je funkcija VLOOKUP -a, koja korisniku ukazuje da potrebna vrijednost nije dostupna. Ova se bilješka može drugačije oblikovati uz pomoć formule.
VLOOKUP - pregled
VLOOKUP je korisna Excel funkcija koja se može koristiti za pretraživanje i procjenu tablica. Njegove prednosti očituju se u jednostavnoj i fleksibilnoj primjeni. Na taj način svi koji redovito rade s Excelovim tablicama mogu imati koristi od ove funkcije. Bio to privatni sakupljač koji stvara vlastite male tablice ili velika tvrtka koja obrađuje znatno značajnije skupove podataka.
S druge strane, ako i dalje imate neodgovorene zahtjeve koje VLOOKUP nije mogao zadovoljiti, možete se veseliti dodatnoj Excelovoj mogućnosti: Microsoft korisnicima Excel 365 nudi novi XLOOKUP od početka 2022-2023.-2022. To se nadograđuje na sposobnosti VLOOKUP -a i nadopunjuje ih dodatnim, ponekad čak i jednostavnijim, funkcijama. Stoga se u ovom trenutku otvara i nova rutina u procjeni podataka.
