Cynnwys
Yn ddiweddar fe wnaethom gysegru erthygl i un o'r swyddogaethau Excel mwyaf defnyddiol o'r enw VPR a dangosodd sut y gellir ei ddefnyddio i echdynnu'r wybodaeth ofynnol o gronfa ddata i mewn i gell taflen waith. Soniasom hefyd fod dau achos defnydd ar gyfer y swyddogaeth VPR a dim ond un ohonynt sy'n delio ag ymholiadau cronfa ddata. Yn yr erthygl hon, byddwch yn dysgu ffordd arall llai adnabyddus o ddefnyddio'r swyddogaeth VPR yn Excel.
Os nad ydych wedi gwneud hyn eto, gwnewch yn siŵr eich bod yn darllen yr erthygl olaf am y swyddogaeth VPR, oherwydd mae'r holl wybodaeth isod yn cymryd yn ganiataol eich bod eisoes yn gyfarwydd â'r egwyddorion a ddisgrifir yn yr erthygl gyntaf.
Wrth weithio gyda chronfeydd data, swyddogaethau VPR mae dynodwr unigryw yn cael ei basio, a ddefnyddir i nodi'r wybodaeth yr ydym am ddod o hyd iddi (er enghraifft, cod cynnyrch neu rif adnabod cwsmer). Rhaid i'r cod unigryw hwn fod yn bresennol yn y gronfa ddata, fel arall VPR yn rhoi gwybod am gamgymeriad. Yn yr erthygl hon, byddwn yn edrych ar y ffordd hon o ddefnyddio'r swyddogaeth VPRpan nad yw'r ID yn bodoli yn y gronfa ddata o gwbl. Fel pe bai'r swyddogaeth VPR newid i'r modd bras, a dewis pa ddata i'w ddarparu i ni pan fyddwn am ddod o hyd i rywbeth. Mewn rhai amgylchiadau, dyma'n union sydd ei angen.
Enghraifft o fywyd. Rydym yn gosod y dasg
Gadewch i ni ddarlunio'r erthygl hon gydag enghraifft o fywyd go iawn - cyfrifo comisiynau yn seiliedig ar ystod eang o fetrigau gwerthu. Byddwn yn dechrau gydag opsiwn syml iawn, ac yna byddwn yn ei gymhlethu'n raddol nes mai'r unig ateb rhesymegol i'r broblem yw defnyddio'r swyddogaeth VPR. Mae'r senario cychwynnol ar gyfer ein tasg ffug fel a ganlyn: os yw gwerthwr yn gwneud mwy na $30000 mewn gwerthiant mewn blwyddyn, yna mae ei gomisiwn yn 30%. Fel arall, dim ond 20% yw'r comisiwn. Gadewch i ni ei roi ar ffurf tabl:
Mae'r gwerthwr yn cofnodi eu data gwerthu yng nghell B1, ac mae'r fformiwla yng nghell B2 yn pennu'r gyfradd comisiwn gywir y gall y gwerthwr ei ddisgwyl. Yn ei dro, defnyddir y gyfradd ganlyniadol yng nghell B3 i gyfrifo cyfanswm y comisiwn y dylai'r gwerthwr ei dderbyn (dim ond lluosi celloedd B1 a B2).
Mae rhan fwyaf diddorol y tabl wedi’i chynnwys yng nghell B2 – dyma’r fformiwla ar gyfer pennu cyfradd y comisiwn. Mae'r fformiwla hon yn cynnwys swyddogaeth Excel o'r enw IF (IF). I'r darllenwyr hynny nad ydynt yn gyfarwydd â'r swyddogaeth hon, byddaf yn esbonio sut mae'n gweithio:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Cyflwr yn ddadl swyddogaeth sy'n cymryd gwerth y naill neu'r llall CÔD GWIR (GWIR), neu Anghywir (GAU). Yn yr enghraifft uchod, mae'r ymadrodd B1
Ydy hi'n wir bod B1 yn llai na B5?
Neu gallwch ei ddweud yn wahanol:
A yw'n wir bod cyfanswm y gwerthiannau am y flwyddyn yn llai na'r gwerth trothwy?
Os atebwn y cwestiwn hwn OES (TRUE), yna mae'r swyddogaeth yn dychwelyd gwerth os yn wir (gwerth os GWIR). Yn ein hachos ni, dyma fydd gwerth cell B6, hy cyfradd y comisiwn pan fydd cyfanswm y gwerthiant yn is na'r trothwy. Os atebwn y cwestiwn RHIF (FALSE) wedyn yn dychwelyd gwerth os ffug (gwerth os ANWIR). Yn ein hachos ni, dyma werth cell B7, hy cyfradd y comisiwn pan fydd cyfanswm y gwerthiant yn uwch na'r trothwy.
Fel y gallwch weld, os cymerwn gyfanswm y gwerthiant o $20000, cawn gyfradd comisiwn o 2% yng nghell B20. Os byddwn yn nodi gwerth o $40000, yna bydd cyfradd y comisiwn yn newid 30%:
Dyma sut mae ein bwrdd yn gweithio.
Rydym yn cymhlethu'r dasg
Gadewch i ni wneud pethau ychydig yn anoddach. Gadewch i ni osod trothwy arall: os yw'r gwerthwr yn ennill mwy na $40000, yna mae cyfradd y comisiwn yn cynyddu i 40%:
Mae'n ymddangos bod popeth yn syml ac yn glir, ond mae ein fformiwla yng nghell B2 yn dod yn amlwg yn fwy cymhleth. Os edrychwch yn ofalus ar y fformiwla, fe welwch fod trydydd dadl y swyddogaeth IF (IF) troi'n swyddogaeth lawn arall IF (IF). Gelwir yr adeiladwaith hwn yn nythu ffwythiannau i'w gilydd. Mae Excel yn caniatáu'r lluniadau hyn yn hapus, ac maen nhw hyd yn oed yn gweithio, ond maen nhw'n llawer anoddach eu darllen a'u deall.
Ni fyddwn yn ymchwilio i'r manylion technegol - pam a sut mae'n gweithio, ac ni fyddwn yn mynd i mewn i naws ysgrifennu swyddogaethau nythu. Wedi'r cyfan, mae hon yn erthygl sy'n ymroddedig i'r swyddogaeth VPR, nid canllaw cyflawn i Excel.
Beth bynnag yw'r achos, mae'r fformiwla'n mynd yn fwy cymhleth! Beth os byddwn yn cyflwyno opsiwn arall ar gyfer cyfradd comisiwn o 50% ar gyfer y gwerthwyr hynny sy'n gwneud mwy na $50000 mewn gwerthiannau. Ac os yw rhywun wedi gwerthu mwy na $60000, a fyddant yn talu comisiwn o 60%?
Nawr mae'r fformiwla yng nghell B2, hyd yn oed os cafodd ei ysgrifennu heb wallau, wedi dod yn gwbl annarllenadwy. Credaf mai ychydig sydd am ddefnyddio fformiwlâu â 4 lefel o nythu yn eu prosiectau. Mae'n rhaid bod ffordd haws?!
Ac mae yna ffordd o'r fath! Bydd y swyddogaeth yn ein helpu ni VPR.
Rydym yn cymhwyso'r swyddogaeth VLOOKUP i ddatrys y broblem
Gadewch i ni newid dyluniad ein bwrdd ychydig. Byddwn yn cadw'r un meysydd a data, ond yn eu trefnu mewn ffordd newydd, fwy cryno:
Cymerwch eiliad a gwnewch yn siŵr y bwrdd newydd Tabl Cyfradd yn cynnwys yr un data â’r tabl trothwy blaenorol.
Y prif syniad yw defnyddio'r swyddogaeth VPR i benderfynu ar y gyfradd tariff a ddymunir yn ôl y tabl Tabl Cyfradd yn dibynnu ar gyfaint gwerthiant. Sylwch y gall y gwerthwr werthu nwyddau am swm nad yw'n hafal i un o'r pum trothwy yn y tabl. Er enghraifft, gallai werthu am $34988, ond nid oes swm o'r fath. Gadewch i ni weld sut mae'r swyddogaeth VPR yn gallu delio â sefyllfa o’r fath.
Mewnosod swyddogaeth VLOOKUP
Dewiswch gell B2 (lle rydym am fewnosod ein fformiwla) a darganfyddwch VLOOKUP (VLOOKUP) yn y Llyfrgell Swyddogaethau Excel: Fformiwlâu (fformiwlâu) > Llyfrgell Swyddogaeth (Llyfrgell Swyddogaeth) > Edrych a Chyfeirio (Cyfeiriadau ac araeau).
Mae blwch deialog yn ymddangos Dadleuon Swyddogaeth (Dadleuon swyddogaeth). Rydym yn llenwi gwerthoedd y dadleuon fesul un, gan ddechrau gyda Edrych_gwerth (Lookup_value). Yn yr enghraifft hon, dyma gyfanswm y gwerthiannau o gell B1. Rhowch y cyrchwr yn y cae Edrych_gwerth (Lookup_value) a dewiswch cell B1.
Nesaf, mae angen i chi nodi'r swyddogaethau VPRble i chwilio am ddata. Yn ein hesiampl, tabl yw hwn Tabl Cyfradd. Rhowch y cyrchwr yn y cae Tabl_array (Tabl) a dewiswch y tabl cyfan Tabl Cyfraddheblaw am benawdau.
Nesaf, mae angen i ni nodi pa golofn i dynnu data o ddefnyddio ein fformiwla. Mae gennym ddiddordeb yn y gyfradd comisiwn, sydd yn ail golofn y tabl. Felly, ar gyfer y ddadl Col_index_num (Colofn_number) rhowch y gwerth 2.
Ac yn olaf, rydym yn cyflwyno'r ddadl olaf - Ystod_lookup (Interval_lookup).
Pwysig: y defnydd o'r ddadl hon sy'n gwneud y gwahaniaeth rhwng y ddwy ffordd o gymhwyso'r ffwythiant VPR. Wrth weithio gyda chronfeydd data, y ddadl Ystod_lookup Rhaid i (range_lookup) bob amser fod â gwerth Anghywir (FALSE) i chwilio am union gyfatebiaeth. Yn ein defnydd o'r swyddogaeth VPR, rhaid inni adael y maes hwn yn wag, neu nodi gwerth CÔD GWIR (GWIR). Mae'n hynod bwysig dewis yr opsiwn hwn yn gywir.
Er mwyn ei gwneud yn gliriach, byddwn yn cyflwyno CÔD GWIR (TRUE) yn y cae Ystod_lookup (Interval_lookup). Er, os byddwch yn gadael y maes yn wag, ni fydd hyn yn wall, ers hynny CÔD GWIR yw ei werth diofyn:
Rydym wedi llenwi'r holl baramedrau. Nawr rydym yn pwyso OK, ac mae Excel yn creu fformiwla i ni gyda swyddogaeth VPR.
Os byddwn yn arbrofi gyda nifer o wahanol werthoedd ar gyfer cyfanswm y gwerthiant, yna byddwn yn sicrhau bod y fformiwla yn gweithio'n gywir.
Casgliad
Pan fydd y swyddogaeth VPR yn gweithio gyda chronfeydd data, dadl Ystod_lookup (range_lookup) rhaid derbyn Anghywir (GAU). A'r gwerth a gofnodwyd fel Edrych_gwerth Rhaid i (Lookup_value) fodoli yn y gronfa ddata. Mewn geiriau eraill, mae'n chwilio am union gyfatebiaeth.
Yn yr enghraifft rydyn ni wedi edrych arno yn yr erthygl hon, nid oes angen cael union gyfatebiaeth. Mae hyn yn wir pan fydd y swyddogaeth VPR rhaid newid i'r modd bras i ddychwelyd y canlyniad a ddymunir.
Er enghraifft: Rydym am benderfynu pa gyfradd i'w defnyddio yng nghyfrifiad y comisiwn ar gyfer gwerthwr gyda chyfaint gwerthiant o $34988. Swyddogaeth VPR yn dychwelyd gwerth o 30% inni, sy'n gwbl gywir. Ond pam y dewisodd y fformiwla y rhes yn cynnwys union 30% ac nid 20% neu 40%? Beth yw ystyr chwilio bras? Gadewch i ni fod yn glir.
Pan fydd y ddadl Ystod_lookup (interval_lookup) â gwerth CÔD GWIR (TRUE) neu hepgor, swyddogaeth VPR yn ailadrodd trwy'r golofn gyntaf ac yn dewis y gwerth mwyaf nad yw'n fwy na'r gwerth chwilio.
Pwynt pwysig: Er mwyn i'r cynllun hwn weithio, rhaid didoli colofn gyntaf y tabl mewn trefn esgynnol.