VLOOKUP u Excelu, 2. dio Korištenje VLOOKUP-a bez baze podataka

U nedavnom članku predstavili smo funkciju Excel koja se zove VLOOKUP i objasnio kako se može koristiti za dohvaćanje informacija iz baze podataka u ćeliju lokalnog radnog lista. U tom smo članku spomenuli da postoje dva načina korištenja VLOOKUP-a, a samo se jedan bavio upitima baza podataka. U ovom članku, drugom i konačnom u nizu VLOOKUP, ispitujemo ovu drugu, manje poznatu uporabu za funkciju VLOOKUP.

Ako to već niste učinili, pročitajte prvi članak za VLOOKUP - ovaj članak pretpostavlja da su mnogi pojmovi objašnjeni u tom članku već poznati čitatelju.

Prilikom rada s bazama podataka, VLOOKUP se prosljeđuje “jedinstvenom identifikatoru” koji služi za identifikaciju podataka koji želimo pronaći u bazi podataka (npr. Kod proizvoda ili ID korisnika). Ovaj jedinstveni identifikator mora postoje u bazi podataka, inače nam VLOOKUP vraća pogrešku. U ovom članku ispitat ćemo način korištenja VLOOKUP-a gdje identifikator uopće ne mora postojati u bazi podataka. To je gotovo kao da VLOOKUP može usvojiti pristup “dovoljno blizu je dovoljno” za vraćanje podataka koje tražimo. U određenim okolnostima, to je točno što nam treba.

Ovaj ćemo članak ilustrirati primjerom iz stvarnog svijeta - izračunom provizija koje se generiraju na skupu prodajnih brojki. Započet ćemo s vrlo jednostavnim scenarijem, a zatim ga postepeno učiniti složenijim, dok jedino racionalno rješenje problema nije korištenje VLOOKUP-a. Početni scenarij u našoj fiktivnoj tvrtki radi ovako: ako prodavač u određenoj godini kreira više od 30.000 $ prodaje, provizija koju ostvaruju na toj prodaji iznosi 30%. Inače njihova provizija iznosi samo 20%. Do sada je ovo prilično jednostavan radni list:

Da bi koristio ovaj radni list, prodavač unosi svoje prodajne podatke u ćeliju B1, a formula u ćeliji B2 izračunava točnu stopu provizije koju imaju pravo primiti, koja se koristi u ćeliji B3 za izračunavanje ukupne provizije koju duguje prodavač ( je jednostavno množenje B1 i B2).

Stanica B2 sadrži jedini zanimljivi dio ovog radnog lista - formulu za odlučivanje koja će se stopa provizije koristiti: ona ispod prag od 30.000 dolara ili jedan iznad praga. Ova formula koristi Excelovu funkciju AKO. Za one čitatelje koji nisu upoznati s IF, to radi ovako:

AKO(uvjet, vrijednost ako je true, vrijednost ako je false)

Gdje je stanje je izraz koji se procjenjuje na oba pravi ili lažan. U gornjem primjeru, stanje je izraz B1, koji se može čitati kao "Je li B1 manji od B5?", ili, drugačije rečeno, "Je li ukupna prodaja manja od praga". Ako je odgovor na ovo pitanje "da" (istina), onda koristimo vrijednost ako je istinita parametar funkcije, naime B6 u ovom slučaju - stopa provizije ako je ukupna prodaja bila ispod praga. Ako je odgovor na pitanje "ne" (netočno), onda koristimo vrijednost ako je lažna parametar funkcije, naime B7 u ovom slučaju - stopa provizije ako je ukupna prodaja bila iznad praga.

Kao što možete vidjeti, korištenje ukupne prodaje od 20.000 dolara daje nam proviziju od 20% u ćeliji B2. Ako unesemo vrijednost od 40.000 USD, dobit ćemo drugačiju stopu provizije:

Tako naša proračunska tablica radi.

Neka bude složenije. Uvedimo drugi prag: ako prodavač zarađuje više od 40.000 USD, tada se njihova provizija povećava na 40%:

Dovoljno lako razumjeti u stvarnom svijetu, ali u ćeliji B2 naša formula postaje sve složenija. Ako pažljivo pogledate formulu, vidjet ćete da je treći parametar izvorne IF funkcije ( vrijednost ako je lažna) sada je cijeli IF djeluje samostalno. To se naziva a ugniježđena funkcija (funkcija unutar funkcije). Savršeno vrijedi u Excelu (čak i radi!), Ali je teže čitati i razumjeti.

Nećemo ulaziti u matice i razloge kako i zašto to radi, niti ćemo ispitivati nijanse ugniježđenih funkcija. Ovo je udžbenik za VLOOKUP, a ne za Excel općenito.

U svakom slučaju, pogoršava se! Što je s tim kada odlučimo da ako zaradite više od 50.000 $ onda imaju pravo na 50% provizije, a ako zarade više od 60.000 $ onda imaju pravo na proviziju od 60%?

Sada je formula u ćeliji B2, iako točna, postala praktično nečitljiva. Nitko ne bi trebao napisati formule u kojima su funkcije ugniježđene u četiri razine! Sigurno mora postojati jednostavniji način?

Sigurno postoji. VLOOKUP za spašavanje!

Redizajniramo radni list malo. Zadržat ćemo sve iste brojke, ali organizirati ih na novi način, više tabličast put:

Odvojite trenutak i uvjerite se da je novo Tablica stopa radi točno kao niz gore navedenih pragova.

Konceptualno, ono što ćemo učiniti jest iskoristiti VLOOKUP za pretraživanje ukupne prodaje prodavača (iz B1) u tablici stopa i vratiti nam odgovarajuću stopu provizije. Imajte na umu da je prodavač doista stvorio prodaju ne jedna od pet vrijednosti u tablici stopa ($ 0, $ 30,000, $ 40,000, $ 50,000 ili $ 60,000). Možda su ostvarili prodaju od 34.988 USD. Važno je napomenuti da je $ 34,988 ne pojavljuju se u tablici stopa. Da vidimo može li VLOOKUP ionako riješiti naš problem ...

Odabiremo stanicu B2 (mjesto na koje želimo staviti našu formulu), a zatim umetnemo funkciju VLOOKUP iz formule kartica:

Argumenti funkcije pojavljuje se okvir za VLOOKUP. Argumente (parametre) popunjavamo jedan po jedan, počevši od tražena_vrijednost, što je u ovom slučaju ukupna prodaja iz ćelije B1. Postavljamo pokazivač u tražena_vrijednost zatim kliknite jednom na ćeliju B1:

Zatim moramo odrediti VLOOKUP-u koju tablicu tražiti ove podatke. U ovom primjeru, naravno, to je tablica stopa. Postavljamo pokazivač u Table_array i označite cijelu tablicu stopa - isključujući naslove:

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

Konačno unosimo vrijednost u range_lookup polje.

Važno: Upotreba ovog polja razlikuje dva načina korištenja VLOOKUP-a. Da biste koristili VLOOKUP s bazom podataka, ovaj konačni parametar, range_lookup, uvijek mora biti postavljeno na NETOČNO, ali s ovom drugom upotrebom VLOOKUP-a, moramo ili ostaviti prazno ili unijeti vrijednost od PRAVI. Prilikom korištenja VLOOKUP-a važno je da napravite ispravan izbor za ovaj konačni parametar.

Da bismo bili eksplicitni, unijet ćemo vrijednost od pravi u range_lookup polje. Također bi bilo u redu ostaviti ga praznim, jer je to zadana vrijednost:

Ispunili smo sve parametre. Sada kliknemo u redu i Excel gradi našu VLOOKUP formulu za nas:

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

Zaključak

U verziji "baze podataka" VLOOKUP, gdje je range_lookup parametar je NETOČNO, vrijednost proslijeđena u prvom parametru (tražena_vrijednost) mora biti prisutni u bazi podataka. Drugim riječima, tražimo točno podudaranje.

No, u ovom drugom korištenju VLOOKUP-a, ne nužno tražimo točno podudaranje. U ovom slučaju, “dovoljno blizu je dovoljno”. Ali što mislimo pod "dovoljno blizu"? Uzmimo primjer: Kada tražimo stopu provizije na ukupnoj prodaji od 34,988 dolara, naša 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” u ovom slučaju? Budimo precizni:

Kada range_lookup je postavljeno na PRAVI (ili izostavljeno), VLOOKUP će gledati u stupcu 1 i podudara se najveća vrijednost koja nije veća od tražena_vrijednost parametar.

Također je važno napomenuti da ovaj sustav funkcionira, tablica mora biti poredana uzlazno po stupcu 1!

Ako želite vježbati s VLOOKUP-om, primjerak datoteke prikazan u ovom članku možete preuzeti ovdje.