Leer hoe u Excel-macro's kunt gebruiken om saaie taken te automatiseren

Een van de krachtigere, maar zelden gebruikte functies van Excel is de mogelijkheid om heel eenvoudig geautomatiseerde taken en aangepaste logica in macro's te maken. Macro's bieden een ideale manier om tijd te besparen op voorspelbare, repetitieve taken en om standaardindelingen te standaardiseren - vaak zonder een enkele coderegel te hoeven schrijven.

Als je nieuwsgierig bent naar wat macro's zijn of hoe je ze echt kunt maken, geen probleem - we zullen je door het hele proces leiden.

Notitie: hetzelfde proces zou moeten werken in de meeste versies van Microsoft Office. De schermafbeeldingen kunnen er iets anders uitzien.

Wat is een macro?

Een Microsoft Office Macro (aangezien deze functionaliteit van toepassing is op verschillende MS Office-toepassingen) is eenvoudig VBA-code (Visual Basic for Applications) die in een document is opgeslagen. Voor een vergelijkbare analogie, denk aan een document als HTML en een macro als Javascript. Net zoals Javascript HTML op een webpagina kan manipuleren, kan een macro een document manipuleren.

Macro's zijn ongelooflijk krachtig en kunnen vrijwel alles wat je verbeelding kan oproepen oproepen. Als een (zeer) korte lijst met functies die u met een macro kunt doen:

Stijl en opmaak toepassen.
Manipuleer data en tekst.
Communiceren met gegevensbronnen (database, tekstbestanden, etc.).
Maak geheel nieuwe documenten.
Elke combinatie, in elke volgorde, van een van de bovenstaande.

Een macro maken: een uitleg per voorbeeld

We beginnen met uw CSV-bestand met tuinvariëteiten. Niets bijzonders hier, alleen een reeks van 10 × 20 tussen 0 en 100 met zowel een rij- als kolomkop. Ons doel is om een goed geformatteerd, presentabel gegevensblad te produceren met samenvattende totalen voor elke rij.

Zoals we hierboven vermeldden, is een macro VBA-code, maar een van de leuke dingen over Excel is dat je ze kunt maken / opnemen zonder codering - zoals we hier zullen doen.

Als u een macro wilt maken, gaat u naar Beeld> Macro's> Macro opnemen.

Wijs de macro een naam toe (geen spaties) en klik op OK.

Zodra dit is gedaan, allemaal van je acties worden vastgelegd - elke celverandering, scrolactie, venstergrootte, noem maar op.

Er zijn een aantal plaatsen die aangeven dat Excel de opnamemodus is. Een daarvan is door het menu Macro te bekijken en te zien dat Stop Recording de optie voor Record Macro heeft vervangen.

De andere is in de rechter benedenhoek. Het 'stop'-pictogram geeft aan dat het zich in de macromodus bevindt en als u hier drukt, stopt de opname (eveneens, als dit niet in de opnamemodus is, zal dit pictogram de knop Recordmacro zijn, die u kunt gebruiken in plaats van naar het menu Macro's te gaan).

Nu we onze macro opnemen, kunnen we onze samenvattende berekeningen toepassen. Voeg eerst de headers toe.

Gebruik vervolgens de juiste formules (respectievelijk):

= SOM (B2: K2)
= GEMIDDELDE (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= Mediaan (B2: K2)

Markeer nu alle berekeningscellen en sleep de lengte van al onze gegevensrijen om de berekeningen op elke rij toe te passen.

Zodra dit is gebeurd, moet elke rij hun respectieve samenvattingen weergeven.

Nu willen we de samenvattende gegevens voor het hele blad ontvangen, dus we passen nog enkele berekeningen toe:

Respectievelijk:

= SUM (L2: L21)
= GEMIDDELDE (B2: K21) *Dit moet voor alle gegevens worden berekend omdat het gemiddelde van de rijgemiddelden niet noodzakelijkerwijs gelijk is aan het gemiddelde van alle waarden.
= MIN (N2: N21)
= MAX (O2: O21)
= Mediaan (B2: K21) * Berekend voor alle gegevens om dezelfde reden als hierboven.

Nu de berekeningen zijn voltooid, passen we de stijl en opmaak toe. Pas eerst algemene getalopmaak toe op alle cellen door een Alles selecteren te doen (Ctrl + A of klik op de cel tussen de rij- en kolomkoppen) en selecteer het pictogram "Kommagestijl" onder het startmenu..

Pas vervolgens wat visuele opmaak toe op zowel de rij- als kolomkopteksten:

Stoutmoedig.
centered.
Achtergrond vulkleur.

En tot slot, pas wat stijl toe op de totalen.

Wanneer alles klaar is, is dit hoe onze datasheet eruit ziet:

Aangezien we tevreden zijn met de resultaten, stop je met het opnemen van de macro.

Gefeliciteerd - u hebt zojuist een Excel-macro gemaakt.

Om onze nieuw opgenomen macro te gebruiken, moeten we onze Excel-werkmap opslaan in een macro-enabled bestandsformaat. Voordat we dat doen, moeten we echter eerst alle bestaande gegevens wissen zodat deze niet in onze sjabloon is ingesloten (het idee is dat we elke keer dat we deze sjabloon gebruiken, de meest actuele gegevens importeren).

Om dit te doen, selecteert u alle cellen en verwijdert u ze.

Nu de gegevens zijn gewist (maar de macro's nog steeds zijn opgenomen in het Excel-bestand), willen we het bestand opslaan als een macro-enabled sjabloon (XLTM) -bestand. Het is belangrijk om te weten dat als u dit opslaat als een standaardsjabloonbestand (XLTX), macro's dat dan zullen doen niet er vanaf kunnen worden gerend. Als alternatief kunt u het bestand opslaan als een legacy-sjabloonbestand (XLT), waardoor macro's kunnen worden uitgevoerd.

Nadat u het bestand als een sjabloon hebt opgeslagen, gaat u door en sluit u Excel.

Een Excel-macro gebruiken

Voordat we bespreken hoe we deze nieuw opgenomen macro kunnen toepassen, is het belangrijk om in het algemeen enkele punten over macro's te behandelen:

Macro's kunnen schadelijk zijn.
Zie het bovenstaande punt.

VBA-code is eigenlijk vrij krachtig en kan bestanden manipuleren buiten het bereik van het huidige document. Een macro kan bijvoorbeeld willekeurige bestanden in uw map Mijn documenten wijzigen of verwijderen. Als zodanig is het belangrijk om ervoor te zorgen dat je enkel en alleen voer macro's uit vertrouwde bronnen uit.

Als u onze gegevensindelingsmacro wilt gebruiken, opent u het Excel-sjabloonbestand dat hierboven is gemaakt. Wanneer u dit doet, wordt aangenomen dat u standaard beveiligingsinstellingen hebt ingeschakeld, een waarschuwing aan de bovenkant van de werkmap die zegt dat macro's zijn uitgeschakeld. Omdat we een door ons gecreëerde macro vertrouwen, klikt u op de knop 'Inhoud inschakelen'.

Vervolgens gaan we de nieuwste dataset importeren vanuit een CSV (dit is de bron die het werkblad heeft gebruikt om onze macro te maken).

Om het importeren van het CSV-bestand te voltooien, moet u mogelijk een aantal opties instellen om Excel correct te kunnen interpreteren (bijvoorbeeld scheidingsteken, aanwezige headers, etc.).

Zodra onze gegevens zijn geïmporteerd, gaat u gewoon naar het menu Macro's (onder het tabblad Weergave) en selecteert u Macro's bekijken.

In het resulterende dialoogvenster zien we de "FormatData" -macro die we hierboven hebben opgenomen. Selecteer het en klik op Uitvoeren.

Als je eenmaal hebt gerend, kun je de cursor een tijdje zien rondspringen, maar dan zul je zien dat de gegevens worden gemanipuleerd precies zoals we het hebben opgenomen. Wanneer alles is gezegd en gedaan, zou het er net zo uit moeten zien als ons origineel - behalve met andere gegevens.

Kijken onder de motorkap: wat een macrowerk doet

Zoals we een paar keer hebben genoemd, wordt een macro bestuurd door Visual Basic for Applications (VBA) -code. Wanneer u een macro "opneemt", vertaalt Excel eigenlijk alles wat u doet in zijn respectievelijke VBA-instructies. Simpel gezegd: u hoeft geen code te schrijven omdat Excel de code voor u schrijft.

Als u de code wilt weergeven die onze macro uitvoert, klikt u in het dialoogvenster Macro's op de knop Bewerken.

Het venster dat wordt geopend, geeft de broncode weer die is vastgelegd met onze acties bij het maken van de macro. Natuurlijk kunt u deze code bewerken of zelfs nieuwe macro's maken geheel binnen het codevenster. Hoewel de opname-actie die in dit artikel wordt gebruikt waarschijnlijk in de meeste behoeften zal passen, zouden voor sterk aangepaste acties of voorwaardelijke acties u de broncode moeten bewerken.

Ons voorbeeld nemen Een stap verder ...

Ga er hypothetisch vanuit dat ons brongegevensbestand, data.csv, wordt geproduceerd door een geautomatiseerd proces dat het bestand altijd op dezelfde locatie opslaat (bijvoorbeeld C: \ Data \ data.csv is altijd de meest recente gegevens). Het proces om dit bestand te openen en te importeren kan ook gemakkelijk in een macro worden omgezet:

Open het Excel-sjabloonbestand met onze "FormatData" -macro.
Neem een nieuwe macro op met de naam "LoadData".
Met de macro-opname importeert u het gegevensbestand zoals u dat normaal zou doen.
Nadat de gegevens zijn geïmporteerd, stopt u met het opnemen van de macro.
Verwijder alle celgegevens (selecteer alles en verwijder vervolgens).
Sla de bijgewerkte sjabloon op (vergeet niet een sjabloonformaat met macro te gebruiken).

Zodra dit is gebeurd, zijn er telkens wanneer de sjabloon wordt geopend twee macro's - een die onze gegevens laadt en de andere die deze indient.

Als je echt je handen vies wilt maken met een beetje codebewerking, kun je deze acties gemakkelijk combineren in een enkele macro door de code die is geproduceerd met "LoadData" te kopiëren en aan het begin van de code in "FormatData" in te voegen.

Download deze sjabloon

Voor uw gemak hebben we zowel de Excel-sjabloon opgenomen in dit artikel opgenomen als een voorbeeldgegevensbestand waarmee u kunt spelen.

Download Excel-macrasjabloon van How-To Geek