Средневзвешенное значение — формула в Excel

В этом руководстве показаны два простых способа вычисления средневзвешенного значения в Excel с использованием функции СУММ или СУММПРОИЗВ).

В одной из предыдущих статей мы обсудили три основные функции усреднения в Excel, которые очень просты и удобны в использовании. 

Но что, если одни ценности более важны, чем другие, и поэтому больше влияют на итоговое среднее значение? Например, если вы участвуете в экзаменах, и все экзамены имеют одинаковый вес, они одинаково важны, тогда для вас важен средний балл.

Однако в реальной жизни это не всегда так. Например, если вы изучаете программирование, насколько важны для вас оценки по истории и физическому воспитанию по сравнению с оценками по основным предметам?

Некоторые задачи всегда важнее других. Некоторые экзамены важнее других.

В таких ситуациях вам просто нужно рассчитать средневзвешенное значение.

Хотя Microsoft Excel не предоставляет конкретной функции средневзвешенного значения, он предоставляет множество других, которые могут оказаться полезными в расчетах, как показано в примерах ниже.

Что такое средневзвешенное значение?

это своего рода среднее арифметическое, при котором одни элементы набора данных более значимы, чем другие. Другими словами, каждому тесту присваивается определенный вес.

Оценки учащихся часто рассчитываются с использованием этого подхода, как показано на следующем снимке экрана. Нормальное среднее легко вычисляется с помощью СРЕДНЕГО. Однако мы хотим, чтобы общая сумма учитывала важность каждого действия, указанного в столбце C.

В математике и статистике вы вычисляете средневзвешенное значение, умножая каждое число в наборе на его вес, затем складываете продукты и делите итоговые продукты на сумму всех весов.

В этом примере, чтобы вычислить средневзвешенное значение итоговой оценки, вы умножаете каждую полученную оценку на соответствующий процент (преобразованный в десятичную форму), складываете 5 произведений и делите это число на сумму пяти весов:

((91 * 0,1) + (85 * 0,05) + (80 * 0,2) + (73 * 0,25) + (68 * 0,4)) / (0,1 + 0,05 + 0,2 + 0,25 + 0,4) = 74,8

Обычное среднее (79,4) и средневзвешенное (74,8) — разные значения.

Формулы для средневзвешенного значения в Excel

В Microsoft Excel средневзвешенное значение рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel делают большую часть работы за вас.

Пример 1. Функция СУММ.

Если у вас есть базовое понимание этого, следующая формула вряд ли потребует каких-либо пояснений:

= СУММ (B2 * C2; B3 * C3; B4 * C4; B5 * C5; B6 * C6) / СУММ (C2: C6)

Посмотрите на изображение ниже — формула возвращает точно такой же результат, как и вычисления, которые мы провели минуту назад. Обратите внимание на разницу между нормальным средним значением, возвращаемым C8 AVERAGE, и средневзвешенным значением (C9).

Хотя эта формула очень проста и понятна, она не подходит, если вы хотите вычислить среднее значение большого количества элементов. В конце концов, вам нужно перечислить множество тем, что довольно скучно.

В этом случае лучше использовать функцию СУММПРОИЗВ. Подробнее об этом ниже.

Пример 2. Функция СУММПРОИЗВ

он идеально подходит для нашей задачи, так как предназначен для сложения произведений чисел. И это именно то, что нам нужно. 

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

= СУММПРОИЗВ (диапазон_значений; диапазон_весов) / СУММ (диапазон_весов)

= СУММА ПРОДУКТ (B2: B6; C2: C6) / СУММ (C2: C6)

Затем формула умножает 1-е число в массиве 1 на 1-е число в массиве 2 (91 * 0,1 в этом примере), затем умножает 2-е число в массиве 1 на 2-е число в массиве 2 (85 * 0,15) в этом примере) и скоро. Когда все умножения будут выполнены, Excel сложит продукты. Затем делим результат на сумму весов.

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

В нашем случае добавление весов дает 100%. То есть это всего лишь процент от общей суммы. В этом случае правильный результат также можно получить следующими способами:

= СУММ (B2 * C2; B3 * C3; B4 * C4; B5 * C5; B6 * C6)

{= СУММ (B2: B6 * C2: C6)}

= СУММПРОИЗВ (B2: B6; C2: C6)

Например, вы можете создать шкалу приоритета / важности и присвоить определенное количество баллов каждому элементу, как показано на следующем рисунке:

Понимаете, в данном случае мы обошлись без процентов.

Пример 3. Средневзвешенная цена.

Еще одна довольно распространенная проблема — как рассчитать средневзвешенную цену продукта. Допустим, мы получили 5 партий от разных поставщиков. Мы продадим его по фиксированной цене. Но чтобы это определить, нужно знать среднюю закупочную цену. В этом случае будет полезно рассчитать средневзвешенную цену. Посмотрите на этот простой пример. Я думаю, ты все понимаешь.

Следовательно, средневзвешенная цена значительно отличается от нормальной средней. На это повлияли 2 большие экспедиции по высокой цене. И мы применяем ту же формулу, что и при вычислении любого средневзвешенного значения. Мы умножаем цену на количество, складываем эти продукты, а затем делим на общее количество продукта.

Ну, это о формуле средневзвешенного значения в Excel.