Kodi Macro mu Excel ndi Nthawi Yanji Imagwiritsidwa Ntchito?
An Excel macro ndi ndondomeko ya mapulogalamu omwe amasungidwa m'Chidziwitso VBA chomwe chingagwiritsidwe ntchito kuthetsa kufunika kobwerezabwereza ntchito zomwe anthu amachita nthawi ndi nthawi.
Ntchito zobwerezabwereza izi zingaphatikizepo zowerengera zovuta zomwe zimafuna kugwiritsa ntchito mafomu kapena zingakhale zosavuta kugwira ntchito - monga kuwonjezera chiwerengero cha chiwerengero ku deta yatsopano kapena kugwiritsa ntchito mawonekedwe a selo ndi maofesi monga mipaka ndi shading.
Ntchito zina zobwereza zomwe macros angagwiritsidwe ntchito kupulumutsa ndizo:
- kuwonjezera kapena kuchotsa mizere ndi mizere
- kuteteza kapena kutetezera ma sheetsi
- kusankha maselo osiyanasiyana
- kuwonjezera tsiku lamakono ku tsamba lokumbukira
Kuyambitsa Macro
Macros ikhoza kuyambitsidwa ndi njira yachinsinsi, chojambula chazamasewero kapena batani kapena chithunzi chomwe chinayikidwa pa tsamba.
Macros vs. Zithunzi
Pamene mukugwiritsa ntchito macros kungakhale nthawi yabwino yopulumutsa ntchito zowonjezera, ngati mumakonda kuwonjezera maonekedwe ena kapena zokhutira - monga zilembo, kapena chizindikiro cha kampani ku mapepala atsopano, zingakhale bwino kupanga ndi kusunga fayilo ya template yomwe ili ndi zinthu zonsezi m'malo mowalenga mwatsopano nthawi iliyonse mukayambitsa pepala latsopano.
Macros ndi VBA
Monga tafotokozera, mu Excel, macros amalembedwa ku Visual Basic for Applications (VBA). Macros akugwiritsa ntchito VBA imachitika pawindo la VBA editor, lomwe lingatsegulidwe mwa kuwonekera pa Visual Basic icon pa Tsatanetsatane tabu la riboni (onani m'munsimu kwa malangizo powonjezerapo Tsatanetsatane pakabuyi ngati mukufunikira).
Zolemba za Excel & # 39; s Macro
Kwa iwo omwe sangathe kulemba VBA code, ali ndi zojambula zambiri zojambulidwa zomwe zimakulolani kulembetsa masitepe pogwiritsa ntchito kibokosi ndi mbewa kuti Excel ndiye mutembenuzire ku VBA code kwa inu.
Mofanana ndi mkonzi wa VBA wotchulidwa pamwambapa, Wolemba Macro ali pa Tsamba la Okonza la Ribbon.
Kuwonjezera Pakanema Khonde
Mwachindunji mu Excel, tabu ya Chinsinthiki sichipezeka pa Mpikisano. Kuwonjezera:
- Dinani pa Fayilo Fayilo kuti mutsegule ndondomeko yotsitsa
- Pa ndondomeko yotsika pansi, dinani Zosankha kuti mutsegule Zokambirana za Excel Zokambirana
- Mu dzanja lamanzere la bokosi la bokosi, dinani pazomwe Mungakonzere mphukira kuti mutsegule Zenera lazenera
- Pansi pa tsamba la Main Tabs muwindo lamanja, dinani pakani pafupi ndi Mtsogoleli kuti muwonjezere tabuyi ku Ribbon
- Dinani OK kuti mutseke bokosi la bokosi ndi kubwerera kuntchito.
Wotsinthana ayenera tsopano kukhalapo - nthawi zambiri kumbali ya dzanja lamanja la Ribbon
Pogwiritsa ntchito zolemba Macro
Monga tafotokozera, Macro Recorder imachepetsa ntchito yopanga macros - ngakhale, nthawi zina, kwa iwo omwe angathe kulemba VBA code, koma pali mfundo zingapo zomwe muyenera kuzidziwa musanayambe kugwiritsa ntchito chida ichi.
1. Konzani Macro
Kulemba Macros ndi zolemba Macro kumaphatikizapo pang'ono kuphunzira. Kuti mukhale ophweka, konzekerani pasadakhale - mpaka kufika polemba zomwe macro akufuna kuti muchite ndi njira zomwe zidzafunikire kukwaniritsa ntchitoyi.
2. Sungitsani Macros Small ndi Specific
Zowonjezera zazikulu ndizowerengera za ntchito zomwe zimakhala zovuta kwambiri kuti zikonzeke ndikuzilemba bwino.
Macros akuluakulu amakhalanso othamanga - makamaka omwe akuphatikiza mawerengero ambiri m'mabuku akuluakulu - ndipo zimakhala zovuta kukonza ndi kukonza ngati sakugwira ntchito yoyamba nthawi yoyamba.
Mwa kusunga macros ang'onoang'ono ndi enieni mwacholinga chiri chosavuta kutsimikizira zolondola za zotsatira ndikuwona komwe iwo analakwitsa ngati zinthu sizipita monga momwe zakhalira.
3. Tchulani Macros Moyenerera
Mayina a Macro ku Excel ali ndi zolemba zingapo zomwe ziyenera kuwonedwa. Choyamba ndikufunika kuti dzina lalikulu liyambe ndi kalata ya zilembo. Otsatira omwe angakhalepo angakhale nambala koma maina aakulu sangaphatikize malo, zizindikiro, kapena zizindikiro zam'mapepala.
Ngakhalenso dzina lalikulu silikhoza kukhala ndi mawu angapo osungidwa omwe ali mbali ya VBA yomwe amagwiritsa ntchito monga gawo la chinenero chake monga ngati , GoTo , New , kapena Select .
Ngakhale mayina akuluakulu akhoza kukhala oposa 255 m'litali nthawi zambiri sangafunike kapena oyenera kugwiritsa ntchito ambiriwo m'dzina.
Chifukwa chimodzi, ngati muli ndi macros ambiri ndipo mukukonzekera kuwathamangitsa kuchokera ku bokosi lalikulu, mayina otalika amachititsa kuti chisokonezo chikhale chovuta kuti mutenge zomwe mwasunga.
Njira yowonjezera ikanakhala kusunga mainawo ndikugwiritsira ntchito malo omwe akufotokozera kuti afotokoze tsatanetsatane wa zomwe aliyense amachita.
Underscore ndi Internal Capitalization mu Mayina
Popeza maina aakulu sangaphatikize malo, chikhalidwe chimodzi chomwe chimaloledwa, ndi chimene chimapangitsa maina a kuwerenga kuwerenga mosavuta ndi chikhalidwe chotsindika chomwe chingagwiritsidwe ntchito pakati pa mawu m'malo mwa malo - monga Change_cell_color kapena Addition_formula.
Njira ina ndikugwiritsira ntchito ndalama zamkati mkati (nthawi zina zimatchedwa Nkhani ya Camel ) yomwe imayambitsa mawu atsopano mu dzina ndi kalata yaikulu - monga ChangeCellColor ndi AdditionFormula.
Mayina afupiafupi a macro ndi osavuta kusankha mu bokosi lalikulu, makamaka ngati pepala lili ndi macros ambiri ndipo mumalemba ma macros ambiri, kotero mumatha kuwazindikira. Mchitidwewu umaperekanso malo kufotokozera, ngakhale kuti aliyense sagwiritsa ntchito.
4. Gwiritsani ntchito Relative vs. Absolute Cell References
Maumboni a magulu , monga B17 kapena AA345, adziwe malo a selo lirilonse muwomaliza.
Mwachinsinsi, mu Makalata a Macro onse mafotokozedwe a maselo ndi amtheradi omwe amatanthawuza kuti malo enieni a malowa amalembedwa ku macro. Mwinanso, macros angagwiritsidwe ntchito pogwiritsa ntchito zigawo zomwe zimatanthauza kusunthika (ndizomwe maulendo angasiyidwe kapena momwe mumasunthira selololo) m'malo mwa malo enieni.
Chimene mumagwiritsa ntchito chimadalira zomwe zazikulu zimayikidwa kuti zikwaniritsidwe. Ngati mukufuna kubwereza masitepe omwewo - monga kupanga mazenera a deta - mobwereza bwereza, koma nthawi iliyonse mukamapanga zigawo zosiyana pazomwe mukulemba, ndiye kugwiritsa ntchito maumboni oyenera angakhale oyenera.
Ngati, ngati mukufuna kufotokoza maselo ofanana - monga A1 kupita ku M23 - koma pamasamba osiyana siyana, ndiye kuti magulu amtundu wosiyanasiyana angagwiritsidwe ntchito kuti nthawi zonse zitha kuthamanga, sitepe yoyamba ndiyo kusunthira selo ya selo ku selo A1.
Kusintha mafotokozedwe a selo kuchokera pazowona kumakhala kosavuta podalira chizindikiro cha Use Rel Ref References pa Tsatanetsatane tab ya Ribbon.
5. Kugwiritsa ntchito Keyboard Keys vs. Mouse
Pokhala ndi makina akuluakulu a makina okhwima pakusuntha selo selo kapena kusankha ma selo ambiri nthawi zambiri amakhala okonda kugwira ntchito yamagulu monga mbali ya macro.
Pogwiritsa ntchito makina a makina - monga Ctrl + Mapeto kapena Ctrl + Shift + Key Key - Kuti mutenge selolo kumbali ya deta (maselo omwe ali ndi deta pa tsamba lamasamba) m'malo mobwereza mobwerezabwereza muvi kapena tabu Makina oyendetsa masentimita kapena mizere yambiri amawathandiza kugwiritsa ntchito makinawo.
Ngakhale zokhudzana ndi kugwiritsa ntchito malamulo kapena kusankha zosakanizika pogwiritsa ntchito makiyi osatsegulira makiyi ndizotheka kugwiritsa ntchito mbewa.