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

Среднее значение — лучший способ получить общее представление о данных. Но иногда среднее дает вам предвзятое значение. В таких ситуациях [каждый раз] лучший способ — рассчитать средневзвешенное значение.

И я обнаружил, что лучший способ — использовать формулу, объединив функции СУММПРОИЗВ и СУММ.

Этот метод прост и удобен в применении. Итак, сегодня в этой статье я хотел бы рассказать вам, как вычислить средневзвешенное значение в Excel, используя СУММПРОИЗВ и СУММ.

Разница между нормальным и средневзвешенным значением

В приведенном ниже примере у нас есть 1658 как нормальное среднее и 1445 как средневзвешенное. Позвольте мне прояснить эту разницу в двух моментах.

• Первое: если умножить 1658 (среднее) на 68 (количество), то получится 112742, что не равно общей сумме. Но если вы умножите 1445 (средневзвешенное значение) на 68 (количество), вы получите 98289, что соответствует общей сумме.
• Во-вторых: продукт-1 имеет самую низкую цену и самое большое количество, а продукт-4 имеет самую высокую цену и самое низкое количество.

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

Шаг для расчета средневзвешенного значения в Excel

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

Теперь выполните эти два простых шага.

• В ячейку C7 введите следующую формулу: =СУММПРОИЗВ(B2:B6,C2:C6)/СУММ(B2:B6).

• Нажмите ОК.

Теперь у вас есть 1445 как средневзвешенное значение цены и продукта. Как я уже сказал, СУММПРОИЗВ — это простой способ расчета средневзвешенного значения или средневзвешенного значения в Excel. Самое приятное в функции СУММПРОИЗВ — то, что она может умножать и складывать массив в одной ячейке.

 =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

Как это работает

Нам нужно разрушить эту формулу, чтобы понять ее.

Во-первых, СУММПРОИЗВ рассчитает произведение цены и количества для всех продуктов и вернет их сумму. После этого некоторые функции выдадут вам сумму количества. И в конце вы получите средневзвешенное значение, разделив значения обеих функций.

Проверьте это: СУММПРОДУКТИВ

Рассчитать взвешенное скользящее среднее

Пойдем немного дальше. Перейдем к части анализа данных. Используя ту же формулу, вы также можете рассчитать взвешенную скользящую среднюю.

Вот шаги:

• Введите формулу ниже в ячейку D4 и перетащите ее в конец. =СУММПРОИЗВ(B2:B4;C2:C4)/СУММ(C2:C4)

• Как только вы перетащите эту формулу, она рассчитает скользящее среднее на основе трех месяцев для каждого месяца.

Как это работает

Если вы посмотрите на снимок выше в ячейке D4, вы увидите скользящее среднее за январь, февраль и март. А в ячейке D5 указано скользящее среднее за февраль, март и апрель.

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