إنشاء دالات مخصصة في Excel

على الرغم من أن Excel يتضمن العديد من دالات ورقة العمل المضمنة، فمن المحتمل ألا يكون لديه دالة لكل نوع من أنواع العمليات الحسابية التي تجريها. لم يتمكن مصممو Excel من توقع احتياجات العملية الحسابية لكل مستخدم. بدلا من ذلك، يوفر لك Excel إمكانية إنشاء دالات مخصصة، موضحة في هذه المقالة.

إنشاء دالة مخصصة بسيطة

تستخدم الدالات المخصصة، مثل وحدات الماكرو، لغة البرمجة Visual Basic for Applications (VBA ). وهي تختلف عن وحدات الماكرو بطريقتين مهمتين. أولا، يستخدمون إجراءات الدالة بدلا من الإجراءات الفرعية . أي أنها تبدأ بعبارة دالة بدلا من العبارة الفرعية وتنتهي ب End Function بدلا من 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 تتطلب وسيطتين، الكميةوالسعر. وعندما تستدعي الدالة في خلية ورقة عمل، يجب تضمين هاتين الوسيطتين. في الصيغة =DISCOUNT(D7,E7)، D7 هي وسيطة الكمية ، وE7 هي وسيطة السعر . يمكنك الآن نسخ صيغة DISCOUNT إلى G8:G13 للحصول على النتائج الموضحة أدناه.

دعنا نفكر في كيفية تفسير Excel لإجراء الدالة هذا. عندما تضغط على مفتاح الإدخال Enter، يبحث Excel عن الاسم DISCOUNT في المصنف الحالي ويجد أنها دالة مخصصة في وحدة نمطية في 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 التي تخزن قيمة في متغير جملة تعيين ، لأنها تقيم التعبير الموجود على الجانب الأيمن من علامة التساوي وتعين النتيجة إلى اسم المتغير في الجانب الأيمن. نظرا لأن المتغير Discount له نفس اسم إجراء الدالة، يتم إرجاع القيمة المخزنة في المتغير إلى صيغة ورقة العمل التي تسمى الدالة DISCOUNT.

إذا كانت الكمية أقل من 100، فإن VBA ينفذ العبارة التالية:

Discount = 0

وأخيرا، تقرب العبارة التالية القيمة المعينة إلى متغير الخصم إلى منزلتين عشريتين:

Discount = Application.Round(Discount, 2)

لا يحتوي VBA على الدالة ROUND، ولكن Excel يمتلكه. لذلك، لاستخدام الدالة ROUND في هذه العبارة، ستطلب من VBA البحث عن أسلوب Round (الدالة) في عنصر التطبيق (Excel). يمكنك القيام بذلك عن طريق إضافة كلمة تطبيق قبل الكلمة Round. استخدم بناء الجملة هذا كلما أردت الوصول إلى دالة Excel من وحدة نمطية في VBA.

فهم قواعد الدالات المخصصة

يجب أن تبدأ الدالة المخصصة بعبارة Function وتنتهي بعبارة End Function. بالإضافة إلى اسم الدالة، عادة ما تحدد عبارة Function وسيطة واحدة أو أكثر. ومع ذلك، يمكنك إنشاء دالة بدون أية وسيطات. يتضمن Excel العديد من الدالات المضمنة، على سبيل المثال RAND وNOW، التي لا تستخدم الوسيطات.

بعد استخدام عبارة الدالة، يتضمن إجراء الدالة عبارة VBA واحدة أو أكثر من جملة 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 ضمن عنوان VBAProject، فسترى وحدة نمطية مسماة على اسم ملف الوظيفة الإضافية. ستحتوي الوظيفة الإضافية على الملحق .xlam.

يؤدي النقر المزدوج فوق هذه الوحدة النمطية في "مستكشف المشاريع" إلى قيام محرر Visual Basic بعرض التعليمات البرمجية للوظيفة. لإضافة دالة جديدة، ضع نقطة الإدراج بعد عبارة End Function التي تنهي الدالة الأخيرة في النافذة Code وابدأ الكتابة. يمكنك إنشاء العديد من الدالات وفقا لاحتياجك بهذه الطريقة، وستكون متوفرة دائما في الفئة "معرفة من قبل المستخدم" في مربع الحوار "إدراج دالة ".

حول المؤلفين

تم تأليف هذا المحتوى في الأصل بواسطة Mark Dodge وCraig Stinson كجزء من كتابهما Microsoft Office Excel 2007 Inside Out. ومنذ ذلك الحين تم تحديثه لتطبيقه على الإصدارات الأحدث من Excel أيضا.

هل تحتاج إلى مزيد من المساعدة؟

يمكنك دائما الاستفسار من أحد الخبراء في مجتمع Excel التقني أو الحصول على الدعم في المجتمعات.