9Sep

Lär dig hur man använder 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.

Obs! 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 oerhört 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.
  • Alla kombinationer, i vilken ordning som helst, av något av ovanstående.

Skapa en makro: En förklaring enligt exempel

Vi börjar med din CSV-fil av 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 & gt;Makroner & gt;Spela in makro.

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

När detta är klart, spelas alla av dina handlingar in - varje cellbyte, bläddringsåtgärd, fönsterändring, namnge den.

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 är i 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 lämpliga formler:

  • = SUM( B2: K2)
  • = AVERAGE( 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 det här är gjort ska varje rad visa sina respektive sammanfattningar.

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

Respektivt:

  • = SUM( L2: L21)
  • = AVERAGE( B2: K21) * Detta måste beräknas över alladata eftersom medelvärdet av radmedelvärdena 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 anledning som ovan.

Nu när beräkningarna är färdiga, kommer vi att tillämpa stilen och formateringen. Först tillämpa generell nummerformatering över 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" på Hem-menyn.

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

  • Bold.
  • Centrerad.
  • Bakgrundsfärg.

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

När allt är klart så ser det här vårt datablad 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. 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 det som en standardmall( XLTX) -fil, kommer makroerna inte att 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 Macro

Innan du 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:

  • Makroer 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 du endast kör 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".

Vid 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 är importerade går du bara till makron-menyn( under fliken Visa) och väljer Visa makron.

I den resulterande dialogrutan ser vi det "FormatData" -makro 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 om i några ögonblick, men som det kommer du att se datan som manipuleras precis när vi spelade in den. När allt är sagt och gjort, ska det se ut som vårt ursprungliga - förutom 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å redigera-knappen i dialogrutan Makroner.

Fönstret som öppnas visar källkoden som spelades in från våra handlingar när makro skapades. Självklart kan du redigera den här koden eller till och med skapa nya makron helt inne i kodfönstret.Även om inspelningsåtgärden som används i den här artikeln troligtvis kommer att passa de flesta behov, skulle mer anpassade åtgärder eller villkorliga å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 mest användasenaste data).Processen att öppna den här filen och importera den kan enkelt även göras i ett makro:

  1. Öppna Excel Mall-filen med 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 sedan radera).
  6. Spara den uppdaterade mallen( kom ihåg att använda ett makroaktiverat mallformat).

När det här är gjort, 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 i ett enda makro genom att kopiera koden som produceras från "LoadData" och infoga den i början av koden från "FormatData".

Ladda ner den här mallen

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

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