Mukati
Isu nguva pfupi yadarika takatsaurira chinyorwa kune imwe yeanonyanya kubatsira Excel mabasa anonzi VPR uye yakaratidza kuti ingashandiswa sei kubvisa ruzivo rwunodiwa kubva mudura rekushandisa musero repepa rekushanda. Isu takataura zvakare kuti kune maviri ekushandisa kesi zvebasa racho VPR uye imwe chete yadzo inobata nemibvunzo yedatabase. Muchikamu chino, iwe uchadzidza imwe nzira isingazivikanwe yekushandisa basa VPR muExcel.
Kana usati waita izvi parizvino, saka ita chokwadi chekuverenga chinyorwa chekupedzisira nezve basa VPR, nokuti mashoko ose ari pasi apa anofunga kuti iwe unotoziva nezvemitemo inotsanangurwa munyaya yekutanga.
Paunenge uchishanda nemadatabase, mabasa VPR chiziviso chakasiyana chinopfuudzwa, chinoshandiswa kuona ruzivo rwatinoda kuwana (semuenzaniso, kodhi yechigadzirwa kana nhamba yekuzivikanwa kwevatengi). Iyi kodhi yakasarudzika inofanirwa kunge iripo mudhatabhesi, kana zvisina kudaro VPR achataura kukanganisa. Muchikamu chino, tichatarisa nzira iyi yekushandisa basa VPRkana id isipo mudhatabhesi zvachose. Sokunge basa VPR inochinjirwa kune yakaringana modhi, uye inosarudza kuti ndeipi data rekutipa isu kana tichida kuwana chimwe chinhu. Mune mamwe mamiriro ezvinhu, izvi ndizvo chaizvo zvinodiwa.
Muenzaniso kubva muupenyu. Isu tinoisa basa
Ngatienzanisirei chinyorwa ichi nemuenzaniso wehupenyu chaihwo - kuverenga makomisheni zvichienderana nehupamhi hwekutengesa metrics. Tichatanga nesarudzo yakapusa, uye tozoiomesa zvishoma nezvishoma kusvikira mhinduro chete ine musoro kudambudziko iri kushandisa basa racho. VPR. Mamiriro ekutanga ebasa redu rekunyepedzera ndeaya anotevera: kana mutengesi anoita mari inodarika $30000 mukutengesa mugore, saka komisheni yake i30%. Zvikasadaro, komisheni inongova 20%. Ngatiiise muchimiro chetafura:
Mutengesi anopinza data ravo rekutengesa muchitokisi B1, uye fomula iri muchitokisi B2 inosarudza iyo chaiyo komisheni chiyero chinogona kutarisirwa nemutengesi. Zvakare, chiyero chinoguma chinoshandiswa musero B3 kuverenga iyo yakazara komisheni iyo mutengesi anofanira kugamuchira (kungowanza masero B1 uye B2).
Iyo inonyanya kunakidza chikamu chetafura chirimo muchitokisi B2 - iyi ndiyo fomula yekuona chiyero chekomisheni. Iyi fomula ine basa reExcel rinonzi IF (IF). Kune avo vaverengi vasina kujairana nebasa iri, ini ndichatsanangura kuti rinoshanda sei:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Condition ishoko rekuita rinotora kukosha kweimwe TRUE CODE (CHOKWADI), kana VENHEMA (NHEMA). Mumuenzaniso uri pamusoro apa, izwi rokuti B1
Ichokwadi here kuti B1 ishoma pane B5?
Kana kuti unogona kuzvitaura zvakasiyana:
Ichokwadi here kuti huwandu hwekutengesa kwegore huri pasi pechikumbaridzo kukosha?
Kana tikapindura mubvunzo uyu EHE (CHOKWADI), ipapo basa rinodzoka kukosha kana ichokwadi (value kana TRUE). Kwatiri, iyi ichave kukosha kwesero B6, kureva komisheni rate kana kutengesa kwakazara kuri pazasi pechikumbaridzo. Kana tikapindura mubvunzo HERE (NHEMA) wobva wadzoka kukosha kana nhema (value kana FALSE). Kwatiri, uku ndiko kukosha kwesero B7, kureva chiyero chekomisheni kana kutengesa kwakazara kuri pamusoro pechikumbaridzo.
Sezvauri kuona, kana tikatora iyo yese yekutengesa ye20000 $, tinowana 2% komisheni rate muchitokisi B20. Kana tikaisa kukosha kwe $40000, ipapo mwero wekomisheni uchachinja ne30%:
Aya ndiwo mashandiro anoita tafura yedu.
Isu tinoomesa basa
Ngatiite kuti zvinhu zvitiomere zvishoma. Ngatiise imwe chikumbaridzo: kana mutengesi akawana mari inodarika $40000, saka mutengo wekomisheni unowedzera kusvika 40%:
Zvese zvinoita sezviri nyore uye zvakajeka, asi fomula yedu muchitokisi B2 inova yakanyanya kuoma. Kana iwe ukanyatsotarisisa pane formula, iwe uchaona iyo yechitatu nharo yebasa IF (IF) yakashandurwa kuita rimwe basa rakazara IF (IF). Kuvakwa uku kunonzi nesting yemabasa mukati memumwe nemumwe. Excel inobvumira nemufaro zvivakwa izvi, uye zvinotoshanda, asi zvakanyanya kuoma kuverenga nekunzwisisa.
Hatisi kuzoongorora mune zvehunyanzvi ruzivo - nei uye sei zvinoshanda, uye isu hatizopinda mune nuances yekunyora nested mabasa. Mushure mezvose, ichi chinyorwa chakatsaurirwa kune basa VPR, kwete gwara rakazara kuExcel.
Chero zvazvingava, fomula yacho inowedzera kuoma! Ko kana tikasuma imwe sarudzo yekomisheni chiyero che50% kune avo vatengesi vanoita zvinopfuura $50000 mukutengesa. Uye kana mumwe munhu atengesa mari inodarika madhora mazana matanhatu, vachabhadhara 60000% komisheni?
Iye zvino fomula musero B2, kunyangwe yakanyorwa isina zvikanganiso, yave isingaverengeki zvachose. Ini ndinofunga kuti kune vashoma vanoda kushandisa mafomula ane 4 mazinga ekudyara mumapurojekiti avo. Panofanira kunge paine nzira iri nyore?!
Uye kune nzira yakadaro! Basa racho richatibatsira VPR.
Isu tinoshandisa VLOOKUP basa kugadzirisa dambudziko
Ngatichinjei magadzirirwo etafura yedu zvishoma. Isu tichachengeta ese akafanana minda uye data, asi kuronga iwo nenzira nyowani, yakawedzera compact:
Tora chinguva uye ita chokwadi tafura itsva Rate Table inosanganisira data rakafanana netafura yechikumbaridzo yapfuura.
Pfungwa huru ndeyekushandisa basa VPR kuona mutero unodiwa maererano netafura Rate Table zvichienderana nehuwandu hwekutengesa. Ndapota cherechedza kuti mutengesi anogona kutengesa zvinhu nemari isingaenzaniswi neimwe yezvikumbaridzo zvishanu patafura. Semuenzaniso, aigona kutengesa nemadhora mazana matatu nemakumi mapfumbamwe nemakumi mapfumbamwe nemasere, asi hapana mari yakadaro. Ngationei kuti basa racho sei VPR anogona kubata nemamiriro ezvinhu akadaro.
Kuisa VLOOKUP basa
Sarudza cell B2 (patinoda kuisa fomula yedu) uye uwane VLOOKUP (VLOOKUP) muExcel Mabasa Raibhurari: Formulas (mafomula) > Basa Remabhuku (Basa Raibhurari) > Kutarisa & Referensi (References uye arrays).
Bhokisi renhaurirano rinoonekwa Basa Kukakavara (Nhau dzebasa). Isu tinozadza kukosha kwenharo imwe neimwe, kutanga Lookup_value (Kutsvaga_kukosha). Mumuenzaniso uyu, iyi ndiyo nhamba yose yekutengesa kubva muchitokisi B1. Isa cursor mumunda Lookup_value (Lookup_value) uye sarudza cell B1.
Zvadaro, unofanira kutsanangura mabasa VPRkwekutsvaga data. Mumuenzaniso wedu, iyi itafura Rate Table. Isa cursor mumunda Table_array (Tafura) uye sarudza tafura yose Rate Tablekunze kwemisoro.
Tevere, isu tinofanirwa kutsanangura kuti ndeipi koramu yekubvisa data kubva pakushandisa fomula yedu. Isu tinofarira muyero wekomisheni, iyo iri muchikamu chechipiri chetafura. Naizvozvo, kune nharo Col_index_num (Column_number) isa kukosha 2.
Uye pakupedzisira, tinosuma nharo yekupedzisira - Range_lokup (Interval_lokup).
Zvichikosha: ndiko kushandiswa kwenharo iyi kunoita mutsauko pakati penzira mbiri dzekushandisa basa VPR. Paunenge uchishanda nemadatabase, gakava Range_lokup (range_lookup) inofanira kugara iine kukosha VENHEMA (FALSE) kutsvaga mutambo chaiwo. Mukushandisa kwedu basa racho VPR, tinofanira kusiya chikamu ichi chisina chinhu, kana kuisa kukosha TRUE CODE (CHOKWADI). Zvinonyanya kukosha kusarudza iyi sarudzo nemazvo.
Kuti zvijeke, tichasuma TRUE CODE (CHOKWADI) mumunda Range_lokup (Interval_lookup). Kunyangwe, kana iwe ukasiya munda usina chinhu, izvi hazvizove kukanganisa, kubvira TRUE CODE ndiko kukosha kwayo kwekutanga:
Takazadza maparameter ese. Zvino tinodzvanya OK, uye Excel inotigadzirira fomula ine basa VPR.
Kana tikayedza akati wandei akasiyana ehuwandu hwekutengesa huwandu, saka isu tichava nechokwadi chekuti fomula inoshanda nemazvo.
mhedziso
Kana basa VPR inoshanda nedatabase, nharo Range_lokup (range_lookup) inofanira kubvuma VENHEMA (NHEMA). Uye kukosha kwakapinda se Lookup_value (Lookup_value) inofanira kuvapo mudhatabhesi. Nemamwe manzwi, iri kutsvaga inowirirana chaizvo.
Mumuenzaniso watakatarisa muchinyorwa chino, hapana chikonzero chekuwana mucheno chaiwo. Izvi ndizvo zvinoitika kana basa racho VPR inofanira kuchinjika kune yakaringana modhi kudzosera yaunoda.
Semuyenzaniso: Tinoda kuona kuti ndeipi chiyero chekushandisa mukomisheni kuverenga kune mutengesi ane huwandu hwekutengesa hwemadhora mazana matatu nemakumi mapfumbamwe nemasere. Function VPR inotidzorera kukosha kwe30%, iyo yakarurama. Asi nei fomura rakasarudza mutsara une 30% chaiyo uye kwete 20% kana 40%? Chii chinorehwa nekufungidzira kutsvaga? Ngatibude pachena.
Apo nharo Range_lokup (interval_lookup) ine kukosha TRUE CODE (CHOKWADI) kana kusiiwa, basa VPR inodzokorora kuburikidza nekoramu yekutanga uye inosarudza kukosha kukuru kusingapfuure kukosha kwekutarisa.
Nhau inokosha: Kuti chirongwa ichi chishande, koramu yekutanga yetafura inofanirwa kurongwa mukukwira.