Lær at bruge Excel-makroer til at automatisere trættende opgaver

En af de mere kraftfulde, men sjældent brugte funktioner i Excel er evnen til meget nemt at skabe automatiske opgaver og brugerdefineret logik inden for makroer. Makroer giver en ideel måde at spare tid på forudsigelige, gentagne opgaver samt standardisere dokumentformater - mange gange uden at skulle skrive en enkelt kode.

Hvis du er nysgerrig, hvad makroer er eller hvordan man faktisk opretter dem, intet problem - vi vil gå igennem 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 Macro (som denne funktionalitet gælder for flere af MS Office-applikationer) er simpelthen Visual Basic for Applications (VBA) -kode gemt i et dokument. For en sammenlignelig analogi, tænk på et dokument som HTML og en makro som Javascript. På 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 fremkalde. Som en (meget) kort liste over funktioner, du kan 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 hvilken som helst rækkefølge, af en hvilken som helst af ovenstående.

Oprettelse af en makro: En forklaring ved eksempel

Vi starter med din haveort CSV-fil. Intet særligt her, kun et 10 × 20 sæt tal mellem 0 og 100 med både en række og kolonneoverskrift. Vores mål er at fremstille et velformateret, præsentabelt datablad, der indeholder sammenfattende totaler for hver række.

Som vi nævnte ovenfor, er en makro VBA kode, men en af de gode ting om Excel er, at du kan oprette / registrere dem med nulkodning på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, alle af dine handlinger registreres - hver celleændring, rullehandling, vinduets størrelse ændrer du navnet.

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

Den anden er i nederste højre hjørne. Ikonet "stop" angiver, at det er i makro-tilstand, og tryk her vil stoppe optagelsen (ligeledes, når det ikke er i optagetilstand, vil dette ikon være Record Macro-knappen, som du kan bruge i stedet for at gå til makro-menuen).

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)
= MIDDEL (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= MEDIAN (B2: K2)

Fremhæv nu alle beregningscellerne, og træk længden af alle vores datarader for at anvende beregningerne til hver række.

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

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

Henholdsvis:

= SUM (L2: L21)
= AVERAGE (B2: K21) *Dette skal beregnes på tværs af alle data, fordi gennemsnittet af række gennemsnit ikke nødvendigvis er lig med gennemsnittet af alle værdier.
= MIN (N2: N21)
= MAX (O2: O21)
= MEDIAN (B2: K21) * Beregnet på tværs af alle data af samme årsag som ovenfor.

Nu hvor beregningerne er færdige, vil vi anvende stil og formatering. Først anvender du generel nummerformatering på tværs af alle celler ved at vælge Select All (enten Ctrl + A eller klikke på cellen mellem rækken og kolonneoverskrifterne) og vælg ikonet Komma Style i menuen Hjem.

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

Dristig.
Centreret.
Baggrundsfyld farve.

Og endelig anvende nogle stil på totals.

Når alt er færdigt, ser det ud til, at vores datablad ser ud:

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

Tillykke - du har netop oprettet en Excel-makro.

For at kunne bruge vores nyregistrerede makro, skal vi gemme vores Excel-arbejdsbog i et makroaktiveret filformat. Men før vi gør det, skal vi først rydde alle eksisterende data, så den ikke er indlejret i vores skabelon (ideen er, hver gang vi bruger denne skabelon, importerer vi de mest ajourførte data).

For at gøre dette skal du markere alle celler og slette dem.

Med de data, der nu er ryddet (men de makroer, der stadig er inkluderet i Excel-filen), vil vi gemme filen som en makroaktiveret skabelon (XLTM) -fil. Det er vigtigt at bemærke, at hvis du gemmer dette som en standard skabelon (XLTX) fil, vil makroer ikke kunne løbe fra det. Alternativt kan du gemme filen som en legitim skabelon (XLT) fil, 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 Macro

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

Makroer kan være ondsindede.
Se punktet ovenfor.

VBA-koden er faktisk ret stærk og kan manipulere filer uden for det nuværende dokument. En makro kan for eksempel ændre eller slette tilfældige filer i mappen Mine dokumenter. Som sådan er det vigtigt at sikre dig kun kør makroer fra pålidelige kilder.

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

Fremover skal vi importere det nyeste datasæt fra en CSV (dette er kilden, som regnearket bruges til at oprette vores makro).

For at fuldføre importen af CSV-filen, skal du muligvis indstille nogle få muligheder for at Excel kan fortolke det korrekt (for eksempel afgrænsning, overskrifter til stede osv.).

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

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

Når du kører, kan du se, at markøren hopper rundt i et øjeblik, men som det vil du se, at dataene bliver manipuleret Nemlig som vi registrerede det. Når alt er sagt og gjort, skal det se ud som vores originale - undtagen med forskellige data.

Ser under hætten: Hvad laver et makroarbejde

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

For at få vist koden, der gør vores makro-runde, skal du klikke på Rediger-knappen i dialogboksen Makroer.

Vinduet der åbner viser kildekoden, der blev optaget fra vores handlinger, når du opretter makroen. Selvfølgelig kan du redigere denne kode eller endda oprette nye makroer helt inde i kodevinduet. Mens den optagelseshandling, der bruges i denne artikel, sandsynligvis passer til de fleste behov, vil mere yderst tilpassede handlinger eller betingede handlinger kræve, at du redigerer kildekoden.

Tager vores eksempel et skridt videre

Hypotetisk antage, at vores kildedatafil, data.csv, er produceret af en automatiseret proces, som altid gemmer filen på samme sted (f.eks. C: \ Data \ data.csv er altid de nyeste data). Processen med at åbne denne fil og importere den kan nemt laves i en makro også:

Åbn Excel-skabelonfilen, der indeholder vores makro "FormatData".
Optag en ny makro med navnet "LoadData".
Med makrooptagelsen importerer du datafilen som du normalt ville.
Når dataene er importeret, skal du stoppe med at optage makroen.
Slet alle celledata (vælg alle derefter slette).
Gem den opdaterede skabelon (husk at bruge et makroaktiveret skabelonformat).

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

Hvis du virkelig ønsker at få dine hænder snavset med en smule kode redigering, kan du nemt kombinere disse handlinger i en enkelt makro ved at kopiere koden produceret fra "LoadData" og indsætte den i begyndelsen af koden fra "FormatData".

Download denne skabelon

For nemheds skyld har vi medtaget både Excel-skabelonen, der er produceret i denne artikel, samt en prøvedatafil, som du kan lege med.

Download Excel Macro Template fra How-To Geek