Excel VLOOKUP vodič za početnike
Charlotte Miller
⚡ Pametni sažetak
Vodič za Excel VLOOKUP objašnjava kako funkcija vertikalnog pretraživanja pretražuje prvi stupac tablice i vraća odgovarajuću vrijednost iz drugog stupca. Ovaj vodič pokriva sintaksu, točna i približna podudaranja, unakrsno pretraživanje, uobičajene pogreške i modernu alternativu XLOOKUP-u.
Što je VLOOKUP?
VLOOKUP (V je kratica za Vertikalno) je ugrađena Excelova funkcija koja uspostavlja odnos između stupaca u proračunskoj tablici. Omogućuje vam pretraživanje vrijednosti u jednom stupcu i vraćanje odgovarajuće vrijednosti iz drugog stupca u istom retku.
Sintaksa i argumenti VLOOKUP-a
Prije primjene VLOOKUP-a, korisno je razumjeti strukturu formule. Funkcija prima četiri argumenta i slijedi dosljedan obrazac u svakoj verziji Excela.
- tražena_vrijednost — vrijednost koju želite pronaći (referenca ćelije ili literal).
- table_array — raspon ćelija koje sadrže stupac za pretraživanje i stupac za povrat.
- col_index_num — broj stupca u table_array iz kojeg treba vratiti vrijednost (1 je krajnji lijevi).
- raspon_potraga — FALSE za točno podudaranje, TRUE (ili izostavljeno) za približno podudaranje na sortiranim podacima.
Važno: Vrijednost pretraživanja mora se nalaziti u krajnjem lijevom stupcu table_array, a VLOOKUP pretražuje samo slijeva nadesno.
Upotreba VLOOKUP-a
Kada trebate pronaći određene informacije u velikoj proračunskoj tablici ili više puta dohvatiti istu vrstu vrijednosti, VLOOKUP štedi značajno vrijeme u usporedbi s ručnim filtriranjem.
Razmislite o Tablica plaća tvrtke održava financijski tim. Počinjete s poznatim podatkom - indeksom - i koristite VLOOKUP za dohvaćanje nepoznate vrijednosti.
Na primjer, već znate ime zaposlenika:
I želite provjeriti plaću zaposlenika:
Excel tablica za gornji primjer:
Preuzmite gornju Excel datoteku
Da bismo pronašli nepoznatu plaću zaposlenika, unosimo zaposlenika Code koji je već dostupan.
Primjenom VLOOKUP-a, vrijednost plaće koja odgovara tom zaposleniku Code pojavljuje se automatski.
Kako koristiti funkciju VLOOKUP u Excelu
Slijedite ovaj vodič korak po korak kako biste primijenili funkciju VLOOKUP u Excelu:
Korak 1) Idite do ciljne ćelije
Kliknite ćeliju u kojoj želite da se prikaže plaća odabranog zaposlenika - u ovom primjeru, ćeliju H3.
Korak 2) Unesite funkciju VLOOKUP =VLOOKUP()
Upišite funkciju u ćeliju. Počnite sa znakom jednakosti (koji Excelu govori da slijedi formula), a zatim ključnom riječi VLOOKUP: =VLOOKUP().
Zagrade sadrže skup argumenata (podataka koje funkcija treba).
VLOOKUP zahtijeva četiri argumenta:
Korak 3) Prvi argument — vrijednost pretraživanja
Prvi argument je referenca ćelije za vrijednost koju želite tražiti. U ovom slučaju, Zaposlenik Code je vrijednost pretraživanja, pa je prvi argument H2 - ćelija čiji sadržaj Excel treba odgovarati.
Korak 4) Drugi argument — tablica niza
Ovo se odnosi na blok vrijednosti koje treba pretraživati, u Excelu poznat kao tablični niz ili tablicu pretraživanja. U našem primjeru, tablica pretraživanja se izvodi od B2 do E25.
NAPOMENA: Stupac za pretraživanje mora biti krajnji lijevi stupac vašeg tabličnog polja.
Korak 5) Treći argument — col_index_num
Ovo govori VLOOKUP-u koji stupac unutar tablice sadrži povratnu vrijednost. Plaća zaposlenika nalazi se u četvrtom stupcu, pa je indeks stupca 4.
Korak 6) Četvrti argument - točno ili približno podudaranje
Posljednji argument je zastavica pretraživanja raspona. Ona kontrolira vraća li VLOOKUP točno ili približno podudaranje. Ovdje želimo točno podudaranje (FALSE).
- FALSE — točno podudaranje.
- TRUE — približno podudaranje.
Korak 7) Pritisnite Enter
Pritisnite Enter za dovršetak formule. U početku ćete vidjeti grešku jer nema zaposlenika Code još nije unesen u H2.
Nakon što unesete valjanog zaposlenika Code U H2, ćelija vraća odgovarajuću plaću zaposlenika.
Ukratko, formula govori Excelu da se poznate vrijednosti nalaze u krajnjem lijevom stupcu podataka (Zaposlenik Code). VLOOKUP zatim skenira tablicu i vraća vrijednost četvrtog stupca u odgovarajućem retku - plaću zaposlenika.
Ovaj primjer pokriva točna podudaranja (ključna riječ FALSE). Sljedeći odjeljak objašnjava približna podudaranja.
VLOOKUP za približna podudaranja (TRUE ključna riječ kao zadnji parametar)
Razmotrite scenarij u kojem tablica izračunava popuste za kupce koji ne kupuju točno desetke ili stotine artikala.
Kao što je prikazano u nastavku, tvrtka primjenjuje popuste na količine u rasponu od 1 do 10,000:
Preuzmite gornju Excel datoteku
Kupac rijetko kupuje točno 100 ili 1,000 jedinica. Način približnog podudaranja omogućuje VLOOKUP-u da pronađe najbližu nižu vrijednost umjesto inzistiranja na točnoj brojci. Koraci:
Korak 1) Kliknite ćeliju u koju će se nalaziti funkcija VLOOKUP - referenca ćelije I2.
Korak 2) U ćeliju unesite =VLOOKUP() i dodajte argumente unutar zagrada.
Korak 3) Argument 1: Unesite referencu ćelije čija se vrijednost treba usporediti s tablicom pretraživanja.
Korak 4) Argument 2: Odaberite tablicu za pretraživanje - ovdje stupce Količina i Popust.
Korak 5) Argument 3: Unesite indeks stupca u tablicu pretraživanja iz koje želite vratiti odgovarajuću vrijednost.
Korak 6) Argument 4: Postavite posljednji argument na TRUE za približne podudarnosti.
Korak 7) Pritisnite Enter. Formula se sada primjenjuje na ćeliju. Kada upišete bilo koju količinu, Excel vraća raspon popusta na temelju približnog podudaranja.
NAPOMENA: Ako četvrti argument ostavite praznim, Excel će prema zadanim postavkama postaviti vrijednost TRUE (približno podudaranje). Za približna podudaranja, stupac za pretraživanje mora biti sortiran uzlaznim redoslijedom.
Funkcija Vlookup primijenjena između 2 različita lista smještena u istoj radnoj knjizi
Sada razmotrite radnu knjigu s dva lista. List 1 navodi zaposlenika Code, Ime i naziv; List 2 navodi zaposlenika Code i Plaća zaposlenika.
LIST 1:
LIST 2:
Preuzmite gornju Excel datoteku
Cilj je konsolidirati sve podatke na Listu 1, kao što je prikazano u nastavku:
VLOOKUP može agregirati podatke tako da zaposlenik Code, Ime i Plaća pojavljuju se zajedno na jednom listu.
Počinjemo na Radnom listu 2 jer on daje dva argumenta - ovdje je stupac Plaća zaposlenika i indeks stupca je 2.
Želimo pronaći plaću koja odgovara svakom zaposleniku Code.
Podaci se protežu od A2 do B25 - to je naš niz tablica.
Korak 1) Prebacite se na List 1 i unesite prikazane naslove.
Korak 2) Kliknite ćeliju pored Plaće zaposlenika – ćeliju F3 – u koju će ići VLOOKUP formula.
Unesite funkciju VLOOKUP: =VLOOKUP().
Korak 3) Argument 1: Unesite F2 — ćeliju koja sadrži zaposlenika Code za podudaranje u tablici pretraživanja.
Korak 4) Argument 2: Tablica pretraživanja nalazi se na drugom listu, pa je referencirajte s nazivom lista: List2!A2:B25.
Korak 5) Argument 3: Unesite indeks stupca unutar tablice pretraživanja koji sadrži povratnu vrijednost.
Korak 6) Argument 4: Koristite FALSE za točno podudaranje jer želimo točnu plaću koja odgovara svakom zaposleniku Code.
Korak 7) Pritisnite Enter. Kada unesete zaposlenika Code, ćelija vraća odgovarajuću plaću preuzetu s Lista 2.
Uobičajene VLOOKUP pogreške i ispravci
Čak i iskusni korisnici nailaze na VLOOKUP pogreške. Najčešće i brza rješenja:
- # N / A — VLOOKUP ne može pronaći vrijednost pretraživanja. Provjerite ima li dodatnih razmaka, neusklađenih tipova podataka (brojevi pohranjeni kao tekst) ili postoji li vrijednost doista u prvom stupcu table_array.
- #REF! — col_index_num je veći od broja stupaca u table_array. Smanjite indeks stupca ili proširite raspon.
- #VRIJEDNOST! — col_index_num je manji od 1 ili argument nije valjan. Provjerite sintaksu formule.
- Vraćen je pogrešan rezultat — četvrti argument je TRUE ili izostavljen, ali stupac za pretraživanje nije sortiran. Prebacite se na FALSE ili sortirajte stupac uzlazno.
- Zaključane reference — prilikom kopiranja formule koristite apsolutne reference (na primjer, $B$2:$E$25) kako se vrijednost table_array ne bi pomicala.
VLOOKUP vs. XLOOKUP: Koji biste trebali koristiti?
Microsoft uveo XLOOKUP u Microsoft 365 i Excel 2021 kao moderna zamjena za VLOOKUP. Uklanja nekoliko ograničenja VLOOKUP-a i sada je preporučeni izbor u podržanim verzijama.
| svojstvo | VLOOKUP | XLOOKUP |
|---|---|---|
| Smjer pretraživanja | Samo s lijeva na desno | Bilo koji smjer (lijevo, desno, gore, dolje) |
| Zadana vrsta podudaranja | Približno (TOČNO) | Točan |
| Rukovanje ako nije pronađeno | Povrat #N/A | Ugrađeni argument if_not_found |
| Indeks stupca | Tvrđeni broj | Referenciraj raspon povratnog stupca |
| Dostupnost | Sve verzije Excela | Microsoft 365, Excel 2021, Excel za web |
Kada odabrati VLOOKUP: Radna knjiga mora se izvoditi u programu Excel 2019 ili starijoj verziji ili održavate naslijeđene formule. Kada odabrati XLOOKUP: Izrađujete nove radne knjige u modernom Excelu i želite pretraživanja s lijeve strane, čišće rukovanje pogreškama i točno podudaranje prema zadanim postavkama. Saznajte više o funkcijama pretraživanja u Excel tutorijali Serija.
Zaključak
Tri gornja scenarija objašnjavaju kako VLOOKUP funkcionira za točna podudaranja, približna podudaranja i unakrsne reference. Vježbajte na vlastitim skupovima podataka kako biste izgradili tečnost. VLOOKUP ostaje važna značajka u MS-Excel za učinkovito upravljanje podacima, a XLOOKUP proširuje taj skup alata u modernom Excelu.
