Kaundan
Bag-ohay lang namon nga gipahinungod ang usa ka artikulo sa usa sa labing mapuslanon nga mga gimbuhaton sa Excel nga gitawag VPR ug gipakita kung giunsa kini magamit sa pagkuha sa gikinahanglan nga impormasyon gikan sa usa ka database ngadto sa usa ka worksheet cell. Gihisgutan usab namo nga adunay duha ka mga kaso sa paggamit alang sa function VPR ug usa lang niini ang naghisgot sa mga pangutana sa database. Niini nga artikulo, makakat-on ka og lain nga wala kaayo nahibal-an nga paagi sa paggamit sa function VPR sa paglihok.
Kung wala pa nimo kini mahimo, siguruha nga basahon ang katapusan nga artikulo bahin sa function VPR, tungod kay ang tanang impormasyon sa ubos nagtuo nga pamilyar ka na sa mga prinsipyo nga gihulagway sa unang artikulo.
Kung nagtrabaho kauban ang mga database, naglihok VPR gipasa ang usa ka talagsaon nga identifier, nga gigamit sa pag-ila sa kasayuran nga gusto namon pangitaon (pananglitan, usa ka code sa produkto o numero sa pag-ila sa kustomer). Kining talagsaon nga code kinahanglang anaa sa database, kon dili VPR magreport ug sayop. Niini nga artikulo, atong tan-awon kini nga paagi sa paggamit sa function VPRkung ang id wala na sa database. Ingon og ang function VPR gibalhin sa gibanabana nga mode, ug gipili kung unsang datos ang ihatag kanamo kung gusto namon mangita usa ka butang. Sa pipila ka mga kahimtang, kini mao gayud ang gikinahanglan.
Usa ka ehemplo gikan sa kinabuhi. Gitakda namo ang buluhaton
Atong i-ilustrar kini nga artikulo sa usa ka tinuod nga kinabuhi nga pananglitan - pagkalkula sa mga komisyon base sa usa ka halapad nga mga sukatan sa pagbaligya. Magsugod kita sa usa ka yano kaayo nga kapilian, ug unya anam-anam naton kini nga komplikado hangtod ang bugtong rational nga solusyon sa problema mao ang paggamit sa function. VPR. Ang inisyal nga senaryo para sa atong tinumotumo nga buluhaton mao ang mosunod: kung ang usa ka salesperson makahimo og labaw sa $30000 nga halin sa usa ka tuig, nan ang iyang komisyon kay 30%. Kung dili, 20% ra ang komisyon. Atong ibutang kini sa porma sa usa ka lamesa:
Gisulod sa magbabaligya ang ilang datos sa pagpamaligya sa cell B1, ug ang pormula sa cell B2 nagtino sa husto nga rate sa komisyon nga mapaabut sa magbabaligya. Sa baylo, ang resulta nga rate gigamit sa cell B3 aron makalkulo ang kinatibuk-ang komisyon nga kinahanglan madawat sa magbabaligya (pagpadaghan lang sa mga cell B1 ug B2).
Ang labing makapaikag nga bahin sa lamesa naa sa cell B2 - kini ang pormula sa pagtino sa rate sa komisyon. Kini nga pormula adunay usa ka function sa Excel nga gitawag IF (KUNG). Alang sa mga magbabasa nga dili pamilyar sa kini nga function, ipasabut nako kung giunsa kini molihok:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
nga kahimtang mao ang usa ka function argumento nga nagkinahanglan sa bili sa bisan hain TINUOD NGA KODE (Tinuod), o SAYOP (MALI). Sa pananglitan sa ibabaw, ang ekspresyon B1
Tinuod ba nga ang B1 mas ubos kaysa B5?
O mahimo nimong isulti kini nga lahi:
Tinuod ba nga ang kinatibuk-ang kantidad sa halin alang sa tuig mas ubos sa kantidad sa threshold?
Kung atong tubagon kini nga pangutana OO (TRUE), unya ang function mobalik bili kon tinuod (bili kon TINUOD). Sa among kaso, kini ang kantidad sa cell B6, ie ang rate sa komisyon kung ang kinatibuk-ang halin ubos sa threshold. Kung atong tubagon ang pangutana Dili (FALSE) unya nibalik bili kon bakak (bili kon FALSE). Sa among kaso, kini ang kantidad sa cell B7, ie rate sa komisyon kung ang kinatibuk-an nga pagbaligya labaw sa threshold.
Sama sa imong nakita, kung among kuhaon ang kinatibuk-ang halin nga $20000, makakuha kami usa ka 2% nga rate sa komisyon sa cell B20. Kung mosulod kita sa kantidad nga $40000, unya ang rate sa komisyon mausab sa 30%:
Ingon niini ang pagtrabaho sa among lamesa.
Among gikomplikado ang buluhaton
Himoon nato nga mas lisud ang mga butang. Magbutang kita og laing threshold: kung ang magbabaligya mokita og labaw sa $40000, unya ang rate sa komisyon mosaka ngadto sa 40%:
Ang tanan ingon og yano ug tin-aw, apan ang among pormula sa cell B2 mahimong labi ka komplikado. Kung imong tan-awon pag-ayo ang pormula, imong makita nga ang ikatulo nga argumento sa function IF (KUNG) nahimong laing bug-os nga function IF (KUNG). Kini nga pagtukod gitawag nga nesting of functions ngadto sa usag usa. Malipayon nga gitugotan sa Excel kini nga mga konstruksyon, ug nagtrabaho pa gani sila, apan mas lisud kini basahon ug sabton.
Dili nato tukion ang mga teknikal nga detalye - ngano ug giunsa kini paglihok, ug dili kita moadto sa mga nuances sa pagsulat sa mga nested function. Pagkahuman, kini usa ka artikulo nga gipahinungod sa function VPR, dili kompleto nga giya sa Excel.
Bisan unsa pa ang kahimtang, ang pormula labi ka komplikado! Unsa kaha kung ipaila namon ang lain nga kapilian alang sa usa ka rate sa komisyon nga 50% alang sa mga namaligya nga nagbaligya labaw sa $ 50000. Ug kung adunay usa nga nakabaligya labaw pa sa $ 60000, magbayad ba sila og 60% nga komisyon?
Karon ang pormula sa cell B2, bisan kung kini gisulat nga wala’y mga sayup, nahimo nang hingpit nga dili mabasa. Sa akong hunahuna adunay pipila nga gusto nga mogamit mga pormula nga adunay 4 nga lebel sa salag sa ilang mga proyekto. Kinahanglan adunay mas sayon nga paagi?!
Ug adunay ingon niana nga paagi! Ang function makatabang kanato VPR.
Among gigamit ang VLOOKUP function aron masulbad ang problema
Usbon ta gamay ang disenyo sa atong lamesa. Atong tipigan ang tanan nga parehas nga mga natad ug datos, apan ihan-ay kini sa usa ka bag-o, mas compact nga paagi:
Paggahin og kadiyot ug siguroha ang bag-ong lamesa Talaan sa Rate naglakip sa sama nga data sa miaging threshold lamesa.
Ang panguna nga ideya mao ang paggamit sa function VPR aron mahibal-an ang gitinguha nga rate sa taripa sumala sa lamesa Talaan sa Rate depende sa sales volume. Palihug timan-i nga ang magbabaligya makabaligya og mga butang sa kantidad nga dili katumbas sa usa sa lima ka mga threshold sa lamesa. Pananglitan, mahimo siyang ibaligya sa $34988, apan walay ingon nga kantidad. Atong tan-awon kung giunsa ang function VPR makasagubang sa ingon nga sitwasyon.
Pagsal-ot sa usa ka VLOOKUP function
Pilia ang cell B2 (diin gusto namon isulud ang among pormula) ug pangitaa VLOOKUP (VLOOKUP) sa Excel Functions Library: Mga pormula (mga pormula) > Function Library (Function Library) > Pagpangita ug Pakisayran (Mga pakisayran ug mga array).
Usa ka dialog box ang makita Mga Argumento sa Pag-andar (Mga argumento sa function). Gipuno namon ang mga kantidad sa mga argumento sa usag usa, sugod sa Lookup_value (Lookup_value). Niini nga pananglitan, kini ang kinatibuk-ang kantidad sa halin gikan sa cell B1. Ibutang ang cursor sa uma Lookup_value (Lookup_value) ug pilia ang cell B1.
Sunod, kinahanglan nimo nga itakda ang mga function VPRasa mangitag data. Sa among pananglitan, kini usa ka lamesa Talaan sa Rate. Ibutang ang cursor sa uma Table_array (Table) ug pilia ang tibuok lamesa Talaan sa Rategawas sa mga ulohan.
Sunod, kinahanglan natong ipiho kung unsang kolum ang makuha sa datos gikan sa paggamit sa atong pormula. Interesado kami sa rate sa komisyon, nga naa sa ikaduhang kolum sa lamesa. Busa, alang sa argumento Col_index_num (Column_number) isulod ang bili 2.
Ug sa katapusan, among gipaila ang katapusang argumento - Range_lookup (Interval_lookup).
Importante: kini ang paggamit niini nga argumento nga naghimo sa kalainan tali sa duha ka mga paagi sa pagpadapat sa function VPR. Kung nagtrabaho kauban ang mga database, ang argumento Range_lookup (range_lookup) kinahanglan kanunay adunay bili SAYOP (FALSE) para mangita ug eksaktong tugma. Sa atong paggamit sa function VPR, kinahanglan natong biyaan nga blangko kining field, o magsulod ug bili TINUOD NGA KODE (Tinuod). Hinungdanon kaayo nga pilion kini nga kapilian sa husto.
Aron mas klaro, atong ipaila TINUOD NGA KODE (TINUOD) sa uma Range_lookup (Interval_lookup). Bisan pa, kung imong biyaan nga blangko ang uma, dili kini usa ka sayup, tungod kay TINUOD NGA KODE mao ang default nga bili niini:
Gipuno namon ang tanan nga mga parameter. Karon atong gipugos OK, ug ang Excel naghimo ug pormula para kanamo nga adunay function VPR.
Kung mag-eksperimento kami sa daghang lainlaing mga kantidad alang sa kinatibuk-ang kantidad sa pagbaligya, nan among sigurohon nga ang pormula molihok sa husto.
Panapos
Sa diha nga ang function VPR nagtrabaho sa mga database, argumento Range_lookup (range_lookup) kinahanglang dawaton SAYOP (BAKAK). Ug ang bili misulod ingon Lookup_value (Lookup_value) kinahanglan anaa sa database. Sa laing pagkasulti, nangita kini og eksaktong tugma.
Sa pananglitan nga among gitan-aw sa kini nga artikulo, dili kinahanglan nga makakuha usa ka eksaktong tugma. Kini ang kaso kung ang function VPR kinahanglan nga mobalhin sa gibanabana nga mode aron mabalik ang gitinguha nga resulta.
Pananglitan: Gusto namon mahibal-an kung unsang rate ang gamiton sa kalkulasyon sa komisyon alang sa usa ka salesperson nga adunay kantidad sa pagbaligya nga $34988. Kalihokan VPR mibalik kanamo sa usa ka bili sa 30%, nga mao ang hingpit nga husto. Apan nganong gipili sa pormula ang laray nga adunay eksaktong 30% ug dili 20% o 40%? Unsa ang gipasabut sa gibanabana nga pagpangita? Klarohon ta.
Sa diha nga ang argumento Range_lookup (interval_lookup) adunay bili TINUOD NGA KODE (TINUOD) o wala, function VPR nagbalikbalik sa unang kolum ug nagpili sa kinadak-ang bili nga dili molapas sa bili sa pagpangita.
Importante nga punto: Aron molihok kini nga laraw, ang una nga kolum sa lamesa kinahanglan nga ihan-ay sa pataas nga han-ay.