المتوسط المرجح في الاكسل: صيغة SUMPRODUCT

تحسب =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) متوسطًا مرجحًا: تُضرب كل درجة في B في وزنها في C، وتُجمع حواصل الضرب، ويُقسم الإجمالي على مجموع الأوزان.

الصيغإعادة الضبط

F2fx

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

	A	B	C	D	E	F
1	Part	Score	Weight		Average	Result
2	Homework	85	20%		Weighted	81.2
3	Quizzes	78	30%		Plain AVERAGE	80.75
4	Midterm	72	20%
5	Final	88	30%

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

الدرجة المرجحة 81.2، بينما تعطي AVERAGE البسيطة 80.75، لأنها تعامل الواجبات، التي وزنها 20%، كأنها تساوي الاختبار النهائي، الذي وزنه 30%. غيّر درجة الاختبار النهائي فتتحرك الدرجة المرجحة أكثر مما تتحرك للتغيير نفسه في الواجبات.

ليس في الاكسل دالة WEIGHTED.AVERAGE، لذا فإن SUMPRODUCT مقسومة على SUM هي الصيغة المعتادة. وفي Google Sheets توجد AVERAGE.WEIGHTED(B2:B5,C2:C5).

كيف تعمل صيغة المتوسط المرجح

تضرب SUMPRODUCT النطاقين صفًا بصف وتجمع النتائج. وإذا كُتبت بعمود مساعد، فهي عمود من حواصل الضرب ومجموعها:

الصيغإعادة الضبط

D6fx

=SUM(D2:D5)

	A	B	C	D
1	Part	Score	Weight	Score x weight
2	Homework	85	20%	17.0
3	Quizzes	78	30%	23.4
4	Midterm	72	20%	14.4
5	Final	88	30%	26.4
6	Total		100%	81.2
7	Weighted average			81.2

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

يساهم كل جزء بدرجته مضروبة في وزنه: 85 × 20% تساوي 17.0، و78 × 30% تساوي 23.4، وهكذا. ومجموعها 81.2. ومجموع الأوزان 100%، لذا لا تغيّر القسمة على C6 شيئًا هنا، لكنها هي ما يُبقي الصيغة صحيحة عندما لا يكون المجموع كذلك.

أوزان لا مجموعها 100%

لا يجب أن تكون الأوزان نسبًا مئوية. فالمعدل التراكمي يُرجَّح بالساعات المعتمدة، ومتوسط السعر بالكمية. والقسمة على SUM الأوزان تتعامل مع أي مجموع.

الصيغإعادة الضبط

F2fx

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

	A	B	C	D	E	F
1	Course	Grade points	Credits		Average	Result
2	Math	4	4		Weighted GPA	3.51
3	History	3	3		Plain average	3.48
4	Biology	3.7	4		Total credits	14
5	Art	2.7	2
6	Lab	4	1

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

دون القسمة ستعيد الصيغة مجموع النقاط مضروبة في الساعات، 49.2 هنا، لا معدلًا تراكميًا. ومعها، تعرض F2 المعدل التراكمي المرجح بأربع عشرة ساعة معتمدة. تسحب المقررات ذات الساعات الأربع المتوسط نحو درجاتها، ولا يكاد المختبر ذو الساعة الواحدة يحركه: غيّر B6 إلى 2 ولاحظ مدى ضآلة تغيّر F2 مقارنة بالخلية F3.

إذا كانت أوزانك نسبًا مئوية مجموعها 100% بالضبط، فإن =SUMPRODUCT(B2:B5,C2:C5) وحدها تعطي النتيجة نفسها. ومع ذلك احتفظ بالجزء /SUM(...): ففي اليوم الذي يتغير فيه وزن ويصبح المجموع 105%، تكون الصيغة دونه خاطئة ولا شيء في الورقة يدل على ذلك.

المتوسط المرجح بشرط

لترجيح بعض الصفوف فقط، اضرب في شرط داخل SUMPRODUCT، واجمع الأوزان المطابقة بالدالة SUMIF. أدناه، يُرجَّح متوسط السعر لكل منطقة بالكمية المباعة.

الصيغإعادة الضبط

G2fx

=SUMPRODUCT((A2:A7=F2)*C2:C7*D2:D7)/SUMIF(A2:A7,F2,D2:D7)

	A	B	C	D	E	F	G
1	Region	Product	Price	Qty		Region	Average price
2	North	Apple	$1.20	100		North	$1.45
3	South	Pear	$1.50	40		South	$1.36
4	North	Pear	$1.50	60
5	South	Apple	$1.20	120
6	North	Plum	$2.00	40
7	South	Plum	$2.00	20

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

باعت North مئة تفاحة وستين حبة كمثرى وأربعين حبة برقوق، لذا متوسط سعرها $1.45، وهو أقرب إلى سعر التفاح مما سيكون عليه المتوسط البسيط للأسعار الثلاثة. يساوي الشرط (A2:A7=F2) القيمة 1 في صفوف North و0 في غيرها، لذا لا تضيف الصفوف الأخرى شيئًا إلى البسط، وتجمع SUMIF كميات North فقط في المقام.

تمرين: متوسط السعر المرجح

الصيغإعادة الضبط

F2fx

	A	B	C	D	E	F
1	Batch	Price	Qty		Average	Result
2	Jan	$4.20	100		Weighted price
3	Feb	$4.50	40
4	Mar	$3.90	250
5	Apr	$4.80	10
6	May	$4.10	120

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

دورك: اشتريت المنتج نفسه في خمس دفعات بأسعار مختلفة. احسب متوسط سعر الوحدة، مرجحًا بكمية كل دفعة. اكتب الصيغة في F2.

تحققعرض الإجابة

أخطاء تعطي متوسطًا مرجحًا خاطئًا

• المتوسط AVERAGE لحواصل الضرب. تقسم =AVERAGE(D2:D5) على عمود من الدرجة × الوزن على عدد الصفوف، لا على الأوزان، فتعطي رقمًا صغيرًا لا معنى له. اقسم مجموع حواصل الضرب على مجموع الأوزان.
• القسمة على العدد بدلًا من الأوزان. لا تصح =SUMPRODUCT(B2:B6,C2:C6)/COUNT(B2:B6) إلا عندما يكون كل وزن 1.
• نطاقات غير متحاذية. تقرن =SUMPRODUCT(B2:B6,C3:C7) كل قيمة بوزن الصف التالي. يجب أن يبدأ النطاقان وينتهيا في الصفوف نفسها؛ والأحجام المختلفة تعيد #VALUE!.
• وزن فارغ. يُحتسب الوزن الفارغ 0، فيُستبعد ذلك الصف بصمت. إذا كان يجب أن يوقف الوزن المفقود الحساب، فتحقق أولًا بالصيغة =COUNTBLANK(C2:C6).
• حساب متوسط المتوسطات. متوسطا صفين 70 (عشرة طلاب) و90 (ثلاثون طالبًا) لا يساويان في المتوسط 80. رجّحهما بحجم الصفين فتكون النتيجة 85؛ وفي صفحة AVERAGEIF الفخ نفسه مع الشروط.