Расчет средневзвешенного значения в 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. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Расчет средневзвешенного на DAX в Power Pivot

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

  • В 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 - и будет вам счастье!

Ссылки по теме



09.01.2018 16:50:38
Николай, спасибо за статью, как обычно очень полезная информация в ваших статьях, не каждый день, но нет-нет будет применяться.
:like:
09.01.2018 21:35:02
Не за что, Чингиз :) Надеюсь, когда-нибудь пригодится ;)
10.01.2018 00:01:34
Вычисляемое поле в сводной (обычной) выдает именно тот результат, который и нужен.
Стоимость/Количество
64xExcel 16.0.8431.2153
10.01.2018 10:28:24
Думаю, Николай решил проверить внимательность своих читателей ;)

Присоединяюсь: Обычная сводная таблица эту задачу решает через вычисляемое поле:
10.01.2018 10:30:08
Согласен. Но это только если вы дополнительный столбец "Стоимость" ручками добавите. А если без него?
10.01.2018 10:33:01
Тогда Power Pivot :)
10.01.2018 05:47:28
vector1 прав,вычисляемое поле легко справляется с данной задачей. Но Power Pivot однозначно круче)
11.01.2018 23:22:11
Николай! Благодарю Вас за видеоурок. Планируете ли сделать что-нибудь по кластерному анализу?
12.01.2018 09:56:23
Не уверен :) Кластерный анализ, ИМХО, лучше делать в специализированных программах (SPSS, Matlab, DataMining и т.д.). Реализации в Excel весьма громоздкие и упираются в ограничение на 200 параметров для встроенного Поиска решения.
26.01.2018 13:33:29
Николай спасибо большое!

Отличная статья! Не знал что такая вещь есть пока не посмотрел. Вот если бы Вам еще написать бы книгу по DAX или Power Query, думаю что такие книги были бы бесценными для русскоязычного читателя. Например "Готовые решения DAX - бери и пользуйся" ну аналогично Вашей книге по формулам.
18.02.2018 09:13:06
Подобные книжки давно есть, но на английском. Над просто перевести :)
10.03.2018 18:51:43
Добавлю свои 5 копеек
касаемо Pivota
Можно вызвать редактор мер  и сразу писать функцию, что очень удобно, подсвечивается синтаксис, вставка функций пишет описание всех функций на русском, можно поменять формат
*глюк экселя, если зайти через выбор полей, то не подсвечивается синтаксис







20.04.2018 11:14:02
Спасибо большое. Как всегда Ваша информация упростила работу :)
05.05.2018 12:16:37
Николай, Большое человеческое спасибо Вам! У меня MAC EXCEL. Есть ли Надстройка Power pivot для MAC EXCEL?
Наверх