Как посчитать средневзвешенное значение в excel
Средневзвешенные значения
Для того, чтобы рассчитать средневзвешенные значения по каким-либо параметрам, необходимо воспользоваться конструкцией из 2-х основных функций в EXCEL.
В примере ведется расчет среднего значения удельных затрат по логистике на одну товарную позицию. Для этого:
1. Вводится функция СУММПРОИЗВ, в аргументах которой вводятся 2 диапазона — B4:B6 с логистическими затратами на одну позицию в регионах и C4:C6 с указанием долей отгрузок по регионам.
2. Далее в строке формул после готовой формулы СУММПРОИЗВ(B4:B6;C4:C6) вводим «/» и делим на сумму «СУММ» самих долей с диапазоном суммирования C4:C6.
Полученное значение будет средневзвешенным, и его можно будет использовать для включения в себестоимость.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Как использовать формулу средневзвешенного ЕСЛИ в Excel
Вы можете использовать следующий синтаксис в Excel, чтобы применить формулу средневзвешенного ЕСЛИ:
=SUMPRODUCT(--( A2:A7 ="A"), B2:B7 , C2:C7 )/SUMIF( A2:A7 , "A", C2:C7 ) Эта формула вычисляет средневзвешенное значение значений в диапазоне B2:B7 , используя C2:C7 в качестве весов, только для ячеек, где A2:A7 равны «A».
В следующем примере показано, как использовать эту формулу на практике.
Пример: формула средневзвешенного ЕСЛИ в Excel
Во-первых, давайте введем следующие данные, которые показывают баллы двух студентов (Студент A и Студент B) на трех разных экзаменах:
Далее мы будем использовать следующую формулу для расчета средневзвешенного балла экзамена только для студента А:
=SUMPRODUCT(--( A2:A7 ="A"), B2:B7 , C2:C7 )/SUMIF( A2:A7 , "A", C2:C7 ) На следующем снимке экрана показано, как использовать эту формулу на практике:
Средневзвешенное значение экзаменационных баллов студента А равно 78 .
Мы можем убедиться, что это правильно, вручную вычислив средневзвешенный экзаменационный балл студента А.
Напомним, что мы используем следующую формулу для средневзвешенного значения:
Средневзвешенное значение = Σw i X i / Σw i
Мы можем подставить значения из нашего набора данных в эту формулу, чтобы рассчитать средневзвешенный балл экзамена для студента А:
- Средневзвешенное значение для студента A = Σw i X i / Σw i
- Средневзвешенное значение для студента А = (2*60 + 5*90 + 70*3) / (2+5+3)
- Средневзвешенное значение для студента A = 78
Это соответствует значению, которое мы рассчитали по формуле в Excel.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Взвешенное среднее значение
Обычно при расчете среднего у всех чисел одинаковый уровень значимости. числа сделяются, а затем делятся на их количество. При значении взвешего среднего одному или несколько чисел дается более вес.
Поиск взвешеного среднего
Используйте функции СУММПРОИВ И СУММ для поиска взвешеного среднего, которое зависит от весовых значений.
- Например, отправка 10 карандашей составляет 20 цент в случае. Но вторая посылка из 40 досье стоит 30 цент на случай, так как карандаши очень востребованы. Если вы усредили стоимость каждой посылки этим способом (0,20+0,30)/2 = 0,25, результат будет не точным. При математических вычислениях не учитываются случаи, когда продано на 30 цент на 30 цент по сравнению с 20 центами. Чтобы получить правильное среднее значение, воспользуйтесь этой формулой, чтобы получить результат (28 цент на отправленные): =СУММПРОИПР(A2:A3;B2:B3)/СУММ(B2:B3) Формула работает путем деления общей стоимости двух заказов на общее количество заказаний.
Средневзвешенное значение в Excel
Существует несколько средних величин: среднее арифметическое, среднее геометрическое, среднее гармоническое, средневзвешенное и так далее.
Как показывает практика, большинство использует в своих расчетах только два понятия — это среднее арифметическое и средневзвешенное
Со средним арифметическим знаком каждый школьник и рассчитать его в Excel при помощи формулы СРЗНАЧ, я думаю, сможет каждый
Средневзвешенное — это показатель, который учитывает «вес» каждого числа или значения, используемого в вычислениях. Считается, что показатель средневзвешенного более точный и правильный в определенных расчетах
Для примера я взял банальный пример по данным о заработной платы сотрудников и их численности по должностям
Как видим, у нас 5 должностей, фонд заработной платы на человека и численность по каждой должности. Заодно я рассчитал среднеарифместическое значение по заработной плате (СРЗНАЧ)
Если считать математически, то формула средневзвешенного будет выглядеть как (Количество1*Заработная плата1+. +Количество5*Заработная плата5)/(Количество1+. +Количество5)
В больших массивах данных прописывать такое очень трудоемко, и здесь нам поможет Excel.
Для расчета средневзвешенной используется формула СУММПРОИЗВ. Она по сути перемножает значение заработной платы и количества. Затем делим все на общий фонд заработной платы и получаем заветное значение
В примере наглядно видно, как сильно различаются показатели, и как сильно это может повлиять на дальнейшие расчеты.
Происходит это потому, что при расчете средневзвешенной было учтено большое количество людей с заработком менее 50 000