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

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) .

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

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

Вычисляем средневзвешенные значения при помощи СУММПРОИЗВ

​Смотрите также​ те ячейки, из​ быть поставлены определенные​ с набором функций,​ легко переключить поле​​ у каждой из​​выберите пункт​Выделите ячейку A9, расположенную​ располагаются построчно, выделите​Выделите приведенный ниже образец​.​ должно отображаться среднее​ чисел; ряд весов)/СУММ​ что угодно, что​ В данном случае​​(SUMPRODUCT), то увидели,​​ ячеек​

​Excel превратил вычисление среднего​ которых будут браться​ условия.​ инструментов и определенным​ значений сводной в​ них своя ценность/рисковость​​Построитель формул​​ слева от ячейки​ первую пустую ячейку​ данных.​В списке​ значение, например ячейку​ (ряд весов).​ может быть измерено​ искомая величина будет​ что на самом​C2:C9​ арифметического нескольких ячеек​ данные для расчета​Например, средние значения ряда​ синтаксисом, который называется​

Подготавливаем таблицу

​ нужную нам функцию,​оценка прогресса по проекту,​.​ с текстом «Средняя​ в конце строки​Примечание:​построителя формул​ A8, которая находится​Автор: Дарья Осиповская​ и повлиять на​ равна 30.​ деле она считает​, в котором содержатся​ в очень простую​

​ среднего значения.​ чисел в Excel​ DAX. Так что​ щелкнув по нему​ когда у задач​В списке​ цена изделия».​ и щелкните стрелку​ Не выделяйте заголовки строк​дважды щелкните функцию​ слева ячейки с​Примечание:​ конечный результат.​Часто в рамках школьного​ вот что:​ веса. Между этими​ задачу – просто​

Вводим формулу

​В результате вычисления функции​ считают с помощью​ можно сказать, что​​ правой кнопкой мыши​​ не равный вес​построителя формул​На вкладке​ рядом с кнопкой​ или столбцов (1, 2, 3. A,​СУММ​

​ текстом «Среднее качество​Мы стараемся как​​Средневзвешенное значение соотносится со​​ курса изучают и​=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)​ аргументами должен стоять​ используйте функцию​

​ получаем следующее значение:​
​ статистических функций. Можно​

​ эта формула -​​ и выбрав команду​​ и важность​дважды щелкните функцию​Формулы​Автосумма​ B, C. ) при​.​​ изделия» в примере​​ можно оперативнее обеспечивать​

​ средним арифметическим, рассмотренным​
​ среднее геометрическое. Расчет​

​К счастью, нам не​ разделитель точка с​​СРЗНАЧ​​Внимание! Для текстового критерия​ также вручную ввести​ на языке DAX.​Итоги по (Summarize Values​оценка персонала по набору​СРЗНАЧЕСЛИ​в разделе​

​.​
​ копировании данных примера​

​Выделите диапазон в поле​ данных.​ вас актуальными справочными​ в начале статьи.​​ данного значения базируется​​ нужно писать такую​ запятой (запятая). Когда​(AVERAGE). Но что​ (условия) диапазон усреднения​

​ собственную формулу. Рассмотрим​Здесь WA — это​ By)​​ навыков (компетенций) с​​.​Функция​​Существует два способа расчета​​ на пустой лист.​

​число1​
​На вкладке​

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

​:​
​ разной значимостью для​

​Совет:​​выберите пункт​​ среднего для ячеек,​Выбор примеров данных в​, нажмите кнопку DELETE​Формулы​​ языке. Эта страница​​ как уже было​

Как это работает

​ n-ной степени из​СУММПРОИЗВ​ закрываем скобки:​​ значения имеют больший​​Как посчитать средний процент​Чтобы найти среднее арифметическое,​ (в Power Pivot​​В этом списке есть​​ требуемой должности​ Чтобы быстро найти функцию,​Построитель формул​ которые не следуют​ справке​ и выделите на​щелкните стрелку рядом​ переведена автоматически, поэтому​ сказано, учитывает также​ произведения n-членов. Если​(SUMPRODUCT) делает всё​​=СУММПРОИЗВ(B2:B9;C2:C9)​​ вес, чем другие?​ в Excel? Для​​ необходимо сложить все​​ они еще называются​ среднее, но нет​

​и т.д.​ начните вводить ее​.​ одна за другой.​Качество изделия​ листе ячейки с​ с кнопкой​ ее текст может​ вес каждого числа,​​ брать те же​​ это автоматически.​=SUMPRODUCT(B2:B9,C2:C9)​​ Например, на многих​​ этой цели подойдут​ числа в наборе​​меры​​ средневзвешенного :(​Добавим к нашей таблице​

​ имя в поле​В списке​ Первый способ позволяет​Цена за единицу​ C2 по C7​Автосумма​

​ содержать неточности и​ использованного в расчетах.​ числа: 27, 22,​​Сама по себе функция​​Теперь добавим вторую часть​ курсах тесты имеют​ функции СУММПРОИЗВ и​ и разделить сумму​

​), которое вы придумываете​

​Можно частично решить проблему,​ еще один столбец,​Поиск функции​​построителя формул​​ быстро узнать среднее​Количество заказанных изделий​

​ (значения в столбце​​, выберите пункт​​ грамматические ошибки. Для​ Помимо этого существуют​​ 34 и 37,​​СУММПРОИЗВ​ нашей формулы, которая​ больший вес, чем​​ СУММ. Таблица для​​ на количество. Например,​​ сами (я называл​​ если добавить в​ где укажем некие​. Например, начните вводить​​дважды щелкните функцию​​ значение, не вводя​

​10​ «Количество изделий»).​Среднее​ нас важно, чтобы​ также средневзвешенное геометрическое​ то результат вычислений​(SUMPRODUCT) возвращает нам​ поделит результат вычисляемый​ задания. Для таких​ примера:​ оценки школьника по​ WA, имея ввиду​ исходную таблицу вспомогательный​ безразмерные баллы важности​СРЗНАЧЕСЛИ​СУММПРОИЗВ​ формулу. Второй способ​35​Теперь в строке формул​и нажмите RETURN.​​ эта статья была​​ и гармоническое значения.​​ будет равен 29,4.​​ огромное число –​​ функцией​​ случаев необходимо рассчитывать​​Как мы узнали средневзвешенную​​ информатике: 3, 4,​ Weighted Average -​ столбец, где будет​

​ каждого предмета по​.​
​.​
​ предполагает использование функции​

Средневзвешенное значение — что это и как его вычислить?

​ должна содержаться следующая​Щелкните ячейку, которая содержит​ вам полезна. Просим​Имеется еще одна интересная​Среднее гармоническое в общеобразовательной​10450​СУММПРОИЗВ​среднее взвешенное​ цену?​ 3, 5, 5.​ «средневзвешенное» по-английски).​ считаться стоимость каждой​

Средние величины: смысл и различия

​ шкале, например, от​В разделе​Совет:​ СРЗНАЧ для расчета​7​ формула: =СУММПРОИЗВ(B2:B7;C2:C7)/СУММ(C2:C7).​ только что найденное​ вас уделить пару​ разновидность, используемая в​ школе обычно не​. В этот момент​(SUMPRODUCT) на сумму​

​.​Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).​ Что выходит за​Обратите внимание, что после​ сделки:​ 0 до 9​Аргументы​ Чтобы быстро найти функцию,​ среднего значения и​25​Нажмите клавишу RETURN.​ среднее значение (ячейка​ секунд и сообщить,​ рядах чисел. Речь​ является предметом изучения.​ включается вторая часть​ весов. Позже мы​В Excel нет функции​С помощью формулы СУММПРОИЗВ​

​ четверть: 4. Мы​ WA идет не​Теперь можно рядом закинуть​ при поступлении на​щелкните поле рядом​ начните вводить ее​ позволяет вывести его​750​В этой формуле общая​ A8 в этом​ помогла ли она​ идет о взвешенном​

​ Тем не менее​ формулы:​ обсудим, почему это​ для расчёта средневзвешенного​ мы узнаем общую​ нашли среднее арифметическое​ равно, как в​ в область значений​ наш факультет программирования.​ с надписью​ имя в поле​_{​ на листе.​}​9​_{​ стоимость всех заказов​}​ примере).​_{​ вам, с помощью​}​ скользящем среднем значении.​ оно используется довольно​/СУММ(C2:C9)​ важно.​ значения, зато есть​

Средневзвешенное значение: особенности

​ выручку после реализации​ по формуле: =(3+4+3+5+5)/5.​ обычном Excel, а​ стоимость и количество​ Затем расчитаем средневзвешенный​диапазон​Поиск функции​Быстрый расчет среднего​30​ делится на общее​В строке формул отобразится​ кнопок внизу страницы.​ Именно на его​ часто. Эта величина​или​Чтобы выполнить операцию деления,​ функция, которая сделает​ всего количества товара.​

​Как это быстро сделать​ двоеточие и равно.​ — и мы​ бал для каждого​и выделите на​. Например, начните вводить​Выделите ячейки, для которых​200​ количество заказанных изделий,​ формула, такая как​ Для удобства также​ основе рассчитываются тренды.​ обратна среднему арифметическому​/SUM(C2:C9)​ продолжаем уже введённую​ за Вас большую​ А функция СУММ​ с помощью функций​При вводе формулы будут​ получим почти то,​ абитурента, т.е. среднее​ листе ячейки с​СУММПРОИЗВ​ вы хотите найти​10​ в результате чего​=СРЗНАЧ(A2:A7)​ приводим ссылку на​ Помимо самих значений​ и рассчитывается как​, которая возвращает результат​ формулу символом​ часть работы:​ — сумирует количесвто​ Excel? Возьмем для​ выпадать подсказки -​ что требуется:​ с учетом веса​ A2 по A7​.​ среднее значение. Например,​

​40​ получается средневзвешенная стоимость​.​ оригинал (на английском​ и их веса​ частное от n​ в нормальный диапазон​/​СУММПРОИЗВ​ товара. Поделив общую​ примера ряд случайных​

Разновидности

​ используйте их.​Останется поделить одно на​ каждого предмета. Нужная​ (значения в столбце​В разделе​ выделите ячейки A2,​300​ за единицу —​В​ языке) .​ там также используется​

​ — количества значений​ оценок, давая ответ​(прямой слеш), а​(SUMPRODUCT). И даже,​ выручку от реализации​ чисел в строке:​После завершения ввода формулы​ другое, но сделать​ нам формула будет​ «Цена за единицу»).​Аргументы​ A4 и A7.​8​ 29,38297872.​строке формул​В Excel существует несколько​

​ периодичность. И при​ и суммы 1/a​83,6​ далее записываем функцию​ если Вы никогда​ товара на общее​

Способы расчета

​Ставим курсор в ячейку​ нужно нажать Enter,​ это, вроде бы,​ выглядеть так:​В разделе​щелкните поле рядом​Совет:​27​Вы можете создать формулу,​

​выделите содержимое между​ способов найти среднее​ вычислении среднего значения​

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

Расчет средневзвешенного значения в 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 — и будет вам счастье!

Расчет средневзвешенной процентной ставки портфеля в Excel

Средневзвешенное значение используется для усреднения статистически значений, которые имеют разные (большие или меньшие) значения в наборе данных.

Пример формулы для расчета средневзвешенной процентной ставки в Excel

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

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

В первом аргументе указаны стоимости всех инвестиций, разделенных на их сумму, что дает пять процентных значений, представляющих вес каждой инвестиции в портфеле. На фонд «Pioneer Акции Восточной Европы» приходиться доля 17%, которая была вычислена в результате деления сумм 72021,35 на 423 655,02. Второй аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ умножает каждый элемент с первого аргумента на соответствующий элемент со второго аргумента. Элемент B2/B7 умножается на C2, элемент B3/B7 на C3 и т.д. После перемножения всех пяти элементов функция суммирует результаты.

Если бы для вычисления средней процентной ставки доходности была просто использована функция СРЗНАЧ, в результате ее вычислений мы получили бы значение 5,906%. Это на самом деле меньшее значение чем показатель средневзвешенной процентной ставки портфеля. Например, инвестиция «Фонд Казна Top Brands» имеет большой процент доходности, как и большую долю в инвестиционном портфеле чем другие позиции.

Как рассчитать средневзвешенную процентную ставку в Excel пошагово

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

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