حساب المتوسط المرجح في إكسل: دالة SUMPRODUCT بسهولة
تعلم طريقة حساب المتوسط المرجح في Excel باستخدام دوال SUMPRODUCT وSUM لحساب الدرجات والأسعار بدقة وتجنب أخطاء الدالة AVERAGE.
بقلم Ayoub Farid · · 2 دقائق قراءة
يتعامل المتوسط الحسابي البسيط مع جميع القيم بالتساوي. أما المتوسط المرجح (المعدل الموزون)، فيمنح بعض القيم وزناً وأهمية أكبر من غيرها؛ فالمادة الدراسية ذات الـ 4 ساعات معتمدة تؤثر أكثر من مادة بساعة واحدة، والمنتج الذي بعت منه 500 قطعة يؤثر على متوسط السعر أكثر بكثير من منتج بعت منه 5 قطع فقط.
المعادلة
=SUMPRODUCT(values, weights)/SUM(weights)مثال: حساب المعدل التراكمي للدرجات حسب الساعات المعتمدة
| A | B | C | |
|---|---|---|---|
| 1 | المادة | الدرجة | الساعات المعتمدة |
| 2 | الرياضيات | 85 | 4 |
| 3 | الفنون | 95 | 1 |
| 4 | التاريخ | 70 | 3 |
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)= (85×4 + 95×1 + 70×3) / 8 = 645 / 8 = 80.6
إذا استخدمت دالة المتوسط البسيطة =AVERAGE(B2:B4)، فستكون النتيجة 83.3؛ وهو رقم مضلل وغير دقيق لأنه رفع النتيجة اعتماداً على درجة مادة وزنها الأكاديمي ساعة واحدة فقط.
مثال: متوسط سعر البيع
لحساب متوسط السعر الفعلي المدفوع، مرجحاً بالكمية المباعة في جدول بيانات المبيعات:
=SUMPRODUCT(B2:B20,C2:C20)/SUM(C2:C20)الأوزان كنسب مئوية
إذا كان مجموع الأوزان يعادل 100% تماماً (مثل: الواجبات 20%، الامتحانات 50%، المشروع 30%)، يمكنك كتابة معادلة إكسل بدون عملية القسمة:
=SUMPRODUCT(B2:B4,C2:C4)ومع ذلك، ننصحك بالإبقاء على القسمة على SUM(C2:C4)؛ فهي تضمن دقة الناتج حتى لو حدث خطأ ولم يصل مجموع النسب إلى 100%.
أخطاء شائعة
- حساب متوسط المتوسطات: لا يساوي متوسط نتائج ثلاثة فصول دراسية متوسط المدرسة بأكملها، إلا إذا كان عدد الطلاب متساوياً تماماً في كل فصل.
- تحديد نطاقات بأحجام غير متطابقة: يؤدي اختلاف حجم مصفوفة القيم عن مصفوفة الأوزان إلى ظهور خطأ
#VALUE!. - الخلايا الفارغة في عمود الوزن: تعتبر الخلية الفارغة في عمود الأوزان مساوية للصفر (0)، مما يتجاهل احتساب هذا الصف نهائياً؛ لذا تحقق دائماً من البيانات المفقودة.
إذا كنت ترغب في إتقان معادلات إكسل و تعلم إكسل لاجتياز أي اختبار إكسل للتوظيف، تصفح شرحنا المفصل عن دالة SUMPRODUCT وكيفية تصميم حاسبة درجات ومعدل تراكمي متكاملة.
الأسئلة الشائعة
كيف أحسب المتوسط المرجح في Excel؟
باستخدام صيغة دوال إكسل التالية: =SUMPRODUCT(values,weights)/SUM(weights).
هل تتوفر دالة مخصصة باسم WEIGHTED AVERAGE في Excel؟
لا، لا توجد دالة جاهزة بهذا الاسم في Excel، وتعتبر قسمة دالة SUMPRODUCT على دالة SUM هي الطريقة القياسية المعتمدة.
ما الفرق بين المتوسط الحسابي البسيط والمتوسط المرجح؟
يتعامل المتوسط البسيط مع كل قيمة في النطاق بنفس الأهمية والوزن، بينما يمنح المتوسط المرجح لكل خلية وزناً ونسبة تأثير تختلف باختلاف أهميتها أو تكرارها.
هل تعمل هذه المعادلة في جداول بيانات Google Sheets؟
نعم، تعمل الصيغة نفسها بكفاءة. كما توفر Google Sheets دالة إضافية مدمجة لحسابها مباشرة وهي AVERAGE.WEIGHTED(values, weights).