9Sep

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 å 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 ganske enkelt 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.
  • Manipuler 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 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. 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 nullkodning som kreves - som vi vil gjøre her.

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

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

Når dette er gjort, , registreres alle av dine handlinger - hver celleendring, rullehandling, vinduesformat, navnet ditt.

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)
  • = AVERAGE( 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å vil vi få oppsummeringsdataene for hele arket, så vi bruker noen få flere beregninger:

Respektivt:

  • = SUM( L2: L21)
  • = AVERAGE( B2: K21) * Dette må beregnes på tvers av alledata 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:

  • Fet.
  • Sentrert.
  • Bakgrunnsfargefarge.

Og til slutt, bruk noen stil til totalene.

Når alt er ferdig, ser dette vårt datablad 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 fremdeles 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 kjøres fra den. 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 du 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 at bare driver makroer fra klarerte kilder.

Hvis du vil sette dataformatet makro til bruk, åpner du Excel-malfilen som ble opprettet ovenfor. 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).

Hvis du vil 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 har kjørt, kan du se markøren hoppe rundt i noen få øyeblikk, men som det vil du se at dataene blir manipulert akkurat 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 nevnte 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.

Hvis du vil vise koden som lager makroen vår, klikker du på Rediger-knappen i makro-dialogboksen.

Vinduet som åpnes 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 redigerer kildekoden.

Ta 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 den mestsiste data).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 kalt "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 inkludert både Excel-malen som ble produsert i denne artikkelen, samt en prøvedatafil som du kan leke med.

Last ned Excel Macro Template fra How-To Geek