Сделать диаграмму Парето в Excel
В учебнике объясняются основы анализа Парето и показано, как создать диаграмму Парето в разных версиях Excel.
В идеальном мире все было бы в гармонии — за каждую работу платили бы одинаково, каждый налогоплательщик получал бы одинаковые налоговые льготы, каждый игрок был бы одинаково важен для команды. Но наш мир не идеален, и отношения между входами и выходами не равны. Вы хотите знать основные причины, которым вы должны посвятить больше всего усилий? Это и есть принцип Парето, или закон меньшинства.
Анализ Парето в Excel
Анализ Парето основан на принципе Парето, названном в честь итальянского экономиста Вильфредо Парето. И этот принцип гласит, что для многих событий около 80% следствий происходят от 20% причин. Вот почему принцип Парето иногда называют правилом 80/20.
Вот несколько практических примеров применения принципа Парето:
- В экономике самые богатые 20% населения мира контролируют около 80% мирового дохода.
- Сообщается, что в медицине 20% пациентов используют 80% ресурсов здравоохранения.
- В программном обеспечении 20% ошибок вызывают 80% ошибок и сбоев.
Чтобы определить наиболее важные факторы, на которые следует обратить внимание, вы можете нарисовать диаграмму Парето на листе Excel.
Диаграмма Парето в Excel
Диаграмма Парето, также называемая диаграмма Парето, представляет собой график, основанный на принципе Парето. В Microsoft Excel это своего рода отсортированная гистограмма, которая содержит как вертикальные полосы, так и горизонтальную линию. Столбцы, расположенные в порядке убывания, представляют относительную частоту значений, а линия представляет собой кумулятивный общий процент.
Вот как выглядит типичная диаграмма Парето в Excel:
Как видите, диаграмма Парето выделяет основные элементы в наборе данных и показывает относительную важность каждого элемента для итоговой суммы. Ниже вы найдете подробные инструкции о том, как создать диаграмму Парето в разных версиях Excel.
Как сделать диаграмму Парето в Excel 2016 – 365
Построить диаграмму Парето в современных версиях Excel несложно, поскольку она имеет встроенный тип диаграммы Парето. Все, что вам нужно, — это список элементов (проблем, факторов, категорий и т. д.) в одном столбце и их количество (частота) в другом столбце.
В качестве примера мы собираемся провести анализ Парето типичных жалоб пользователей на программное обеспечение на основе этого набора данных:
Чтобы построить диаграмму Парето в Excel, выполните следующие простые действия:
- Выберите свой стол. В большинстве случаев достаточно выбрать только одну ячейку, и Excel автоматически выберет всю таблицу.
- На Вставлять вкладка, в Графики группа, нажмите Рекомендуемые графики.
- Переключиться на Все диаграммы вкладка, выберите Гистограмма на левой панели и щелкните миниатюру Парето.
- Нажмите ХОРОШО.
Вот и все! Диаграмма Парето сразу же вставляется в рабочий лист. Единственное улучшение, которое вы, вероятно, захотите сделать, это добавить/изменить заголовок диаграммы:
Настройка графика Парето в Excel
Диаграмма Парето, созданная в Excel, полностью настраиваема. Вы можете изменить цвета и стиль, показать или скрыть метки данных и многое другое.
Создайте диаграмму Парето по своему вкусу
Щелкните в любом месте диаграммы Парето, чтобы Инструменты диаграммы появиться на ленте. Переключиться на Дизайн и поэкспериментируйте с различными стилями и цветами диаграмм:
Показать или скрыть метки данных
По умолчанию диаграмма Парето в Excel создается без меток данных. Если вы хотите отобразить значения столбцов, щелкните значок Элементы диаграммы в правой части диаграммы, установите флажок «Метки данных» и выберите, где вы хотите разместить метки:
Основная вертикальная ось, показывающая те же значения, стала лишней, и ее можно скрыть. Для этого нажмите кнопку Элементы диаграммы кнопку еще раз, затем нажмите маленькую стрелку рядом с Осии снимите флажок Основная вертикальная ось коробка.
В результате диаграмма Парето будет выглядеть примерно так:
Как создать диаграмму Парето в Excel 2013
В Excel 2013 нет предопределенного параметра для диаграммы Парето, поэтому мы будем использовать тип комбинированной диаграммы, который наиболее близок к тому, что нам нужно. Для этого потребуется еще несколько шагов, потому что все манипуляции, которые современный Excel выполняет за кулисами, вам придется делать вручную.
Организация данных для анализа Парето
Настройте набор данных, как описано ниже:
1. Рассчитать кумулятивный общий процент
Добавьте еще один столбец в свой набор данных и введите туда формулу нарастающего итога в процентах:
=СУММ($B$2:B2)/СУММ($B$2:$B$11)
Где B2 — первая, а B11 — последняя ячейка с данными в Считать столбец.
В дивиденде вы помещаете формулу кумулятивной суммы, которая суммирует числа в текущей ячейке и во всех ячейках над ней. Затем вы делите часть на сумму, чтобы получить проценты.
Введите приведенную выше формулу в первую ячейку, а затем скопируйте ее вниз по столбцу. Чтобы результаты отображались в процентах, установите Процент формат столбца. Если вы хотите, чтобы проценты отображались как целые числа, уменьшите количество десятичных разрядов до нуля (см. инструкции в разделе Как отображать десятичные разряды в Excel).
2. Сортировать по количеству в порядке убывания
Так как столбцы на диаграмме Парето должны располагаться в порядке убывания, расположите значения в Считать столбец от высшего к низшему. Для этого выберите любую ячейку и нажмите AZ на Данные вкладка, в Сортировка и фильтрация группа. Если Excel предлагает расширить выборку, сделайте это, чтобы сохранить строки вместе при сортировке.
Кроме того, добавьте автоматический фильтр, чтобы иметь возможность быстрее пересортировать данные в будущем.
На этом этапе ваши исходные данные должны выглядеть примерно так:
Нарисуйте диаграмму Парето
При правильно организованных исходных данных создать диаграмму Парето так же просто, как 1-2-3. Буквально всего 3 шага:
- Выберите свою таблицу или любую ячейку в ней.
- На Вставлять вкладка, в Графики группа, нажмите Рекомендуемые графики.
- Переключиться на Все диаграммы выберите Combo слева и внесите следующие изменения:
- Для Считать выберите Clustered Column (тип по умолчанию).
- Для серии Cumulative % выберите тип Line и установите флажок Secondary Axis.
Диаграмма, которую Excel вставит на ваш лист, будет похожа на эту:
Улучшить диаграмму Парето
Ваша диаграмма уже очень похожа на диаграмму Парето, но вы можете кое-что улучшить:
1. Установите максимальное процентное значение на 100%.
По умолчанию Excel установил максимальное значение для вторичной вертикальной оси на 120%, а нам нужно 100%.
Чтобы изменить это, щелкните правой кнопкой мыши значения в процентах на оси Y справа и выберите «Формат оси…». Формат оси панель, под Границыустановите 1.0 в Максимум коробка:
2. Удалите лишнее расстояние между стержнями
На классической диаграмме Парето столбцы располагаются ближе друг к другу, чем на комбинированной диаграмме. Чтобы исправить это, щелкните правой кнопкой мыши столбцы и выберите «Формат ряда данных…». Серия данных формата панели установите желаемую ширину зазора, скажем, 5%:
Наконец, измените заголовок диаграммы и, при желании, скройте легенду диаграммы.
То, что у вас сейчас есть, выглядит как идеальная диаграмма Парето в Excel:
Как нарисовать диаграмму Парето в Excel 2010
В Excel 2010 нет ни диаграмм Парето, ни комбинированных диаграмм, но это не означает, что вы не можете рисовать диаграмму Парето в более ранних версиях Excel. Конечно, это потребует немного больше работы, но и больше удовольствия 🙂 Итак, приступим.
- Организуйте свои данные, как описано ранее: сортировать по количеству в порядке убывания и вычислить совокупный общий процент.
- Выберите свой стол, перейдите на Вкладка «Вставка» > Графики группу и выберите тип гистограммы 2-D Clustered Column:
Это вставит столбчатую диаграмму с 2 рядами данных (Считать и Совокупный процент).
- Щелкните правой кнопкой мыши столбцы Cumulative % и выберите Change Chart Series Type. (Это может быть самой сложной частью, потому что столбцы очень маленькие. Попробуйте навести указатель мыши на столбцы, пока не увидите Серия “Накопительный %” подсказку, а затем щелкните правой кнопкой мыши.)
- в Изменить тип диаграммы диалоговое окно, выберите линию
- На данный момент у вас есть гистограмма с плоской линией вдоль горизонтальной оси. Чтобы придать ему кривую, вам нужно поместить второстепенную вертикальную ось с правой стороны. Для этого щелкните правой кнопкой мыши Совокупный % строку, а затем нажмите «Формат ряда данных…».
- в Серия данных формата диалоговом окне выберите Второстепенная ось в Параметры сериии закройте диалоговое окно:
- Внесите последние штрихи: установите максимальное значение вторичной вертикальной оси на 100 %, сделайте полосы шире и, при желании, скройте легенду. Шаги в основном такие же, как в Excel 2013. описано выше.
Вуаля, ваша диаграмма Парето в Excel 2010 готова:
Вот как сделать диаграмму Парето в Excel. Если вы хотите узнать о других типах диаграмм, я рекомендую вам ознакомиться с приведенными ниже ресурсами. Я благодарю вас за чтение и надеюсь снова увидеть вас в нашем блоге на следующей неделе!
