Анализ диаграммы Парето помогает оценить эффективность деятельности бизнеса компании. Давайте разберемся как построить диаграмму Парето в Excel.
Закон Парето (правило Парето) в общем виде звучит как «20% усилий дают 80% результата, остальные 80% усилий дают оставшиеся 20% результата». Поэтому грамотное построение анализа поможет определить сильные стороны бизнеса (ресурсы, которые нужно развить и усилить), так и слабые (ресурсы, которые также нужно существенно улучшить или отказаться).
Построение диаграммы Парето в Excel
Предположим, что у нас есть данные по продажам продуктов в следующей таблице:
Данные в таблице не упорядочены, поэтому в первую очередь отсортируем данные по убыванию прибыли.
Для этого выделим таблицу и выберем в панели вкладок Данные -> Сортировка и фильтр -> Сортировка:
Построение вспомогательной таблицы
Дополнительно добавили в таблицу несколько столбцов:
- Нарастающий процент прибыли, % — каждый продукт суммируется с предыдущим и показывается общая доля в прибыли;
- Коэффициент эффективности — в данном случае 80% (согласно правилу Парето);
- Критерий подсветки — в итоговой диаграмме будут подсвечиваться основные источники прибыли, указываем значение заведомо больше 1.
Расшифровка формул вспомогательной таблицы
Выделяем все данные и вставляем гистограмму. Для этого переходим в панели вкладок на Вставка -> Диаграмма -> Гистограмма -> Гистограмма с группировкой:
Построение гистограммы
Теперь преобразуем график в более удобный вид. Выделяем ряд «Нарастающий процент прибыли, %» и переносим его на второстепенную ось (нажимаем правой кнопкой мыши на ряд, выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси):
Перенос ряда на вспомогательную ось
Также поменяем тип диаграммы для этого ряда на обычный линейный график (нажимаем правой кнопкой мыши на ряд, выбираем Изменить тип диаграммы для ряда):
Изменение типа диаграммы для ряда
Далее аналогичные действия проводим и для ряда «Коэффициент», который переносим на вспомогательную ось и делаем горизонтальной линией:
Добавление горизонтальной линии на диаграмму
Добавим подсветку на диаграмму, которая показывает какие конкретно группы продуктов приносят основную прибыль. Выделяем ряд «Подсветка» и переносим его на второстепенную ось. Выставляем боковой зазор равным 0 — нажимаем правой кнопкой мыши на ряд, выбираем Формат ряда данных -> Параметры ряда -> Боковой зазор:
Добавление подсветки на диаграмму
Пример диаграммы Парето в Excel
Настраиваем диаграмму по своему усмотрению и получаем окончательный вид графика Парето в Excel:
Диаграмма Парето
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!