Диаграмма Парето
Возможно вы уже слышали про "Закон Парето" или "Принцип 20/80". В конце 19 века итальянский социолог и экономист Вильфредо Парето обнаружил, что распределение богатства в обществе неравномерно и подчиняется определенной зависимости: с ростом обеспеченности количество богатых людей уменьшается в геометрической прогрессии с постоянным коэффициентом (среди итальянских домохозяйств 80% доходов было у 20% семей). В дальнейшем эту идею в своей книге развил Ричард Кох, предложивший формулировку универсального "Принципа 20/80" (20% усилий дают 80% результата). На практике этот закон обычно выражается не в таких красивых цифрах (почитайте "Длинный хвост" Криса Андерсона), но отчетливо показывает неравномерность распределения ресурсов, прибыли, затрат и т.д.
В бизнес-анализе часто строят диаграмму Парето, отображающую эту неравномерность. С ее помощью можно наглядно показать, например, какие товары или клиенты приносят наибольшую прибыль. Выглядит она обычно так:
Ее основные особенности:
- Каждый синий столбец гистограммы представляет собой прибыль по товару в абсолютных единицах и откладывается по левой оси.
- Оранжевый график представляет собой накопленный процент прибыли (т.е. долю прибыли нарастающим итогом).
- На условной границе в 80% обычно рисуют пороговую горизонтальную линию для наглядности. Все товары левее точки пересечения этой линии с графиком накопленной прибыли - приносят нам 80% денег, все товары правее - оставшиеся 20%.
Давайте разберем как построить диаграмму Парето в Microsoft Excel своими силами.
Способ 1. Встроенный тип в Excel 2016 и новее
Начиная с версии Excel 2016 диаграмма Парето была добавлена в стандартный набор диаграмм Excel. Теперь, чтобы ее построить, достаточно просто выделить диапазон и на вкладке Вставка (Insert) выбрать соответствующий тип:
Одно нажатие - и диаграмма готова:
К минусам же этого способа можно отнести только полную невозможность как-то донастроить получившийся график, например, добавить на него горизонтальную пороговую линию на отметке 80%, подсветить ключевые столбцы и т.д.
Способ 2. Сводная таблица и сводная диаграмма Парето
Что же делать, если для построения нет готовых данных, а есть только исходная необработанная информация? Предположим, что в начале у нас есть таблица с данными продаж вот такого вида:
Чтобы построить по ней диаграмму Парето и выяснить какие товары лучше всего продаются, придется сначала проанализировать исходные данные. Проще всего это сделать с помощью сводной таблицы. Выделим любую ячейку в исходной таблице и воспользуемся командой Вставка - Сводная таблица (Insert - Pivot Table). В появившемся промежуточном окне ничего не меняем и жмем ОК, затем в появившейся справа панели переносим мышью поля исходных данных из верхней части в нижние области макета будущей сводной таблицы:
В итоге должна получиться сводная таблица с суммарной выручкой по каждому товару:
Сортируем ее по убыванию выручки, установив активную ячейку в столбец Сумма по полю Выручка и используя кнопку сортировки От Я до А (From Z to A) на вкладке Данные (Data).
Теперь нужно добавить вычисляемый столбец с накопленной процентной выручкой. Для этого еще раз перетащите поле Выручка в область Значения (Values) на правой панели, чтобы получить дубликат столбца в сводной. Затем щелкните по клонированному столбцу правой кнопкой мыши и выберите команду Дополнительные вычисления - % от суммы с нарастающим итогом в поле (Show Data As - % Running Total In). В появившемся окне выберите поле Наименование, по которому сверху-вниз будут накапливаться проценты выручки. На выходе должна получиться вот такая таблица:
Как легко заметить - это уже практически готовая таблица из первой части статьи. В ней только не хватает для полного счастья столбца с пороговым значением 80% для построения линии отсечки в будущей диаграмме. Такой столбец можно легко добавить с помощью вычисляемого поля. Выделите любое число в сводной и затем нажмите на вкладке Главная - Вставить - Вычисляемое поле (Home - Insert - Calculated Field). В открывшемся окне введем имя поля и его формулу (в нашем случае - константу):
После нажатия на ОК в таблицу добавится третий столбец со значением 80% во всех ячейках и она, наконец, примет требуемый вид. Дальше можно воспользоваться командой Сводная диаграмма (Pivot Chart) на вкладке Параметры (Options) или Анализ (Analysis) и настроить диаграмму совершенно аналогично первому варианту:
Подсветка ключевых товаров
Для подсветки самых влияющих факторов, т.е. столбцов находящихся левее точки пересечении оранжевой кривой накопленных процентов с горизонтальной линией отсечки в 80% можно использовать подсветку. Для этого придется добавить к таблице еще один столбец с формулой:
Эта формула выдает на выходе 1, если товар находится левее точки пересечения и 0 - если правее. Затем нужно сделать следующее:
- Добавляем новый столбец к диаграмме - проще всего это сделать простым копированием, т.е. выделить столбец Подсветка, скопировать его (Ctrl+C), выделить диаграмму и произвести вставку (Ctrl+V).
- Выделяем добавленный ряд и переключаем его по вторичной оси, как было описано выше.
- Тип диаграммы для ряда Подсветка меняем на столбцы (гистограмму).
- Убираем боковой зазор в свойствах ряда (правой кнопкой мыши по ряду Подсветка - Формат ряда - Боковой зазор), чтобы столбцы слились в единое целое.
- Убираем границы столбцов, а заливку делаем полупрозрачной.
На выходе получим вот такую симпатичную подсветку наилучших товаров:
Способ 3. Частотный анализ в диаграмме Парето
Если бросить в область строк предыдущей сводной таблицы не товары, а прибыль, то можно легко сгруппировать её затем в интервалы с заданным шагом - для этого щёлкните правой кнопкой мыши по любому числовому значению прибыли и выберите из контекстного меню команду Группировать (Group). В открывшемся окне можно задать начальное и конечное значение и нужный нам шаг:
После сортировки и добавления столбца с нарастающим процентом (как в предыдущем способе) получим диаграмму, по которой хорошо будет видно прибыль (цену, стоимость, объем корзины...) которую у нас чаще всего покупают:
Если нужен, не постоянный, а переменный шаг, то придется рассмотреть другой способ - надстройку Пакет Анализа.
Способ 4. Диаграмма Парето с помощью надстройки Пакет анализа
Ещё один способ заключается в использовании надстройки Пакет Анализа, содержащей кучу аналитических и статистических инструментов, в том числе и частотный анализатор и построитель диаграммы Парето. Подключить надстройку можно либо через Файл - Параметры - Надстройки - Перейти (File - Options - Add-ins - Go to) либо кнопкой Надстройки Excel на вкладке Разработчик (Developer - Excel Add-ins).
Также нам потребуется заранее создать вспомогательную таблицу с пороговыми значениями (их еще называют в статистике "карманами"), количество попаданий в которые нас интересует. Размер карманов (т.е. шаг между порогами) может быть произвольным и не постоянным - в этом большое преимущество данного подхода.
После запуска надстройки на вкладке Данные - Анализ данных (Data - Data Analysis) выбираем в списке доступных инструментов Гистограмму (Histogram) и задаем в диалоговом окне все необходимые параметры:
После нажатия на ОК получаем таблицу с результатами частотного анализа и диаграмму Парето, построенную по рассчитанным данным:
К недостаткам этого способа можно отнести отсутствие в результатах формул (т.е. пересчета при изменении исходных данных не будет) и подсчет только количества попаданий в каждый карман (а не суммы или среднего - как это можно произвольно выбрать в способе со сводной таблицей).
Способ 5. Диаграмма Парето по итогам на формулах
Если исходные данные попали к вам в виде подобной таблицы (т.е. уже в готовом виде):
... то делаем следующее.
Сортируем таблицу по убыванию прибыли (вкладка Данные - Сортировка) и добавляем столбец с формулой для расчета накопленного процента прибыли:
Эта формула делит суммарную накопленную прибыль с начала списка до текущего товара на общую прибыль по всей таблице. Также добавляем столбец с константой 80% для создания в будущей диаграмме горизонтальной пороговой пунктирной линии:
Выделяем все данные и строим обычную гистограмму на вкладке Вставка - Гистограмма (Insert - Column Chart). Должно получиться примерно следующее:
Ряды с процентами на полученной диаграмме нужно отправить по вторичной (правой) оси. Для этого нужно выделить ряды мышью, но это может быть сложно, поскольку их плохо видно на фоне больших столбцов прибыли. Так что лучше воспользоваться для выделения выпадающим списком на вкладке Макет (Layout) или Формат (Format):
Затем щелкнуть по выделенному ряду правой кнопкой мыши и выбрать команду Формат ряда (Format Data Series) и в появившемся окне выбрать опцию По вторичной оси (Secondary Axis). В итоге наша диаграмма начнет выглядеть так:
Для рядов Накопленная доля прибыли и Порог надо поменять тип диаграммы со столбцов на линию. Для этого щелкните по каждому из этих рядов и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type).
Останется выделить горизонтальный ряд Порог и отформатировать его так, чтобы он стал похож на линию отсечки, а не на данные (т.е. убрать маркеры, сделать линию красной пунктирной и т.д.). Все это можно сделать, щелкнув по ряду правой кнопкой мыши и выбрав команду Формат ряда (Format Data Series). Теперь диаграмма примет окончательный вид:
По ней можно сделать вывод, что 80% прибыли приносят 5 первых товаров, а на все остальные товары правее картофеля приходится только 20% прибыли.
В Excel 2013 можно поступить еще проще - воспользоваться новым встроенным комбинированным типом диаграммы сразу при построении графика:
Ссылки по теме
- Как построить отчет с помощью сводной таблицы
- Настройка вычислений в сводных таблицах
- Новые возможности диаграмм в Excel 2013
- Статья про закон Парето в Википедии
Что касается самого графика, есть один вопрос, а как можно рисовать вертикальную линию в точке пересечения 80% ? - это было бы очень удобно и показательно.
Спасибо за идею!
"Ссылки, имена и массивы нельзя использовать в формулах сводных таблиц".
Ах, если бы Microsoft смог это изменить =) К кому обращаться?
Есть проблемка, не поможете?
Собрал график на динамических данных, после "сборки" получил на графике все данные, включая и те которые не должны по идее отображаться?
рис
По идее НД() на графике показываться не должно, и график должен растягиваться на всю длину оси Х. Может что то с настройками Excel?
Как раз к диплому!
Добавляем еще один столбец и вписываем в него формулу:
Добавляем этот ряд в диаграмму и пускаем по основной оси. Далее идем в Формат ряда и ставим градиентную заливку: заготовка по умолчанию, тип линейный, угол 0 градусов.
Далее делаем 6 точек градиента. Первой самой левой - назначаем прозрачность 100%. 2-ю и 3-ю располагаем в районе середины общей длины полосы меток. 4-ю и 5-ю где-то на 1/6 от общей длины полосы меток - это индивидуально надо смотреть - это расстояние будет влиять как раз на ширину линии. 3-ей и 4-ой назначаем нужный цвет. 2-ой, 5-ой и 6-ой назначаем прозрачность 100%.
Все, вот и получилась вертикальная линия. Муторно и запутанно звучит, но на деле все просто. Выглядит примерно так:
Сводная таблица ссылается на диапазон данных преобразованный в таблицу (Excel 2013), вставить вычисляемое поле в сводную таблицу любым из указанных способов невозможно (данный пункт недоступен для выбора). Не подскажете почему?
Если все те же операции производить с обычным диапазоном, вычисляемое поле вставляется в сводную таблицу без проблем.
Есть исходный диапазон о продажах содержит не группированные данные товаров.
Как построить диаграмму Парето при условии динамически изменяемых исходного списка данных? Представление данных на графике необходимо по группам товаров. Перечень групп товаров - конечный. В столбце "наименование" товары собираются схоластично.
Группировка данных через сводную таблице не сильно помогает т.к. сводные таблицы автоматом не обновляются. График хотелось бы видеть всегда с актуальными данными.
Данный график (линия) совсем не там где реально на графике 80%. Как добиться желаемого результата?
Но при построении диаграммы столкнулась с некоторыми проблемами и все никак не могу найти ошибку
Фактически, все столбцы аналогичны данным в примере (Наим.базы, Выручка, Накопл.доля), но при построении диаграммы по Варианту 1 столбец с наибольшей выручкой выходит за границу 100%. Вычисляла накопленную долю как в примере, проверяла через нахождение отдельных долей и затем суммирование - то же самое. Подскажите, пожалуйста, в чем может быть ошибка?
И второй вопрос: пыталась строить через встроенную диаграмму Парето и добавлять поля Порог и Подсветка, но они не отображаются (то есть в окне "Выбор источника данных они есть", а по факту их не видно, и в Формат - Область диаграммы их тоже нет). Получается, в встроенную диаграмму нельзя добавлять дополнительные обозначения?
Господа уже третий час с этой диаграммой никак не осилю.
Если кто владеет навыками подскажите как в этой диаграмме каждый вид овоща/фрукта отобразить с накоплением (к примеру яблоко флан 100, факт 75, т.е. Столбец в диаграмме с значением яблоко заполнен на 75% соответственно.