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

Обновлено: 21.11.2024

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

Excel 2016 или более поздней версии

Чтобы создать диаграмму Парето в Excel 2016 или более поздней версии, выполните следующие действия.

<р>1. Выберите диапазон A3:B13.

<р>2. На вкладке "Вставка" в группе "Диаграммы" щелкните значок "Гистограмма".

Примечание. Диаграмма Парето сочетает в себе гистограмму и линейный график.

<р>4. Введите название диаграммы.

<р>5. Нажмите кнопку + в правой части диаграммы и установите флажок рядом с Метки данных.

Вывод: оранжевая линия Парето показывает, что (789 + 621) / 1722 ≈ 80% жалоб приходится на 2 из 10 = 20% типов жалоб (Завышенная цена и Маленькие порции). Другими словами: применяется принцип Парето.

Все версии

Если у вас нет Excel 2016 или более поздней версии, просто создайте диаграмму Парето, объединив гистограмму и линейный график. Этот метод работает со всеми версиями Excel.

<р>1. Сначала выберите число в столбце B.

<р>2. Затем отсортируйте данные в порядке убывания. На вкладке "Данные" в группе "Сортировка и фильтр" нажмите ZA.

<р>3. Подсчитайте совокупный счет. Введите приведенную ниже формулу в ячейку C5 и перетащите формулу вниз.

<р>4. Рассчитайте совокупный %. Введите приведенную ниже формулу в ячейку D4 и перетащите формулу вниз.

Примечание: ячейка C13 содержит общее количество жалоб. Когда мы перетаскиваем эту формулу вниз, абсолютная ссылка ($C$13) остается прежней, а относительная ссылка (C4) меняется на C5, C6, C7 и т. д.

<р>5. Выберите данные в столбцах A, B и D. Для этого, удерживая нажатой клавишу CTRL, выберите каждый диапазон.

<р>6. На вкладке "Вставка" в группе "Диаграммы" щелкните символ "Столбец".

<р>7. Нажмите Кластеризованный столбец.

<р>8. Щелкните правой кнопкой мыши оранжевые столбцы (кумулятивный процент) и выберите "Изменить тип диаграммы серии".

Появится диалоговое окно "Изменить тип диаграммы".

<р>9. Для ряда "Совокупный %" выберите тип диаграммы "Линия с маркерами".

<р>10. Нанесите совокупный процентный ряд на второстепенную ось.

<р>12. Щелкните правой кнопкой мыши проценты на диаграмме, выберите "Формат оси" и установите для параметра "Максимум" значение 100.

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

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

Цель диаграммы – выявить важные факторы в наборе данных, отделив их от второстепенных.

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

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

Начало работы

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

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

Итак, давайте приступим.

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

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

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

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

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

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

Как по волшебству, тут же появится диаграмма Парето:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Не забудьте отформатировать вывод формулы в процентах (Главная > Группа чисел > Стиль процентов).

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

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

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

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

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

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

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

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

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

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

    Нажмите правой кнопкой мыши на вторичной вертикальной оси (цифры справа) и выберите «Формат оси».

    После того как появится панель задач "Формат оси", выполните следующие действия:

    Расширьте столбцы, изменив ширину промежутка в соответствующем ряду данных (ряд "Возвращенные товары").

    Для этого щелкните правой кнопкой мыши любой из синих столбцов, представляющих серию "Возвращенные товары", и выберите "Форматировать серию данных".

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

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

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

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

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

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

    Диаграмма Парето основана на принципе Парето (также известном как правило 80/20), широко известном в управлении проектами.

    Согласно этому принципу, примерно 80 % проблем можно отнести к примерно 20 % проблем (или ~80 % ваших результатов могут быть прямым результатом примерно 20 % ваших усилий и т. д.). .).

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

    Это широко используемая концепция приоритизации работы в управлении проектами.

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

    В этом уроке я покажу вам, как сделать:

    • Простая (статическая) диаграмма Парето в Excel.
    • Динамическая (интерактивная) диаграмма Парето в Excel.

    Создать диаграмму Парето в Excel очень просто.

    Все хитрости скрыты в том, как вы упорядочиваете данные в серверной части.

    Давайте возьмем пример отеля, для которого данные о жалобах могут выглядеть так, как показано ниже:

    ПРИМЕЧАНИЕ. Чтобы создать диаграмму Парето в Excel, необходимо расположить данные в порядке убывания.

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

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

    Например, таргетинг на первые три проблемы автоматически устранит около 80 % жалоб.

    Создание динамической (интерактивной) диаграммы Парето в Excel

    Теперь, когда у нас есть статичная/простая диаграмма Парето в Excel, давайте сделаем еще один шаг и сделаем ее немного интерактивной.

    Как показано ниже:

    В этом случае пользователь может указать процент жалоб, которые необходимо решить (используя полосу прокрутки Excel), и на диаграмме будут автоматически выделены проблемы, которые необходимо рассмотреть.

    Идея состоит в том, чтобы иметь 2 разных бара.

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

    Вот шаги, чтобы сделать эту интерактивную диаграмму Парето в Excel:

    1. В ячейке B14 у меня есть целевое значение, связанное с полосой прокрутки (значение которой варьируется от 0 до 100).=B14/100 .Поскольку вы не можете указать процентное значение для полосы прокрутки, мы просто делим значение полосы прокрутки (в ячейке B14) на 100, чтобы получить процентное значение. Функции ИНДЕКС, ПОИСКПОЗ и ЕСЛИОШИБКА:
      =ЕСЛИОШИБКА(ИНДЕКС($C $2:$C$10,ЕСЛИОШИБКА(ПОИСКПОЗ($B$12,$C$2:$C$10,1),0)+1),1)
      Эта формула возвращает совокупное значение, которое покрывает целевое значение. Например, если у вас есть целевое значение 70 %, оно вернет 77 %, что указывает на то, что вы должны попытаться решить первые три проблемы.
    1. В ячейку D2 введите следующую формулу (и перетащите или скопируйте для всех ячеек – D2:D10):
      =IF($B$13>=C2,B2,NA())
    2. В ячейку E2 введите следующую формулу (и перетащите или скопируйте для всех ячеек – E2:E10):
      =IF($B$13
    3. Выберите данные в столбцах A, C, D и E (нажмите Control и выберите с помощью мыши).
    4. Перейдите к меню «Вставка» –> «Диаграммы» –> «Двухмерный столбец» –> «Сгруппированный столбец». Будет вставлена ​​столбчатая диаграмма с 3 рядами данных (совокупный процент, столбцы, которые необходимо выделить для достижения цели, и оставшиеся все остальные столбцы).
    5. Нажмите правой кнопкой мыши на любую из полос и выберите "Изменить тип диаграммы серии".
    6. В диалоговом окне "Изменить тип диаграммы" выберите "Комбинированный" на левой панели и внесите следующие изменения:
      • Совокупный %: линия (также установите флажок Secondary Axis).
      • Выделенные столбцы: кластерный столбец.
      • Остальные столбцы: кластеризованный столбец.
    7. Нажмите правой кнопкой мыши любую из выделенных полос и измените цвет на красный.

    Вы создали интерактивную диаграмму Парето в Excel.

    Теперь при изменении цели с помощью полосы прокрутки диаграмма Парето будет соответствующим образом обновляться.

    Сэнди Риттенхаус

    Сэнди Риттенхаус
    Писатель

    С ее B.S. В области информационных технологий Сэнди много лет проработала в ИТ-индустрии в качестве руководителя проекта, руководителя отдела и руководителя отдела управления проектами. Она узнала, как технологии могут обогатить как профессиональную, так и личную жизнь, используя правильные инструменты. И со временем она поделилась этими предложениями и практическими рекомендациями на многих веб-сайтах. Имея за плечами тысячи статей, Сэнди стремится помочь другим использовать технологии в своих интересах. Подробнее.

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

    Преимущества диаграммы Парето

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

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

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

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

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

    Перейдите на вкладку «Вставка» и нажмите стрелку раскрывающегося списка «Вставить статистическую диаграмму». Выберите «Парето» в разделе меню «Гистограмма». Помните, что диаграмма Парето — это отсортированная гистограмма.

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

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

    Настроить диаграмму Парето

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

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

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

    Вы также можете выбрать диаграмму и перейти на открывшуюся вкладку "Дизайн диаграммы". Лента предоставляет инструменты для изменения макета или стиля, добавления или удаления элементов диаграммы или настройки выбора данных.

    Еще один способ настроить диаграмму Парето – открыть боковую панель "Формат области диаграммы" двойным щелчком мыши. У вас есть вкладки «Заливка и линия», «Эффекты» и «Размер и свойства». Таким образом, вы можете добавить границу, тень или определенную высоту и ширину.

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

    Чтобы узнать о других типах диаграмм, узнайте, как создать диаграмму с географической картой или гистограмму в Excel.

    • › Как построить график в Microsoft Excel
    • › Как применить фильтр к диаграмме в Microsoft Excel
    • › Как создать шаблон диаграммы в Microsoft Excel
    • › Как создать и настроить древовидную диаграмму в Microsoft Excel
    • › Как выбрать диаграмму, соответствующую вашим данным в Microsoft Excel
    • › Как восстановить метки панели задач в Windows 11
    • ›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
    • › Почему прозрачные чехлы для телефонов желтеют?

    Читайте также: