Диаграмма парето как построить в excel
Думаем, вам уже приходилось слышать о Парето. Если нет, напоминаем: Вильфредо Парето открыл так называемое правило 80/20. Анализируя статистические данные о землевладении в Италии, он пришел к выводу, что 80% земли в Италии принадлежит 20% населения. Открытое таким образом правило 80/20, применимо теперь ко многим дисциплинам по всему спектру отраслей.
Согласно теории Парето, относительно небольшое количество причин (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
Настраиваем диаграмму по своему усмотрению и получаем окончательный вид графика Парето в Excel:
Каскадная диаграмма показывает нарастающий итог по мере добавления или вычитания значений. Это помогает понять, как серия положительных и отрицательных значений влияет на исходную величину (например, чистую прибыль).
Столбцы обозначены цветом, чтобы можно было быстро отличить положительные значения от отрицательных. Столбцы начального и конечного значений часто начинаются с горизонтальной оси,в то время как промежуточные значения являются плавающими столбцами. Из-за такого вида каскадные диаграммы также часто называют диаграммами моста.
Создание каскадной диаграммы
Щелкните Вставка > Вставить каскадную или биржевую диаграмму > Каскадная.
Для создания каскадной диаграммы также можно использовать вкладку Все диаграммы в разделе Рекомендуемые диаграммы.
Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, и на ленте появится область Работа с диаграммами.
Итоги и промежуточные итоги с началом на горизонтальной оси
Если данные содержат значения, которые считаются итогами или итогами, например "Чистый доход", их можно настроить так, чтобы они начинались с горизонтальной оси с нуля и не "плавали".
Дважды щелкните точку данных, чтобы открыть области задач Формат точки данных, и установите в поле Установить как итог.
Примечание: Если щелкнуть столбец один раз, будет выбран ряд данных, а не точка данных.
Чтобы снова сделать столбец плавающим, снимите флажок Задать как итог.
Совет: Итоги также можно задать, щелкнув правой кнопкой мыши точку данных и выбрав в контекстном меню пункт Задать как итог.
Отображение и скрытие соединительных линий
Соединительные линии соединяют конец предыдущего столбца c началом следующего, помогая отобразить поток данных на диаграмме.
Чтобы скрыть соединитеальные линии, щелкните правой кнопкой мыши ряд данных, чтобы открыть области задач Формат ряда данных, и скроем поле Показать соединитеальные линии.
Чтобы снова отобразить эти линии, установите флажок Отображать соединительные линии.
Совет: В легенде диаграммы точки данных сгруппированы по типам: Увеличение, Уменьшение и Итог. Если щелкнуть легенду диаграммы, на диаграмме будут выделены все столбцы, соответствующие выбранной группе.
Вот как можно создать каскадную диаграмму в Excel для Mac:
На вкладке Вставка нажмите кнопку Каскадная (значок каскадной) и выберите каскадная.
Примечание: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, чтобы отобразить их на ленте.
В Excel диаграмма Парето состоит из столбца и линейной диаграммы, где столбцы столбцов представляют значения частоты в убывающем порядке, а линия указывает совокупные итоги на дополнительной оси. Он используется для анализа значительных дефектов продукта и определения улучшений, повышающих ценность компании. Эта статья поможет вам создать диаграмму Парето для отображения наиболее распространенных причин сбоев и дефектов продукта на листе Excel.
Создание простой диаграммы Парето в Excel 2016 и более поздних версиях
Если у вас есть Excel 2016 и более поздние версии, есть встроенная функция - Диаграмма Парето, которая поможет вам быстро и легко вставить диаграмму Парето. Пожалуйста, сделайте следующее:
1. Выберите диапазон данных, на основе которого вы хотите создать диаграмму Парето.
2, Затем нажмите Вставить > Вставить статистическую диаграмму > Парето, см. снимок экрана:
3. Затем сразу была создана диаграмма Парето, как показано на скриншоте ниже:
4. Затем вы можете отредактировать заголовок диаграммы и добавить метки данных по мере необходимости, см. Снимок экрана:
Создание простой диаграммы Парето в Excel 2013 и более ранних версиях
Если вы используете Excel 2013 и более ранние версии, вам следует выполнить следующие шаги один за другим:
Во-первых, подготовьте данные для создания диаграммы Парето:
1. Вы должны отсортировать данные в порядке убывания, выберите ячейку B2 и нажмите Данные > Сортировка Z по A, см. снимок экрана:
2. А затем диапазон данных был отсортирован в порядке убывания, см. Снимок экрана:
3. Затем вычислите совокупный счет, введя эту формулу = B2 в ячейку C2 в этом случае, и нажмите Enter ключ. Смотрите скриншот:
4. А затем введите эту формулу = C2 + B3 в ячейку C3 и перетащите маркер заполнения вниз к ячейкам, см. снимки экрана:
 |  |  |
5. После получения кумулятивного подсчета, продолжайте вычислять кумулятивный процент, введите следующую формулу: = C2 / $ C $ 11 в ячейку D2 и перетащите маркер заполнения в нужные ячейки, см. снимок экрана:
6. Затем вы должны преобразовать десятичные значения в процентные значения, выбрать ячейки формулы и затем щелкнуть Главная > Процентный стиль, см. снимок экрана:
Во-вторых, создайте диаграмму на основе данных
7. После подготовки данных выберите данные в столбце A, столбце B и столбце D по Ctrl ключ, см. снимок экрана:
8. А затем нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец, см. снимок экрана:
9. И вы получите диаграмму, как показано на скриншоте ниже:
10. Затем щелкните правой кнопкой мыши одну красную полосу (совокупный процент) и выберите Изменить тип диаграммы серии из контекстного меню см. снимок экрана:
11. В Изменить тип диаграммы диалоговое окно под Все диаграммы вкладку нажмите Комбо вариант на левой панели в Выберите тип диаграммы и ось для ряда данных список, щелкните раскрывающийся список в поле Накопительное и выберите График линии с маркерами введите и установите флажок, см. снимок экрана:
12. А затем нажмите OK кнопку, и вы получите диаграмму, как показано на скриншоте ниже:
13. Затем щелкните правой кнопкой мыши процентную ось и выберите Ось формата вариант из контекстного меню, см. снимок экрана:
14. В Ось формата панель, под Параметры оси вкладка рядом с максимальная, установите номер на 1.0 в текстовое поле, и диаграмма Парето была успешно создана, см. снимок экрана:
15. Наконец, вы можете изменить заголовок диаграммы и добавить метки данных по мере необходимости.
Создать динамическую диаграмму Парето в Excel
В этом разделе я расскажу о том, как создать динамическую диаграмму Парето с помощью полосы прокрутки. Когда вы изменяете целевое значение, щелкая или перемещая полосу прокрутки, диаграмма автоматически выделит полосу проблем, как показано ниже.
Чтобы создать эту диаграмму Парето, выполните следующие действия шаг за шагом:
1. Сначала вычислите совокупный процент в столбце C, примените эту формулу к ячейке C2:
2. Затем перетащите дескриптор заполнения вниз к ячейкам и отформатируйте десятичные числа как процентные значения, см. Снимок экрана:
3. Затем вам понадобятся три вспомогательные ячейки: Target, Cumulative Value, Scroll Bar Link Value, чтобы выполнить некоторые вычисления, как показано ниже:
4. После создания трех ячеек нажмите разработчик > Вставить > Полоса прокрутки (элемент управления формой), а затем нарисуйте полосу прокрутки, как показано ниже:
 | |  |
5. Затем щелкните полосу прокрутки правой кнопкой мыши и выберите Управление форматом из контекстного меню во всплывающем Форматировать объект диалоговое окно под Control на вкладке, установите относительные значения и укажите связанную ячейку, как показано ниже:
6. А затем нажмите OK кнопку, чтобы закрыть диалоговое окно. Теперь введите следующие две формулы в ячейки B14 и B15 отдельно, а затем отформатируйте их как процентные значения:
B14: =B16/100B15: =IFERROR(INDEX($C$2:$C$11,IFERROR(MATCH($B$14,$C$2:$C$11,1),0)+1),1)
7. И теперь вы должны создать два вспомогательных столбца рядом с исходными данными и ввести две формулы ниже в ячейки D2 и E2:
D2: =IF($B$15>=C2,B2,NA())E2: =IF($B$15<C2,B2,NA())
8. Затем скопируйте формулы в другие ячейки, как показано на следующем снимке экрана:
9. После создания данных выберите данные в столбце A, столбце C, столбце D, столбце E, удерживая Ctrl ключ, затем нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец, и диаграмма вставлена, как показано на скриншоте ниже:
10. Затем выберите диаграмму и нажмите Дизайн > Изменить тип диаграммы, В Изменить тип диаграммы диалоговое окно под Все диаграммы вкладку нажмите Комбо на левой панели, а затем щелкните Таблица пользовательских комбинаций, то в Выберите тип диаграммы и ось для ряда данных В разделе, пожалуйста, укажите следующие операции для каждой серии данных:
- Совокупный%: Тип линейной диаграммы и проверьте Вторичная ось также флажок;
- Выделенные полосы: Тип столбчатой диаграммы с кластерами;
- Оставшиеся бары: Тип кластерной столбчатой диаграммы.
11, Затем нажмите OK кнопку, чтобы закрыть диалоговое окно, и вы получите диаграмму, как показано на скриншоте ниже:
12. Затем щелкните правой кнопкой мыши процентную ось и выберите Ось формата вариант, в расширенном Ось формата панель, под Параметры оси вкладка рядом с максимальная, установите номер на 1.0 в текстовое поле, см. снимок экрана:
13. Затем щелкните правой кнопкой мыши любую серию столбцов на диаграмме и выберите Форматировать ряд данных, В Форматировать ряд данных панель, под Варианты серий вкладка, введите 100% в Перекрытие серий текстовое поле, см. снимок экрана:
14. Наконец, вы можете переместить полосу прокрутки под диаграмму и ввести эту формулу: = "Цель" & ТЕКСТ (B14, "0%") в ячейку под полосой прокрутки, чтобы получить целевое процентное значение, см. снимок экрана:
15. Теперь, когда динамическая диаграмма Парето создана, вы можете изменить цвет заливки выделенной полосы по своему усмотрению, а при изменении целевого значения с помощью полосы прокрутки диаграмма Парето будет обновляться автоматически, как показано ниже:
Видео: создание диаграммы Парето в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Читайте также: