למד כיצד להשתמש פקודות מאקרו של Excel כדי להפוך משימות מייגעות

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

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

הערה: אותו תהליך אמור לפעול ברוב הגרסאות של Microsoft Office. צילומי המסך עשויים להיראות מעט שונים.

מהו מאקרו??

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

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

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

יצירת מאקרו: הסבר על ידי דוגמה

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

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

כדי ליצור מאקרו, עבור אל 'תצוגה'> 'פקודות מאקרו'> 'הקלט מאקרו'.

הקצה למאקרו שם (ללא רווחים) ולחץ על אישור.

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

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

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

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

הבא, להחיל את הנוסחאות המתאימות (בהתאמה):

• = SUM (B2: K2)
• = AVERAGE (B2: K2)
• MIN = (B2: K2)
• מקס = (B2: K2)
• = MEDIAN (B2: K2)

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

ברגע שזה נעשה, כל שורה צריכה להציג את הסיכומים שלהם.

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

בהתאמה:

• = SUM (L2: L21)
• = AVERAGE (B2: K21) *זה חייב להיות מחושב על פני כל הנתונים, כי הממוצע של ממוצעים השורה לא בהכרח שווה לממוצע של כל הערכים.
• MIN = MIN (N2: N21)
• מקס = (O2: O21)
• = MEDIAN (B2: K21) * חושב על כל הנתונים מאותה סיבה כמו לעיל.

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

לאחר מכן, החלת עיצוב חזותי על כותרות השורה ועל העמודות:

• מודגש.
• מרוכז.
• צבע מילוי רקע.

ולבסוף, להחיל סגנון כלשהו על הסיכומים.

כשהכל נגמר, כך נראה גיליון הנתונים שלנו:

מאז אנחנו מרוצים מהתוצאות, להפסיק את ההקלטה של ​​המאקרו.

ברכות - יצרת רק מאקרו של Excel.

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

לשם כך, בחר את כל התאים ומחק אותם.

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

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

באמצעות מאקרו של Excel

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

• פקודות מאקרו יכולות להיות זדוניות.
• ראה את הנקודה לעיל.

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

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

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

כדי להשלים את הייבוא ​​של קובץ ה- CSV, ייתכן שתצטרך להגדיר מספר אפשרויות כדי ש- Excel יפרש אותו כהלכה (למשל, מפריד, כותרות קיימות וכו ').

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

בתיבת הדו-שיח המתקבלת, אנו רואים את המאקרו "FormatData" שהקלטנו לעיל. בחר אותו ולחץ על הפעלה.

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

מחפש מתחת למכסה המנוע: מה עושה עבודה מאקרו

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

כדי להציג את הקוד שעושה את ריצת המאקרו שלנו, מתיבת הדו-שיח 'מאקרו', לחץ על הלחצן עריכה.

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

לוקח דוגמה שלנו צעד אחד רחוק יותר ...

באופן היפותטי, נניח שמידע נתוני המקור שלנו, data.csv, מיוצר על ידי תהליך אוטומטי שתמיד שומר את הקובץ לאותו מיקום (לדוגמה, C: \ Data \ data.csv הוא תמיד הנתונים העדכניים ביותר). תהליך פתיחת הקובץ וייבואו ניתן בקלות להפוך מאקרו גם כן:

1. פתח את קובץ תבנית Excel המכיל את מאקרו "FormatData".
2. הקלט מאקרו חדש בשם "LoadData".
3. עם הקלטה מאקרו, לייבא את קובץ הנתונים כמו שאתה עושה בדרך כלל.
4. לאחר הנתונים מיובאים, להפסיק להקליט את המאקרו.
5. מחק את כל נתוני התאים (בחר הכל ולאחר מכן מחק).
6. שמור את התבנית המעודכנת (זכור להשתמש בתבנית תבנית מאקרו מאופשרת).

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

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

הורד תבנית זו

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

הורד Excel תבנית מאקרו מ כיצד חנון