9Sep
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 regel code te hoeven schrijven.
Als u nieuwsgierig bent naar wat macro's zijn of hoe u ze daadwerkelijk kunt maken, geen probleem - wij zullen u door het hele proces leiden.
Opmerking: 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 Visual Basic for Applications( VBA) -code 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 fantasie kan oproepen oproepen. Als een( zeer) korte lijst met functies die u met een macro kunt uitvoeren:
- Stijl en opmaak toepassen.
- Gegevens en tekst bewerken.
- Communiceren met gegevensbronnen( database, tekstbestanden, enz.).
- Maak volledig nieuwe documenten.
- Elke combinatie, in elke volgorde, van een van de bovenstaande.
Een macro maken: een uitleg door het voorbeeld
We beginnen met uw CSV-bestand met tuinvariëteit. Hier niets bijzonders, 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 maken met samenvattende totalen voor elke rij.
Zoals we hierboven al stelden, 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.
Ga naar View & gt; om een macro te maken. Macro's & gt;Macro opnemen.
Wijs de macro een naam toe( geen spaties) en klik op OK.
Als dit is gebeurd, worden alle van je acties 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 op te merken dat Stop Recording de optie voor Record Macro heeft vervangen.
De andere bevindt zich in de rechterbenedenhoek. Het pictogram 'stop' geeft aan dat het zich in de macromodus bevindt en als u hier drukt, wordt de opname gestopt( eveneens, als dit niet in de opnamemodus is, is dit pictogram de knop Recordmacro 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):
- = SUM( B2: K2)
- = GEMIDDELD( B2: K2)
- = MIN( B2: K2)
- = MAX( B2: K2)
- = MEDIAN( B2: K2)
Markeer nu alle berekeningscellen en sleep de lengte van al onze gegevensrijen om de berekeningen op elke rij toe te passen.
Als dit is gebeurd, moet elke rij hun respectievelijke samenvattingen weergeven.
Nu willen we de samenvattende gegevens voor het hele blad krijgen, dus we gebruiken nog enkele berekeningen:
Respectievelijk:
- = SUM( L2: L21)
- = GEMIDDELD( B2: K21) * Dit moet voor alle vakken worden berekendgegevens omdat het gemiddelde van de rijgemiddelden niet noodzakelijkerwijs gelijk is aan het gemiddelde van alle waarden.
- = MIN( N2: N21)
- = MAX( O2: O21)
- = MEDIAANS( 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 nummeropmaak 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 menu Start.
Pas vervolgens wat visuele opmaak toe op zowel de rij- als kolomkoppen:
- Vet.
- gecentreerd.
- Achtergrond vulkleur.
En ten slotte, pas wat stijl toe op de totalen.
Wanneer alles is voltooid, ziet ons gegevensblad er zo uit:
Aangezien we tevreden zijn met de resultaten, stop de opname van de macro.
Gefeliciteerd - je 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 is ingesloten in onze sjabloon( het idee is dat we elke keer dat we deze sjabloon gebruiken, de meest actuele gegevens importeren).
Selecteer hiervoor alle cellen en verwijder 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 op te merken dat als u dit opslaat als een standaardsjabloonbestand( XLTX), macro's niet kunnen uitvoeren. Als alternatief kunt u het bestand opslaan als een legacy-sjabloonbestand( XLT), waarmee macro's kunnen worden uitgevoerd.
Nadat u het bestand als sjabloon hebt opgeslagen, kunt u doorgaan en Excel sluiten.
Een Excel-macro gebruiken
Voordat we ingaan op hoe we deze nieuw opgenomen macro kunnen toepassen, is het belangrijk om een paar punten over macro's in het algemeen te behandelen:
- Macro's kunnen schadelijk zijn.
- Zie het bovenstaande punt.
VBA-code is eigenlijk vrij krachtig en kan bestanden manipuleren die buiten het bereik van het huidige document vallen. Een macro kan bijvoorbeeld willekeurige bestanden in uw map Mijn documenten wijzigen of verwijderen. Als zodanig is het belangrijk om ervoor te zorgen dat alleen macro's uitvoert uit vertrouwde bronnen.
Als u wilt dat onze gegevensindelingsmacro wordt gebruikt, 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'.
Nu 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 wellicht enkele opties instellen om Excel correct te kunnen interpreteren( bijvoorbeeld afbakening, aanwezige headers, etc.).
Zodra onze gegevens zijn geïmporteerd, gaat u eenvoudig naar het menu Macro's( onder het tabblad Beeld) 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 bent gestart, zie je misschien dat de cursor een tijdje rondspringt, maar je ziet dan dat de gegevens exact manipuleerden terwijl we het vastlegden. Wanneer alles is gezegd en gedaan, zou het er net zo uit moeten zien als ons origineel - behalve met andere gegevens.
Onder de motorkap kijken: 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.
Om de code te bekijken 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 voldoet aan de meeste behoeften, moeten voor sterk aangepaste acties of voorwaardelijke acties u de broncode wijzigen.
Ons voorbeeld 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( bijv. C: \ Data \ data.csv is altijd het meestrecente gegevens).Het proces om dit bestand te openen en te importeren kan ook gemakkelijk in een macro worden gemaakt:
- Open het Excel-sjabloonbestand met onze "FormatData" -macro.
- Neem een nieuwe macro op met de naam "LoadData".
- Importeer met de macro-opname het gegevensbestand zoals u dat normaal zou doen.
- Stop de opname van de macro zodra de gegevens zijn geïmporteerd.
- Wis alle celgegevens( selecteer alles en verwijder vervolgens).
- Sla de bijgewerkte sjabloon op( vergeet niet om een sjabloonindeling met een macro te gebruiken).
Als dit eenmaal 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 dit sjabloon
Voor uw gemak hebben we zowel de Excel-sjabloon opgenomen in dit artikel als een voorbeeldgegevensbestand voor u om mee te spelen.
Excel-macrosjabloon downloaden van How-To Geek
