vsebina
Nedavno smo posvetili članek eni najbolj uporabnih Excelovih funkcij, imenovani VPR in pokazal, kako ga je mogoče uporabiti za pridobivanje zahtevanih informacij iz baze podatkov v celico delovnega lista. Omenili smo tudi, da obstajata dva primera uporabe funkcije VPR in le eden od njih se ukvarja s poizvedbami po bazi podatkov. V tem članku boste izvedeli še en manj znan način uporabe funkcije VPR v Excelu.
Če tega še niste storili, potem obvezno preberite zadnji članek o funkciji VPR, saj vse spodnje informacije predvidevajo, da že poznate načela, opisana v prvem članku.
Pri delu z bazami podatkov funkcije VPR se posreduje enolični identifikator, ki se uporablja za identifikacijo informacij, ki jih želimo najti (na primer koda izdelka ali identifikacijska številka stranke). Ta edinstvena koda mora biti prisotna v bazi podatkov, sicer VPR bo javil napako. V tem članku si bomo ogledali ta način uporabe funkcije VPRko ID sploh ne obstaja v bazi podatkov. Kot da funkcija VPR preklopi na način približevanja in izbere, katere podatke nam bo posredoval, ko želimo nekaj najti. V določenih okoliščinah je ravno to potrebno.
Primer iz življenja. Postavili smo nalogo
Naj ta članek ponazorimo s primerom iz resničnega življenja – izračunom provizij na podlagi širokega nabora prodajnih meritev. Začeli bomo z zelo preprosto možnostjo, nato pa jo bomo postopoma zapletali, dokler ne bo edina racionalna rešitev problema uporaba funkcije VPR. Začetni scenarij za našo fiktivno nalogo je naslednji: če prodajalec v enem letu ustvari več kot 30000 $ prodaje, potem je njegova provizija 30 %. V nasprotnem primeru je provizija samo 20%. Postavimo ga v obliki tabele:
Prodajalec vnese podatke o prodaji v celico B1, formula v celici B2 pa določi pravilno stopnjo provizije, ki jo prodajalec lahko pričakuje. Dobljena stopnja se nato uporabi v celici B3 za izračun skupne provizije, ki bi jo moral prejeti prodajalec (preprosto množenje celic B1 in B2).
Najbolj zanimiv del tabele je v celici B2 – to je formula za določitev stopnje provizije. Ta formula vsebuje Excelovo funkcijo, imenovano IF (ČE). Za tiste bralce, ki ne poznajo te funkcije, bom razložil, kako deluje:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Stanje je funkcijski argument, ki ima vrednost bodisi PRAVA KODA (TRUE), oz FALSE (LAŽNO). V zgornjem primeru je izraz B1
Je res, da je B1 manjši od B5?
Ali pa lahko rečeš drugače:
Ali je res, da je skupni znesek prodaje za leto manjši od mejne vrednosti?
Če odgovorimo na to vprašanje DA (TRUE), potem se funkcija vrne vrednost, če je res (vrednost, če je TRUE). V našem primeru bo to vrednost celice B6, tj. stopnja provizije, ko je skupna prodaja pod pragom. Če odgovorimo na vprašanje NE (FALSE) se nato vrne vrednost, če je napačna (vrednost, če je FALSE). V našem primeru je to vrednost celice B7, tj. stopnja provizije, ko je celotna prodaja nad pragom.
Kot lahko vidite, če vzamemo skupno prodajo 20000 $, dobimo 2-odstotno stopnjo provizije v celici B20. Če vnesemo vrednost 40000 $, se bo stopnja provizije spremenila za 30 %:
Tako deluje naša miza.
Nalogo zapletamo
Dajmo stvari malo otežiti. Postavimo še en prag: če prodajalec zasluži več kot 40000 $, se stopnja provizije poveča na 40%:
Zdi se, da je vse preprosto in jasno, vendar naša formula v celici B2 postane opazno bolj zapletena. Če pozorno pogledate formulo, boste videli, da je tretji argument funkcije IF (IF) spremenila v drugo polnopravno funkcijo IF (ČE). Ta konstrukcija se imenuje gnezdenje funkcij ena v drugo. Excel z veseljem omogoča te konstrukte in celo delujejo, vendar jih je veliko težje brati in razumeti.
Ne bomo se spuščali v tehnične podrobnosti – zakaj in kako deluje, prav tako se ne bomo spuščali v nianse pisanja ugnezdenih funkcij. Navsezadnje je to članek, posvečen funkciji VPR, ni popoln vodnik po Excelu.
Kakorkoli že, formula postane bolj zapletena! Kaj pa, če uvedemo še eno možnost za 50-odstotno provizijo za tiste prodajalce, ki ustvarijo več kot 50000 $ prodaje. In če je nekdo prodal več kot 60000 $, ali bo plačal 60% provizije?
Zdaj je formula v celici B2, tudi če je bila zapisana brez napak, postala popolnoma neberljiva. Mislim, da je malo takih, ki želijo v svojih projektih uporabljati formule s 4 stopnjami gnezdenja. Mora obstajati lažja pot?!
In obstaja tak način! Funkcija nam bo pomagala VPR.
Za rešitev težave uporabimo funkcijo VLOOKUP
Nekoliko spremenimo dizajn naše mize. Ohranili bomo vsa ista polja in podatke, vendar jih uredili na nov, bolj kompakten način:
Vzemite si trenutek in poskrbite za novo mizo Tabela ocen vključuje iste podatke kot prejšnja tabela pragov.
Glavna ideja je uporaba funkcije VPR za določitev želene tarifne stopnje po tabeli Tabela ocen odvisno od obsega prodaje. Upoštevajte, da lahko prodajalec proda blago za znesek, ki ni enak enemu od petih pragov v tabeli. Na primer, lahko bi prodal za 34988 $, vendar tega zneska ni. Poglejmo, kako deluje VPR lahko spopade s takšno situacijo.
Vstavljanje funkcije VLOOKUP
Izberite celico B2 (kamor želimo vstaviti našo formulo) in poiščite VLOOKUP (VLOOKUP) v Excelovi knjižnici funkcij: Formule (formule) > Knjižnica funkcij (Knjižnica funkcij) > Iskanje in reference (Reference in nizi).
Prikaže se pogovorno okno Argumenti funkcije (Argumenti funkcije). Vrednosti argumentov izpolnimo eno za drugo, začenši z Iskalna_vrednost (Iskalna_vrednost). V tem primeru je to skupni znesek prodaje iz celice B1. Kazalec postavite v polje Iskalna_vrednost (Lookup_value) in izberite celico B1.
Nato morate določiti funkcije VPRkje iskati podatke. V našem primeru je to tabela Tabela ocen. Kazalec postavite v polje Tabela_ matrika (Tabela) in izberite celotno tabelo Tabela ocenrazen glav.
Nato moramo z našo formulo določiti, iz katerega stolpca bomo izvlekli podatke. Zanima nas stopnja provizije, ki je v drugem stolpcu tabele. Zato za argument Col_index_num (Številka_stolpca) vnesite vrednost 2.
In na koncu predstavimo še zadnji argument – Območje_iskanje (Interval_lookup).
pomembno: uporaba tega argumenta naredi razliko med obema načinoma uporabe funkcije VPR. Pri delu z bazami podatkov argument Območje_iskanje (range_lookup) mora vedno imeti vrednost FALSE (FALSE) za iskanje natančnega ujemanja. Pri naši uporabi funkcije VPR, moramo to polje pustiti prazno ali vnesti vrednost PRAVA KODA (PRAV). Zelo pomembno je, da to možnost pravilno izberete.
Da bo bolj jasno, se bomo predstavili PRAVA KODA (TRUE) v polju Območje_iskanje (Interval_lookup). Čeprav, če pustite polje prazno, to ne bo napaka, saj PRAVA KODA je njegova privzeta vrednost:
Izpolnili smo vse parametre. Zdaj pritiskamo OK, Excel pa nam s funkcijo ustvari formulo VPR.
Če eksperimentiramo z več različnimi vrednostmi za skupni znesek prodaje, se bomo prepričali, da formula deluje pravilno.
zaključek
Ko je funkcija VPR dela z bazami podatkov, argument Območje_iskanje (range_lookup) mora sprejeti FALSE (LAŽNO). In vrednost, vnesena kot Iskalna_vrednost (Lookup_value) mora obstajati v bazi podatkov. Z drugimi besedami, išče natančno ujemanje.
V primeru, ki smo si ga ogledali v tem članku, ni potrebe po natančnem ujemanju. To je v primeru, ko funkcija VPR mora preklopiti na približen način, da vrne želeni rezultat.
Na primer: Določiti želimo, katero stopnjo uporabiti pri izračunu provizije za prodajalca z obsegom prodaje 34988 USD. funkcija VPR nam vrne vrednost 30 %, kar je popolnoma pravilno. Toda zakaj je formula izbrala vrstico, ki vsebuje točno 30 % in ne 20 % ali 40 %? Kaj pomeni približno iskanje? Bodimo jasni.
Ko argument Območje_iskanje (interval_lookup) ima vrednost PRAVA KODA (TRUE) ali izpuščeno, funkcija VPR ponovi skozi prvi stolpec in izbere največjo vrednost, ki ne presega vrednosti iskanja.
Pomembna točka: Da ta shema deluje, mora biti prvi stolpec tabele razvrščen v naraščajočem vrstnem redu.