Диаграмма Парето в Excel
Анализ диаграммы Парето помогает оценить эффективность деятельности бизнеса компании. Давайте разберемся как построить диаграмму Парето в Excel.
Закон Парето (правило Парето) в общем виде звучит как «20% усилий дают 80% результата, остальные 80% усилий дают оставшиеся 20% результата». Поэтому грамотное построение анализа поможет определить сильные стороны бизнеса (ресурсы, которые нужно развить и усилить), так и слабые (ресурсы, которые также нужно существенно улучшить или отказаться).
Построение диаграммы Парето в Excel
Предположим, что у нас есть данные по продажам продуктов в следующей таблице:
Данные в таблице не упорядочены, поэтому в первую очередь отсортируем данные по убыванию прибыли.
Для этого выделим таблицу и выберем в панели вкладок Данные -> Сортировка и фильтр -> Сортировка:
Построение вспомогательной таблицы
- Нарастающий процент прибыли, % — каждый продукт суммируется с предыдущим и показывается общая доля в прибыли;
- Коэффициент эффективности — в данном случае 80% (согласно правилу Парето);
- Критерий подсветки — в итоговой диаграмме будут подсвечиваться основные источники прибыли, указываем значение заведомо больше 1.
Расшифровка формул вспомогательной таблицы
Перенос ряда на вспомогательную ось
Изменение типа диаграммы для ряда
Добавление горизонтальной линии на диаграмму
Добавление подсветки на диаграмму
Пример диаграммы Парето в Excel
Настраиваем диаграмму по своему усмотрению и получаем окончательный вид графика Парето в Excel:
Как построить диаграмму Парето в Excel
В Excel 2016 разработчики добавили несколько новых диаграмм. Одна из них – гистограмма Парето, которая отражает графическое изображение известного принципа Парето или закона 20/80. На практике этот метод довольно хорошо прижился, т.к. его простота, и в то же время эффективность, приносят неплохие плоды. Диаграмма (гистограмма) Парето имеет следующий вид.
Интерпретация довольно проста. Столбики – это отсортированные по убыванию значения отдельных элементов. График – соответствующие накопленные доли. Последнее значение равно 100%. Чтобы построить подобную диаграмму в Excel 2013 и более ранних версиях, нужно выполнить следующие действия:
1) отсортировать данные по убыванию, чтобы значения, имеющие наибольший вклад, были в начале списка;
2) рассчитать столбец с накопленными долями;
3) использовать комбинированную диаграмму, чтобы столбиками показать отдельные элементы, а графиком накопленные доли.
В целом не сложно (пример построения диаграммы Парето таким способом показан в ролике ниже). Однако при обновлении данных как минимум сортировку придется провести заново. Возможно, придется настраивать новые диапазоны и формулы, если количество данных изменилось.
В Excel 2016 больше не нужно сортировать данные, рассчитывать отдельный столбец с накопленными долями и ковыряться в настройках. Достаточно иметь исходные данные, чтобы в пару кликов построить гистограмму Парето.
Построение диаграммы Парето в Excel 2016
Допустим, анализируется доходность групп товаров. Данные расположены по алфавиту.
Изобразим на основе этих данных гистограмму Парето (в Excel 2016). Вначале активируем любую ячейку в исходных данных. Затем через Рекомендуемые диаграммы либо напрямую выберем гистограмму Парето.
По умолчанию получим следующий вид диаграммы.
Полдела сделано. Осталось подобрать дизайн диаграммы и отдельных ее элементов.
Прелесть этой диаграммы в том, что она не зависит от порядка расположения исходных данных. Наиболее важные элементы всегда будут на первом месте. Так что, если данные обновить, то все будет работать, как надо.
Последний совет. Данные для диаграмм лучше всего хранить в режиме Таблица. Это позволит диаграмме автоматически подстраиваться под размер диапазона. Подробности в ролике ниже.