Turinys
Neseniai skyrėme straipsnį vienai iš naudingiausių „Excel“ funkcijų VPR ir parodė, kaip ji gali būti naudojama norint išgauti reikiamą informaciją iš duomenų bazės į darbalapio langelį. Taip pat minėjome, kad yra du šios funkcijos naudojimo atvejai VPR ir tik vienas iš jų susijęs su duomenų bazių užklausomis. Šiame straipsnyje sužinosite dar vieną mažiau žinomą šios funkcijos naudojimo būdą VPR „Excel“.
Jei to dar nepadarėte, būtinai perskaitykite paskutinį straipsnį apie funkciją VPR, nes visa toliau pateikta informacija daro prielaidą, kad jau esate susipažinę su pirmame straipsnyje aprašytais principais.
Dirbant su duomenų bazėmis, funkcijomis VPR perduodamas unikalus identifikatorius, kuris naudojamas norint identifikuoti informaciją, kurią norime rasti (pavyzdžiui, prekės kodas arba kliento identifikavimo numeris). Priešingu atveju šis unikalus kodas turi būti duomenų bazėje VPR praneš apie klaidą. Šiame straipsnyje apžvelgsime šį funkcijos naudojimo būdą VPRkai id iš viso nėra duomenų bazėje. Tarsi funkcija VPR perjungiamas į apytikslį režimą ir pasirenka, kokius duomenis mums pateikti, kai norime ką nors rasti. Tam tikromis aplinkybėmis tai yra būtent tai, ko reikia.
Pavyzdys iš gyvenimo. Mes nustatome užduotį
Iliustruojame šį straipsnį pavyzdžiu iš tikro – komisinių apskaičiavimu remiantis įvairiais pardavimo rodikliais. Pradėsime nuo labai paprasto varianto, o po to palaipsniui jį komplikuosime, kol vienintelis racionalus problemos sprendimas yra naudoti funkciją VPR. Pradinis mūsų fiktyvios užduoties scenarijus yra toks: jei pardavėjas per metus parduoda daugiau nei 30000 30 USD, jo komisiniai yra 20%. Kitu atveju komisiniai tik XNUMX proc. Padėkime lentelės pavidalu:
Pardavėjas įveda savo pardavimo duomenis į langelį B1, o formulė langelyje B2 nustato teisingą komisinių normą, kurios pardavėjas gali tikėtis. Savo ruožtu gauta norma naudojama langelyje B3 apskaičiuojant bendrą komisinį mokestį, kurį pardavėjas turėtų gauti (tiesiog padauginus langelius B1 ir B2).
Įdomiausia lentelės dalis yra langelyje B2 – tokia komisinio dydžio nustatymo formulė. Šioje formulėje yra „Excel“ funkcija, vadinama IF (JEI). Tiems skaitytojams, kurie nėra susipažinę su šia funkcija, paaiškinsiu, kaip ji veikia:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Sąlyga yra funkcijos argumentas, kuris įgauna bet kurio iš jų reikšmę TIKRAS KODAS (TRUE), arba FALSE (NETEISINGA). Aukščiau pateiktame pavyzdyje išraiška B1
Ar tiesa, kad B1 yra mažesnis nei B5?
Arba galite pasakyti kitaip:
Ar tiesa, kad bendra metų pardavimų suma yra mažesnė už ribinę vertę?
Jei atsakysime į šį klausimą TAIP (TRUE), tada funkcija grįžta vertė, jei tiesa (reikšmė, jei TRUE). Mūsų atveju tai bus langelio B6 reikšmė, ty komisinis dydis, kai bendras pardavimas nesiekia slenksčio. Jei atsakysime į klausimą NE (FALSE), tada grįžta vertė, jei klaidinga (reikšmė, jei FALSE). Mūsų atveju tai yra langelio B7 reikšmė, ty komisinis dydis, kai bendras pardavimas viršija slenkstį.
Kaip matote, jei paimsime bendrą 20000 2 USD pardavimą, langelyje B20 gausime 40000% komisinį mokestį. Jei įvesime 30 XNUMX USD vertę, komisinis dydis pasikeis XNUMX%:
Štai kaip veikia mūsų stalas.
Mes apsunkiname užduotį
Šiek tiek apsunkinkime dalykus. Nustatykime kitą slenkstį: jei pardavėjas uždirba daugiau nei 40000 40 USD, komisinis dydis padidėja iki XNUMX %:
Viskas atrodo paprasta ir aišku, bet mūsų formulė langelyje B2 tampa pastebimai sudėtingesnė. Jei atidžiai pažvelgsite į formulę, pamatysite trečiąjį funkcijos argumentą IF (IF) virto dar viena visaverte funkcija IF (JEI). Ši konstrukcija vadinama funkcijų įdėjimu viena į kitą. „Excel“ mielai leidžia šias konstrukcijas ir netgi veikia, tačiau jas daug sunkiau skaityti ir suprasti.
Nesigilinsime į technines detales – kodėl ir kaip tai veikia, ir nesigilinsime į įdėtųjų funkcijų rašymo niuansus. Galų gale, tai yra straipsnis, skirtas funkcijai VPR, nėra išsamus „Excel“ vadovas.
Kad ir kaip būtų, formulė tampa sudėtingesnė! Ką daryti, jei įvesime kitą 50% komisinio mokesčio parinktį tiems pardavėjams, kurie parduoda daugiau nei 50000 60000 USD. Ir jei kas nors pardavė daugiau nei 60 XNUMX USD, ar mokės XNUMX% komisinių?
Dabar formulė langelyje B2, net jei ji buvo parašyta be klaidų, tapo visiškai neįskaitoma. Manau, kad mažai kas nori savo projektuose naudoti formules su 4 lygių lizdais. Turi būti lengvesnis būdas?!
Ir yra toks būdas! Funkcija mums padės VPR.
Problemai išspręsti taikome funkciją VLOOKUP
Šiek tiek pakeisime savo stalo dizainą. Išsaugosime visus tuos pačius laukus ir duomenis, bet sutvarkysime juos nauju, kompaktiškesniu būdu:
Skirkite šiek tiek laiko ir įsitikinkite, kad naujas stalas Įvertinimų lentelė apima tuos pačius duomenis kaip ir ankstesnėje slenksčių lentelėje.
Pagrindinė idėja yra naudoti funkciją VPR pagal lentelę nustatyti pageidaujamą tarifo normą Įvertinimų lentelė priklausomai nuo pardavimo apimties. Atkreipkite dėmesį, kad pardavėjas gali parduoti prekes už sumą, kuri nėra lygi vienai iš penkių lentelėje nurodytų slenksčių. Pavyzdžiui, jis galėtų parduoti už 34988 USD, bet tokios sumos nėra. Pažiūrėkime, kaip veikia funkcija VPR gali susidoroti su tokia situacija.
Įterpiama funkcija VLOOKUP
Pasirinkite langelį B2 (kur norime įterpti savo formulę) ir raskite PERŽIŪRA (VLOOKUP) „Excel“ funkcijų bibliotekoje: Formulės (formulės) > Funkcijų biblioteka (Funkcijų biblioteka) > Ieškoti ir nuoroda (Nuorodos ir masyvai).
Pasirodo dialogo langas Funkcijos argumentai (Funkcijų argumentai). Argumentų reikšmes užpildome po vieną, pradedant nuo Lookup_value (Paieškos_vertė). Šiame pavyzdyje tai yra bendra pardavimo iš langelio B1 suma. Įveskite žymeklį į lauką Lookup_value (Lookup_value) ir pasirinkite langelį B1.
Toliau reikia nurodyti funkcijas VPRkur ieškoti duomenų. Mūsų pavyzdyje tai yra lentelė Įvertinimų lentelė. Įveskite žymeklį į lauką Table_masyvas (Lentelė) ir pasirinkite visą lentelę Įvertinimų lentelėišskyrus antraštes.
Toliau, naudodami formulę, turime nurodyti, iš kurio stulpelio išgauti duomenis. Mus domina komisinis tarifas, kuris yra antrame lentelės stulpelyje. Todėl už argumentą Col_index_num (Stulpelio_numeris) įveskite 2 reikšmę.
Ir galiausiai pristatome paskutinį argumentą – Range_lookup (Interval_lookup).
Svarbu: būtent šio argumento naudojimas lemia skirtumą tarp dviejų funkcijos taikymo būdų VPR. Dirbant su duomenų bazėmis argumentas Range_lookup (range_lookup) visada turi turėti reikšmę FALSE (FALSE), jei norite ieškoti tikslios atitikties. Naudodami šią funkciją VPR, turime palikti šį lauką tuščią arba įvesti reikšmę TIKRAS KODAS (TIESA). Labai svarbu teisingai pasirinkti šią parinktį.
Kad būtų aiškiau, pristatysime TIKRAS KODAS (TRUE) lauke Range_lookup (Interval_lookup). Nors, jei paliksite lauką tuščią, tai nebus klaida, nes TIKRAS KODAS yra numatytoji vertė:
Užpildėme visus parametrus. Dabar spaudžiame OK, o „Excel“ mums sukuria formulę su funkcija VPR.
Jei eksperimentuosime su keliomis skirtingomis bendros pardavimo sumos reikšmėmis, įsitikinsime, kad formulė veikia tinkamai.
Išvada
Kai funkcija VPR dirba su duomenų bazėmis, argumentu Range_lookup (range_lookup) turi priimti FALSE (NETEISINGA). Ir vertė įvesta kaip Lookup_value (Lookup_value) turi egzistuoti duomenų bazėje. Kitaip tariant, ieškoma tikslios atitikties.
Šiame straipsnyje pateiktame pavyzdyje nereikia gauti tikslios atitikties. Taip yra, kai funkcija VPR turi persijungti į apytikslį režimą, kad būtų grąžintas norimas rezultatas.
Pavyzdžiui: Norime nustatyti, kokį tarifą naudoti apskaičiuojant komisinį pardavėjo, kurio pardavimo apimtis yra 34988 XNUMX USD. Funkcija VPR grąžina mums 30 % vertę, kuri yra visiškai teisinga. Bet kodėl formulė pasirinko eilutę, kurioje yra tiksliai 30%, o ne 20% ar 40%? Ką reiškia apytikslė paieška? Būkime aiškūs.
Kai argumentas Range_lookup (interval_lookup) turi reikšmę TIKRAS KODAS (TRUE) arba praleista, funkcija VPR kartojasi per pirmąjį stulpelį ir pasirenka didžiausią reikšmę, kuri neviršija paieškos reikšmės.
Svarbus punktas: Kad ši schema veiktų, pirmasis lentelės stulpelis turi būti surūšiuotas didėjančia tvarka.