Средневзвешенное в Excel (эксель): формула СУММПРОИЗВ

=СУММПРОИЗВ(B2:B5;C2:C5)/СУММ(C2:C5) считает средневзвешенное: каждая оценка из B умножается на свой вес из C, произведения складываются, и сумма делится на сумму весов. СУММПРОИЗВ по-английски называется SUMPRODUCT; в таблице ниже формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.

ФормулыСбросить

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%

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММПРОИЗВ(B2:B5;C2:C5)/СУММ(C2:C5)

Взвешенная оценка 81,2, а простое СРЗНАЧ (AVERAGE) даёт 80,75, потому что считает домашние задания с весом 20% так же, как итоговый экзамен с весом 30%. Измените оценку за итоговый экзамен, и взвешенная оценка сдвинется сильнее, чем при таком же изменении оценки за домашние задания.

В Excel нет функции WEIGHTED.AVERAGE, поэтому стандартная формула это СУММПРОИЗВ, делённая на СУММ. В Google Таблицах есть AVERAGE.WEIGHTED(B2:B5,C2:C5).

Как работает формула средневзвешенного

СУММПРОИЗВ построчно перемножает два диапазона и складывает результаты. Если расписать это во вспомогательном столбце, получится столбец произведений и их СУММ:

ФормулыСбросить

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

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММ(D2:D5)

Каждая часть даёт свою оценку, умноженную на вес: 85 × 20% это 17,0, 78 × 30% это 23,4 и так далее. В сумме получается 81,2. Веса в сумме дают 100%, поэтому деление на C6 здесь ничего не меняет, но именно оно сохраняет формулу верной, когда веса в сумме дают другое число.

Веса, которые в сумме не дают 100%

Веса не обязаны быть процентами. Средний балл (GPA) взвешивается по кредитным часам, средняя цена по количеству. Деление на СУММ весов работает с любой суммой.

ФормулыСбросить

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

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММПРОИЗВ(B2:B6;C2:C6)/СУММ(C2:C6)

Без деления формула вернула бы сумму баллов, умноженных на кредиты, здесь 49,2, а не средний балл. С делением F2 показывает средний балл, взвешенный по 14 кредитам. Курсы на четыре кредита тянут среднее к своим оценкам, а лабораторная на один кредит его почти не двигает: замените B6 на 2 и посмотрите, насколько меньше меняется F2 по сравнению с F3.

Если веса это проценты, которые в сумме дают ровно 100%, =СУММПРОИЗВ(B2:B5;C2:C5) без деления даёт тот же результат. Всё равно оставляйте /СУММ(...): в тот день, когда кто-то изменит вес и сумма станет 105%, формула без деления окажется неверной, и ничто на листе об этом не скажет.

Средневзвешенное с условием

Чтобы взвешивать только часть строк, умножьте на условие внутри СУММПРОИЗВ и сложите подходящие веса через СУММЕСЛИ. Ниже средняя цена по региону взвешена по проданному количеству.

ФормулыСбросить

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

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММПРОИЗВ((A2:A7=F2)*C2:C7*D2:D7)/СУММЕСЛИ(A2:A7;F2;D2:D7)

North продал 100 яблок, 60 груш и 40 слив, поэтому его средняя цена $1.45, она ближе к цене яблок, чем было бы простое среднее трёх цен. Условие (A2:A7=F2) равно 1 в строках North и 0 в остальных, поэтому другие строки ничего не добавляют в числитель, а СУММЕСЛИ складывает в знаменателе только количество 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.

ПроверитьПоказать ответ

Ошибки, из-за которых средневзвешенное получается неверным

• СРЗНАЧ произведений. =СРЗНАЧ(D2:D5) по столбцу оценка × вес делит на число строк, а не на веса, и даёт маленькое бессмысленное число. Делите СУММ произведений на СУММ весов.
• Деление на количество вместо весов. =СУММПРОИЗВ(B2:B6;C2:C6)/СЧЁТ(B2:B6) верно, только когда каждый вес равен 1.
• Диапазоны не совпадают по строкам. =СУММПРОИЗВ(B2:B6;C3:C7) сопоставляет каждое значение с весом следующей строки. Оба диапазона должны начинаться и заканчиваться на одних и тех же строках; диапазоны разного размера возвращают #ЗНАЧ! (по-английски #VALUE!).
• Пустой вес. Пустой вес считается нулём, поэтому строка молча выпадает. Если отсутствующий вес должен останавливать расчёт, сначала проверьте =СЧИТАТЬПУСТОТЫ(C2:C6).
• Среднее средних. Средние двух классов, 70 (10 учеников) и 90 (30 учеников), в среднем не дают 80. Взвесьте их по численности классов, и результат будет 85; на странице СРЗНАЧЕСЛИ та же ловушка с условиями.