Microsoft Excel macros na iya hanzarta yin aiki tare da takardu a cikin wannan edita. Wannan ana samunshi ta atomatik ayyuka na maimaitawa wanda aka rubuta a lamba ta musamman. Bari mu ga yadda ake ƙirƙirar macros a cikin Excel, da kuma yadda ake shirya su.
Hanyoyin Rikodin Macro
Ana iya rubutu macro ta hanyoyi biyu:
- ta atomatik;
- da hannu.
Ta amfani da zaɓi na farko, kawai za ka yi rikodin wasu ayyuka a cikin aikin Microsoft Excel wanda kake aiwatarwa a halin yanzu. To, zaku iya yin wannan rikodin. Wannan hanyar tana da sauƙi, kuma baya buƙatar sanin lambar, amma aikace-aikacensa a aikace yana da iyakantaccen iyaka.
Rikodin Macro na Manu, akasin haka, yana buƙatar ilimin shirye-shirye, tunda ana buga lamba da hannu daga maballin. Amma, madaidaiciyar lambar da aka rubuta ta wannan hanyar na iya hanzarta aiwatar da ayyukan.
Rikodin Macro ta atomatik
Kafin ka fara rikodin macro ta atomatik, dole ne a kunna macros a cikin Microsoft Excel.
Na gaba, je zuwa shafin "Mai haɓaka". Latsa maɓallin "Macro Record", wanda ke kan haƙarƙarin a cikin toshe kayan aiki "Code".
Wurin saita saitin Macro yana buɗewa. Anan zaka iya tantance kowane sunan macro idan tsohuwar bata dace da kai ba. Babban abu shine sunan yana farawa da harafi, kuma ba tare da lamba ba. Hakanan, taken bai ƙunshi sarari ba. Mun bar tsohuwar suna - "Macro1".
Nan da nan, idan ana so, zaku iya saita gajerar hanya, idan aka latsa, za a ƙaddamar da macro. Makullin farko dole ne ya kasance mabuɗin Ctrl, kuma mai amfani ya saita maɓallin na biyu daban daban. Misali, mu, a matsayin misali, mun sanya mabuɗin M.
Abu na gaba, kuna buƙatar ƙayyade inda za'a adana macro. Ta hanyar tsoho, za a adana shi a cikin littafin (fayil) guda, amma idan kuna so, zaku iya saita ajiya a cikin sabon littafi, ko a cikin littafin macros daban. Zamu bar darajar asali.
A cikin ƙasa tushe na saitunan macro, zaku iya barin kowane bayanin macro wanda ya dace da mahallin. Amma, wannan ba lallai ba ne.
Lokacin da aka gama saitunan duka, danna maɓallin "Ok".
Bayan haka, duk ayyukan ku a cikin wannan littafin Jagora (fayil) za a rubuta su a cikin wani macro har ku kanku da kuka daina yin rikodi.
Misali, muna yin saurin aikin lissafi mafi sauki: kara abubuwan da ke cikin sel uku (= C4 + C5 + C6).
Bayan haka, danna maɓallin "Tsaya Rikodi". Wannan maballin ya canza daga maɓallin "Macro Record", bayan da aka fara yin rikodi.
Macro gudu
Domin bincika yadda macro da aka yi rikodin yake aiki, danna maɓallin "Macros" a cikin toolbar ɗin "Code" ɗin, ko latsa Alt + F8.
Bayan haka, taga yana buɗe tare da jerin jerin macros da aka rubuta. Muna neman macro da muka yi rikodin, zaɓi shi, kuma danna maɓallin "Run".
Kuna iya yin sauƙi ko da, kuma kar ma kira taga zaɓi na macro. Mun tuna cewa munyi haduwa da "mabuɗin zafi" don kiran macro cikin sauri. A cikin yanayinmu, wannan shine Ctrl + M Mun buga wannan haɗin a cikin keyboard, bayan wanda macro ya fara.
Kamar yadda kake gani, Macro ya yi daidai duk ayyukan da aka rubuta a baya.
Gyara Macro
Domin shirya macro, danna maɓallin "Macros". A cikin taga da ke buɗe, zaɓi macro da ake so, kuma danna maɓallin "Canza".
Yana buɗe Microsoft Visual Basic (VBE) - muhalli inda macros ke gyarawa.
Rikodin kowane macro yana farawa da umarnin Sub, kuma yana ƙare da End Sub umurnin. Nan da nan bayan umurnin Sub, ana nuna sunan macro. Mai aiki da "Range (" ... ") Zaɓi zaɓi zaɓi na tantanin halitta. Misali, tare da umarnin “Range (“ C4. ”) Zaɓi,” An zaɓi tantanin halitta C4. Mai amfani da "ActiveCell.FormulaR1C1" ana amfani dashi don yin rikodin ayyuka a cikin tsari, da kuma wasu lissafin.
Bari muyi kokarin canza macro kadan. Don yin wannan, ƙara magana a cikin macro:
Range ("C3"). Zaɓi
ActiveCell.FormulaR1C1 = "11"
Kalmar "ActiveCell.FormulaR1C1 =" = R [-3] C + R [-2] C + R [-1] C "an maye gurbinsu da" ActiveCell.FormulaR1C1 = "= R [-4] C + R [-3 ] C + R [-2] C + R [-1] C "."
Mun rufe edita, kuma muna gudanar da macro, kamar yadda ƙarshe. Kamar yadda kake gani, saboda canje-canjen da muka gabatar, an ƙara wani sel data. Hakanan an haɗa shi cikin lissafin jimlar.
Idan Macro yayi girma da yawa, zai iya ɗaukar tsawon lokaci kafin a zartar. Amma, ta yin canjin mai amfani zuwa lambar, zamu iya hanzarta aiwatarwa. Sanya umarnin "Application.ScreenUpdating = Karya". Zai kiyaye ikon sarrafa lissafi, wanda ke nufin hanzarta aiki. Ana samun wannan ta hanyar ƙin yin sabunta allo yayin ayyukan kwamfuta. Don ci gaba da sabuntawa bayan kashe macro, a ƙarshen muna rubuta umarnin "Application.ScreenUpdating = Gaskiya"
Theara umarni "Application.Calculation = xlCalculationManual" a farkon lambar, kuma a ƙarshen lambar mun ƙara "Application.Calculation = xlCalculationAutomatic". Don haka, a farkon Macro, muna kashe sakamakon sakamako na atomatik sakamakon kowace canzawar kwayar, kuma a ƙarshen macro, kunna. Don haka, Excel zai lissafta sakamakon sau ɗaya kawai, kuma ba zai riƙa ba da labari ba koyaushe, wanda zai ceci lokaci.
Rubuta lambar macro daga karce
Masu amfani da ci gaba ba za su iya shirya da haɓaka macros da aka rubuta ba, har ma suna rubuta lambar macro daga karce. Don fara wannan, kuna buƙatar danna maballin "Kayayyakin Kayayyakin", wanda yake a farkon farkon ƙirar ƙirar.
Bayan wannan, sanannen editar taga taga yana buɗewa.
Mai shirye-shirye yana rubuta lambar macro a ciki da hannu.
Kamar yadda kake gani, macros a cikin Microsoft Excel na iya hanzarta aiwatar da ayyukan yau da kullun da tsarin tsari. Amma, a mafi yawan lokuta, macros wanda aka rubuta lambar sa hannu da hannu maimakon yin rikodin ayyukan ta atomatik sun fi dacewa da wannan. Bugu da kari, za a iya inganta lambar macro ta hanyar editan VBE don hanzarta aikin.
