Microsoft Excel har forskjellige funksjoner, og en av dem genererer rapporter automatisk. Du kan lage interaktive regneark for å forenkle prosessen med å legge inn data for andre i arbeidsboken, samtidig som du automatiserer rapportgenerering. Begge disse funksjonene krever tilstrekkelig kunnskap om Visual Basic. Trinnene for å utføre begge oppgavene er beskrevet nedenfor.
Steg
Metode 1 av 2: Opprette et interaktivt regneark
Trinn 1. Bestem oppsettet til regnearket
Oppsettet av regnearket må gjøres slik at andre kan finne feltene som trengs for å legge inn data.
Regnearkoppsett kan legges horisontalt eller vertikalt. De fleste brukere synes det er lettere å jobbe med et vertikalt oppsett, spesielt hvis regnearket skrives ut
Trinn 2. Lag tekstetiketter for regnearket
Skriv en etikett øverst i hver kolonne, og til venstre for hver celle i kolonnen du planlegger å bruke som dataoppføring.
Trinn 3. Trykk alt="Bilde" og F11 -tastene sammen
Denne tastekombinasjonen åpner Microsoft Visual Basic -editoren.
Trinn 4. Dobbeltklikk på "Denne arbeidsboken" i "Project-VBA Project" -ruten øverst til venstre
Et vindu for å skrive kode vil vises i hoveddelen av redaktøren.
Trinn 5. Velg "Prosedyre" fra Sett inn -menyen
Dialogboksen Legg til prosedyre vises.
Trinn 6. Skriv inn navnet på prosedyren i feltet Navn
Skriv inn et meningsfylt navn for prosedyren, for eksempel "SumExpenses" hvis regnearket skal brukes til å rapportere reiseutgifter. Klikk OK for å lukke dialogboksen.
- Prosedyre navn kan ikke inneholde mellomrom, men kan bruke en understreking (_) for å erstatte mellomrom.
- Etter at dialogboksen Legg til prosedyre lukkes, vises en linje merket "Offentlig under" etterfulgt av navnet på prosedyren. Under den linjen er det et mellomrom og ordene "End Sub."
Trinn 7. Skriv inn koden for hvert inndatafelt i regnearket
Du skriver to kodelinjer for hver oppføring.
- Den første linjen med kode er i form av "Range (" cellname "). Velg", "cellname" representerer cellen der input er angitt. Fyll ut navnet på cellen som er til høyre for tekstetiketten. Hvis tekstetiketten er i celle A2, er feltet for inndata celle B2 (område ("B2"). Velg). Anførselstegnene før og etter cellenavnet er fremdeles skrevet på dette stadiet, men er ikke lenger inkludert i den komplette kodesetningen.
- Koden på den andre linjen er "ActiveCell. Value = InputBox (" InputPrompt ")". "InputPrompt" representerer teksten som vil vises for å varsle brukeren om typen data som skal legges inn i inndatacellen. For eksempel, hvis inndatacellen blir fylt med matutgifter, erstatter "InputPrompt" med "Angi totale utgifter til måltider, inkludert tips." (Anførselstegnene for inputinstruksjonsteksten er fortsatt inkludert, mens anførselstegnene før og etter kommandoen ikke trenger å skrives.)
Trinn 8. Skriv inn koden for hvert beregningsfelt
Igjen er de to linjene som er de samme som før, men denne gangen er ActiveCell. Value en beregning av en numerisk funksjon, for eksempel SUM, der funksjonen InputBox -funksjonen brukes til å vise inndatainstruksjoner.
Trinn 9. Legg til en kodelinje for å lagre det interaktive regnearket
Formatet er "ActiveWorkbook. SaveAs Filnavn: =" Filnavn.xls "." Filnavn "er navnet på det interaktive regnearket. (Anførselstegn før og etter" Filnavn.xls "forblir skrevet, mens anførselstegnene for alle kommandoer er ikke obligatorisk.)
Hvis du bruker Microsoft Excel versjon 2007 eller nyere, bruker du ".xlsx" i stedet for ".xls". Hvis det imidlertid er interaktive regnearkbrukere med Excel 2003-versjon og nyere, vil de ikke kunne bruke regnearket uten en leser-plug-in
Trinn 10. Trykk alt="Bilde" og Q -tastene samtidig
Visual Basic -redaktøren lukkes.
Trinn 11. Trykk alt="Bilde" og F8 -tastene samtidig
Makro -dialogboksen vises.
Trinn 12. Klikk på prosedyrenavnet i Makro -listen
Hvis prosedyren som er opprettet er den eneste i listen, vil den bli valgt automatisk.
Trinn 13. Klikk på Alternativer -knappen
Du blir bedt om å skrive inn et tastaturtegn som skal brukes som snarvei med Ctrl -tasten. Velg en meningsfull bokstav som ikke har blitt brukt som snarvei, for eksempel "e" for "oppføring".
Trinn 14. Klikk "OK" for å lukke dialogboksen Makroalternativer
Nå kan du distribuere interaktive regneark til brukerne. Etter å ha åpnet den, kan brukeren bruke hurtigtastene til å legge inn oppføringer og følge instruksjonene du opprettet for å fylle ut dataene.
Metode 2 av 2: Automatiser rapportgenerering
Trinn 1. Opprett en rapport i en pivottabell
Pivottabeller er designet for å oppsummere data slik at du kan sammenligne tall og identifisere trender. Pivottabellen må forholde seg til data som er i en av databehandlerne eller importert fra en bestemt database.
Trinn 2. Skriv et Visual Basic -skript for å åpne og lukke rapporten
Skriptet skal kunne utføre de forskjellige funksjonene som er oppført nedenfor. Hver funksjon vil bli beskrevet etterfulgt av koden gitt i parentes for å implementere den. Når du skriver faktisk kode, skriver du den i en enkelt blokk, erstatter navnet i eksemplet med din egen, og ikke inkluderer parenteser i begynnelsen og slutten av kodeeksemplet.
- Åpne regnearket i skrivebeskyttet modus. [DIM XLAppSet XLApp = CreateObject ("Excel. App") xlapp.visible = falsexlapp.workbooks.open / excelloc / filename.xls, 3,]
- Last inn dataene på nytt og lagre rapporten. I dette eksemplet lagres de som en PDF med datotekst. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, / pdfloc / reportname_ & DatePart ("yyyy, Now ()) &"-"& Right (" 0 "& DatePart (" m ", Now ()), 2) & "-" Right ("0" & DatePart ("d", Now ()), 2) & ".pdf"] Hvis utdataformatet er et annet, erstatt ".pdf" -formatet med riktig forlengelse etter ønske.
- Lukk regnearket uten å lagre det, og lukk deretter Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Bruk ".xlsx" i stedet for ".xls" på slutten av regnearket hvis regnearket er lagret i Excel 2007 og et senere XML-basert format.
Trinn 3. Skriv et batchskript for å starte Visual Basic -skriptet
Hensikten med å skrive skriptet er slik at Visual Basic -skript kan kjøres automatisk. Uten batchskript må VB -skript kjøres manuelt.
Skriptet er skrevet i følgende format, og erstattet med ditt eget mappenavn og filnavn for navnet i dette eksemplet: [cscript /nologo / fileloc / script.vbs]
Trinn 4. Skriv et batch -skript for å sikre at utdatafilen eksisterer som den er opprettet
Skriptet ditt skal utføre funksjonene nedenfor. Hver funksjon vil bli fulgt av koden gitt i parentes for å implementere den. Når du skriver faktisk kode, skriver du den i en enkelt blokk, og erstatter navnet i eksemplet med navnet du trenger, og ikke ta med parenteser i begynnelsen og slutten av prøvekoden.
- Sjekk om det er en utdatafil. [For /f "tokens = 2-4 delims = /" %% a in ('date /t') do set rreport = reportname _ %% c-%% a-%% b.pdf)] If the output file format er ikke PDF, erstatt ".pdf" med den riktige utvidelsen.
- Hvis utdatafilen/rapporten finnes, send den som en e -post til personen som trenger den. [Hvis det finnes / pdfloc \%rreport%(sendmail -f [email protected] -t [email protected] -u Planlagt rapport -m Rapport %% rapport%er vedlagt. -A / pdfloc \%rreport%-s din server: port -xu brukernavn -xp passord)]
- Hvis utdatafilen/rapporten ikke finnes på det angitte stedet, må du opprette en prosedyre slik at den kan sende deg en melding om at leveringen mislyktes. [Ellers (sendmail -f [email protected] -t [email protected] -u Rapporten kjørte ikke -m fil % rreport % finnes ikke i / pdfloc / -s din server: port -xu brukernavn -xp passord)]
Trinn 5. Kontroller at "Desktop" -mappen er på datamaskinen
Du må bekrefte eksistensen av Desktop-mappen for både 32-biters og 64-biters systemer. Ellers må Excel og regnearket åpnes manuelt.
- 32-biters systemplassering: c: / windows / system32 / config / systemprofil
- 64-biters systemplassering: c: / windows / syswow64 / config / systemprofil
Trinn 6. Planlegg oppgaver for å kjøre skript etter behov
Batchskript må kjøres sekvensielt og kontinuerlig, selv om ingen bruker datamaskinen. Privilegier må settes til høyest mulig innstilling.
