Cum se automatizează rapoartele în Excel

Una dintre multele funcții ale Microsoft Excel este capacitatea sa de a automatiza rapoartele. Puteți crea foi de lucru interactive pentru a simplifica inserarea datelor în registrele dvs. de lucru, precum și pentru a automatiza generarea de rapoarte. Ambele funcții necesită o anumită cunoaștere a Visual Basic. Pașii pentru aceste activități sunt descrise mai jos, utilizând MS Excel în limba engleză.

conținut

Pași
Metoda 1crearea unei foi de lucru interactive
Metoda 2generarea automată a rapoartelor
Sfaturi

pași

Metoda 1
Crearea unei foi de lucru interactive

Imagine intitulată Automate Reports in Excel Pasul 1

Decideți asupra aspectului foii de calcul. Foaia dvs. de calcul trebuie să respecte un stil care permite altor utilizatori să găsească rapid câmpurile dorite.

Puteți organiza foaia de calcul orizontal sau vertical. Majoritatea utilizatorilor consideră că este mai ușor să lucreze cu un aspect vertical, mai ales dacă tipăriți foaia de lucru.

Imaginea intitulată Automate Reports in Excel Pasul 2

Creați unele "etichete de text" pentru foaia de calcul. Efectuați un titlu / etichetă în partea de sus a fiecărei coloane din stânga fiecărui rând.

Imaginea intitulată Automate Reports in Excel Pasul 3

Apăsați simultan pe Alt și pe F11. Aceasta va inițializa editorul Microsoft Visual Basic.

Imaginea intitulată Automate Reports in Excel Pasul 4

Faceți dublu clic pe "ThisWorkbook" din secțiunea "Project-VBA Project" din colțul din stânga sus. Veți deschide o fereastră de codificare în partea principală a editorului.

Imaginea intitulată Automate Reports in Excel Pasul 5

Selectați "Procedură" din meniul Inserare. Se deschide caseta "Adăugați procedura".

Imaginea intitulată Automate Reports in Excel Pasul 6

Introduceți un nume pentru procedură, în câmpul "Nume". Oferiți un nume semnificativ, cum ar fi "SumExpenses", dacă foaia de lucru interactivă este utilizată, de exemplu, în raportul de cheltuieli de călătorie. Faceți clic pe OK pentru a închide caseta.

Numele procedurii nu poate conține spații, dar puteți utiliza o subliniere (_) dacă doriți.
După ce închideți caseta Adăugați o procedură, veți vedea o linie numită "Public Sub", urmată de numele procedurii dvs. Sub această linie va fi un spațiu și cuvintele "End Sub".

Imaginea intitulată Automate Reports in Excel Pasul 7

Introduceți un cod pentru fiecare câmp de inserare în foaia de lucru. Trebuie să introduceți două rânduri pentru fiecare intrare.

Prima linie de cod folosește formularul "Range (" cellname "). Selectați", unde "cellname" reprezintă celula la care vor intra datele introduse. Aceasta ar trebui să fie celula imediat din dreapta titlului. Dacă aveți titlul în A2, utilizați câmpul de introducere în B2 (Intervalul ("B2"). Selectați). Utilizați ghilimelele între numele celulei și nu întreaga formulă.
A doua linie de cod va fi „ActiveCell.Value = InputBox (“ InputPrompt „),“ în cazul în care „InputPrompt“ este textul care va apărea pentru a ghida utilizatorul cu privire la tipul de informații care trebuie introduse. De exemplu, dacă "celula de intrare" este pentru cheltuielile de masă, înlocuiți "InputPrompt" cu "Introduceți suma totală cheltuită pentru mese, inclusiv sfaturile". Utilizați ghilimelele între numele celulei și nu întreaga formulă.

Imaginea intitulată Automate Reports in Excel Pasul 8

Introduceți codul pentru fiecare câmp de calcul. Trebuie să utilizați din nou cele două linii ca deja menționat mai sus, dar acest timp, ActiveCell.Value este un calcul sau o funcție numerică ca „SUM“ ( „USM“), în locul InputCasuta folosit pentru a afișa promptul de intrare.

Imaginea intitulată Automate Reports in Excel Pasul 9

Adăugați o linie de comandă pentru a salva foaia de lucru. Formatul este "ActiveWorkbook.SaveAs Nume fișier: =" Filename.xls "," unde "Filename" reprezintă numele foii de lucru interactive. Utilizați ghilimelele între numele foii de lucru și nu întreaga formulă.

Dacă utilizați Excel 2007 sau o versiune ulterioară, puteți înlocui extensia ".xls" cu ".xlsx", dar cele cu Excel 2003 sau anterioare nu vor putea deschide foaia de lucru fără a fi instalat un plug-in.

Imaginea intitulată Automate Reports in Excel Pasul 10

Apăsați simultan pe Alt și pe Q. Aceasta va închide editorul Visual Basic.

Imaginea intitulată Automate Reports in Excel Pasul 11

Apăsați simultan pe Alt și pe F8. Aceasta va deschide caseta Macro.

Imagine intitulată Automate Reports in Excel Pasul 12

Faceți clic pe numele procedurii dvs. din lista Macrocomenzi. Dacă procedura dvs. este singura din listă, aceasta va fi selectată automat.

Imagine intitulată Automate Reports in Excel Pasul 13

Faceți clic pe butonul "Opțiuni". Vi se va solicita să definiți o cheie care va fi utilizată ca o comandă rapidă, alături de Ctrl. Utilizați ceva intuitiv și nu este folosit, de exemplu "și" pentru "intrare".

Imaginea intitulată Automate Reports in Excel Pasul 14

Faceți clic pe "OK" pentru a închide caseta Opțiuni macro. Acum puteți distribui foaia de lucru interactivă oricui o folosește. După ce îl deschideți, utilizatorii pot utiliza comanda rapidă și urmează instrucțiunile create pentru a completa datele.

Metoda 2
Generarea automată a rapoartelor

Imaginea intitulată Automate Reports in Excel Pasul 15

Asigurați-vă raportul cu un PivotTable. PivotTables sunt făcute pentru a grupa și a rezuma informații, permițând compararea datelor și identificarea tendințelor. PivotTable trebuie să fie conectat la date oriunde pe foaia de lucru sau importat dintr-o bază de date.

Imaginea intitulată Automate Reports in Excel Pasul 16

Scrieți un script Visual Basic pentru a deschide și a închide raportul. Scriptul dvs. trebuie să îndeplinească funcțiile enumerate mai jos. Fiecare funcție va fi descrisă urmată de codul introdus în paranteze, care urmează să fie implementat. Când scrieți codul în mod eficient, scrieți totul într-un singur bloc, înlocuiți numele exemplelor cu datele reale și nu includeți parantezele pătrate care închid întregul exemplu.

Deschideți foaia de lucru în modul numai pentru citire. [DIM XLAppSet xlApp = CreateObject ( "Excel.App") xlApp.Visible = falsexlapp.workbooks.open excelloc numefișier.xls, 3]
Actualizați datele și salvați raportul. În acest exemplu, a fost salvat ca un PDF, cu o ștampilă de dată. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, pdfloc reportname_ DatePart ("aaaa, Acum ()) ";" Dreapta ("0" DatePart ("m", Acum ()), 2) ";" Dreapta ("0" DatePart ("d", Acum ()), 2) ".pdf"] Dacă fișierul dvs. final este într-un format diferit, înlocuiți extensia .pdf cu cea dorită.
Închideți foaia de lucru fără să o salvați și apoi închideți Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
Utilizați ".xlsx" în loc de ".xls" dacă foaia de calcul este salvată în Excel 2007 sau într-un alt format mai recent bazat pe XML.

Imaginea intitulată Automate Reports in Excel Pasul 17

Tastați un "script batch" pentru a porni scriptul Visual Basic. Acest lucru este necesar pentru ca scriptul să ruleze automat. Fără scriptul de lot, scriptul VB ar trebui să fie executat manual.

Dacă scriptul dvs. este în acest format, înlocuiți căile de dosar și numele fișierului cu dvs. și eliminați "[]". [cscript / Nologo fileloc script.vbs]

Imaginea intitulată Automate Reports in Excel Pasul 18

Scrieți un script batch pentru a verifica fișierul de ieșire creat. Scriptul dvs. trebuie să îndeplinească funcțiile enumerate mai jos. Fiecare funcție va fi urmată de codul introdus în paranteze, care urmează să fie implementat. Când scrieți codul în mod eficient, scrieți totul într-un singur bloc, înlocuiți numele exemplelor cu datele reale și nu includeți parantezele pătrate care închid întregul exemplu.

Verificați dacă fișierul de ieșire există. [For / f "token-uri = 2-4 delims = /" %% a în (`data / t`) din setul rreport = raport nume _ %% c - %% a - %% b.pdf)]. Dacă fișierul de ieșire nu este un PDF, înlocuiți extensia.
Dacă fișierul de ieșire / există Nelatório, trimite-l prin e-mail la persoanele care au nevoie de ea. [Dacă există pdfloc %% rreport (sendemail [email protected] -f -t [email protected] -u -m este atașat Raport Raport Raport Programat %%%. -a pdfloc %% -s rreport yourserver: port -xu username -xp parola)]
În cazul în care raportul nu există sau nu a fost găsit locația specifică, determină un mesaj de eroare să fie trimis: [Else (sendemail -f -t [email protected] [email protected] -u raportul nu a rula fișierul -m % rreport% nu există în pdfloc -s yourserver: port -xu username -xp password]]

Imagine intitulată Automate Reports in Excel Pasul 19

Asigurați-vă că dosarul "Desktop" există pe computer. Ar trebui să faceți acest lucru atât pe sistemele pe 32 de biți, cât și pe cele pe 64 de biți. Dacă nu faceți acest lucru, Excel și dosarul său ar trebui să fie deschise manual.

32-biți de sistem de locație: c: windows system32 config systemprofile
64-biți de sistem de locație: c: windows syswow64 config systemprofile

Imaginea intitulată Automate Reports in Excel Pasul 20

Programați sarcina de a rula scripturile atunci când este necesar. Loturile scripturi trebuie să fie executate în ordine, în mod continuu, indiferent dacă cineva utilizează computerul sau nu. "Privilegiile" ar trebui stabilite în cea mai înaltă poziție posibilă.

sfaturi

Sarcinile programate sunt cel mai bine executate de la un server, de obicei dintr-un sistem cu privilegii de administrator. Singura problemă de a rula sarcinile dintr-un cont de sistem este că interfața cu utilizatorul nu va fi disponibilă - cu toate acestea, aceste activități automatizate sunt de obicei idealizate pentru a rula în fundal fără ca utilizatorul să le observe.

Distribuiți pe rețelele sociale:

înrudit