شرح أهم الدوال الشرطية في اكسيل بطريقة مبسطة

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

ويتم استخدام الدوال الشرطية في اكسيل لاستخراج البيانات المطلوبة وإنشاء التقارير المختلفة، ومن أهم تلك الدوال الشرطية في اكسيل دالة إذا الشرطية IF، دالة OR و AND، دالة الجمع الشرطي SUMIF، ودالة العد الشرطي COUNTIF.

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

طريقة كتابة الدوال الشرطية في اكسيل

تختلف الدوال الشرطية في اكسيل في الرموز المستخدمة في كل دالة ولكن تتفق في تنفيذ خطوات المعادلة وهى كالتالي:-

• يتم الضغط على الخلية المراد حل المعادلة فيها.
• اكتب علامة يساوي =.
• ثم اكتب اسم الدالة المراد استخدامها مثلا: IF.
• بعد ذلك قم بالكتابة اليدوية لصيغة المعادلة وتم شرحه تفصيلاً في كل دالة.
•  بعد كتابة المعادلة بطريقة صحيحة اضغط enter.
• سوف تظهر نتيجة المعادلة ويتم حذف تفاصيل المعادلة.
• يمكنك الضغط على الخلية والسحب بزر الماوس لأسفل لتكرار العملية في الخلايا التالية.

1. دالة IF أشهر الدوال الشرطية في اكسيل

تتكون دالة IF الشرطية من نوعين وهما:-

• معادلة IF البسيطة

شكل المعادلة:- IF (logical_test, [value_if_true], [value_if_false])

• Logical-test: وهى قيمة إجبارية بمعنى لا يمكن قبول الدالة بدونها وهى شرط الدالة على سبيل المثال: C2<100 أو B2<D2.
• value –if- True: قيمة اختيارية يتم إرجاعها في حالة تحقق شرط الدالة على سبيل المثال: إذا كانت الإجابة على السؤال نعم أو كتابة قيمة منطقية مثل ناجح أو قيمة عددية 100 وهكذا.
• value-if-false: قيمة اختيارية يتم إرجاعها في حالة عدم تحقق شرط الدالة على سبيل المثال:إذا كانت الإجابة على السؤال لا أو كتابة قيمة منطقية راسب أو قيمة عددية 80 وهكذا.

على سبيل المثال:- ـ

إذا أردنا فحص القيم الأعلى من 100.

إذا تحقق الشرط فإن القيم الأعلى من 100 يتم كتابة True.

وإذا لم يتحقق الشرط فإن القيم الأقل من 100يتم كتابة False .

يتم تطبيق هذه الشروط على المعادلة كالتالي:-

IF(C2<100,”True”,”False”)=

وفي حالة الرغبة في تطبيق المعادلة على عمود بأكمله فإنه ليس من الطبيعي كتابة تلك المعادلة في كل خلية بل يتم تحديد الخلية التي تم كتابة المعادلة بها من خلال زر الماوس ثم الاستمرار في السحب لتحديد العمود بأكمله وعند ترك زر الماوس نجد تطبيق المعادلة تلقائياً في كافة الخلايا.

• معادلة IF المركبة

تتكون معادلة IF المركبة من عدة شروط ويكون شكل المعادلة:-

IF(Logical-test1,[value if true],IF Logical-test2,[value if true],IF(Logical-test3,[value if true],[value if false])

على سبيل المثال:

إذا أردنا حساب تقديرات الطلاب في مادة معينة.

1- أكبر من أو يساوي 90 نحسب التقدير Excellent.

2- أكبر من أو يساوي 80 نحسب التقدير Very Good.

3- أكبر من أو يساوي 60 نحسب التقدير Good.

4- أكبر من أو يساوي 50 نحسب التقدير Pass.

فيما عدا ذلك نحسب التقدير bad.

يتم تطبيق هذه الشروط في المعادلة كالتالي:-

IF(B2>=90,”Excellent”,IF(B2>=80,”very Good”, IF(B2>=70,”Good”,IF(B2>=50,”Pass”,”bad”)

ومن أجل توزيع تلك المعادلة على كافة علامات الطلاب الموجودة على شيت الاكسيل لا يتطلب منك كتابة المعادلة مرة أخرى في الخلايا التالية ولكن ما يجب عليك فعله هو تحديد الخلية المكتوب بها المعادلة ثم السحب مطولاً لتحديد كافة الخلايا التالية في العمود نفسه وبعد ترك التحديد يتم وضع التقديرات تلقائياً حسب تحقق شرط المعادلة.

2. دالة OR أحد الدوال الشرطية في اكسيل

يتم استخدام دالة OR لاختبار شروط عديدة ليكون إجابة الدالة True في حالة تحقق أي شرط من الشروط بينما تظهر الإجابة False في حالة عدم تحقق أي شرط من الشروط، وتستخدم هذه الدالة عادة لاختبار مجموعة شروط في نفس الوقت ويمكنك وضع شروط كثيرة تصل إلى 255 شرط.

شكل الدالة يكون كالتالي:

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

فعلى سبيل المثال: تستخدم لمعرفة إذا A2 أوC2 أكبر من 50 تستخدم الصيغة OR(A2>50,C2>50) =وعند تحقق أحد الشرطين يتم كتابة True وفي حالة عدم تحقق كل الشروط يتم كتابة False.

3. دالة AND أحد الدوال الشرطية في اكسيل

يتم استخدام دالة AND لاختبار شروط عديدة ليكون إجابة الدالة True في حالة تحقق جميع الشروط بينما تظهر الإجابة False في حالة عدم تحقق أياً من الشروط، وتستخدم هذه الدالة عادة لاختبار مجموعة شروط في نفس الوقت ويمكنك وضع شروط كثيرة تصل إلى 255 شرط.

شكل الدالة يكون كالتالي:-

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

فعلى سبيل المثال:- لمعرفة إذا A2 وC2 أكبر من 50 تستخدم الصيغة AND(A2>50,C2>50) = وعند تحقق كلا الشرطين فقط يتم كتابة True وفي حالة عدم تحقق أي شرط منهما يتم كتابة False.

استخدام دالة IF مع OR و AND

نادراً ما يتم استخدام دوال OR و AND بمفردهما، إذ أنه في العادة يتم استخدامها مع دالة IF.

IF(OR(logical1;logical2;…..Value if True; Value if False)=

IF(AND(logical1;logical2;…..Value if True; Value if False)=

بمعنى يتم وضع OR أو AND في مكان شرط الدالة وفي في حالة تحقق الشرط Value if True وعدم تحقق الشرط Value if False كما شرحنا دالة IF مسبقاً.

على سبيل المثال:-

إذا اردنا تحديد مستوى جيد أو سئ للطلاب في العام الدراسي بتحقيق إحدى الشروط لقيمة أعلى من 80 يتم كتابة المعادلة كالتالي:

IF(OR(A2>80,B2>80,C2>80),(“Good”,”Bad”)=

معنى ذلك سوف تظهر كلمة Good إذا كانت أحد الخلايا أكبر من 80 وسوف تظهر كلمة Bad إذا لم تحتوي أي خلية على أكبر من 80.

بينما إذا أردنا تحديد مستوى جيد أو سئ للطلاب في العام الدراسي بتحقيق كل الشروط لقيمة أعلى من 80 يتم كتابة المعادلة كالتالي:

IF(AND(A2>80,B2>80,C2>80),(“Good”,”Bad”)=

معنى ذلك سوف تظهر كلمة Good إذا كانت كل الخلايا أكبر من 80 وسوف تظهر كلمة Bad إذا لم تحتوي خلية واحدة أو أكثرعلى أكبر من 80.

4. دالة SUMIF من الدوال الشرطية في اكسيل

تختلف دالة الجمع SUM والتي تعد إحدى الدوال الحسابية في اكسيل عن دالة الجمع الشرطي SUMIF والتي تعد واحدة من الدوال الشرطية في اكسيل إذ أن هذه الدالة لا تقوم بجمع القيم إلا في حالة تحقق الشرط الذي يتم إدخاله.

شكل الدالة كالتالي:-

SUMIF(range;criteria;[sum-range])

• Range: يوضح النطاق المقرر تطبيق الشرط عليه وفي حالة تطبيق الشرط يتم جمع قيم هذه الخلايا.
• Criteria: يمثل الشرط الذي يجب تحقيقه من أجل القيام بعملية الجمع.
• Sum-range: يمثل نطاق الخلايا التي تم جمع القيم بداخلها في حالة تحقق الشرط ويمكن التخلي عن هذا الوسيط إذا كان نطاق الشرط هو نفسه نطاق الجمع.

على سبيل المثال:-

SUMIF(B2:B9;”>70″)=

تعني حساب مجموع الطلاب الذين تتجاوز درجاتهم 70 درجة.

5. دالة الجمع بشروط متعددة SUMIFS

تعد دالة الجمع بشروط متعددة SUMIFS إحدى الدوال الشرطية في اكسيل، وهى دالة الجمع في حالة توافر شروط عديدة.

شكل الدالة كالتالي:

SUMIFS(sum-range1,criteria-range1,criteria1, criteria-range2,criteria2)

• sum-range1: يمثل نطاق الخلايا المراد جمع القيم بداخلها في حالة تحقق الشروط.
• criteria-range1: يمثل النطاق الأول به عدة بنود وموجود به الشرط الأول.
• criteria1: يمثل المعيار الأول والذي قد يتم عملية الجمع على أساسه.
• criteria-range2,criteria2: يمثل النطاقات الإضافية والمعايير المقابلة لها ويمكن إدخال ما يصل إلى 127 زوجاً من النطاقات والمعايير.

على سبيل المثال:-

SUMIFS(A2:A8,B2:B8,”Female”, C2:C8,”Good”)=

تعني حساب مجموع عدد الطلاب الإناث الحاصلين على تقدير جيد.

A يمثل عدد الطلاب، B يمثل الجنس، وC يمثل التقدير جيد.

6. دالة COUNTIF من الدوال الشرطية في اكسيل

تختلف دالة العد الشرطي COUNTIF أحد الدوال الشرطية في اكسيل عن دالة COUNT إحدى الدوال الحسابية في اكسيل.

وشكل دالة العد الشرطي:-

COUNTIF(range,criteria)=

• Range: يمثل نطاق الخلايا الذي سوف تنطبق عليه شرط الدالة.
• Criteria: يمثل المعيار الذي سوف يتم العد على أساسه.

على سبيل المثال:-

COUNTIF(B2:B8,”>50″)=

تعني عدد جميع القيم الأكبر من 50 في المجال المحدد.

7. دالة العد بشروط متعددة COUNTIFS

تعد دالة العد بشروط متعددة COUNTIFS إحدى الدوال الشرطية في اكسيل، وهى دالة الجمع في حالة توافر شروط عديدة.

شكل الدالة كالتالي:-

COUNTIFS(Criteria-Range1,Criteria1,Criteria-range2,Criteria2,….)

• criteria-range1: يمثل النطاق الأول به عدة بنود وموجود به الشرط الأول.
• criteria1: يمثل المعيار الأول والذي قد يتم عملية الجمع على أساسه.
• criteria-range2,criteria2: يمثل النطاقات الإضافية والمعايير المقابلة لها ويمكن إدخال ما يصل إلى 127 زوجاً من النطاقات والمعايير.

على سبيل المثال:-

COUNTIFS(B2:B8,”Female”, C2:C8″>60″,C2:C8″<90″)=

تعني حساب عدد الطلاب الإناث الحاصلين الذين تتراوح درجاتهم من 60 إلى 90.

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