Saturs
Nesen mēs veltījām rakstu vienai no visnoderīgākajām Excel funkcijām VPR un parādīja, kā to var izmantot, lai izvilktu nepieciešamo informāciju no datu bāzes darblapas šūnā. Mēs arī minējām, ka funkcijai ir divi lietošanas gadījumi VPR un tikai viens no tiem nodarbojas ar datu bāzes vaicājumiem. Šajā rakstā jūs uzzināsit citu mazāk zināmu veidu, kā izmantot šo funkciju VPR programmā Excel.
Ja vēl neesat to izdarījis, noteikti izlasiet pēdējo rakstu par funkciju VPR, jo visa tālāk sniegtā informācija paredz, ka esat jau iepazinies ar pirmajā rakstā aprakstītajiem principiem.
Strādājot ar datu bāzēm, funkcijām VPR tiek nodots unikāls identifikators, kas tiek izmantots, lai identificētu informāciju, kuru vēlamies atrast (piemēram, preces kods vai klienta identifikācijas numurs). Pretējā gadījumā šim unikālajam kodam ir jābūt datu bāzē VPR ziņos par kļūdu. Šajā rakstā mēs aplūkosim šo funkcijas izmantošanas veidu VPRkad ID datu bāzē vispār nepastāv. It kā funkcija VPR pārslēdzās uz aptuveno režīmu un izvēlas, kādus datus mums sniegt, kad vēlamies kaut ko atrast. Noteiktos apstākļos tas ir tieši tas, kas vajadzīgs.
Piemērs no dzīves. Mēs uzstādījām uzdevumu
Ilustrēsim šo rakstu ar piemēru no dzīves – komisijas maksas aprēķināšana, pamatojoties uz plašu pārdošanas rādītāju klāstu. Mēs sāksim ar ļoti vienkāršu iespēju, un tad pakāpeniski to sarežģīsim, līdz vienīgais racionālais problēmas risinājums ir izmantot funkciju VPR. Mūsu fiktīvā uzdevuma sākotnējais scenārijs ir šāds: ja pārdevējs gadā pārdod vairāk nekā 30000 30 USD, tad viņa komisijas maksa ir 20%. Pretējā gadījumā komisija ir tikai XNUMX%. Ieliksim to tabulas veidā:
Pārdevējs ievada savus pārdošanas datus šūnā B1, un šūnā B2 esošā formula nosaka pareizo komisijas likmi, ko pārdevējs var sagaidīt. Savukārt iegūto likmi izmanto šūnā B3, lai aprēķinātu kopējo komisiju, kas pārdevējam jāsaņem (vienkārši reizinot šūnas B1 un B2).
Interesantākā tabulas daļa ir šūnā B2 – tā ir komisijas likmes noteikšanas formula. Šī formula satur Excel funkciju, ko sauc IF (JA). Tiem lasītājiem, kuri nav pazīstami ar šo funkciju, es paskaidrošu, kā tā darbojas:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Stāvoklis ir funkcijas arguments, kas izmanto jebkuras vērtības vērtību PATIESAIS KODS (TRUE), vai FALSE (FALSE). Iepriekš minētajā piemērā izteiksme B1
Vai tā ir taisnība, ka B1 ir mazāks par B5?
Vai arī varat to pateikt savādāk:
Vai tā ir taisnība, ka gada kopējais pārdošanas apjoms ir mazāks par sliekšņa vērtību?
Ja atbildam uz šo jautājumu JĀ (TRUE), tad funkcija atgriežas vērtība, ja taisnība (vērtība, ja TRUE). Mūsu gadījumā tā būs šūnas B6 vērtība, ti, komisijas likme, ja kopējais pārdošanas apjoms ir zem sliekšņa. Ja atbildam uz jautājumu NĒ (FALSE) pēc tam atgriežas vērtība, ja nepatiesa (vērtība, ja FALSE). Mūsu gadījumā tā ir šūnas B7 vērtība, ti, komisijas likme, ja kopējais pārdošanas apjoms pārsniedz slieksni.
Kā redzat, ja mēs ņemam kopējo pārdošanas apjomu 20000 2 $, šūnā B20 iegūstam 40000% komisijas maksu. Ja ievadīsim vērtību 30 XNUMX $, komisijas maksa mainīsies par XNUMX%:
Tā darbojas mūsu galds.
Mēs sarežģījam uzdevumu
Padarīsim lietas nedaudz grūtākas. Nosakīsim citu slieksni: ja pārdevējs nopelna vairāk nekā 40000 40 $, komisijas maksa palielinās līdz XNUMX%:
Šķiet, ka viss ir vienkāršs un skaidrs, taču mūsu formula šūnā B2 kļūst ievērojami sarežģītāka. Ja paskatās cieši uz formulu, jūs redzēsiet, ka trešais arguments funkcijas IF (IF) pārvērtās par citu pilnvērtīgu funkciju IF (JA). Šo konstrukciju sauc par funkciju ligzdošanu savā starpā. Programma Excel ar prieku pieļauj šīs konstrukcijas, un tās pat darbojas, taču tās ir daudz grūtāk lasīt un saprast.
Mēs neiedziļināsimies tehniskajās detaļās – kāpēc un kā tas darbojas, kā arī neiedziļināsimies ligzdoto funkciju rakstīšanas niansēs. Galu galā šis ir raksts, kas veltīts funkcijai VPR, nav pilnīgs Excel ceļvedis.
Jebkurā gadījumā formula kļūst sarežģītāka! Ko darīt, ja mēs ieviestu citu iespēju komisijas maksai 50% apmērā tiem pārdevējiem, kuru pārdošanas apjoms pārsniedz USD 50000 60000. Un, ja kāds ir pārdevis vairāk nekā $60, vai viņš maksās XNUMX% komisijas maksu?
Tagad formula šūnā B2, pat ja tā tika uzrakstīta bez kļūdām, ir kļuvusi pilnīgi nelasāma. Es domāju, ka ir maz tādu, kas savos projektos vēlas izmantot formulas ar 4 līmeņu ligzdošanu. Jābūt vieglākam ceļam?!
Un ir tāds veids! Funkcija mums palīdzēs VPR.
Lai atrisinātu problēmu, mēs izmantojam funkciju VLOOKUP
Nedaudz mainīsim mūsu galda dizainu. Mēs saglabāsim visus tos pašus laukus un datus, bet sakārtosim tos jaunā, kompaktākā veidā:
Veltiet laiku un pārliecinieties, vai jaunais galds Novērtējumu tabula ietver tos pašus datus kā iepriekšējā sliekšņa tabulā.
Galvenā ideja ir izmantot funkciju VPR lai noteiktu vēlamo tarifa likmi saskaņā ar tabulu Novērtējumu tabula atkarībā no pārdošanas apjoma. Lūdzu, ņemiet vērā, ka pārdevējs var pārdot preces par summu, kas nav vienāda ar vienu no pieciem tabulā norādītajiem sliekšņiem. Piemēram, viņš varētu pārdot par $34988, bet tādas summas nav. Apskatīsim, kā darbojas funkcija VPR var tikt galā ar šādu situāciju.
VLOOKUP funkcijas ievietošana
Atlasiet šūnu B2 (kur mēs vēlamies ievietot formulu) un atrodiet VLOOKUP (VLOOKUP) Excel funkciju bibliotēkā: Formulas (formulas) > Funkciju bibliotēka (Funkciju bibliotēka) > Meklēt un uzzināt (Atsauces un masīvi).
Parādās dialoglodziņš Funkcijas argumenti (Funkciju argumenti). Mēs aizpildām argumentu vērtības pa vienam, sākot ar Uzmeklēšanas_vērtība (Uzmeklēšanas_vērtība). Šajā piemērā tas ir kopējais pārdošanas apjoms no šūnas B1. Novietojiet kursoru laukā Uzmeklēšanas_vērtība (Lookup_value) un atlasiet šūnu B1.
Tālāk jums jānorāda funkcijas VPRkur meklēt datus. Mūsu piemērā šī ir tabula Novērtējumu tabula. Novietojiet kursoru laukā Tabulas_masīvs (Tabula) un atlasiet visu tabulu Novērtējumu tabulaizņemot galvenes.
Tālāk mums ir jānorāda, no kuras kolonnas iegūt datus, izmantojot mūsu formulu. Mūs interesē komisijas likme, kas ir tabulas otrajā ailē. Tāpēc par argumentu Col_index_num (Slejas_numurs) ievadiet vērtību 2.
Un visbeidzot mēs ieviešam pēdējo argumentu - Range_lookup (Interval_lookup).
Svarīgi: tieši šī argumenta izmantošana nosaka atšķirību starp diviem funkcijas pielietošanas veidiem VPR. Strādājot ar datu bāzēm, arguments Range_lookup (range_lookup) vienmēr ir jābūt vērtībai FALSE (FALSE), lai meklētu precīzu atbilstību. Mēs izmantojam šo funkciju VPR, šis lauks ir jāatstāj tukšs vai jāievada vērtība PATIESAIS KODS (TRUE). Ir ārkārtīgi svarīgi pareizi izvēlēties šo iespēju.
Lai padarītu to skaidrāku, mēs iepazīstināsim PATIESAIS KODS (TRUE) laukā Range_lookup (Interval_lookup). Lai gan, ja atstājat lauku tukšu, tā nebūs kļūda, jo PATIESAIS KODS ir tā noklusējuma vērtība:
Esam aizpildījuši visus parametrus. Tagad mēs nospiežam OK, un Excel mums izveido formulu ar funkciju VPR.
Ja mēs eksperimentēsim ar vairākām dažādām kopējās pārdošanas summas vērtībām, tad mēs pārliecināsimies, ka formula darbojas pareizi.
Secinājumi
Kad funkcija VPR strādā ar datu bāzēm, argumentāciju Range_lookup (range_lookup) ir jāpieņem FALSE (FALSE). Un vērtība tika ievadīta kā Uzmeklēšanas_vērtība (Lookup_value) ir jābūt datu bāzē. Citiem vārdiem sakot, tas meklē precīzu atbilstību.
Piemērā, ko apskatījām šajā rakstā, nav nepieciešams iegūt precīzu atbilstību. Tas ir gadījumā, ja funkcija VPR ir jāpārslēdzas uz aptuveno režīmu, lai atgrieztu vēlamo rezultātu.
Piemēram: Mēs vēlamies noteikt, kādu likmi izmantot komisijas aprēķinos pārdevējam, kura pārdošanas apjoms ir 34988 USD. Funkcija VPR atgriež mums vērtību 30%, kas ir pilnīgi pareizi. Bet kāpēc formula atlasīja rindu, kurā ir tieši 30%, nevis 20% vai 40%? Ko nozīmē aptuvenā meklēšana? Būsim skaidrībā.
Kad arguments Range_lookup (interval_lookup) ir vērtība PATIESAIS KODS (TRUE) vai izlaista, funkcija VPR atkārtojas pirmajā kolonnā un atlasa lielāko vērtību, kas nepārsniedz uzmeklēšanas vērtību.
Svarīgs punkts: Lai šī shēma darbotos, tabulas pirmā kolonna ir jāsakārto augošā secībā.