יצירת פונקציות מותאמות אישית ב- Excel

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

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

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

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

1. הקש Alt+F11 כדי לפתוח את ה- Visual Basic עורך (ב- Mac, הקש FN+ALT+F11) ולאחר מכן לחץ על הוסף > מודול. חלון מודול חדש מופיע בצד השמאלי של חלון Visual Basic עורך.

2. העתק והדבק את הקוד הבא במודול החדש.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

כעת אתה מוכן להשתמש בפונקציה DISCOUNT החדשה. סגור את הכרטיסיה Visual Basic עורך, בחר את תא G7 והקלד את הטקסט הבא:

=DISCOUNT(D7,E7)

Excel מחשב את ההנחה של 10 אחוזים על 200 יחידות ב- $47.50 ליחידה ומחזיר $950.00.

בשורה הראשונה של קוד VBA, הפונקציה DISCOUNT(quantity, price), ציבה שהפונקציה DISCOUNT דורשת שני ארגומנטים, כמותומחיר. בעת קריאה לפונקציה בתא של גליון עבודה, עליך לכלול שני ארגומנטים אלה. בנוסחה =DISCOUNT(D7,E7), D7 הוא הארגומנט quantity , ו- E7 הוא הארגומנט price. כעת באפשרותך להעתיק את הנוסחה DISCOUNT ל- G8:G13 כדי לקבל את התוצאות המוצגות להלן.

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

המשפט If בבלוק הקוד הבא בוחן את הארגומנט quantity וקובע אם מספר הפריטים שנמכרו גדול או שווה ל- 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

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

Discount = quantity * price * 0.1

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

אם כמות נמוכה מ- 100, VBA מבצע את המשפט הבא:

Discount = 0

לבסוף, המשפט הבא מעגל את הערך שהוקצה למשתנה Discount לשני מקומות עשרוניים:

Discount = Application.Round(Discount, 2)

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

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

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

מספר מילות המפתח של VBA שניתן להשתמש בהן בפונקציות מותאמות אישית קטן מהמספר שבו ניתן להשתמש בפקודות מאקרו. פונקציות מותאמות אישית אינן מותרות לבצע פעולות שאינן מחזירות ערך לנוסחה בגליון עבודה, או לביטוי שנעשה בו שימוש במאקרו או בפונקציה אחרים של VBA. לדוגמה, לפונקציות מותאמות אישית אין אפשרות לשנות גודל של חלונות, לערוך נוסחה בתא או לשנות את אפשרויות הגופן, הצבע או התבנית עבור הטקסט בתא. אם תכלול קוד "פעולה" מסוג זה בהליך פונקציה, הפונקציה מחזירה את #VALUE! שגיאת ‎#REF!‎.

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

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

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

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

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

כדי להשתמש בפונקציה מותאמת אישית, חוברת העבודה המכילה את המודול שבו יצרת את הפונקציה חייבת להיות פתוחה. אם חוברת העבודה אינה פתוחה, אתה מקבל #NAME? בעת ניסיון להשתמש בפונקציה. אם אתה מפנה לפונקציה בחוברת עבודה אחרת, עליך להוסיף לפני שם הפונקציה את שם חוברת העבודה שבה נמצאת הפונקציה. לדוגמה, אם אתה יוצר פונקציה בשם DISCOUNT בחוברת עבודה שנקראת Personal.xlsb ואתה קורא לפונקציה זו מחוברת עבודה אחרת, עליך להקליד =personal.xlsb!discount(), לא פשוט =discount().

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

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

לאחר ביצוע שלבים אלה, הפונקציות המותאמות אישית שלך יהיו זמינות בכל פעם שתפעיל את Excel. אם ברצונך להוסיף לספריית הפונקציות, חזור אל הכרטיסיה Visual Basic עורך. אם אתה מסתכל ב- Visual Basic עורך Project Explorer תחת כותרת VBAProject, תראה מודול בשם על שם קובץ התוספת. התוספת שלך תכלול את הסיומת .xlam

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

תוכן זה נכתב במקור על-ידי מארק דודג' וקרייג סטינסון כחלק מספרם Microsoft Office Excel 2007 Inside Out. הוא עודכן מאז להחלה גם על גירסאות חדשות יותר של Excel.