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

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

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

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

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

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

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

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

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

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

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

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

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