حساب الدرجات في إكسل: المتوسطات والأوزان والتقديرات
تعلم كيفية إنشاء حاسبة درجات في إكسل: حساب المعدل الموزون بدالة SUMPRODUCT، وتحديد التقديرات بالأحرف، وإحصاءات الفصل.
بقلم Ayoub Farid · · 2 دقائق قراءة
يُعد تصميم حاسبة الدرجات مشروعاً تطبيقياً ممتازاً للطلاب والمعلمين، كما أنه تمرين كلاسيكي في أي اختبار إكسل للتوظيف. يجمع هذا التطبيق بين حساب المتوسطات الموزونة، ودوال الشرط IF، ودوال البحث، والإحصاء والعدّ.
الخطوة 1: جدول الدرجات
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | الطالب | الواجبات | منتصف الفصل | النهائي | المجموع |
| 2 | Ana | 88 | 76 | 82 | |
| 3 | Ben | 95 | 89 | 91 |
ضع الأوزان النسبية في صف أعلى الجدول أو بجانبه، مثلاً النطاق G2:I2 يحتوي على: 20%، 30%، 50%.
الخطوة 2: المجموع الموزون
في الخلية E2، اكتب المعادلة التالية:
=SUMPRODUCT(B2:D2,$G$2:$I$2)استخدم المرجع المطلق لتثبيت نطاق الأوزان النسبية حتى يُطبق بدقة على جميع الطلاب عند سحب المعادلة للأسفل. بالنسبة للطالبة Ana: 88×0.2 + 76×0.3 + 82×0.5 = 81.4. للمزيد، راجع المتوسط الموزون في إكسل.
الخطوة 3: التقدير بالأحرف
باستخدام دالة IFS:
=IFS(E2>=90,"A",E2>=80,"B",E2>=70,"C",E2>=60,"D",TRUE,"F")أو عبر جدول بحث مرجعي منفصل (وهو الخيار الأسهل للتعديل لاحقاً):
| K | L | |
|---|---|---|
| 1 | الحد الأدنى | التقدير |
| 2 | 0 | F |
| 3 | 60 | D |
| 4 | 70 | C |
| 5 | 80 | B |
| 6 | 90 | A |
=VLOOKUP(E2,$K$2:$L$6,2,TRUE)تعرف على المزيد حول دالة IFS ودالة IF المتداخلة.
الخطوة 4: تحديد حالة النجاح أو الرسوب
=IF(E2>=60,"Pass","Fail")الخطوة 5: إحصاءات درجات الفصل
- متوسط الفصل:
=AVERAGE(E2:E31) - الوسيط:
=MEDIAN(E2:E31) - أعلى درجة / أقل درجة:
=MAX(E2:E31)/=MIN(E2:E31) - عدد الطلاب الحاصلين على تقدير A:
=COUNTIF(F2:F31,"A") - نسبة النجاح العامة:
=COUNTIF(G2:G31,"Pass")/COUNTA(G2:G31) - ترتيب الطالب:
=RANK.EQ(E2,$E$2:$E$31)(راجع شرح دالة RANK)
معالجة الدرجات المفقودة أو الغياب
تُعامل الخلية الفارغة كصفر تلقائياً عند استخدام دالة SUMPRODUCT. لتمييز الصفوف غير المكتملة وتجنب الحساب الخاطئ، يمكنك استخدام معادلات Excel كالتالي: =IF(COUNT(B2:D2)<3,"Incomplete",...).
الأسئلة الشائعة
كيف أحسب الدرجة الموزونة في إكسل؟
باستخدام الصيغة =SUMPRODUCT(scores,weights)، مع التأكد من أن مجموع الأوزان يساوي 100%.
كيف أحوّل الدرجات الرقمية إلى تقديرات بالحروف؟
يمكنك استخدام دالة IFS، أو دوال IF المتداخلة، أو تطبيق دالة VLOOKUP مع التطابق التقريبي بناءً على جدول للحدود الدنيا للدرجات.
كيف أحسب متوسط الفصل بالكامل؟
باستخدام صيغة المتوسط =AVERAGE(range) وتطبيقها على عمود المجموع النهائي.
هل تعمل هذه المعادلات في Google Sheets؟
نعم، تعمل جميع دوال إكسل المذكورة في جداول بيانات Google Sheets بالطريقة نفسها تماماً.