Средневзвешенная цена в 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.