U ovom svijetu vođenom podacima potrebni su razni alati za upravljanje podacima. Podaci su u stvarnom vremenu ogromni i dohvaćanje detalja u vezi s određenim dijelom podataka definitivno bi bio naporan zadatak, ali s VLOOKUP-om u Excel , ovaj se zadatak može postići jednim retkom naredbe. U ovom ćete članku učiti o jednom od važnih Excel funkcije tj. funkcija VLOOKUP.
Prije nego što krenemo dalje, pogledajmo na brzinu sve teme o kojima se ovdje raspravlja:
- Što je VLOOKUP u programu Excel?
- Kako radi?
- Točno podudaranje
- Približno podudaranje
- Prva utakmica
- Osjetljivost na velika i mala slova
- Pogreške
- Dvosmjerno traženje
- Upotreba zamjenskih znakova
- Više tablica pretraživanja
Što je VLOOKUP u programu Excel?
U Excelu je VLOOKUP ugrađena funkcija koji se koristi za traženje i dohvaćanje određenih podataka s excel lista. V označava Vertical (Vertikalno), a da bi se u programu Excel mogla koristiti funkcija VLOOKUP, podaci se moraju poredati okomito. Ova funkcija je vrlo korisna kada imate ogromnu količinu podataka i bilo bi praktički nemoguće ručno tražiti neke određene podatke.
Kako radi?
Funkcija VLOOKUP uzima vrijednost tj. Vrijednost pretraživanja i počinje je tražiti u krajnjem lijevom stupcu. Kada se pronađe prva pojava vrijednosti pretraživanja, ona se počinje kretati točno u tom retku i vraća vrijednost iz stupca koji navedete. Ova se funkcija može koristiti za vraćanje i točnih i približnih podudaranja (Zadano podudaranje je približno podudaranje).
Sintaksa:
Sintaksa ove funkcije je sljedeća:
pročitajte xml datoteku u primjeru Java
VLOOKUP (lookup_value, niz_tablica, col_index_num, [range_lookup])
gdje,
- lookup_value je vrijednost na koju treba paziti u prvom stupcu date tablice
- indeks_tablice je tablica iz koje se preuzimaju podaci
- broj_indeksa je stupac iz kojeg treba dohvatiti vrijednost
- range_lookup je logična vrijednost koja određuje treba li vrijednost pretraživanja savršeno odgovarati ili približno odgovarati ( PRAVI naći će najbliže podudaranje NETOČNO provjere za točno podudaranje)
Točno podudaranje:
Kada želite da funkcija VLOOKUP traži točno podudaranje vrijednosti pretraživanja, morat ćete postaviti range_lookup vrijednost na FALSE. Pogledajte sljedeći primjer koji je tablica koja se sastoji od podataka o zaposleniku:
U slučaju da želite potražiti oznaku bilo kojeg od ovih zaposlenika, možete učiniti sljedeće:
- Odaberite ćeliju u kojoj želite prikazati izlaz, a zatim upišite znak '='
- Upotrijebite funkciju VLOOKUP i opskrbite lookup_value (Ovdje će biti ID zaposlenika)
- Zatim unesite ostale parametre, tj niz_tabela , broj_indeksa i postavite range_lookup vrijednost na FALSE
- Stoga će funkcija i njezini parametri biti: = VLOOKUP (104, A1: D8, 3, FALSE)
Funkcija VLOOKUP počinje tražiti ID zaposlenika 104, a zatim se pomiče udesno u redu u kojem se nalazi vrijednost. Nastavlja se do col_index_num i vraća vrijednost prisutnu na tom položaju.
Približno podudaranje:
Ova značajka funkcije VLOOKUP omogućuje vam preuzimanje vrijednosti čak i kada nemate točno podudaranje za loopup_value. Kao što je ranije spomenuto, da biste VLOOKUP tražili približno podudaranje, morat ćete postaviti range_lookup vrijednost na TRUE. Pogledajte sljedeći primjer gdje se ocjene mapiraju zajedno s njihovim ocjenama i razredom kojem pripadaju.
- Slijedite iste postupke kao i za točno podudaranje, slijedite iste korake
- Umjesto vrijednosti range_lookup, upotrijebite TRUE umjesto FALSE
- Stoga će funkcija zajedno s njezinim parametrima biti: = VLOOKUP (55, A12: C15, 3, TRUE)
U tablici koja je sortirana u rastućem redoslijedu, VLOOKUP počinje tražiti približno podudaranje i zaustavlja se na sljedećoj najvećoj vrijednosti koja je manja od vrijednosti pretraživanja koju ste unijeli. Zatim se pomiče točno u taj redak i vraća vrijednost iz navedenog stupca. U gornjem primjeru vrijednost pretraživanja je 55, a sljedeća najveća vrijednost pretraživanja u prvom stupcu je 40. Stoga je izlaz Druga klasa.
Prva utakmica:
U slučaju da imate tablicu koja se sastoji od više vrijednosti pretraživanja, VLOOKUP se zaustavlja na prvom podudaranju te dohvaća vrijednost iz tog retka u navedenom stupcu.
Pogledajte donju sliku:
ID 105 se ponavlja i kada je vrijednost pretraživanja navedena kao 105, VLOOKUP je vratio vrijednost iz retka koji ima prvo pojavljivanje vrijednosti pretraživanja.
Osjetljivost na velika i mala slova:
Funkcija VLOOKUP ne razlikuje velika i mala slova. U slučaju da imate vrijednost pretraživanja koja je mala i velika vrijednost prisutna u tablici mala, VLOOKUP će i dalje dohvatiti vrijednost iz retka u kojem je vrijednost prisutna. Pogledajte donju sliku:
Kao što vidite, vrijednost koju sam odredio kao parametar je 'RAFA', dok je vrijednost prisutna u tablici 'Rafa', ali VLOOKUP je i dalje vratio navedenu vrijednost. Ako se točno podudarate čak i sa slučajem, VLOOKUP će i dalje vratiti prvo podudaranje vrijednosti pretraživanja, bez obzira na korišteni slučaj. Pogledajte donju sliku:
Pogreške:
Prirodno je naići na pogreške kad god koristimo funkcije. Slično tome, možete naići na pogreške i kada koristite funkciju VLOOKUP, a neke od uobičajenih pogrešaka su:
- #IME
- # N / A
- # REF
- #VRIJEDNOST
#NAME Pogreška:
Ova pogreška u osnovi vas obavještava da ste pogriješili u sintaksi. Da biste izbjegli sintaktičke pogreške, bolje je koristiti čarobnjak za funkcije koji nudi Excel za svaku funkciju. Čarobnjak za funkcije pomaže vam u informacijama o svakom parametru i vrsti vrijednosti koje trebate unijeti. Pogledajte donju sliku:
Kao što vidite, čarobnjak za funkcije vas obavještava da umjesto parametra lookup_value unesete bilo koju vrstu vrijednosti, a također daje i kratki opis iste. Slično tome, kada odaberete ostale parametre, vidjet ćete i informacije o njima.
# N / A Pogreška:
Ova se pogreška vraća u slučaju da nije pronađeno podudaranje za zadanu vrijednost pretraživanja. Na primjer, ako unesem 'AFA' umjesto 'RAFA', dobit ću pogrešku # N / A.
Da biste definirali neku poruku pogreške za gornje dvije pogreške, možete koristiti funkciju IFNA. Na primjer:
#REF pogreška:
Do ove pogreške dolazi kada dajete referencu na stupac koji nije dostupan u tablici.
Pogreška #VALUE:
Do ove pogreške dolazi kada postavite pogrešne vrijednosti parametrima ili propustite neke obvezne parametre.
Dvosmjerno traženje:
Dvosmjerno traženje odnosi se na dohvaćanje vrijednosti iz dvodimenzionalne tablice iz bilo koje ćelije referencirane tablice. Da biste izvršili dvosmjerno traženje pomoću VLOOKUP-a, morat ćete upotrijebiti funkciju MATCH zajedno s njom.
Sintaksa MATCH je sljedeća:
MATCH (lookup_value, lookup_array, match_type)
- lookup_value je vrijednost koju treba tražiti
- niz_potraži je raspon ćelija koje se sastoje od vrijednosti pretraživanja
- vrsta_podudaranja može biti broj, tj. 0, 1 ili -1 koji predstavlja točno podudaranje, manje ili veće od
Umjesto da koristite tvrdo kodirane vrijednosti s VLOOKUP-om, možete ga učiniti dinamičkim zaobilaženjem u referencama ćelija. Razmotrimo sljedeći primjer:
Kao što možete vidjeti na gornjoj slici, funkcija VLOOKUP uzima referencu ćelije kao F6 za vrijednost pretraživanja, a vrijednost indeksa stupca određuje se funkcijom MATCH. Kada promijenite bilo koju od ovih vrijednosti, izlaz će se također promijeniti u skladu s tim. Pogledajte donju sliku gdje sam promijenio vrijednost prisutnu u F6 iz Chris u Leo, a izlaz je također ažuriran u skladu s tim:
U slučaju da promijenim vrijednost G5, ili i F6 i G5, ova će formula raditi u skladu s prikazom odgovarajućih rezultata.
Također možete stvoriti padajuće popise kako biste zadaću promjene vrijednosti učinili vrlo korisnom. U gornjem primjeru to bi trebalo učiniti za F6 i G5. Evo kako možete stvoriti padajuće popise:
- Na kartici vrpce odaberite Podaci
- Iz grupe Alati za podatke odaberite Provjera podataka
- Otvorite okno Postavke i iz Dopusti odaberite Popis
- Odredite niz popisa izvora
Evo kako to izgleda nakon što stvorite padajući popis:
Upotreba zamjenskih znakova:
U slučaju da ne znate točnu vrijednost pretraživanja, ali samo jedan dio, možete koristiti zamjenske znakove. U Excelu simbol '*' predstavlja zamjenski znak. Ovaj simbol obavještava Excel da se niz koji dolazi prije, nakon ili između mora pretraživati i da može biti neograničen broj znakova prije ili poslije njih. Na primjer, u tablicu koju sam kreirao, ako s obje strane unesete 'erg' zajedno sa zamjenskim znakovima, VLOOKUP će vratiti izlaz za 'Sergio' kao što je prikazano dolje:
Više tablica pretraživanja:
U slučaju da imate više tablica pretraživanja, možete upotrijebiti funkciju IF zajedno s njom kako biste pregledali bilo koju tablicu na temelju nekog zadanog uvjeta. Na primjer, ako postoji tablica s podacima o dva supermarketa i trebate saznati dobit koju je svaki od njih stvorio na temelju prodaje, možete učiniti sljedeće:
Stvorite glavnu tablicu kako slijedi:
aws pokreće instancu iz snimke
Zatim stvorite dvije tablice iz kojih se mora dohodati.
Nakon što je to učinjeno, stvorite imenovani raspon za svaku od novostvorenih tablica. Da biste stvorili imenovani raspon, slijedite korake dane u nastavku:
- Odaberite tablicu cijeloj tablici kojoj želite dodijeliti ime
- Na kartici vrpce odaberite Formule, a zatim iz grupe Definirana imena odaberite Definiraj ime
- Vidjet ćete sljedeći dijaloški okvir
- Dajte bilo koje ime po svom izboru
- Kliknite U redu
Nakon što se to učini za obje tablice, možete koristiti ove imenovane raspone u funkciji IF na sljedeći način:
Kao što vidite, VLOOKUP je vratio odgovarajuće vrijednosti za popunjavanje stupca Dobit prema kojem supermarketu pripadaju. Umjesto da formulu zapišem u svaku ćeliju stupca Dobit, upravo sam kopirao formulu kako bismo uštedjeli vrijeme i energiju.
Ovo nas dovodi do kraja ovog članka o VLOOKUP-u u Excelu. Nadam se da vam je jasno sa svime što je s vama podijeljeno. Obavezno vježbajte što je više moguće i vratite svoje iskustvo.
Imate pitanje za nas? Molimo vas da ga spominjete u odjeljku za komentare ovog bloga „VLOOKUP u Excelu“ i javit ćemo vam se što je prije moguće.
Da biste stekli detaljno znanje o bilo kojim trendovskim tehnologijama, zajedno s raznim aplikacijama, možete se prijaviti za uživo s 24/7 podrškom i doživotnim pristupom.