Sužinokite, kaip naudoti "Excel" makrokomandas, siekiant automatizuoti nuobodų užduotį

2024 Autorius: Geoffrey Carr | [email protected]. Paskutinį kartą keistas: 2023-12-17 10:53

Viena iš galingiausių, bet retai naudojamų "Excel" funkcijų - tai galimybė labai lengvai sukurti automatines užduotis ir pritaikytą logiką makrokomanduose. Makrokomandos yra ideali priemonė taupyti laiką numatomoms, pasikartojančioms užduotims, taip pat standartizuoti dokumentų formatus - daug kartų nereikia rašyti vienos eilutės kodo.

Jei jums įdomu, kas yra makrokomandas ar kaip juos iš tikrųjų kurti, nėra problemų - mes pasieksime visą procesą.

Pastaba:tas pats procesas turėtų veikti daugelyje "Microsoft Office" versijų. Ekrano nuotraukos gali atrodyti šiek tiek kitaip.

Kas yra makrokomandas?

"Microsoft Office" makrokomandas (nes ši funkcija taikoma kelioms MS Office programoms) yra tik dokumento Visual Basic for Applications (VBA) kodas. Panašią analogiją galvoja apie dokumentą kaip HTML ir makrokomandą kaip Javascript. Daugeliu atvejų, kai Javascript gali manipuliuoti HTML tinklalapyje, makro gali manipuliuoti dokumentu.

Makrokomandai yra nepaprastai galingi ir gali padaryti beveik viską, ką gali išprovokuoti jūsų vaizduotė. Kaip (labai) trumpą funkcijų sąrašą galite padaryti makrokomandoje:

Taikyti stilių ir formatavimą.
Manipuliuoti duomenis ir tekstą.
Bendraukite su duomenų šaltiniais (duomenų baze, tekstiniais failais ir pan.).
Kurti visiškai naujus dokumentus.
Bet koks derinys bet kokia tvarka iš bet kurio iš aukščiau.

Makro sukūrimas: paaiškinimas pagal pavyzdį

Pradėkime nuo jūsų sodo CSV failo. Čia nieko ypatingo nėra, tik 10 × 20 skaičių rinkinys nuo 0 iki 100, tiek su eilu, tiek su stulpelio antrašte. Mūsų tikslas yra parengti tinkamai suformatuotą, reprezentatyvų duomenų lapą, kuriame pateikiami kiekvienos eilutės santraukos.

Kaip minėjome aukščiau, makrokomandas yra VBA kodas, bet vienas iš gražių dalykų, susijusių su "Excel", galite sukurti / įrašyti juos nuliuoju kodavimu, kurio reikia - kaip mes tai padarysime.

Norėdami sukurti makrokomandą, eikite į View> Macros> Record Macro.

Priskirkite makrokomandą pavadinimą (be tarpų) ir spustelėkite Gerai.

Kai tai bus padaryta, visi jūsų veiksmų įrašoma - kiekvienas ląstelių keitimas, slinkimo veiksmas, dydžio lange, jūs jį pavadinate.

Yra keletas vietų, kuriose nurodoma, kad "Excel" yra įrašymo režimas. Vienas iš jų - peržiūrint makrokomandos meniu ir pažymėdamas, kad "Stop Recording" pakeitė makrokomandos įrašymo funkciją.

Kitas yra apatiniame dešiniajame kampe. "Stop" piktograma rodo, kad tai yra makro režimu, ir paspaudus čia bus sustabdytas įrašymas (taip pat, kai ne įrašymo režimu, ši piktograma bus mygtukas "Įrašyti makro", kurį galite naudoti vietoj eiti į makrokomandos meniu).

Dabar, kai mes įrašome mūsų makrokomandą, galime taikyti skaičiavimus. Pirmiausia pridėkite antraštes.

Toliau pritaikykite atitinkamas formules (atitinkamai):

= SUM (B2: K2)
= VIDUTINIS (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= Mediana (B2: K2)

Dabar paryškinkite visas skaičiavimo ląsteles ir vilkdami visų duomenų eilučių ilgį, kad kiekvienai eilutei būtų taikomi skaičiavimai.

Kai tai bus padaryta, kiekvienoje eilutėje turi būti pateikiamos atitinkamos jų santraukos.

Dabar mes norime gauti viso lapo suvestinius duomenis, todėl taikome kelis skaičiavimus:

= SUM (L2: L21)
= VIDUTINIS (B2: K21) *Tai turi būti apskaičiuota visiems duomenims, nes eiliškumo vidurkis nebūtinai lygus visų reikšmių vidurkiui.
= MIN (N2: N21)
= MAX (O2: O21)
= Mediana (B2: K21) * Apskaičiuota visiems duomenims dėl tos pačios priežasties kaip nurodyta aukščiau.

Dabar, kai atliekami skaičiavimai, mes pritaikysime stilių ir formatavimą. Pirmiausia bendrų numerių formatavimas atliekamas visose ląstelėse, atlikus "Select All" (Ctrl + A arba spustelėkite langelį tarp eilučių ir stulpelių antraščių) ir "Home" meniu pasirinkite "Comma Style" piktogramą.

Tada pridėkite tam tikrą vaizdo formatavimą į eilutę ir stulpelių antraštes:

Bold.
Nukreiptas
Fono užpildo spalva.

Ir galiausiai, taikykite tam tikrą stilių, kad gautumėte bendrą sumą.

Kai visa tai baigta, mūsų duomenų lapas atrodo taip:

Kadangi mes patenkinti rezultatais, sustabdykite makrokomandos įrašymą.

Sveikiname - ką tik sukūrėte "Excel" makrokomandą.

Kad galėtume naudoti mūsų naujai užregistruotą makrokomandą, mes turime išsaugoti "Excel" darbo knygą makroaktyviuoju failo formatu. Tačiau prieš tai mes turime išvalyti visus esamus duomenis, kad jie nebūtų įtraukti į mūsų šabloną (idėja yra kiekvieną kartą, kai mes naudojame šį šabloną, importuosime naujausius duomenis).

Norėdami tai padaryti, pasirinkite visas langelius ir ištrinkite juos.

Kai duomenys yra išvalyti (tačiau makrokomandos vis dar įtrauktos į "Excel" failą), mes norime įrašyti failą kaip makrokomandos šablono (XLTM) failą. Svarbu pažymėti, kad jei išsaugosite šį failą kaip standartinį šabloną (XLTX), tada makrokomandas ne galėti būti paleisti iš jo. Be to, galite išsaugoti failą kaip senojo šablono (XLT) failą, kuris leis paleisti makrokomandas.

Kai išsaugojote failą kaip šabloną, eikite į priekį ir uždarykite "Excel".

"Excel" makro naudojimas

Prieš aptardami, kaip galėtume taikyti šią naujai užregistruotą makrokomandą, svarbu apskritai padengti keletą taškų apie makrokomandas:

Makrokomandai gali būti kenksmingi.
Žr. Aukščiau esantį punktą.

VBA kodas iš tiesų yra gana galingas ir gali manipuliuoti failais, esančiais už dabartinio dokumento taikymo srities ribų. Pavyzdžiui, makrokomandas gali pakeisti ar ištrinti atsitiktinius failus aplanke Mano dokumentai.Todėl svarbu įsitikinti, kad jūs tik paleisti makrokomandas iš patikimų šaltinių.

Norėdami įdėti mūsų duomenų formato makrokomandą, atidarykite anksčiau sukurtą "Excel" šablono failą. Kai tai padarysite, darant prielaidą, kad įjungėte įprastus saugos nustatymus, darbo knygos viršuje pamatysite įspėjimą, kuriame sakoma, kad makrokomandos yra išjungtos. Kadangi mes pasitikime makiažu, sukurtu patys, spustelėkite mygtuką "Įgalinti turinį".

Toliau mes ketiname importuoti naujausią duomenų rinkinį iš CSV (tai šaltinis - darbaknelis, naudojamas mūsų makrokomandai kurti).

Norėdami užbaigti CSV failo importavimą, gali tekti nustatyti keletą parinkčių, kad "Excel" galėtų ją teisingai interpretuoti (pvz., Delimiteris, antraštės ir pan.).

Kai mūsų duomenys bus importuoti, tiesiog eikite į makrokomandos meniu (po skirtuku Rodymas) ir pasirinkite Peržiūrėti makrokomandas.

Gautame dialogo lange mes matome "FormatData" makrokomandą, kurią užfiksavome aukščiau. Pasirinkite jį ir spustelėkite Vykdyti.

Kai bėga, galite pastebėti, kad žymeklis peršokti kelias minutes, bet kaip matysite manipuliuojamus duomenis tiksliai kaip tai užfiksavome. Kai visa tai sakoma ir padaryta, ji turėtų atrodyti lygiai taip pat, kaip ir mūsų originalas, išskyrus su skirtingais duomenimis.

Ieškote po gaubtu: tai, kas daro makro darbą

Kaip jau minėjome keletą kartų, makrokomandą valdo "Visual Basic for Applications" (VBA) kodas. Kai "įrašote" makrokomandą, "Excel" iš tikrųjų verčia viską, ką jūs darote, į atitinkamas VBA instrukcijas. Paprasčiau tariant - nereikia rašyti jokio kodo, nes "Excel" jums rašo kodą.

Jei norite peržiūrėti kodą, kuriame makrokomandas paleidžiamas, dialogo lange Makros spustelėkite mygtuką Redaguoti.

Atsidariusiame lange rodomas šaltinio kodas, kuris buvo užregistruotas mūsų veiksmuose kuriant makrokomandą. Žinoma, galite redaguoti šį kodą arba net kurti naujas makrokomandas visiškai kodo lango viduje. Nors šiame straipsnyje naudojami įrašymo veiksmai greičiausiai atitiks daugumą poreikių, labiau pritaikytuose veiksmuose ar sąlyginiuose veiksmuose reikės redaguoti šaltinio kodą.

Mūsų pavyzdžio priėmimas vienu žingsniu toliau …

Apytikriai teigiame, kad mūsų šaltinio duomenų failas data.csv gaminamas automatiniu būdu, kuris visada išsaugo failą toje pačioje vietoje (pvz., C: Data data.csv visada yra naujausi duomenys). Failo atidarymo ir importavimo procesą galima lengvai padaryti ir makrokomandoje:

Atidarykite "Excel" šablono failą, kuriame yra mūsų "FormatData" makrokomandas.
Įrašykite naują makrokomandą "LoadData".
Įrašę makrokomandą, importuokite duomenų failą, kaip įprastai.
Kai duomenys bus importuoti, sustabdykite makrokomandos įrašymą.
Ištrinti visus ląstelių duomenis (pažymėkite visus tada ištrinkite).
Išsaugokite atnaujintą šabloną (nepamirškite naudoti makroformuoto formato).

Kai tai bus padaryta, kiekvieną kartą, kai bus atidarytas šablonas, bus du makrokomandai - vienas, kuris įkelia mūsų duomenis ir kitas, kuris jį formuoja.