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