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