sadržaj
Nedavno smo posvetili jedan članak jednoj od najkorisnijih Excel funkcija tzv VPR i pokazao kako se može koristiti za izdvajanje potrebnih informacija iz baze podataka u ćeliju radnog lista. Također smo spomenuli da postoje dva slučaja korištenja funkcije VPR i samo jedan od njih se bavi upitima baze podataka. U ovom članku ćete naučiti još jedan manje poznat način korištenja funkcije VPR u Excelu.
Ako to još niste učinili, svakako pročitajte posljednji članak o funkciji VPR, jer sve informacije u nastavku pretpostavljaju da ste već upoznati s principima opisanim u prvom članku.
Kada radite sa bazama podataka, funkcije VPR prosljeđuje se jedinstveni identifikator koji se koristi za identifikaciju informacija koje želimo pronaći (na primjer, kod proizvoda ili identifikacijski broj kupca). U suprotnom, ovaj jedinstveni kod mora biti prisutan u bazi podataka VPR prijavit će grešku. U ovom članku ćemo pogledati ovaj način korištenja funkcije VPRkada id uopće ne postoji u bazi podataka. Kao da je funkcija VPR prebacuje se na približni način rada i bira koje podatke će nam dati kada želimo nešto pronaći. U određenim okolnostima, to je upravo ono što je potrebno.
Primjer iz života. Postavili smo zadatak
Ilustrujmo ovaj članak primjerom iz stvarnog života – izračunavanjem provizija na osnovu širokog spektra prodajnih metrika. Počećemo s vrlo jednostavnom opcijom, a zatim ćemo je postepeno komplikovati sve dok jedino racionalno rješenje problema ne bude korištenje funkcije VPR. Početni scenario za naš fiktivni zadatak je sljedeći: ako prodavač zaradi više od 30000 dolara u prodaji godišnje, onda njegova provizija iznosi 30%. Inače, provizija je samo 20%. Stavimo to u obliku tabele:
Prodavac unosi svoje podatke o prodaji u ćeliju B1, a formula u ćeliji B2 određuje ispravnu stopu provizije koju prodavač može očekivati. Zauzvrat, rezultujuća stopa se koristi u ćeliji B3 za izračunavanje ukupne provizije koju bi prodavac trebao dobiti (jednostavno množenje ćelija B1 i B2).
Najzanimljiviji dio tabele nalazi se u ćeliji B2 – to je formula za određivanje stope provizije. Ova formula sadrži Excel funkciju pod nazivom IF (IF). Za one čitatelje koji nisu upoznati s ovom funkcijom, objasnit ću kako ona funkcionira:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
stanje je argument funkcije koji uzima vrijednost bilo koje TRUE CODE (TAČNO), ili FALSE (LAŽNO). U gornjem primjeru, izraz B1
Da li je tačno da je B1 manji od B5?
Ili možete reći drugačije:
Da li je tačno da je ukupan iznos prodaje za godinu manji od granične vrijednosti?
Ako odgovorimo na ovo pitanje DA (TRUE), tada se funkcija vraća vrijednost ako je istina (vrijednost ako je TRUE). U našem slučaju, to će biti vrijednost ćelije B6, odnosno stopa provizije kada je ukupna prodaja ispod praga. Ako odgovorimo na pitanje NE (FALSE) zatim se vraća vrijednost ako je netačna (vrijednost ako je FALSE). U našem slučaju, to je vrijednost ćelije B7, odnosno stopa provizije kada je ukupna prodaja iznad praga.
Kao što vidite, ako uzmemo ukupnu prodaju od 20000$, dobićemo stopu provizije od 2% u ćeliji B20. Ako unesemo vrijednost od $40000, tada će se stopa provizije promijeniti za 30%:
Ovako funkcioniše naš sto.
Komplikujemo zadatak
Hajde da malo otežamo stvari. Postavimo još jedan prag: ako prodavač zaradi više od 40000 USD, tada se stopa provizije povećava na 40%:
Čini se da je sve jednostavno i jasno, ali naša formula u ćeliji B2 postaje primjetno složenija. Ako pažljivo pogledate formulu, vidjet ćete da je treći argument funkcije IF (IF) se pretvorio u još jednu punopravnu funkciju IF (IF). Ova konstrukcija se zove ugniježđenje funkcija jedne u drugu. Excel rado dozvoljava ove konstrukcije, čak i rade, ali ih je mnogo teže čitati i razumjeti.
Nećemo ulaziti u tehničke detalje – zašto i kako to funkcionira, a nećemo ulaziti ni u nijanse pisanja ugniježđenih funkcija. Uostalom, ovo je članak posvećen funkciji VPR, nije potpuni vodič za Excel.
U svakom slučaju, formula postaje složenija! Šta ako uvedemo još jednu opciju za stopu provizije od 50% za one prodavce koji zarađuju više od 50000 dolara u prodaji. A ako je neko prodao više od 60000$, da li će platiti 60% provizije?
Sada je formula u ćeliji B2, čak i ako je napisana bez grešaka, postala potpuno nečitljiva. Mislim da je malo onih koji žele koristiti formule sa 4 nivoa ugniježđenja u svojim projektima. Mora da postoji lakši način?!
A postoji i takav način! Funkcija će nam pomoći VPR.
Za rješavanje problema primjenjujemo funkciju VLOOKUP
Hajde da malo promijenimo dizajn našeg stola. Zadržat ćemo sva ista polja i podatke, ali ih urediti na novi, kompaktniji način:
Odvojite trenutak i provjerite novi sto Tabela stopa uključuje iste podatke kao prethodna tabela pragova.
Glavna ideja je korištenje funkcije VPR da se odredi željeni tarifni stav prema tabeli Tabela stopa zavisno od obima prodaje. Imajte na umu da prodavac može prodati robu za iznos koji nije jednak jednom od pet pragova u tabeli. Na primjer, mogao bi prodati za 34988 dolara, ali ne postoji takav iznos. Pogledajmo kako funkcionira VPR može da se nosi sa takvom situacijom.
Umetanje funkcije VLOOKUP
Odaberite ćeliju B2 (gdje želimo umetnuti našu formulu) i pronađite VLOOKUP (VLOOKUP) u biblioteci Excel funkcija: formule (formule) > Library Library (Biblioteka funkcija) > Pregled i reference (Reference i nizovi).
Pojavljuje se dijaloški okvir Argumenti funkcije (Argumenti funkcije). Vrijednosti argumenata popunjavamo jedan po jedan, počevši od Lookup_value (Lookup_value). U ovom primjeru, ovo je ukupan iznos prodaje iz ćelije B1. Stavite kursor u polje Lookup_value (Lookup_value) i odaberite ćeliju B1.
Zatim morate odrediti funkcije VPRgdje tražiti podatke. U našem primjeru, ovo je tabela Tabela stopa. Stavite kursor u polje Table_array (Tabela) i odaberite cijelu tabelu Tabela stopaosim zaglavlja.
Zatim moramo odrediti koji stupac ćemo izdvojiti podatke iz naše formule. Zanima nas stopa provizije koja se nalazi u drugoj koloni tabele. Dakle, za argument Col_index_num (Broj_kolone) unesite vrijednost 2.
I na kraju, uvodimo posljednji argument – Range_lookup (Interval_lookup).
Bitan: upotreba ovog argumenta čini razliku između dva načina primjene funkcije VPR. Kada radite sa bazama podataka, argument Range_lookup (range_lookup) uvijek mora imati vrijednost FALSE (FALSE) za traženje tačnog podudaranja. U našoj upotrebi funkcije VPR, moramo ostaviti ovo polje praznim ili unijeti vrijednost TRUE CODE (TAČNO). Izuzetno je važno pravilno odabrati ovu opciju.
Da bude jasnije, predstavićemo TRUE CODE (TRUE) na terenu Range_lookup (Interval_lookup). Iako, ako ostavite polje prazno, to neće biti greška, jer TRUE CODE je njegova zadana vrijednost:
Popunili smo sve parametre. Sada pritiskamo OK, a Excel kreira formulu za nas pomoću funkcije VPR.
Ako eksperimentiramo s nekoliko različitih vrijednosti za ukupan iznos prodaje, tada ćemo se uvjeriti da formula radi ispravno.
zaključak
Kada je funkcija VPR radi sa bazama podataka, argument Range_lookup (range_lookup) mora prihvatiti FALSE (LAŽNO). I vrijednost unesena kao Lookup_value (Lookup_value) mora postojati u bazi podataka. Drugim riječima, traži tačno podudaranje.
U primjeru koji smo pogledali u ovom članku, nema potrebe za točno podudaranjem. To je slučaj kada je funkcija VPR mora se prebaciti na približni način rada kako bi se dobio željeni rezultat.
Na primjer: Želimo da odredimo koju stopu da koristimo u obračunu provizije za prodavca sa obimom prodaje od 34988 USD. Funkcija VPR vraća nam vrijednost od 30%, što je apsolutno tačno. Ali zašto je formula odabrala red koji sadrži tačno 30%, a ne 20% ili 40%? Šta se podrazumijeva pod približnim pretraživanjem? Da budemo jasni.
Kada argument Range_lookup (interval_lookup) ima vrijednost TRUE CODE (TRUE) ili izostavljena, funkcija VPR prolazi kroz prvu kolonu i bira najveću vrijednost koja ne prelazi vrijednost traženja.
Važna točka: Da bi ova šema funkcionirala, prva kolona tabele mora biti sortirana uzlaznim redoslijedom.