تريد أن يكتب Excel تلقائيًا «ناجح» أو «راسب» بجوار كل طالب؟ أو أن يحسب التقدير ممتاز وجيد جدًا؟ أو يعدّ الناجحين في كل فصل؟ هذا عمل الدوال الشرطية، وهي من أهم دوال Excel للمعلم. نكمل هنا على كشف الدرجات: الدرجات في C2:C31، والفصل في العمود E.
1. IF: إذا... فإن... وإلا
الصيغة: IF(logical_test, value_if_true, [value_if_false])
مثال: ناجح أو راسب (النجاح من 50):
=IF(C2>=50,"ناجح","راسب")
- الشرط: C2>=50.
- إذا تحقق يكتب «ناجح»، وإلا يكتب «راسب».
- النصوص يجب أن تكون بين علامتي تنصيص " "، وإلا ظهر خطأ #NAME?.
2. IF المتداخلة: التقديرات
لأكثر من حالتين نضع IF داخل IF:
=IF(C2>=90,"ممتاز",IF(C2>=80,"جيد جدًا",IF(C2>=65,"جيد",IF(C2>=50,"مقبول","راسب"))))
الترتيب مهم: ابدأ بالشرط الأعلى. لو بدأت بـ C2>=50 لحصل صاحب الـ 95 على «مقبول»! وتنصح Microsoft بعدم الإكثار من التداخل لأنه يصعب مراجعته، وتقترح IFS بدلًا منه.
3. IFS: شروط متعددة بلا تداخل
الصيغة: IFS(test1, value1, [test2, value2], ...)
=IFS(C2>=90,"ممتاز",C2>=80,"جيد جدًا",C2>=65,"جيد",C2>=50,"مقبول",TRUE,"راسب")
- الشرط الأخير TRUE يعني «في كل الحالات الباقية».
- بدونه، إن لم يتحقق أي شرط يظهر خطأ #N/A.
- متاحة في مايكروسوفت 365 والإصدارات الحديثة.
عدّل الحدود حسب نظام التقديرات في مدرستك.
4. AND وOR مع IF
AND: كل الشروط يجب أن تتحقق. مثال: ينجح الطالب إذا حصل على 50 أو أكثر في الاختبار (C) وفي الأعمال (F):
=IF(AND(C2>=50,F2>=50),"ناجح","راسب")
OR: يكفي شرط واحد. مثال: يحتاج «خطة علاجية» إذا قلت درجته عن 50 أو غاب أكثر من 5 أيام (G):
=IF(OR(C2<50,G2>5),"خطة علاجية","")
والعلامتان "" تعنيان: اترك الخلية فارغة.
5. IFERROR: إخفاء رسائل الخطأ
الصيغة: IFERROR(value, value_if_error)
=IFERROR(C2/H2,"")
إذا كانت H2 فارغة أو صفرًا، تبقى الخلية فارغة بدل ظهور #DIV/0!. لكن انتبه: IFERROR تخفي كل الأخطاء، فاستخدمها بعد التأكد أن صيغتك صحيحة.
6. COUNTIF وCOUNTIFS: العدّ بشرط
| المطلوب | الصيغة |
|---|---|
| عدد الناجحين | =COUNTIF(C2:C31,">=50") |
| عدد الراسبين | =COUNTIF(C2:C31,"<50") |
| عدد الحاصلين على ممتاز (عمود التقدير D) | =COUNTIF(D2:D31,"ممتاز") |
| عدد الغائبين (مكتوب «غ») | =COUNTIF(C2:C31,"غ") |
| ممتازون في فصل «10/1» (شرطان) | =COUNTIFS(C2:C31,">=90",E2:E31,"10/1") |
قواعد كتابة الشرط:
- الشرط الذي فيه علامة مقارنة أو نص يُكتب بين " ": ">=50" و"ممتاز".
- الرقم وحده لا يحتاج علامات: 100.
- للمقارنة بخلية: ">="&K1 (فتغير حد النجاح في K1 فتتحدث كل النتائج).
- الرموز البديلة: * أي عدد من الحروف، و? حرف واحد. مثال: "محمد*" كل الأسماء التي تبدأ بمحمد.
7. SUMIF وSUMIFS: الجمع بشرط
SUMIF(range, criteria, [sum_range])
مثال: مجموع درجات الناجحين: =SUMIF(C2:C31,">=50")
SUMIFS(sum_range, criteria_range1, criteria1, ...)
مثال: مجموع درجات الناجحين في فصل 10/1: =SUMIFS(C2:C31,E2:E31,"10/1",C2:C31,">=50")
انتبه إلى خطأ شائع: في SUMIF يأتي نطاق الجمع آخرًا، أما في SUMIFS فيأتي أولًا.
8. AVERAGEIF وAVERAGEIFS: المتوسط بشرط
- متوسط درجات فصل 10/1:
=AVERAGEIF(E2:E31,"10/1",C2:C31) - متوسط الناجحين فقط:
=AVERAGEIF(C2:C31,">=50") - متوسط بشرطين:
=AVERAGEIFS(C2:C31,E2:E31,"10/1",D2:D31,"ممتاز")
إذا لم توجد أي خلية تحقق الشرط، يظهر #DIV/0!.
تطبيق: جدول تحليل نتائج الصف
| البيان | الصيغة |
|---|---|
| عدد الناجحين | =COUNTIF(C2:C31,">=50") |
| نسبة النجاح | =COUNTIF(C2:C31,">=50")/COUNT(C2:C31) |
| عدد الممتازين | =COUNTIF(C2:C31,">=90") |
| متوسط الناجحين | =AVERAGEIF(C2:C31,">=50") |
نسّق خلية نسبة النجاح بتنسيق النسبة المئوية (Ctrl+Shift+%).
الخلاصة
IF تتخذ القرار، وIFS تختصر الشروط الكثيرة، وCOUNTIF وSUMIF وAVERAGEIF تعدّ وتجمع وتحسب المتوسط بشرط. بهذه الدوال يصبح تحليل النتائج آليًا: غيّر الدرجات، وكل شيء يتحدث وحده.
تعليقات
إرسال تعليق
اكتب تعليقك هنا