Как рассчитать средневзвешенное значение в Excel
Обновлено: 30.06.2024
Вы когда-нибудь задумывались, зачем нам нужно среднее или взвешенное значение? В нашем ежедневном мы не можем все рассчитать, допустим, я спрашиваю, как погода на неделю, вы не будете говорить мне температуру каждого дня, но оценка здесь сводится к понятию Среднего. Математически вы можете оценить среднее значение = сумма значений/количество значений, и значения имеют одинаковый вес.
Теперь давайте рассмотрим еще один пример: вы учитесь в университете и получили результат семестрового экзамена. Поскольку семестр состоит из минора и майора, где минор имеет вес 25%, а майор - 75% веса, как теперь вы будете рассчитывать свое среднее значение? Итак, вот понятие среднего веса, когда мы учитываем вес каждого значения.
Как рассчитать средневзвешенное значение в Excel?
Чтобы вычислить средневзвешенное значение, мы будем использовать две функции в Excel: первая – функция суммы, а вторая – функция СУММПРОИЗВ, давайте обсудим ее по очереди.
Функция СУММ
Это встроенная в Excel функция, помогающая суммировать числовые данные в диапазоне ячеек. Эта функция позволяет нам очень легко вычислять несколько данных.
Например, вы покупаете учебники по естествознанию для 12 класса для пожертвования НПО, поэтому теперь вам нужно подсчитать общее количество книг, поэтому здесь вы просто используете функцию суммы.
Функция СУММПРОИЗВ
Это встроенная функция в Excel, она возвращает сумму произведений значения, указанного в аргументе или массиве. Эта функция обычно используется, когда вам нужно перемножить много ячеек вместе.
Например, вы покупаете учебники для 12 класса для пожертвования НПО, поэтому теперь вам нужно рассчитать общую стоимость. Итак, вот эта функция пригодится, вы сначала вводите массив 1 и ставите запятую, а затем снова массив 2, а затем нажимаете ввод, вы получите свой результат, который равен 5780.
Теперь мы разобрались с концепцией как функции СУММ, так и функции СУММПРОИЗВ, теперь давайте рассчитаем среднее взвешенное значение. Таким образом, в Excel для расчета среднего веса используется СУММПРОИЗВ/СУММ.
Давайте разберемся на примере: вам нужно рассчитать свои оценки в колледже, но по каждому предмету разное среднее значение. Таким образом, мы можем использовать среднее значение веса для расчета наших средних оценок. Итак, теперь поместите значения в формулу, которая выглядит следующим образом:
В одной из предыдущих статей мы обсуждали три основные функции для расчета среднего значения в Excel, которые очень просты и удобны в использовании. Но что, если некоторые значения имеют больший «вес», чем другие, и, следовательно, вносят больший вклад в итоговое среднее значение? В таких ситуациях вам потребуется рассчитать средневзвешенное значение.
Хотя в Microsoft Excel нет специальной функции средневзвешенного значения, в нем есть несколько других функций, которые окажутся полезными в ваших расчетах, как показано в следующих примерах формул.
Что такое средневзвешенное значение?
Среднее взвешенное значение – это своего рода среднее арифметическое, при котором некоторые элементы набора данных имеют большее значение, чем другие. Другими словами, каждому усредняемому значению присваивается определенный вес.
Оценки учащихся часто рассчитываются с использованием средневзвешенного значения, как показано на следующем снимке экрана. Обычное среднее значение легко вычисляется с помощью функции Excel AVERAGE. Однако мы хотим, чтобы формула среднего учитывала вес каждого действия, указанного в столбце C.
В математике и статистике средневзвешенное значение рассчитывается путем умножения каждого значения в наборе на его вес, затем вы складываете произведения и делите сумму произведений на сумму всех весов.
В этом примере, чтобы рассчитать средневзвешенную оценку (общую оценку), вы умножаете каждую оценку на соответствующий процент (преобразованный в десятичную дробь), складываете 5 произведений и делите это число на сумму 5. веса:
Как видите, нормальная средняя оценка (75,4) и средневзвешенная оценка (73,5) — разные значения.
Вычисление средневзвешенного значения в Excel
В Microsoft Excel средневзвешенное значение рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel сделают большую часть работы за вас.
Пример 1. Вычисление средневзвешенного значения с помощью функции СУММ
Если у вас есть базовые знания о функции СУММ в Excel, приведенная ниже формула вряд ли потребует каких-либо объяснений:
=СУММ(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6)/СУММ(C2:C6)
По сути, он выполняет те же вычисления, что и описанные выше, за исключением того, что вы указываете ссылки на ячейки вместо чисел.
Как видно на снимке экрана, формула возвращает точно такой же результат, как и вычисление, которое мы сделали минуту назад. Обратите внимание на разницу между нормальным средним значением, возвращаемым функцией СРЗНАЧ (C8), и средневзвешенным значением (C9).
Несмотря на то, что формула СУММ очень проста и понятна, она не подходит, если нужно усреднить большое количество элементов. В этом случае лучше использовать функцию СУММПРОИЗВ, как показано в следующем примере.
Пример 2. Нахождение средневзвешенного значения с помощью функции СУММПРОИЗВ
Функция СУММПРОИЗВ в Excel идеально подходит для этой задачи, поскольку она предназначена для суммирования произведений, что нам и нужно. Итак, вместо того, чтобы умножать каждое значение на его вес по отдельности, вы предоставляете два массива в формуле СУММПРОИЗВ (в данном контексте массив — это непрерывный диапазон ячеек), а затем делите результат на сумму весов:
Предполагая, что значения для усреднения находятся в ячейках B2:B6, а веса — в ячейках C2:C6, наша формула Sumproduct Weighted Average примет следующий вид:
=СУММПРОИЗВ(B2:B6, C2:C6) / СУММ(C2:C6)
Чтобы увидеть фактические значения массива, выберите его в строке формул и нажмите клавишу F9. Результат будет примерно таким:
Итак, функция СУММПРОИЗВ умножает 1-е значение в массиве1 на 1-е значение в массиве2 (91*0,1 в этом примере), а затем умножает 2-е значение в массиве1 на 2-е значение в массиве2 ( 65*0,15 в этом примере) и так далее. Когда все умножения выполнены, функция складывает произведения и возвращает эту сумму.
Чтобы убедиться, что функция СУММПРОИЗВ дает правильный результат, сравните ее с формулой СУММ из предыдущего примера, и вы увидите, что числа идентичны.
При использовании функции СУММ или СУММПРОИЗВ для нахождения среднего веса в Excel сумма весов не обязательно должна составлять 100 %. Их также не нужно выражать в процентах. Например, вы можете составить шкалу приоритет/важность и присвоить каждому элементу определенное количество баллов, как показано на следующем снимке экрана:
Ну вот и все, что касается расчета средневзвешенного значения в Excel. Вы можете скачать электронную таблицу взвешенных средних и попробовать формулы на своих данных. В следующем уроке мы подробно рассмотрим расчет скользящей средней. Я благодарю вас за чтение и с нетерпением жду встречи с вами на следующей неделе!
Вас также может заинтересовать
37 комментариев к "Как рассчитать средневзвешенное значение в Excel"
Привет,
Можете ли вы объяснить мне эту ситуацию, когда у меня есть годовые данные для каждого конкретного блюда с 21 января по 21 декабря на ежедневном уровне.
Теперь каждое блюдо доступно по разным ценам. дней в неделю. Например, курица в масле будет доступна все 7 дней в неделю, но, возможно, сэндвич с индейкой будет доступен только два раза в неделю.
Как рассчитать средневзвешенное дневное значение для каждого блюда.
Здравствуйте!
Формула расчета средневзвешенного значения может быть совершенно разной в зависимости от того, как выглядят ваши данные. Если в столбце B указано количество, а в столбце C – цена, формула может выглядеть так:
=СУММПРОИЗВ(B2:B53, C2:C53) / СУММ(C2:C53)
Если это не то, что вы хотели, опишите проблему более подробно.
Когда мы вычисляем простое среднее значение заданного набора значений, предполагается, что все значения имеют одинаковый вес или важность.
Например, если вы явились на экзамены, и все экзамены имеют одинаковый вес, то среднее значение ваших общих оценок также будет средневзвешенным значением ваших баллов.
Однако в реальной жизни это вряд ли так.
Некоторые задачи всегда важнее других. Некоторые экзамены важнее других.
И здесь на помощь приходит средневзвешенное значение.
Вот определение средневзвешенного значения из учебника:
Теперь давайте посмотрим, как рассчитать средневзвешенное значение в Excel.
Это руководство охватывает:
Вычисление средневзвешенного значения в Excel
В этом руководстве вы узнаете, как рассчитать средневзвешенное значение в Excel:
- Использование функции СУММПРОИЗВ.
- Использование функции СУММ.
Итак, приступим.
Вычисление средневзвешенного значения в Excel — функция СУММПРОИЗВ
Возможны различные сценарии, в которых вам необходимо рассчитать средневзвешенное значение. Ниже приведены три различные ситуации, когда вы можете использовать функцию СУММПРОИЗВ для вычисления средневзвешенного значения в Excel
Ниже приведены три различные ситуации, когда вы можете использовать функцию СУММПРОИЗВ для вычисления средневзвешенного значения в Excel
Пример 1. Когда веса в сумме составляют 100 %
Предположим, у вас есть набор данных с оценками, полученными учащимся на разных экзаменах, а также весами в процентах (как показано ниже):
В приведенных выше данных учащийся получает оценки по разным оценкам, но в конце концов ему нужно дать окончательный балл или оценку. Здесь невозможно рассчитать простое среднее значение, так как важность различных оценок различается.
Например, викторина с весом 10 % имеет вдвое больший вес по сравнению с заданием, но на четверть веса по сравнению с экзаменом.
В таком случае вы можете использовать функцию СУММПРОИЗВ, чтобы получить средневзвешенное значение оценки.
Вот формула, которая даст вам средневзвешенное значение в Excel:
Вот как работает эта формула: Функция СУММПРОИЗВ Excel умножает первый элемент первого массива на первый элемент второго массива. Затем он умножает второй элемент первого массива на второй элемент второго массива. И так далее..
И, наконец, добавляются все эти значения.
Вот иллюстрация, чтобы было понятно.
Пример 2 — когда веса не дают в сумме 100 %
В приведенном выше случае веса были присвоены таким образом, что общая сумма составила 100%. Но в реальных сценариях это может быть не всегда так.
Давайте посмотрим на тот же пример с разными весами.
В приведенном выше случае вес в сумме составляет 200%.
Если я использую ту же формулу СУММПРОИЗВ, это даст мне неверный результат.
В приведенном выше результате я удвоил все веса, и он возвращает средневзвешенное значение как 153,2. Теперь мы знаем, что учащийся не может получить больше 100 баллов из 100, какими бы блестящими он ни был.
Причина этого в том, что веса не дают в сумме 100%.
Вот формула для сортировки:
В приведенной выше формуле результат СУММПРОИЗВ делится на сумму всех весов. Следовательно, несмотря ни на что, вес всегда будет составлять 100%.
Одним из практических примеров использования различных весов является расчет компаниями средневзвешенной стоимости капитала. Например, если компания привлекла капитал с помощью заемных средств, собственного капитала и привилегированных акций, то они будут обслуживаться по другой цене. Затем бухгалтерия компании рассчитывает средневзвешенную стоимость капитала, которая представляет собой стоимость капитала для всей компании.
Пример 3. Когда необходимо рассчитать веса
В рассмотренном примере были указаны веса. Однако могут быть случаи, когда веса недоступны напрямую, и вам нужно сначала вычислить веса, а затем вычислить средневзвешенное значение.
Предположим, вы продаете три разных типа продуктов, как указано ниже:
Вы можете рассчитать средневзвешенную цену за продукт с помощью функции СУММПРОИЗВ. Вот формула, которую вы можете использовать:
Деление результата СУММПРОИЗВ на СУММУ количеств гарантирует, что вес (в данном случае количество) составит 100%.
Вычисление средневзвешенного значения в Excel — функция SUM
Хотя функция СУММПРОИЗВ — лучший способ расчета средневзвешенного значения в Excel, вы также можете использовать функцию СУММ.
Чтобы рассчитать средневзвешенное значение с помощью функции СУММ, необходимо умножить каждый элемент на присвоенную ему важность в процентах.
Используя тот же набор данных:
Вот формула, которая даст правильный результат:
Этот метод подходит для использования, когда у вас есть несколько элементов. Но когда у вас много предметов и весов, этот метод может быть громоздким и подверженным ошибкам. Есть более короткий и лучший способ сделать это с помощью функции СУММ.
Продолжая тот же набор данных, вот короткая формула, которая даст вам средневзвешенное значение с помощью функции СУММ:
Хитрость при использовании этой формулы заключается в том, чтобы использовать Control + Shift + Enter, а не просто Enter. Поскольку функция СУММ не может работать с массивами, вам нужно использовать Control + Shift + Enter.
Когда вы нажмете Ctrl + Shift + Enter, в начале и в конце формулы автоматически появятся фигурные скобки (см. панель формул на изображении выше).
Снова убедитесь, что веса в сумме составляют 100 %. Если это не так, вам нужно разделить результат на сумму весов (как показано ниже на примере продукта):
Чтобы вычислить средневзвешенное значение в Excel, просто используйте СУММПРОИЗВ и СУММ.
<р>1. Во-первых, приведенная ниже функция СРЗНАЧ вычисляет нормальное среднее трех значений.
Предположим, ваш учитель говорит: "Тест оценивается в два раза больше, чем тест, а выпускной экзамен оценивается в три раза больше, чем тест".
<р>2. Ниже вы можете найти соответствующие веса оценок.
Приведенная ниже формула вычисляет средневзвешенное значение этих оценок.
| Средневзвешенная WA | = | 20 + 40 + 40 + 90 + 90 + 90 |
| = | 370 | = | 61,67 |
| 6 |
Примечание: функция СУММПРОИЗВ выполняет следующий расчет: (20 * 1) + (40 * 2) + (90 * 3) = 370.
<р>4. Мы можем использовать функцию СУММ в Excel, чтобы вычислить число под дробной чертой (6).
Читайте также: