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

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

Диаграмма Парето в Excel

  1. Прежде всего, отсортируйте данные в ячейках по убыванию. Для этого нажмите левой кнопкой мыши на верхнюю ячейку в столбце Count (количество жалоб), затем на вкладке Данные (Data) найдите и нажмите кнопку ЯА (ZA) – сортировка по убыванию.

    Диаграмма Парето в Excel

  2. Далее подсчитаем количество с накоплением (Cumulative Count). Для этого введите представленную ниже формулу в ячейку С5 и протяните её вниз по всему столбцу.

    Диаграмма Парето в Excel

  3. Далее посчитаем накопленный процент (Cumulative %). Для этого введите представленную ниже формулу в ячейку D4 и протяните её вниз по всему столбцу.

    Диаграмма Парето в Excel

Примечание: В ячейке С13 отображается общее число жалоб. При протягивании формулы вниз абсолютная ссылка $С$13 остаётся неизменной, в то время как относительная С4 меняется на С5, С6, С7 и т.д.

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

    Диаграмма Парето в Excel

  2. На вкладке Вставка (Insert) щёлкните по кнопке Гистограмма (Column), затем в выпадающем меню выберите Гистограмма с группировкой (Clustered Column).

    Диаграмма Парето в Excel

  3. Кликните правой кнопкой мыши по красным столбцам (накопленный %) и в контекстном меню выберите Изменить тип диаграммы для ряда (Change Series Chart Type).

    Диаграмма Парето в Excel

  4. Выберите тип График с маркерами (Line with Markers).

    Диаграмма Парето в Excel

  5. Кликните правой кнопкой мыши по красной линии и выберите Формат ряда данных (Format Data Series).

    Диаграмма Парето в Excel

  6. Выберите пункт По вспомогательной оси (Secondary Axis).

    Диаграмма Парето в Excel

  7. Кликните правой кнопкой мыши по вспомогательной оси (ось процентов) и выберите Формат оси (Format Axis), установите Максимум (Maximum) равным 100 и нажмите Закрыть (Close).

    Диаграмма Парето в Excel

В результате мы получаем следующее:

Диаграмма Парето в Excel

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

08.11.2015 11:00
1438

Комментарии

Нет комментариев. Ваш будет первым!