Ma macros a Microsoft Excel angathe kuthamanga kwambiri ntchitoyi ndi zolembedwa m'dongosolo la spreadsheet. Izi zimapindula pogwiritsa ntchito zochita zobwerezabwereza zomwe zili mu code yapadera. Tiyeni tione momwe tingakhalire ma macros ku Excel, ndi momwe angasinthidwe.
Njira Zolembera Macros
Macros akhoza kulembedwa m'njira ziwiri:
- posachedwa;
- mwadala.
Pogwiritsa ntchito njira yoyamba, mumangolemba zochitika zina mu Microsoft Excel zomwe mukuchita panthawi yake. Ndiye, mukhoza kusewera mbiri iyi. Njirayi ndi yosavuta, ndipo safuna kudziwa chidziwitso, koma ntchito yake ndi yoperewera.
Kulemba zolemba za macros, mosiyana, kumafuna pulogalamu yolingalira, popeza chikhocho chimayikidwa pamanja kuchokera pa khibhodi. Koma, ndondomeko yoyenera kulembedwa mwanjira imeneyi ingathe kupititsa patsogolo kuchitidwa kwa njira.
Makina Ojambula Achimake
Musanayambe kujambula zojambula zamakono, muyenera kuika macros ku Microsoft Excel.
Chotsatira, pitani ku tab "Wotsambitsa". Dinani pa batani "Zolemba za Macro", zomwe ziri pa tepi mu chikhomo cha "Code".
Mawindo ojambula ojambula ambiri amayamba. Pano mungathe kufotokoza dzina lamodzi ngati zosasintha sizikugwirizana ndi inu. Chinthu chachikulu ndi chakuti dzina limayamba ndi kalata, osati nambala. Ndiponso, payenera kukhala malo omwe ali pamutu. Tasiya dzina losasintha - "Macro1".
Pano, ngati mukufuna, mungathe kukhazikitsa njira yochepetsera, mukasindikizidwa, macro ayambitsidwa. Mfungulo woyamba uyenera kukhala makiyi a Ctrl, ndipo fungulo lachiwiri limayikidwa ndi wosuta mwiniwake. Mwachitsanzo, ife, monga chitsanzo, tanikeni fungulo M.
Chotsatira, muyenera kudziwa komwe malo osungirako angasungidwe. Mwachidule, zidzasungidwa m'buku lomwelo (fayilo), koma ngati mukufuna, mukhoza kusungira yosungiramo buku latsopano, kapena m'buku lina la macros. Tidzasiya mtengo wokhazikika.
M'malo otsika kwambiri a masewera, mukhoza kusiya chilichonse chomwe chikugwirizana ndi zomwe zilipo. Koma sikofunika kuchita izi.
Pamene zonsezi zikuchitika, dinani pa "Kulungama".
Pambuyo pake, zochita zanu zonse mubuku la ntchito ya Excel (fayilo) lidzalembedwa mu macro mpaka mutasiya kujambula nokha.
Mwachitsanzo, timalemba zolemba zosavuta kwambiri: Kuwonjezera pa zomwe zili m'maselo atatu (= C4 + C5 + C6).
Pambuyo pake, dinani "Sakani kujambula" batani. Bululi linatembenuzidwa kuchoka ku batani la "Record Macro", pambuyo polemba kujambula.
Thamani Macro
Kuti muwone momwe macro olembawo amagwirira ntchito, dinani pakani la Macros mu Codebar toolbar, kapena panikizani kuphatikizira kwa Alt + F8.
Pambuyo pake, zenera likuyamba ndi mndandanda wa macros olembedwa. Tikuyang'ana zolemba zomwe tazilemba, tazisankha, ndipo dinani pa batani "Kuthamanga".
Mukhoza kuchita ngakhale zosavuta, ndipo simunatchedwe ndiwindo la kusankha. Timakumbukira kuti talemba zojambulidwa za "mafungulo otentha" a foni yofulumira. Kwa ife, izi ndi Ctrl + M. Timayitanitsa mgwirizano uwu pa kambokosi, pambuyo pake timathamanga kwambiri.
Monga mukuonera, macro anachita zochuluka zonsezi zomwe zinalembedwa poyamba.
Kusintha kwa macro
Kuti musinthe macro, dinani kachiwiri pa batani "Macros". Pawindo limene limatsegulira, sankhani chofunika kwambiri, ndipo dinani pa "Kusintha".
Microsoft Visual Basic (VBE) imatsegula - chilengedwe kumene macros akusinthidwa.
Kujambula kwa chiwerengero chilichonse kumayambira ndi gawo lachigawo, ndipo kumathera ndi lamulo la End Sub. Posakhalitsa pambuyo pa lamulo lachigawo, dzina lalikulu likufotokozedwa. Wogwiritsira ntchito "Pangani (" ... "") "Sankhani" akusonyeza kusankha kwa selo. Mwachitsanzo, pamene lamulo "Pangani (" C4 "). Sankhani" yasankhidwa selo C4. Wogwiritsira ntchito "ActiveCell.FormulaR1C1" amagwiritsidwa ntchito kulembetsa zochitika m'mafomu, ndi mawerengedwe ena.
Tiyeni tiyesetse kusintha zochepa. Kuti tichite izi, tikuwonjezera mafotokozedwe kwa macro:
Mtundu ("C3"). Sankhani
ActiveCell.FormulaR1C1 = "11"
Mawu akuti "ActiveCell.FormulaR1C1 =" = R [-3] C + R [-2] C + R [-1] C "" amachotsedwa ndi "ActiveCell.FormulaR1C1 =" = R [-4] C + R [-3 ] C + R [-2] C + R [-1] C "".
Tsekani mkonzi, ndi kuthamanga kwambiri, monga nthawi yotsiriza. Monga mukuonera, chifukwa cha kusintha komwe tinayambitsa, selo lina la deta linawonjezeredwa. Anaphatikizidwanso mu mawerengedwe a ndalama zonse.
Ngati lalikulu ndi lalikulu kwambiri, kupha kwake kungatenge nthawi yambiri. Koma, pokonza kusintha kwa code, tikhoza kufulumira ndondomekoyi. Onjezani lamulo lakuti "Application.ScreenUpdating = Onama". Idzakuthandizani kusunga mphamvu zamagetsi, ndipo motero mufulumire ntchitoyo. Izi zimapindula mwa kukana kusindikiza chinsalu pamene mukuchita zolemba zamakono. Kuti mupitirize kusinthidwa mutatha kugwiritsa ntchito macro, pamapeto pake lembani lamulo lakuti "Application.ScreenUpdating = True"
Timaonjezeranso lamulo lakuti "Application.Calculation = xlCalculationManual" kumayambiriro kwa code, ndipo pamapeto pake timakhala ndi "Application.Calculation = xlCalculationAutomatic". Mwa ichi, timaletsa kubwezeretsa kwa zotsatira pambuyo pa maselo onse osintha, ndikutembenuzira kumapeto kwa macro. Choncho, Excel iwerengera zotsatira zokha kamodzi, ndipo sichidzachiwerenganso nthawi zonse, zomwe zidzasunga nthawi.
Kulemba code yakuda kuyambira pachiyambi
Ogwiritsira ntchito mwakuya sangathe kungosintha ndi kukonzanso ma macros olembedwa, koma amalembanso kachidindo kakang'ono kuyambira pachiyambi. Kuti mupitirize ndi izi, muyenera kutsegula "batual Basic" batani, yomwe ili pachiyambi cha makina osungirako.
Pambuyo pake, mawindo a Editor VBE amawonekera.
Wolemba mapulogalamuyo amalemba malamulo aakulu pamenepo pamanja.
Monga mukuonera, macros mu Microsoft Excel akhoza kuthamanga kwambiri kuchitidwa kwachizoloŵezi ndi zosasangalatsa. Koma, nthawi zambiri, chifukwa chaichi, macros ndi abwino kwambiri, chilembo chazolembedwa pamanja, osati zolembedwa. Kuphatikizanso, ndondomeko yayikulu ikhoza kukonzedwa kupyolera mu mkonzi wa VBE kuti ikufulumizitse ndondomeko yoyenera ntchito.
