Как создать диаграмму Парето в Excel (статическая и интерактивная)

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

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

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

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

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

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

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

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

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

Вся хитрость кроется в том, как вы размещаете данные в бэкэнде.

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

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

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

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

1. Настройте свои данные, как показано ниже.
2. Рассчитайте совокупный% в столбце C. Используйте следующую формулу: = СУММ ($ B $ 2: B2) / СУММ ($ B $ 2: $ B $ 1).
3. Выделите весь набор данных (A1: C10), перейдите в Вставить -> Диаграммы -> Двухмерный столбец -> Кластерный столбец. Будет вставлена ​​столбчатая диаграмма с двумя сериями данных (количество жалоб и совокупный процент).
4. Щелкните правой кнопкой мыши любую полосу и выберите «Изменить тип диаграммы ряда».
5. В диалоговом окне «Изменить тип диаграммы» выберите «Комбинированный» на левой панели.
6. Сделайте следующие изменения:
   • Количество жалоб: сгруппированный столбец.
   • Совокупный%: линия (также установите флажок «Дополнительная ось»).[Если вы используете Excel 2010 или 2007, это будет двухэтапный процесс. Сначала измените тип диаграммы на линейную диаграмму. Затем щелкните правой кнопкой мыши линейную диаграмму и выберите «Форматировать ряд данных» и выберите «Вторичная ось в параметрах серии»]
7. Ваша диаграмма Парето в Excel готова. Отрегулируйте значения вертикальной оси и заголовок диаграммы.

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

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

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

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

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

Что-то вроде того, что показано ниже:

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

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

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

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

1. В ячейке B14 у меня есть целевое значение, связанное с полосой прокрутки (значение которой варьируется от 0 до 100).
2. В ячейке B12 я использовал формулу = B14 / 100. Поскольку вы не можете указать процентное значение для полосы прокрутки, мы просто делим значение полосы прокрутки (в B14) на 100, чтобы получить процентное значение.
3. В ячейке B13 введите следующую комбинацию функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИОШИБКА:
   = ЕСЛИОШИБКА (ИНДЕКС ($ C $ 2: $ C $ 10; ЕСЛИОШИБКА (ПОИСКПОЗ ($ B $ 12; $ C $ 2: $ C $ 10,1); 0) +1); 1)
   Эта формула возвращает совокупное значение, которое покрывает целевое значение. Например, если у вас есть целевое значение 70%, оно вернет 77%, что означает, что вы должны попытаться решить первые три проблемы.

4. В ячейке D2 введите следующую формулу (и перетащите или скопируйте все ячейки - D2: D10):
   = ЕСЛИ ($ B $ 13> = C2, B2, NA ())
5. В ячейке E2 введите следующую формулу (и перетащите или скопируйте для всей ячейки - E2: E10):
   = ЕСЛИ ($ B $ 13<>
6. Выберите данные в столбцах A, C, D и E (нажмите элемент управления и выберите с помощью мыши).
7. Перейдите в Вставка -> Диаграммы -> Двухмерный столбец -> Кластерный столбец. Будет вставлена ​​столбчатая диаграмма с 3 сериями данных (совокупный процент, столбцы, которые должны быть выделены для достижения цели, и оставшиеся все остальные столбцы)
8. Щелкните правой кнопкой мыши любую полосу и выберите «Изменить тип диаграммы ряда».
9. В диалоговом окне «Изменить тип диаграммы» выберите «Комбинированный» на левой панели и внесите следующие изменения:
   • Совокупный%: Линия (также установите флажок Вторичная ось).
   • Выделенные столбцы: сгруппированный столбец.
   • Оставшиеся столбцы: кластерный столбец.
10. Щелкните правой кнопкой мыши любую из выделенных полос и измените цвет на красный.

Это оно!

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

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

Вы используете диаграмму Парето в Excel?

Я хотел бы услышать ваши мысли об этой технике и о том, как вы ее использовали. Оставляйте свои следы в комментариях 🙂

• Анализ жалоб в ресторанах с использованием диаграммы Парето.
• Создание диаграммы Ганта в Excel.
• Создание диаграммы вех в Excel.
• Создание гистограммы в Excel.
• Шаблон калькулятора расписания Excel.
• Шаблон отслеживания увольнений сотрудников.
• Расчет средневзвешенного значения в Excel.
• Создание колоколообразной кривой в Excel.
• Расширенные диаграммы Excel
• Как добавить дополнительную ось в диаграммы Excel.