כיצד להפוך אוטומטי דוחות ב- Excel (עם תמונות)

2024 מְחַבֵּר: Jason Gerald | [email protected]. שונה לאחרונה: 2024-01-19 22:12

ל- Microsoft Excel תכונות שונות ואחת מהן מייצרת דוחות באופן אוטומטי. אתה יכול ליצור גיליונות אלקטרוניים אינטראקטיביים כדי לפשט את תהליך הזנת הנתונים עבור אחרים בחוברת העבודה, תוך הפקת דוחות אוטומטיים. שתי התכונות הללו דורשות ידע מספיק ב- Visual Basic. השלבים לביצוע שתי המשימות מתוארים להלן.

שלב

שיטה 1 מתוך 2: יצירת גיליון אלקטרוני אינטראקטיבי

שלב 1. קבע את פריסת הגיליון האלקטרוני

פריסת הגיליון האלקטרוני חייבת להיעשות כך שאחרים יוכלו למצוא את השדות הדרושים להכנסת נתונים.

ניתן לפרוס פריסות גיליון אלקטרוני אופקית או אנכית. לרוב המשתמשים קל יותר לעבוד עם פריסה אנכית, במיוחד אם הגיליון האלקטרוני יודפס

שלב 2. צור תוויות טקסט עבור הגיליון האלקטרוני

כתוב תווית בראש כל עמודה, ומשמאל לכל תא בעמודה שבה אתה מתכוון להשתמש כערך נתונים.

שלב 3. הקש alt="Image" ו- F11 מקשים יחד

שילוב מקשים זה יפתח את עורך Microsoft Visual Basic.

שלב 4. לחץ פעמיים על "חוברת עבודה זו" בחלונית "פרוייקט- VBA פרוייקט" בפינה הימנית העליונה

חלון לכתיבת קוד יופיע בחלק הראשי של העורך.

שלב 5. בחר "נוהל" מתפריט הוספה

תיבת הדו -שיח הוספת נוהל תופיע.

שלב 6. הזן את שם ההליך בשדה שם

הזן שם משמעותי להליך, כגון "סכום הוצאות" אם הגיליון האלקטרוני ישמש לדיווח על הוצאות נסיעה. לחץ על אישור כדי לסגור את תיבת הדו -שיח.

שמות ההליכים אינם יכולים להכיל רווחים, אך הם יכולים להשתמש בקו תחתון (_) כדי להחליף רווחים.
לאחר סגירת תיבת הדו -שיח הוספת נוהל, תופיע שורה שכותרתה "תת ציבורי" ואחריה שם ההליך. מתחת לקו זה נמצא רווח והמילים "קצה תת".

שלב 7. הזן את הקוד עבור כל שדה קלט בגיליון האלקטרוני

תכתוב שתי שורות קוד לכל ערך.

שורת הקוד הראשונה היא בצורה של "טווח (" שם התא "). בחר", "שם התא" מייצג את התא שבו הזנת הקלט. מלא את שם התא הנמצא מימין לתווית הטקסט. אם תווית הטקסט נמצאת בתא A2, השדה עבור הקלט הוא תא B2 (טווח ("B2"). בחר). המרכאות לפני ואחרי שם התא עדיין נכתבות בשלב זה, אך אינן כלולות עוד בהצהרת הקוד המלאה.
הקוד בשורה השנייה הוא "ActiveCell. Value = InputBox (" InputPrompt ")". "InputPrompt" מייצג את הטקסט שיופיע כדי להודיע למשתמש על סוג הנתונים שייכנס לתא הקלט. לדוגמה, אם תא הקלט יתמלא בהוצאות מזון, החלף את "InputPrompt" ב- "הזן את סך ההוצאות לארוחות, כולל טיפים". (מרכאות הטקסט להוראת הקלט עדיין כלולות, בעוד שאין צורך לכתוב את מרכאות לפני הפקודה ואחריה.)

שלב 8. הזן את הקוד עבור כל שדה חישוב

שוב שתי השורות שהיו בשימוש זהות לקודם, אך הפעם ActiveCell. Value הוא חישוב של פונקציה מספרית, למשל SUM, כאשר הפונקציה InputBox משמשת להצגת הוראות קלט.

שלב 9. הוסף שורת קוד לשמירת הגיליון האלקטרוני האינטראקטיבי שלך

הפורמט הוא "ActiveWorkbook. SaveAs שם קובץ: =" שם שם.קסלס "." שם קובץ "הוא שם הגיליון האלקטרוני האינטראקטיבי. (מרכאות לפני ואחרי" Filename.xls "נותרו כתובים, בעוד שהמרכאות לכל הפקודות הן לא דרוש.)

אם אתה משתמש ב- Microsoft Excel גירסה 2007 ואילך, השתמש ב- ".xlsx" במקום ".xls". עם זאת, אם יש משתמשי גיליון אלקטרוני עם גרסת Excel 2003 ומטה, הם לא יוכלו להשתמש בגיליון האלקטרוני ללא תוסף קורא

שלב 10. הקש alt="Image" ו- Q במקביל

עורך Visual Basic ייסגר.

שלב 11. הקש בו זמנית על מקשי alt="תמונה" ו- F8

תיבת הדו -שיח מאקרו תופיע.

שלב 12. לחץ על שם ההליך ברשימת המאקרו

אם ההליך שנוצר הוא היחיד ברשימה, הוא ייבחר באופן אוטומטי.

שלב 13. לחץ על הלחצן אפשרויות

תתבקש להזין תו מקלדת שישמש כקיצור דרך באמצעות מקש Ctrl. בחר אות משמעותית שלא שימשה כדמות קיצור, כגון "e" עבור "ערך".

שלב 14. לחץ על "אישור" כדי לסגור את תיבת הדו -שיח אפשרויות מאקרו

כעת תוכל להפיץ גיליונות אלקטרוניים אינטראקטיביים למשתמשים. לאחר פתיחתו, המשתמש יכול להשתמש במקשי הקיצור כדי לערוך ערכים ולפעול לפי ההנחיות שיצרת כדי למלא את הנתונים.

שיטה 2 מתוך 2: הפקת דוחות אוטומטית

שלב 1. צור דוח ב- PivotTable

PivotTables נועדו לסכם נתונים כך שתוכל להשוות מספרים ולזהות מגמות. PivotTable חייב להתייחס לנתונים שנמצאים באחד ממעבדי הנתונים או מיובאים ממאגר נתונים ספציפי.

שלב 2. כתוב סקריפט Visual Basic לפתיחה וסגירה של הדוח

התסריט אמור להיות מסוגל לבצע את הפונקציות השונות המפורטות להלן. כל פונקציה תתואר ואחריה הקוד שניתן בסוגריים לצורך יישומה. בעת כתיבת קוד בפועל, כתוב אותו בבלוק יחיד, החלף את השם בדוגמה בשלך, ואל תכלול סוגריים בתחילת ובסוף מדגם הקוד.

פתח את הגיליון האלקטרוני במצב קריאה בלבד. [DIM XLAppSet XLApp = CreateObject ("Excel. App") xlapp.visible = falsexlapp.workbooks.open / excelloc / filename.xls, 3,]
טען מחדש את הנתונים ושמור את הדוח, בדוגמה זו הוא נשמר כקובץ PDF עם כיתוב התאריך. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, / pdfloc / reportname_ & DatePart ("yyyy, Now ()) &"-"& Right (" 0 "& DatePart (" m ", Now ()), 2) & "-" Right ("0" & DatePart ("d", Now ()), 2) & ".pdf"] אם פורמט מסמך הפלט שונה, החלף את פורמט ".pdf" ב את הסיומת הנכונה כרצונך.
סגור את הגיליון האלקטרוני מבלי לשמור אותו ולאחר מכן סגור את Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
השתמש ".xlsx" במקום ".xls" בסוף הגיליון האלקטרוני אם הגיליון האלקטרוני נשמר ב- Excel 2007 ובתבנית מבוססת XML מאוחרת יותר.

שלב 3. כתוב סקריפט אצווה להפעלת הסקריפט Visual Basic

מטרת כתיבת התסריט היא כך שסקריפטים של Visual Basic יוכלו לפעול באופן אוטומטי. ללא סקריפט אצווה, יש להפעיל סקריפטים של VB באופן ידני.

התסריט כתוב בפורמט הבא, ומוחלף בשם התיקיה שלך ושם הקובץ לשם בשם בדוגמה זו: [cscript /nologo / fileloc / script.vbs]

שלב 4. כתוב סקריפט אצווה כדי לוודא שקובץ הפלט קיים כפי שנוצר

התסריט שלך צריך לבצע את הפונקציות שלהלן. אחרי כל פונקציה יופיע הקוד שניתן בסוגריים לצורך יישומה. בעת כתיבת קוד בפועל, כתוב אותו בבלוק יחיד, החלף את השם בדוגמה בשם הדרוש לך, ואל תכלול סוגריים בתחילת ובקוד הקוד לדוגמה.

בדוק אם יש קובץ פלט. [For /f "tokens = 2-4 delims = /" %% a in ('date /t') do set rreport = reportname _ %% c-%% a-%% b.pdf)] אם תבנית קובץ הפלט אינו PDF, החלף את ".pdf" בסיומת המתאימה.
אם קיים קובץ הפלט/הדוח, שלח אותו כמייל לאדם הזקוק לו. [אם קיים / pdfloc \%rreport%(sendmail -f [email protected] -t [email protected] -u דוח מתוזמן -m דוח %% דו"ח%מצורף. -א / pdfloc \%rreport%-s שרת שלך: port -xu שם משתמש -סיסמה xp)]
אם קובץ הפלט/הדוח אינו קיים במיקום שצוין, צור הליך כך שיוכל לשלוח לך הודעה שהמסירה נכשלה. [אחרת (sendmail -f [email protected] -t [email protected] -u הדוח לא הופעל -m הקובץ % rreport % אינו קיים ב- / pdfloc / -s שרת שלך: port -xu שם משתמש -סיסמא xp)]