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

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


Комментарии
Только для авторизованных пользователей

Аноним, 7 февраля 2018 г.
Спасибо, помогли
Аноним, 19 февраля 2018 г.
Прям вот да!
Аноним, 28 августа 2018 г.
Помоги найти средневзвешенную цену по поставщику 1 в диапазоне между 20 и 40 руб
Аноним, 8 октября 2018 г.
добрый день. к вопросу о средневзвешенной без условий. Почему не используется действие деления суммы стоимости всех партий на на количество товаров в этих партиях? Сумм (С2:С8), где стоимость всех партий = 5 452 р / СУММ (А2:А8) = 33,2 (почти всегда в таблицах с данными о закупке или продажах есть стоимость партии и сумма всех товаров в партиях)
Михаил, 8 октября 2018 г.
=СУММПРОИЗВ(B5:B11;A5:A11) это и есть стоимость всех партий, только одной формулой, иначе пришлось бы городить еще 1 столбец с промежуточными вычислениями
Аноним, 15 февраля 2020 г.
а как рассчитать цены товаров, если известна средневзвешенная цена и количество товаров?
Михаил, 23 февраля 2020 г.
Это не возможно сделать в принципе. Точнее вариантов решений бесчисленное множество. Например, измените цену у одного любого товара. Меняя цену любого другого товара можно подогнать его цену так, чтобы средневзвешенная цена была равна начальной/заданной.
Аноним, 19 мая 2020 г.
Добрый день А если условия два? Например, в столбце А у нас будут месяца? и нужна средневзвешанная по каждому поставщику помесячно?
Михаил, 19 мая 2020 г.
сделал в файле примера лист Пример 3. Теперь там есть средневзвешенное с 2-мя условиями
Аноним, 21 мая 2020 г.
Спасибо ВАМ ОГРООООМНОЕ! Поняла. А подскажите, пожалуйста, как можно простыми словами объяснить как влияет двойное бинарное отрицание (--) в данной формуле?
Михаил, 30 мая 2020 г.
[id413139588|Елена], двойное отрицание для преобразования значения ЛОЖЬ или ИСТИНА в число 0 или 1 соответственно
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved