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

В Excel 2016 разработчики добавили несколько новых диаграмм. Одна из них – гистограмма Парето, которая отражает графическое изображение известного принципа Парето или закона 20/80. На практике этот метод довольно хорошо прижился, т.к. его простота, и в то же время эффективность, приносят неплохие плоды. Диаграмма (гистограмма) Парето имеет следующий вид.

Интерпретация довольно проста. Столбики – это отсортированные по убыванию значения отдельных элементов. График – соответствующие накопленные доли. Последнее значение равно 100%. Чтобы построить подобную диаграмму в Excel 2013 и более ранних версиях, нужно выполнить следующие действия:

1) отсортировать данные по убыванию, чтобы значения, имеющие наибольший вклад, были в начале списка;

2) рассчитать столбец с накопленными долями;

3) использовать комбинированную диаграмму, чтобы столбиками показать отдельные элементы, а графиком накопленные доли.

В целом не сложно (пример построения диаграммы Парето таким способом показан в ролике ниже). Однако при обновлении данных как минимум сортировку придется провести заново. Возможно, придется настраивать новые диапазоны и формулы, если количество данных изменилось.

В Excel 2016 больше не нужно сортировать данные, рассчитывать отдельный столбец с накопленными долями и ковыряться в настройках. Достаточно иметь исходные данные, чтобы в пару кликов построить гистограмму Парето.

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

Допустим, анализируется доходность групп товаров. Данные расположены по алфавиту.

Изобразим на основе этих данных гистограмму Парето (в Excel 2016). Вначале активируем любую ячейку в исходных данных. Затем через Рекомендуемые диаграммы либо напрямую выберем гистограмму Парето.

По умолчанию получим следующий вид диаграммы.

Полдела сделано. Осталось подобрать дизайн диаграммы и отдельных ее элементов.

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

Последний совет. Данные для диаграмм лучше всего хранить в режиме Таблица. Это позволит диаграмме автоматически подстраиваться под размер диапазона. Подробности в ролике ниже.