Õppige Exceli makrosid kasutama, et automatiseerida tüütuid ülesandeid

Excel'i üks võimsamaid, kuid harva kasutatavaid funktsioone on võime luua automaatselt ülesandeid ja kohandatud loogikat makrodes väga lihtsalt. Makrod pakuvad ideaalse võimaluse säästa aega prognoositavate ja korduvate ülesannete puhul ning standardiseerida dokumentide vorminguid - mitu korda ilma ühe koodirida kirjutamata.

Kui teil on huvi, mis makrosid on või kuidas neid tegelikult luua, pole probleem - me käime läbi kogu protsessi.

Märkus: peaks sama protsess töötama enamikes Microsoft Office'i versioonides. Ekraanipildid võivad tunduda veidi erinevad.

Mis on makro?

Microsoft Office'i makro( kuna see funktsioon kehtib paljude MS Office'i rakenduste kohta) on lihtsalt dokumendi Visual Basic for Applications( VBA) kood. Võrreldava analoogia puhul mõtle dokumendile HTML-i ja makrosidena kui Javascripti. Javascript võib HTML-iga veebisaidil enam-vähem sama manipuleerida, võib makro dokumendiga manipuleerida.

makrod on uskumatult võimsad ja suudavad teha suhteliselt palju, mida teie kujutlusvõime võib tekitada. Nagu( väga) lühike funktsioonide nimekiri, mida saate teha makroga:

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

Makro loomine: näide

selgitus Alustame teie aia-tüüpi CSV-failiga. Siin pole midagi erilist, vaid 10 x 20 numbrite kogumit vahemikus 0 kuni 100 nii rea kui ka veeru päisena. Meie eesmärgiks on koostada hästi vormindatud, esitatava andmeleht, mis sisaldab iga rea ​​kokkuvõtlikke kogusummasid.

Nagu eespool öeldud, on makro VBA-kood, kuid Exceli puhul on see üks kena asju, mida saab luua / salvestada null-kodeerimisega - nagu me siin teeme.

Makro loomiseks minge & gt;Makrosid & gt;Salvesta makro.

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

Kui see on tehtud, salvestab kõik oma toimingud - iga lahtri muutus, kerimisnupp, akna suuruse muutmine, selle nimi.

On paar kohti, mis näitavad, et Excel on salvestusrežiim.Üks neist on vaadates makromenüüd ja märkides, et Stop Recording on asendanud Makro salvestamise võimaluse.

teine ​​on paremas alanurgas. Stopp-ikoon näitab, et see on makrorežiimis ja siin vajutamine peatab salvestamise( ka siis, kui mitte salvestusrežiimis, on see ikoon Macro nupu salvestamine, mida saate kasutada Macro menüüsse asumise asemel).

Nüüd, kui me salvestame oma makro, lähme kokku oma arvutustes. Esmalt lisage päised.

Järgmiseks kasutage sobivaid valemeid( vastavalt):

• = SUM( B2: K2)
• = KESKMINE( B2: K2)
• = MIN( B2: K2)
• = MAX( B2: K2)
• = MEDIAAN( B2: K2)

Nüüd tõstke kõik arvutuslahtrid esile ja lohistage kõigi andmeribade pikkus, et arvutada iga rea ​​kohta.

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

Nüüd soovime saada kogu lehe kokkuvõtteid, nii et me kasutame veel mõnda arvutust:

Vastavalt:

• = SUM( L2: L21)
• = KESKMINE( B2: K21) * Seda tuleb arvutada kõigiandmed, sest rea keskmiste keskmine ei pruugi tingimata võrdne kõikide väärtuste keskmisega.
• = MIN( N2: N21)
• = MAX( O2: O21)
• = MEDIAN( B2: K21) * Arvutatud kõigi andmete põhjal samadel põhjustel nagu eespool.

Nüüd, kui arvutused on tehtud, rakendame stiili ja vormingut. Esmalt rakendatakse üldine numbrite vormindamine kõigis lahtrites, tehes valiku All( kas Ctrl + A või klikkige rea ja veerupäiste vahel) ja valige Kodumenüü all komaga stiil.

Järgnevalt rakendage mõlemat rea ja veeru päiste visuaalset vormingut:

• Bold.
• keskendunud.
• Taustapildi värv.

Ja lõpuks, lisage kogusummadele teatud stiil.

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

Kuna tulemused on rahul, lõpetage makro salvestamine.

Õnnitleme - äsja on loodud Exceli makro.

Meie äsja registreeritud makro kasutamiseks peame salvestama Exceli töövihiku makrofunktsionaalses failivormingus. Kuid enne seda me peame esmalt kustutama kõik olemasolevad andmed nii, et see ei ole meie malli sisse lülitatud( idee on iga kord, kui me kasutame seda malli, impordime kõige värskemaid andmeid).

Selleks valige kõik lahtrid ja kustutage need.

Kui andmed on nüüd kustutatud( kuid makrodesse on veel Exceli faili kaasatud), tahame faili salvestada makro-vormingus( XLTM) failina. Oluline on märkida, et kui salvestate selle kui standardmalli( XLTX) faili, siis saab makrodest mitte olla sellel käivitada. Alternatiivina võite faili salvestada pärandmalli( XLT) failina, mis lubab makrode käitamist.

Kui olete faili mallina salvestanud, jätkake ja sulgege Excel.

Exceli makro

kasutamine Enne seda, kui me suudame seda värskelt salvestatud makrot rakendada, on tähtis katta mõned punktid makrodest üldiselt:

• makro võib olla pahatahtlik.
• Vaadake ülalolevat punkti.

VBA kood on tegelikult üsna võimas ja võib manipuleerida failidega, mis jäävad väljapoole käesoleva dokumendi ulatust. Näiteks võib makro muuta või kustutada juhuslikke faile oma kausta Minu dokumendid. Sellisena on oluline veenduda, et kasutab ainult usaldusväärsete allikate makrosid.

Et panna meie andmevormingu makro kasutama, avage eespool loodud Exceli mallifail. Kui teete seda, eeldades, et teil on standardseid turbesätteid lubatud, näete tööriista ülaosas hoiatust, milles öeldakse, et makrosid on keelatud. Kuna usaldame ise loodud makreid, klõpsake nuppu "Luba sisu".

Järgmisena impordime CSV-i viimast andmekogumit( see on makro loomiseks kasutatav tööleht).

CSV-faili importimise lõpuleviimiseks peate võib-olla määrama mõned võimalused, et Excel saaks seda õigesti tõlgendada( nt eraldaja, olemasolevad pealkirjad jne).

Kui meie andmed on imporditud, minge lihtsalt menüüsse Macros( vahekaardil Vaade) ja valige View macros.

Tulemuseks olevas dialoogiboksis näeme ülaltoodud vormingus "FormatData" makro. Valige see ja klõpsake käsul Käivita.

Kui olete käivitanud, võite näha kursorit mõne hetke pärast hüpata, kuid nagu näete, näeme, et -ga töödeldavad andmed on täpselt , nagu me seda salvestasime. Kui kõik on öeldud ja tehtud, peaks see välja nägema nagu meie originaal - välja arvatud erinevad andmed.

Kasutades kapuutsi: mis teeb makro töö

Nagu me oleme paar korda maininud, on makro käivitatud rakenduse Visual Basic for Applications( VBA) koodiga. Kui te "salvestate" makro, Excel tegelikult tõlgib kõik, mida teete vastavate VBA juhistega. Lihtsalt öeldes - sa ei pea kirjutama ühtegi koodi, sest Excel kirjutab teile koodi.

Makroülevaadet käivitava koodi vaatamiseks klõpsake dialoogis Makrused nuppu Muuda.

Avanevas aknas kuvatakse makro loomisel meie toimingutest kirjutatud lähtekood. Loomulikult saate seda koodi muuta või isegi luua uusi makrode täielikult koodiaknas. Kuigi käesolevas artiklis kasutatud salvestusmeetmed sobivad tõenäoliselt kõige rohkem vajadusi, vajavad täpsemalt kohandatud toimingud või tingimuslikud tegevused lähtekoodi.

Meie näite võtmine üks samm edasi. ..

Hüpoteetiliselt eeldame, et meie lähteandmete faili data.csv toodetakse automatiseeritud protsessi abil, mis alati salvestab faili samas asukohas( nt C: \ Data \ data.csv on alati kõige rohkemhiljutised andmed).Faili avamise ja selle importimise protsessi saab hõlpsasti ka makro teha:

1. Avage Exceli vormingu fail, mis sisaldab meie makroformat "FormatData".
2. Kirjutage uus makro nimega LoadData.
3. Makro salvestusega importige andmefail nagu tavaliselt.
4. Kui andmed on imporditud, lõpetage makro salvestamine.
5. Kustuta kõik lahtri andmed( vali kõik siis kustuta).
6. Salvesta ajakohastatud mall( pidage meeles, et kasutada makro-vormingus mallivormingut).

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

Kui sa tõesti tahad, et oma käed oleksid mõnevõrra koodi redigeerimisega määrdunud, võite neid toiminguid lihtsalt ühendada ühe makroga, kopeerides "LoadData" loodud koodi ja sisestades selle koodi alguses "FormatData".

Laadige see mall

Teie mugavuse huvides oleme lisanud nii käesolevas artiklis toodetud Exceli malli kui ka näidisandmete faili, mille abil saate mängida.

Laadige alla Exceli makro mudeli How-To Geekist