Диаграмма парето как построить в excel

Создание диаграммы Парето в Microsoft Excel

Существуют задачи, которые нельзя решить с помощью Excel «методом научного тыка» или «сам всё знаю». Поэтому, порой приходится садиться за учебники и уходить в них с головой, чтобы последовательно освоить какой-нибудь новый метод. Вот так случилось и со мной, когда я, зная технологию построения диаграммы анализа Парето в предыдущей версии Excel 2003, не сумел «сходу» построить её в новой версии. Поэтому пришлось штудировать новые издания по Excel. Здесь я решил поделиться с вами моим опытом построения этой диаграммы.

Что такое анализ Парето и «с чем его едят»?

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

Результат анализа дает “принцип Парето” или принцип соотношения «20-80», который неоднократно подтверждался исследованиями в самых различных сферах жизни. Например, только 20% товаров определяют 80% всех доходов компании; 20% солдат совершают 80% подвигов; 80% дефектов возникает из-за 20% причин. Этот принцип можно научиться применять и в нашей повседневной действительности, например, чтобы что-нибудь сэкономить. Допустим, можно оценить долю действительно полезных вещей в доме, долю полезной информации в книге, долю важный файлов на жестком диске компьютера.

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

Пошаговая инструкция по созданию диаграммы Парето

1. Сперва необходимо подготовить данные. Предлагаю воспользоваться реальными данными, которые предлагает нам Госкомстат.
2. Открываем пункт «Национальные счета», затем подпункт «Валовой внутренний продукт» а там «Произведенный ВВП» — «Данные по разделам ОКВЭД» в текущих ценах.
3. Сохраняем файл с данными на свой жесткий диск и открываем его для редактирования.
4. Выделяем данные по разделам за 2012 год и копируем их на новый лист. Задаем подписи данных.
5. Создаем рядом два дополнительных столбца и подписываем их «Доля» и «Доля с нарастанием».
6. Производим сортировку данных по убыванию в столбце «Вклад сферы в валовую добавленную стоимость».
7. Внизу под данными столбца «Вклад сферы в валовую добавленную стоимость» подсчитываем сумму.
8. В столбце «Доля» создаем формулу отношения значения конкретного раздела к общей сумме. Копируем протягиванием формулу для всех ячеек столбца.
9. В столбце «Доля с нарастанием» берём результат значения первой ячейки столбца «Доля».
10. Во второй ячейке суммируем значение верхней ячейки и значение боковой ячейки из столбца «Доля».
11. Протягиваем ячейку, и заполняем все оставшиеся ячейки. В самой последней ячейке столбца «Доля с нарастанием» должно появиться значение «100%».
12. Скрываем столбец «Вклад сферы в валовую добавленную стоимость» со всеми данными.
13. Выделяем все оставшиеся данные со столбцами и нажимаем на «Вставка» -> «Гистограмма».
14. Выбираем столбец второго ряда (тот который идет вверх, и нажимаем правую кнопку мыши.
15. Здесь в контекстом меню выбираем «Изменить тип диаграммы для ряда» и тип диаграммы «График с маркерами».
16. Ограничиваем ось значений значением «100%».
17. Настраиваем графики в соответствии с вашими требованиями. Область диаграммы можно разместить на отдельном листе.

Скачать решение Парето

Вот в принципе и всё, что нужно сделать для построения диаграммы Парето в программе Microsoft Excel 2007. Надеюсь, что у вас всё получилось с первого раза. Если нет, то советую прочитать ещё раз все шаги построения. Также можно посмотреть видеоролик о том, как построить диаграмму Парето.

Как построить диаграмму Парето в Excel

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

Интерпретация довольно проста. Столбики – это отсортированные по убыванию значения отдельных элементов. График – соответствующие накопленные доли. Последнее значение равно 100%. Чтобы построить подобную диаграмму в Excel 2013 и более ранних версиях, нужно выполнить следующие действия:

1) отсортировать данные по убыванию, чтобы значения, имеющие наибольший вклад, были в начале списка;

2) рассчитать столбец с накопленными долями;

3) использовать комбинированную диаграмму, чтобы столбиками показать отдельные элементы, а графиком накопленные доли.

В целом не сложно (пример построения диаграммы Парето таким способом показан в ролике ниже). Однако при обновлении данных как минимум сортировку придется провести заново. Возможно, придется настраивать новые диапазоны и формулы, если количество данных изменилось.

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

Построение диаграммы Парето в Excel 2016

Допустим, анализируется доходность групп товаров. Данные расположены по алфавиту.

Изобразим на основе этих данных гистограмму Парето (в Excel 2016). Вначале активируем любую ячейку в исходных данных. Затем через Рекомендуемые диаграммы либо напрямую выберем гистограмму Парето.

По умолчанию получим следующий вид диаграммы.

Полдела сделано. Осталось подобрать дизайн диаграммы и отдельных ее элементов.

Прелесть этой диаграммы в том, что она не зависит от порядка расположения исходных данных. Наиболее важные элементы всегда будут на первом месте. Так что, если данные обновить, то все будет работать, как надо.

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

ВВТ Лицензии Минпромторга на вооружение и военную технику

Поможем получить лицензии Минпромторга на ВВТ, ФСБ на гостайну, ФСТЭК России. Внедрим и проведем сертификацию СМК в соответствии с ГОСТ РВ 0015-002-2020, ГОСТ Р ИСО 9001-2015. Подготовим документы на получение КЭР.

Диаграмма Парето. Работа в MS Excel

Диаграмма Парето(Вильфредо Федерико Дамасо Парето) строится в виде столбчатого графика и показывает в убывающем порядке относительное влияние каждой причины на общую проблему. Кроме того, на диаграмме обычно приводят кумулятивную кривую накопленного процента причин.

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

Диаграмму Парето целесообразно применять вместе с причинно-следственной диаграммой.

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

При использовании диаграммы Парето для контроля важнейших факторов распространён АВС-анализ. Например, если на складе находится большое число деталей, проводить контроль всех деталей без всякого различия неэффективно. Но если разделить детали на группы по их стоимости, то на долю группы наиболее дорогих деталей (группа А), составляющих 20-30% от общего числа деталей, придётся 70-80% от общей стоимости всех деталей. На долю группы самых дешёвых деталей (группа С), составляющей 40-50% от всего количества деталей, придётся всего 5-10% от общей стоимости. Стоимость промежуточной группы (группа В) составляет 20-30% от общей стоимости. Контроль деталей на складе будет эффективным, если контроль деталей группы А будет самым жёстким, а контроль деталей группы С – упрощённым.

Рекомендуется составлять несколько вспомогательных диаграмм, входящих в состав группы А, с тем чтобы, последовательно анализируя их, в конечном итоге составить отдельную диаграмму Парето для конкретных явлений недоброкачественности.

Пример 1.1. Исследовать проблему появления брака при выпуске деталей.

С учётом того, что потери от брака одной детали каждого вида примерно одинаковы, в качестве единицы измерения выбираем число дефектных деталей каждого вида. После заполнения контрольных листков получаем данные, представленные в табл. 7.1.

№ детали	1	2	3	4	5	6	Прочие
Число дефектных деталей	255	101	59	39	26	15	11

По полученным данным разрабатываем таблицу для проверок данных. Создаём новую книгу Excel. В ячейке А1 вводим заголовок работы. В ячейки А3:Е3 вводим заголовки: № детали, Число дефектных деталей, Накопленная сумма деталей, Процент деталей, Накопленный процент. Для компактного размещения заголовков выделяем третью строку и используем команду Формат>Ячейки…, вкладку Выравнивание, режим выравнивания по вертикали По центру, режим отображения Переносить по словам.

В ячейки А4:В10 вводим данные из таблицы 7.1. В ячейку А11 вводим заголовок Итого. В ячейке В11 рассчитываем суммарное число дефектных деталей при помощи математической формулы СУММ.

Для расчёта накопленной суммы деталей в ячейку С4 вводим значение 255, т.е. число дефектных деталей 1. В ячейке С5 суммируем число дефектных деталей 1 и 2, т.е. вводим формулу =C4+B5. Для расчёта накопленной суммы деталей в остальных ячейках копируем формулу из ячейки С5 в диапазон С6:С10.

Для расчёта процента деталей следует делить число дефектных деталей каждого вида на общее число дефектных деталей и умножать на 100. Таким образом, в ячейку D4 вводим формулу =B4/B11*100. После указания необходимой абсолютной адресации копируем эту формулу в диапазон D5:D10. В ячейке D11 рассчитываем суммарный процент, который должен составить 100%.

Для расчёта накопленного процента деталей в ячейку Е4 значение (только значение, а не формулу) из ячейки D4. Для этого используем команды Правка>Копировать и Правка>Специальная вставка… . В ячейке Е5 суммируем процент дефектных деталей 1 и 2, т.е. вводим формулу =E4+D5. Для расчёта накопленного процента в остальных ячейках копируем формулу из ячейки Е5 в диапазон Е6:Е10.

По таблице для проверок данных строим диаграмму Парето. Для этого открываем в мастере диаграмм вкладку Нестандартные, выбираем диаграмму типа График/гистограмма 2. На втором шаге указываем диапазон данных А4:В10; Е4:E10. На третьем шаге вводим заголовки и убираем легенду.

После создания диаграммы мастером диаграмм редактируем её при помощи контекстных меню. В частности, максимальное значение шкалы Число дефектных деталей указываем 506, а минимальное 0. Максимальное значение шкалы Накопленный процент указываем 100. Открываем контекстное меню на одном из столбцов, выбираем команду Формат рядов данных…, вкладку Параметры, и устанавливаем ширину зазора 0.

Результаты расчётов и построений показаны на рис. 1.1.

Рис. 1.1. Построение диаграммы Парето по числу дефектных деталей.

Как видно из диаграммы, к группе А можно отнести детали 1 и 2 (70% от брака), к группе В – детали 3,4,5, к группе С – детали 6 и прочие.

Для выяснения наиболее важных дефектов целесообразно построить диаграммы Парето по явления дефектности в деталях 1 и 2.

Рассмотрим построение такой диаграммы для детали 1. В качестве единицы измерения выбираем сумму потерь от брака, млн. руб. После исследования явлений дефектности получили данные, представленные в табл. 1.2.

Дефект	Сумма потерь, млн. руб.
Шаг резьбы завышен	1,5
На режущей кромке резца налипы	6,9
Зависание	1,9
Пропуск операции	0,4
Осталась чернота	0,9
Скос кромки увеличен	0,6
Наружный диаметр занижен	8,3
Прочие	0,2

Диаграмма Парето, построенная по этим данным, показана на рис.1.2.

Рис.1.2. Диаграмма Парето по дефектам детали 1.

Как видно из диаграммы, к группе А можно отнести занижение наружного диаметра и налипы на режущей кромке резца (73% от суммы потерь), к группе В – зависание, завышение шага резьбы, остаточную черноту, к группе С – увеличение скоса кромки, пропуск операции и прочие.

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

При построении такой диаграммы для причин занижения наружного диаметра после заполнения контрольных листков получили данные, представленные в табл. 1.3.

Причина	Число дефектов
Смещение копира	53
Неопытность оператора	11
Неточность рабочего инструмента	4
Устаревший чертёж	98
Ошибки в управлении станком	20
Неточность станка	8
Прочие	7

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

Статистические методы управления качеством
Лабораторные работы, учебник
В.В.Заляжных, к.т.н.

• Адрес:
  187342, Ленинградская область, г. Кировск, ул. Магистральная, дом 48б, офис 40. Часы работы:
  Понедельник—пятница: 8:00–17:00
  Суббота и воскресенье: выходной Телефон:
  +7(911) 740 19 36; +7(951) 659 16 68 Наши
  сайты:
  Разработка и получение КЭР:
  Анализ действующей разрешительной документации, доработка недостающей
  документации, разработка и согласование технологических нормативов, НДТ,
  прохождение ГЭЭ. — http://center-vvt.ru
  Процессный подход в ISO 9001, ГОСТ РВ (пример использования) —
  https://ввт-центр.рфe-mail: office@vvt.center

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

Возможно вы уже слышали про «Закон Парето» или «Принцип 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 — если правее. Затем нужно сделать следующее:

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

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

Способ 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
• Статья про закон Парето в Википедии