VLOOKUP u programu Excel, 2. dio: Korištenje VLOOKUP bez baze podataka

U nedavnom članku predstavili smo funkciju programa Excel pod nazivom VLOOKUP i objasnili kako se ona može koristiti za preuzimanje podataka iz baze podataka u ćeliju u lokalnom radnom listu. U tom smo članku naveli da postoje dvije upotrebe za VLOOKUP, a samo je jedna od njih bavila se upitnim bazama podataka. U ovom članku, drugom i konačnom u seriji VLOOKUP, istražujemo ovu drugu, manje poznatu uporabu za funkciju VLOOKUP.

Ako to već niste učinili, pročitajte prvi članak VLOOKUP - ovaj će članak pretpostaviti da su mnogi od pojmova objašnjeni u tom članku već poznati čitatelju.

Kada radite s bazama podataka, VLOOKUP je prošao "jedinstveni identifikator" koji služi za identifikaciju podataka koji želimo pronaći u bazi podataka( npr. Kod proizvoda ili korisnički ID).Ovaj jedinstveni identifikator mora sadržavati u bazi podataka, inače VLOOKUP vraća nam pogrešku. U ovom članku ispitat ćemo način korištenja VLOOKUP-a gdje identifikator uopće ne mora postojati u bazi podataka. Gotovo je kao da VLOOKUP može usvojiti "dovoljno blizu dovoljno dobar" pristup vraćanju podataka koje tražimo. U određenim okolnostima, ovo je

točno ono što nam je potrebno.

Ilustriramo ovaj članak sa stvarnim primjerom - računom komisija koje se generiraju na skupu prodajnih podataka. Započeti ćemo s vrlo jednostavnim scenarijem, a zatim ga progresivno učiniti složenijim, sve dok jedino racionalno rješenje problema ne bude korištenje VLOOKUP-a. Početni scenarij u našoj fiktivnoj tvrtki funkcionira ovako: Ako prodavač u određenoj godini stvara više od 30.000 dolara prodaje, provizija koju oni zarađuju na tim prodajama iznosi 30%.Inače njihova provizija iznosi samo 20%.Do sada je ovo prilično jednostavan radni list:

Da biste koristili ovaj radni list, prodavač unosi svoje prodajne brojke u ćeliju B1, a formula u ćeliji B2 izračunava ispravnu stopu provizije koju imaju pravo primiti, a koji se koristi u ćeliji B3 za izračunavanjeukupna komisija koju duguje prodavač( što je jednostavna množenja B1 i B2).

Stanica B2 sadrži jedini zanimljiv dio ovog radnog lista - formula za odlučivanje kojom se stopom provizije koristi: onaj ispod prag od 30.000 dolara ili jedan iznad prag. Ova formula koristi Excel funkciju pod nazivom IF .Za one čitače koji nisu upoznati s IF, to funkcionira ovako:

IF( uvjet, vrijednost ako je istinita, vrijednost ako je lažna )

Gdje je uvjet izraz koji vrednuje pravi ili lažno ,U gornjem primjeru, uvjet je izraz B1 , koji se može čitati kao "Je li B1 manji od B5?" Ili, na drugi način, "Je li ukupna prodaja manja od praga".Ako je odgovor na to pitanje "yes"( istinit), onda koristimo vrijednost ako je pravi parametar funkcije, naime B6 u ovom slučaju - stopa provizije ako je ukupni prodajni iznos ispod prag. Ako je odgovor na pitanje "ne"( lažan), onda koristimo vrijednost ako je false parametar funkcije, naime B7 u ovom slučaju - stopa provizije ako je prodajni iznos bio iznad prag.

Kao što možete vidjeti, korištenje prodajnog iznosa od 20.000 USD daje nam stopu provizije od 20% u ćeliji B2.Ako unesemo vrijednost od 40.000 USD, dobivamo različitu stopu provizije:

Dakle, naša proračunska tablica radi.

Dopustimo da bude složeniji. Uvožimo drugi prag: ako prodavač zarađuje više od 40.000 dolara, njihova stopa provizije povećava se na 40%:

Dovoljno je jednostavno razumjeti u stvarnom svijetu, ali u ćeliji B2 formula postaje sve složenija. Ako pomno pogledate ovu formulu, vidjet ćete da je treći parametar izvornog IF funkcije( vrijednost ako je lažan ) sada u cijelosti funkcionira kao IF.To se zove ugniježđena funkcija ( funkcija unutar funkcije).To je savršeno valjana u Excelu( to čak radi!), Ali je teže čitati i razumjeti.

Nećemo ući u matice i vijke kako i zašto to funkcionira, niti ćemo ispitati nijanse ugnjetenih funkcija. Ovo je vodič za VLOOKUP, a ne na Excel uopće.

Ipak, sve se pogoršava!Što kada odlučimo da ako zarade više od 50.000 dolara onda imaju pravo na 50% provizije, a ako zarade više od 60.000 dolara onda imaju pravo na 60% provizije?

Sada formula u ćeliji B2, dok je točna, postala je gotovo nečitljiva. Nitko ne bi trebao napisati formule gdje su funkcije duboko uglavljene na četiri razine! Sigurno mora postojati jednostavniji način? Tu je sigurno. VLOOKUP u spašavanje!

Pokušajmo redizajnirati radni list malo. Zadržat ćemo sve iste figure, ali ih organizirati na nov način, više tabularni način:

Odvojite trenutak i uvjerite se da je nova tablica stanja jednaka onoj s nizom gornjih granica. Konceptualno, ono što namjeravamo učiniti jest koristiti VLOOKUP kako bismo potražili ukupni prodajni prodavatelj( iz B1) u tablici s cijenama i vratili nam odgovarajuću stopu provizije. Imajte na umu da je prodavač doista stvorio prodaju koje nisu ili jedna od pet vrijednosti u tablici s cijenama( $ 0, $ 30,000, $ 40,000, $ 50,000 ili $ 60,000).Možda su ostvarili prodaju od 34.988 USD.Važno je napomenuti da se 34,988 dolara ne prikazuje ne u tablici s cijenama. Pogledajmo hoće li VLOOKUP ipak riješiti naš problem. ..

Odabrali smo ćeliju B2( mjesto na kojem želimo staviti našu formulu), a zatim umetnemo funkciju VLOOKUP na karticu formule :

Značajka funkcije Argumenti pojavljuje se okvir za VLOOKUP.Argumente( parametre) ispunjavamo jedan po jedan, počevši od vrijednosti Lookup_value , što je u ovom slučaju prodajni iznos od stanice B1.Stavljamo pokazivač u polje Lookup_value , a zatim kliknite jednom na ćeliju B1:

Dalje moramo navesti da VLOOKUP tablici za koje treba tražiti ove podatke. U ovom primjeru to je stope tablice, naravno. Stavljamo pokazivač u polje Table_array , a zatim istaknimo cijelu tablicu stope - bez naslova :

Zatim moramo odrediti koji stupac u tablici sadrži podatke koje želimo da nam se formula vrati. U ovom slučaju želimo proviziju provizije, koja se nalazi u drugom stupcu u tablici pa stoga unosimo 2 u polje Col_index_num :

Konačno unosimo vrijednost u polje Range_lookup .

Važno: Upotreba ovog polja razlikuje dva načina upotrebe VLOOKUP.Da biste koristili VLOOKUP s bazom podataka, ovaj konačni parametar, Range_lookup , mora uvijek biti postavljen na FALSE , ali s tom drugom upotrebom VLOOKUP-a moramo ostaviti prazan ili unijeti vrijednost TRUE .Kada koristite VLOOKUP, bitno je da ispravno odaberete ovaj konačni parametar.

Da budemo eksplicitni, unijet ćemo vrijednost true u polju Range_lookup .Također bi bilo dobro ostaviti prazno, jer je to zadana vrijednost:

Dovršili smo sve parametre. Sada kliknemo gumb OK i Excel oblikuje našu VLOOKUP formulu za nas:

Ako eksperimentiramo s nekoliko različitih prodajnih ukupnih iznosa, možemo se uvjeriti da formula funkcionira.

Zaključak

U bazi podataka "VLOOKUP", gdje parametar Range_lookup je FALSE , vrijednost prenesenog u prvom parametru( Lookup_value ) mora biti prisutna u bazi podataka. Drugim riječima, tražimo točno podudaranje. Ipak, u ovoj drugoj upotrebi VLOOKUP-a ne tražimo točno podudaranje. U ovom slučaju, "dovoljno blizu je dovoljno dobro".Ali što mislimo pod "dovoljno blizu"?Koristimo primjer: pri traženju provizije na prodajnom iznosu od 34.988 USD, formula VLOOKUP će nam vratiti vrijednost od 30%, što je točan odgovor. Zašto je odabrao redak u tablici koji sadrži 30%?Što zapravo znači "dovoljno blizu" znači u ovom slučaju? Budimo precizni:

Kad je Range_lookup postavljen na TRUE ( ili izostavljen), VLOOKUP će izgledati u stupcu 1 i podudarati sa najvećom vrijednošću koja nije veća od parametra Lookup_value .

Također je važno napomenuti da za rad ovog sustava tablica mora biti poredana po uzlaznom redoslijedu na stupcu 1 !

Ako želite vježbati s VLOOKUP, primjerak datoteke ilustrirane u ovom članku može se preuzeti odavde.