Формула в эксель средневзвешенное значение

Формула в эксель средневзвешенное значение

Если ты экономист, аналитик, бухгалтер или работаешь со статистикой, то наверняка тебе должно быть известно такое понятие как Средневзвешенная. Сперва немного теории

Существует несколько средних величин: среднее арифметическое, среднее геометрическое, среднее гармоническое, средневзвешенное и так далее.

Как показывает практика, большинство использует в своих расчетах только два понятия — это среднее арифметическое и средневзвешенное

Со средним арифметическим знаком каждый школьник и рассчитать его в Excel при помощи формулы СРЗНАЧ, я думаю, сможет каждый

Средневзвешенное — это показатель, который учитывает "вес" каждого числа или значения, используемого в вычислениях. Считается, что показатель средневзвешенного более точный и правильный в определенных расчетах

Для примера я взял банальный пример по данным о заработной платы сотрудников и их численности по должностям

Как видим, у нас 5 должностей, фонд заработной платы на человека и численность по каждой должности. Заодно я рассчитал среднеарифместическое значение по заработной плате (СРЗНАЧ)

Если считать математически, то формула средневзвешенного будет выглядеть как (Количество1*Заработная плата1+. +Количество5*Заработная плата5)/(Количество1+. +Количество5)

В больших массивах данных прописывать такое очень трудоемко, и здесь нам поможет Excel.

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

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

Происходит это потому, что при расчете средневзвешенной было учтено большое количество людей с заработком менее 50 000

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

Пусть дана таблица продаж партий одного товара (см. файл примера ). В каждой партии указано количество проданного товара (столбец А ) и его цена (столбец В ).

Читайте также:  Положение о колл центре

Найдем средневзвешенную цену. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(B2:B8) , в средневзвешенной учитываются «вес» каждой цены (в нашем случае в качестве веса выступают значения из столбца Количество ). Т.е. если продали одну крупную партию товара по очень низкой цене (строка 2 ), а другие небольшие партии по высокой, то не смотря, что средняя цена будет высокой, средневзвешенная цена будет смещена в сторону низкой цены.

Средневзвешенная цена вычисляется по формуле. =СУММПРОИЗВ(B2:B8;A2:A8)/СУММ(A2:A8)

Если в столбце «весов» ( А ) будут содержаться одинаковые значения, то средняя и средневзвешенная цены совпадут.

Средневзвешенная цена с условием

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

Пусть имеется таблица партий товара от разных поставщиков.

Формула для вычисления средневзвешенной цены для Поставщика1:

К аргументам функции СУММПРОИЗВ() добавился 3-й аргумент: —($B$7:$B$13=B17)

Если выделить это выражение и нажать F9 , то получим массив <1:1:1:1:0:0:0>. Т.е. значение 1 будет только в строках, у которых в столбце поставщик указан Поставщик1. Теперь сумма произведений не будет учитывать цены от другого поставщика, т.к. будут умножены на 0. Сумма весов для Поставщика1 вычисляется по формуле СУММЕСЛИ ($B$7:$B$13;B17;$D$7) .

Решение приведено в файле примера на листе Пример2 .

трюки • приёмы • решения

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

На рис. 96.1 показан обычный лист, который содержит цены на бензин в течение 30 дней. Например, цена составляла $2,48 первые пять дней. Затем она снизилась до $2,41 в течение двух дней. Далее цена еще снизилась до $2,39 в течение трех дней и т. д.

Читайте также:  Как запретить программе запускаться при включении компьютера

Рис. 96.1. Формула в ячейке В16 вычисляет средневзвешенную цену на бензин

Ячейка В15 содержит формулу, которая использует функцию СРЗНАЧ: =СРЗНАЧ(B4:B13) . Если вы проанализируете ее, то увидите, что формула не возвращает точный результат. Скорее, цены должны быть взвешены по фактору количества дней, в течение которых каждая цена была в силе. Другими словами, средневзвешенное является соответствующим типом расчета. Следующая формула, указанная в ячейке В16 , делает то, что нужно: =СУММПРОИЗВ(B4:B13;C4:C13)/СУММ(C4:C13) .

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

Ссылка на основную публикацию
Установка виндовс зависла на начало установки
Если вы решили переустановить или установить операционную систему, но начало установки Windows 7 зависает, то в этой статье, думаю, вы...
Тс 6 силы в механике
Тест по физике Силы в механике для 10 класса с ответами. Тест включает в себя 2 варианта. В каждом варианте...
Тульские двери металлические отзывы
Рады представить вам новинку с двумя МДФ панелями- Тульскую дверь Б25 НОВИНКИ 2019 Тульская дверь Е4 ОРХИДЕЯ НОВИНКИ 2019 Тульская...
Установка драйвера принтера отказ
Нередки ситуации, когда не устанавливается принтер, хотя система видит, что к компьютеру подсоединилось новое оборудование. Решение такой задачи требует серьезного...
Adblock detector