Hur man automatiserar Excel-rapporter

En av de viktigaste funktionerna i Microsoft Excel är möjligheten att automatisera rapporter. Du kan skapa interaktiva kalkylblad så att det blir lättare att skriva in data i tabeller, och du kan även automatisera rapportering. Båda dessa funktioner kräver viss kunskap om Visual Basic. Fortsätt läsa för att få reda på hur man gör båda.

Metod 1

Skapa ett interaktivt beräkningsblad

Bestäm om layouten i kalkylbladet. Kalkylbladet ska struktureras så att alla som skriver in data kan enkelt hitta och använda de obligatoriska fälten.

Du kan rikta kalkylbladet horisontellt eller vertikalt. De flesta användare föredrar att arbeta med ett vertikalt orienterat ark, speciellt om de behöver skriva ut det.

Skapa etiketter. Du behöver en etikett på varje kolumn och en etikett i cellerna till vänster om varje cell i kolumnen där du måste placera fälten för intäkterna.

Tryck på Alt och F11 samtidigt. På så sätt öppnas Microsoft Visual Basic-redigeraren.

Dubbelklicka på "This WorkBook" i panelen "Project - VBA Project" längst upp till vänster. Ett fönster öppnas i huvuddelen av redigeraren där vi kan ange vår kod.

Välj "Procedure" från Insert-menyn. På så sätt visas fönstret Lägg till procedur.

I fältet Namn anger du ett namn för proceduren. Välj ett lämpligt namn, till exempel "SumSales", om ditt kalkylblad ska användas som kostnadsrapport. Klicka på OK för att stänga fönstret.

Namnet på proceduren kan inte innehålla mellanslag, men du kan använda understreck (_) istället för mellanslag.

När fönstret Lägg till procedur är stängd kommer du att se en rad som heter "Public Sub" följt av namnet på proceduren. Under raden kommer det att finnas ett mellanslag och orden "End Sub".

Ange koden för varje inmatningsfält i kalkylbladet. Du måste skriva två koder för varje ingång.

Den första raden av kod kommer att vara "Range (" nomename "). Välj", var "nomename" representerar cellen där vi ska infoga data. Detta bör vara cellen som ligger omedelbart till höger om etiketten - om du har en etikett på cell A2, i cell B2 kommer du att sätta in (Område ("B2") .Select). Inkludera citattecken runt "namnnamn", men inte de som bifogar koden i exemplet.

Den andra raden av kod måste skrivas enligt följande: "ActiveCell.Value = InputBox ("InputPrompt")", där "InputPrompt" representerar texten som visas för att fråga användaren vilken typ av data som ska infogas i inmatningscellen. Om exempelvis inmatningscellen är avsedd för att ange måltidskostnader, kommer du att ersätta "InputPrompt" med "Ange totala måltider, inklusive tips". (Inkludera citattecken runt textmeddelanden, men inte de som bifogar koden.)

Ange koden för varje beräkningsfält. Igen, med användning av samma två linjen som beskrivits ovan, men denna gång ange ActiveCell.Value beräkning, numerisk eller funktion, såsom SUM, i stället för den InputBox funktion som används för att visa en begäran om inmatning.

Lägg till en rad kod för att spara ditt interaktiva kalkylblad. Skriv koden enligt följande: "ActiveWorkbook.SaveAs Filnamn: ="Filename.xls"," där "filnamn" är namnet på din interaktiva kalkylblad (Inkludera citattecken runt "Filename.xls", men inte de som omsluter hela koden).

I Excel 2007 eller senare kan du ersätta suffixet ".xlsx" med ".xls," men om några av de interaktiva arkanvändarna använder Excel 2003 eller tidigare, kommer de inte att kunna använda det interaktiva arket utan ett ytterligare program som behövs för att visa denna typ av format på äldre versioner av Excel.

Tryck samtidigt på Alt och Q-tangenterna. Detta stänger Visual Basic Editor-fönstret.

Tryck samtidigt på Alt och F8-tangenterna. Detta öppnar makrofönstret.

Klicka på namnet på proceduren i makrolistan. Om din är den enda proceduren på listan, kommer den att väljas automatiskt.

Klicka på alternativknappen. Du kommer att bli ombedd att ange ett tecken som ska användas som en tangentbordsgenväg tillsammans med Ctrl-tangenten. Välj ett lämpligt brev som inte redan har använts för andra genvägar, t.ex. "e", för "post".

Klicka på "OK" för att stänga fönstret Makroalternativ. Nu kan du distribuera ditt interaktiva kalkylblad till andra användare. När du har öppnat det kommer de att kunna använda tangentbordets genväg för att aktivera intäkterna och följa anvisningarna du har skapat och fylla i de obligatoriska fälten

Metod 2

Automatisera rapportgenerering

Vrid rapporten till ett pivottabell. Pivottabeller är utformade för att sammanfatta data så att de kan jämföras och identifiera och analysera trender. Pivottabeller ska ansluta data i kalkylbladet eller importeras från en databas.

Skriv ett Visual Basic-skript för att öppna och stänga rapporten. Ditt skript måste utföra funktionerna nedan. Varje funktion kommer att beskrivas och följas i parentes med koden som ska implementeras. När du skriver koden skriver du den i ett enda block, ersätter exempelnamnen med dina namn och utan att inkludera parenteserna som bifogar koden i exemplen.

Öppna kalkylbladet i skrivskyddat läge. [DIM XLAppSet XLApp = CreateObject ("Excel.App") xlapp.visible = falsexlapp.workbooks.open excelloc nomefile.xls, 3,]

Uppdatera data och spara rapporten, i det här exemplet en PDF-fil med datumstämpel. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, pdfloc reportname_ & DatePart ("dd, Nu ()) & "-" & höger ("0" & DatePart ("m", Nu ()), 2) & "-" höger ("0" & DatePart ("d", Nu ()), 2) & ".pdf"] Om du vill att dokumentet ska sparas i ett annat format, byt ut förlängningen med önskat format.

Stäng kalkylbladet utan att spara det och stäng sedan Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]

Använd "xlsx" istället för "xls" i kalkylbladets förlängning om din beräkningssida har sparats i Excel 2007 och efterföljande XML-baserade format.

Skriv ett batchskript för att starta det visuella grundskriptet. Detta är nödvändigt eftersom Visual Basic-skriptet kan startas automatiskt. Utan satsvis script måste Visual Basic-skriptet startas manuellt.

Ditt skript kommer att vara i det här formatet, ersätta filvägen med den verkliga sökvägen och utelämna parenteserna: [cscript / rologue fileloc script.vbs]

Skriv ett batchskript för att verifiera att outpout-filen skapades. Skriptet måste utföra funktionerna nedan. Varje funktion kommer att beskrivas och följas i parentes med koden som ska implementeras. När du skriver koden skriver du den i ett enda block, ersätter exempelnamnen med dina namn och utan att inkludera parenteserna som bifogar koden i exemplen.

Kontrollera att utdatafilen har skapats. [För / f "tokens = 2-4 delims = / " %% en i (datum / t) = göra rreport September nomerapporto _ %% c - %% a - %% b.pdf)] om storleken på ouput filen inte PDF, byt PDF med rätt format.

Om utskrifts- / rapportfilen skapades, skicka den till användarna via e-post. [Om existerar pdfloc % rreport% (sendemail -f -t [email protected] [email protected] -u -m rapporter Schemalagda rapporter %%% rapporten bifogas. -a pdfloc % rreport% -s yourserver: port -xu användarnamn -xp lösenord)]

Om filen för utdata / rapport inte existerar på den angivna filvägen, utför ett förfarande för att skicka ett meddelande utan leverans. [Else (sendemail [email protected] -f -t -u [email protected] rapport inte köra -M% rreport% existerar inte i pdfloc -s your: port -xu -XP användarnamn lösenord) ]

Verifiera att skrivbordsmappen finns på din dator. Det är nödvändigt att verifiera förekomsten av skrivbordsmappen för både 32-bitars och 64-bitars system, annars måste Excel-kalkylbladet öppnas manuellt.

32-bitars filväg: c: windows system32 config systemprofile

64-bitars filväg: c: windows syswow64 config systemprofile

Skapa en schemalagd uppgift för att starta skripten efter behov. Batchskript ska utföras i följd och kontinuerligt, oavsett användarstatus för datorn. Dessutom ska användaren som kör skriptet ha högsta möjliga behörighet.

tips

Planerad verksamhet är effektivare när den utförs på servrar, vanligtvis från ett systemkonto med administrativa behörigheter. Den enda nackdelen med att utföra schemalagd serveroperation är att det inte är möjligt att använda ett användargränssnitt - i alla fall kör de schemalagda operationerna i bakgrunden utan att användaren märker.

Visa mer ... (1)

Dela på sociala nätverk:

Relaterade

Hur man automatiserar Excel-rapporter

steg

Metod 1

Metod 2

tips