В этом руководстве показаны два простых способа вычисления средневзвешенного значения в 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) — разные значения.
В Microsoft Excel средневзвешенное значение рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel делают большую часть работы за вас.
Если у вас есть базовое понимание этого, следующая формула вряд ли потребует каких-либо пояснений:
По сути, он выполняет те же вычисления, что и описанные выше, за исключением того, что вы указываете ссылки на ячейки вместо чисел.= СУММ (B2 * C2; B3 * C3; B4 * C4; B5 * C5; B6 * C6) / СУММ (C2: C6)
Посмотрите на изображение ниже — формула возвращает точно такой же результат, как и вычисления, которые мы провели минуту назад. Обратите внимание на разницу между нормальным средним значением, возвращаемым C8 AVERAGE, и средневзвешенным значением (C9).
Хотя эта формула очень проста и понятна, она не подходит, если вы хотите вычислить среднее значение большого количества элементов. В конце концов, вам нужно перечислить множество тем, что довольно скучно.
В этом случае лучше использовать функцию СУММПРОИЗВ. Подробнее об этом ниже.
он идеально подходит для нашей задачи, так как предназначен для сложения произведений чисел. И это именно то, что нам нужно.
Поэтому вместо того, чтобы отдельно умножать каждое число на его значение, вы предоставляете два массива в формуле СУММПРОИЗВ (в данном контексте массив представляет собой непрерывный диапазон ячеек), а затем делите результат на сумму весов:
Предполагая, что значения для усреднения находятся в ячейках B2: B6, а значения значимости находятся в ячейках C2: C6, наша формула будет выглядеть следующим образом:= СУММПРОИЗВ (диапазон_значений; диапазон_весов) / СУММ (диапазон_весов)
= СУММА ПРОДУКТ (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)
Это формула массива, не забудьте ввести ее, используя комбинацию клавиш CTRL + SHIFT + ENTER.{= СУММ (B2: B6 * C2: C6)}
Однако при использовании функции СУММ или СУММПРОИЗВ веса не обязательно должны быть 100%. Однако их необязательно выражать в процентах.= СУММПРОИЗВ (B2: B6; C2: C6)
Например, вы можете создать шкалу приоритета / важности и присвоить определенное количество баллов каждому элементу, как показано на следующем рисунке:
Понимаете, в данном случае мы обошлись без процентов.
Еще одна довольно распространенная проблема — как рассчитать средневзвешенную цену продукта. Допустим, мы получили 5 партий от разных поставщиков. Мы продадим его по фиксированной цене. Но чтобы это определить, нужно знать среднюю закупочную цену. В этом случае будет полезно рассчитать средневзвешенную цену. Посмотрите на этот простой пример. Я думаю, ты все понимаешь.
Следовательно, средневзвешенная цена значительно отличается от нормальной средней. На это повлияли 2 большие экспедиции по высокой цене. И мы применяем ту же формулу, что и при вычислении любого средневзвешенного значения. Мы умножаем цену на количество, складываем эти продукты, а затем делим на общее количество продукта.
Ну, это о формуле средневзвешенного значения в Excel.
