Общая формула
=SUMPRODUCT(weights,values)/SUM(weights)
Резюме
Чтобы вычислить средневзвешенное значение, вы можете использовать функцию СУММПРОИЗВ вместе с функцией СУММ. В показанном примере формула в G5, скопированная ниже, имеет следующий вид:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
где веса - именованный диапазон I5: K5.
Объяснение
Средневзвешенное значение, также называемое средневзвешенным, - это среднее значение, при котором одни значения имеют большее значение, чем другие. Другими словами, некоторые значения имеют больший «вес». Мы можем вычислить средневзвешенное значение, умножив значения на среднее значение на соответствующие веса, а затем разделив сумму результатов на сумму весов. В Excel это можно представить с помощью приведенной ниже общей формулы, где веса и значения - это диапазоны ячеек:
=SUMPRODUCT(weights,values)/SUM(weights)
На показанном листе оценки за 3 теста отображаются в столбцах от C до E, а веса указаны в названном диапазоне весов (I5: K5). Формула в ячейке G5:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Работая изнутри, мы сначала используем функцию СУММПРОИЗВ, чтобы умножить веса на соответствующие оценки и суммировать результат:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
СУММПРОИЗВ сначала умножает соответствующие элементы двух массивов вместе, а затем возвращает сумму произведения:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Затем результат делится на сумму весов:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Поскольку формула копируется в столбец G, веса именованного диапазона I5: K5 не меняются, поскольку они ведут себя как абсолютная ссылка. Однако оценки в C5: E5, введенные в качестве относительной ссылки, обновляются в каждой новой строке. Результатом является средневзвешенное значение для каждого имени в списке, как показано. Среднее значение в столбце F рассчитано только для справки с помощью функции СРЕДНИЙ:
=AVERAGE(C5:E5)
Вес, не равный 1
В этом примере веса настроены на суммирование до 1, поэтому делитель всегда равен 1, а результатом является значение, возвращаемое функцией SUMPRODUCT. Однако приятной особенностью формулы является то, что веса не нужно суммировать до 1.
Например, мы могли бы использовать вес 1 для первых двух тестов и вес 2 для финала (поскольку финал вдвое важнее), и средневзвешенное значение будет таким же:
В ячейке G5 формула решается следующим образом:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Примечание: значения в фигурных скобках () выше - это диапазоны, выраженные в виде массивов.
Перенос веса
Функция СУММПРОИЗВ требует, чтобы измерения массива были совместимы. Если размеры несовместимы, СУММПРОИЗВ вернет ошибку #VALUE. В приведенном ниже примере веса такие же, как в исходном примере, но они указаны в вертикальном диапазоне:
Чтобы вычислить средневзвешенное значение по той же формуле, нам нужно «перевернуть» веса в горизонтальный массив с помощью функции TRANSPOSE следующим образом:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
После выполнения TRANSPOSE вертикальный массив:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
становится:
=(0.25,0.25,0.5) // horizontal array
И с этого момента формула ведет себя как раньше.
Подробнее: вертикальные и горизонтальные массивы.
