Microsoft Excel

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

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

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

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

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

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

Рис. 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. Диаграмма Парето

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

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

Рис. 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

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

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

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

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

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

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

Рис. 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 (Макет)

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

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

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

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

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

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

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

Рис. 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 (Выбор источника данных)

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

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

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

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

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

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

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

СКАЧАТЬ ШАБЛОН «ДИАГРАММА ПАРЕТО / PARETO CHART», 17Kb

По теме

Новые публикации

Top