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

Содержание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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