Sign in with Microsoft
Sign in or create an account.

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

פונקציות מותאמות אישית, כמו פקודות מאקרו, משתמשות בשפת התיכנות Visual Basic for Applications (VBA). הן שונות מפקודות מאקרו בשתי דרכים משמעותיות. תחילה, הם משתמשים בהליכים של פונקציה במקום בהליכים של Sub. זאת, הם מתחילים במשפט פונקציה במקום במשפט משנה ומסתיים בפונקציה End במקום ב- 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
    

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

כעת אתה מוכן להשתמש בפונקציה 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.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. לאחר שיצרת את הפונקציות שאתה זקוק לה, לחץ על קובץ > בשם.

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

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

  3. לאחר ששמרת את חוברת העבודה, לחץ על קובץ > Excel אפשרויות.

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

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

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

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

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

  1. לאחר שיצרת את הפונקציות שאתה זקוק לה, לחץ על קובץ > בשם.

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

  3. לאחר ששמרת את חוברת העבודה, לחץ על > Excel תוספות.

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

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

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

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

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

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

תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.

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

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

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

עד כמה אתם מרוצים מאיכות השפה?
מה השפיע על החוויה שלכם?

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

×