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

2024 Forfatter: Geoffrey Carr | [email protected]. Sist endret: 2023-12-17 10:53

En av de kraftigste, 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 å måtte skrive en enkelt linje med 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 skjule. Som en (veldig) kort liste over funksjoner du kan gjøre med en makro:

Bruk stil og formatering.
Behandle data og tekst.
Kommuniser 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 for hagen din. Ikke noe spesielt her, bare et 10 × 20 sett med tall mellom 0 og 100 med både en rad og kolonneoverskrift. Vårt mål 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 nullkoding 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 (likevel, når det ikke er 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 datarader 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:

= 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 Komma Stil under Hjemmemenyen.

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

Modig.
Sentrert.
Bakgrunnsfarge.

Og til slutt, bruk noen stil til totalene.

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. Før vi gjør det, må vi først slette 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 fjernet (men makroene som fortsatt er inkludert i Excel-filen), vil vi lagre filen som en makroaktivert mal (XLTM) -fil. 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-makro

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 å sikre deg bare Kjør makroer fra klarerte kilder.

For å sette datamaterialet makro til bruk, åpne Excel Template filen 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 makroen vår).

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 til Makro-menyen (under Vis-fanen) og velger Vis makroer.

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

Når du kjører, 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 de fleste behov, vil mer spesialtilpassede handlinger eller betingede handlinger kreve at du redigerer kildekoden.

Ta vårt eksempel ett skritt videre …

Hypotetisk, anta 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å: