Inhoud
Excel heeft een krachtige, maar tegelijkertijd zeer zelden gebruikte mogelijkheid om automatische reeksen acties te maken met behulp van macro's. Een macro is een ideale uitweg als je te maken hebt met hetzelfde type taak dat vaak wordt herhaald. Bijvoorbeeld gegevensverwerking of documentopmaak volgens een gestandaardiseerd sjabloon. In dit geval heeft u geen kennis van programmeertalen nodig.
Ben je al benieuwd naar wat een macro is en hoe het werkt? Ga dan stoutmoedig door - dan zullen we stap voor stap het hele proces van het maken van een macro met u doen.
Wat is Makro?
Een macro in Microsoft Office (ja, deze functionaliteit werkt in veel applicaties van het Microsoft Office pakket hetzelfde) is een programmacode in een programmeertaal Visual Basic voor toepassingen (VBA) opgeslagen in het document. Om het duidelijker te maken, een Microsoft Office-document kan worden vergeleken met een HTML-pagina, dan is een macro een analoog van Javascript. Wat Javascript kan doen met HTML-gegevens in een webpagina, lijkt sterk op wat een macro kan doen met gegevens in een Microsoft Office-document.
Macro's kunnen zo ongeveer alles doen wat u wilt in een document. Hier zijn er enkele (een heel klein deel):
- Pas stijlen en opmaak toe.
- Voer verschillende bewerkingen uit met numerieke en tekstgegevens.
- Gebruik externe gegevensbronnen (databasebestanden, tekstdocumenten, enz.)
- Maak een nieuw document.
- Doe al het bovenstaande in elke combinatie.
Een macro maken – een praktisch voorbeeld
Laten we bijvoorbeeld het meest voorkomende bestand nemen CSV. Dit is een eenvoudige 10×20-tabel gevuld met getallen van 0 tot 100 met koppen voor kolommen en rijen. Het is onze taak om deze dataset om te zetten in een representatief opgemaakte tabel en totalen te genereren in elke rij.
Zoals eerder vermeld, is een macro code geschreven in de VBA-programmeertaal. Maar in Excel kunt u een programma maken zonder een regel code te schrijven, wat we nu zullen doen.
Om een macro te maken, open Bekijk (Type) > Macro's (Macro) > Macro opnemen (Macro-opname...)
Geef je macro een naam (geen spaties) en klik op OK.
Vanaf dit moment worden AL je acties met het document vastgelegd: wijzigingen aan cellen, scrollen door de tabel, zelfs het formaat van het venster wijzigen.
Excel signaleert dat de macro-opnamemodus op twee plaatsen is ingeschakeld. Eerst op het menu Macro's (Macro's) – in plaats van een string Macro opnemen (Een macro opnemen...) regel verscheen Opname stoppen (Stop met opnemen).
Ten tweede, in de linkerbenedenhoek van het Excel-venster. Icoon stop (klein vierkantje) geeft aan dat de macro-opnamemodus is ingeschakeld. Door erop te klikken stopt de opname. Omgekeerd, wanneer de opnamemodus niet is ingeschakeld, is er een pictogram om macro-opname op deze locatie in te schakelen. Als u erop klikt, krijgt u hetzelfde resultaat als het inschakelen van de opname via het menu.
Nu de macro-opnamemodus is ingeschakeld, gaan we aan onze taak beginnen. Laten we eerst koppen toevoegen voor de samenvattingsgegevens.
Next, enter the formulas in the cells in accordance with the names of the headings (variants of the formulas for the English and versions of Excel are given, cell addresses are always Latin letters and numbers):
- =SOM(B2:K2) or =SOM(B2:K2)
- =GEMIDDELDE(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIAAN(B2:K2) or =MEDIAAN(B2:K2)
Selecteer nu de cellen met formules en kopieer ze naar alle rijen van onze tabel door de handgreep voor automatisch aanvullen te slepen.
Nadat u deze stap hebt voltooid, moet elke rij de bijbehorende totalen hebben.
Vervolgens zullen we de resultaten voor de hele tabel samenvatten, hiervoor doen we nog een paar wiskundige bewerkingen:
Respectievelijk:
- =SOM(L2:L21) or =SOM(L2:L21)
- =GEMIDDELDE(B2:K21) or =СРЗНАЧ(B2:K21) – om deze waarde te berekenen, is het noodzakelijk om exact de initiële gegevens van de tabel te nemen. Als u het gemiddelde neemt van de gemiddelden voor afzonderlijke rijen, zal het resultaat anders zijn.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAAN(B2:K21) or =MEDIAAN(B2:K21) – we overwegen om de initiële gegevens van de tabel te gebruiken, om de hierboven aangegeven reden.
Nu we klaar zijn met de berekeningen, gaan we wat opmaak doen. Laten we eerst hetzelfde gegevensweergaveformaat instellen voor alle cellen. Selecteer alle cellen op het blad, gebruik hiervoor de sneltoets Ctrl + Aof klik op het icoon Selecteer alle, die zich op de kruising van de rij- en kolomkoppen bevindt. Dan klikken Kommastijl (Gescheiden formaat) tabblad Home (Thuis).
Wijzig vervolgens het uiterlijk van de kolom- en rijkoppen:
- Vet lettertype.
- Uitlijning in het midden.
- Kleur vulling.
En tot slot, laten we het formaat van de totalen instellen.
Zo zou het er uiteindelijk uit moeten zien:
Als alles je bevalt, stop dan met het opnemen van de macro.
Gefeliciteerd! Je hebt zojuist je eerste macro zelf in Excel opgenomen.
Om de gegenereerde macro te gebruiken, moeten we het Excel-document opslaan in een indeling die macro's ondersteunt. Eerst moeten we alle gegevens verwijderen uit de tabel die we hebben gemaakt, dwz er een lege sjabloon van maken. Feit is dat we in de toekomst, werkend met deze sjabloon, de meest recente en relevante gegevens erin zullen importeren.
Om alle cellen uit de gegevens te wissen, klikt u met de rechtermuisknop op het pictogram Selecteer alle, die zich op de kruising van de rij- en kolomkoppen bevindt, en selecteer in het contextmenu Verwijder (Verwijderen).
Nu is ons blad volledig ontdaan van alle gegevens, terwijl de macro geregistreerd blijft. We moeten de werkmap opslaan als een macro-enabled Excel-sjabloon met de extensie XLTM.
Een belangrijk punt! Als u het bestand opslaat met de extensie XLTX, dan zal de macro er niet in werken. Trouwens, u kunt de werkmap opslaan als een Excel 97-2003-sjabloon, die de indeling heeft: XLT-extensie, het ondersteunt ook macro's.
Wanneer de sjabloon is opgeslagen, kunt u Excel veilig afsluiten.
Een macro uitvoeren in Excel
Voordat ik alle mogelijkheden van de macro die je hebt gemaakt onthult, denk ik dat het goed is om aandacht te besteden aan een paar belangrijke punten met betrekking tot macro's in het algemeen:
- Macro's kunnen schadelijk zijn.
- Lees de vorige alinea nog eens.
VBA-code is zeer krachtig. Het kan met name bewerkingen uitvoeren op bestanden buiten het huidige document. Een macro kan bijvoorbeeld alle bestanden in een map verwijderen of wijzigen Mijn documenten. Voer daarom alleen macro's uit en sta deze toe van bronnen die u vertrouwt.
Om onze macro voor gegevensopmaak uit te voeren, opent u het sjabloonbestand dat we in het eerste deel van deze zelfstudie hebben gemaakt. Als u standaard beveiligingsinstellingen heeft, verschijnt er bij het openen van een bestand een waarschuwing boven de tabel dat macro's zijn uitgeschakeld en een knop om ze in te schakelen. Omdat we de sjabloon zelf hebben gemaakt en we onszelf vertrouwen, drukken we op de knop Inhoud aanzetten (Inhoud opnemen).
De volgende stap is om de laatste bijgewerkte dataset uit het bestand te importeren CSV (op basis van zo'n bestand hebben we onze macro gemaakt).
Wanneer u gegevens uit een CSV-bestand importeert, kan Excel u vragen enkele instellingen in te stellen om de gegevens correct naar de tabel over te brengen.
Als het importeren is voltooid, gaat u naar het menu Macro's (Macro's) tabblad Bekijk (Bekijken) en selecteer een commando Bekijk macro's (Macro).
In het dialoogvenster dat wordt geopend, zien we een regel met de naam van onze macro Formaatgegevens. Selecteer het en klik op lopen (Uitvoeren).
Wanneer de macro begint te lopen, ziet u de tabelcursor van cel naar cel springen. Na een paar seconden worden dezelfde handelingen met de gegevens uitgevoerd als bij het opnemen van een macro. Als alles klaar is, moet de tabel er hetzelfde uitzien als het origineel dat we met de hand hebben opgemaakt, alleen met andere gegevens in de cellen.
Laten we eens onder de motorkap kijken: hoe werkt een macro?
Zoals meer dan eens vermeld, is een macro programmacode in een programmeertaal. Visual Basic voor toepassingen (VBA). Wanneer u de macro-opnamemodus inschakelt, registreert Excel eigenlijk elke actie die u uitvoert in de vorm van VBA-instructies. Simpel gezegd, Excel schrijft de code voor u.
Om deze programmacode te zien, moet je in het menu Macro's (Macro's) tabblad Bekijk (bekijken) klik Bekijk macro's (Macro's) en klik in het dialoogvenster dat wordt geopend op Edit (Wijziging).
Het raam gaat open. Visual Basic voor toepassingen, waarin we de programmacode zullen zien van de macro die we hebben opgenomen. Ja, je hebt het goed begrepen, hier kun je deze code wijzigen en zelfs een nieuwe macro maken. De acties die we in deze les met de tabel hebben uitgevoerd, kunnen worden vastgelegd met behulp van automatische macro-opname in Excel. Maar complexere macro's, met een nauwkeurig afgestemde volgorde en actielogica, vereisen handmatige programmering.
Laten we nog een stap aan onze taak toevoegen...
Stel je voor dat ons originele gegevensbestand gegevens.csv wordt automatisch gemaakt door een of ander proces en wordt altijd op dezelfde plaats op schijf opgeslagen. Bijvoorbeeld, C:Datadata.csv – pad naar het bestand met bijgewerkte gegevens. Het proces van het openen van dit bestand en het importeren van gegevens daaruit kan ook worden vastgelegd in een macro:
- Open het sjabloonbestand waarin we de macro hebben opgeslagen − Formaatgegevens.
- Maak een nieuwe macro met de naam Gegevens laden.
- Tijdens het opnemen van een macro Gegevens laden gegevens importeren uit bestand gegevens.csv – zoals we deden in het vorige deel van de les.
- Wanneer het importeren is voltooid, stopt u met het opnemen van de macro.
- Verwijder alle gegevens uit cellen.
- Sla het bestand op als een macro-enabled Excel-sjabloon (XLTM-extensie).
Door deze sjabloon uit te voeren, krijgt u dus toegang tot twee macro's: de ene laadt de gegevens, de andere formatteert ze.
Als u wilt beginnen met programmeren, kunt u de acties van deze twee macro's in één combineren - gewoon door de code te kopiëren van Gegevens laden naar het begin van de code Formaatgegevens.