Lär dig att använda Excel-makron för att automatisera tråkiga uppgifter

En av de mer kraftfulla, men sällan använda funktionerna i Excel är möjligheten att mycket enkelt skapa automatiska uppgifter och anpassad logik inom makron. Makroer är ett perfekt sätt att spara tid på förutsägbara, repetitiva uppgifter samt standardisera dokumentformat - många gånger utan att behöva skriva en enda kodrad.

Om du är nyfiken på vilka makron är eller hur man faktiskt skapar dem, inga problem - vi kommer att gå igenom hela processen.

Notera: samma process ska fungera i de flesta versioner av Microsoft Office. Skärmbilderna kan se lite annorlunda ut.

Vad är en makro?

En Microsoft Office Macro (som den här funktionen gäller för flera av MS Office-programmen) är helt enkelt Visual Basic for Applications (VBA) -koden sparad i ett dokument. För en jämförbar analogi, tänk på ett dokument som HTML och ett makro som Javascript. På ungefär samma sätt som Javascript kan manipulera HTML på en webbsida kan ett makro manipulera ett dokument.

Makroer är otroligt kraftfulla och kan göra ganska mycket vad din fantasi kan framkalla. Som en (mycket) kort lista över funktioner du kan göra med ett makro:

• Använd stil och formatering.
• Manipulera data och text.
• Kommunicera med datakällor (databas, textfiler, etc.).
• Skapa helt nya dokument.
• Vilken kombination som helst, i vilken som helst av ovanstående.

Skapa en makro: En förklaring enligt exempel

Vi börjar med din CSV-fil i trädgården. Inget speciellt här, bara en 10 × 20 uppsättning tal mellan 0 och 100 med både en rad och kolumnrubrik. Vårt mål är att skapa en välformaterad presentabel datablad som innehåller sammanfattande totals för varje rad.

Som vi nämnde ovan är ett makro VBA-kod, men en av de fina sakerna om Excel är att du kan skapa / spela in dem med nollkodning som krävs - som vi kommer att göra här.

För att skapa ett makro, gå till Visa> Makroner> Spela in makro.

Tilldela makro ett namn (inga mellanslag) och klicka på OK.

När detta är klart, Allt av dina handlingar spelas in - varje cellbyte, bläddraåtgärd, fönsterändring, du heter det.

Det finns ett par platser som indikerar att Excel är rekordläge. En är genom att titta på makromenyn och notera att Stop Recording har ersatt alternativet för Record Macro.

Den andra ligger i det nedre högra hörnet. Ikonen "Stopp" indikerar att den är i makroläge och om du trycker på här stoppar inspelningen (likaså, när det inte är i inspelningsläge, kommer denna ikon att vara Record Macro-knappen, som du kan använda istället för att gå till makron-menyn).

Nu när vi spelar in vårt makro, låt oss tillämpa våra sammanfattande beräkningar. Lägg först till rubrikerna.

Använd därefter de lämpliga formlerna:

• = SUMMA (B2: K2)
• = MEDEL (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Markera nu alla beräkningsceller och dra längden på alla våra rader för att tillämpa beräkningarna till varje rad.

När detta är klart bör varje rad visa sina respektive sammanfattningar.

Nu vill vi få sammanfattningsdata för hela arket, så vi tillämpar några fler beräkningar:

Respektive:

• = SUMMA (L2: L21)
• = AVERAGE (B2: K21) *Detta måste beräknas för alla data eftersom medelvärdet av radmedeltalen inte nödvändigtvis motsvarar genomsnittet av alla värden.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Beräknad över alla data av samma skäl som ovan.

Nu när beräkningarna är färdiga, kommer vi att tillämpa stilen och formateringen. Först tillämpa generell nummerformatering i alla celler genom att göra en Välj alla (antingen Ctrl + A eller klicka på cellen mellan rad och kolumnrubriker) och välj ikonen "Komma Style" under Hemmenyn.

Använd sedan visuell formatering till både rad- och kolumnrubrikerna:

• Djärv.
• centrerad.
• Bakgrundsfärg.

Och slutligen tillämpa någon stil på totalsummorna.

När allt är klart så ser det här som vårt datablad ser ut:

Eftersom vi är nöjda med resultaten, stoppa inspelningen av makroet.

Grattis - du har just skapat ett Excel-makro.

För att kunna använda vårt nyligen registrerade makro måste vi spara vår Excel-arbetsbok i ett makroaktiverat filformat. Men innan vi gör det måste vi först rensa alla befintliga data så att den inte är inbäddad i vår mall (idén är att varje gång vi använder den här mallen, importerar vi den mest aktuella data).

För att göra detta, välj alla celler och radera dem.

Med de data som nu rensats (men makronen som fortfarande ingår i Excel-filen) vill vi spara filen som en XLTM-fil (makroaktiverad mall). Det är viktigt att notera att om du sparar detta som en standardmall (XLTX) -fil kommer makron att inte kunna köras från den. Alternativt kan du spara filen som en äldre mall (XLT) -fil, som tillåter att makron körs.

När du har sparat filen som en mall, fortsätt och stäng Excel.

Använda en Excel-makro

Innan vi täcker hur vi kan tillämpa det här nyregistrerade makroet, är det viktigt att täcka några punkter om makron i allmänhet:

• Makroner kan vara skadliga.
• Se punkten ovan.

VBA-koden är faktiskt ganska kraftfull och kan manipulera filer utanför ramen för det aktuella dokumentet. Ett makro kan till exempel ändra eller radera slumpmässiga filer i mappen Mina dokument. Som sådan är det viktigt att försäkra dig endast köra makron från betrodda källor.

Om du vill använda vårt datamatormakroskopi öppnar du Excel Mall-filen som skapades ovan. När du gör detta, förutsatt att du har standard säkerhetsinställningar aktiverade, visas en varning överst i arbetsboken som säger att makron är inaktiverade. Eftersom vi litar på ett makro som skapats av oss, klickar du på knappen "Aktivera innehåll".

Upp nästa kommer vi att importera den senaste datasatsen från en CSV (detta är källan som kalkylbladet används för att skapa vårt makro).

För att slutföra importen av CSV-filen måste du kanske ställa in några alternativ för att Excel ska kunna tolka det korrekt (t.ex. avgränsare, aktuella rubriker etc.).

När våra data har importerats går du bara till makron-menyn (under fliken Visa) och väljer Visa makron.

I den resulterande dialogrutan ser vi makro "FormatData" som vi spelade in ovan. Markera den och klicka på Kör.

När du har kört kan du se att markören hoppa i ett ögonblick, men som det kommer du att se att data hanteras exakt som vi spelade in det. När allt är sagt och gjort ska det se ut som vårt ursprungliga - utom med olika data.

Ser under huven: Vad gör ett makroarbete

Som vi nämnde ett par gånger drivs ett makro av Visual Basic for Applications (VBA) -koden. När du "registrerar" ett makro, översätter Excel faktiskt allt du gör i sina respektive VBA-instruktioner. För att uttrycka det enkelt - du behöver inte skriva någon kod eftersom Excel skriver koden för dig.

För att visa koden som gör vår makrokörning, klicka på Rediger-knappen i dialogrutan Makroner.

Fönstret som öppnas visar källkoden som spelades in från våra handlingar när makron skapades. Naturligtvis kan du redigera den här koden eller skapa nya makron helt inne i kodfönstret. Medan inspelningsåtgärden som används i den här artikeln troligtvis kommer att passa de flesta behov, skulle mer anpassade åtgärder eller villkorade åtgärder kräva att du redigerar källkoden.

Ta vårt exempel ett steg längre ...

Hypotetiskt förutsätt att vår källdatafil, data.csv, produceras av en automatiserad process som alltid sparar filen till samma plats (t ex C: \ Data \ data.csv är alltid den senaste data). Processen att öppna den här filen och importera den kan enkelt göras i ett makro också:

1. Öppna Excel Mall-filen som innehåller vårt "FormatData" -makro.
2. Spela in ett nytt makro med namnet "LoadData".
3. Med makroinspelningen importerar du datafilen som du normalt skulle.
4. När data har importerats, sluta registrera makroet.
5. Radera alla celldata (välj alla ta bort).
6. Spara den uppdaterade mallen (kom ihåg att använda ett makroaktiverat mallformat).

När detta är klart, kommer varje mall att öppnas två makron - en som laddar våra data och den andra som formaterar den.

Om du verkligen ville få dina händer smutsiga med lite kodredigering, kan du enkelt kombinera dessa åtgärder till ett enda makro genom att kopiera koden som produceras från "LoadData" och infoga den i början av koden från "FormatData".

Hämta den här mallen

För din bekvämlighet har vi inkluderat både Excel-mallen som produceras i den här artikeln och en exempeldatafil för dig att leka med.

Hämta Excel Macro Template från How-To Geek