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

تستخدم الوظائف المخصصة، مثل وحدات الماكرو، لغة برمجة Visual Basic for Applications (VBA ). وهي تختلف عن وحدات الماكرو بطريقتين مهمتين. أولا، يستخدمون إجراءات الدالة بدلا من الإجراءات الفرعية . أي أنها تبدأ ببيان دالة بدلا من عبارة Sub وتنتهي ب 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

يتم تخزين النتيجة كمتغير Discount. تسمى عبارة VBA التي تخزن قيمة في متغير عبارة التعيين ، لأنها تقيم التعبير على الجانب الأيسر من علامة المساواة وتعين النتيجة إلى اسم المتغير على اليسار. نظرا لأن المتغير Discount له نفس اسم إجراء الدالة، يتم إرجاع القيمة المخزنة في المتغير إلى صيغة ورقة العمل التي تسمى الدالة DISCOUNT.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

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

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

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

عدد الكلمات الأساسية ل VBA التي يمكنك استخدامها في الوظائف المخصصة أصغر من العدد الذي يمكنك استخدامه في وحدات الماكرو. لا يسمح للدالات المخصصة بالقيام بأي شيء آخر غير إرجاع قيمة إلى صيغة في ورقة عمل، أو إلى تعبير مستخدم في ماكرو أو دالة VBA أخرى. على سبيل المثال، لا يمكن للدالات المخصصة تغيير حجم النوافذ أو تحرير صيغة في خلية أو تغيير خيارات الخط أو اللون أو النمط للنص في خلية. إذا قمت بتضمين رمز "الإجراء" من هذا النوع في إجراء دالة، ترجع الدالة #VALUE! #REF!.

الإجراء الوحيد الذي يمكن أن يقوم به إجراء الدالة (بصرف النظر عن إجراء العمليات الحسابية) هو عرض مربع حوار. يمكنك استخدام عبارة InputBox في دالة مخصصة كوسيلة للحصول على إدخال من المستخدم الذي يقوم بتنفيذ الدالة. يمكنك استخدام عبارة MsgBox كوسيلة لنقل المعلومات إلى المستخدم. يمكنك أيضا استخدام مربعات الحوار المخصصة أو UserForms، ولكن هذا موضوع خارج نطاق هذه المقدمة.

حتى وحدات الماكرو البسيطة والوظائف المخصصة قد يكون من الصعب قراءتها. يمكنك تسهيل فهمها بكتابة نص توضيحي في شكل تعليقات. يمكنك إضافة تعليقات بسبق النص التوضيحي باقتباس اقتباسي. على سبيل المثال، يوضح المثال التالي الدالة DISCOUNT مع التعليقات. إضافة تعليقات مثل هذه تسهل عليك أو على الآخرين الحفاظ على التعليمات البرمجية ل VBA مع مرور الوقت. إذا كنت بحاجة إلى إجراء تغيير على التعليمات البرمجية في المستقبل، فسيكون لديك وقت أسهل لفهم ما قمت به في الأصل.

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

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

إن كيفية توثيق وحدات الماكرو والوظائف المخصصة هي مسألة تفضيل شخصي. المهم هو اعتماد بعض أساليب الوثائق واستخدامها باستمرار.

لاستخدام دالة مخصصة، يجب أن يكون المصنف الذي يحتوي على الوحدة النمطية التي أنشأت الدالة فيها مفتوحا. إذا لم يكن هذا المصنف مفتوحا، فستحصل على #NAME؟ عند محاولة استخدام الدالة. إذا قمت بالإشارة إلى الدالة في مصنف مختلف، يجب أن تسبق اسم الدالة باسم المصنف الذي توجد فيه الدالة. على سبيل المثال، إذا قمت بإنشاء دالة تسمى DISCOUNT في مصنف يسمى Personal.xlsb وقمت باستدعاء هذه الدالة من مصنف آخر، فيجب كتابة =personal.xlsb!discount()، وليس ببساطة =discount().

يمكنك حفظ بعض ضغطات المفاتيح (وأخطاء الكتابة المحتملة) عن طريق تحديد الدالات المخصصة من مربع الحوار إدراج دالة. تظهر الدالات المخصصة في الفئة معرفة من قبل المستخدم:

من الطرق الأسهل لتوفير الوظائف المخصصة في جميع الأوقات تخزينها في مصنف منفصل ثم حفظ هذا المصنف كوظيفة إضافية. يمكنك بعد ذلك توفير الوظيفة الإضافية كلما قمت بتشغيل Excel. فيما يلي كيفية القيام بذلك:

بعد اتباع هذه الخطوات، ستتوفر الوظائف المخصصة في كل مرة تقوم فيها بتشغيل Excel. إذا كنت تريد إضافة إلى مكتبة الوظائف الخاصة بك، فارجع إلى محرر Visual Basic. إذا نظرت في Visual Basic Editor Project Explorer ضمن عنوان VBAProject، فسترى وحدة نمطية باسم ملف الوظيفة الإضافية. سيكون للوظيفة الإضافية الملحق .xlam.

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

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