Preuzmite primjer radne knjige
Ovaj vodič prikazuje kako se koristi Excel VLOOKUP funkcija u Excelu za traženje vrijednosti.
Pregled funkcije VLOOKUP
Funkcija VLOOKUP Vlookup označava vertikalno pretraživanje. Traži vrijednost u krajnjem lijevom stupcu tablice. Zatim vraća vrijednost određeni broj stupaca desno od pronađene vrijednosti. Isto je kao i hlookup, osim što traži vrijednosti okomito umjesto vodoravno.
(Obratite pažnju na to kako se pojavljuju unosi formule)
Sintaksa i argumenti funkcije VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Vrijednost koju želite tražiti.
stol_pored - Raspon podataka koji sadrži i vrijednost koju želite tražiti i vrijednost koju želite da vlookup vrati. Stupac koji sadrži vrijednosti pretraživanja mora biti krajnji lijevi stupac.
col_index_num - Broj stupca raspona podataka iz kojeg želite vratiti vrijednost.
range_lookup - Istina ili laž. FALSE traži točno podudaranje. TRUE traži najbliže podudaranje koje je manje ili jednako vrijednosti lookup_value. Ako je odabrano TRUE, krajnji lijevi stupac (stupac za traženje) mora se sortirati uzlazno (od najniže do najviše).
Što je funkcija VLOOKUP?
Kao jedna od starijih funkcija u svijetu proračunskih tablica, koristi se funkcija VLOOKUP V.etički Potrage. Ima nekoliko ograničenja koja se često prevladavaju drugim funkcijama, poput INDEX/MATCH. Međutim, ima prednost što je jedna funkcija koja sama može pretraživati točno podudaranje. Također je to jedna od prvih funkcija o kojima ljudi uče.
Osnovni primjer
Pogledajmo uzorak podataka iz razredne knjige. Dotaknut ćemo se nekoliko primjera za izvlačenje informacija za određene učenike.
Ako želimo saznati u kojoj je klasi Bob, napisali bismo formulu:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Važno je zapamtiti da stavka koju tražimo (Bob) mora biti u prvom stupcu našeg raspona pretraživanja (A2: C5). Rekli smo funkciji da želimo vratiti vrijednost iz 2nd stupac, koji je u ovom slučaju stupac B. Konačno, naznačili smo da želimo napraviti an točno podudaranje stavljajući False kao posljednji argument. Ovdje će odgovor biti "Čitanje".
Bočni savjet: Također možete koristiti broj 0 umjesto False kao posljednji argument, jer imaju istu vrijednost. Nekim se ljudima ovo sviđa jer je brže pisati. Samo znajte da je oboje prihvatljivo.
Pomaknuti podaci
Da bismo dodali pojašnjenje u naš prvi primjer, stavka za pretraživanje ne mora biti u stupcu A vaše proračunske tablice, već samo u prvom stupcu vašeg raspona pretraživanja. Koristimo isti skup podataka:
Sada pronađimo ocjenu za razred Znanosti. Naša formula bila bi
| 1 | = VLOOKUP ("Znanost", B2: C5, 2, FALSE) |
Ovo je još uvijek valjana formula jer je prvi stupac našeg raspona pretraživanja stupac B, gdje će se pronaći naš pojam za pretraživanje "Znanost". Vraćamo vrijednost od 2nd stupac raspona pretraživanja, koji je u ovom slučaju stupac C. Odgovor je onda „A-“.
Korištenje zamjenskih znakova
Funkcija VLOOKUP podržava korištenje zamjenskih znakova "*" i "?" prilikom pretraživanja. Na primjer, recimo da smo zaboravili napisati Frankovo ime i samo smo htjeli potražiti ime koje počinje s "F". Mogli bismo napisati formulu
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
To bi moglo pronaći ime Frank u retku 5, a zatim vratiti vrijednost iz 2nd relativni stupac. U ovom slučaju odgovor će biti "znanost".
Netočno podudaranje
Većinu vremena morate biti sigurni da je zadnji argument u VLOOKUP -u lažan (ili 0) kako biste dobili točno podudaranje. Međutim, postoji nekoliko slučajeva u kojima biste mogli tražiti netočno podudaranje. Ako imate popis razvrstanih podataka, možete koristiti i VLOOKUP za vraćanje rezultata za stavku koja je ista ili sljedeća najmanja. To se često koristi kada se radi o povećanju raspona brojeva, na primjer u poreznoj tablici ili bonusima za provizije.
Recimo da želite pronaći poreznu stopu za prihod koji je unet u ćeliju D2. Formula u D4 može biti:
| 1 | = VLOOKUP (D2, A2: B6, 2, ISTINA) |
Razlika u ovoj formuli je u tome što je naš posljednji argument "Istina". U našem konkretnom primjeru možemo vidjeti da će, kada naši pojedinci unesu prihod od 45.000 USD, imati poreznu stopu od 15%.
Bilješka: Iako obično želimo točno podudaranje s False kao argumentom, zaboravite navesti 4th argument u VLOOKUP -u, zadana vrijednost je True. To može uzrokovati neočekivane rezultate, osobito kada se radi o tekstualnim vrijednostima.
Dinamički stupac
VLOOKUP zahtijeva da navedete argument u kojem stupcu želite vratiti vrijednost, ali može se dogoditi prilika kada ne znate gdje će se stupac nalaziti ili želite dopustiti korisniku da promijeni iz kojeg se stupca vratiti. U tim slučajevima može biti korisno upotrijebiti funkciju MATCH za određivanje broja stupca.
Razmotrimo ponovno primjer naše knjige ocjena, s nekim ulazima u E2 i E4. Da bismo dobili broj stupca, mogli bismo napisati formulu
| 1 | = MATCH (E2, A1: C1, 0) |
Time će se pokušati pronaći točan položaj “Ocjene” unutar raspona A1: C1. Odgovor će biti 3. Znajući to, možemo ga uključiti u funkciju VLOOKUP i napisati formulu u E6 ovako:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Dakle, funkcija MATCH će procijeniti na 3, a to govori VLOOKUP -u da vrati rezultat iz 3rd stupcu u rasponu A2: C5. Sve u svemu, tada dobivamo željeni rezultat "C". Naša je formula sada dinamična jer možemo promijeniti stupac za gledanje ili naziv za pretraživanje.
Ograničenja VLOOKUP -a
Kao što je spomenuto na početku članka, najveći pad VLOOKUP -a je taj što zahtijeva da se pojam za pretraživanje nalazi u krajnjem lijevom stupcu raspona pretraživanja. Iako postoje neki fantastični trikovi koje možete učiniti kako biste to prevladali, uobičajena alternativa je korištenje INDEX -a i MATCH -a. Ta vam kombinacija daje veću fleksibilnost, a ponekad čak može biti i brži izračun.
VLOOKUP u Google tablicama
Funkcija VLOOKUP radi potpuno isto u Google tablicama kao i u Excelu:
dodatne napomene
Upotrijebite funkciju VLOOKUP za izvođenje VERTICAL pretraživanja. VLOOKUP traži točno podudaranje (range_lookup = FALSE) ili najbliže podudaranje koje je jednako ili manje od lookup_value (ilirange_lookup = TRUE, samo numeričke vrijednosti) u prvom retku tablice. Zatim vraća odgovarajuću vrijednost, n broj redaka ispod podudaranja.
Kada koristite VLOOKUP za pronalaženje točnog podudaranja, prvo definirate identifikacijsku vrijednost koju želite tražiti kao lookup_value. Ova identifikacijska vrijednost može biti SSN, ID zaposlenika, naziv ili neki drugi jedinstveni identifikator.
Zatim definirate raspon (koji se naziva stol_pored) koji sadrži identifikatore u gornjem retku i sve vrijednosti koje na kraju želite tražiti u retcima ispod njega. VAŽNO: Jedinstveni identifikatori moraju biti u gornjem retku. Ako nisu, morate pomaknuti redak na vrh ili upotrijebiti MATCH / INDEX umjesto VLOOKUP -a.
Treće, definirajte broj retka (red_indeks) od stol_pored koje se želite vratiti. Imajte na umu da je prvi redak koji sadrži jedinstvene identifikatore redak 1. Drugi red je redak 2 itd.
Na kraju, morate naznačiti hoćete li tražiti točno podudaranje (FALSE) ili najbliže podudaranje (TRUE) u range_lookup. Ako je odabrana opcija točnog podudaranja, a točno podudaranje nije pronađeno, vraća se pogreška (#N/A). Da bi formula bila prazna ili "nije pronađena", ili bilo koju drugu vrijednost umjesto vrijednosti pogreške (#N/A), upotrijebite funkciju IFERROR s VLOOKUP -om.
Da biste pomoću funkcije VLOOKUP vratili približni skup podudaranja: range_lookup = ISTINA. Ova je opcija dostupna samo za numeričke vrijednosti. Vrijednosti se moraju sortirati uzlaznim redoslijedom.
Za izvođenje vodoravnog pretraživanja umjesto toga upotrijebite funkciju HLOOKUP.
VLOOKUP Primjeri u VBA
Također možete koristiti funkciju VLOOKUP u VBA. Tip:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Za argumente funkcije (lookup_value itd.) Možete ih unijeti izravno u funkciju ili definirati varijable koje ćete umjesto toga koristiti.
Povratak na popis svih funkcija u Excelu
