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

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

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

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

מהו מאקרו?

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

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

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

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

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

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

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

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

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

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

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

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

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

  • = SUM (B2: K2)
  • = ממוצע (B2: K2)
  • = MIN (B2: K2)
  • = MAX (B2: K2)
  • = MEDIAN (B2: K2)

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

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

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

בהתאמה:

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

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

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

  • נוֹעָז.
  • מְרוּכָּז.
  • צבע מילוי רקע.

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

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

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

מזל טוב - יצרת זה עתה מאקרו של Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

מבט מתחת למכסה המנוע: מה גורם למאקרו לעבוד

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

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

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

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

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

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

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

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

הורד תבנית זו

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

הורד את תבנית המאקרו של Excel מ- How-To Geek