כיצד ליצור פונקציות מוגדרות משתמש ב- Microsoft Excel

2024 מְחַבֵּר: Jason Gerald | [email protected]. שונה לאחרונה: 2023-12-16 11:12

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

שלב

שלב 1. צור חוברת עבודה חדשה או פתח את חוברת העבודה שברצונך לעבד בעזרת פונקציות מוגדרות משתמש (UDF)

שלב 2. פתח את עורך Visual Basic ב- Microsoft Excel באמצעות כלים-> מאקרו-> עורך Visual Basic (או הקש על קיצור הדרך Alt+F11)

שלב 3. לחץ על לחצן מודול כדי להוסיף מודול חדש לדף העבודה שלך

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

שלב 4. צור את "ראש" או "אב טיפוס" של הפונקציה שלך

אב הטיפוס של הפונקציה חייב לעקוב אחר המבנה הבא:

פונקציה ציבורית "שם פונקציה" (פרמטר 1 כסוג 1, פרמטר 2 כסוג 2) כסוג תוצאה.

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

שלב 5. הוסף את קוד הפונקציה כדי לוודא שאתה: 1) משתמש בערך הנתון על ידי הפרמטר; 2) להעביר את התוצאה לשם הפונקציה; ו 3) לסגור את הפונקציה עם המשפט "פונקציית סיום".למידה לתכנות ב- VBA או בכל שפה אחרת דורשת הרבה זמן והדרכה מפורטת. למרבה המזל, לפונקציות אלה יש בדרך כלל בלוקים של קודים קטנים ואינם עושים שימוש רב בתכונות שפת התכנות. להלן כמה אלמנטים של שפת ה- VBA שניתן להשתמש בהם:

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

תוצאה של קורס תפקוד ציבורי (כערך שלם) כמחרוזת

אם ערך> = 5 אז

תוצאות הקורס = "התקבל"

אַחֵר

תוצאות הקורס = "נדחה"

סוף אם

פונקציית סיום

שימו לב לרכיבים שבבלוק הקוד אם:

מצב IF אז קוד ELSE קוד END IF

2. . תוכל להשאיר את מילת המפתח אחרת יחד עם החלק השני של הקוד מכיוון שהיא אופציונלית.
3. בלוק Do (do), המבצע חלק מקוד ה- While או עד כאשר או עד שהתנאי מתקיים. לדוגמא:

פונקציה ציבורית BilPrima (ערך כמספר שלם) כבולאני

Dim i כמספר שלם

i = 2

BilPrima = נכון

לַעֲשׂוֹת

אם ערך / i = Int (ערך / i) לאחר מכן

BilPrima = שקר

סוף אם

i = i + 1

Loop While i <value And NumberPrima = True

פונקציית סיום

תסתכל שוב על האלמנטים:

עשה קוד LOOP WHILE/TIL מצב

4. . שימו לב גם לשורה השנייה אשר "מכריזה" על המשתנה. תוכל להוסיף משתנים לקוד שלך לשימוש מאוחר יותר. משתנים פועלים כערכים זמניים בקוד. לבסוף, שקול את הצהרת הפונקציות כ- BOOLEAN, שהיא סוג נתונים המאפשר רק ערכי TRUE או FALSE. שיטה זו לקביעת מספרים ראשוניים רחוקה מלהיות אופטימלית, אך הקוד נכתב בצורה כזו שקל לקרוא אותו.
5. עבור בלוק (אל), המבצע כמות מסוימת של קוד. לדוגמא:

פקטוריאל פונקציה ציבורית (ערך כמספר שלם) כל עוד

עמום תוצאות כל עוד

Dim i כמספר שלם

אם ערך = 0 אז

תוצאה = 1

ערך ElseIf = 1 לאחר מכן

תוצאה = 1

אַחֵר

תוצאה = 1

עבור i = 1 לערך

תוצאה = תוצאה * i

הַבָּא

סוף אם

פקטוריאל = תוצאה

פונקציית סיום

תסתכל שוב על האלמנטים:

FOR משתנה = גבול תחתון לגבול העליון של הקוד הבא

. כמו כן, שים לב לרכיב ElseIf הנוסף בהצהרת If, המאפשר להוסיף אפשרויות נוספות לקוד המבוצע. לבסוף, שקול את פונקציית "התוצאה" והמשתנה המוצהר כארוך. סוג הנתונים הארוך מאפשר ערכים גדולים בהרבה מאשר מספר שלם.

להלן מוצג הקוד לפונקציה הממירה מספרים קטנים למילים.



שלב 6. חזור לחוברת העבודה והשתמש בפונקציה על ידי כתיבת סמל "שווה" (=) ואחריו שם הפונקציה בתא

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

= NumberToLetter (A4)

. תוכל גם להשתמש בנוסחאות תוצרת בית על ידי חיפוש אותן בקטגוריות משתמש הוגדר בתוך האפשרות Insert Formula. אתה פשוט לוחץ על הכפתור Fx משמאל לסרגל הנוסחאות. ישנם שלושה סוגים של צורות פרמטרים בפונקציות:

1. ערך קבוע שמוקלד ישירות בנוסחת התא. במקרה זה, יש לצטט את הטקסט (מחרוזת).
2. הפניות לתאים, למשל B6 או טווח כמו A1: C3 (הפרמטר חייב להיות מסוג הנתונים "טווח")

3. פונקציה נוספת הכלולה בפונקציה שלך (הפונקציה שלך יכולה להיות מוקפת גם בפונקציה אחרת), לדוגמה: = Factorial (MAX (D6: D8))

   

   שלב 7. ודא שהתוצאות נכונות

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

   טיפים

   • בעת כתיבת גושי קוד במבני שליטה כגון If, For, Do וכו ', הקפד להכניס (הכנס את גבול השורה השמאלית מעט פנימה) את בלוק הקוד על ידי לחיצה על מקש הרווח מספר פעמים או על הכרטיסייה. זה יקל על ההבנה של הקוד וקל יותר למצוא שגיאות. בנוסף, העלייה בפונקציונליות הופכת לקלה יותר לביצוע.
   • אם אינך יודע כיצד לכתוב קוד לפונקציות, קרא את המאמר כיצד לכתוב מאקרו פשוט ב- Microsoft Excel.
   • לפעמים, פונקציות אינן צריכות את כל הפרמטרים כדי לחשב את התוצאה. במקרה זה, תוכל להשתמש במילת המפתח האופציונלית לפני שם הפרמטר בכותרת הפונקציה. תוכל להשתמש בפונקציה IsMissing (parameter_name) בקוד שלך כדי לקבוע אם לפרמטר מוקצה ערך או לא.
   • השתמש בשמות שאינם בשימוש כפונקציות ב- Excel, כך שלא יוחלפו ונמחקו פונקציות.
   • ל- Excel פונקציות מובנות רבות וניתן לבצע את רוב החישובים באמצעות פונקציות מובנות אלה, בנפרד או בבת אחת. הקפד לעיין ברשימת הפונקציות הזמינות לפני שתתחיל לקודד בעצמך. ניתן לבצע את הביצוע מהר יותר אם אתה משתמש בפונקציות מובנות.

   אַזהָרָה

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