Lugege, kuidas kasutada Excel makroid tüütu ülesannete automatiseerimiseks

Üks võimsamaid, kuid harva kasutatavaid Exceli funktsioone on võime luua makrode sees väga kergesti automatiseeritud ülesandeid ja kohandatud loogikat. Makrod annavad ideaalse võimaluse säästa prognoositavatel, korduvatel ülesannetel ja dokumendivormingute standardimisel aega - mitu korda, ilma et oleks vaja kirjutada ühte koodirida.

Kui te olete uudishimulik, millised makrosid on või kuidas neid tegelikult luua, siis pole mingit probleemi - me teeme teid kogu protsessi läbi.

Märge: sama protsess peaks töötama enamikus Microsoft Office'i versioonides. Ekraanipildid võivad veidi erineda.

Mis on makro?

Microsoft Office'i makro (kuna see funktsioon kehtib mitme MS Office'i rakenduse kohta) on lihtsalt Visual Basic for Applications (VBA) kood, mis on salvestatud dokumendi sees. Sarnase analoogia korral mõtle dokumendina HTML-i ja makro kui Javascript-i. Samal viisil, nagu Javascript võib HTML-i veebisaidil manipuleerida, saab makro dokumendiga manipuleerida.

Makro on uskumatult võimas ja suudab teha päris palju midagi, mida teie kujutlusvõime võib kujutada. (Väga) lühikese loendina funktsioonidest, mida saate makro abil teha:

Rakenda stiili ja vormingut.
Manipuleeri andmeid ja teksti.
Suhtle andmeallikatega (andmebaas, tekstifailid jne).
Loo täiesti uued dokumendid.
Mis tahes kombinatsioon mis tahes järjekorras mis tahes eespool.

Makro loomine: selgitus näitega

Alustame teie CSV-failist. Siin pole midagi erilist, vaid 10 × 20 numbrit vahemikus 0 kuni 100, nii rea kui veeru päises. Meie eesmärk on koostada hästi vormindatud esitlusleht, mis sisaldab iga rea kokkuvõtteid.

Nagu me ülalpool mainisime, on makro VBA-kood, kuid üks Exceli ilusaid asju on teil võimalik luua / salvestada nullkoodiga nõutav - nagu me siin teeme.

Makro loomiseks minge jaotisse Vaade> Makrod> Salvesta makro.

Määrake makro nimi (tühikuteta) ja klõpsake OK.

Kui see on tehtud, kõik salvestatakse kõik toimingud - iga lahtri muutus, kerimismeetmed, akna suuruse muutmine, nimi.

On paar kohta, mis näitavad, et Excel on salvestusrežiim. Üks on Macro menüü vaatamine ja märkides, et Stop Recording on asendanud Record Macro valiku.

Teine on alumises paremas nurgas. Ikoon „Stopp“ näitab, et see on makro-režiimis ja vajutades siin peatatakse salvestamine (samuti, kui see ei ole salvestusrežiimis, on see ikoon Record Macro, mida saate kasutada makro-menüüsse mineku asemel).

Nüüd, kui me oma makro salvestame, rakendame meie kokkuvõtteid. Esmalt lisage päised.

Seejärel kasutage sobivaid valemeid (vastavalt):

= SUM (B2: K2)
= AVERAGE (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= MEDIAN (B2: K2)

Nüüd tõstke esile kõik arvutusrakud ja lohistage kõikide meie ridade pikkus, et rakendada iga rida arvutusi.

Kui see on tehtud, peaks iga rida näitama nende vastavaid kokkuvõtteid.

Nüüd tahame saada kogu lehe kokkuvõtlikud andmed, seega rakendame veel mõningaid arvutusi:

Vastavalt:

= SUM (L2: L21)
= AVERAGE (B2: K21) *See tuleb arvutada kõigi andmete põhjal, sest rea keskmiste keskmine ei pruugi võrduda kõigi väärtuste keskmisega.
= MIN (N2: N21)
= MAX (O2: O21)
= MEDIAN (B2: K21) * Arvutatakse kõigi andmete põhjal eespool nimetatud põhjusel.

Nüüd, kui arvutused on tehtud, rakendame stiili ja vormingut. Kõigepealt rakendage kõigis lahtrites üldnumbrite vormingut, tehes Vali kõik (kas Ctrl + A või klõpsa rida rea ja veeru päiste vahel) ja vali Home menüüst „Comma Style” ikoon..

Seejärel rakendage visuaalset vormingut nii rea- kui ka veeru päistele:

Julge.
Keskendatud.
Taustavärv.

Lõpuks rakendage kogumitele teatud stiili.

Kui kõik on lõpetatud, näeb meie andmeleht välja nagu:

Kuna oleme tulemustega rahul, peatage makro salvestamine.

Palju õnne - olete just loonud Exceli makro.

Uuesti salvestatud makro kasutamiseks peame salvestama Exceli töövihiku makro-toega failivormingus. Kuid enne, kui me seda teeme, peame kõigepealt kõik olemasolevad andmed tühjendama, et see ei oleks meie malli külge kinnitatud (idee iga kord, kui kasutame seda malli, impordime kõige värskemad andmed).

Selleks valige kõik lahtrid ja kustutage need.

Nüüd, kui andmed on nüüd kustutatud (kuid makroid veel Exceli failis), tahame me faili salvestada makro-toega malli (XLTM) failina. Oluline on märkida, et kui salvestate selle tavalise malli (XLTX) failina, siis makroid mitte olla võimeline sellest välja sõitma. Teise võimalusena saate faili salvestada pärandvormingu (XLT) failina, mis võimaldab makroid käivitada.

Kui olete faili mallina salvestanud, mine edasi ja sulgege Excel.

Exceli makro kasutamine

Enne selle värskelt salvestatud makro rakendamist, on oluline makrode kohta üldiselt mõni punkt:

Makrod võivad olla pahatahtlikud.
Vt eespool toodud punkti.

VBA kood on tegelikult üsna võimas ja suudab faile manipuleerida väljaspool praeguse dokumendi ulatust. Näiteks võib makro muuta oma dokumente kausta Minu dokumendid või neid kustutada. Sellisena on oluline veenduda ainult käivitada usaldusväärsetest allikatest pärit makroid.

Andmeformaadi makro kasutamiseks kasutage ülal loodud Exceli malli faili. Kui te seda teete, eeldades, et teil on standardsed turvaseaded lubatud, näete töövihiku ülaosas hoiatust, mis ütleb, et makrod on keelatud. Kuna me usaldame enda loodud makrot, klõpsake nupul „Luba sisu”.

Järgmisena impordime CSV-st uusima andmekogumi (see on allikas, mida tööleht kasutab meie makro loomiseks).

CSV-faili impordi lõpuleviimiseks peate võib-olla seadistama mõned võimalused, et Excel saaks seda õigesti tõlgendada (nt eraldaja, kohal olevad päised jne).

Kui meie andmed on imporditud, minge lihtsalt makro menüüsse (vahekaardi Vaade alla) ja valige Vaata makroid.

Saadud dialoogiboksis näeme me ülalpool salvestatud “FormatData” makro. Valige see ja klõpsake käsku Käivita.

Kui olete käivitanud, võite näha, et kursor hüppab mõne minuti jooksul ringi, kuid nagu näete, siis manipuleeritavad andmed täpselt kui me selle salvestasime. Kui kõik on öeldud ja tehtud, peaks see nägema nagu meie originaal - välja arvatud erinevate andmetega.

Kapoti all vaadates: mis teeb makro töö

Nagu oleme paar korda maininud, juhib makro Visual Basic for Applications (VBA) koodi. Kui salvestate makro, siis tõlkib Excel tegelikult kõik, mida te teete, oma vastavatesse VBA juhistesse. Lihtsalt öeldes - sa ei pea koodi kirjutama, sest Excel kirjutab teile koodi.

Makro käivitamiseks vajaliku koodi vaatamiseks klõpsake dialoogiboksi Makroid klõpsates nuppu Redigeeri.

Avanevas aknas kuvatakse makro loomisel meie tegevustest salvestatud lähtekood. Muidugi saate seda koodi redigeerida või isegi luua uusi makroid täielikult koodiakna sees. Kuigi käesolevas artiklis kasutatud salvestusmeetmed sobivad tõenäoliselt enamiku vajadustega, nõuavad rohkem kohandatud kohandused või tingimuslikud toimingud lähtekoodi muutmist.

Üks näide ühe sammu võrra kaugemale ...

Oletame, et meie lähteandmete fail, data.csv on toodetud automatiseeritud protsessiga, mis salvestab faili alati samasse kohta (nt C: Data.csv on alati kõige värskemad andmed). Selle faili avamine ja selle importimine on samuti makromajandusse lihtne:

Avage Exceli malli fail, mis sisaldab meie “FormatData” makro.
Salvestage uus makro nimega "LoadData".
Makro salvestamise korral importige sellist andmefaili nagu tavaliselt.
Kui andmed on imporditud, lõpetage makro salvestamine.
Kustutage kõik lahtriandmed (valige kõik, seejärel kustutage).
Salvestage uuendatud mall (pidage meeles, et kasutate makro-lubatud malliformaati).

Kui see on tehtud, siis, kui mall avatakse, on kaks makrot - üks, mis laadib meie andmed ja teine, mis selle vormindab.

Kui sa tõesti soovisid oma käed määrdunud koodide redigeerimisega määrduda, siis saate need toimingud lihtsalt üheks makroksiks ühendada, kopeerides "LoadData" -st toodetud koodi ja sisestades selle koodi algusest "FormatData".

Laadige see mall alla

Teie mugavuse huvides oleme lisanud nii käesolevas artiklis toodetud Exceli malli kui ka proovi andmefaili, mida saate mängida.

Lae Exceli makro mall How-To Geekist