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

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

האם אתה מחפש מידע לגבי אופן היצירה של פונקציה מותאמת אישית של Javascript שניתן לפעול בה ב-Excel עבור Windows, Excel עבור Mac או Excel Online ? אם כן, עיין במאמר מבט כולל על פונקציות מותאמות אישית של Excel.

פונקציות מותאמות אישית, כגון פקודות מאקרו, השתמש בשפת התיכנות של Visual basic For applications (VBA) . הן שונות מפקודות מאקרו בשתי דרכים משמעותיות. תחילה, הם משתמשים בהליכי פונקציה במקום בהליכי משנה. כלומר, הם מתחילים במשפט function במקום במשפט Sub ומסתיימים בפונקציה end במקום באפשרות סיום Sub. שנית, הם מבצעים חישובים במקום לבצע פעולות. סוגים מסוימים של משפטים, כגון משפטים שיבחרו ועוצבו טווחים, אינם נכללים בפונקציות מותאמות אישית. במאמר זה, תלמד כיצד ליצור פונקציות מותאמות אישית ולהשתמש בהן. כדי ליצור פונקציות ופקודות מאקרו, עליך לעבוד עם עורך Visual basic (VBE), שנפתח בחלון חדש בנפרד מ-Excel.

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

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

טופס הזמנה לדוגמה ללא פונקציה מותאמת אישית

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

  1. הקש Alt + F11 כדי לפתוח את Visual basic Editor (ב-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
    

הערה: כדי לגרום לקוד להיות קריא יותר, באפשרותך להשתמש במקש Tab כדי להסיט שורות. הכניסה היא לטובתך בלבד, והיא אופציונלית, כאשר הקוד יפעל עם או בלעדיו. לאחר הקלדת קו מדורג, עורך Visual Basic מניח שהשורה הבאה שלך מוסטת פנימה באופן דומה. כדי לצאת (כלומר, משמאל) תו טאב אחד, הקש Shift + tab.

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

= הנחה (D7, E7)

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

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

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

טופס הזמנה לדוגמה עם פונקציה מותאמת אישית

משפט If בבלוק הקוד הבא בוחן את הארגומנט ' כמות ' וקובע אם מספר הפריטים הנמכרים גדול או שווה ל-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 המאחסן ערך במשתנה נקרא משפט מטלה , מאחר שהוא מעריך את הביטוי בצד השמאלי של סימן השוויון ומקצה את התוצאה לשם המשתנה מימין. מכיוון שלהנחה המשתנה יש שם זהה לזה של פרוצדורת הפונקציה, הערך המאוחסן במשתנה מוחזר לנוסחת גליון העבודה שנקראת הפונקציה הנחה.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

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

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

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

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

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

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

דוגמה של פונקציית VBA עם הערות

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

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

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

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

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

תיבת הדו-שיח 'הוספת פונקציה'

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

  1. לאחר שיצרת את הפונקציות הדרושות לך, לחץ על קובץ _GT_ שמירה בשם.

    ב- Excel 2007, לחץ על לחצן Microsoft Officeולחץ על שמירה בשם

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

  3. לאחר שתשמור את חוברת העבודה, לחץ על File _GT_ Excel options.

    ב- Excel 2007, לחץ על לחצן Microsoft Officeולחץ על אפשרויות Excel.

  4. בתיבת הדו אפשרויות Excel , לחץ על הקטגוריה תוספות .

  5. ברשימה הנפתחת נהל , בחר תוספות של Excel. לאחר מכן לחץ על לחצן Go .

  6. בתיבת הדו תוספות , בחר את תיבת הסימון לצד השם שבו השתמשת כדי לשמור את חוברת העבודה, כפי שמוצג להלן.

    תיבת הדו-שיח 'תוספות'

  1. לאחר שיצרת את הפונקציות הדרושות לך, לחץ על קובץ _GT_ שמירה בשם.

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

  3. לאחר שתשמור את חוברת העבודה, לחץ על כלים _GT_ של Excel Add-Ins.

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

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

מודול בעל שם ב- VBE

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

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

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

ניתן לשאול תמיד מומחה ב- קהילת Tech Community עבור Excel, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- User Voice עבור Excel.

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

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×