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

276868 13.07.2013 Скачать пример


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

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

pareto1.png

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

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

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

Способ 1. Встроенный тип в Excel 2016 и новее

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

pareto19.png

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

pareto20.png

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

Способ 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

Способ 3. Частотный анализ в диаграмме Парето

Если бросить в область строк предыдущей сводной таблицы не товары, а прибыль, то можно легко сгруппировать её затем в интервалы с заданным шагом - для этого щёлкните правой кнопкой мыши по любому числовому значению прибыли и выберите из контекстного меню команду Группировать (Group). В открывшемся окне можно задать начальное и конечное значение и нужный нам шаг:

Группировка числовых данных в сводной

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

Парето по числовым интервалам

Если нужен, не постоянный, а переменный шаг, то придется рассмотреть другой способ - надстройку Пакет Анализа.

Способ 4. Диаграмма Парето с помощью надстройки Пакет анализа

Ещё один способ заключается в использовании надстройки Пакет Анализа, содержащей кучу аналитических и статистических инструментов, в том числе и частотный анализатор и построитель диаграммы Парето. Подключить надстройку можно либо через Файл - Параметры - Надстройки - Перейти (File - Options - Add-ins - Go to) либо кнопкой Надстройки Excel на вкладке Разработчик (Developer - Excel Add-ins).

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

После запуска надстройки на вкладке Данные - Анализ данных (Data - Data Analysis) выбираем в списке доступных инструментов Гистограмму (Histogram) и задаем в диалоговом окне все необходимые параметры:

Диаграмма Парето из Пакета Анализа

После нажатия на ОК получаем таблицу с результатами частотного анализа и диаграмму Парето, построенную по рассчитанным данным:

Готовая диаграмма Парето из Пакета Анализа

К недостаткам этого способа можно отнести отсутствие в результатах формул (т.е. пересчета при изменении исходных данных не будет) и подсчет только количества попаданий в каждый карман (а не суммы или среднего - как это можно произвольно выбрать в способе со сводной таблицей).

Способ 5. Диаграмма Парето по итогам на формулах

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

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

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

 


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 вариант со сводной диаграмой не работает :( Помогите плиз.
Можно сделать некое подобие вертикальной линии для пересечения с кривой и горизонтальной линией.
Добавляем еще один столбец и вписываем в него формулу:
=ЕСЛИ(И(СУММ($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%. Как добиться желаемого результата?
19.06.2017 14:01:41
Коллеги, подскажите, у меня excel 2010 всё сделал и получилось как показано, проблема с маркером в сводной таблице, ну никак не выходит и формулу ввести в вычисляемое поле тоже не дает.?
17.07.2017 15:36:26
Спасибо большое за информацию!
Но при построении диаграммы столкнулась с некоторыми проблемами и все никак не могу найти ошибку :(

Фактически, все столбцы аналогичны данным в примере (Наим.базы, Выручка, Накопл.доля), но при построении диаграммы по Варианту 1 столбец с наибольшей выручкой выходит за границу 100%. Вычисляла накопленную долю как в примере, проверяла через нахождение отдельных долей и затем суммирование - то же самое. Подскажите, пожалуйста, в чем может быть ошибка?

И второй вопрос: пыталась строить через встроенную диаграмму Парето и добавлять поля Порог и Подсветка, но они не отображаются (то есть в окне "Выбор источника данных они есть", а по факту их не видно, и в Формат - Область диаграммы их тоже нет). Получается, в встроенную диаграмму нельзя добавлять дополнительные обозначения?
23.02.2021 06:39:59
Всем доброго дня
Господа уже третий час с этой диаграммой никак не осилю.
Если кто владеет навыками подскажите как в этой диаграмме каждый вид овоща/фрукта отобразить с накоплением (к примеру яблоко флан 100, факт 75, т.е. Столбец в диаграмме с значением яблоко заполнен на 75% соответственно.
17.07.2023 19:23:10
Надеялся в "паре новых способов" увидеть метод касательных. ИМХО, это самый правильный способ делать ABC-анализ.
Наверх