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

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

יצירת פונקציה מותאמת אישית פשוטה

פונקציות מותאמות אישית, כמו פקודות מאקרו, משתמשות בשפת התיכנות Visual Basic for Applications (VBA). הם נבדלים מפקודות מאקרו בשתי דרכים משמעותיות. ראשית, הם משתמשים בפרוצדורות פונקציה במקום בפרוצדורות משנה . כלומר, הם מתחילים במשפט פונקציה במקום במשפט Sub ומסתיימים בפונקציה 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 דורשת שני ארגומנטים, quantity ו - price. בעת קריאה לפונקציה בתא של גליון עבודה, עליך לכלול שני ארגומנטים אלה. בנוסחה =DISCOUNT(D7,E7), D7 הוא הארגומנט quantity ו- E7 הוא הארגומנט price . כעת באפשרותך להעתיק את הנוסחה DISCOUNT ל- G8:G13 כדי לקבל את התוצאות המוצגות להלן.

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

משפט 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

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

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

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

הכרת כללי פונקציה מותאמים אישית

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

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

שימוש במילות מפתח של 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.

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

על המחברים

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

זקוק לעזרה נוספת?

תמיד תוכל לשאול מומחה בקהילה הטכנולוגית של Excel או לקבל תמיכה בקהילות.