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

Анализ диаграммы Парето помогает оценить эффективность деятельности бизнеса компании. Давайте разберемся как построить диаграмму Парето в Excel.

Закон Парето (правило Парето) в общем виде звучит как «20% усилий дают 80% результата, остальные 80% усилий дают оставшиеся 20% результата». Поэтому грамотное построение анализа поможет определить сильные стороны бизнеса (ресурсы, которые нужно развить и усилить), так и слабые (ресурсы, которые также нужно существенно улучшить или отказаться).

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

Предположим, что у нас есть данные по продажам продуктов в следующей таблице:

Таблица по продажам
Данные в таблице не упорядочены, поэтому в первую очередь отсортируем данные по убыванию прибыли.
Для этого выделим таблицу и выберем в панели вкладок Данные -> Сортировка и фильтр -> Сортировка:

Построение вспомогательной таблицы

Построение вспомогательной таблицы


Дополнительно добавили в таблицу несколько столбцов:

  • Нарастающий процент прибыли, % — каждый продукт суммируется с предыдущим и показывается общая доля в прибыли;
  • Коэффициент эффективности — в данном случае 80% (согласно правилу Парето);
  • Критерий подсветки — в итоговой диаграмме будут подсвечиваться основные источники прибыли, указываем значение заведомо больше 1.

Расшифровка формул вспомогательной таблицы

Расшифровка формул вспомогательной таблицы


Выделяем все данные и вставляем гистограмму. Для этого переходим в панели вкладок на Вставка -> Диаграмма -> Гистограмма -> Гистограмма с группировкой:

Построение гистограммы

Построение гистограммы


Теперь преобразуем график в более удобный вид. Выделяем ряд «Нарастающий процент прибыли, %» и переносим его на второстепенную ось (нажимаем правой кнопкой мыши на ряд, выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси):

Перенос ряда на вспомогательную ось

Перенос ряда на вспомогательную ось


Также поменяем тип диаграммы для этого ряда на обычный линейный график (нажимаем правой кнопкой мыши на ряд, выбираем Изменить тип диаграммы для ряда):

Изменение типа диаграммы для ряда

Изменение типа диаграммы для ряда


Далее аналогичные действия проводим и для ряда «Коэффициент», который переносим на вспомогательную ось и делаем горизонтальной линией:

Добавление горизонтальной линии на диаграмму

Добавление горизонтальной линии на диаграмму


Добавим подсветку на диаграмму, которая показывает какие конкретно группы продуктов приносят основную прибыль. Выделяем ряд «Подсветка» и переносим его на второстепенную ось. Выставляем боковой зазор равным 0 — нажимаем правой кнопкой мыши на ряд, выбираем Формат ряда данных -> Параметры ряда -> Боковой зазор:

Добавление подсветки на диаграмму

Добавление подсветки на диаграмму

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

Настраиваем диаграмму по своему усмотрению и получаем окончательный вид графика Парето в Excel:

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

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

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Добавить комментарий

Ваш адрес email не будет опубликован.