Когда мы вычисляем простое среднее для данного набора значений, предполагается, что все значения имеют одинаковый вес или важность.
Например, если вы явитесь на экзамены, и все экзамены имеют одинаковый вес, то среднее значение ваших общих оценок также будет средневзвешенным значением ваших оценок.
Однако в реальной жизни это не так.
Некоторые задачи всегда важнее других. Некоторые экзамены важнее других.
И вот где Средневзвешенное входит в картину.
Вот определение средневзвешенного значения из учебника:
Теперь давайте посмотрим, как рассчитать средневзвешенное значение в Excel.
Расчет средневзвешенного значения в Excel
В этом руководстве вы узнаете, как рассчитать средневзвешенное значение в Excel:
- Использование функции СУММПРОИЗВ.
- С помощью функции СУММ.
Итак, приступим.
Расчет средневзвешенного значения в Excel - функция СУММПРОИЗВ
Могут быть разные сценарии, когда вам нужно рассчитать средневзвешенное значение. Ниже приведены три различных ситуации, в которых вы можете использовать функцию СУММПРОИЗВ для расчета средневзвешенного значения в Excel.
Ниже приведены три различных ситуации, в которых вы можете использовать функцию СУММПРОИЗВ для расчета средневзвешенного значения в Excel.
Пример 1 - Когда веса в сумме составляют 100%
Предположим, у вас есть набор данных с оценками, полученными студентом на разных экзаменах, а также весами в процентах (как показано ниже):
В приведенных выше данных учащийся получает оценки по разным оценкам, но, в конце концов, ему нужно дать окончательный балл или оценку. Здесь невозможно рассчитать простое среднее значение, поскольку важность разных оценок различается.
Например, викторина с весом 10% имеет вдвое больший вес по сравнению с заданием, но на одну четверть веса по сравнению с экзаменом.
В таком случае вы можете использовать функцию СУММПРОИЗВ, чтобы получить средневзвешенное значение оценки.
Вот формула, которая даст вам средневзвешенное значение в Excel:
= СУММПРОИЗВ (B2: B8; C2: C8)
Вот как работает эта формула: Функция СУММПРОИЗВ в Excel умножает первый элемент первого массива на первый элемент второго массива. Затем он умножает второй элемент первого массива на второй элемент второго массива. И так далее…
И, наконец, он складывает все эти значения.
Вот иллюстрация, чтобы прояснить это.
Пример 2 - Когда веса не прибавляют до 100%
В приведенном выше случае веса были присвоены таким образом, чтобы общая сумма составляла 100%. Но в реальных жизненных сценариях это может быть не всегда.
Давайте посмотрим на один и тот же пример с разным весом.
В приведенном выше случае веса в сумме составляют 200%.
Если я воспользуюсь той же формулой СУММПРОИЗВ, результат будет неверным.
В приведенном выше результате я удвоил все веса, и он возвращает средневзвешенное значение как 153,2. Теперь мы знаем, что студент не может набрать больше 100 баллов из 100, независимо от того, насколько он хорош.
Причина в том, что веса не составляют в сумме 100%.
Вот формула, по которой это будет отсортировано:
= СУММПРОИЗВ (B2: B8; C2: C8) / СУММ (C2: C8)
В приведенной выше формуле результат СУММПРОИЗВ делится на сумму всех весов. Следовательно, несмотря ни на что, вес всегда будет составлять 100%.
Пример 3 - Когда необходимо рассчитать веса
В рассмотренном выше примере веса были указаны. Однако могут быть случаи, когда веса не доступны напрямую, и вам нужно сначала вычислить веса, а затем вычислить средневзвешенное значение.
Предположим, вы продаете три разных типа продуктов, как указано ниже:
Вы можете рассчитать средневзвешенную цену за товар с помощью функции СУММПРОИЗВ. Вот формула, которую вы можете использовать:
= СУММПРОИЗВ (B2: B4, C2: C4) / СУММ (B2: B4)
Разделение результата СУММПРОИЗВ на СУММ количеств гарантирует, что вес (в данном случае количества) в сумме составит 100%.
Расчет средневзвешенного значения в Excel - функция СУММ
Хотя функция СУММПРОИЗВ является лучшим способом вычисления средневзвешенного значения в Excel, вы также можете использовать функцию СУММ.
Чтобы вычислить средневзвешенное значение с помощью функции СУММ, вам необходимо умножить каждый элемент на присвоенную ему важность в процентах.
Используя тот же набор данных:
Вот формула, которая даст вам правильный результат:
= СУММ (B2 * C2, B3 * C3, B4 * C4, B5 * C5, B6 * C6, B7 * C7, B8 * C8)
Этот метод можно использовать, когда у вас есть пара предметов. Но когда у вас много предметов и весов, этот метод может быть громоздким и подверженным ошибкам. Есть более короткий и лучший способ сделать это с помощью функции СУММ.
Продолжая с тем же набором данных, вот короткая формула, которая даст вам средневзвешенное значение с использованием функции СУММ:
= СУММ (B2: B8 * C2: C8)
Уловка при использовании этой формулы состоит в том, чтобы использовать Control + Shift + Enter, а не просто Enter. Поскольку функция СУММ не может обрабатывать массивы, вам нужно использовать Control + Shift + Enter.
Когда вы нажмете Control + Shift + Enter, вы увидите, что фигурные скобки автоматически появятся в начале и в конце формулы (см. Строку формул на изображении выше).
Опять же, убедитесь, что вес составляет 100%. Если это не так, вам нужно разделить результат на сумму весов (как показано ниже на примере продукта):
