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

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

مثال على دالة VBA مع التعليقات

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

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

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

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

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

مربع الحوار "إدراج دالة"

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

  1. بعد إنشاء الوظائف التي تحتاجها، انقر فوق ملف > حفظ باسم.

  2. في مربع الحوار حفظ باسم ، افتح القائمة المنسدلة حفظ بنوع ، وحدد وظيفة Excel الإضافية. احفظ المصنف باسم يمكن التعرف عليه، مثل MyFunctions، في مجلد AddIns . سيقترح مربع الحوار حفظ باسم هذا المجلد، لذلك كل ما عليك القيام به هو قبول الموقع الافتراضي.

  3. بعد حفظ المصنف، انقر فوق ملف > خيارات Excel.

  4. في مربع الحوار خيارات Excel ، انقر فوق فئة الوظائف الإضافية .

  5. في القائمة المنسدلة إدارة ، حدد وظائف Excel الإضافية. ثم انقر فوق الزر Go .

  6. في مربع الحوار الوظائف الإضافية ، حدد خانة الاختيار الموجودة بجانب الاسم الذي استخدمته لحفظ المصنف، كما هو موضح أدناه.

    مربع الحوار "الوظائف الإضافية"

  1. بعد إنشاء الوظائف التي تحتاجها، انقر فوق ملف > حفظ باسم.

  2. في مربع الحوار حفظ باسم ، افتح القائمة المنسدلة حفظ بنوع ، وحدد وظيفة Excel الإضافية. احفظ المصنف باسم يمكن التعرف عليه، مثل MyFunctions.

  3. بعد حفظ المصنف، انقر فوق أدوات > وظائف Excel الإضافية.

  4. في مربع الحوار الوظائف الإضافية ، حدد الزر استعراض للعثور على الوظيفة الإضافية، وانقر فوق فتح، ثم حدد المربع الموجود بجانب Add-In في المربع الوظائف الإضافية المتوفرة .

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

وحدة نمطية مسماة في vbe

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

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

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

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

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

الخروج من الخيارات إضافية؟

استكشف مزايا الاشتراك، واستعرض الدورات التدريبية، وتعرف على كيفية تأمين جهازك، والمزيد.

تساعدك المجتمعات على طرح الأسئلة والإجابة عليها، وتقديم الملاحظات، وسماعها من الخبراء ذوي الاطلاع الواسع.