Hoe rapporten in Excel te automatiseren

Een van de vele functies van Microsoft Excel is de mogelijkheid om rapporten te automatiseren. U kunt interactieve spreadsheets maken om de vaardigheden van anderen om gegevens in uw boeken in te voeren te vereenvoudigen en u kunt ook het genereren van uw rapporten automatiseren. Beide functies vereisen enige kennis in Visual Basic. Hier zijn de stappen om deze twee taken uit te voeren.

Inhoud

Stappen

Methode 1maak een interactieve spreadsheet
Methode 2automatiseer het genereren van rapporten

Tips

stappen

Methode 1
Maak een interactieve spreadsheet

Titel afbeelding Automate Reports in Excel Stap 1

Bepaal wat de lay-out van de spreadsheet zal zijn. U moet uw spreadsheet zo ontwerpen dat andere mensen die het gebruiken snel de velden kunnen vinden die ze nodig hebben om gegevens in te voeren.

U kunt de lay-out van uw spreadsheet verticaal of horizontaal maken. Voor de meeste gebruikers is het gemakkelijker om met een verticale lay-out te werken, vooral als u van plan bent ze het blad te laten afdrukken.

Titel afbeelding Automate Reports in Excel Step 2

Maak tekstlabels voor uw spreadsheet. Het is beter om een label boven elke kolom te hebben, evenals een label in de cel links van elk van de cellen in de kolom waar u invoervelden wilt plaatsen.

Titel afbeelding Automate Reports in Excel Step 3

Druk tegelijkertijd op de toetsen Alt en F11. Hiermee wordt de Microsoft Visual Basic-editor geopend.

Titel afbeelding Automate Reports in Excel Step 4

Dubbelklik op "ThisWorkbook" in het opgeroepen paneel "Project-VBA-project" die zich in het bovenste deel van de linkerkant bevindt. Hierdoor wordt een codevenster geopend in het hoofdgedeelte van de editor.

Titel afbeelding Automate Reports in Excel Step 5

Selecteer de optie "procédé" in het menu Invoegen. Dit zal u het dialoogvenster tonen "Voeg procedure toe".

Titel afbeelding Automate Reports in Excel Step 6

Voer het veld in "naam" een naam voor uw procedure. Geef uw procedure een naam die representatief is (bijvoorbeeld "SumarGastos") als u uw interactieve spreadsheet gebruikt om uitgaven te rapporteren die verband houden met reizen. Klik op "aanvaarden" om de dialoog te sluiten.

De naam van uw procedure mag geen spaties bevatten, maar u kunt het onderstrepingsteken (_) gebruiken als vervanging.

Zodra het dialoogvenster is gesloten "Voeg procedure toe", ziet u een regel met de naam als "Openbare sub" gevolgd door de naam van uw procedure. Onder deze regel is er een spatie en dan zie je de woorden "End Sub".

Titel afbeelding Automate Reports in Excel Step 7

Voer de code in voor elk veld dat in het werkblad moet worden ingevoerd. U moet twee regels code voor elk veld schrijven.

De eerste regel code heeft de vorm "Bereik ("nombreDeLaCelda"). Selecteer", waar "nombreDeLaCelda" vertegenwoordigt de cel waarin het veld waar de gegevens worden ingevoerd, zal verschijnen. Dit moet de cel zijn die zich onmiddellijk rechts van een tekstlabel bevindt. Als u een tekstlabel hebt in cel A2, moet u een invoerveld in cel B2 plaatsen (Bereik ("B2"). Selecteer). Voeg de aanhalingstekens toe die naast de celnaam staan, niet de aanhalingstekens rond de volledige codeverklaring.

De tweede regel code heeft de vorm "ActiveCell.Value = InputBox ("SolicitudDeEntrada")", waar "SolicitudDeEntrada" staat voor de tekst die verschijnt om aan de gebruiker aan te geven welk type gegevens moet worden ingevoerd in de invoercel. Als de invoercel bijvoorbeeld wordt gebruikt om voedselkosten in te voeren, moet u deze vervangen "SolicitudDeEntrada" voor de tekst "Voer het totaal van alle maaltijden in, inclusief tips" (inclusief de aanhalingstekens rond de tekst van het invoerverzoek, maar niet de aanhalingstekens die het volledige commando omsluiten).

Titel afbeelding Automate Reports in Excel Step 8

Voer de code in voor elk berekeningsveld. Nogmaals, moet dezelfde twee lijnen boven beschreven te gebruiken, maar deze keer je ActiveCell.Value een numerieke berekening of functie, zoals SOM, in plaats van de InputBox functie van een invoervenster verzoek weergegeven.

Titel afbeelding Automate Reports in Excel Step 9

Voeg een regel code toe om uw interactieve spreadsheet op te slaan. Het formaat is "ActiveWorkbook.SaveAs Bestandsnaam: ="NombreDelArchivo.xls"", waar "filename" vertegenwoordigt de naam van uw interactieve spreadsheet (inclusief de citaten die er in de buurt zijn "filename", maar niet degenen die het voorbeeld volledig omringen).

Als u Excel 2007 of een latere versie hebt, kunt u het achtervoegsel vervangen ".xls" door ".xlsx", maar als een van de mensen die de spreadsheet gebruiken Excel 2003 of een eerdere versie heeft, kunnen ze de spreadsheet niet gebruiken tenzij ze een programma hebben met een plug-in om te lezen.

Titel afbeelding Automate Reports in Excel Step 10

Druk tegelijkertijd op de toetsen Alt en Q. Hierdoor wordt de Visual Basic-editor gesloten.

Titel afbeelding Automate Reports in Excel Step 11

Druk tegelijkertijd op de toetsen Alt en F8. Hierdoor wordt het dialoogvenster Macro`s geopend.

Titel afbeelding Automate Reports in Excel Step 12

Klik op de naam van uw procedure in de macro`s-lijst. Als uw procedure de enige in de lijst is, wordt deze automatisch geselecteerd.

Titel afbeelding Automate Reports in Excel Step 13

Klik op de knop Opties. Ze vragen u een toetsenbordteken in te voeren om als sneltoets te gebruiken met de Ctrl-toets. Kies een letter die representatief is en die niet is gebruikt als een snelkoppelingsteken, zoals "en" van "binnenkomst".

Titel afbeelding Automate Reports in Excel Step 14

Klik op "aanvaarden" om het dialoogvenster Macro`sopties te sluiten. Nu kunt u uw interactieve spreadsheet verspreiden onder degenen die het zullen gebruiken. Nadat u deze hebt geopend, kunt u de sneltoets gebruiken om gegevensinvoer in te schakelen en de instructies te volgen die u hebt gemaakt om deze met uw gegevens te voltooien.

Methode 2
Automatiseer het genereren van rapporten

Titel afbeelding Automate Reports in Excel Step 15

Converteer uw rapport naar een dynamische tabel. Dynamische tabellen zijn ontworpen om informatie samen te vatten, zodat u getallen kunt vergelijken en trends kunt identificeren. Uw draaitabel moet verbinding maken met gegevens die zich overal in uw spreadsheet bevinden of met die gegevens die uit een database zijn geïmporteerd.

Titel afbeelding Automate Reports in Excel Step 16

Schrijf een Visual Basic-code om het rapport te openen en te sluiten. Uw code moet de onderstaande functies uitvoeren. Elke functie zal worden beschreven, gevolgd door de code tussen haakjes die dient om het te implementeren. Wanneer u de code schrijft, schrijft u deze in een enkel blok, vervangt u de namen van het voorbeeld door uzelf en voegt u niet de haakjes toe die de voorbeeldcode omsluiten.

Open de spreadsheet in de alleen-lezen modus. [DIM XLAppSet XLApp = CreateObject ("Excel.App") xlapp.visible = falsexlapp.workbooks.open excelloc filename.xls, 3,]

Werk de gegevens bij en sla het rapport op, in dit voorbeeld als PDF met de datuminformatie. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, pdfloc reportname_ & DatePart ("jjjj, nu ()) & "-" & Rechts ("0" & DatePart ("m", Nu ()), 2) & "-" Rechts ("0" & DatePart ("d", Nu ()), 2) & ".pdf"]. Als het document dat is gemaakt, een ander formaat moet hebben, vervangt u het formaat ".pdf" door de bijbehorende extensie.

Sluit de spreadsheet zonder deze op te slaan en sluit Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]

toepassingen ".xlsx" in plaats van ".xls" in het achtervoegsel van de spreadsheet als u deze in Excel 2007 en latere indelingen op basis van XML hebt opgeslagen.

Titel afbeelding Automate Reports in Excel Step 17

Schrijf een batchcode om de Visual Basic-code te starten. Dit is nodig om de Visual Basic-code automatisch uit te voeren. Als u geen batchverwerkingscode gebruikt, moet u uw VB-code handmatig uitvoeren.

Uw code moet dit formaat hebben, de namen van de map en het bestand vervangen door uw eigen en de haakjes weglaten: [cscript / rologo fileloc script.vbs].

Titel afbeelding Automate Reports in Excel Step 18

Schrijf een batchverwerkingscode om te controleren of het uitvoerbestand bestaat zoals u het hebt gemaakt. Uw code moet de hieronder beschreven functies uitvoeren. Elke functie heeft naast de code tussen haakjes die dient om het te implementeren. Wanneer u de code schrijft, schrijft u deze in een enkel blok, vervangt u de namen van het voorbeeld door uzelf en voegt u niet de haakjes toe die de voorbeeldcode omsluiten.

Controleer of het uitvoerbestand bestaat. [Voor / f "tokens = 2-4 delims = / " %% a in (`datum / t`) stelt rreport = rapportnaam _ %% c - %% a - %% b.pdf)] in. Als het formaat van het uitvoerbestand geen PDF is, vervangt u dit indien nodig.

Als het bestand of het exit-rapport bestaat, stuur het dan per e-mail naar de mensen die het nodig hebben. [Als bestaan pdfloc % RReport% (sendemail -f -t [email protected] [email protected] -u -m Verslag gepland rapport% %% is bijgevoegd. -a pdfloc % RReport% -s yourserver: port -xu gebruikersnaam -xp wachtwoord)]

Als het bestand of het afsluitingsrapport niet op de opgegeven locatie bestaat, stuurt u de procedure een bericht met de melding dat de zending is mislukt. [Else (sendemail -f -t [email protected] [email protected] -u Report liep niet -m bestand% RReport% bestaat niet in pdfloc yourserver -s: -xu haven gebruikersnaam wachtwoord -xp) ]

Titel afbeelding Automate Reports in Excel Step 19

Controleer dat de map "bureau" bestaan op de computer. U moet het bestaan van de map controleren "bureau" voor zowel 32-bit en 64-bit systemen. Als u dit niet doet, moet u Excel en uw spreadsheet handmatig openen.

Locatie op 32-bits systemen: c: windows system32 config systemprofile

Locatie op 64-bits systemen: c: windows syswow64 config systemprofile

Titel afbeelding Automate Reports in Excel Step 20

Plan een taak om uw codes zo nodig uit te voeren. Batchverwerkingcodes moeten sequentieel en permanent worden uitgevoerd, ongeacht of iemand de computer gebruikt of niet. U moet de configuratie gebruiken die het hoogst mogelijke aantal privileges biedt.

tips

Doorgaans werken geplande taken beter vanaf een server van een systeemaccount met beheerdersbevoegdheden. Het enige nadeel bij het uitvoeren van taken van een systeemaccount is dat de gebruikersinterface niet beschikbaar is. Geautomatiseerde taken zijn echter meestal ontworpen om op een verborgen manier te worden uitgevoerd, zonder dat de gebruiker ze kan opmerken.

Delen op sociale netwerken:

Verwant