Mnogi ljudi pokušavaju koristiti Excel kao bazu podataka. Iako može raditi kao baza podataka, neki od zadataka koji bi bili vrlo jednostavni u programu baze podataka prilično su složeni u programu Excel. Jedan od tih zadataka je podudaranje dvaju popisa na temelju zajedničkog polja; to se lako može postići pomoću programa Excel VLOOKUP. Funkcija VLOOKUP smatrat će vam se izuzetno korisnom, pa pogledajte primjer kada i kako koristiti ovu funkciju u nastavku.
Recimo da vam vaša turistička agencija šalje mjesečni izvještaj o svim mjestima koja su vaši zaposlenici putovali. Izvješće koristi nazive aerodroma umjesto imena gradova. Bilo bi korisno ako biste mogli jednostavno unijeti pravo ime grada umjesto samo koda.
Na Internetu možete pronaći i uvesti popis s nazivom grada za svaki kod zračne luke.
Ali, kako doći do ovih podataka o svakom zapisu u izvješću?
- Upotrijebite funkciju VLOOKUP. VLOOKUP je skraćenica od "Vertical Lookup". Može se koristiti bilo kada da imate popis podataka s ključnim poljem u krajnjem lijevom stupcu.
- Počnite upisivati funkciju
=VLOOKUP(. Upišite Ctrl + A da biste dobili pomoć s funkcijom. - VLOOKUP trebaju četiri parametra. Prvo je gradski broj u izvornom izvješću. U ovom primjeru to bi bila ćelija D4
- Sljedeći je parametar raspon s vašom tablicom pretraživanja. Istaknite raspon. Obavezno upotrijebite F4 da bi raspon bio apsolutni. (Apsolutna referenca ima znak dolara ispred broja stupca i broja retka. Kada se kopira formula, referenca će i dalje voditi prema I3: J351.
- Treći parametar govori Excelu u kojem se stupcu nalazi naziv grada. U opsegu I3: J351, naziv grada nalazi se u stupcu 2. Unesite 2 za ovaj parametar.
- Četvrti parametar govori Excelu je li podudaranje „zatvori“ u redu. U ovom slučaju nije, zato unesite False.
- Kliknite U redu da biste dovršili formulu. Povucite ručku za popunjavanje da biste kopirali formulu prema dolje.
- Budući da ste pažljivo unijeli apsolutne formule, možete kopirati stupac E u stupac D da biste dobili odredišni grad. U ovom slučaju, svi polasci su iz međunarodne zračne luke Pearson u Torontu.
Iako se ovaj primjer savršeno razradio, kada gledatelji koriste VLOOKUP, to obično znači da odgovaraju popisima koji su došli iz različitih izvora. Kad popisi dolaze iz različitih izvora, uvijek mogu postojati suptilne razlike zbog kojih se popisi teško podudaraju. Evo tri primjera što može poći po zlu i kako ih ispraviti.
-
Jedan popis ima crtice, a drugi nema. Upotrijebite
=SUBSTITUTE()funkciju za uklanjanje crtica. Kad prvi put isprobate VLOOKUP, dobit ćete N / A pogreške.Da biste uklonili crtice pomoću formule, upotrijebite formulu ZAMJENA. Upotrijebite 3 argumenta. Prvi argument je ćelija koja sadrži vrijednost. Sljedeći je argument tekst koji biste željeli promijeniti. Posljednji argument je zamjenski tekst. U ovom slučaju crtice želite promijeniti u ništa, pa formula je
=SUBSTITUTE(A4,"-","").Tu funkciju možete umotati u VLOOKUP da biste dobili opis.
-
Ovaj je suptilan, ali vrlo čest. Jedan popis ima prazan prostor nakon unosa. Upotrijebite = TRIM () da biste uklonili višak razmaka. Kada u početku unesete formulu, ustanovit ćete da su svi odgovori pogreške N / A. Sigurno znate da su vrijednosti na popisu i da s formulom sve izgleda u redu.
Jedna standardna stvar koju treba provjeriti je prelazak u ćeliju s vrijednošću pretraživanja. Pritisnite F2 da stavite ćeliju u način uređivanja. Jednom u načinu uređivanja možete vidjeti da se pokazivač nalazi na udaljenosti od zadnjeg slova. To ukazuje na to da u unosu postoji prazan prostor.
Da biste riješili problem, upotrijebite funkciju TRIM.
=TRIM(D4)uklonit će vodeće razmake, prateće razmake i zamijeniti sve unutarnje dvostruke razmake s jednim razmakom. U ovom slučaju TRIM savršeno radi na uklanjanju pratećeg prostora.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)je formula. -
U napomenama o emisiji spomenuo sam bonus savjet: kako zamijeniti rezultat # N / A za nedostajuće vrijednosti praznim. Ako se vaša vrijednost pretraživanja ne nalazi u tablici pretraživanja, VLOOKUP će vratiti pogrešku N / A.
Ova formula koristi
=ISNA()funkciju za otkrivanje je li rezultat formule pogreška N / A. Ako dobijete pogrešku, drugi argument u funkciji IF reći će Excelu da stavi bilo koji tekst koji želite.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP vam omogućuje uštedu vremena prilikom podudaranja popisa podataka. Odvojite vrijeme da naučite osnovnu upotrebu i moći ćete obavljati daleko moćnije zadatke u programu Excel.
