Если кто-то попросит меня найти среднее из пяти значений — 1, 4, 7, 8 и 10 — уравнение будет простым. Я складываю все пять значений и делю это на общее количество значений.

Выглядит это так: (1 + 4+ 7+ 8+ 10)/5

Посчитаем, и мы получим в среднем 6. Просто, правда?

Что произойдет теперь, если одна из этих ценностей окажется важнее или «весит» больше остальных? Простое среднее значение не будет отражать это значение, поскольку оно присваивает всем значениям одинаковый вес. Хотя я мог бы проделать тяжелую работу на бумаге, чтобы правильно взвесить значения, есть более простой способ: формула средневзвешенного значения.

В этой статье я расскажу, как использовать эту формулу в Excel, приведу несколько примеров и рассмотрю аналогичную формулу: взвешенное скользящее среднее.

Оглавление

Когда использовать средневзвешенное значение

Используйте средневзвешенное значение, когда значения имеют разное значение. Но что это на самом деле означает?

Вот пример. Допустим, я хочу купить новый дом, но не уверен, какова его средняя рыночная стоимость в моем районе. Мой бюджет составляет 350 000 долларов, поэтому я смотрю на цены пяти разных домов:

• 1 000 000 долларов США
• 800 000 долларов США
• 400 000 долларов США
• 300 000 долларов США
• 250 000 долларов США

Если я воспользуюсь простой формулой среднего, я получу 550 000 долларов, что значительно выходит за рамки моего ценового диапазона. Проблема? Мой средний показатель неправильный. Это потому, что я не учел, сколько домов продано в каждой ценовой категории. Вот еще раз список, но в скобках указано количество домов, проданных по этой цене.

• 1 000 000 долларов США (1)
• 800 000 долларов США (2)
• 400 000 долларов США (10)
• 300 000 долларов США (25)
• 250 000 долларов США (15)

Используя формулу средневзвешенного значения, я могу принять во внимание, что только один дом продается за миллион долларов, в то время как в 25 раз больше домов стоят по цене 300 000 долларов. Используя формулу средневзвешенного значения, я получаю в среднем 336 792 доллара, что как раз для меня.

Это похоже на волшебство, да? Вот как это работает.

Как рассчитать средневзвешенное значение в Excel

Чтобы вычислить средневзвешенное значение в Excel, используйте функции СУММПРОИЗВ и СУММ в следующей формуле:

=СУММПРОИЗВ(X:X;X:X)/СУММ(X:X)

Эта формула работает путем умножения каждого значения на его вес и объединения значений. Затем вы делите ОБЩИЙ ПРОДУКТ на сумму весов вашего средневзвешенного значения.

Все еще в замешательстве? Давайте пройдемся по шагам в следующем разделе.

Использование СУММПРОИЗВ для расчета средневзвешенного значения в Excel

Вот мои шаги по использованию СУММПРОИЗВ.

1. Я ввожу свои данные в электронную таблицу, а затем добавляю столбец, содержащий вес для каждой точки данных.

2. Затем я набираю =СУММПРОИЗВ, чтобы запустить формулу и ввести значения.

3. Наконец я нажимаю Enter, чтобы получить результаты.

Вот что происходит под капотом.

Сначала уравнение умножает каждый балл на вес:

• 86 х 15 = 1290
• 55 х 10 = 550
• 78 х 20 = 1560
• 92 х 15 = 1380
• 64 х 40 = 2560

Затем он объединяет эти значения:

• 1290 + 550 + 1560 + 1380 + 2560 = 7340

Наконец, уравнение делит объединенное значение на общее значение наших весов:

• 7340 / (15 + 10 + 20 + 15 + 40) = 73,4

Как найти взвешенные скользящие средние в Excel

Полезным вариантом взвешенной скользящей средней является взвешенная скользящая средняя.

Когда я использую взвешенное скользящее среднее, я могу рассчитать среднее значение за определенный период времени, даже если добавляю новые данные или придаю больший вес определенным значениям. Это может помочь легче выявлять тенденции и закономерности.

Например, если я знаю, сколько просмотров мой сайт получил за последние пять дней, я могу легко определить среднее количество просмотров за пятидневный период.

На следующей неделе мне нужно то же значение, но за последние пять дней, а не за пять дней прошлой недели. Это означает, что я использую то же количество времени, но обновляю данные, чтобы получить среднее значение, учитывающее новые данные.

Чтобы найти взвешенное скользящее среднее, вы придаете больший вес значениям, зависящим от времени.

В приведенном выше примере я назначаю вес просмотров сайта на основе давности. Более поздним мнениям (те, что произошли вчера) придается больший вес, чем тем, которые произошли пять дней назад. Это означает, что каждый день, когда я рассчитываю по скользящему среднему, веса меняются.

Вот как это выглядит:

За первые пять дней я получаю 100, 200, 150, 300 и 100 просмотров. Каждому из этих дней я присваиваю вес, причем последний день имеет самый высокий вес. Для простоты я буду использовать веса, сумма которых равна 100.

• День 1: 10 (х 100)
• День 2: 15 (х 200)
• День 3: 15 (х 150)
• День 4: 25 (х 300)
• День 5: 35 (х 100)

Чтобы получить средневзвешенное значение, я использую формулу из предыдущего раздела. Я умножаю каждое значение на вес и делю на сумму весов. Для перечисленных значений получаем: 172,5

На шестой день я снова запускаю расчет взвешенных ходов с новыми числами. Наш предыдущий день 1 больше не актуален — он был заменен значениями дня 2, который теперь является нашим днем ​​1. У нас также есть новый набор данных за день 6 (всего), который теперь является нашим днем ​​5. .

Это означает, что применяются все итоговые значения за дни 2, 3, 4 и 5 – они просто смещаются на один день влево. Тем временем наш новый День 5 набрал 200 просмотров. Наши весы говорят то же самое; все изменения касаются количества просмотров, поскольку они относятся к последним пяти дням.

Используя наши новые данные, наше взвешенное скользящее среднее рассчитывается следующим образом:

• День 1: 10 (х 200)
• День 2: 15 (х 150)
• День 3: 15 (х 300)
• День 4: 25 (х 100)
• День 5: 35 (х 200)

В результате мы получаем новое среднее значение, которое составляет: 182,5.

В Excel вы должны ввести эту формулу вручную в каждую применимую ячейку.

WMA = [value 1 x (weight)] + [value 2 x (weight)] + [value 3 x (weight)] + [value 4 x (weight)] + [value 5 x (weight)] / общий вес

Лучше, чем в среднем: Мастер операций Excel

Я думаю, что как только вы освоитесь, использовать формулу средневзвешенного значения будет довольно легко. Все, что нужно, — это немного практики. Хотя взвешенное скользящее среднее немного сложнее, это отличный способ отслеживать данные о производительности с течением времени.

Но это только верхушка айсберга Excel. Попрактиковавшись и воспользовавшись небольшой помощью нашего руководства по взломам Excel, вы сможете овладеть искусством уравнений. Проверьте это ниже.

Примечание редактора: этот пост был первоначально опубликован в апреле 2022 года и обновлен для полноты.