Mene ne Macro a Excel kuma Yaushe Ana Amfani?
Macel Excel shine saitin umarnin shirin da aka adana a abin da aka sani da lambar VBA wanda za a iya amfani dashi don kawar da buƙatar sake maimaita matakai na ayyuka da yawa akai-akai.
Wadannan ayyuka na sakewa zasu iya haɗa da lissafin ƙididdiga waɗanda suke buƙatar yin amfani da samfurori ko kuma zasu iya zama sauƙin ayyuka na tsarawa - kamar ƙara tsarin lambobi zuwa sababbin bayanai ko yin amfani da tantanin halitta da tsarin aiki irin su iyakoki da shading.
Sauran ayyukan da aka yi amfani da su don amfani da macros za su iya amfani da su:
- Ƙara ko cire layuka da ginshiƙai
- karewa ko kare kayan aiki
- zabi wani kewayon sel
- Ƙara kwanan wata zuwa wata takarda
Ƙara wani Macro
Ana iya jawo macros ta hanyar gajerun hanyoyin keyboard, madogarar kayan aiki ko maɓalli ko gunkin da aka kara zuwa takardun aiki.
Macros vs. Samfura
Yayinda ake amfani da macros zai iya zama babban tsinkayyar lokaci don ayyuka na sakewa, idan kun ƙara wasu siffofin tsarawa ko abun ciki - irin su rubutun, ko alamar kamfanin zuwa sababbin kayan aiki, zai zama mafi alhẽri don ƙirƙirar da ajiye fayilolin samfuri wanda ke dauke da waɗannan abubuwa. maimakon ƙirƙirar su sabuntawa duk lokacin da ka fara sabon takardun aiki.
Macros da VBA
Kamar yadda aka ambata, a cikin Excel, an rubuta macros a cikin Kayayyakin Gida don Aikace-aikace (VBA). Ana yin amfani da VBA ta yin amfani da VBA a cikin babban editan VBA, wanda za a iya bude ta danna kan maɓallin Kayayyakin Kayan aiki akan Ƙunƙidar shafin ɗigon rubutun (duba ƙasa don umarnin akan ƙara Ƙungiyar Masu Tsarawa zuwa rubutun idan an buƙata).
Macel Recorder Excel & # 39;
Ga wadanda basu iya rubuta rubutun VBA ba, suna da rikodin macro mai ginawa wanda ke ba ka damar rikodin matakai ta yin amfani da keyboard da linzamin kwamfuta da Excel sannan suka tuba zuwa lambar VBA don ku.
Kamar Editan VBA da aka ambata a sama, Macro Recorder yana kan shafin Developers ta Ribbon.
Ƙara Tabbin Developer
By tsoho a cikin Excel, shafin Developer bai kasance a kan Ribbon ba. Don ƙara da shi:
- Danna fayil ɗin Fayil don bude jerin jerin zaɓuɓɓuka
- A jerin jeri, danna Zabuka don buɗe akwatin maganganu na Excel Zabuka
- A cikin ɓangaren hagu na akwatin maganganu, danna kan Zaɓin rubutun don buɗe Fassarar Rubutun Ribbon
- A ƙarƙashin Shafuka na Shafuka a hannun dama, danna kan akwati kusa da Developer don ƙara wannan shafin zuwa Ribbon
- Danna Ya yi don rufe akwatin maganganu kuma komawa zuwa aikin aiki.
Dole Developer ya kasance a yanzu - yawanci a gefen dama na Ribbon
Yin amfani da Macro Recorder
Kamar yadda aka ambata, Macro Recorder ya saukake aiki na samar da macros - har ma, a wasu lokuta, ga waɗanda suka iya rubuta lambar VBA, amma akwai wasu matakai da za su sani kafin ka fara amfani da wannan kayan aiki.
1. Shirya Macro
Macros rikodin tare da Macro Recorder ya ƙunshi wani ɓangaren koyo na ilmantarwa. Don sauƙaƙe da tsari, shirya kafin lokaci - har zuwa maƙasudin rubutun abin da aka yi nufin macro da kuma matakan da za a buƙaci don kammala aikin.
2. Saka Macros Small da Specific
Mahimmanci macro yana cikin sharuddan ɗawainiyar da ya aikata mafi yawan rikitarwa zai iya shirya da kuma rikodin shi da kyau.
Mafi girma macros kuma suna gudana a hankali - musamman ma wadanda suka hada da ƙididdigar lissafi a cikin manyan takardun aiki - kuma suna da wuya a tattake da gyara idan ba su aiki daidai a karon farko ba.
Ta hanyar ajiye ƙananan macros da ƙayyadaddu a manufar ya fi sauki don tabbatar da daidaitattun sakamakon da kuma ganin inda suka yi kuskure idan abubuwa ba su tafiya kamar yadda aka shirya ba.
3. Sunan Macros Daidai
Maganin Macro a Excel suna da ƙayyadaddun sunayen namomin da dole ne a kiyaye su. Abu na farko shi ne cewa sunan macro dole ne ya fara da wasika na haruffa. Bayanan haruffa na iya zama lambobi amma sunayen macro ba zasu iya haɗawa da sarari, alamu, ko alamomi ba.
Kuma macro ba zai iya ƙunshi kowane nau'in kalmomin da aka ajiye da suke ɓangare na VBA yana amfani da shi na ɓangaren harshe mai tsarawa kamar Su , GoTo , Sabo , ko Zaɓi .
Yayin da sunayen macro zasu iya zama har zuwa haruffan 255 a tsawon haka bazai yiwu ba ko buƙatar don amfani da mutane da yawa a cikin suna.
Ga ɗaya, idan kuna da macros da dama kuma kuna shirya akan gudu daga cikin akwatin maganganu na macro, dogon sunaye suna haifar da sauƙaƙe yana sa ya fi ƙarfin karɓar macro da kake bayan.
Kyakkyawan abin da zai fi dacewa shi ne don kiyaye sunayen taƙaitaccen wuri kuma yin amfani da yankin da aka kwatanta don bada cikakken bayani game da abin da kowane macro yake yi.
The Underscore da na ciki Capitalization a cikin Names
Tunda sunayen macro ba zasu iya haɗawa da sarari ba, hali guda wanda aka yarda, kuma abin da ke sa yaran da ake kira macro sun fi sauƙi shine halayyar da za a iya amfani dasu tsakanin kalmomi a wuri na sarari - irin su Change_cell_color ko Addition_formula.
Wani zaɓi shine a yi amfani da ƙididdiga na ciki (wani lokaci ana magana da ita a matsayin Kamfanin Camel ) wanda ya fara kowace kalma a cikin suna tare da babban harafi - irin su ChangeCellColor da AdditionFormula.
Mahimman sunayen macro sun fi sauƙi a samo su a cikin akwatin maganganun macro, musamman idan takardar aiki yana ƙunshe da macros da yawa kuma zaka rikodin macros masu yawa, saboda haka zaka iya gane su a cikin. Wannan tsarin yana samar da filin don bayanin, ko da yake ba kowa yana amfani da shi ba.
4. Yi amfani da Maɗaukaki vs. Sakamakon Saitunan Kasa
Siffofin salula , irin su B17 ko AA345, gano wurin da kowane tantanin halitta yake a cikin takardun aiki.
Ta hanyar tsoho, a cikin Macro Recorder duk bayanan salula ya zama cikakke wanda ke nufin cewa ainihin wuraren da aka sanya shi a cikin macro. A madadin haka, ana iya saita macros don amfani da labaran dangi wanda ke nufin cewa ƙungiyoyi (yawancin ginshiƙan da suka bar ko dama ka motsa siginan siginar) an rubuta su maimakon wurare masu mahimmanci.
Wanda kake amfani da shi ya dogara da abin da aka saita macro don cikawa. Idan kana so ka sake maimaita matakai guda ɗaya - irin su ginshiƙai bayanai na bayanai - a kan kuma a kan, amma duk lokacin da kake tsara ginshiƙai daban-daban a cikin takardun aiki, to, yin amfani da nassoshin zumunta zai dace.
Idan, a gefe guda, kana so ka tsara iri iri ɗaya na sel - irin su A1 zuwa M23 - amma a kan daban-daban ayyuka, to, zaku iya amfani da cikakkun bayanai na labura don kowane lokaci macro ke gudana, mataki na farko shi ne motsawa wayar siginan kwamfuta zuwa cell A1.
Ana canza saurin tantanin halitta daga dangi zuwa cikakke za'a iya aikatawa ta hanyar danna kan mahaɗin Abubuwan Amfani da Abubuwan Abubuwan Amfani da Abubuwan Taɓaɓɓun shafin.
5. Amfani da Keyboard Keys vs. Mouse
Samun macro rikodin keystrokes key lokacin da motsi siginar salula ko zaɓi wani kewayon sel ya fi dacewa da kasancewar ƙungiyoyi masu linzamin kwamfuta da aka rubuta a matsayin ɓangare na macro.
Amfani da haɗin maɓallin kullin - kamar Ctrl + End ko Ctrl + Shift + Maɓallin Dama mai Dama - don matsar da siginar siginan kwamfuta a gefuna na yankin bayanai (waɗannan kwayoyin dake dauke da bayanai a kan aikin aiki na yanzu) maimakon maimaita danna ko shafin maɓallan don motsa ginshiƙai ko wasu layuka suna sauƙaƙe tsarin aiwatar da amfani da keyboard.
Ko da idan ya shafi yin amfani da umarni ko zaɓin zabin rubutun ta amfani da makullin gajeren maɓallin keyboard shine mafi dacewa ta yin amfani da linzamin kwamfuta.