Lær hvordan du bruker Excel-makroer til å automatisere kjedelige oppgaver

En av de kraftigere, men sjelden brukte funksjonene i Excel, er evnen til å enkelt lage automatiske oppgaver og tilpasset logikk i makroer. Makroer gir en ideell måte å spare tid på forutsigbare, repeterende oppgaver, samt standardisere dokumentformater - mange ganger uten å skrive en enkelt kode.

Hvis du er nysgjerrig på hva makroer er eller hvordan du faktisk lager dem, ikke noe problem - vi vil gå deg gjennom hele prosessen.

Merk: samme prosess burde fungere i de fleste versjoner av Microsoft Office. Skjermbildene kan se litt annerledes ut.

Hva er en makro?

En Microsoft Office Macro (som denne funksjonaliteten gjelder for flere av MS Office-applikasjonene) er bare Visual Basic for Applications (VBA) -kode lagret i et dokument. For en sammenlignbar analogi, tenk på et dokument som HTML og en makro som Javascript. På samme måte som Javascript kan manipulere HTML på en nettside, kan en makro manipulere et dokument.

Makroer er utrolig kraftige og kan gjøre ganske mye hva fantasien din kan tåle. Som en (veldig) kort liste over funksjoner du kan gjøre med en makro:

• Bruk stil og formatering.
• Behandle data og tekst.
• Kommunisere med datakilder (database, tekstfiler, etc.).
• Lag helt nye dokumenter.
• Enhver kombinasjon, i hvilken som helst rekkefølge, av noen av de ovennevnte.

Opprette en makro: En forklaring ved eksempel

Vi starter med CSV-filen av hagen din. Ikke noe spesielt her, bare et 10 × 20 sett med tall mellom 0 og 100 med både en rad og kolonneoverskrift. Målet vårt er å produsere et godt formatert, presentabelt dataark som inneholder sammendragsopplysninger for hver rad.

Som vi nevnte ovenfor, er en makro VBA-kode, men en av de fine tingene om Excel er at du kan lage / registrere dem med null koding som kreves - som vi vil gjøre her.

Hvis du vil opprette en makro, går du til Vis> Makroer> Ta opp makro.

Tilordne makroen et navn (ingen mellomrom) og klikk OK.

Når dette er gjort, alle av dine handlinger er registrert - hver celleendring, rullehandling, vindu resize, du nevner den.

Det er et par steder som indikerer at Excel er rekordmodus. En er ved å se Makro-menyen og notere at Stop Recording har erstattet alternativet for Record Macro.

Den andre er i nederste høyre hjørne. Ikonet "Stopp" angir at det er i makromodus, og hvis du trykker her, stoppes opptaket (på samme måte når ikke i opptaksmodus, vil dette ikonet være Record Macro-knappen, som du kan bruke i stedet for å gå til makro-menyen).

Nå som vi registrerer makroen vår, la oss bruke våre sammendragsberegninger. Først legg til topptekstene.

Bruk deretter de riktige formlene (henholdsvis):

• = SUM (B2: K2)
• = Gjennomsnitt (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = Median (B2: K2)

Fremhev nå alle beregningscellene og dra lengden på alle våre dataarker for å bruke beregningene til hver rad.

Når dette er gjort, skal hver rad vise sine respektive oppsummeringer.

Nå ønsker vi å få oppsummeringsdataene for hele arket, så vi bruker noen få flere beregninger:

henholdsvis:

• = SUM (L2: L21)
• = AVERAGE (B2: K21) *Dette må beregnes på tvers av alle data fordi gjennomsnittet av rad-gjennomsnittene ikke nødvendigvis er lik gjennomsnittet av alle verdiene.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = Median (B2: K21) * Beregnet over alle data av samme grunn som ovenfor.

Nå som beregningene er ferdige, bruker vi stilen og formateringen. Først må du bruke generell nummerformatering på tvers av alle cellene ved å velge Select All (enten Ctrl + A eller klikk på cellen mellom radene og kolonneoverskriftene) og velg ikonet "Kommastil" under Hjemmenyen.

Deretter bruker du noen visuell formatering til både rad- og kolonneoverskriftene:

• Modig.
• sentrert.
• Bakgrunnsfargefarge.

Og til slutt, bruk noen stil til totals.

Når alt er ferdig, ser dette ut som vårt datablad ser ut:

Siden vi er fornøyd med resultatene, må du stoppe opptaket av makroen.

Gratulerer - du har nettopp opprettet en Excel-makro.

For å kunne bruke vår nyregistrerte makro, må vi lagre Excel-arbeidsboken i et makroaktivert filformat. Men før vi gjør det, må vi først fjerne alle eksisterende data slik at den ikke er innebygd i vår mal (ideen er hver gang vi bruker denne malen, vil vi importere de mest oppdaterte dataene).

For å gjøre dette, velg alle celler og slett dem.

Med dataene som nå er slettet (men makroene som fortsatt er inkludert i Excel-filen), vil vi lagre filen som en XLTM-fil (macro enabled template). Det er viktig å merke seg at hvis du lagrer dette som en standardmal (XLTX) -fil, vil makroer ikke kunne løpe fra det. Alternativt kan du lagre filen som en eldre mal (XLT) -fil, som gjør at makroer kan kjøres.

Når du har lagret filen som en mal, fortsett og lukk Excel.

Bruke en Excel Macro

Før dekker hvordan vi kan bruke denne nyregistrerte makroen, er det viktig å dekke noen få punkter om makroer generelt:

• Makroer kan være skadelig.
• Se punktet ovenfor.

VBA-koden er faktisk ganske kraftig og kan manipulere filer utenfor omfanget av gjeldende dokument. For eksempel kan en makro endre eller slette tilfeldige filer i mappen Mine dokumenter. Som sådan er det viktig å sørge for deg bare Kjør makroer fra klarerte kilder.

For å sette datamaterialet makro til bruk, åpne Excel-malfilen som ble opprettet over. Når du gjør dette, forutsatt at du har standard sikkerhetsinnstillinger aktivert, vil du se en advarsel over toppen av arbeidsboken som sier at makroer er deaktivert. Fordi vi stoler på en makro som er opprettet av oss selv, klikker du på «Aktiver innhold» -knappen.

Opp neste, skal vi importere det nyeste datasettet fra en CSV (dette er kilden som regnearket brukes til å lage vår makro).

For å fullføre importen av CSV-filen, må du kanskje angi noen alternativer for at Excel skal tolke det riktig (for eksempel avgrensning, nåværende nåværende osv.).

Når dataene våre er importert, går du ganske enkelt til makro-menyen (under fanen Vis) og velger Vis makroer.

I den resulterende dialogboksen ser vi makroen "FormatData" vi registrerte ovenfor. Velg den og klikk Kjør.

Når du har kjørt, kan du se markøren hoppe rundt i noen få øyeblikk, men som det vil du se at dataene blir manipulert nøyaktig som vi registrerte det. Når alt er sagt og gjort, bør det se ut som vår opprinnelige - unntatt med forskjellige data.

Ser under hetten: Hva gjør et makroarbeid

Som vi har nevnt et par ganger, er en makro drevet av Visual Basic for Applications (VBA) -koden. Når du "registrerer" en makro, oversetter Excel faktisk alt du gjør i sine respektive VBA-instruksjoner. For å si det enkelt - du trenger ikke skrive noen kode fordi Excel skriver koden for deg.

For å se koden som gjør makroen vår, klikker du på Rediger-knappen i makro-dialogboksen.

Vinduet som åpner viser kildekoden som ble registrert fra våre handlinger når du oppretter makroen. Selvfølgelig kan du redigere denne koden eller til og med lage nye makroer helt inne i kodevinduet. Selv om opptaksaksjonen som brukes i denne artikkelen, sannsynligvis passer til de fleste behov, vil mer spesialtilpassede handlinger eller betingede handlinger kreve at du rediger kildekoden.

Tar vårt eksempel ett skritt videre ...

Hypotetisk antar at kildedatafilen, data.csv, er produsert av en automatisert prosess som alltid lagrer filen til samme sted (f.eks. C: \ Data \ data.csv er alltid de nyeste dataene). Prosessen med å åpne denne filen og importere den kan enkelt gjøres til en makro også:

1. Åpne Excel Template filen som inneholder vår "FormatData" makro.
2. Ta opp en ny makro med navnet "LoadData".
3. Med makroopptaket, importer datafilen som du normalt ville.
4. Når dataene er importert, må du slutte å registrere makroen.
5. Slett alle celledataene (velg alle deretter slett).
6. Lagre oppdatert mal (husk å bruke et makroaktivert malformat).

Når dette er gjort, når malen åpnes, vil det være to makroer - en som laster våre data og den andre som formaterer den.

Hvis du virkelig ønsker å få hendene skitne med litt koderedigering, kan du enkelt kombinere disse handlingene i en enkelt makro ved å kopiere koden som er produsert fra "LoadData" og sette den inn i begynnelsen av koden fra "FormatData".

Last ned denne malen

For enkelhets skyld har vi tatt med både Excel-malen som ble produsert i denne artikkelen, samt en prøvedatafil for deg å leke med.

Last ned Excel Macro Template fra How-To Geek