Kako koristiti VLOOKUP u programu Excel

VLOOKUP je jedna od najkorisnijih funkcija programa Excel, a ujedno je i najmanje razumljiva. U ovom članku demistificiramo VLOOKUP primjerom iz stvarnog života. Izradit ćemo upotrebljivi predložak fakture za fiktivnu tvrtku.

VLOOKUP je Excel funkcija . Ovaj će članak pretpostaviti da čitač već usput razumije Excel funkcije i da može koristiti osnovne funkcije kao što su SUM, PROSJEČNO i DANAS. U svojoj najčešćoj upotrebi, VLOOKUP je funkcija baze podataka , što znači da radi s tablicama baze podataka - ili jednostavnije, sa popisima stvari na Excel radnom listu. Kakve stvari? Pa, bilo kakve stvari. Možda imate radni list koji sadrži popis zaposlenika ili proizvoda ili kupaca ili CD-ove u vašoj kolekciji CD-a ili zvijezde na noćnom nebu. Nije zapravo svejedno.

Evo primjera popisa ili baze podataka. U ovom slučaju to je popis proizvoda koje naša fiktivna tvrtka prodaje:

Ovakvi popisi obično imaju nekakav jedinstveni identifikator za svaku stavku na popisu. U ovom se slučaju jedinstveni identifikator nalazi u stupcu "Šifra artikla". Napomena: Da bi funkcija VLOOKUP radila s bazom podataka / popisom, taj popis mora imati stupac koji sadrži jedinstveni identifikator (ili „ključ“ ili „ID“), a taj stupac mora biti prvi stupac u tablici . Naša gornja baza podataka s uzorcima zadovoljava ovaj kriterij.

Najteži dio upotrebe VLOOKUP-a je razumjeti točno čemu služi. Pa da vidimo možemo li to prvo razjasniti:

VLOOKUP dohvaća podatke iz baze podataka / popisa na temelju isporučene instance jedinstvenog identifikatora.

U gornjem primjeru umetnuli biste funkciju VLOOKUP u drugu proračunsku tablicu s kodom artikla i ona bi vam vratila ili opis odgovarajućeg predmeta, njegovu cijenu ili dostupnost (njegova količina na zalihi) kako je opisano u vašem izvorniku popis. Koje će vam od ovih informacija proslijediti? Pa, to ćete odlučiti kad kreirate formulu.

Ako vam je potreban samo jedan podatak iz baze podataka, bilo bi puno problema otići na konstrukciju formule s funkcijom VLOOKUP. Obično biste ovu vrstu funkcionalnosti upotrebljavali u proračunskoj tablici za višekratnu upotrebu, poput predloška. Svaki put kad netko unese valjani kod stavke, sustav će dohvatiti sve potrebne informacije o odgovarajućoj stavci.

Stvorimo primjer za to: Predložak fakture koji možemo uvijek iznova koristiti u našoj fiktivnoj tvrtki.

Prvo pokrenemo Excel i stvorimo si prazan račun:

Ovako će to funkcionirati: osoba koja koristi predložak računa ispunit će niz šifri stavki u stupcu "A", a sustav će iz naše baze podataka proizvoda preuzeti opis i cijenu svakog predmeta. Te će se informacije koristiti za izračunavanje ukupnog broja linija za svaku stavku (pod pretpostavkom da unesemo valjanu količinu).

Da bismo olakšali ovaj primjer, locirat ćemo bazu podataka o proizvodima na zasebnom listu u istoj radnoj knjizi:

U stvarnosti je vjerojatnije da bi se baza podataka o proizvodima nalazila u zasebnoj radnoj knjizi. Nema velike razlike u funkciji VLOOKUP, koju zapravo nije briga nalazi li se baza podataka na istom listu, drugom listu ili potpuno drugoj radnoj knjizi.

Dakle, stvorili smo našu bazu podataka o proizvodima koja izgleda ovako:

Da bismo testirali formulu VLOOKUP koju ćemo napisati, prvo unosimo važeći kod stavke u ćeliju A11 naše prazne fakture:

Dalje, aktivnu ćeliju premještamo u ćeliju u kojoj želimo da se pohranjuju podaci do kojih je VLOOKUP došao iz baze podataka. Zanimljivo je da je ovo korak u kojem većina ljudi pogriješi. Da bismo dalje objasnili: Sprema se stvoriti VLOOKUP formulu koja će dohvatiti opis koji odgovara kodu stavke u ćeliji A11. Gdje želimo da se ovaj opis stavi kad ga dobijemo? U ćeliji B11, naravno. Dakle, tu upisujemo formulu VLOOKUP: u ćeliju B11. Odaberite ćeliju B11 odmah.

Moramo pronaći popis svih dostupnih funkcija koje Excel nudi, kako bismo mogli odabrati VLOOKUP i dobiti pomoć u popunjavanju formule. To se pronalazi tako da prvo kliknete karticu Formule , a zatim kliknete Umetni funkciju :

Pojavljuje se okvir koji nam omogućuje odabir bilo koje funkcije dostupne u programu Excel.

Da bismo pronašli onu koju tražimo, mogli bismo upisati pojam za pretraživanje poput "traženje" (jer je funkcija koja nas zanima funkcija pretraživanja ). Sustav bi nam vratio popis svih funkcija povezanih s pretraživanjem u Excelu.  VLOOKUP je drugi na popisu. Odaberite ga i kliknite U redu .

Pojavljuje se okvir Funkcijski argumenti koji nas traži od svih argumenata (ili parametara ) potrebnih za dovršetak funkcije VLOOKUP. Ovaj okvir možete smatrati funkcijom koja nam postavlja sljedeća pitanja:

  1. Koji jedinstveni identifikator tražite u bazi podataka?
  2. Gdje je baza podataka?
  3. Koji dio podataka iz baze podataka, povezan s jedinstvenim identifikatorom, želite dobiti za vas?

Prva tri argumenta prikazana su podebljano , što znači da su to obvezni argumenti (funkcija VLOOKUP bez njih je nepotpuna i neće vratiti valjanu vrijednost). Četvrti argument nije podebljan, što znači da je neobavezan:

Argumente ćemo dovršiti redom, od vrha do dna.

Prvi argument koji moramo ispuniti je argument Lookup_value . Funkcija treba da joj kažemo gdje pronaći jedinstveni identifikator ( u ovom slučaju kod artikla ) kojem bi trebala vraćati opis. Moramo odabrati šifru stavke koju smo ranije unijeli (u A11).

Kliknite ikonu selektora s desne strane prvog argumenta:

Zatim kliknite jednom na ćeliju koja sadrži kod stavke (A11) i pritisnite Enter :

Vrijednost "A11" umetnuta je u prvi argument.

Sada moramo unijeti vrijednost za argument Table_array . Drugim riječima, moramo reći VLOOKUP-u gdje pronaći bazu podataka / popis. Kliknite ikonu selektora pored drugog argumenta:

Sada pronađite bazu podataka / popis i odaberite cijeli popis - ne uključujući zaglavlje. U našem primjeru baza podataka nalazi se na zasebnom radnom listu, pa prvo kliknemo na karticu tog radnog lista:

Zatim odabiremo cijelu bazu podataka, ne uključujući zaglavlje:

... i pritisnite Enter . Raspon ćelija koji predstavlja bazu podataka (u ovom slučaju „Baza podataka proizvoda! A2: D7“) automatski se za nas unosi u drugi argument.

Sada moramo unijeti treći argument, Col_index_num . Ovim argumentom specificiramo VLOOKUP-u koji dio podataka iz baze podataka, pridružen našem kodu stavke u A11, želimo da nam se vrati. U ovom konkretnom primjeru želimo da nam se vrati opis predmeta . Ako pogledate radni list baze podataka, primijetit ćete da je stupac "Opis" drugi stupac u bazi podataka. To znači da u okvir Col_index_num moramo unijeti vrijednost "2" :

Važno je napomenuti da ovdje ne unosimo „2“, jer je stupac „Opis“ u stupcu B na tom radnom listu. Ako bi baza podataka slučajno započela u stupcu K radnog lista, u ovo bismo polje i dalje unijeli "2", jer je stupac "Opis" drugi stupac u skupu ćelija koje smo odabrali prilikom specificiranja "Table_rada".

Konačno, moramo odlučiti da li ćemo unijeti vrijednost u konačni VLOOKUP argument, Range_lookup . Ovaj argument zahtijeva istinitu ili lažnu vrijednost ili ga treba ostaviti praznim. Kada koristite VLOOKUP s bazama podataka (kao što je istina u 90% slučajeva), način odlučivanja što staviti u ovaj argument može se zamisliti na sljedeći način:

Ako se prvi stupac baze podataka (stupac koji sadrži jedinstvene identifikatore) sortira abecedno / numerički u rastućem redoslijedu, tada je u ovaj argument moguće unijeti vrijednost true ili ga ostaviti praznim.

Ako prvi stupac baze podataka nije sortiran ili je sortiran silaznim redoslijedom, tada u ovaj argument morate unijeti vrijednost false

Kako prvi stupac naše baze podataka nije sortiran, u ovaj argument unosimo false :

To je to! Upisali smo sve podatke potrebne za VLOOKUP da bi vratio vrijednost koja nam je potrebna. Kliknite gumb U redu i primijetite da je opis koji odgovara kodu stavke “R99245” ispravno unesen u ćeliju B11:

Formula koja je stvorena za nas izgleda ovako:

Ako unesemo drugačiji kod stavke u ćeliju A11, počet ćemo uviđati snagu funkcije VLOOKUP: Opis ćelije mijenja se kako bi odgovarao novom kodu stavke:

Možemo izvesti sličan niz koraka da vratimo cijenu predmeta u ćeliju E11. Imajte na umu da se nova formula mora stvoriti u ćeliji E11. Rezultat će izgledati ovako:

... i formula će izgledati ovako:

Imajte na umu da je jedina razlika između dviju formula treći argument ( Col_index_num ) promijenjen iz "2" u "3" (jer želimo podatke preuzete iz 3. stupca u bazi podataka).

Ako bismo odlučili kupiti 2 od ovih predmeta, unijeli bismo „2“ u ćeliju D11. Tada bismo u ćeliju F11 unijeli jednostavnu formulu da bismo dobili ukupni redak:

= D11 * E1

... što izgleda ovako ...

Popunjavanje predloška fakture

Do sada smo naučili puno o VLOOKUP-u. Zapravo smo u ovom članku naučili sve što ćemo naučiti. Važno je napomenuti da se VLOOKUP može koristiti i u drugim okolnostima, osim baza podataka. To je rjeđe i možda će biti obrađeno u budućim člancima How-To Geek.

Naš predložak fakture još nije dovršen. Da bismo je dovršili, učinili bismo sljedeće:

  1. Uklonili bismo uzorak koda stavke iz ćelije A11 i "2" iz ćelije D11. To će uzrokovati da naše novostvorene VLOOKUP formule prikazuju poruke pogreške:



    To možemo ispraviti razumnom uporabom Excel-ovih funkcija IF () i ISBLANK () . Mijenjamo svoju formulu s ove ...      = VLOOKUP (A11, 'Baza podataka o proizvodu'! A2: D7,2, FALSE) ... u ovu ... = AKO (ISBLANK (A11), "", VLOOKUP (A11, 'Baza podataka o proizvodu'! A2 : D7,2, NETOČNO))


  2. Kopirali bismo formule u ćelijama B11, E11 i F11 do ostatka redaka stavki na računu. Imajte na umu da ako to učinimo, dobivene formule više se neće ispravno odnositi na tablicu baze podataka. To bismo mogli popraviti promjenom referenci stanica za bazu podataka u apsolutne reference ćelija. Alternativno - i još bolje - mogli bismo stvoriti naziv raspona za cijelu bazu podataka proizvoda (kao što je „Proizvodi“) i koristiti ovaj naziv raspona umjesto referenci na ćeliju. Formula bi se promijenila iz ovog ...      = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Baza podataka proizvoda! A2: D7,2, FALSE)) ... u ovo ...       = IF (ISBLANK (A11), "" , VLOOKUP (A11, Proizvodi, 2, FALSE)) ... i zatim kopirajte formule u ostatak redaka stavke fakture.
  3. Mi bi vjerojatno „zaključati” stanice koje sadrže naše formule (odnosno otključati na druge stanice), a zatim zaštititi list, kako bi se osiguralo da su naši pažljivo izgrađene formule nisu slučajno izbrisane kada netko dođe da ispunite računu.
  4. Datoteku bismo spremili kao predložak kako bi je svi u našoj tvrtki mogli ponovno koristiti

Ako bismo se osjećali doista pametno, stvorili bismo bazu podataka svih naših kupaca na drugom radnom listu, a zatim bismo pomoću korisničkog ID-a unesenog u ćeliju F5 automatski unijeli ime i adresu kupca u ćelije B6, B7 i B8.

Ako želite vježbati s VLOOKUP-om ili jednostavno pogledajte naš predložak fakture, možete ga preuzeti ovdje.