Как рассчитать средневзвешенное значение в Excel (формулы СУММ и СУММПРОИЗВ)
В учебнике показаны два простых способа вычисления средневзвешенного значения в Excel — с помощью функции СУММ или СУММПРОИЗВ.
В одной из предыдущих статей мы обсуждали три основные функции для вычисления среднего значения в Excel, которые очень просты и удобны в использовании. Но что, если некоторые значения имеют больший «вес», чем другие, и, следовательно, вносят больший вклад в итоговое среднее значение? В таких ситуациях вам необходимо рассчитать средневзвешенное значение.
Хотя Microsoft Excel не предоставляет специальной функции средневзвешенного значения, в нем есть несколько других функций, которые окажутся полезными в ваших вычислениях, как показано в следующих примерах формул.
Что такое средневзвешенное значение?
Средневзвешенное значение — это своего рода среднее арифметическое, в котором некоторые элементы набора данных имеют большее значение, чем другие. Другими словами, каждому усредняемому значению присваивается определенный вес.
Оценки учащихся часто рассчитываются с использованием средневзвешенного значения, как показано на следующем снимке экрана. Обычное среднее значение легко вычисляется с помощью функции Excel AVERAGE. Однако мы хотим, чтобы формула среднего учитывала вес каждого действия, указанного в столбце C.
В математике и статистике вы вычисляете средневзвешенное значение, умножая каждое значение в наборе на его вес, затем вы складываете произведения и делите сумму произведений на сумму всех весов.
В этом примере, чтобы рассчитать средневзвешенную оценку (общую оценку), вы умножаете каждую оценку на соответствующий процент (преобразованный в десятичную дробь), складываете 5 произведений вместе и делите это число на сумму 5 весов:
((91*0,1)+(65*0,15)+(80*0,2)+(73*0,25)+(68*0,3)) / (0,1+0,15+0,2+0,25+0,3)=73,5
Как видите, нормальный средний балл (75,4) и средневзвешенный (73,5) — разные значения.
Вычисление средневзвешенного значения в Excel
В Microsoft Excel средневзвешенное значение вычисляется с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel сделают большую часть работы за вас.
Вычисление средневзвешенного значения с помощью функции СУММ
Если у вас есть базовые знания о функции СУММ в Excel, приведенная ниже формула вряд ли потребует пояснений:
=СУММ(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6)/СУММ(C2:C6)
По сути, он выполняет те же вычисления, что и описанные выше, за исключением того, что вы указываете ссылки на ячейки вместо чисел.
Как вы можете видеть на снимке экрана, формула возвращает точно такой же результат, что и вычисление, которое мы сделали минуту назад. Обратите внимание на разницу между нормальным средним значением, возвращаемым функцией СРЗНАЧ (C8), и средневзвешенным значением (C9).
Хотя формула СУММ очень проста и понятна, она не подходит, если у вас есть большое количество элементов для усреднения. В этом случае лучше использовать функцию СУММПРОИЗВ, как показано в следующем примере.
Нахождение средневзвешенного значения с помощью SUMPRODUCT
Функция СУММПРОИЗВ в Excel идеально подходит для этой задачи, поскольку она предназначена для суммирования произведений, что нам и нужно. Итак, вместо того, чтобы умножать каждое значение на его вес по отдельности, вы предоставляете два массива в формуле СУММПРОИЗВ (в данном контексте массив — это непрерывный диапазон ячеек), а затем делите результат на сумму весов:
=СУММПРОИЗВ(значения_диапазон, диапазон_весов) / СУММ(диапазон_весов)
Предположим, что значения для усреднения находятся в ячейках B2:B6, а веса — в ячейках C2:C6, наша формула Sumproduct Weighted Average примет следующий вид:
=СУММПРОИЗВ(B2:B6, C2:C6) / СУММ(C2:C6)
Чтобы увидеть фактические значения за массивом, выберите его в строке формул и нажмите клавишу F9. Результат будет похож на этот:
Итак, функция СУММПРОИЗВ умножает 1-е значение в массиве1 на 1-е значение в массиве2 (91*0,1 в этом примере), затем умножает 2-е значение в массиве1 на 2-е значение в массиве2 (65*0,15 в этом примере). , и так далее. Когда все умножения выполнены, функция складывает произведения и возвращает эту сумму.
Чтобы убедиться, что функция СУММПРОИЗВ дает правильный результат, сравните ее с формулой СУММ из предыдущий пример и вы увидите, что числа идентичны.
При использовании функции СУММ или СУММПРОИЗВ для нахождения среднего веса в Excel вес не обязательно должен составлять 100%. Их также не нужно выражать в процентах. Например, вы можете составить шкалу приоритет/важность и присвоить каждому пункту определенное количество баллов, как показано на следующем снимке экрана:
Вот и все, что касается расчета средневзвешенного значения в Excel. Вы можете скачать образец электронной таблицы ниже и попробовать формулы на своих данных. В следующем уроке мы подробно рассмотрим расчет скользящей средней. Я благодарю вас за чтение и с нетерпением жду встречи с вами на следующей неделе!
Практическая рабочая тетрадь
Средневзвешенное значение Excel – примеры (файл .xlsx)
