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

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

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

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

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

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

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

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

  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.

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

=DISCOUNT(D7,E7)

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

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

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

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

אם הצהרת בבלוק הבאים של קוד בודקת את הארגומנט כמות והן קובע אם מספר הפריטים שנמכרו גדול או שווה ל- 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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

×