9Sep

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 er en ideel måde at spare tid på forudsigelige, gentagne opgaver og 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 inde 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.
  • Manipuler 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 ovennævnte.

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 summariske 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.

Hvis du vil oprette en makro, skal du gå til Vis & gt;Makroer & gt;Optag makro.

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

Når dette er gjort, registreres alle af dine handlinger - 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, da vi optager vores makro, lad os anvende vores sammenfattende beregninger. Tilføj først overskrifterne.

Anvend derefter de relevante formler( henholdsvis):

  • = SUM( B2: K2)
  • = AVERAGE( 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 vil vi få sammenfattende data for hele arket, så vi anvender nogle få flere beregninger:

Respektivt:

  • = SUM( L2: L21)
  • = AVERAGE( B2: K21) * Dette skal beregnes på tværs af alledata, 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:

  • Bold.
  • centreret.
  • Baggrundsfarve.

Og endelig anvende nogle stil på totalerne.

Når alt er færdigt, ser det her, 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 opdaterede 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 standardmal( XLTX) -fil, vil makroer ikke kunne køres fra den. 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 du dækker, hvordan vi kan anvende denne nyoptagne 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 kraftig 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, at kun kører 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'.

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

For at afslutte 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", som vi registrerede ovenfor. Vælg det og klik på Kør.

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

Kigger 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.

Hvis du vil se koden, der laver vores makrokørsel, 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.

Ved at tage vores eksempel et trin videre. ..

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

  1. Åbn Excel Template filen, der indeholder vores "FormatData" makro.
  2. Optag en ny makro med navnet "LoadData".
  3. Med makrooptagelsen importerer du datafilen som du normalt ville.
  4. Når dataene er importeret, skal du stoppe med at optage makroen.
  5. Slet alle celledata( vælg alle derefter slet).
  6. 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 ønskede 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 inkluderet både Excel-skabelonen, der er produceret i denne artikel, samt en prøvedatafil, som du kan lege med.

Hent Excel makro skabelon fra How-To Geek