Iyiphi i-Macro kwi-Excel kwaye iyasetyenziswa nini?
I-Excel macro yiseti yemiyalelo yoqwalaselo egcinwe kwizinto ezibizwa ngokuba yi- VBA ikhowudi engasetyenziselwa ukuqeda imfuneko yokuphinda amanyathelo omsebenzi oqhelekileyo owenziwa rhoqo ngokuphindaphindiweyo.
Imisebenzi yokuphindaphinda ingabandakanya ukubala okulukhuni okufuna ukusetyenziswa kwefomula okanye zibe yinto elula yokufometha imisebenzi - njengokungenisa ukufometha kwenombolo kwidatha entsha okanye ukusebenzisa iifom zefomathi kunye neefomathi zokusebenzela ezifana nemida kunye no-shading.
Ezinye izinto eziphindaphindiweyo apho ii-macros ezingasetyenziswa ukugcina ziquka:
- ukongeza okanye ukususa imigqa kunye neikholomu
- ukukhusela okanye ukukhusela ii-sheetworksheets
- ukhetha uluhlu lweeseli
- ukongezelela umhla okhoyo kwiphepha lokusebenzela
Ukubangela iMacro
I-Macros inokubangela i-shortcut yebhodibhodi, uphawu lwebarbar okanye iqhosha okanye uphawu olongeziweyo kwiphepha lokusebenzela.
Macros vs. Templates
Xa usebenzisa i-macros ingaba ngumsindisi omkhulu wemizuzu yokuphindaphinda imisebenzi, ukuba uhlala ufaka ezinye iimpawu zokufomathiza okanye umxholo - njengezihloko, okanye uphawu lweenkampani kwiifom ezitsha zamaphepha, mhlawumbi kungcono ukudala nokugcina ifayile ye template equkethe zonke izinto ezinjalo kunokuba udale kwakhona xa uqala iphepha lokusebenzela elitsha.
Macros kunye neVBA
Njengoko kukhankanywe, kwi-Excel, ii-macros zibhalwa kwi-Visual Basic yeZicelo (VBA). Ukubhala i-Macros usebenzisa i-VBA yenziwe kwifayile ye-VBA yomhleli, onokuvulwa ngokuchofoza uphawu lwe-Visual Basic kwiTable Developers yebhoni (jonga ngezantsi imiyalelo ngokufaka iThebhu yonxibelelwano kwibhebhoni xa kuyimfuneko).
Excel & # 39; s Macro Recorder
Kulabo abangakwazi ukubhala ikhowudi ye-VBA, ine- rekhoda ekwi- macro ekwakhiweyo ekuvumela ukurekhoda uluhlu lwamanyathelo usebenzisa ikhibhodi kunye nemouse ukuba i-Excel iguqule ibe yi-VBA ikhowudi kuwe.
Njengomhleli we-VBA ukhankanywe ngasentla, i-Macro Recorder ifumaneke kwiTebhu yeThuthukisi.
Ukongeza ithebhu yonjiniyela
Ngokungagqibekanga kwi-Excel, ithebhu yonjiniyela ayikho kwiRibhoni. Ukongeza:
- Cofa kwiTebhu yefayile ukuvula uluhlu lwezinto ezikhethiweyo
- Kuluhlu lokuhlahla, nqakraza iinketho ukuvula ibhokisi yencoko yebhokisi ye - Excel
- Kwiphepha lesandla sobunxele kwibhokisi yencoko, nqakraza ngokwezifiso iRibhoni ukuze uvule iWebbon window
- Ngaphantsi kweCandelo eliKhethekileyo leTabhu kwifestile lesandla sokunene, cofa kwibhokisi yokukhangela elilandelayo kuMninimzi ukufaka le tab kwi Ribbon
- Cofa OK ukuvala ibhokisi yencoko kwaye ubuyele kwiphepha lokusebenzela.
Umqhubi kufuneka abe khona-ngokuqhelekileyo kwicala lasekunene leRibhoni
Ukusebenzisa i-Macro Recorder
Njengoko kubonisiwe, i-Macro Recorder yenza lula umsebenzi wokudala i-macros - kwanabanye, ngamanye amaxesha, kulabo abangabhala ikhowudi ye-VBA, kodwa kukho iingqinamba ezimbalwa ozaziyo phambi kokuba uqale ukusebenzisa le sixhobo.
1. Cwangcisa iMacro
Ukurekhoda i-Macros kunye ne-Macro Recorder kuquka inxalenye yendlela yokufunda. Ukwenza lula inkqubo, cwangcisa phambili kwexesha-kude kube yindawo yokubhala oko i-macro ejoliswe ukuyenza kunye namanyathelo aza kufuneka ukuze aphumeze umsebenzi.
2. Gcina iiMacros ezincinci kunye ezicacileyo
I-macro enkulu kakhulu ngokwemigangatho yemisebenzi eyenza nzima ngakumbi ukuba iya kucwangciswa kwaye uyirekhodi ngempumelelo.
Ii-macros ezinkulu zibuye zihambe ngokukhawuleza-ngakumbi ezo zibandakanya izibalo ezininzi kwiifayile ezinkulu zokusebenzela - kwaye zinzima ukukhuphaza kwaye zichanekile ukuba zingasebenzi kakuhle okokuqala.
Ngokugcina ii-macros ezincinci kunye ezithile kwiinjongo kulula ukuqinisekisa ukuchaneka kweziphumo kunye nokubona apho behamba khona xa izinto zingahambi njengoko zicwangcisiweyo.
3. Igama elithi Macros ngokufanelekileyo
Amagama e-Macro kwi-Excel anemiqathango emininzi yokuqamba igama ekufuneka ibonwe. Okokuqala kwaye okuphambili kukuba igama elibanzi kufuneka liqale ngeleta yealfabhethi. Abalinganiswa abalandelayo banokuba ngamanani kodwa amagama amaninzi akakwazi ukufaka izikhala, iimpawu, okanye iimpawu zokubhala.
Akunakwenzeka ukuba igama elininzi linalo naluphi lwamagama alondoloziweyo ayingxenye ye-VBA esebenzisa njengenxalenye yolwimi lwenkqubo efana ne- If , GoTo , New , okanye Khetha .
Nangona amagama angama-macro angaba ngama-255 angamahlamvu ubude akusoloko efunekayo okanye ecebisekileyo ukusebenzisa ezininzi kwigama.
Enye, ukuba unamacros amaninzi kwaye uceba ukuwasebenzisa kwibhokisi yencoko ebalulekileyo, amagama amade nje angabangela ukudibanisa ukwenza kube nzima ukukhetha i-macro emva kwakho.
Indlela engcono kukuba kugcinwe amagama amfutshane kwaye asebenzise indawo yenkcazo ukunika iinkcukacha malunga nokuba yiyiphi i-macro nganye.
I-Underscore ne-Internal Capitalization ngamagama
Ekubeni amagama amaninzi akakwazi ukufaka izikhala, uphawu olulodwa oluvunyelwe, nolwenza ukuba ukufunda amagama macro kube lula ngumgca ongasentla ongasetyenziswa phakathi kwamagama endaweni yendawo - njengeTshintsho_cell_color okanye i-Addition_formula.
Enye inxaxheba kukusebenzisa i-capital capital (ngezinye iinkcukacha ibizwa ngokuba yiCamel Case ) eqala igama ngalinye elitsha kwigama elinobumba obuninzi - njenge-ChangeCellColor ne-AdditionFormula.
Amagama amfutshane macro alula ukukhetha kwibhokisi yencoko yababini, ngokukodwa ukuba iphepha lokusebenzela liqukethe inani lee-macros kwaye ubhala amanqaku amaninzi, ukuze ukwazi ukuwafumanisa kalula. Inkqubo inika kwakhona insimba yeNkcazo, nangona bonke abayisebenzisiyo.
4. Sebenzisani iziNxulumaniso eziLungileyo kunye neeReferensi zeNgcaciso
Iingqungquthela zeselula , ezifana ne-B17 okanye i-AA345, zichonga indawo yeseli nganye kwiphepha lokusebenzela.
Ngokungagqibekanga, kwi- Macro Recorder zonke izikhokelo zeseli zithetha ngokupheleleyo ukuba indawo ezikhoyo zeseli zibhaliswe kwi-macro. Ngaphandle koko, ii-macros zingasetyenziselwa ukusebenzisa izithenxo zeseli ezihambelanayo ezithetha ukuba ukunyuka (zingaphi iikholomu ezishiywe okanye ngokunene uhambisa isalathisi seseli) zibhalwa ngaphandle kweendawo ezichanekileyo.
Leyiphi oyisebenzisayo kuxhomekeke kwizinto eziphambili ezilungiselelwe ukufezekisa. Ukuba ufuna ukuphinda amanyathelo afanayo - njengokufomatha iikholomu zedatha - ngaphezulu nangaphezulu, kodwa ngexesha ngalinye ufomathile iikholomu ezahlukileyo kwiphepha lokusebenzela, ngoko usebenzise izikhombisi ezihambelana nazo.
Ukuba, ngakolunye uhlangothi, ufuna ukufomathiza uluhlu olufanayo lweeseli - njenge-A1 ukuya kwi-M23 - kodwa kwiifayile ezihlukeneyo, ngoko ke izikhombisi zeseli ezipheleleyo zingasetyenziselwa ukwenzela ukuba ixesha ngalinye liqhubekele, isinyathelo sokuqala ukuhambisa isalathisi seseli kwi-cell A1.
Ukutshintsha iiferensi zeeselfm ukusuka kwizinto ezihambelana nokugqithiseleyo kulula ngokuklikha kwi icon Use Relative References kwiTebhuthukisi yoThuthuli.
5. Ukusebenzisa i-Keyboard Keys ngokuMouse
Ukuba neenkcukacha ezinkulu zokubhaliweyo zebhodibhodi xa uhambisa isikhombisi sesisele okanye ukhetha uluhlu lweeseli luvame ukukhetha ukunyakaza kwemouse kubhalwe njengengxenye ye-macro.
Ukusebenzisa ukudibanisa ukhiye wekhibhodi - njengeCtrl + End okanye Ctrl + Shift + iqhosha lasekhohlo lasekunene - ukuhambisa isalathisi seseli kwimida yedatha yecandelo (ezo iiseli eziqulethwe kwiphepha lokusebenzela) kunokuba ucindezele ngokuphindaphindiweyo itolo okanye ithebhu izitshixo zokuhambisa iikholomu ezininzi okanye imigqa yenza lula inkqubo yokusebenzisa ikhibhodi.
Nangona kufikelele ekusebenziseni imiyalelo okanye ukhetho lweerbbon ukhetho usebenzisa izitshixo zokuncinci zekhibhodi ezikhethekileyo ukusebenzisa imouse.