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

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

Анализ диаграммы Парето помогает оценить эффективность деятельности бизнеса компании. Давайте разберемся как построить диаграмму Парето в Excel.

Закон Парето (правило Парето) в общем виде звучит как «20% усилий дают 80% результата, остальные 80% усилий дают оставшиеся 20% результата». Поэтому грамотное построение анализа поможет определить сильные стороны бизнеса (ресурсы, которые нужно развить и усилить), так и слабые (ресурсы, которые также нужно существенно улучшить или отказаться).

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

Предположим, что у нас есть данные по продажам продуктов в следующей таблице:


Данные в таблице не упорядочены, поэтому в первую очередь отсортируем данные по убыванию прибыли.
Для этого выделим таблицу и выберем в панели вкладок Данные -> Сортировка и фильтр -> Сортировка:

Построение вспомогательной таблицы

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

Расшифровка формул вспомогательной таблицы

Перенос ряда на вспомогательную ось

Изменение типа диаграммы для ряда

Добавление горизонтальной линии на диаграмму

Добавление подсветки на диаграмму

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

Настраиваем диаграмму по своему усмотрению и получаем окончательный вид графика Парето в 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. Надеюсь, что у вас всё получилось с первого раза. Если нет, то советую прочитать ещё раз все шаги построения. Также можно посмотреть видеоролик о том, как построить диаграмму Парето.

Microsoft Excel

трюки • приёмы • решения

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

Думаем, вам уже приходилось слышать о Парето. Если нет, напоминаем: Вильфредо Парето открыл так называемое правило 80/20. Анализируя статистические данные о землевладении в Италии, он пришел к выводу, что 80% земли в Италии принадлежит 20% населения. Открытое таким образом правило 80/20, применимо теперь ко многим дисциплинам по всему спектру отраслей.

Читать еще:  Как в excel отфильтровать ячейки по цвету

Согласно теории Парето, относительно небольшое количество причин (20%) порождает большинство проблем (80%). Диаграмма Парето отображает эту информацию в виде гистограммы. Гистограммы — это столбиковые диаграммы, которые отображают распределение переменных по времени. С точки зрения качества эти переменные упорядочиваются по таким факторам, как частота возникновения дефектов, затраты, задержки и т.д. По мнению Парето, гораздо целесообразнее тратить время на устранение именно тех причин, которые порождают большинство проблем.

Допустим, целью нашего проекта является разработка нового производственного процесса. Нас интересует, в частности, такой показатель, как частота возникновения дефектов в этом новом процессе. Для отображения этой информации можно построить диаграмму Парето. Прежде всего, нужно собрать соответствующие данные. На рис. 1 приведен пример показателей качества, касающихся частоты возникновения дефектов (столбец Defect Frequency).

Рис. 1. Частота возникновения дефектов

Чтобы получить процентные величины, мы используем формулу =+B2/$B$7*100 — для столбца С (строка 2), формулу =+B3/$B$7*100 — для столбца С (строка 3) и т.д. Идея заключается в том, что для получения процентных величин мы берем значение в столбце Defect Frequency (Частота возникновения дефектов), делим его на общее количество дефектов и умножаем полученное значение на 100. Столбец С форматируется таким образом, чтобы в его ячейках отображались значения в процентах. В столбце D мы начинаем в строке 2 с формулы =+С2 , а затем начинаем наращивать последующие строки, добавляя значение в предыдущей строке. Например, в столбце D, строка 3, содержится формула =+D2+C3 . Мы берем значение в текущей строке и добавляем его к значению предыдущей строки. И действуем в том же духе, пока значение в последней строке столбца не увеличится до 100.

Данные в этой диаграмме упорядочены по частоте возникновения дефектов. Изделие Е характеризуется самым высоким количеством дефектов, а изделие В — самым низким. Количество дефектов в процентном отношении отображено в столбце С, а в столбце D показан накопленный процент дефектов. В данном примере нетрудно увидеть, что нам приходится тратить большую часть времени на работу по исправлению проблем с изделиями Е и С, поскольку именно они порождают 80% наших проблем. На рис. 2 та же информация представлена в виде диаграммы Парето.

Рис. 2. Диаграмма Парето

Построение этой диаграммы выполняется в два этапа. Для начала выделите данные в ячейках А2-В6. Затем, удерживая нажатой клавишу Ctrl, выделите данные диапазона ячеек D2-D6. Далее активизируйте вкладку Insert (Вставка) и в группе Charts (Диаграммы) щелкните на кнопке Column (Гистограмма). В появившемся меню щелкните на первом значке группы 2-D Column (Гистограмма), как показано на рис. 3. (Если вы хотите заглянуть вперед, чтобы ознакомиться с результатами указанных действий, взгляните на рис. 5).

Рис. 3. Вставка гистограммы

Возможно, вы обратили внимание, что в нижней части меню кнопки Column (Гистограмма) (см. рис. 3) предусмотрена команда All Chart Types (Все типы диаграмм). Эта команда доступна независимо от выбранного вами типа диаграммы. Если ее активизировать, на экране появится диалоговое окно Insert Chart (Вставка диаграммы) (рис. 4) с перечнем всех без исключения типов диаграмм, которые можно построить в Microsoft Excel (Column (Гистограмма), Line (График), Pie (Круговая) и т.н.).

Сразу после создания диаграммы в правой части ленты Microsoft Excel появятся две дополнительные вкладки — Design (Конструктор) и Format (Формат), — которые предназначены для редактирования и форматирования диаграммы. Например, с помощью вкладки Design можно редактировать цвет и внешний вид столбцов и линий диаграммы. Для этой цели на этой вкладке предусмотрена группа параметров Styles (Стили). Для просмотра и применения стилей воспользуйтесь полосой прокрутки, которая расположена справа от упомянутой выше группы. Параметры вкладки Layout позволяют редактировать названия диаграммы и ее осей, добавлять системы обозначений (так называемую легенду) и т.п. Ниже мы покажем, как это делается.

Рис. 4. В диалоговом окне Insert Chart (Вставка диаграммы) приведены все типы диаграмм, которые можно построить в Microsoft Excel

Прежде всего, нам нужно отобразить данные о накопленном проценте дефектов не в виде столбцов, а в виде графика. Щелкните правой кнопкой мыши на столбцах диаграммы, которая представляет данные Series2 (Ряд2) (в нашем случае это данные накопленного процента), чтобы отобразить контекстное меню, показанное на рис. 5.

Рис. 5. Изменение типа диаграммы

Затем выберите в этом контекстном меню команду Change Series Chart Туре (Изменить тип диаграммы для ряда). На экране появится диалоговое окно Change Chart Туре (Изменение типа диаграммы) с перечнем всех типов диаграмм (см. рис. 4). Для того чтобы отобразить ряд данных не в виде столбцов, а в виде кривой линии, щелкните в этом диалоговом окне на значке Line with Markers (График с маркерами). (Обратите внимание, что название каждого типа диаграммы отображено на экранной подсказке. Чтобы отобразить саму подсказу, задержите указатель мыши над значком интересующего вас типа диаграммы.) Щелкните на кнопке ОК. На рис. 6 показаны данные накопленного процента, измененные со столбцового отображения на линейное.

Рис. 6. Для отображения ряда данных Series2 выбран другой тип диаграммы — Line with Markers (График с маркерами)

Теперь наша диаграмма отформатирована надлежащим образом. Далее этой диаграмме, а также вертикальной и горизонтальной осям нужно присвоить названия.

Читать еще:  Как в эксель проставить нумерацию страниц

Активизируйте диаграмму, щелкнув на ней мышью. В группе параметров Labels (Подписи) вкладки Layout (Макет) щелкните на кнопке Chart Title (Название диаграммы), как показано на рис. 7. На вкладке Layout (Макет) (см. рис. 7) предусмотрено несколько групп параметров, с помощью которых можно быстро изменить формат области построения диаграммы, скрыть/отобразить ее оси, вставить рисунок, текстовую область с пояснениями и т.п.

После щелчка на кнопке Chart Title (Название диаграммы) на экране появится меню с тремя командами: None (Отсутствует), Centered Overlay Title (Название по центру с перекрытием) и Above Chart (Над диаграммой). При выборе команды Centered Overlay Title название будет помещено поверх диаграммы без изменения ее размера. При выборе команды Above Chart программа автоматически уменьшит размер диаграммы в соответствии с размерами названия. В нашем примере использована команда Above Chart, поэтому нам нужно изменить размер диаграммы таким образом, чтобы ее название соответствовало этому размеру.

Рис. 7. Кнопка Chart Title (Название диаграммы) расположена в группе Labels (Подписи) вкладки Layout (Макет)

Для этого просто перетащите в нужном направлении один из угловых маркеров диаграммы. После выбора команды Above Chart Excel поместит над диаграммой текстовую область (рис. 8). Щелкните мышыо внутри этой области и введите название диаграммы. Названия осей присваиваются точно так же. Выделите диаграмму, а затем на вкладке Layout щелкните на кнопке Axis Titles (Названия осей).

В появившемся меню выберите ось, которой вы хотите присвоить название. В подменю для основной горизонтальной оси предусмотрены только две команды: None (Отсутствует) и Title Below Axis (Название под осью). В подменю для основной вертикальной оси предусмотрены четыре команды: None (Отсутствует), Rotated Title (Повернутое название), Vertical Title (Вертикальное название) и Horizontal Title (Горизонтальное название). Рядом с каждой из этих команд в схематичном виде показан пример размещения названия.

Рис. 8. Присвоение названия диаграмме

Вкладка Format (Формат) позволяет вам форматировать текстовые элементы диаграммы, добавлять заливку, изменять формат контуров объектов и т.д. Например, в названии нашей диаграммы и в названиях осей мы изменили заливку текста, чтобы шрифт отображался более темным цветом. Параметры вкладки Format показаны на рис. 9.

Рис. 9. Параметры вкладки Format

Если перед созданием диаграммы вы забыли включить в выделенный диапазон ячеек заголовки столбцов (как это произошло с нами), программа автоматически присвоит указанным вами рядам данных названия Series1 (Ряд1) и Series2 (Ряд2) (см. рис. 8). Подобные обозначения размещаются в пределах легенды диаграммы и, к сожалению, являются маловразумительными. В нашем примере необходимо, чтобы вместо Series1 было название Defect Frequency (Частота возникновения дефектов), а вместо Series2 — название Cumulative Percent (Накопленный процент). Сделать это можно следующим образом.

Щелчком мыши выделите легенду (расположена в правой части диаграммы), в которой указаны названия рядов Series1 и Series2. Затем щелкните на легенде правой кнопкой мыши и в появившемся контекстном меню выберите команду Select Data (Выбрать данные) либо щелкните на кнопке Select Data вкладки Design. В любом случае на экране появится диалоговое окно Edit Data Source (Выбор источника данных), показанное на рис. 10. В группе параметров Legend Entries (Series) (Элементы легенды (ряды)) этого диалогового окна выделите элемент Series1 (Ряд1), а затем щелкните мышью на кнопке Edit (Изменить). На экране появится диалоговое окно Edit Series (Изменение ряда), показанное на рис. 11.

Рис. 10. Диалоговое окно Edit Data Source (Выбор источника данных)

Рис. 11. Диалоговое окно Edit Series (Изменение ряда)

В текстовом поле Series Name (Имя ряда) диалогового окна Edit Series укажите название ряда Series1. Для этого просто щелкните мышью на ячейке В1, а затем — на кнопке ОК. В результате ваших действий вместо заданного по умолчанию названия ряда данных Series1 появится фраза Defect Frequency (Частота возникновения дефектов), как показано на рис. 12.

Рис. 12. Переименование отдельных элементов системы обозначений

Выполните такие же действия для переименования ряда Series2 (Ряд2), чтобы получить окончательный вариант диаграммы Парето (см. рис. 2).

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

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

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

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

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

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

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

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

Читать еще:  Формат xml как создать из excel

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

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

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

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

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

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

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

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

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

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

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

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

Если выбрать два столбца чисел, а не одно из чисел и одну соответствующую текстовую категорию, Excel отобразит данные в виде интервалов, как на гистограмме. Затем вы можете настроить эти интервалы.

Выберите Вставка > Вставить диаграмму статистики, а затем в разделе Гистограмма, щелкните элемент Парето.

Чтобы создать диаграмму Парето, можно также воспользоваться вкладкой Все диаграммы в области Рекомендуемые диаграммы: выбрать Вставка > Рекомендуемые диаграммы и перейти на вкладку Все диаграммы.

Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте диаграммы Парето, чтобы добавить на ленту область Работа с диаграммами.

Настройка интервалов

Щелкните правой кнопкой мыши горизонтальную ось диаграммы и выберите Формат оси >Параметры оси.

Руководствуясь приведенными ниже сведениями, выберите нужные параметры в области задач Формат оси.

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

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

Автоматически . Это параметр по умолчанию для диаграммы Парето с одним столбцом данных. Длина интервала вычисляется по формуле Скотта.

Длина интервала . Введите положительное десятичное число, указывающее количество точек данных в каждом диапазоне.

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

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

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

Формулы для создания гистограмм в Excel 2016

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

Выход за верхнюю границу интервала

Выход за нижнюю границу интервала

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

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

Если выбрать два столбца чисел, а не одно из чисел и одну соответствующую текстовую категорию, Excel отобразит данные в виде интервалов, как на гистограмме. Затем вы можете настроить эти интервалы. Подробные сведения можно найти в разделе «Настройка ячеек» на вкладке «Windows».

На ленте откройте вкладку Вставка и щелкните (значок Статистическая диаграмма), а затем в разделе гистограмма выберите пункт Парето.

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

Совет: Дополнительные сведения о диаграммах Парето и их пользе для визуализации статистических данных см. в этой записи о гисторамме, диаграммах Парето и «ящик с усами» блога группы разработчиков Excel. Дополнительные сведения о других новых типах диаграмм приведены в этой записи блога.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector