Sadržaj
Nedavno smo posvetili č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 upotrebe funkcije VPR a samo jedan od njih bavi se upitima baze podataka. U ovom ćete članku naučiti još jedan manje poznat način korištenja funkcije VPR u programu Excel.
Ako to još niste učinili, svakako pročitajte zadnji članak o funkciji VPR, jer sve informacije u nastavku pretpostavljaju da ste već upoznati s načelima opisanim u prvom članku.
Pri radu s 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). Ovaj jedinstveni kod mora biti prisutan u bazi podataka, inače VPR će prijaviti grešku. U ovom članku ćemo pogledati ovaj način korištenja funkcije VPRkada ID uopće ne postoji u bazi podataka. Kao da funkcija VPR prešao na aproksimativni način rada i odabire koje će nam podatke dati kada želimo nešto pronaći. U određenim okolnostima, to je upravo ono što je potrebno.
Primjer iz života. Postavili smo zadatak
Ilustrirajmo ovaj članak primjerom iz stvarnog života – izračunavanjem provizija na temelju širokog raspona prodajnih metrika. Počet ćemo s vrlo jednostavnom opcijom, a zatim ćemo je postupno komplicirati sve dok jedino racionalno rješenje problema ne bude korištenje funkcije VPR. Početni scenarij za naš fiktivni zadatak je sljedeći: ako prodavač ostvari više od 30000 30 dolara prodaje godišnje, njegova provizija iznosi 20%. Inače, provizija je samo XNUMX%. Stavimo to u obliku tablice:
Prodavatelj unosi svoje podatke o prodaji u ćeliju B1, a formula u ćeliji B2 određuje ispravnu stopu provizije koju prodavatelj može očekivati. Zauzvrat, rezultirajuća stopa koristi se u ćeliji B3 za izračun ukupne provizije koju bi prodavač trebao dobiti (jednostavnim množenjem ćelija B1 i B2).
Najzanimljiviji dio tablice nalazi se u ćeliji B2 – to je formula za određivanje stope provizije. Ova formula sadrži Excel funkciju tzv IF (AKO). Za one čitatelje koji nisu upoznati s ovom funkcijom, objasnit ću kako radi:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Stanje je argument funkcije koji uzima vrijednost bilo kojeg PRAVI KOD (ISTINA), ili FALSE (NETOČNO). U gornjem primjeru, izraz B1
Je li istina da je B1 manji od B5?
Ili možete reći drugačije:
Je li točno da je ukupni 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 laž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 2 USD, dobit ćemo stopu provizije od 20% u ćeliji B40000. Ako unesemo vrijednost od 30 XNUMX USD, tada će se stopa provizije promijeniti za XNUMX%:
Ovako funkcionira naš stol.
Kompliciramo zadatak
Hajdemo malo otežati stvari. Postavimo još jedan prag: ako prodavač zaradi više od 40000 40 dolara, tada se stopa provizije povećava na XNUMX%:
Čini se da je sve jednostavno i jasno, ali naša formula u ćeliji B2 postaje primjetno kompliciranija. Ako pažljivo pogledate formulu, vidjet ćete da je treći argument funkcije IF (IF) pretvorio u još jednu punopravnu funkciju IF (AKO). Ova konstrukcija se naziva ugniježđivanje funkcija jedne u drugu. Excel rado dopušta te konstrukcije, pa čak i rade, ali ih je puno teže čitati i razumjeti.
Nećemo ulaziti u tehničke detalje – zašto i kako to radi, i nećemo ulaziti 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 kompliciranija! Što ako uvedemo drugu opciju za stopu provizije od 50% za one prodavače koji ostvare više od 50000 60000 dolara prodaje. A ako je netko prodao više od 60 XNUMX dolara, hoće li platiti XNUMX% provizije?
Sada je formula u ćeliji B2, čak i ako je napisana bez pogrešaka, postala potpuno nečitljiva. Mislim da je malo onih koji žele koristiti formule s 4 razine ugniježđenja u svojim projektima. Mora postojati lakši način?!
I postoji takav način! Funkcija će nam pomoći VPR.
Za rješavanje problema primjenjujemo funkciju VLOOKUP
Promijenimo malo dizajn našeg stola. Zadržat ćemo sva ista polja i podatke, ali ćemo ih rasporediti na novi, kompaktniji način:
Odvojite trenutak i uvjerite se u novi stol Tablica cijena uključuje iste podatke kao prethodna tablica pragova.
Glavna ideja je koristiti funkciju VPR odrediti željeni tarifni stav prema tablici Tablica cijena ovisno o obimu prodaje. Imajte na umu da prodavatelj može prodati robu za iznos koji nije jednak jednom od pet pragova u tablici. Na primjer, mogao bi prodati za 34988 dolara, ali ne postoji takav iznos. Pogledajmo kako funkcionira VPR može nositi s takvom situacijom.
Umetanje funkcije VLOOKUP
Odaberite ćeliju B2 (gdje želimo umetnuti našu formulu) i pronađite VLOOKUP (VLOOKUP) u biblioteci funkcija programa Excel: Formule (formule) > Biblioteka funkcija (Biblioteka funkcija) > Pregled i reference (Reference i nizovi).
Pojavljuje se dijaloški okvir Argumenti funkcije (Argumenti funkcije). Ispunjavamo vrijednosti argumenata jedan po jedan, počevši od Lookup_value (Traženje_vrijednosti). U ovom primjeru, ovo je ukupan iznos prodaje iz ćelije B1. Postavite 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 tablica Tablica cijena. Postavite kursor u polje Niz_tablice (Tablica) i odaberite cijelu tablicu Tablica cijenaosim zaglavlja.
Zatim moramo odrediti iz kojeg ćemo stupca izvući podatke pomoću naše formule. Zanima nas stopa provizije koja se nalazi u drugom stupcu tablice. Stoga, za argument Col_index_num (Broj_stupca) unesite vrijednost 2.
I na kraju, uvodimo zadnji argument – Traženje_raspona (Interval_pretrage).
Važno: upravo korištenje ovog argumenta čini razliku između dva načina primjene funkcije VPR. Kada radite s bazama podataka, argument Traženje_raspona (range_lookup) mora uvijek imati vrijednost FALSE (FALSE) za traženje točnog podudaranja. U našoj uporabi funkcije VPR, ovo polje moramo ostaviti praznim ili unijeti vrijednost PRAVI KOD (PRAVI). Izuzetno je važno pravilno odabrati ovu opciju.
Da bi bilo jasnije, predstavit ćemo PRAVI KOD (ISTINA) u polju Traženje_raspona (Interval_pretrage). Iako, ako ostavite polje prazno, to neće biti pogreška, jer PRAVI KOD je njegova zadana vrijednost:
Ispunili smo sve parametre. Sada pritiskamo OK, a Excel nam stvara formulu s funkcijom VPR.
Ako eksperimentiramo s nekoliko različitih vrijednosti za ukupni iznos prodaje, tada ćemo se uvjeriti da formula radi ispravno.
Zaključak
Kada je funkcija VPR radi s bazama podataka, argument Traženje_raspona (range_lookup) mora prihvatiti FALSE (NETOČNO). I vrijednost unesena kao Lookup_value (Lookup_value) mora postojati u bazi podataka. Drugim riječima, traži točno podudaranje.
U primjeru koji smo pogledali u ovom članku nema potrebe za točnim podudaranjem. To je slučaj kada funkcija VPR mora se prebaciti na aproksimativni način rada kako bi vratio željeni rezultat.
Na primjer: Želimo odrediti koju stopu koristiti u izračunu provizije za prodavača s obujmom prodaje od 34988 USD. Funkcija VPR vraća nam vrijednost od 30%, što je apsolutno točno. Ali zašto je formula odabrala redak koji sadrži točno 30%, a ne 20% ili 40%? Što se podrazumijeva pod približnom pretragom? Budimo jasni.
Kada argument Traženje_raspona (interval_lookup) ima vrijednost PRAVI KOD (TRUE) ili izostavljena funkcija VPR ponavlja kroz prvi stupac i odabire najveću vrijednost koja ne prelazi vrijednost traženja.
Važna točka: Da bi ova shema radila, prvi stupac tablice mora biti poredan uzlaznim redoslijedom.