Chii chiri Macro muExcel uye Inoshandiswa Rini?
An Excel macro inenge yakagadzirirwa mirayiridzo yakachengetwa mune izvo zvinonzi VBA code iyo inogona kushandiswa kubvisa kudiwa kwekudzokorora matanho emabasa anowanzoitwa kakawanda.
Izvi zvinogona kudzokorora zvinogona kusanganisira kuverenga kwakaoma kunoda kushandiswa kwemafomu kana kuti zvingave zviri nyore kumisa mabasa - zvakadai sekuwedzera nhamba yekufambisa kune data itsva kana kushandisa masero uye mapepa okushanda mapepa akaita semiganhu uye shading.
Zvimwe zvekudzokorora mabasa ayo macros anogona kushandiswa kuponesa anosanganisira:
- kuwedzera kana kubvisa mitsetse uye mapuranga
- kudzivirira kana kusavhara mapepa emabasa
- kusarudza mhando yemasero
- kuwedzera zuva razvino kune rimwe pepaji
Kurwisa Macro
Macros inogona kukonzerwa nekatsinhanisi yebhodi, toolbar icon kana bhatani kana icon yakawedzerwa pane pepa.
Macros vs. Templates
Paunenge uchishandisa macrosi inogona kuva nguva yakawanda yekuponesa mabasa ekudzokorora, kana iwe paunowedzera mamwe maitiro ekugadzirisa kana zvigadziro - zvakadai semisoro, kana kambani yekambani kune zvitsva zvemashizha matsva, zvingave zviri nani kugadzira nokuchengetedza template faira ine zvinhu zvose zvakadaro panzvimbo pokuvasika patsva nguva dzose paunotanga pepa rekushanda.
Macros uye VBA
Sezvakataurwa, mu Excel, macros yakanyorwa muVisual Basic for Applications (VBA). Macros kunyora uchishandisa VBA inowanikwa muVBA editor window, iyo inogona kuvhurwa nekukanya pa Visual Basic icon paVadziri veTabhoni rebhoni (ona pasi apa pane mirayiridzo paunowedzera Tate Developers kuibhoni kana zvichidiwa).
Excel & # 39; s Macro Recorder
Kune avo vasingakwanisi kunyora VBA code, ine chikwata chakakonzerwa ma macro chinokubvumira kuti ureve matanho ematanho uchishandisa keyboard uye mouse izvo Excel zvino uchinje kuVBA code iwe.
Kungofanana neVBA mhariri ambotaurwa pamusoro, Macro Recorder iri pavanokurudzira tab yeRibbon.
Kuwedzera Mubhiririri Tab
Nokusara muExcel, Tandemuteri tab haisipo paRibbon. Kuti uwedzere iyo:
- Dzvanya paFichi yebhu kuti uzarure doro rezasi yezvasarudzo
- Pamusoro pezvinyorwa pasi, sarudza Options kuti uzarure Excel Options dialog box
- Muchikamu chekuruboshwe-bhokisi rebhokisi rebhokisi, tora paGadzira Ribhoni kuti uzarure firati yeRibbon yeChirungu
- Pasi pechikamu chikuru cheTabs muwindo rekurudyi, chengeta pane bhokisi riri pedo neMusununguri kuti uwedzere iyi tab kuRibbon
- Dzvanya OK kuti uvhare bhokisi rebhokisi uye dzokera kubheji rekushanda.
Mukugadziri anofanira kunge aripo - kazhinji kuruoko rworudyi rweRibbon
Achishandisa Macro Recorder
Sezvakataurwa, Macro Recorder inogadzirisa basa rekusika macros - kunyange, pane dzimwe nguva, kune avo vanogona kunyorera VBA code, asi pane zvishoma zvingazivikanwa musati matanga kushandisa shanduri iyi.
1. Rongedza Macro
Kunyora Macros neChirungu Macro kunosanganisira chiduku chekudzidza. Kuti uite nyore kuita, ronga mberi kwenguva - kunyange kusvika pakunyorera izvo izvo macro zvakarongwa kuita uye matanho achazodiwa kuti azadzise basa racho.
2. Chengeta Macros Small uye Specific
Iyo yakakura macro iri maererano nehuwandu hwemabasa iyo inoita zvakanyanya kuoma zvingangodaro kuva kuronga nekuinyora iyo kubudirira.
Macros macros inomhanyawo zvishoma-kunyanya iyo inosanganisira yakawanda yekuverenga mumashizha makuru - uye zvakaoma kugadzirisa nekugadzirisa kana kusashanda zvakanaka kwekutanga.
Nokuchengeta macros maduku uye zvakananga mune chinangwa zviri nyore kuratidza kururama kwezvigumisiro uye kuona kuti zvakakanganisika kana zvinhu zvisingaendi sezvazvakarongwa.
3. Zita Macros Zvakakodzera
Mazita macro muExcel ane zvikwangwani zvakawanda zvezita zita rinofanira kuonekwa. Chokutanga uye chinonyanya kukosha ndechokuti zita guru rinofanira kutanga netsamba yerabhaibheri. Zvinyorwa zvinotevera zvinogona kuva nhamba asi mazita macro haagoni kuisa nzvimbo, zviratidzo, kana kuti zviratidzo zvepumbedzi.
Uyewo hapana zita guru rine mamwe emazwi akachengeterwa ayo ari chikamu cheVBA chinoshandisa sechikamu chemuitiro wayo wekugadzira shanduro dzakadai seIyo , GoTo , New , kana Sarudza .
Kunyange zvazvo mazita macro angave anokwana mairi 255 murefu iyo inowanzodikanwa kana inobatsira kushandisa vakawanda ivavo mune zita.
Kune imwechete, kana iwe une macros akawanda uye iwe unoronga kuti uvashandise kubva ku macro dialog box, nguva refu inoshandisa mhaka yekukonzera kuita kuti zvive zvakaoma kutora macro iwe uri shure.
Nzira iri nani ndiyo yekuchengeta mazita mafupi uye kushandisa sarudzo nzvimbo kuti ipe tsanangudzo pamusoro peiyo macro imwe neimwe inoita.
IUnderscore uye Internal Capitalization mumazita
Sezvo mazita macro asingakwanisi kusanganisira nzvimbo, imwe mhando inobvumirwa, uye iyo inoita kuti maverengi macro ekuverenga ave nyore ndeye unhu hunoshandiswa hunogona kushandiswa pakati pemashoko panzvimbo yenzvimbo - shanduko Change_cell_color kana Addition_formula.
Imwe nzira ndeyokushandisa mukati memari (dzimwe nguva inonzi Camel Case ) iyo inotanga shoko rimwe nerimwe idzva muzita rine tsamba huru-yakadai seChangeCellColor uye AdditionFormula.
Mamwe mazita mazita machena ari nyore kutsvaga mu macro dialog box, kunyanya kana pepa rekushandisa rine mazita macros uye iwe unonyora akawanda macros, kuitira kuti iwe unogona kuzviona nyore nyore. Nzira yacho inopawo munda kune Tsanangudzo, kunyange kwete munhu wose anoshandisa iyo.
4. Shandisa Relative vs. Absolute Cell References
Nhamba dzemaferensi , dzakadai seB17 kana AA345, zivisa nzvimbo yesero imwe neimwe mune pepa rekushanda.
Nekusavapo, muMacro Recorder yese yese magwaro zvinoreva zvachose izvo zvinoreva kuti chaiwo nzvimbo dzese dzinonyorwa mu macro. Zvimwe, macros inogona kushandiswa kushandisa sero seferensi iyo inoreva kuti kufamba (mangani emirasi yakasara kana kurudyi iwe unotamisa cell cursor) yakanyorwa kune nzvimbo chaiyo.
Iyo yaunoshandisa inobva pane izvo zvakanyanya kuiswa kuti zviite. Kana iwe uchida kudzokorora matanho akafanana - akafanana nekugadzirisa mapuranga e data-nekupfuura, asi nguva imwe neimwe paunenge uchigadzira mapuranga akasiyana mune pepaji, ipapo kushandisa zvinyorwa zvinyorwa zvingave zvakakodzera.
Kana, kune rumwe rutivi, iwe unoda kuenzanisa mararamiro akafanana emasero-akadai seA1 kuenda kuGumus - asi pamashandisi akasiyana-siyana, ipapo zvinyorwa zvese zvisingashandisi zvingashandiswa kuitira kuti nguva imwe neimwe inenge ichitanga, danho rayo rokutanga nderokuendesa cell cell cursor kuseri A1.
Kushandura masero kubva kune zvakakwana kune zvakajeka zvinoshandiswa kuburikidza nekushanda paZvirevo Relative References icon pane Vatadziri tabu rebhoni.
5. Kushandisa Keyboard Keys vs. Mouse
Kuva ne macro rekodhi keyboard keystrokes paunenge uchifambisa se-cell cursor kana kusarudza mhando yemasero inowanzove yakanakisisa kuva nemafungiro emakumbo akanyorwa sechikamu che macro.
Kushandisa michina yekwiyamu yekakani - yakadai seCtrl + End kana Ctrl + Shift + yeKoona Arrow key - kuti ishandise se-cell cursor kumativi edunhu renharaunda (iyo masero ane data pane pepa rekushandura iripo) panzvimbo pokuramba uchinyanya kuve museve kana tab kiyi yekuendesa mutsara yakawanda kana mitsetse inotsanangurira nzira yekushandisa keyboard.
Kunyangwe pazvinouya pakushandisa mirairo kana kusarudza mabhandi ekushandiswa uchishandisa keyboard zvidhinha zvitsva zvinoshandiswa kushandisa mouse.