Iyini i-macro e-Excel futhi uma isetshenziswa nini?
I-Excel macro iyisethi yemiyalelo yokuhlela egcinwe kulokho okuyaziwa ngokuthi ikhodi ye-VBA engasetshenziswa ukuqeda isidingo sokuphinda izinyathelo zemisebenzi evame ukuyenza kaningi.
Lezi zinto eziphindaphindiwe zingase zibandakanye izibalo eziyinkimbinkimbi ezidinga ukusetshenziswa kwamafomula noma zingase zibe imisebenzi elula yokufometha - njengokungeza ukufometha kwenombolo kumininingwane emisha noma ukusebenzisa amafomethi weselula namakhasi okusebenza njengemingcele nokufaka shading.
Okunye imisebenzi ephindaphindiwe yimuphi ama-macros angasetshenziswa ukulondoloza afaka:
- ungeze noma ususe imigqa namakholomu
- ukuvikela noma ukuvimbela ama-worksheets
- ukhethe uhla lwamaseli
- engeza usuku lwamanje kushidi lokusebenzela
Idala i-Macro
Ama-Macros angabangela ukunqamulela kwekhibhodi, isithonjana sesibha yamathuluzi noma inkinobho noma isithonjana esingeziwe ekhasini lokusebenzela.
Macros vs. Izifanekiso
Ngesikhathi usebenzisa ama-macros kungaba yisikhathi esikhulu sokusindisa imisebenzi, uma ufaka ngezici ezithile zokufometha noma okuqukethwe - njengezihloko, noma uphawu lwekhampani kumashidi omsebenzi amasha, kungaba ngcono ukudala nokugcina ifayela lethempulethi equkethe zonke izinto ezinjalo kunokuba udale kabusha uma uqala iphepha lokusebenzela elisha.
I-Macros ne-VBA
Njengoba kukhonjisiwe, ku-Excel, ama-macros abhalwe ku-Visual Basic for Applications (VBA). Ukubhala i-Macros usebenzisa i-VBA kwenziwa efasiteleni lomhleli we-VBA, okungavuleka ngokuchofoza isithonjana se-Visual Basic kuthebhu yabathuthukisi bebhande (bheka ngezansi ukuze uthole imiyalelo ngokufaka ithebhu yabathuthukisi kubhebhoni uma kudingeka).
I-Excel & # 39; s i-Macro Recorder
Kulabo abangakwazi ukubhala ikhodi ye-VBA, ine- recorder yama-macro eyakhelwe ngaphakathi evumela ukuthi urekhode uchungechunge lwezinyathelo usebenzisa ikhibhodi negundane ukuthi i-Excel iphinde iguqulwe ibe yi-VBA ikhodi yakho.
Njengomhleli we-VBA okukhulunywe ngenhla, i-Macro Recorder ifakwe kuthebhu yabathuthukisi be-Ribbon.
Ukungeza ithebhu yonjiniyela
Ngokuzenzakalelayo ku-Excel, ithebhu yonjiniyela ayikho kwi-Ribbon. Ukwengeza:
- Chofoza ithebhu yefayela ukuze uvule uhlu lokudonsa phansi lwezinketho
- Kuhlu lokudonsa phansi, chofoza Izinketho ukuvula ibhokisi lengxoxo ye- Excel Izinketho
- Kwiphaneli lesandla sokunxele lebhokisi lebhokisi, chofoza okuthi Yenza i-Ribbon ukuze uvule iWebhu yefasitela ngokwezifiso
- Ngaphansi kwesigaba samathuluzi amakhulu ngakwesokudla, qhafaza ebhokisini lokuhlola eliseduze ne- Developer ukuze ungeze le ithebhu ku-Ribbon
- Chofoza OK ukuvala ibhokisi lengxoxo bese ubuyela ekhasini lokusebenzela.
Umthuthukisi kufanele manje abe khona - ngokuvamile ngakwesokudla ngakwesokudla kweRibhoni
Ukusebenzisa i-Macro Recorder
Njengoba kukhonjisiwe, i-Macro Recorder yenza lula umsebenzi wokwakha ama-macros - ngisho, ngezinye izikhathi, kulabo abangabhala ikhodi ye-VBA, kodwa kunamaphuzu ambalwa okufanele uwaqaphele ngaphambi kokuba uqale ukusebenzisa leli thuluzi.
1. Hlela iMacro
Ukuqopha i-Macros ne-Macro Recorder kuhilela kancane ijika lokufunda. Ukuze kube lula ukwenza lolu hlelo, hlela kusengaphambili kwesikhathi - kuze kube sekugcineni kokubhala ukuthi yini i-macro ehloselwe ukuyenza kanye nezinyathelo ezizodingeka ukuze ufeze umsebenzi.
2. Gcina i-Macros Encane futhi ecacile
I-macro enkulu kakhulu ngokwemisebenzi yemisebenzi eyenza kube nzima nakakhulu ukuyihlela nokuyiqopha ngempumelelo.
Ama-macros amakhulu nawo ahamba kancane - ikakhulukazi lawo ahlanganisa izibalo eziningi emafishishini amakhulu - futhi kunzima ukulungisa futhi ukulungisa uma kungasebenzi kahle okokuqala.
Ngokugcina ama-macros amancane futhi acacile ngenhloso kulula ukuqinisekisa ukunemba kwemiphumela nokubona ukuthi ahambaphi uma izinto zingahambi njengoba zihleliwe.
3. Igama elithi Macros Ngokufanelekile
Amagama we-Macro ku-Excel anemikhawulo eminingi yokuqamba igama okufanele ibonwe. Okokuqala futhi okubaluleke kakhulu ukuthi igama elikhulu kufanele liqale ngencwadi yama-alfabhethi. Izinhlamvu ezilandelayo zingaba izinombolo kodwa amagama ama-macro awakwazi ukufaka izikhala, izimpawu, noma izimpawu zokubhala.
Igama lakho kuzibuyekezo ezindlule manje selivela "Umsebenzisi we-Google". Bhalisela i-Google + Izibuyekezo ze-Google Play manje zisebenzisa i-Google + ngakho-ke kulula ukubona imicabango yabantu esuka kubantu obakhathalelayo. Izibuyekezo ezintsha zixhunyaniswa ngokusesidlangalaleni kuphrofayeli yakho ye-Google + .
Ngenkathi amagama ama-macro angaba yizinhlamvu ezingu-255 ubude akudingeki ukuthi asebenzise abaningi ngaleli gama.
Ngolunye, uma unama-macros amaningi futhi uhlela ukuwasebenzisa kusukela ebhokisini lengxoxo lama-macro, amagama amade nje abangela ukubambisana okwenza kube nzima ukukhetha i-macro oyilandelayo.
Indlela engcono kuyoba ukugcina amagama amfushane futhi asebenzise indawo yenkcazelo ukunikeza imininingwane mayelana nokuthi yiyiphi i-macro ngayinye eyenzayo.
I-Underscore ne-Internal Capitalization ngamagama
Njengoba amagama ama-macro awakwazi ukufaka izikhala, umlingisi oyedwa ovunyelwe, futhi okwenza amagama wokufunda ama-Macro abe lula yilolu hlobo olungagqamile olungasetshenziswa phakathi kwamagama endaweni yesikhala - njenge-Change_cell_color noma Addition_formula.
Enye indlela ukusebenzisa imali yangaphakathi (ngezinye izikhathi ibizwa ngokuthi i- Camel Case ) eqala igama ngalinye elisha egameni ngencwadi enkulu - njenge-ChangeCellColor ne-AddeditionFormula.
Amagama ama-macro ambalwa kulula ukukhetha ebhokisini lengxoxo lama-macro, ikakhulukazi uma ishidi lomsebenzi liqukethe ama-macros amaningi bese ubhala amanothi amaningi, ukuze ukwazi ukuwabona kalula. Uhlelo lubuye lunikeze insimu yokuchaza, nakuba kungekhona wonke umuntu oyisebenzisayo.
4. Sebenzisa Izihlobo Ezibhekene Nezihlobo Ezihlobene Namaqiniso Nama-Absolute
Izinkomba zeselula , njenge-B17 noma i-AA345, zikhomba indawo yeseli ngalinye kushidi lokusebenzela.
Ngokuzenzakalelayo, kwi- Macro Recorder zonke izingqikithi zamaseli ziphelele ukuthi kusho ukuthi izindawo zangempela zeseli zilotshwe ku-macro. Ngaphandle kwalokho, ama-macros angasethwa ukusebenzisa izinkomba ezisezingeni elihlobene nalokho okusho ukuthi ukunyakaza (mangaki amakholomu asele noma kwesokudla uhambisa isilayidi seseli) kubhaliwe kunezindawo eziqondile.
Yikuphi oyisebenzisayo kuxhomeke kulokho i-macro ehlelwe ukufeza. Uma ufuna ukuphinda izinyathelo ezifanayo - njengokufometha amakholomu wedatha - ngokuphindaphindiwe, kodwa njalo uma ufometha amakholomu ahlukene kushidi lokusebenzela, bese usebenzisa izikhombisi ezihambisanayo kuzodingeka.
Uma, ngakolunye uhlangothi, ufuna ukufometha ububanzi obufanayo bamaseli - njenge-A1 ukuya ku-M23 - kodwa kumafomu okusebenza ahlukene, ngakho-ke izingqikithi zeseli eziphelele zingasetshenziswa ukuze ngaso sonke isikhathi i-macro igijime, isinyathelo sokuqala ukuhambisa isilawuli seseli kwiseli A1.
Ukushintsha izingqikithi zamangqamuzana kusuka ekuhambeni okuphelele kuya kwenziwa kalula ngokuchofoza isithonjana se- Use Rel References kuThuthukisi labathengi.
5. Ukusebenzisa i-Keyboard Keys ngokuMouse
Ukuba nezinkinobho zekhibhodi zokuqopha ama-macro uma uhambisa isikhombisi se-cell noma ukhetha uhla lwamaseli ngokuvamile kuvame ukuba nokunyakaza kwegundane okurekhodiwe njengengxenye ye-macro.
Ukusebenzisa inhlanganisela yamakhibhodi ekhibhodi - njenge Ctrl + End noma Ctrl + Shift + ikhi le-Right Arrow - ukuhambisa iseleli eselimeni emkhakheni wedatha (lawo maseli aqukethe idatha kumshicileli wamanje) esikhundleni sokucindezela ngokuphindaphindiwe umcibisholo noma ithebhu izinkinobho zokuhambisa amakholomu amaningi noma imigqa yenza lula inqubo yokusebenzisa ikhibhodi.
Ngisho noma kuziwa ekusebenziseni imiyalo noma ngokukhetha izinketho zebhebhoni usebenzisa izinkinobho zokunciphisa ikhibhodi kungcono ukusebenzisa i-mouse.