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

Как правильно в 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).

Create a Pareto Chart with Chart Studio and Excel

A Pareto chart combines a bar and line chart. The Pareto principle states that, ‘for many events, roughly 80% of the effects come from 20% of the causes.’ In this example, we will see that 80% of complaints come from 20% of the complaint types.

Upload your Excel data to Chart Studio’s grid

Open the data file for this tutorial in Excel. You can download the file here in CSV format

Head to Chart Studio

Head to the Chart Studio Workspace and sign into your free Chart Studio account. Go to ‘Import,’ click ‘Upload a file,’ then choose your Excel file to upload. Your Excel file will now open in Chart Studio’s grid. For more about Chart Studio’s grid, see this tutorial

Creating Your Chart

Once you’ve loaded the data in Chart Studio, label your columns like we did below. You’ll have complaint type on the x axis data and complaint count and cumulative percentage on the y axis data. Then, select ‘Line plots’ from the CHOOSE PLOT TYPE menu. When you’re finished, click on the blue ‘LINE PLOT’ button in the sidebar.

Your plot would initially look something like this.

Remember, a Pareto chart combines a bar and line chart. The complaint count will be the ‘bar’ portion of the chart and the cumulative percentage will be the ‘line’ portion of the chart. Let’s set the complaint count trace (Col2) to ‘Bar.’ Head to the ‘Traces’ menu and select ‘Bar’ as type.

Take a moment to resize your plot to something less wide. A width of 800 and a height of 600 seems reasonable. Head to the layout menu to do this.

Next, we’ll add a second y-axis for the other trace (Col3). To do this, head to the ‘Traces’ menu then to ‘New Axis/Subplot.’ Set your ‘New Y’ to ‘Right.’

Your plot should now look something like this.

Now, we’ll clean up the grid lines. Go to the ‘Axes’ menu, then select ‘Y Axis 2’ in the drop down menu. Set ‘Grid lines’ to off.

Finalizing Your Graph

Your graph should now look something like this.

Head to the ‘Traces’ menu and then the ‘Style’ tab to set the trace color to your liking. You can title your graph and axes like we did. You can add ‘Tick Suffixes’ to ‘Y Axis 2’ by heading to the ‘Axes’ menu, then the ‘Labels’ menu within.

Here’s our finished graph.

You can download your finished Chart Studio graph to embed in your Excel workbook. We also recommend including the Chart Studio link to the graph inside your Excel workbook for easy access to the interactive Chart Studio version. Get the link to your graph by clicking the ‘Share’ button. Download an image of your Chart Studio graph by clicking EXPORT on the toolbar.

To add the Excel file to your workbook, click where you want to insert the picture inside Excel. On the INSERT tab inside Excel, in the ILLUSTRATIONS group, click PICTURE. Locate the Chart Studio graph image that you downloaded and then double-click it. Notice that we also copy-pasted the Chart Studio graph link in a cell for easy access to the interactive Chart Studio version.

Пример построения диаграммы Парето в Excel скачать шаблон

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

На каком принципе основана диаграмма Парето

Название диаграммы происходит от имени итальянского экономиста Вильфредо Парето (1848-1923), который обнаружил интересную связь, позже названную правилом 20/80. Ну, в 1887 году в Италии он заметил, что 80% богатства всей страны принадлежало 20% населения. Принцип, замеченный Парето, стал восприниматься позже в других областях экономической практики. Спустя 54 года Джозеф Джуран, изучая состояние «качества», снова пришел к выводу, что 80% проблем вызваны 20% причинами, что подтверждает тезис его предшественника. На практике сам принцип 20/80 не всегда распределяется равномерно. Тем не менее, его применение может быть полезным в нескольких аспектах деятельности компании, например, для устранения наиболее распространенных причин неудовлетворенности услугами компании. Или чтобы вычислить те 20% клиентов, которые приносят 80% прибыли и т.п.

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

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

Для построения диаграммы мы будем использовать довольно обширный диапазон данных о компаниях из CNBC. Наш анализ буде, посвящен переводу прибыли и активов за границу крупнейшими компаниями США, чтобы избежать уплаты налогов в США. Необходимо узнать соотношение количества компаний, и какая на них приходится доля денег (в процентах) находящихся в офшорных зонах. Данные, которые нам понадобятся для построения диаграммы — это название компании и суммы оффшорного перевода. На основе этих двух столбцов мы создадим дополнительные столбцы, полезные для создания диаграммы Парето в Excel.

Подготовка данных и формулы

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

Таблица показателей активов в оффшорных зонах для всех известных компаний:

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

Добавим табличку для вычисления точки пересечения соотношений между процентной долей от целой суммы общего оффшорного капитала и количества компаний. В ячейке B2 мы указываем желаемое количество компаний. Так как график у нас на отдельном листе, а вместе с ним будет и интерфейс управления динамическим графиком – делаем ссылку на внешний лист «График».

Теперь нам необходимо сделать вспомогательный столбец, благодаря которому будут выбраны показатели процентной доли только лишь для определенного количества компаний указанного пользователем:

Половина подготовки данных сделана – подытожим, определив значения названий всех столбцов:

1. Количество – по данному столбцу будет определятся количество выбранных пользователем компаний.
2. Наименование – название компаний.
3. Оффшорные ресурсы (млн. $) – показатели объемов оффшорных финансовых ресурсов, полученных по статистическим данным из внешних соответственных источников.
4. Всего в офшорах – нарастающая сумма всех показателей объемов оффшорных финансов в соответствии с ростом количества компаний.
5. % оффшор – здесь показано какую процентную долю по нарастанию количества составляет сумма оффшорных финансов по отношению к общему объему средств.
6. Вспом. % оффшор – вспомогательный столбец для выборки процентной доли оффшорных ресурсов в зависимости от указанного количества компаний пользователем.

Порядок построения диаграммы Парето в Excel на основе данных

Используя подготовленную таким образом таблицу, мы создаем первую часть графика, благодаря которой получаем соответствующую горизонтальную ось X с количеством компаний. Для этого выделите два несмежных диапазона ячеек удерживая клавишу CTRL: A5:A291 и E5:E291 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Точечная»-«Точечная с гладкими кривыми»

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

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

Метод получения эффекта заполнения под графиком будет реализован с помощью масштабирования от столбца (X) до 0-1000. На этом этапе лучше воздержаться от подробного описания всех шагов, связанных с построением дополнительных таблиц, потому что в конце статьи можно скачать диаграмму Парето в Excel. На данном этапе достаточно лишь показать все формулы:

1. Вычисление входящего параметра формул, возвращающих значения необходимые для образования первой большой заполненной (статической-синей) диаграммы на графике.
2. Формула вычисления размеров по оси X для большой синей заполненной диаграммы:
3. Вычисление входящего параметра формул, возвращающих значения необходимые для образования второй малой заполненной (динамической-зеленой) диаграммы на графике.
4. Формула вычисления размеров по оси X для малой зеленой заполненной диаграммы:
5. Табличка для подписей пределов границ второй динамической зеленой диаграммы в месте точки пересечения соотношения количества компаний и процентной доли финансовых ресурсов в оффшорных зонах:
6. Табличка для вычисления столбцов границ пределов для второй динамической зеленой диаграммы в месте точки пересечения количества и доли %.

Таким образом мы подготовили все необходимые данные для построения графика Парето в Excel. Общая картина данных выглядит следующим образом:

Давайте перейдем к следующей части – настройка диаграммы Парето. На следующем шаге скопируйте все данные из столбца «Большая площадь» в диапазоне ячеек L3:M293 и нажмите на созданный график, чтобы активировать его. После чего выберите инструмент: «ГЛАВНАЯ»-«Буфер обмена»-«Вставить»-«Специальная вставка» или нажмите комбинацию горячих клавиш CTRL+ALT+V. В результате на график добавиться еще один ряд данных:

Мы снова нажимаем на график, подготовленный таким образом, как показано выше на рисунке и используя спрайт на вкладке «Работа с диаграммами»-«КОНСТРУКТОР»-«Тип»-«Изменить тип диаграммы», мы меняем точечный тип большого графика на диаграмму с областями, представленной на оси вспомогательных данных:

Выполните эти же действия скопировав на график данные из двух столбцов P4:Q293 таблицы «Малая площадь»:

В результате получим следующий вид графика Парето:

Теперь нам необходимо убрать лишнюю правую вертикальную ось Y значений процентов:

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

Меняем цвета заливок областей и делаем прозрачный цвет для синей линии чтобы скрыть ее:

График почти готов.

Информативный результат на диаграмме Парето в Excel

На этом этапе мы добавим к нему еще точечный график, который будет состоять из одной точки, из которой будем вести вертикальные и горизонтальные полосы границ погрешностей. Данный точечный график будет использовать значения из таблички «Точка пересечения». Для этого выделите график и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Данные»-«Выбрать данные»-«Добавить» и заполните параметрами поля ввода так как показано ниже на рисунке:

В 2 шага добавляем предел погрешностей для точки пересечения (вертикальную и горизонтальную линию):

1. Выбираем ряд данных (из одной точки) «Точка пересечения» с помощью инструмента: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Элементы диаграммы»-«Ряд Точка пересечения».
2. Добавляем ему новый элемент – «поредел погрешностей» с помощью инструмента: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Предел погрешностей»-«Дополнительные параметры предела погрешностей»:

В настройках формат предела погрешностей указываем пользовательскую величину погрешности взяв значения из ячеек I2 и J2.

Таким образом мы настроили вертикальную ось погрешностей, а сейчас настроим горизонтальную. Для этого выберите инструмент «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Элементы диаграммы»-«Ряд Точка пересечения Предел погрешностей по X»:

После чего снова выбераем инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Предел погрешностей»-«Дополнительные параметры предела погрешностей»:

Также как мы добавляли точку пересечения точечным графиком ряда данных из одной точки, аналогичным способом добавляем еще 2 точки, то есть еще 2 ряда данных точечного графика, которые берут данные из таблички XY+-:

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

Сначала выделяем одну из точек уже знакомым нам инструментом: инструмент «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Элементы диаграммы»-«Ряд подпись вертикаль». А затем добавляем подпись значений ряда: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Подписи данных»-«Справа». Делаем правый щелчок мышкой по подписи и из появившегося контекстного меню выбираем опцию: «Формат подписей данных». В настройке подписей отмечаем галочку «значения из ячеек» и указываем ссылку на значения из ячейки B2:

Выполняем аналогичные действия для ряда «подпись горизонталь» только указываем ссылку на ячейку B3. Динамическую диаграмму Парето копируем на отдельный лист под названием «График» и любуемся готовым результатом:

Диаграмма интерактивная пользователь вводит в ячейку F2 необходимое количество компаний, которое нужно выбрать из общей базы (на листе Данные). Жмем Enter и сразу обновляется результат визуализации данных с точными маркерами текущих значений соотношения количества компаний и их процентной доли оффшорных финансовых ресурсов от общего капитала в офшорных зонах.

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

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

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

Все обнаруженные причины проблем расставляем по частоте их возникновения. Глядя на цифры, вы обнаруживаете, что отсутствие коммуникации между исполнителями проекта и заинтересованными сторонами проекта является первопричиной 23 основных проблем, с которыми сталкивается команда, в то время как вторая по величине проблема – доступ к необходимым ресурсам (компьютерным системам, оборудованию и т.д.) повлекло за собой только 11 сопутствующих осложнений. Другие проблемы носят единичный характер. Понятно, что, решая проблему коммуникации можно устранить огромный процент проблем, а, решая проблему доступа к ресурсам, можно разрешить почти 90% препятствий на пути команды. Вы не только выяснили, как помочь команде, Вы только что провели анализ по принципу Парето.

Выполнение всей этой работы на бумаге, вероятно, потребует определённого количества времени. Процесс можно значительно ускорить, используя диаграмму Парето в Microsoft Excel.

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

Моя задача — помочь Вам подготовить данные для диаграммы Парето и затем создать саму диаграмму. Если Ваши данные уже подготовлены для диаграммы Парето, то можете переходить ко второй части.

Сегодня мы разберём проблемную ситуацию в компании, которая регулярно возмещает расходы сотрудников. Наша задача – выяснить, на что мы тратим больше всего, и понять, как можно уменьшить эти затраты на 80%, при помощи быстрого анализа Парето. Мы можем узнать, на какие затраты приходится 80% возмещений, и предотвратить высокие затраты в будущем за счет изменения политики в направлении использования оптовых цен и обсуждения расходов сотрудников.

Часть первая: Подготовьте данные для диаграммы Парето

1. Организуйте свои данные. В нашей таблице есть 6 категорий денежных компенсации и затребованные работниками суммы.
2. Отсортируйте данные по убыванию суммы. Проверьте, что выделены столбцы А и В, чтобы сортировка была выполнена правильно.
3. Сумму по столбцу Amount (количество расходов) вычислим при помощи функции СУММ (SUM). В нашем примере для того, чтобы получить итоговую сумму, нужно сложить ячейки от В3 до В8.

Горячие клавиши: Чтобы суммировать диапазон значений, выберите ячейку B9 и нажмите Alt+=. Общая сумма составит $12250.

1. 
2. Создайте столбец Cumulative Amount (накопительная сумма). Начнем с первого значения $3750 в ячейке B3. Каждое значение опирается на значение предыдущей ячейки. В ячейке C4 наберите =C3+В4 и нажмите Enter.
3. Чтобы автоматически заполнить оставшиеся ячейки столбца, дважды кликните по маркеру автозаполнения.
4. Далее, создайте столбец Cumulative % (накопительный процент). Для заполнения этого столбца можно использовать сумму диапазона Amount и значения из столбца Cumulative Amount. В строке формул для ячейки D3 введите =C3/$B$9 и нажмите Enter. Символ $ создаёт абсолютную ссылку таким образом, что значение суммы (ссылка на ячейку B9) не изменяется при копировании формулы вниз.
5. Дважды кликните по маркеру автозаполнения, чтобы заполнить столбец формулой, либо нажмите на маркер и протяните его по столбцу данных.
6. Теперь всё готово, чтобы приступить к построению диаграммы Парето!

Часть вторая: Строим диаграмму Парето в Excel

1. Выделите данные (в нашем примере – ячейки от A2 по D8).
2. Нажмите Alt+F1 на клавиатуре, чтобы автоматически создать диаграмму из выбранных данных.
3. Щелкните правой кнопкой мыши в области диаграммы и в появившемся меню нажмите Выбрать данные (Select Data). Появится диалоговое окно Выбор источника данных (Select Data Source). Выберите строку Cumulative Amount и нажмите Удалить (Remove). Затем ОК.
4. Кликните по графику и при помощи клавиш со стрелками на клавиатуре перемещайтесь между его элементами. Когда окажется выделенным ряд данных Cumulative %, который сейчас совпадает с осью категорий (горизонтальная ось), кликните по нему правой кнопкой мыши и в контекстном меню выберите Изменить тип диаграммы для ряда (Change Chart Series Type). Сейчас этот ряд данных увидеть трудно, но возможно.
5. Появится диалоговое окно Изменение типа диаграммы (Change Chart Type), выберите линейный график.
6. Итак, мы получили гистограмму и плоский линейный график вдоль горизонтальной оси. Для того, чтобы показать рельеф линейного графика, нам потребуется ещё одна вертикальная ось.
7. Щелкните правой кнопкой мыши по ряду Cumulative % и в появившемся меню нажмите Формат ряда данных (Format Data Series). Появится одноименное диалоговое окно.
8. В разделе Параметры ряда (Series Options) выберите По вспомогательной оси (Secondary Axis) и нажмите кнопку Закрыть (Close).
9. Появится ось процентов, а диаграмма превратится в полноценную диаграмму Парето! Теперь можно делать выводы: основную часть расходов составляют плата за обучение (Training Fees), оборудование (Hardware) и канцтовары (Office supplies).

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