Okuqukethwe
Sisanda kunikezela i-athikili komunye wemisebenzi ye-Excel ewusizo kakhulu ebizwa VPR futhi yabonisa ukuthi ingasetshenziswa kanjani ukuze kukhishwe ulwazi oludingekayo kusizindalwazi kuya kuseli lephepha lokusebenzela. Siphinde sasho ukuthi kunezimo ezimbili zokusebenzisa umsebenzi VPR futhi eyodwa kuphela yazo ephathelene nemibuzo egciniwe. Kulesi sihloko, uzofunda enye indlela eyaziwa kancane yokusebenzisa umsebenzi VPR ku-Excel.
Uma ungakakwenzi lokhu, qiniseka ukuthi ufunda isihloko sokugcina mayelana nomsebenzi VPR, ngoba lonke ulwazi olungezansi luthatha ngokuthi usujwayelene nezimiso ezichazwe esihlokweni sokuqala.
Lapho usebenza nesizindalwazi, imisebenzi VPR kuphasiswe isihlonzi esiyingqayizivele, esisetshenziselwa ukuhlonza ulwazi esifuna ukulithola (isibonelo, ikhodi yomkhiqizo noma inombolo yokuhlonza ikhasimende). Le khodi eyingqayizivele kufanele ibe khona kusizindalwazi, ngaphandle kwalokho VPR izobika iphutha. Kulesi sihloko, sizobheka le ndlela yokusebenzisa umsebenzi VPRuma i-id ingekho kusizindalwazi nhlobo. Njengokungathi umsebenzi VPR ishintshele kumodi elinganiselwe, futhi ikhetha ukuthi iyiphi idatha esizosinika yona uma sifuna ukuthola okuthile. Ezimweni ezithile, yilokhu kanye okudingekayo.
Isibonelo esivela ekuphileni. Sibeka umsebenzi
Ake sifanekise lesi sihloko ngesibonelo sempilo yangempela - ukubala amakhomishini ngokusekelwe ezinhlobonhlobo zamamethrikhi okuthengisa. Sizoqala ngenketho elula kakhulu, bese sizoyihlanganisa kancane kancane kuze kube yilapho kuphela isisombululo esinengqondo senkinga ukusebenzisa umsebenzi. VPR. Isimo sokuqala somsebenzi wethu oqanjiwe simi kanje: uma umthengisi enza ngaphezu kuka-$30000 ngokuthengisa ngonyaka, ikhomishini yakhe ingu-30%. Ngaphandle kwalokho, ikhomishini ingamaphesenti angama-20 kuphela. Ake sikubeke ngendlela yetafula:
Umthengisi ufaka idatha yakhe yokuthengisa kuseli B1, futhi ifomula kuseli B2 inquma izinga lekhomishini elilungile umthengisi angalilindela. Ngokulandelayo, isilinganiso esiwumphumela sisetshenziswa kuseli B3 ukubala inani lekhomishini umthengisi okufanele ayithole (umane aphindaphinde amaseli B1 kanye no-B2).
Ingxenye yetafula ethakazelisa kakhulu iqukethwe kuseli B2 - lena ifomula yokunquma izinga lekhomishini. Le fomula iqukethe umsebenzi we-Excel obizwa ngokuthi IF (IF). Kulabo bafundi abangawazi lo msebenzi, ngizochaza ukuthi usebenza kanjani:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Isimo i-agumenti yomsebenzi ethatha inani lanoma yikuphi IKHODI LEQINISO (IQINISO), noma FALSE (AMAANGA). Esibonelweni esingenhla, inkulumo ethi B1
Ingabe kuyiqiniso ukuthi i-B1 ingaphansi kwe-B5?
Noma ungakusho ngendlela ehlukile:
Ingabe kuyiqiniso ukuthi inani eliphelele lokuthengiswa konyaka lingaphansi kwevelu yomkhawulo?
Uma siphendula lo mbuzo YEBO (IQINISO), bese umsebenzi uyabuya inani uma kuyiqiniso (inani uma TRUE). Esimweni sethu, lokhu kuzoba inani leseli B6, okungukuthi isilinganiso sekhomishini lapho ukuthengiswa okuphelele kungaphansi komkhawulo. Uma siphendula umbuzo CHA (FALSE) bese kuyabuya inani uma kungamanga (inani uma AMANGA). Esimweni sethu, leli inani leseli B7, okungukuthi izinga lekhomishini lapho ukuthengiswa okuphelele kungaphezu komkhawulo.
Njengoba ubona, uma sithatha inani eliphelele lama-$20000, sithola isilinganiso sekhomishini engu-2% kuseli B20. Uma sifaka inani lika-$40000, izinga lekhomishini lizoshintsha ngo-30%:
Lisebenza kanje itafula lethu.
Siwenza ube nzima umsebenzi
Asenze izinto zibe nzima nakakhulu. Ake sibeke omunye umkhawulo: uma umthengisi ehola ngaphezu kuka-$40000, izinga lekhomishini likhuphuka libe ngu-40%:
Yonke into ibonakala ilula futhi icacile, kodwa ifomula yethu kuseli B2 iba yinkimbinkimbi ngokuphawulekayo. Uma ubhekisisa ifomula, uzobona ukuthi ingxabano yesithathu yomsebenzi IF (IF) iphenduke omunye umsebenzi ogcwele IF (IF). Lokhu kwakhiwa kubizwa ngokuthi i-nesting of functions zibe enye kwenye. I-Excel ivumela ngenjabulo lezi zakhiwo, futhi ziyasebenza, kodwa zinzima kakhulu ukuzifunda nokuqonda.
Ngeke singene emininingwaneni yezobuchwepheshe - kungani futhi isebenza kanjani, futhi ngeke singene emacaleni okubhala imisebenzi efakwe isidleke. Phela, lesi sihloko esinikezelwe emsebenzini VPR, hhayi umhlahlandlela ophelele we-Excel.
Kungakhathaliseki ukuthi yikuphi, ifomula iba yinkimbinkimbi kakhulu! Kuthiwani uma sethula enye inketho yesilinganiso sekhomishini esingu-50% salabo bathengisi abenza imali engaphezu kuka-$50000 ngokuthengisa. Futhi uma umuntu ethengise ngaphezu kuka-$60000, bazokhokha ikhomishini engu-60%?
Manje ifomula kuseli B2, noma ngabe ibhalwe ngaphandle kwamaphutha, ayifundeki ngokuphelele. Ngicabanga ukuthi bambalwa abafuna ukusebenzisa amafomula anamazinga angu-4 okuzalela kumaphrojekthi abo. Kufanele kube nendlela elula?!
Futhi kukhona indlela enjalo! Umsebenzi uzosisiza VPR.
Sisebenzisa umsebenzi we-VLOOKUP ukuze sixazulule inkinga
Ake siguqule ukwakheka kwetafula lethu kancane. Sizogcina zonke izinkambu nedatha efanayo, kodwa sizihlele ngendlela entsha, ehlangene kakhulu:
Thatha isikhashana bese uqinisekisa itafula elisha Ithebula Lesilinganiso ihlanganisa idatha efanayo nethebula le-threshold langaphambilini.
Umqondo oyinhloko uwukusebenzisa umsebenzi VPR ukunquma inani lentela elifunekayo ngokwethebula Ithebula Lesilinganiso kuye ngevolumu yokuthengisa. Sicela uqaphele ukuthi umdayisi angadayisa izimpahla ngenani elingalingani nowodwa wemikhawulo emihlanu etafuleni. Isibonelo, angathengisa ngo-$34988, kodwa alikho inani elinjalo. Ake sibone ukuthi umsebenzi VPR angabhekana nesimo esinjalo.
Ifaka umsebenzi we-VLOOKUP
Khetha iseli B2 (lapho sifuna ukufaka khona ifomula yethu) bese uthola I-VLOOKUP (VLOOKUP) kumtapo wezincwadi we-Excel: amafomula (amafomula) > I-Function Library (Umtapo womsebenzi) > Ukubheka nokubhekisela (Amareferensi nama-array).
Kuvela ibhokisi lengxoxo Ukuphikisana Komsebenzi (Izimpikiswano zomsebenzi). Sigcwalisa amanani ezimpikiswano ngamunye ngamunye, siqale Bheka_inani (Bheka_inani). Kulesi sibonelo, leli inani eliphelele lokuthengiswa okuvela kuseli B1. Faka ikhesa ensimini Bheka_inani (Lookup_value) bese ukhetha iseli B1.
Okulandelayo, udinga ukucacisa imisebenzi VPRlapho ungabheka khona idatha. Esibonelweni sethu, leli yitafula Ithebula Lesilinganiso. Faka ikhesa ensimini Uhlu_lwethebula (Ithebula) bese ukhetha itafula lonke Ithebula Lesilinganisongaphandle kwezihloko.
Okulandelayo, sidinga ukucacisa ukuthi iyiphi ikholomu esingakhipha idatha ekusebenziseni ifomula yethu. Sinentshisekelo ngenani lekhomishini, ekukholamu yesibili yetafula. Ngakho-ke, ngempikiswano Inombolo_yenkomba (Inombolo_yekholomu) faka inani 2.
Futhi ekugcineni, sethula impikiswano yokugcina - Ukubheka_ububanzi (Ukubheka_Isikhawu).
Okubalulekile: ukusetshenziswa kwale mpikiswano okwenza umehluko phakathi kwezindlela ezimbili zokusebenzisa umsebenzi VPR. Lapho usebenza nesizindalwazi, impikiswano Ukubheka_ububanzi (ububanzi_ukubheka) kufanele njalo kube nenani FALSE (FALSE) ukuze useshe okufana ncamashi. Ekusebenziseni kwethu umsebenzi VPR, kufanele sishiye le nkambu ingenalutho, noma sifake inani IKHODI LEQINISO (IQINISO). Kubaluleke kakhulu ukukhetha le nketho ngendlela efanele.
Ukukwenza kucace kakhudlwana, sizokwethula IKHODI LEQINISO (IQINISO) ensimini Ukubheka_ububanzi (Ukubheka_Isikhawu). Nakuba, uma ushiya inkambu ingenalutho, lokhu ngeke kube iphutha, kusukela IKHODI LEQINISO inani layo elizenzakalelayo:
Sigcwalise wonke amapharamitha. Manje siyacindezela OK, futhi i-Excel isenzela ifomula ngomsebenzi VPR.
Uma sizama ngamavelu amaningana ahlukene ngenani eliphelele lokuthengisa, sizobe sesiqinisekisa ukuthi ifomula isebenza ngendlela efanele.
Isiphetho
Lapho umsebenzi VPR isebenza nesizindalwazi, ingxabano Ukubheka_ububanzi (ukubheka_ububanzi) kufanele kwamukele FALSE (AMAANGA). Futhi inani elifakwe njenge Bheka_inani (Lookup_value) kufanele kube khona kusizindalwazi. Ngamanye amazwi, ibheka okufanayo ngqo.
Esibonelweni esisibhekile kulesi sihloko, asikho isidingo sokuthola okufana ncamashi. Lokhu kunjalo lapho umsebenzi VPR kufanele ishintshele kumodi elinganiselwe ukuze ibuyisele umphumela oyifunayo.
Ngokwesibonelo: Sifuna ukunquma ukuthi yiliphi izinga esizolisebenzisa ekubalweni kwekhomishini kumthengisi onenani lokuthengisa elingu-$34988. Umsebenzi VPR isibuyisela inani elingu-30%, okulungile ngokuphelele. Kodwa kungani ifomula ikhethe umugqa oqukethe ncamashi u-30% hhayi u-20% noma u-40%? Kusho ukuthini ukuseshwa okulinganiselwe? Ake sicace.
Lapho ingxabano Ukubheka_ububanzi (i-interval_lookup) inenani IKHODI LEQINISO (IQINISO) noma kweqiwe, umsebenzi VPR iphindaphinda kukholamu yokuqala bese ikhetha inani elikhulu elingadluli inani lokubheka.
Iphuzu elibalulekile: Ukuze lolu hlelo lusebenze, ikholomu yokuqala yethebula kufanele ihlungwe ngendlela ekhuphukayo.