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

• מה זה מאקרו?
• יצירת מאקרו - דוגמה מעשית
• הפעלת מאקרו באקסל
• בואו נסתכל מתחת למכסה המנוע: איך עובד מאקרו?
• בואו נוסיף עוד שלב למשימה שלנו...

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

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

מה זה מאקרו?

מאקרו ב-Microsoft Office (כן, פונקציונליות זו פועלת אותו הדבר ביישומים רבים של חבילת Microsoft Office) הוא קוד תוכנית בשפת תכנות Visual Basic ליישומים (VBA) מאוחסן בתוך המסמך. כדי להבהיר יותר, ניתן להשוות מסמך של Microsoft Office לדף HTML, ואז מאקרו הוא אנלוגי של Javascript. מה ש-Javascript יכול לעשות עם נתוני HTML בדף אינטרנט דומה מאוד למה שמאקרו יכול לעשות עם נתונים במסמך של Microsoft Office.

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

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

יצירת מאקרו - דוגמה מעשית

לדוגמה, ניקח את הקובץ הנפוץ ביותר CSV. זוהי טבלה פשוטה של ​​10×20 המלאה במספרים מ-0 עד 100 עם כותרות עבור עמודות ושורות. המשימה שלנו היא להפוך את מערך הנתונים הזה לטבלה בפורמט ייצוגי וליצור סכומים בכל שורה.

כפי שכבר ציינו, מאקרו הוא קוד שנכתב בשפת התכנות VBA. אבל באקסל, אתה יכול ליצור תוכנית בלי לכתוב שורת קוד, מה שנעשה עכשיו.

כדי ליצור מאקרו, פתח לצפיה (הקלד) > מאקרו (מאקרו) > הקלט מאקרו (הקלטת מאקרו...)

תן למאקרו שלך שם (ללא רווחים) ולחץ OK.

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

Excel מאותת שמצב הקלטת המאקרו מופעל בשני מקומות. ראשית, בתפריט מאקרו (מאקרו) - במקום מחרוזת הקלט מאקרו (הקלטת מאקרו...) הופיעה הפסק להקליט (הפסק להקליט).

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

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

לאחר מכן, הזינו את הנוסחאות בתאים בהתאם לשמות הכותרות (ניתן גרסאות של הנוסחאות עבור האנגלית והגרסאות של Excel, כתובות התא הן תמיד אותיות ומספרים לטיניים):

• =SUM(B2:K2)‎ or =SUM(B2:K2)‎
• =AVERAGE(B2:K2) or =СРЗНАЧ(B2:K2)
• =MIN(B2:K2) or =MIN(B2:K2)
• =MAX(B2:K2) or =MAX(B2:K2)
• =MEDIAN(B2:K2)‎ or =MEDIAN(B2:K2)‎

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

לאחר השלמת שלב זה, כל שורה אמורה לכלול את הסכומים המתאימים.

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

בהתאמה:

• =SUM(L2:L21) or =SUM(L2:L21)
• =AVERAGE(B2:K21) or =СРЗНАЧ(B2:K21) - כדי לחשב ערך זה, יש צורך לקחת בדיוק את הנתונים הראשוניים של הטבלה. אם תיקח את הממוצע של הממוצעים עבור שורות בודדות, התוצאה תהיה שונה.
• =MIN(N2:N21) or =MIN(N2:N21)
• =MAX(O2:O21) or =MAX(O2:O21)
• =MEDIAN(B2:K21)‎ or =MEDIAN(B2:K21)‎ – אנו שוקלים להשתמש בנתונים הראשוניים של הטבלה, מהסיבה שצוינה לעיל.

כעת, לאחר שסיימנו עם החישובים, בואו נעשה קצת עיצוב. ראשית, בואו נגדיר את אותו פורמט תצוגת נתונים עבור כל התאים. בחר את כל התאים בגיליון, כדי לעשות זאת, השתמש בקיצור המקלדת Ctrl + Aאו לחץ על הסמל בחר הכל, שנמצא במפגש בין כותרות השורה והעמודות. לאחר מכן לחץ סגנון פסיק הכרטיסייה (פורמט מופרד). עמוד הבית (בית).

לאחר מכן, שנה את המראה של כותרות העמודות והשורות:

• סגנון גופן מודגש.
• יישור למרכז.
• מילוי צבע.

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

כך זה אמור להיראות בסופו של דבר:

אם הכל מתאים לך, הפסק להקליט את המאקרו.

מזל טוב! זה עתה הקלטת את המאקרו הראשון שלך באקסל בעצמך.

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

כדי לנקות את כל התאים מנתונים, לחץ לחיצה ימנית על הסמל בחר הכל, שנמצא בצומת של כותרות השורה והעמודות, ומתפריט ההקשר, בחר מחק (לִמְחוֹק).

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

נקודה חשובה! אם תשמרו את הקובץ עם הסיומת XLTX, אז המאקרו לא יעבוד בו. אגב, אתה יכול לשמור את חוברת העבודה כתבנית Excel 97-2003, שיש לה את הפורמט XLT, הוא תומך גם בפקודות מאקרו.

כאשר התבנית נשמרת, אתה יכול לסגור את Excel בבטחה.

הפעלת מאקרו באקסל

לפני שחושפים את כל האפשרויות של המאקרו שיצרת, אני חושב שנכון לשים לב לכמה נקודות חשובות לגבי פקודות מאקרו באופן כללי:

• מאקרו יכול להזיק.
• קרא שוב את הפסקה הקודמת.

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

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

השלב הבא הוא לייבא את מערך הנתונים המעודכן האחרון מהקובץ CSV (על סמך קובץ כזה, יצרנו את המאקרו שלנו).

כאשר אתה מייבא נתונים מקובץ CSV, Excel עשוי לבקש ממך להגדיר הגדרות מסוימות כדי להעביר נכון את הנתונים לטבלה.

בסיום הייבוא, עבור לתפריט מאקרו כרטיסיית (פקודות מאקרו). לצפיה (הצג) ובחר פקודה הצג פקודות מאקרו (מאקרו).

בתיבת הדו-שיח שנפתחת נראה שורה עם שם המאקרו שלנו FormatData. בחר אותו ולחץ הפעלה (לבצע).

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

בואו נסתכל מתחת למכסה המנוע: איך עובד מאקרו?

כאמור יותר מפעם אחת, מאקרו הוא קוד תוכנה בשפת תכנות. Visual Basic ליישומים (VBA). כאשר אתה מפעיל את מצב הקלטת המאקרו, Excel מתעד למעשה כל פעולה שאתה מבצע בצורה של הוראות VBA. במילים פשוטות, Excel כותב את הקוד עבורך.

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

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

בואו נוסיף עוד שלב למשימה שלנו...

תאר לעצמך את קובץ הנתונים המקורי שלנו data.csv נוצר באופן אוטומטי על ידי תהליך כלשהו ותמיד מאוחסן בדיסק באותו מקום. לדוגמה, C:Datadata.csv - נתיב לקובץ עם נתונים מעודכנים. תהליך פתיחת הקובץ וייבוא ​​הנתונים ממנו יכול להיות מתועד גם במאקרו:

1. פתח את קובץ התבנית שבו שמרנו את המאקרו - FormatData.
2. צור מאקרו חדש בשם לטעון מידע.
3. בזמן הקלטת מאקרו לטעון מידע לייבא נתונים מהקובץ data.csv – כפי שעשינו בחלק הקודם של השיעור.
4. בסיום הייבוא, הפסק את הקלטת המאקרו.
5. מחק את כל הנתונים מהתאים.
6. שמור את הקובץ כתבנית Excel עם מאקרו (סיומת XLTM).

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

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