Средневзвешенное excel

Расчет средневзвешенного значения в Excel

Основная идея

Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:

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

Первое, что обычно приходит в голову — это рассчитать классический средний балл с помощью стандартной функции Excel СРЗНАЧ (AVERAGE).

На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется «Программирование», а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень. Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение.

Средневзвешенное — это среднее с учетом различной ценности (веса, важности) каждого из элементов.

В бизнесе средневзвешенное часто используется в таких задачах, как:

• оценка портфеля акций, когда у каждой из них своя ценность/рисковость
• оценка прогресса по проекту, когда у задач не равный вес и важность
• оценка персонала по набору навыков (компетенций) с разной значимостью для требуемой должности
• и т.д.

Расчет средневзвешенного формулами

Добавим к нашей таблице еще один столбец, где укажем некие безразмерные баллы важности каждого предмета по шкале, например, от 0 до 9 при поступлении на наш факультет программирования. Затем расчитаем средневзвешенный бал для каждого абитурента, т.е. среднее с учетом веса каждого предмета. Нужная нам формула будет выглядеть так:

Функция СУММПРОИЗВ (SUMPRODUCT) попарно перемножает друг на друга ячейки в двух указанных диапазонах — оценки абитурента и вес каждого предмета — а затем суммирует все полученные произведения. Потом полученная сумма делится на сумму всех баллов важности, чтобы усреднить результат. Вот и вся премудрость.

Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉

Расчет средневзвешенного в сводной таблице

Поднимем ставки и усложним задачу. Допустим, что теперь нам нужно подсчитать средневзвешенное, но не в обычной, а в сводной таблице. Предположим, что у нас есть вот такая таблица с данными по продажам:

Обратите внимание, что я преобразовал ее в «умную» таблицу с помощью команды Главная — Форматировать как таблицу (Home — Format as Table) и дал ей на вкладке Конструктор (Design) имя Data.

Заметьте, что цена на один и тот же товар может различаться. Наша задача: рассчитать средневзвешенные цены для каждого товара. Следуя той же логике, что и в предыдущем пункте, например, для земляники, которая продавалась 3 раза, это должно быть:

=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33

То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.

Правда, с реализацией этой нехитрой логики именно в сводной таблице нас ждет небольшой облом. Если вы работали со сводными раньше, то, наверное, помните, что можно легко переключить поле значений сводной в нужную нам функцию, щелкнув по нему правой кнопкой мыши и выбрав команду Итоги по (Summarize Values By) :

В этом списке есть среднее, но нет средневзвешенного 🙁

Можно частично решить проблему, если добавить в исходную таблицу вспомогательный столбец, где будет считаться стоимость каждой сделки:

Теперь можно рядом закинуть в область значений стоимость и количество — и мы получим почти то, что требуется:

Останется поделить одно на другое, но сделать это, вроде бы, простое математическое действие внутри сводной не так просто. Придется либо добавлять в сводную вычисляемое поле (вкладка Анализ — Поля, элементы, наборы — Вычисляемое поле), либо считать обычной формулой в соседних ячейках или привлекать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA) , о которой я уже писал. А если завтра изменятся размеры сводной (ассортимент товаров), то все эти формулы придется вручную корректировать.

В общем, как-то все неудобно, трудоемко и нагоняет тоску. Да еще и дополнительный столбец в исходных данных нужно руками делать. Но красивое решение есть.

Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX

Если у вас Excel 2013-2016, то в него встроен супермощный инструмент для анализа данных — надстройка Power Pivot, по сравнению с которой сводные таблицы с их возможностями — как счеты против калькулятора. Если у вас Excel 2010, то эту надстройку можно совершенно бесплатно скачать с сайта Microsoft и тоже себе установить. С помощью Power Pivot расчет средневзвешенного (и других невозможных в обычных сводных штук) очень сильно упрощается.

1. Для начала, загрузим нашу таблицу в Power Pivot. Это можно сделать на вкладке Power Pivot кнопкой Добавить в модель данных (Add to Data Model) . Откроется окно Power Pivot и в нем появится наша таблица.

2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Несколько нюансов по формуле:

• В Power Pivot есть свой встроенный язык с набором функций, инструментов и определенным синтаксисом, который называется DAX. Так что можно сказать, что эта формула — на языке DAX.
• Здесь WA — это название вычисляемого поля (в Power Pivot они еще называются меры), которое вы придумываете сами (я называл WA, имея ввиду Weighted Average — «средневзвешенное» по-английски).
• Обратите внимание, что после WA идет не равно, как в обычном Excel, а двоеточие и равно.
• При вводе формулы будут выпадать подсказки — используйте их.
• После завершения ввода формулы нужно нажать Enter , как и в обычном Excel.

3. Теперь строим сводную. Для этого в окне Power Pivot выберите на вкладке Главная — Сводная таблица (Home — Pivot Table). Вы автоматически вернетесь в окно Excel и увидите привычный интерфейс построения сводной таблицы и список полей на панели справа. Осталось закинуть поле Наименование в область строк, а нашу созданную формулой меру WA в область значений — и задача решена:

Вот так — красиво и изящно.

Общая мораль: если вы много и часто работаете со сводными таблицами и вам их возможности «тесноваты» — копайте в сторону Power Pivot и DAX — и будет вам счастье!

Microsoft Excel

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

Как в Excel рассчитать средневзвешенное значение

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

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

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

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

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

Средневзвешенная цена в MS EXCEL

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

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

Найдем средневзвешенную цену. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(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 .

Взвешенное среднее значение

В этом курсе:

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Обычно при вычислении среднего значения все числа задаются равным значимости; числа добавляются одновременно, а затем поделяются на количество чисел. При использовании взвешенного среднего значения одному или нескольким числам присваивается более значимое значение или толщина.

Вычисление среднего взвешенного значения

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

Например, отгрузка 10 вариантов Карандашов составляет 20 центов на каждый вариант. Но вторая отгрузка 40 долларов на один случай, поскольку карандашы имеют высокий спрос. Если средние затраты для каждой отгрузки таким образом (0,20 + 0.30)/2 = 0,25, результат будет неточным.

Математический ввод не учитывает, что количество коробок составляет более 30 центов, чем 20 центов. Чтобы получить правильное среднее значение, используйте эту формулу для получения результата (28 центов на каждую отгрузку).

Формула выполняет деление общей стоимости двух заказов на общее количество заказанных коробок.

Как посчитать средневзвешенное значение в Excel? Какая нужна формула?

Средневзвешенное значение (средняя взвешенная) в отличие от среднего значения учитывает вес (важности) каждого из элементов совокупности.

Как в программе Эксель (Excel) можно сделать расчёт средней взвешенной?

Какую формулу нужно написать?

Средневзвешенное значение можно посчитать по следующей формуле:

x1, x2, x3, . xn — это значения (цена, оценка, курс валюты и т.д.).

w1, w2, w3, . wn — вес каждого значения.

То есть нужно каждое значение умножить на его вес и просуммировать все произведения, а затем разделить всё это на сумму всех весов.

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

Для его расчёта можно использовать комбинацию из 2 других функций: СУММПРОИЗВ и СУММ.

Вот несложный пример расчёта средневзвешенного значения в Excel:

Есть несколько товаров, которые имеют разную цену. За 1 день было продано сколько-то штук каждого из товаров. Нужно посчитать средневзвешенную цену продажи.

1) Составляем таблицу.

Здесь всё понятно — пишем название товара, рядом — соответствующее количество и цену.

2) Теперь ставим курсор в ту ячейку, куда нужно записать формулу для средневзвешенного.

Переходим на вкладку панели инструментов «Формулы», там нажимаем на «Математические» и в списке выбираем «СУММПРОИЗВ».

В качестве первого аргумента этой функции указываем столбец с количеством (С), в качестве второго аргумента указываем столбец с ценой (D).

3) Теперь в строке формул после функции СУММПРОИЗВ ставим символ деления (/) и добавляем функцию СУММ.

Здесь достаточно указать в 1 аргументе столбец с количеством (С).

4) В результате получится средневзвешенная цена.