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

2024 Autor: Geoffrey Carr | [email protected]. Viimati modifitseeritud: 2023-12-17 10:56

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

Kui olete huvitav, millised makrosid on või kuidas neid tegelikult luua, pole probleem - me käime kogu protsessi läbi.

Märge:sama protsess peaks 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 päris palju teha, mida teie kujutlusvõime võib tekitada. Nagu (väga) lühike funktsioonide nimekiri, mida saate teha makroga:

Rakenda stiil ja vormindamine.
Andmete ja teksti manipuleerimine.
Suhtle andmeallikatega (andmebaas, tekstifailid jne).
Loo täiesti uusi dokumente.
Mis tahes kombinatsioon mis tahes järjekorras ülalnimetatud viisil.

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 nullkodeerimisega - nagu me siin teeme.

Makro loomiseks minge jaotisse Vaade> Makrid> Salvesta makro.

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

Kui see on tehtud, kõik teie toimingutest registreeritakse - iga raku muutmine, 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, rakendame oma kokkuvõtlikke arvutusi. 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 arvutusrühmad esile ja lohistage kõigi andmeribade pikkus iga rea arvutamiseks.

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

Nüüd tahame saada kogu lehe kokkuvõtteid, nii et me kasutame veel mõned arvutused:

= SUM (L2: L21)
= Keskmine (B2: K21) *Seda tuleb arvutada kõigi andmete puhul, sest rea keskmiste keskmine ei pruugi tingimata võrdne kõikide väärtuste keskmisega.
= MIN (N2: N21)
= MAX (O2: O21)
= MEDIAAN (B2: K21) * Arvutatud kõigi andmete puhul 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.

Seejärel rakendage mõlemat rea ja veeru päist visuaalset vormingut:

Julge.
Tsentreeritud
Taustapildi värv.

Ja lõpuks, lisage kogusummadele mõned stiilid.

Kui kõik on lõpetatud, on meie andmeleht välja järgmine:

Kuna tulemused on rahul, lõpetage makro salvestamine.

Õnnitleme - äsja olete loonud Exceli makro.

Selleks, et kasutada meie äsja salvestatud makro, peame salvestama Exceli töövihiku makrofunktsiooniga 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 (ent makrodesse on veel Exceli faili kaasatud), soovime faili salvestada makro-vormingus (XLTM) failina. On oluline märkida, et kui salvestate selle kui standardmalli (XLTX) faili, siis tehakse makrosid mitte olema võimeline sellest käima. 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 suudame seda uut registreeritud makrot rakendada, on oluline ka mõned punktid umbes makrodest üldiselt katta:

Makrod võivad olla pahatahtlikud.
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, et teid kindlustaks ainult käivitage makrosid usaldusväärsetest allikatest.

Et meie andmevormingu makro kasutada, avage ülaltoodud 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 allikas 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 (vahekaardi Vaated) ja valige View macros.

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

Kui töötab, näete kursorit mõne hetke pärast hüppa, kuid nagu näete, näete manipuleeritavaid andmeid 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.

Katte all otsimine: mis teeb makro töö

Nagu me paar korda mainisime, juhindub makro rakenduse Visual Basic for Applications (VBA) koodist. Kui te "salvestate" makro, Excel tegelikult tõlkib kõik, mida teete vastavate VBA juhistega. Lihtsalt öeldes - sa ei pea koodi kirjutama, sest Excel kirjutab teile koodi.

Makro käivitamiseks käivitatava koodi vaatamiseks klõpsake dialoogiboksis Makrosid 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 uusimad andmed). Selle faili avamise ja selle importimise protsessi saab hõlpsasti ka makro teha:

Avage Exceli malli fail, mis sisaldab meie makroformat "FormatData".
Salvestage uus makro nimega LoadData.
Makro salvestusega importige andmefail nagu tavaliselt.
Kui andmed on imporditud, lõpetage makro salvestamine.
Kustuta kõik rakkude andmed (vali kõik siis kustuta).
Salvestage värskendatud 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.