Lær hvordan du bruger Excel-makroer til at automatisere kedelige opgaver

En af de mere kraftfulde, men sjældent anvendte funktioner i Excel, er evnen til meget let at oprette automatiserede opgaver og brugerdefineret logik inden for makroer. Makroer er en ideel måde at spare tid på forudsigelige, gentagne opgaver samt standardisere dokumentformater - mange gange uden at skulle skrive en eneste linje kode.

Hvis du er nysgerrig efter, hvad makroer er, eller hvordan du rent faktisk opretter dem, er det ikke noget problem - vi leder dig gennem hele processen.

Bemærk:  den samme proces skal fungere i de fleste versioner af Microsoft Office. Skærmbillederne kan se lidt anderledes ud.

Hvad er en makro?

En Microsoft Office-makro (da denne funktion gælder for flere af MS Office-applikationer) er simpelthen Visual Basic for Applications (VBA) -kode gemt inde i et dokument. For en sammenlignelig analogi, tænk på et dokument som HTML og en makro som Javascript. På meget samme måde som Javascript kan manipulere HTML på en webside, kan en makro manipulere et dokument.

Makroer er utroligt kraftfulde og kan gøre stort set alt, hvad din fantasi kan fremmane. Som en (meget) kort liste over funktioner kan du gøre med en makro:

  • Anvend stil og formatering.
  • Manipulere data og tekst.
  • Kommuniker med datakilder (database, tekstfiler osv.).
  • Opret helt nye dokumenter.
  • Enhver kombination, i en hvilken som helst rækkefølge, af et af ovenstående.

Oprettelse af en makro: en forklaring ved eksempel

Vi starter med din CSV-fil med havevarianter. Intet specielt her, bare et sæt 10 × 20 med tal mellem 0 og 100 med både en række- og kolonneoverskrift. Vores mål er at producere et velformateret, præsentabelt datablad, der inkluderer opsummerede totaler for hver række.

Som vi nævnte ovenfor, er en makro VBA-kode, men en af ​​de gode ting ved Excel er, at du kan oprette / optage dem med nul kodning krævet - som vi vil gøre her.

For at oprette en makro skal du gå til Vis> Makroer> Optag makro.

Tildel makroen et navn (ingen mellemrum), og klik på OK.

Når dette er gjort, registreres alle dine handlinger - hver celleændring, rullehandling, vinduestørrelse, du hedder det.

Der er et par steder, der angiver, at Excel er optagetilstand. Den ene er ved at se Macro-menuen og bemærke, at Stop Recording har erstattet muligheden for Record Macro.

Den anden er i nederste højre hjørne. 'Stop'-ikonet angiver, at det er i makrotilstand, og ved at trykke her stoppes optagelsen (ligesom når dette ikke er i optagetilstand, vil dette ikon være knappen Optag makro, som du kan bruge i stedet for at gå til menuen Makroer).

Nu hvor vi optager vores makro, lad os anvende vores sammenfattende beregninger. Tilføj først overskrifterne.

Anvend derefter de relevante formler (henholdsvis):

  • = SUM (B2: K2)
  • = GENNEMSNIT (B2: K2)
  • = MIN (B2: K2)
  • = MAX (B2: K2)
  • = MEDIAN (B2: K2)

Fremhæv nu alle beregningscellerne, og træk længden på alle vores datarækker for at anvende beregningerne på hver række.

Når dette er gjort, skal hver række vise deres respektive oversigter.

Nu ønsker vi at få de sammenfattende data for hele arket, så vi anvender et par flere beregninger:

Henholdsvis:

  • = SUM (L2: L21)
  • = GENNEMSNIT (B2: K21) * Dette skal beregnes på tværs af alle data, fordi gennemsnittet af rækkenes gennemsnit ikke nødvendigvis svarer til gennemsnittet af alle værdier.
  • = MIN (N2: N21)
  • = MAX (O2: O21)
  • = MEDIAN (B2: K21) * Beregnet på tværs af alle data af samme grund som ovenfor.

Nu hvor beregningerne er udført, anvender vi stilen og formateringen. Anvend først generel nummerformatering på tværs af alle cellerne ved at udføre et Vælg alle (enten Ctrl + A eller klik på cellen mellem række- og kolonneoverskrifterne) og vælg ikonet "Komma-stil" under menuen Hjem.

Anvend derefter visuel formatering til både række- og kolonneoverskrifterne:

  • Fremhævet.
  • Centreret.
  • Baggrundsfyldfarve.

Og til sidst skal du anvende en stil til totalerne.

Når alt er færdigt, så ser dette datablad ud:

Da vi er tilfredse med resultaterne, skal du stoppe optagelsen af ​​makroen.

Tillykke - du har lige oprettet en Excel-makro.

For at kunne bruge vores nyoptagede makro skal vi gemme vores Excel-projektmappe i et makroaktiveret filformat. Før vi gør det, skal vi dog først rydde alle eksisterende data, så de ikke er indlejret i vores skabelon (ideen er, hver gang vi bruger denne skabelon, importerer vi de mest opdaterede data).

For at gøre dette skal du vælge alle celler og slette dem.

Med dataene nu ryddet (men makroerne er stadig inkluderet i Excel-filen), vil vi gemme filen som en makroaktiveret skabelonfil (XLTM). Det er vigtigt at bemærke, at hvis du gemmer dette som en standardskabelonfil (XLTX), kan makroer ikke køres fra den. Alternativt kan du gemme filen som en ældre skabelonfil (XLT), som gør det muligt at køre makroer.

Når du har gemt filen som en skabelon, skal du fortsætte og lukke Excel.

Brug af en Excel-makro

Før vi dækker, hvordan vi kan anvende denne nyoptagede makro, er det vigtigt at dække et par punkter om makroer generelt:

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

VBA-kode er faktisk ret kraftig og kan manipulere filer uden for anvendelsesområdet for det aktuelle dokument. For eksempel kan en makro ændre eller slette tilfældige filer i mappen Mine dokumenter. Som sådan er det vigtigt at sikre, at du kun kører makroer fra pålidelige kilder.

For at anvende vores dataformatmakro skal du åbne Excel-skabelonfilen, der blev oprettet ovenfor. Når du gør dette, forudsat at du har aktiveret standard sikkerhedsindstillinger, vil du se en advarsel øverst i projektmappen, der siger, at makroer er deaktiveret. Da vi stoler på en makro, der er oprettet af os selv, skal du klikke på knappen 'Aktivér indhold'.

Derefter importerer vi det nyeste datasæt fra en CSV (dette er kilden regnearket bruges til at oprette vores makro).

For at fuldføre importen af ​​CSV-filen skal du muligvis indstille et par muligheder for at Excel kan fortolke den korrekt (f.eks. Afgrænser, overskrifter til stede osv.).

Når vores data er importeret, skal du blot gå til menuen Makroer (under fanen Vis) og vælge Vis makroer.

I den resulterende dialogboks ser vi makroen "FormatData", som vi har optaget ovenfor. Vælg det, og klik på Kør.

Når du har kørt, kan du muligvis se markøren hoppe rundt et øjeblik, men som den gør, vil du se dataene manipuleres nøjagtigt, som vi registrerede dem. Når alt er sagt og gjort, skal det se ud som vores originale - undtagen med forskellige data.

Ser under hætte: Hvad får en makro til at fungere

Som vi har nævnt et par gange, drives en makro af Visual Basic for Applications (VBA) kode. Når du “optager” en makro, oversætter Excel faktisk alt, hvad du laver, i dens respektive VBA-instruktioner. For at sige det enkelt - du behøver ikke at skrive nogen kode, fordi Excel skriver koden til dig.

For at se koden, der får vores makro til at køre, skal du klikke på knappen Rediger i dialogboksen Makroer.

Det vindue, der åbnes, viser den kildekode, der blev optaget fra vores handlinger, da vi oprettede makroen. Selvfølgelig kan du redigere denne kode eller endda oprette nye makroer helt inde i kodevinduet. Mens den optagefunktion, der anvendes i denne artikel, sandsynligvis passer til de fleste behov, vil mere tilpassede handlinger eller betingede handlinger kræve, at du redigerer kildekoden.

Tager vores eksempel et skridt videre ...

Antag hypotetisk, at vores kildedatafil, data.csv, er produceret af en automatiseret proces, der altid gemmer filen på den samme placering (f.eks. C: \ Data \ data.csv er altid de nyeste data). Processen med at åbne denne fil og importere den kan også let gøres til en makro:

  1. Åbn Excel-skabelonfilen, der indeholder vores "FormatData" -makro.
  2. Optag en ny makro med navnet “LoadData”.
  3. Med makrooptagelsen skal du importere datafilen som du normalt ville.
  4. Når dataene er importeret, skal du stoppe optagelsen af ​​makroen.
  5. Slet alle celledataene (vælg alle derefter slet).
  6. Gem den opdaterede skabelon (husk at bruge et makroaktiveret skabelonformat).

Når dette er gjort, vil der, når skabelonen åbnes, være to makroer - den ene, der indlæser vores data, og den anden, der formaterer den.

Hvis du virkelig ville gøre dine hænder beskidte med lidt kodedigering, kunne du nemt kombinere disse handlinger i en enkelt makro ved at kopiere koden, der er produceret fra "LoadData" og indsætte den i begyndelsen af ​​koden fra "FormatData".

Download denne skabelon

For din bekvemmelighed har vi inkluderet både Excel-skabelonen, der er produceret i denne artikel, samt en eksempeldatafil, som du kan lege med.

Download Excel-makroskabelon fra How-To Geek