U nedavnom članku predstavili smo funkciju Excel VLOOKUP i objasnio kako se 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.
Prilikom rada 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 postoje 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, to je točno ono što nam treba.
Ilustriramo ovaj članak sa stvarnim primjerom - računom komisija koje se generiraju na skupu prodajnih veličina. 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 bi se ovaj radni list koristio, prodavač unosi svoje prodajne brojke u ćeliju B1, a formula u ćeliji B2 izračunava ispravnu stopu provizije koju imaju, a koristi se u ćeliji B3 za izračunavanje ukupne provizije koju prodavač duguje (koji je jednostavna množenja B1 i B2).
Stanica B2 sadrži jedini zanimljiv dio ovog radnog lista - formula za odlučivanje o stopi provizije koju treba koristiti: onu ispod prag od 30.000 dolara ili jedan iznad prag. Ova formula koristi naziv funkcije Excel AKO, Za one čitatelje koji nisu upoznati sa IF, to ovisi:
AKO(uvjet, vrijednost ako je istina, vrijednost ako je netočna)
Gdje je stanje je izraz koji ocjenjuje bilo pravi ili lažan, U gornjem primjeru, stanje je izraz B1<>, koji se može čitati kao "Je li B1 manji od B5?" ili, na drugi način, "Jesu li ukupna prodaja manja od praga". Ako je odgovor na ovo pitanje "yes" (true), onda koristimo vrijednost ako je istinita parametar funkcije, naime B6 u ovom slučaju - stopa provizije ako je ukupni promet bio ispod prag. Ako je odgovor na pitanje "ne" (lažan), onda koristimo vrijednost ako je netočna parametar funkcije, naime B7 u ovom slučaju - stopa provizije ako je ukupni promet bio iznad prag.
Kao što vidite, upotreba 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š proračunska tablica radi.
Neka je to složenija. Uvedimo drugi prag: ako prodavač zarađuje više od 40.000 USD, tada njihova provizijska stopa povećava se na 40%:
Dovoljno je lako shvatiti 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 netočna) je sada cjelovita IF funkcija u svom vlastitom pravu. Ovo se zove a 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.
U svakom slučaju, to 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 ispravna, 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?
Sigurno postoji. VLOOKUP u spašavanje!
Redizajnamo radni list malo. Zadržat ćemo sve iste figure, ali organizirati ih na nov način, još više tabličast put:
Odvojite malo vremena i provjerite da li ste novi Ocijenite tablicu djeluje točno kao i niz pragova gore.
Konceptualno, ono što namjeravamo učiniti jest korištenje VLOOKUP-a da potražimo ukupni prodajni prodavač (iz B1) u tablici s cijenama i vratimo nam odgovarajuću stopu provizije. Imajte na umu da je prodavač možda doista stvorio prodaju koje su ne jedna od pet vrijednosti u tablici stope ($ 0, $ 30,000, $ 40,000, $ 50,000 ili $ 60,000). Možda su ostvarili prodaju od 34.988 USD. Važno je napomenuti da vrijedi 34.988 dolara ne pojavljuju se u tablici stope. Da vidimo može li VLOOKUP ipak riješiti naš problem ...
Odabiremo B2 stanicu (mjesto na kojem želimo staviti našu formulu), a zatim umetnemo funkciju VLOOKUP iz formule kartica:
Argumentni argumenti pojavit će se okvir za VLOOKUP. Ispunjavamo argumente (parametre) jedan po jedan, počevši od tražena_vrijednost, što je, u ovom slučaju, ukupni promet od stanice B1. Postavljamo pokazivač u tražena_vrijednost polje, a zatim jednom kliknite na ćeliju B1:
Zatim moramo navesti da VLOOKUP tabelu za pretraživanje ovih podataka. U ovom primjeru, naravno, to je stopa tablice. Postavljamo pokazivač u Table_array polje, a zatim označite cijelu tablicu stope - bez naslova:
Zatim moramo navesti koji stupac u tablici sadrži podatke koje želimo da se formula za nas vrati. U ovom slučaju želimo da se stopa provizije, koja se nalazi u drugom stupcu u tablici, stoga ulazimo u 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. Da biste koristili VLOOKUP s bazom podataka, ovaj konačni parametar, range_lookup, mora uvijek biti postavljen na NETOČNO, ali s tom drugom upotrebom VLOOKUP-a moramo ostaviti prazno ili unijeti vrijednost PRAVI, Kada koristite VLOOKUP, bitno je da ispravno odaberete 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 prazno, jer je to zadana vrijednost:
Dovršili smo sve parametre. Sada kliknemo u redu gumb i Excel izrađuje našu VLOOKUP formulu za nas:
Ako eksperimentirate s nekoliko različitih prodajnih ukupnih iznosa, možemo se uvjeriti da formula funkcionira.
Zaključak
U verziji "baze podataka" VLOOKUP, gdje je range_lookup parametar je NETOČNO, vrijednost je prošla u prvom parametru (tražena_vrijednost) mora biti prisutan u bazi podataka. Drugim riječima, tražimo točno podudaranje.
Ali 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:
Kada range_lookup postavljena je na PRAVI (ili izostavljen), VLOOKUP će izgledati u stupcu 1 i odgovarati najveća vrijednost koja nije veća od tražena_vrijednost parametar.
Također je važno napomenuti da za rad ovog sustava, tablica mora biti poredana uzastopnim redoslijedom na stupcu 1!
Ako želite vježbati s VLOOKUP, primjerak datoteke ilustrirane u ovom članku može se preuzeti odavde.
