برنامج اكسل و تطبيقات الفيجوال بيزيك - الدرس الأول

Excel & VBA applications_ Lesson no. 1

إعداد : محمد طاهر

 

ملخص : فى هذا الدرس سنبحث كيفية استخدام بعض الاوامر مثل

IF , Then

IF , ELSEIF, ELSE, Then

Select Case و

و ذلك من خلال تطبيق استنتاج التقدير المناظر لنسبة مئوية من الدرجات ، فمثال من حصل علي 30% يكون تقديره راسب و من حصل علي 86% يكون تقديره امتياز ...و هكذا ، و ذلك من خلال استخدام أولا قاعدة IF الموجودة في اكسل ، ثم عن طريق بناء 4 دوال مختلفة تؤدي نفس الغرض و هو استنتاج التقدير من القيمة.

المثال التطبيقي علي الدرس

و المثال الذي يشمل استخدام قاعدة IF  و أمثلة الكود اللأربعة موجود هنا

 

أولا   استخدام قاعدة IF الموجودة فى اكسيل

يمكن الحصول علي النتيجة المطلوبة باستخدام تركيبة مركبة من دالة IF  كما فى المثال التالي

+IF(C16>=85;"ممتاز";IF(C16>=75;"جيد جدا";IF(C16>=65;"جيد";IF(C16>=50;"مقبول";"راسب"))))

حيث C16  هي الخلية التي بها الدرجة التي حصل عليها الطالب من 100 ، فينتج فى الخلية التي بها المعادلة عاليه تقدير الطالب

ولكي نفهم كيف تكونت هذه الدالة لندرس استخدام أبسط لدالة IF  و هو استنتاج هل الطالب راسب أم ناجح

 تتكون جملة IF  من ثلاثة أجزاء : الشرط ، و الناتج اذا تحقق الشرط ، ثم الناتج اذا لم يتحقق الشرط و لنبدأ بمثال أبسط :

IF(C7>=50;1;0)

أي اذا كانت القيمة الموجودة فى الخلية C9  أكبر من أو يساوي 50 يكون الناتج  1 و اذا لم يتحقق الشرط يكون الناتج صفر

و بالطبع يمكن الحصول علي كلمة كناتج للمعادلة فى حالة تحقق الشرط أو عدم تحققه أو كلاهما

IF(C7>=50;"ناجح";"راسب")

أي اذا كانت القيمة الموجودة فى الخلية C7  أكبر من أو يساوي 50 يكون الناتج  "ناجح" و اذا لم يتحقق الشرط يكون الناتج "راسب"

أما عن المعادلة الأصلية

+IF(C16>=85;"ممتاز";IF(C16>=75;"جيد جدا";IF(C16>=65;"جيد";IF(C16>=50;"مقبول";"راسب"))))

ففيها استخدام متداخل للدالة فبعد اختبار الشرط الأول C16>=85 يكون الناتج "ممتاز" اذا تحقق الشرط ، أما اذا لم يتحقق فبدلا من اعطاء قيمة يتم تنفيذ إختبار آخر   C16>=75   و بكون ناتج الاختبار الثاني  "جيد جدا"  اذا تحقق الشرط ، أما اذا لم يتحقق فبدلا من اعطاء قيمة يتم تنفيذ إختبار آخر   C16>=65    و بكون ناتج الاختبار الثاني  "جيد"  اذا تحقق الشرط ، أما اذا لم يتحقق فبدلا من اعطاء قيمة يتم تنفيذ إختبار آخر C16>=50   و بكون ناتج الاختبار الثاني  "مقبول"  اذا تحقق الشرط ،فلا يتم تنفيذ إختبار آخر و لكن تكون نتيجة الاختبار "راسب"

 

ثانيا   استخدام الكود

و سنقوم هنا باستعراض 4 دوال مختلفة للحصول علي نفس نتيجة معادلة IF السابقة

و فى البدابة نتعرف علي كيف تصاغ الدالة و الصياغة النمطية هي

 Function FunctionmName()

--سياق الدالة

End Function

 : و يمكن أن تكون الدالة تستقبل متغيرا و في هذه الحالة يكون شكلها كالتالي

 

 Function FunctionmName(اسم المتغير)

--سياق الدالة

End Function

 

 : وفى حالة معرفة نوع المتغير يستحسن تعريف نوعه

 

 Function FunctionmName(Myvarriable as double)

--سياق الدالة

End Function

 

 

 

و هذا يدفعنا الي تعريف أنواع المتغيرات الرئيسية كما هي فى التعليمات علي عجالة و هي

 

Data type نوع البيانات Range مثال علي المدي
Byte رقم صغير 0 to 255
Boolean صح أم خطأ

أو نعم أم لا

 True or False
Integer رقم صحيح بدون كسور

بحد أقصي +32767

 أو - 32768

 -32,768 to 32,767
Long
(long integer)		 مثل السابق  و لكن بسعة أكبر -2,147,483,648 to 2,147,483,647
Single
(single-precision floating-point)		 رقم به كسور بالسعة الموضحة -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double
(double-precision floating-point)		 مثل السابق و لكن بسعة أكبر -1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency
(scaled integer)		 للمعاملات النقدية -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal رقم به كسور بسعة أقل من ال
single  

و ال Double

 +/-79,228,162,514,264,337,593,543,950,335 with no decimal point;
+/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is
+/-0.0000000000000000000000000001
Date تاريخ January 1, 100 to December 31, 9999
Object شيء

مثل صورة مثلا

 Any Object reference
String
(variable-length)		 نص 0 to approximately 2 billion
String
(fixed-length)		 نص 1 to approximately 65,400
Variant
(with numbers)		 أي قيمة في حالة الارقام Any numeric value up to the range of a Double
Variant
(with characters)		 مثل ال Var. Lenght . String

 فى حالة النص

Same range as for variable-length String
User-defined
(using Type)		 نوع معرف من قبل المستخدم و هو يتوقف علي أنواع البيانات المستخدمة فى تعريفه The range of each element is the same as the range of its data type.

 

 

 و قد تعيد الدالة قيمة أو تكتفي بتنفيذ مجموعة من الإجراءات ، و لكن الحالة الأعم هي أن تعيد الدالة قيمة و في هذه الحالة يكون من ضمن السياق الجملة الآتية

FunctionmName = Myvalue

و التي تعني أن الدالة ستعيد القيمة Myvalue

كما سنري فى الدوال الأربعة القادمة

 

 

الدالة الاولي

وهذه الدالة تعتمد علي تطبيق قاعدة Select Case بطريقة المجال ( من الي ) و أيضا مع استخدام حالة أكبر من قيمة معينة

Function GetGrade(mycell)

   Dim x As Double

   x = Round(mycell.Value, 1)

  Select Case x

  Case 0 To 49.9

         GetGrade = "راسب"

 Case 50 To 64.9

        GetGrade = "مقبول"

 Case 65 To 74.9

        GetGrade = "جيد"

 Case 75 To 84.9

        GetGrade = "جيد جدا"

 Case Is >= 85

        GetGrade = "ممتاز"

Case Else

        GetGrade = "رقم غريب"

 End Select

End Function

 

 

و في بداية الدالة يتم استخدام الدالة Round

و ذلك لكي يتم تقريب الرقم الي عدد معين من الأرقام العشرية و هنا اخترانا رقمان عشريان ، و ذلك لأننا ننوي استخدام رقمان عشريان عند تحديد مجالات البحث

فمثلا المجال الذي حددناه للطالب لكي يكون راسبا هو من صفر الي 49.9 و لوكنا اخترنا 49.99 لاستخدمنا رقمان عشريان مع الدالة Round

 

ثم بعد ذلك نبدأ فى صياغة جملة ال Select

 و هنا سيتم تطبيق الجملة علي ناتج التفريب المسمي X

و نبدأ فى تصنيف الحالات بين حملتي Select Case

 و End select

 فتكون الحالة الاولي من صفر الي 49.9 ، و يكون ناتج الدالة (نفس اسم الدالة هو "راسب"

و يلي ذلك الحالة الثانية و التي ناتجها مقبول ... و هكذا الي أن نصل الي الحالة قبل الأخيرة و فيها اخترنا  أنه اذا كان الرقم أكبر من 85 يكون الناتج ممتاز

أما عن الشق الأخير فهو اذا كان الرقم غير كل ما ذكر سابقا ( و هو هنا أن تكون الدرجة بالسالب ) فاخترنا أن يكون ناتج المعادلة

 ( رقم غريب )

الدالة الثانية

وهذه الدالة تعتمد علي تطبيق قاعدة Select Case بطريقة المجال  مع استخدام حالة أكبر من قيمة معينة فقط

و هنا يتم اختبار الحالات بترتيب وضع الشروط  الي أن تتحق حالة فيتوقف الاختبار عندها و يتم تسجيل قيمة الدالة عند هذه الخطوة

Function GetGrade2(mycell)

Dim x As Double

x = mycell.Value

     Select Case x

        Case Is >= 85

          GetGrade2 = "ممتاز"

        Case Is >= 75

         GetGrade2 = "جيد جدا"

        Case Is >= 65

         GetGrade2 = "جيد"

        Case Is >= 50

         GetGrade2 = "مقبول"

       Case Is >= 0

          GetGrade2 = "راسب"

       Case "else"

         GetGrade2 = "رقم غريب"

       End Select

 End Function

 

 

 

هنا لم يتم استخدام الدالة Round

و ذلك لأنطريقة المجال ( مثل 50 الي 65 ) غير متبعة و لا داعي لتقريب القيم

حيث أن اختبار القيم يعتمد علي اختبار كونها أكبر من قيمة معينة أم لا

و هنا لأن الاختبار سيتوقف عند تحقق الشرط فاما ان نبدأ بأكبر من 85 (حالة الامتياز ) أو أصغر من 50 ( حالة الرسوب ) و نستمر تنازليا فى الحالة الأولي و  تصاعديا فى الحالة الثانية

 

و هذا المثال يعرض الحالة الأولي

 

فأما ان كان الرقم فعلا أكبر من أو يساوي 85 فيتم تسجيل قيمة الدالة ب "ممتاز" و ينتهي الاختبار بتحقق الشرط و ان لم يكن ، فيتم اختبار حالة كونه أكبر من 75   ( و هنا يكون قد فشل اختبار كونه أكبر من 85 ) فمعني ذلك أن الرقم يختبر من حيث كونه أكبر من أو يساوي 75 و أقل من أو يساوي 85 ، فان تحقق الشرط فتكون قيمة الدالة "جيد جداً "

و ان لم يكن فيتم اختبار الحالة التالية

 

الي أن نصل الي الشق الأخير فهو اذا كان الرقم غير كل ما ذكر سابقا ( و هو هنا أن تكون الدرجة بالسالب ) فاخترنا أن يكون ناتج المعادلة

 ( رقم غريب )

 

 

الدالة الثالثة

وهذه الدالة تعتمد علي تطبيق قاعدة IF , THEN  بدون استخدام خيارات ELSEIF  و ELSE

و هنا يتم اختبار الحالات بترتيب وضع الشروط  الي أن تتحق حالة فيتم تسجيل فيمة الدالة و لكن لا يتوقف الاختبار و نستمر فى اختبار القيم و يتم تسجيل قيمة جديد للدالة عند نجاح أي اختبار  ، وهذه الطريقة تؤدي الغرض و لكن يعيبها المرور بكافة الاختبارات ، و تمتاز عنها باقي الطرق بقلة وقت الاختبار

Function GetGrade3(mycell)

Dim x As Double

x = mycell.Value

GetGrade3 = "رقم غريب"

If x >= 0 Then GetGrade3 = "راسب"

If x >= 50 Then GetGrade3 = "مقبول"

If x >= 65 Then GetGrade3 = "جيد"

If x >= 75 Then GetGrade3 = "جيد جدا"

If x >= 85 Then GetGrade3 = "ممتاز"

End Function

 

اما ان نبدأ بأكبر من صفر  (حالة الرسوب ) أو أصغر من 100

( حالة االامتياز ) و نستمر تنازليا فى الحالة الثانية و  تصاعديا فى الحالة الأولي

 

و هذا المثال يعرض الحالة الأولي

 

فأما ان كان الرقم فعلا أكبر من أو يساوي صفر فيتم تسجيل قيمة الدالة ب "راسب" و لا ينتهي الاختبار بتحقق الشرط و بعد ذلك يتم اختبار حالة كونه أكبر من 50   ( و هنا لا يكون معناه أنه  قد فشل اختبار كونه أكبر من صفر ) فاذا نجح الاختبار تصبح القيمة 50 و ان لم يكن تبقي صفر و هكذا ..

 

و نلاحظ أنه اذا لم يتحقق أي من الشروط المذكورة فيكون مطلوبا أن تعيد الدالة " رقم غريب" كما سبق ، و هنا تم تسجيله كقيمة مبدأية للدالة قبل اجراء أي اختبار ، بحيث اذا فشلت جميع الاختبارات تكون القيمة هي "رقم غريب"

 

الدالة الرابعة

وهذه الدالة تعتمد علي تطبيق قاعدة IF , THEN  مع  استخدام خيارات ELSEIF  و ELSE

و هنا يتم اختبار الحالات بترتيب وضع الشروط  الي أن تتحقق حالة  فيتم تسجيل فيمة الدالة و لكن لا يتم تسجيل قيمة جديد للدالة  الا عند نجاح الاختبار  ، و تكون حالات الاختبار حالات فريدة أي أنه لا يصح كون القيمة المختبرة تحقق أكثر من شرط ، وهذه الطريقة تمتاز عن الطريقة السابقة  بقلة وقت الاختبار حيث أنه فى الحالة السابقة  كان من الممكن أن تنجح القيمة المختبرة فى تحقيق أكثر من شرط ( أي أنها أكبر من صفر و أكبر من خمسين و أكبر من 65  - القيمة 68 مثلا ) ، و بالتالي تيم تسجيل القيمة أكثر من مرة. و هذا م لا يحدث هنا كما سنشاهد سويا

Function GetGrade4(mycell)

Dim x As Double

x = mycell.Value

If x >= 0 And x < 50 Then

GetGrade4 = "راسب"

ElseIf x > 50 And x <= 65 Then

GetGrade4 = "مقبول"

ElseIf x > 65 And x <= 75 Then

GetGrade4 = "جيد"

ElseIf x > 75 And x <= 85 Then

GetGrade4 = "جيد جدا"

ElseIf x > 85 Then

GetGrade4 = "ممتاز"

Else

GetGrade4 = "رقم غريب"

End If

End Function

 

اذا كانت القيمة بين صفر و أقل من أو يساوي 50 يكون ناتج المعالة "راسب "

و لا يمكن أن يحقق أي من الشروط التالية

 

و ان كانت القيمة بين 50 و أقل من أو يساوي 65 يكون ناتج المعالة "مقبول "

و لا يمكن أن يحقق أي من الشروط التالية أو السابقة

 

... و هكذا الي أن نصل الي الحالة قبل الأخيرة و فيها اخترنا  أنه اذا كان الرقم أكبر من 85 يكون الناتج ممتاز

 

أما عن الشق الأخير فهو اذا كان الرقم غير كل ما ذكر سابقا ( و هو هنا أن تكون الدرجة بالسالب ) فاخترنا أن يكون ناتج المعادلة

 ( رقم غريب )