Диаграмма Парето

Возможно вы уже слышали про "Закон Парето" или "Принцип 20/80". В конце 19 века итальянский социолог и экономист Вильфредо Парето обнаружил, что распределение богатства в обществе неравномерно и подчиняется определенной зависимости: с ростом обеспеченности количество богатых людей уменьшается в геометрической прогрессии с постоянным коэффициентом (среди итальянских домохозяйств 80% доходов было у 20% семей). В дальнейшем эту идею в своей книге развил Ричард Кох, предложивший формулировку универсального "Принципа 20/80" (20% усилий дают 80% результата). На практике этот закон обычно выражается не в таких красивых цифрах (почитайте "Длинный хвост" Криса Андерсона), но отчетливо показывает неравномерность распределения ресурсов, прибыли, затрат и т.д.

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

pareto1.png

Ее основные особенности:

  • Каждый синий столбец гистограммы представляет собой прибыль по товару в абсолютных единицах и откладывается по левой оси.
  • Оранжевый график представляет собой накопленный процент прибыли (т.е. долю прибыли нарастающим итогом).
  • На условной границе в 80% обычно рисуют пороговую горизонтальную линию для наглядности. Все товары левее точки пересечения этой линии с графиком накопленной прибыли - приносят нам 80% денег, все товары правее - оставшиеся 20%.

Давайте разберем как построить диаграмму Парето в Microsoft Excel своими силами.

Вариант 1. Простая диаграмма Парето по готовым данным

Если исходные данные попали к вам в виде подобной таблицы (т.е. уже в готовом виде):

pareto2.png

... то делаем следующее.

Сортируем таблицу по убыванию прибыли (вкладка Данные - Сортировка) и добавляем столбец с формулой для расчета накопленного процента прибыли:

pareto3.png

Эта формула делит суммарную накопленную прибыль с начала списка до текущего товара на общую прибыль по всей таблице. Также добавляем столбец с константой 80% для создания в будущей диаграмме горизонтальной пороговой пунктирной линии:

pareto4.png

Выделяем все данные и строим обычную гистограмму на вкладке Вставка - Гистограмма (Insert - Column Chart). Должно получиться примерно следующее:

pareto5.png

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

pareto6.png

Затем щелкнуть по выделенному ряду правой кнопкой мыши и выбрать команду Формат ряда (Format Data Series) и в появившемся окне выбрать опцию По вторичной оси (Secondary Axis). В итоге наша диаграмма начнет выглядеть так:

pareto7.png

Для рядов Накопленная доля прибыли и Порог надо поменять тип диаграммы со столбцов на линию. Для этого щелкните по каждому из этих рядов и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type).

Останется выделить горизонтальный ряд Порог и отформатировать его так, чтобы он стал похож на линию отсечки, а не на данные (т.е. убрать маркеры, сделать линию красной пунктирной и т.д.). Все это можно сделать, щелкнув по ряду правой кнопкой мыши и выбрав команду Формат ряда (Format Data Series). Теперь диаграмма примет окончательный вид:

pareto8.png

По ней можно сделать вывод, что 80% прибыли приносят 5 первых товаров, а на все остальные товары правее картофеля приходится только 20% прибыли.

В Excel 2013 можно поступить еще проще - воспользоваться новым встроенным комбинированным типом диаграммы сразу при построении графика:

pareto9.png

Вариант 2. Сводная таблица и сводная диаграмма Парето

Что же делать, если для построения нет готовых данных, а есть только исходная необработанная информация? Предположим, что в начале у нас есть таблица с данными продаж вот такого вида:

pareto10.png

Чтобы построить по ней диаграмму Парето и выяснить какие товары лучше всего продаются, придется сначала проанализировать исходные данные. Проще всего это сделать с помощью сводной таблицы. Выделим любую ячейку в исходной таблице и воспользуемся командой Вставка - Сводная таблица (Insert - Pivot Table). В появившемся промежуточном окне ничего не меняем и жмем ОК, затем в появившейся справа панели переносим мышью поля исходных данных из верхней части в нижние области макета будущей сводной таблицы:

pareto12.png

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

pareto13.png

Сортируем ее по убыванию выручки, установив активную ячейку в столбец Сумма по полю Выручка и используя кнопку сортировки От Я до А (From Z to A) на вкладке Данные (Data).

Теперь нужно добавить вычисляемый столбец с накопленной процентной выручкой. Для этого еще раз перетащите поле Выручка в область Значения (Values) на правой панели, чтобы получить дубликат столбца в сводной. Затем щелкните по клонированному столбцу правой кнопкой мыши и выберите команду Дополнительные вычисления - % от суммы с нарастающим итогом в поле (Show Data As - % Running Total In). В появившемся окне выберите поле Наименование, по которому сверху-вниз будут накапливаться проценты выручки. На выходе должна получиться вот такая таблица:

pareto14.png

Как легко заметить - это уже практически готовая таблица из первой части статьи. В ней только не хватает для полного счастья столбца с пороговым значением 80% для построения линии отсечки в будущей диаграмме. Такой столбец можно легко добавить с помощью вычисляемого поля. Выделите любое число в сводной и затем нажмите на вкладке Главная - Вставить - Вычисляемое поле (Home - Insert - Calculated Field). В открывшемся окне введем имя поля и его формулу (в нашем случае - константу):

pareto15.png

После нажатия на ОК в таблицу добавится третий столбец со значением 80% во всех ячейках и она, наконец, примет требуемый вид. Дальше можно воспользоваться командой Сводная диаграмма (Pivot Chart) на вкладке Параметры (Options) или Анализ (Analysis) и настроить диаграмму совершенно аналогично первому варианту:

pareto16.png

Подсветка ключевых товаров

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

pareto17.png

Эта формула выдает на выходе 1, если товар находится левее точки пересечения и 0 - если правее. Затем нужно сделать следующее:

  1. Добавляем новый столбец к диаграмме - проще всего это сделать простым копированием, т.е. выделить столбец Подсветка, скопировать его (Ctrl+C), выделить диаграмму и произвести вставку (Ctrl+V).
  2. Выделяем добавленный ряд и переключаем его по вторичной оси, как было описано выше.
  3. Тип диаграммы для ряда Подсветка меняем на столбцы (гистограмму).
  4. Убираем боковой зазор в свойствах ряда (правой кнопкой мыши по ряду Подсветка - Формат ряда - Боковой зазор), чтобы столбцы слились в единое целое.
  5. Убираем границы столбцов, а заливку делаем полупрозрачной.

На выходе получим вот такую симпатичную подсветку наилучших товаров:

pareto18.png

P.S.

Начиная с версии Excel 2016 диаграмма Парето была добавлена в стандартный набор диаграмм Excel. Теперь, чтобы ее построить, достаточно просто выделить диапазон и на вкладке Вставка (Insert) выбрать соответствующий тип:

pareto19.png

Одно нажатие - и диаграмма готова:

pareto20.png

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

 


13.06.2014 12:00:52
Николай, очень интересная статья, хоть и используем в компании постоянно ABC и Парето, но до такого графика дело не доходило.
Что касается самого графика, есть один вопрос, а как можно рисовать вертикальную линию в точке пересечения 80% ? - это было бы очень удобно и показательно.
13.06.2014 14:35:34
Насчет вертикальной линии не уверен, а вот подсветку ключевых товаров сделать достаточно просто - дописал про это в статью.
Спасибо за идею!
13.06.2014 14:49:59
А вот в сводных, этот номер не проходит. Поскольку мы хоть и делаем доп.вычисление по прибыли "% с нарастающим итогом", но вот вычисляемое поле, никак не хочет воспринимать эту величину как %, а отталкивается от его реальной величины.
13.06.2014 15:02:36
Да, вы правы, в сводной придется делать ручками дополнительный столбец с формулой рядом с таблицей - не комильфо :(
20.06.2014 11:49:09
Отличная статья, Николай! Столбец рядом со сводной построить можно, только вот добавить данные с этого столбца на диаграмму не выходит. Проблема решается следующей формулой в вычисляемом поле (конкретно для вашего примера): "=ЕСЛИ(Выручка<6600000;0;1)". Минус в том, что приходится вводить фиксированное значение и не забывать менять его при изменении данных родительского столбца.
23.06.2014 12:52:37
Спасибо, Василий! Ваш вариант с формулой очень неплох. А если вместо 660000 вписать именованный диапазон, где это число будет лежать - будет работать, интересно?
29.01.2015 01:28:07
Увы, нет.

"Ссылки, имена и массивы нельзя использовать в формулах сводных таблиц".

Ах, если бы Microsoft смог это изменить =) К кому обращаться? :)
15.02.2016 18:38:46
Николай, доброе время суток!

Есть проблемка, не поможете?
Собрал график на динамических данных, после "сборки" получил на графике все данные, включая и те которые не должны по идее отображаться?

рис https://www.dropbox.com/s/p9e0e9zndeoi7tc/screen22.png?dl=0

По идее НД() на графике показываться не должно, и график должен растягиваться на всю длину оси Х. Может что то с настройками Excel?
27.06.2014 02:22:10
Вертикальную линую линию можно сделать с помощью планок погрешностей и дополнительного ряда, НО только в точке, где значение Парето для товара самое близкое к 80%, иначе приходится строить дополнительную категорию, что отразится на внешнем виде диаграммы.
16.06.2014 23:47:09
Николай, спасибо за этот пост!
Как раз к диплому!:)
17.06.2014 07:47:07
Не за что, Алексей! Удачи на защите!
18.06.2014 13:40:15
Николай, когда у вас ближайший открытый курс по визуализации?
23.06.2014 12:53:42
26 июля - приходите, Евгений!
26.06.2014 14:47:24
Добрый день! У меня получилось почти все, кроме значений второстепенной вертикальной оси. После добавления "подсветки" на вспомогательную ось значения с процентов изменились на значения столбца подсветки. Пожалуйста, подскажите, как это исправить. Нужно что-то изменить в параметрах этой оси?
27.06.2014 16:42:34
Щелкнуть правой кнопкой по оси - Формат оси - Число - Процентный.
28.06.2014 07:50:55
Спасибо!
04.07.2014 00:17:57
Николай, спасибо большое за эту публикацию! Отличный способ визуализации анализа прибылеобразующих партнеров и продукции.
04.07.2014 09:25:51
Спасибо на добром слове, Ирина! Да, клиентов из 20% надо беречь ;)
04.07.2014 10:12:30
Раньше просто делали табличку, к примеру, Топ-10 клиентов или продуктов, которые делают выручку компании. А теперь еще с помощью этой диаграммы это можно визуально отобразить. Везде про ее построение написано очень замысловато. У Вас же все понятно и без лишних громоздких расчетов.
06.07.2014 14:26:40
Здравствуйте, Николай! все делал по примеру и по пунктам, столкнулся с подсветкой, возле сводной таблицы создал столбец "подсветка" http://joxi.ru/ISS5U_3JTJBCY_Ypsuc копирую его и вставляю, но в диаграмме ничего не появляется, и не понимаю что дальше делать, где искать Подсветку внутри диаграммы? или в сводных таблицах это исключено? по последней картинке видно что диаграмма из листа "простая"
06.07.2014 15:56:46
Роман, копированием добавить новый ряд можно только в обычную диаграмму (не в сводную). В сводной придется делать вычисляемый столбец, как это было описано в комментариях выше.
MEP
15.01.2015 12:40:34
Неплохо было бы в скачиваемый пример добавлять на втором листе описание с сайта. было бы очень удобно просматривать и учиться
06.03.2015 06:37:45
Добрый день, Николай! у меня почему-то первый столбец выходит за рамки 100% делал вроде строго по вашему примеру, подскажите в чем загвоздка.
https://yadi.sk/i/eFcSWCfEf58V8
08.06.2015 17:09:46
DВ Office 2007 вариант со сводной диаграмой не работает :( Помогите плиз.
19.07.2015 19:33:20
Можно сделать некое подобие вертикальной линии для пересечения с кривой и горизонтальной линией.
Добавляем еще один столбец и вписываем в него формулу:
=ЕСЛИ(И(СУММ($B$2:B2)/СУММ($B$2:$B$13)<0,8;СУММ($B$2:B3)/СУММ($B$2:$B$13)>0,8);B2;НД())

Добавляем этот ряд в диаграмму и пускаем по основной оси. Далее идем в Формат ряда и ставим градиентную заливку: заготовка по умолчанию, тип линейный, угол 0 градусов.
Далее делаем 6 точек градиента. Первой самой левой - назначаем прозрачность 100%. 2-ю и 3-ю располагаем в районе середины общей длины полосы меток. 4-ю и 5-ю где-то на 1/6 от общей длины полосы меток - это индивидуально надо смотреть - это расстояние будет влиять как раз на ширину линии. 3-ей и 4-ой назначаем нужный цвет. 2-ой, 5-ой и 6-ой назначаем прозрачность 100%.

Все, вот и получилась вертикальная линия. Муторно и запутанно звучит, но на деле все просто. Выглядит примерно так:
Парето с вертикальной линией
06.12.2015 14:57:02
Николай, добрый день!

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

Если все те же операции производить с обычным диапазоном, вычисляемое поле вставляется в сводную таблицу без проблем.
14.02.2016 17:01:28
Коллеги, подскажите...

Есть исходный диапазон о продажах содержит не группированные данные товаров.

https://yadi.sk/i/aAdQKliaopa2r

Как построить диаграмму Парето при условии динамически изменяемых исходного списка данных? Представление данных на графике необходимо по группам товаров. Перечень групп товаров - конечный. В столбце "наименование" товары собираются схоластично.

Группировка данных через сводную таблице не сильно помогает т.к. сводные таблицы автоматом не обновляются. График хотелось бы видеть всегда с актуальными данными.
20.06.2016 07:23:38
Добрый день. Не могу разобраться с константой 80%. Формат ячейки установил %, значение ввёл 80, но на графике линия остаётся где-то внизу.
При построении диаграммы Парето через сводную таблицу в вычисляемом поле ввожу порог =80%.

Данный график (линия) совсем не там где реально на графике 80%. Как добиться желаемого результата?