Средневзвешенная цена в MS EXCEL
Рассчитаем средневзвешенную цену товара без использования вспомогательных столбцов. Также рассчитаем средневзвешенную цену с условием.
Пусть дана таблица продаж партий одного товара (см. файл примера). В каждой партии указано количество проданного товара (столбец А) и его цена (столбец В).
Найдем средневзвешенную цену. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(B2:B8), в средневзвешенной учитываются «вес» каждой цены (в нашем случае в качестве веса выступают значения из столбца Количество). Т.е. если продали одну крупную партию товара по очень низкой цене (строка 2), а другие небольшие партии по высокой, то не смотря, что средняя цена будет высокой, средневзвешенная цена будет смещена в сторону низкой цены.
Средневзвешенная цена вычисляется по формуле.
=СУММПРОИЗВ(B2:B8;A2:A8)/СУММ(A2:A8)
Если в столбце «весов» (А) будут содержаться одинаковые значения, то средняя и средневзвешенная цены совпадут.
Средневзвешенная цена с условием
Если требуется найти средневзвешенную цену не для всего массива данных, а для определенной группы значений, то формула усложнится. Покажем на примере.
Пусть имеется таблица партий товара от разных поставщиков.
Формула для вычисления средневзвешенной цены для Поставщика1:
=СУММПРОИЗВ($C$7:$C$13;$D$7:$D$13;--($B$7:$B$13=B17))/СУММЕСЛИ($B$7:$B$13;B17;$D$7)
К аргументам функции СУММПРОИЗВ() добавился 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.