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. Napravit ćemo korisnu 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 a baza podataka funkcija, što znači da radi s tablicama baze podataka - ili jednostavno, arena stvari u radnom listu Excel. Kakve stvari? Dobro, bilo koji vrsta stvari. 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 sadržavati stupac koji sadrži jedinstveni identifikator (ili "ključ" ili "ID") i 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 ovo najprije jasno vidjeti:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
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 "u zalihama") kako je opisano u izvorniku popis. Koji od ovih dijelova informacija će vas vratiti natrag? Pa, odlučite to kad izrađujete formulu.
Ako vam je potreban samo jedan podatak iz baze podataka, bilo bi puno poteškoća u izradi 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 toga: An Predložak fakture da možemo ponovo upotrijebiti u našoj fiktivnoj tvrtki.
Prvo ćemo pokrenuti Excel, a sami stvorimo prazan račun:
U svrhu održavanja ovog primjera jednostavna ćemo pronaći bazu podataka proizvoda u zasebnom listu u istoj radnoj knjizi:
Stoga smo izradili bazu podataka o proizvodu, koja izgleda ovako:
Moramo pronaći popis svih dostupnih funkcija koje Excel nudi, tako da možemo odabrati VLOOKUP i dobiti pomoć u popunjavanju formule. Ovo se pronalazi prvo klikom na gumb formule karticu, a zatim kliknite Umetnite funkciju:
Da biste pronašli onu koju tražimo, mogli bismo upisati pojam za pretraživanje poput "pretraživanja" (jer je funkcija o kojoj se zanima Pogledaj funkcija). Sustav bi nam vratio popis svih funkcija povezanih s pretraživanjem u Excelu. VLOOKUP je drugi na popisu. Odaberite ga klikom u redu.
Argumentni argumenti pojavljuje se okvir, koji nas potiče za sve argumenti (ili parametri) potrebna kako bi se dovršila funkcija VLOOKUP. Ovu kutiju možete zamisliti kao funkciju koja nas postavlja na sljedeća pitanja:
- Kakav jedinstveni identifikator tražite u bazi podataka?
- Gdje je baza podataka?
- Koji podatak iz baze podataka, povezan s jedinstvenim identifikatorom, želiš dobiti za vas?
Prikazani su prva tri argumenata podebljano, što ukazuje da jesu obavezan argumente (funkcija VLOOKUP je nepotpuna bez njih i neće vraćati važeću vrijednost). Četvrti argument nije podebljan, što znači da je izborno:
Prvi argument koji moramo dovršiti jest tražena_vrijednost argument. Funkcija nam treba da kažemo gdje pronaći jedinstveni identifikator ( Šifra artikla 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 Unesi:
Sada moramo unijeti vrijednost za Table_array argument. Drugim riječima, moramo obavijestiti VLOOKUP gdje pronaći bazu podataka / popis. Kliknite ikonu izbornika pored drugog argumenta:
… i pritisnite Unesi, 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 imati stavku opis vratili su nam se. Ako pogledate na radni list baze, primijetit ćete da je stupac "Opis" drugi stupac u bazi podataka. To znači da moramo unijeti vrijednost "2" u Col_index_num kutija:
Važno je napomenuti da ovdje ne ulazimo u "2" jer je stupac "Opis" u B stupca na tom radnom listu. Ako se baza podataka započela u stupcu K u radnom listu, još uvijek bi unijeli "2" u ovo polje jer je stupac "Opis" drugi stupac u skupu stanica 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 a pravi ili lažan vrijednost, ili bi trebalo ostati prazno. Prilikom korištenja VLOOKUP-a s bazama podataka (kao što je istina 90% vremena), način odlučivanja što se može staviti u ovaj argument može se smatrati sljedećim:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Kao prvi stupac naše baze podataka ne sortirano, ulazimo lažan u ovaj argument:
To je to! Ušli smo sve informacije potrebne za VLOOKUP da bismo vratili potrebnu vrijednost. Kliknite gumb u redu i primijetite da je opis koji odgovara kodu stavke "R99245" ispravno unesen u ćeliju B11:
Ako uđemo u različit šifru stavke u ćeliju A11, početi ćemo vidjeti snagu funkcije VLOOKUP: opis stanica se mijenja kako bi odgovarala novom šifru stavke:
Možemo izvršiti sličan korak kako bismo dobili stavku cijena vratio se u ćeliju E11. Imajte na umu da se nova formula mora stvoriti u ćeliji E11. Rezultat će izgledati ovako:
Imajte na umu da je jedina razlika između dvije formule treći argument (Col_index_num) promijenio se s "2" na "3" (jer želimo da podaci dobiveni iz 3. stupca u bazi podataka).
Ako smo odlučili kupiti 2 od tih stavki, unijeli bi "2" u ćeliju D11. Zatim ulazimo u jednostavnu formulu u ćeliju F11 kako bismo dobili ukupni redak:
=D11*E11
… koja izgleda ovako …
Dovršetak predloška fakture
Do sada smo naučili puno 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:
-
Uklonit ćemo kodni broj uzorka iz ćelije A11 i "2" iz ćelije D11. To će uzrokovati da naša novo stvorena formula VLOOKUP prikaže poruke o pogreškama:
Image To možemo ukloniti razumnim korištenjem Excela AKO() i ISBLANK () funkcije. Iz toga promijenimo svoju formulu … = VLOOKUP (A11, 'Baza podataka'! A2: D7,2, FALSE) … ovom … = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Baza podataka'! A2: D7,2, FALSE))
- Sastavit ćemo formulare u ćelijama B11, E11 i F11 sve do ostalog redaka stavke fakture. Ako to učinimo, dobivene formule više se neće ispravno odnositi na tablicu baze podataka. Mogli bismo to riješiti mijenjanjem ćelije reference za bazu podataka apsolutan stanične reference. Alternativno - i još bolje - možemo stvoriti a naziv raspona za cijelu bazu podataka proizvoda (kao što su "Proizvodi") i upotrijebite taj naziv raspona umjesto staničnih referencija. Formula bi se mijenjala iz ovog … = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Baza podataka'! A2: D7,2, FALSE)) … ovom … = IF (ISBLANK (A11),””, VLOOKUP (A11, Products, 2, FALSE)) …i zatim kopirajte formule do ostalog redaka stavki fakture.
- Vjerojatno bismo "zaključali" stanice koje sadrže naše formule (ili bolje otključati drugo ćelije), a zatim zaštitite radni list, kako bi se osiguralo da se naše pažljivo konstruirane formule ne slučajno prekriju kada netko dođe da popuni fakturu.
- Spremili smo datoteku kao a predložak, kako bi ga svi mogli koristiti u našoj tvrtki
Kad bismo se osjećali stvarno pametno ćemo izraditi bazu podataka svih naših kupaca u drugom radnom listu, a zatim upotrijebiti korisnički ID unesen u ćeliju F5 da automatski popuni ime i adresu klijenta u ćelijama B6, B7 i B8.
