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

В этом руководстве будет показано, как создать диаграмму Парето во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

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

Цель диаграммы - выделить критические факторы в наборе данных, отличить их от незначительных.

График Парето стал встроенным типом диаграммы в Excel 2016, но для пользователей Excel 2013 или более старых версий единственный выход - вручную построить диаграмму с нуля.

В этом руководстве вы узнаете, как создать настраиваемую диаграмму Парето как вручную, так и с помощью встроенного инструмента построения диаграмм в Excel.

Начиная

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

Имея это в виду, рассмотрим следующие данные:

Итак, приступим.

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

Во-первых, позвольте мне показать вам, как построить диаграмму Парето с помощью соответствующего встроенного типа диаграммы Excel, представленного в Excel 2016.

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

Итак, если вам нужен дополнительный уровень настройки, имеет смысл построить диаграмму с нуля. Но сначала вот алгоритм для тех, кто хочет выбрать более легкий путь.

Шаг № 1: Постройте диаграмму Парето.

Опять же, если вы используете Excel 2016 или более позднюю версию, Excel позволяет создать простую диаграмму Парето, едва отрывая палец:

1. Выделите свои фактические данные (A1: B11).
2. Перейти к Вставлять таб.
3. Нажмите «Вставить статистическую диаграмму.”
4. Выбирать "Парето.”

Волшебным образом сразу же появится диаграмма Парето:

Технически, мы можем назвать это днем, но, чтобы помочь диаграмме рассказать историю, вам может потребоваться дополнительная работа.

Шаг № 2: Добавьте метки данных.

Начните с добавления меток данных на диаграмму. Щелкните правой кнопкой мыши любой столбец и выберите «Добавьте метки данных.”

Настройте цвет, шрифт и размер этикеток, чтобы они выделялись (Дом > Шрифт).

Шаг № 3: Добавьте заголовки осей.

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

1. Выберите область диаграммы.
2. Переключитесь на Дизайн таб.
3. Ударь "Добавить элемент диаграммы" кнопка.
4. Выбирать "Названия осей»Из раскрывающегося меню.
5. Выберите оба "Первичный горизонтальный" а также "Первичная вертикаль.”

Шаг №4: Добавьте последние штрихи.

По мере того, как мы продолжаем настраивать диаграмму, теперь вы можете удалить линии сетки, которые не служат никакой практической цели. Щелкните правой кнопкой мыши линии сетки и нажмите «Удалить», Чтобы стереть их.

Наконец, измените названия оси и диаграммы, и обновленная диаграмма Парето готова!

Как создать диаграмму Парето в Excel 2007, 2010 и 2013

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

Несмотря на то, что все снимки экрана были сделаны в Excel 2022, метод, который вы собираетесь изучить, остается совместимым с версиями до Excel 2007. Просто следуйте инструкциям и внесите соответствующие изменения.

Шаг №1: отсортируйте данные в порядке убывания.

Для начала отсортируйте данные по количеству возвращенных элементов (столбец B) в порядке убывания.

1. Выделите свои фактические данные (A1: B11).
2. Перейти к Данные таб.
3. Щелкните значок «Сортировать" кнопка.
4. в Сортировать диалоговом окне выполните следующие действия:
   1. Для "Столбец," Выбрать "Возвращенные товары.”
   2. Для "Сортировать по," выбирать "Значения ячеек.”
   3. Для "порядок," выбирать "От наибольшего к наименьшему.”

Шаг № 2: Рассчитайте совокупные проценты.

Наш следующий шаг - создать вспомогательный столбец с именем «Совокупный% » который суммирует относительные доли категорий продуктов в наборе данных, которые будут использоваться в качестве значений для построения графика линии Парето.

Чтобы найти значения, введите следующую формулу в C2 и скопируйте его, перетащив маркер в правом нижнем углу ячейки вниз до ячейки C11:

1	= СУММ ($ B $ 2: B2) / СУММ ($ B $ 2: $ B $ 11)

Не забудьте отформатировать вывод формулы в виде процентов (Дом > Группа номеров > Процентный стиль).

Шаг № 3: Создайте кластерную столбчатую диаграмму.

После того, как данные диаграммы были упорядочены должным образом, постройте простую кластерную столбчатую диаграмму.

1. Выделите все данные диаграммы (A1: C11).
2. Перейти к Вставлять таб.
3. Нажмите «Вставить столбец или гистограмму.”
4. Выбирать "Кластерные столбцы.”

Оказавшись там, будущая диаграмма Парето должна выглядеть так:

Шаг №4: Создайте комбинированную диаграмму.

Теперь пора перевернуть ряд данных, иллюстрирующих совокупные проценты (Серия «Совокупный%») в линейный график.

Щелкните правой кнопкой мыши любую из крошечных оранжевых полосок, представляющих Серия «Совокупный%» и выберите "Изменить тип диаграммы серии»В появившемся меню.

в Изменить тип диаграммы диалоговом окне преобразуйте кластеризованную гистограмму в комбинированную диаграмму:

1. Переключитесь на Комбо таб.
2. Для Серия «Совокупный%», изменение "Тип диаграммы" к "Линия с маркерами"И отметьте"Вторичная ось" коробка.

Excel 2010 или более ранние версии: В Изменить тип диаграммы вкладку, перейдите на вкладку Линия вкладку и выберите «Линия с маркерами. » Затем щелкните правой кнопкой мыши появившуюся строку, выберите Форматировать ряд данных, а в Варианты серий вкладка, выберите «Вторичная ось.”

После этого ваша комбинированная диаграмма должна выглядеть так:

Шаг № 5: Отрегулируйте масштаб вторичной вертикальной оси.

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

Щелкните правой кнопкой мыши дополнительную вертикальную ось (числа справа) и выберите «Ось формата.”

Однажды Ось формата появится панель задач, выполните следующие действия:

1. Перейти к Параметры оси
2. Изменить Максимальные границы к "1.”

Шаг № 6: Измените ширину зазора между столбцами.

Сделайте столбцы шире, настроив ширину зазора в соответствующем ряду данных (Серия «Товары возвращены»).

Для этого щелкните правой кнопкой мыши любой из синих столбцов, представляющих Серия «Товары возвращены» и нажмите «Форматировать ряд данных.”

Затем выполните несколько простых шагов:

1. Перейдите к Варианты серий таб.
2. Изменять "Ширина зазора" к "3%.”

Шаг № 7: Добавьте метки данных

Пришло время добавить метки данных для обоих рядов данных (Щелкните правой кнопкой мыши > Добавить метки данных).

Как вы могли заметить, надписи на линии Парето выглядят немного неаккуратно, поэтому давайте разместим их прямо над линией. Чтобы переместить метки, щелкните правой кнопкой мыши метки данных для Серия «Совокупный%» и выберите «Отформатируйте метки данных.”

в Форматирование меток данных области задач измените положение меток, выполнив следующие действия:

1. Перейти к Параметры метки таб.
2. Под "Положение ярлыка," выбирать "Выше.”

Вы также можете изменить цвет и размер этикеток, чтобы отличать их друг от друга.

Шаг № 8: Очистите график.

Наконец, когда мы закончим шлифовать график, не забудьте избавиться от легенды диаграммы и линий сетки. Не забудьте добавить названия осей, выполнив шаги, показанные ранее (Дизайн > Добавить элемент диаграммы > Названия осей > Первичный горизонтальный + Первичная вертикаль).

Ваша улучшенная диаграмма Парето готова!