Как посчитать средневзвешенное значение в excel
Средневзвешенные значения
Для того, чтобы рассчитать средневзвешенные значения по каким-либо параметрам, необходимо воспользоваться конструкцией из 2-х основных функций в EXCEL.
В примере ведется расчет среднего значения удельных затрат по логистике на одну товарную позицию. Для этого:
1. Вводится функция СУММПРОИЗВ, в аргументах которой вводятся 2 диапазона — B4:B6 с логистическими затратами на одну позицию в регионах и C4:C6 с указанием долей отгрузок по регионам.
2. Далее в строке формул после готовой формулы СУММПРОИЗВ(B4:B6;C4:C6) вводим «/» и делим на сумму «СУММ» самих долей с диапазоном суммирования C4:C6.
Полученное значение будет средневзвешенным, и его можно будет использовать для включения в себестоимость.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Средневзвешенное значение в Excel
Существует несколько средних величин: среднее арифметическое, среднее геометрическое, среднее гармоническое, средневзвешенное и так далее.
Как показывает практика, большинство использует в своих расчетах только два понятия — это среднее арифметическое и средневзвешенное
Со средним арифметическим знаком каждый школьник и рассчитать его в Excel при помощи формулы СРЗНАЧ, я думаю, сможет каждый
Средневзвешенное — это показатель, который учитывает «вес» каждого числа или значения, используемого в вычислениях. Считается, что показатель средневзвешенного более точный и правильный в определенных расчетах
Для примера я взял банальный пример по данным о заработной платы сотрудников и их численности по должностям
Как видим, у нас 5 должностей, фонд заработной платы на человека и численность по каждой должности. Заодно я рассчитал среднеарифместическое значение по заработной плате (СРЗНАЧ)
Если считать математически, то формула средневзвешенного будет выглядеть как (Количество1*Заработная плата1+. +Количество5*Заработная плата5)/(Количество1+. +Количество5)
В больших массивах данных прописывать такое очень трудоемко, и здесь нам поможет Excel.
Для расчета средневзвешенной используется формула СУММПРОИЗВ. Она по сути перемножает значение заработной платы и количества. Затем делим все на общий фонд заработной платы и получаем заветное значение
В примере наглядно видно, как сильно различаются показатели, и как сильно это может повлиять на дальнейшие расчеты.
Происходит это потому, что при расчете средневзвешенной было учтено большое количество людей с заработком менее 50 000
Как в Excel рассчитать средневзвешенное значение
Функция Excel СРЗНАЧ вычисляет среднее значение набора данных. Часто пользователям нужно рассчитать средневзвешенное. Вы можете потратить целый день на поиски, но не найдете функцию, позволяющую сделать такой расчет. Можно, однако, рассчитывать средневзвешенные, создав формулу, которая использует функции СУММПРОИЗВ и СУММ.
На рис. 96.1 показан обычный лист, который содержит цены на бензин в течение 30 дней. Например, цена составляла $2,48 первые пять дней. Затем она снизилась до $2,41 в течение двух дней. Далее цена еще снизилась до $2,39 в течение трех дней и т. д.
Рис. 96.1. Формула в ячейке В16 вычисляет средневзвешенную цену на бензин
Ячейка В15 содержит формулу, которая использует функцию СРЗНАЧ: =СРЗНАЧ(B4:B13) . Если вы проанализируете ее, то увидите, что формула не возвращает точный результат. Скорее, цены должны быть взвешены по фактору количества дней, в течение которых каждая цена была в силе. Другими словами, средневзвешенное является соответствующим типом расчета. Следующая формула, указанная в ячейке В16 , делает то, что нужно: =СУММПРОИЗВ(B4:B13;C4:C13)/СУММ(C4:C13) .
Эта формула умножает каждую цену на соответствующее количество дней, а затем складывает все, что получилось. Результат делится на количество дней. Вы можете легко переделать эту формулу для других видов расчета средневзвешенного.
Как использовать формулу средневзвешенного ЕСЛИ в 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: