المحتويات
جعل Excel حساب متوسط خلايا متعددة مهمة سهلة للغاية - ما عليك سوى استخدام الوظيفة متوسط (معدل). ولكن ماذا لو كان لبعض القيم وزنا أكبر من غيرها؟ على سبيل المثال ، في العديد من الدورات التدريبية ، تحمل الاختبارات وزنًا أكبر من المهام. لمثل هذه الحالات ، من الضروري حساب متوسط الوزن.
لا يحتوي Excel على وظيفة لحساب المتوسط المرجح ، ولكن هناك وظيفة ستقوم بمعظم العمل نيابة عنك: SUMPRODUCT (منتج SUM). وحتى إذا لم تستخدم هذه الميزة من قبل ، فستستخدمها في نهاية هذه المقالة كمحترف. الطريقة التي نستخدمها تعمل في أي إصدار من Excel بالإضافة إلى جداول البيانات الأخرى مثل Google Sheets.
نحضر الجدول
إذا كنت ستحسب المتوسط المرجح ، فستحتاج إلى عمودين على الأقل. يحتوي العمود الأول (العمود B في مثالنا) على درجات كل مهمة أو اختبار. يحتوي العمود الثاني (العمود C) على الأوزان. تعني زيادة الوزن تأثيرًا أكبر للمهمة أو الاختبار على الدرجة النهائية.
لفهم ما هو الوزن ، يمكنك التفكير فيه كنسبة مئوية من درجتك النهائية. في الواقع ، هذا ليس هو الحال ، لأنه في هذه الحالة يجب أن تضيف الأوزان ما يصل إلى 100٪. الصيغة التي سنحللها في هذا الدرس ستحسب كل شيء بشكل صحيح ولا تعتمد على المقدار الذي تضيفه الأوزان.
ندخل الصيغة
الآن وقد أصبح جدولنا جاهزًا ، نضيف الصيغة إلى الخلية B10 (أي خلية فارغة ستفي بالغرض). كما هو الحال مع أي صيغة أخرى في Excel ، نبدأ بعلامة التساوي (=).
الجزء الأول من الصيغة هو الدالة SUMPRODUCT (منتج SUM). يجب وضع الحجج بين قوسين ، لذلك نفتحها:
=СУММПРОИЗВ(
=SUMPRODUCT(
بعد ذلك ، أضف وسيطات الدالة. SUMPRODUCT يمكن أن يحتوي (SUMPRODUCT) على وسيطات متعددة ، ولكن عادةً ما يتم استخدام اثنتين. في مثالنا ، ستكون الوسيطة الأولى عبارة عن نطاق من الخلايا. ب 2: ب 9أ الذي يحتوي على الدرجات.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
ستكون الوسيطة الثانية عبارة عن نطاق من الخلايا ج2:ج9التي تحتوي على الأوزان. يجب فصل هذه الوسائط بفاصلة منقوطة (فاصلة). عندما يكون كل شيء جاهزًا ، أغلق الأقواس:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
لنقم الآن بإضافة الجزء الثاني من الصيغة ، والذي سيقسم النتيجة المحسوبة على الدالة SUMPRODUCT (SUMPRODUCT) بمجموع الأوزان. سنناقش فيما بعد لماذا هذا مهم.
لإجراء عملية القسمة ، نواصل الصيغة التي تم إدخالها بالفعل مع الرمز / (شرطة مائلة مستقيمة) ، ثم اكتب الوظيفة مجموع (مجموع):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
للوظيفة مجموع (SUM) سنحدد وسيطة واحدة فقط - نطاق من الخلايا ج2:ج9. لا تنس إغلاق الأقواس بعد إدخال الوسيطة:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
مستعد! بعد الضغط على المفتاح أدخلسيقوم Excel بحساب المتوسط المرجح. في مثالنا ، ستكون النتيجة النهائية 83,6.
كيف يعمل
دعنا نقسم كل جزء من الصيغة ، بدءًا من الوظيفة SUMPRODUCT (SUMPRODUCT) لفهم كيفية عمله. دور SUMPRODUCT (SUMPRODUCT) يحسب منتج نقاط كل عنصر ووزنه ، ثم يجمع كل المنتجات الناتجة. بمعنى آخر ، تبحث الوظيفة عن مجموع المنتجات ، ومن هنا جاء الاسم. وذلك ل التكليفات 1 اضرب 85 في 5 ، ومن أجل الاختبار اضرب 83 في 25.
إذا كنت تتساءل عن سبب حاجتنا إلى ضرب القيم في الجزء الأول ، فتخيل أنه كلما زاد وزن المهمة ، زاد عدد المرات التي يتعين علينا فيها التفكير في الدرجة الخاصة بها. فمثلا، المهمة 2 عد 5 مرات و إمتحان نهائي - 45 مرة. لهذا إمتحان نهائي له تأثير أكبر على الدرجة النهائية.
للمقارنة ، عند حساب المتوسط الحسابي المعتاد ، تؤخذ كل قيمة في الاعتبار مرة واحدة فقط ، أي أن جميع القيم متساوية في الوزن.
إذا كان بإمكانك النظر تحت غطاء الوظيفة SUMPRODUCT (SUMPRODUCT) ، رأينا أنها في الواقع تؤمن بهذا:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
لحسن الحظ ، لسنا بحاجة إلى كتابة مثل هذه الصيغة الطويلة لأن SUMPRODUCT (SUMPRODUCT) يقوم بكل هذا تلقائيًا.
وظيفة في حد ذاتها SUMPRODUCT (SUMPRODUCT) يعيد لنا عددًا كبيرًا - 10450. في هذه المرحلة ، يتم تشغيل الجزء الثاني من الصيغة: / SUM (C2: C9) or / SUM (C2: C9)، والذي يعيد النتيجة إلى النطاق الطبيعي للنتائج ، مع إعطاء الإجابة 83,6.
الجزء الثاني من الصيغة مهم جدًا لأنه يسمح لك بتصحيح الحسابات تلقائيًا. تذكر أن الأوزان لا يجب أن تصل إلى 100٪؟ كل هذا بفضل الجزء الثاني من الصيغة. على سبيل المثال ، إذا قمنا بزيادة قيمة وزن واحدة أو أكثر ، فسيتم تقسيم الجزء الثاني من الصيغة ببساطة على القيمة الأكبر ، مما يؤدي مرة أخرى إلى الإجابة الصحيحة. أو يمكننا أن نجعل الأوزان أصغر بكثير ، على سبيل المثال عن طريق تحديد قيم مثل 0,5, 2,5, 3 or 4,5، وستظل الصيغة تعمل بشكل صحيح. إنه رائع ، أليس كذلك؟