شرح أهم الدوال الأساسية في برنامج الإكسل “الدالات الشرطية”

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

في المقال السابق تم شرح أهم الدوال الحسابية وهم :-
دالة الجمع – SUM ، دالة المعدل – AVERAGE ، القيمة الأعلى – MAX ، القيمة الأصغر – MIN ، القيمة الكبرى – LARGE ، القيمة الصغری – SMALL ، دوال العد – COUNT – COUNTA COUNTBLANK .

فيم يلي شرح دوال من أهم الدالات الأساسية في برنامج الإكسل “الدالات الشرطية” :-

إذا الشرطية IF ، استخدام أكثر من شرط ( IF المتعددة ) ، العد الشرطي – COUNTIF ، العد بشروط متعددة – COUNTIFS ، الجمع الشرطي – SUMIF الجمع بشروط متعددة – SUMIFS ، دالة حساب المعدل بشرط واحد – AVERAGEIF ، دالة حساب المعدل بشروط – AVERAGEIFS ، تعدد الشروط مع AND و OR .

إذا الشرطية – IF :

= IF ( logical_test , [ value_if_true ] , [ value_if_false ] )

logical_test : الاختبار المنطقي ، أي الشرط المراد التحقق منه .
value_if_true : القيمة التي تظهر في حال تحقق الشرط .
value_if_false : القيمة التي ستظهر في حال لم يتحقق الشرط .

ملاحظة : القيمة ممكن أن تكون معادلة ، أو خلية أو رقمأ ، أو نص يوضع بين علامتي اقتباس .

مثال : وضع كلمة Large إذا كانت القيمة أكبر من 20 ، Small إذا كانت أقل من ذلك :

مثال : حساب الراتب الجديد إذا كانت الزيادة 15 بالمئة لمن يعمل بدوام الكامل (Full Time) و 10 بالمئة لمن يعمل لبقية الموظفين :

استخدام أكثر من شرط ( IF المتعددة ) :-

مثال : وضع كلمة Large إذا كانت القيمة أكبر من 30 ، و Medium إذا كانت القيمة أكبر من 20 ، و Small إذا كانت غير ذلك :

بمعني افحص الخانة A1 في حال كانت أكبر من 30 ضع Large ، أما في حال لم يكن كذلك افتح شرط جديد وتحقق من جديد ، إذا كانت الخلية ذاتها أكبر من 20 ضع Medium وإذا لم يتحقق كل ماسبق ضع Small .
وهنا نغلق أقواس بعدد ال IF التي فتحناها .

مثال : حساب الراتب الجديد إذا كانت الزيادة هي 15 بالمئة لمن يعمل بدوام كامل ( Full Time ) و 10 بالمئة لمن يعمل بدوام جزئي ( Short Time ) و 5 بالمئة لبقية الموظفين :

تعدد الشروط مع AND و OR :-

= AND ( logical1 , [ logical2 ] , …. )
= OR ( logical1 , [ logical2 ] , …. )

AND وهي معادلة تحقق جميع الشروط ، أي في حال أردنا أن تتحقق مجموعة من الشروط نستخدم AND وسوف يعطينا القيمة True في حال تحققها جميعها و False في حال لم يتحقق واحد منها على الأقل .

أما OR فهي معادلة تحقق شرط واحد على الأقل من عدة شروط ، بحيث يعطي True في حال تحقق واحد منها على الأقل ، و False في حال لم يتحقق أي شيء .

مثال : معرفة الأشخاص الذين تجاوزت علاماتهم ال 50 في الامتحانات الأولية والنهائية :

بينما لو قمنا بإستخدام الدالة OR سيقوم بإعطائنا من تجاوزت علامته 50 في إمتحان واحد على الأقل :

العد الشرطي – COUNTIF :-

= COUNTIF ( range , criteria )

range : نطاق الخانات المراد عدها في حال تحقق شرط معين .
criteria : المعيار الذي سيقوم بالعدبناء عليه .

فلو كان لدينا أسماء موظفين مع طبيعة عملهم .. وأردنا أن نعرف عدد الموظفين الذين يكون دوامهم كاملاً .. نختار العمود الذي يحتوي على طبيعة العمل ، ثم نضع معيار العد بأن يكون دوام كاملا .

مثال : حساب عدد الأشخاص الذين يعملون بدوام كامل ( Full Time ) :

بإمكاننا بذالك الأمر أن نضع بدل كلمة Full Time ، الخلية التي تحتوي عليه :

ونقصد هنا ، متى وجدت في النطاق B2:B8 خلية مشابهة للخلية B2 قم بعدها .

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

مثال : عد القيم التي هي أكبر من القيمة 20 :

وبعبارة أخرى نستطيع القول أنه متى ما أردنا أن نضع إشارة مقارنة ضمن جزء المعيار Criteria يتوجب علينا أن نضعهاجميعابين إشارتي اقتباس .
والاشارات تشمل : الأكبر > ، الأصغر < ، اليساوي = ، المختلف ( اللا يساوي ) < >

مثال : حساب عدد الموظفين جميعا باستثناء من يعمل عمل حر Free lance :

العد بشروط متعددة – COUNTIFS :-

= COUNTIFS ( criteria_range1 , criteria 1 , [ criteria_range2 ] , [ criteria2 ] , …. )

criteria_range1 : نطاق الخانات الأول المراد عدها بناء على المعيار الأول
criteria1 : المعيار الأول الذي سيقوم بالعدبناء عليه .
criteria_range2 : نطاق الخانات الثاني المراد عدها بناء على المعيار الثاني .
criteria2 : المعيار الثاني الذي سيقوم بالعدبناء عليه .

ماذا لو أردنا أن نضع أكثر من شرطه ، هنا نحتاج COUNTIFS لأنها تضع أكثر من شرط ، كأن نعد الخانات ذات دوام كامل وأن يكون المرتب أعلى من قيمة معينة .

مثال : حساب عدد الموظفين الذين يعملون بدوام كامل Full Time ومرتبهم يزيد عن ال 1300 :

مثال : حساب عدد الموظفين الذي تتراوح رواتبهم بين 500 و 1300 :

الجمع الشرطي – SUMIF :-

= SUMIF ( range , criteria , [ sum_range ] )

وهي من عائلة الدالة SUM إلا أنها هنا لا تجمع إلا في حال تحقق شرط معين .

range : نطاق الشرط وهو نطاق الخانات الذي سنحدد له معيار ، في حال تحققه سيجمع قيمته المقابلة .
criteria : المعيار المراد تحققه حتى يقوم بالجمع .
sum_range : نطاق الجمع ، وهو نطاق الخانات الحاوية على القيم المراد جمعها في حال تحقق المعيار .

مثال : حساب مجموع رواتب الموظفين الذين يعملون بدوام كامل Full Time

مثال : حساب مجموع رواتب الموظفين الذين تتجاوز مرتباتهم الـ 1000 :

يمكننا الإستغناء عن الجزء الثالث من معادلة SUMIF في حال كان النطاق المشروط هو ذاته نطاق الجمع .

الجمع بشروط متعددة – SUMIFS :-

= SUMIFS ( sum_range , criteria_range1 , criteria1 , [ criteria_range2 ] , [ criteria2 ] , ….. )

وهي أن يتم الجمع في حال كان لدينا أكثر من شرط .

sum_range : نطاق الجمع ، وهو الخانات الذي يقوم بجمعها في حال تحققت الشروط
criteria_range1 : نطاق الخانات المشروط الأول الذي سوف نضع له شرط حتى يقوم بعد القيمة المقابلة
criterial : المعيار الأول المراد تحققه في نطاق الخانات الأول .
criteria_range2 : نطاق الخانات المشروط الثاني الذي سوف نضع له شرط ثاني حتى يقوم بعد القيمة المقابلة
criteria2 : المعيار الثاني المراد تحققه في نطاق الخانات الثاني .

مثال : حساب مجموع رواتب الموظفين الذكور الذين يعملون بدوام كامل Full Time :

مثال : حساب كمية اللابتوبات المباعة من نوع HP :

حساب المعدل عند تواجد شرط معين :-

الدالتان AVERAGEIF و AVERAGEIFS تشبهان بهيكليتهما معادلتي SUMIF و SUMIFS إلا أنها هنا تقوم بحساب المعدل وليس الجمع .

دالة حساب المعدل بشرط واحد – AVERAGEIF :-

= AVERAGEIF ( range , criteria , [ average_range ] )

تقوم بحساب المعدل في حال كان لدينا شرط واحد .

range : نطاق الشرط .
criteria : المعيار المراد تحققه .
average_range : نطاق حساب المعدل .

مثال : حساب معدل رواتب الموظفين الذين يعملون بدوام كامل Full Time

دالة حساب المعدل بشروط – AVERAGEIFS :-

= AVERAGEIFS ( average_range , criteria_range1 , criteria1 , ( criteria_range2 ] , [ criteria2 ] , ….. )

تقوم بحساب المعدل في حال كان لدينا أكثر من شرط .

average_range : نطاق حساب المعدل
criteria_range1 : نطاق الخانات المشروط الأول .
criterial : المعيار الأول .
criteria_range2 : نطاق الخانات المشروط الثاني .
criteria2 : المعيار الثاني .