Kako koristiti VLOOKUP u programu Excel

VLOOKUP je jedna od najkorisnijih funkcija programa Excel i također je jedan od najmanje razumljivih. U ovom članku demistificiramo VLOOKUP putem primjera stvarnog života. Izradit ćemo upotrebljiv predložak fakture za fiktivnu tvrtku.

VLOOKUP je Excel funkcija .Ovaj će članak pretpostaviti da čitatelj već ima prolazno razumijevanje funkcija programa Excel te može koristiti osnovne funkcije kao što su SUM, AVERAGE i DANAS.U svom najčešćem korištenju, VLOOKUP je baza podataka , što znači da radi s tablicama baze podataka - ili, jednostavno, navodi stvari u radnom listu programa Excel. Kakve stvari? Pa, bilo koji vrsta stvar. Možda imate radni list s popisom zaposlenika ili proizvoda ili kupaca ili CD-a u zbirci CD-a ili zvijezda na noćnom nebu. Nije važno.

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

Obično navedeni popisi imaju neku vrstu jedinstvenog identifikatora za svaku stavku na popisu. U tom je slučaju jedinstveni identifikator u stupcu "Šifra stavke".Napomena: Za funkciju VLOOKUP za rad s bazom podataka / popis, 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 baza podataka o uzorku iznad zadovoljava ovaj kriterij.

Najteži dio korištenja VLOOKUP-a je razumijevanje upravo ono što je za to. Pogledajmo možemo li to učiniti jasno:

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 stavke i vratit će vam opis odgovarajuće stavke, cijenu ili dostupnost( količina "Dostupna") kako je opisano u vašemizvorni popis. Koji od ovih dijelova informacija će vas vratiti natrag? Pa, odlučite to kad izrađujete formulu.

Ako vam sve što trebate je jedan komad podataka iz baze podataka, bilo bi puno poteškoća s konstrukcijom formule s funkcijom VLOOKUP u njemu. Obično biste koristili ovu vrstu funkcionalnosti u novorazumnoj proračunskoj tablici, kao što je predložak. Svaki put kad netko unese valjani kôd stavke, sustav će preuzeti sve potrebne informacije o odgovarajućoj stavci.

Napravimo primjer ovoga: predložak fakture koji možemo ponovo koristiti u našoj fiktivnoj tvrtki.

Prvo ćemo pokrenuti Excel, a sami stvorimo prazan račun:

Tako će raditi: Osoba koja koristi obrascu fakture ispunit će niz šifara stavki u stupcu "A", a sustav će preuzeti svaku stavkuopis i cijenu iz naše baze podataka. Te će se informacije upotrebljavati za izračun ukupne količine linije za svaku stavku( uz pretpostavku da unesemo važeću količinu).

U svrhu održavanja ovog primjera jednostavna ćemo pronaći bazu podataka proizvoda na zasebnom listu u istoj radnoj knjizi:

U stvarnosti je vjerojatnije da će se baza podataka proizvoda nalaziti u zasebnoj radnoj knjizi. Malo je razlika u funkciji VLOOKUP, što zapravo ne znači da se baza podataka nalazi na istom listu, drugačiji list ili potpuno drugačiju radnu knjigu.

Dakle, stvorili smo bazu podataka o proizvodu, koja izgleda ovako:

Da bismo testirali formulu VLOOKUP koju namjeravamo upisati, najprije unosimo valjani kôd stavke u ćeliju A11 naše prazne fakture:

Zatim ćemopremjestite aktivnu ćeliju na ćeliju u kojoj želimo pohraniti informacije koje je VLOOKUP prikupio iz baze podataka. Zanimljivo je da je to korak koji većina ljudi pogriješi. Dalje objasniti: Namjeravamo stvoriti VLOOKUP formulu koja će preuzeti opis koji odgovara kodu stavke u ćeliji A11.Gdje želimo ovaj opis staviti kad ga dobijemo? U ćeliji B11, naravno. Zato tamo pišemo VLOOKUP formulu: u ćeliji B11.Sada odaberite ćeliju B11.

Moramo pronaći popis svih raspoloživih funkcija koje Excel nudi, tako da možemo odabrati VLOOKUP i dobiti pomoć u popunjavanju formule. Ovo se pronalazi tako da prvo kliknete karticu , a zatim kliknete Insert Function :

Pojavljuje se okvir koji omogućuje odabir bilo koje funkcije dostupne u Excelu.

Da biste pronašli onu koju tražimo, mogli bismo upisati pojam za pretraživanje kao što je "lookup"( jer je funkcija zainteresirana za lookup funkciju).Sustav bi nam vratio popis svih funkcija povezanih s pretraživanjem u Excelu. VLOOKUP je drugi na popisu. Odaberite ga klikom OK .

funkcija Argumenti okvir pojavljuje se, potičući nas za sve argumente ( ili parametre ) potrebne kako bi se dovršila funkcija VLOOKUP.Možete misliti na ovaj okvir kao funkciju postavljajući nam sljedeća pitanja:

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

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

Dovršit ćemo argumente redom, od vrha do dna.

Prvi argument koji moramo dovršiti je Lookup_value argument. Funkcija nam treba da kažemo gdje treba pronaći jedinstveni identifikator( šifra stavke u ovom slučaju) da treba vratiti opis. Moramo odabrati kod stavke koji smo unijeli ranije( u A11).

Kliknite ikonu izbornika s desne strane prvog argumenta:

Zatim kliknite jednom na ćeliju koja sadrži šifru stavke( A11) i pritisnite Unesite :

Vrijednost "A11" umetnuta je u prvi argument.

Sada moramo unijeti vrijednost za table_array argument. Drugim riječima, moramo obavijestiti VLOOKUP gdje pronaći bazu podataka / popis. Kliknite ikonu izbornika pokraj drugog argumenta:

Sada pronađite bazu podataka / popis i odaberite cijeli popis - ne uključujući liniju zaglavlja .U našem primjeru, baza podataka se nalazi na zasebnom radnom listu, tako da najprije kliknemo na taj list radnog lista:

Zatim odaberemo cijelu bazu podataka, ne uključujući liniju zaglavlja:

. .. i pritisnite Unesite .Raspon ćelija koje predstavljaju bazu podataka( u ovom slučaju "Baza podataka"! A2: D7 ") unosi se automatski za nas u drugi argument.

Sada moramo unijeti treći argument, Col_index_num .Koristimo ovaj argument kako bismo naveli VLOOKUP koji podatak iz baze podataka, povezan s našim kodom stavke u A11, želimo se vratiti nama. U ovom primjeru želimo da se opis stavke vratio nama. Ako pogledate na radni list baze podataka, primijetit ćete da je stupac "Opis" drugi stupac u bazi podataka. To znači da moramo unijeti vrijednost "2" u Col_index_num okvir:

Važno je napomenuti da ovdje ne ulazimo u "2" jer je stupac "Opis" u B stupcutaj radni list. Ako se baza podataka započela u stupcu K iz radnog lista, još uvijek bi unijeli "2" u ovo polje jer je stupac "Opis" drugi stupac u skupu ćelija koje smo odabrali prilikom navođenja "Table_array".

Konačno, moramo odlučiti hoće li unijeti vrijednost u konačni argument VLOOKUP, Range_lookup .Ovaj argument zahtijeva ili true ili false vrijednost, ili bi trebalo ostati prazno. Kada koristite VLOOKUP s bazama podataka( kao što je istina 90% vremena), način odlučivanja o tome što treba staviti u ovaj argument može se misliti na sljedeći način:

Ako prvi stupac baze podataka( stupac koji sadrži jedinstvene identifikatore)je poredano abecednim redom / brojčano u uzlaznom redoslijedu, tada je moguće unijeti vrijednost prave u ovaj argument ili ostaviti prazno.

Ako je prvi stupac baze podataka nije razvrstan ili je razvrstan u silaznom redoslijedu, mora unijeti vrijednost false u ovaj argument

Budući da je prvi stupac naše baze podataka nije razvrstan,mi ulazimo lažno u ovaj argument:

To je to! Ušli smo sve informacije potrebne za VLOOKUP da bismo vratili potrebnu vrijednost. Kliknite gumb OK 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 razliku u kodu stavke u ćelijuA11, početi ćemo vidjeti snagu funkcije VLOOKUP: opis stanica se mijenja kako bi odgovarala novom šifru stavke:

Možemo izvršiti sličan skup koraka kako bismo dobili cijenu stavke vratio 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 dvije formule treći argument( Col_index_num ) promijenjen s "2" na "3"( jerželimo podatke preuzete iz trećeg stupca u bazi podataka).

Ako smo odlučili kupiti 2 od tih predmeta, unijeli bi "2" u ćeliju D11.Zatim ćemo unijeti jednostavnu formulu u ćeliju F11 kako bismo dobili ukupni redak:

= D11 * E11

. .. koji izgleda ovako. ..

Dovršetak predloška fakture

Do sada smo naučili mnogo o VLOOKUP-u. Zapravo smo naučili sve što ćemo naučiti u ovom članku. Važno je napomenuti da se VLOOKUP može koristiti u drugim okolnostima osim baza podataka. To je manje uobičajeno, a može se prikazati u budućim člancima Kako-To Geek.

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

1. Od koda A11 i "2" iz ćelije D11 uklonit ćemo šifru stavke uzorka. To će uzrokovati da naša novo stvorena formula VLOOKUP prikaže poruke o pogreškama:
   
   To možemo ukloniti razumnim korištenjem i ISBLANK() funkcija IF() .Izmijenimo formulu iz ove. .. = VLOOKUP( A11, 'Baza podataka'! A2: D7,2, FALSE) . .. na ovo. .. = IF( ISBLANK( A11), "VLOOKUP( A11,'! A2: D7,2, FALSE))
   
2. Bilo bi kopije formula u ćelijama B11, E11 i F11 do ostatka retka stavke fakture. Ako to učinimo, dobivene formule više se neće ispravno odnositi na tablicu baze podataka. Mogli bismo to riješiti promjenom reference stanica za bazu podataka apsolutnim referencama stanice. Alternativno - i još bolje - možemo stvoriti naziv naziva za cijelu bazu proizvoda( kao što su "Proizvodi") i upotrijebiti taj naziv raspon umjesto referenci za ćelije. Formula se mijenja iz ove. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Baza podataka'! A2: D7,2, FALSE)) . .. ovo. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Products, 2, FALSE)) . .. i zatim kopirajte formule na ostatak retka stavke fakture.
3. Vjerojatno bismo "zaključali" stanice koje sadrže naše formule( ili otključati druge ćelije), a zatim zaštititi radni list, kako bi se osiguralo da naše pažljivo konstruirane formule nisu slučajno prepisane kada netko dođe popunitina računu.
4. Spremili smo datoteku kao predložak , tako da bi ga mogli ponovno koristiti svi u našoj tvrtki

Ako bismo se osjećali stvarno pametnim, mi bismo stvorili bazu podataka svih naših kupaca u drugom radnom listu, a zatim koristitekorisnički ID unesen u ćeliju F5 da automatski popuni ime i adresu klijenta u ćelijama B6, B7 i B8.

Ako želite vježbati s VLOOKUP-om ili jednostavno pogledati našu predložak dostavnice, on se može preuzeti odavde.