Как построить диаграмму Парето в Excel
Содержание
Программа MS Excel может использоваться для реализации нестандартных методов анализа данных. К их числу можно отнести так называемый закон Парето или правило «20/80», в соответствии с которым 20% ресурсов обеспечивают 80% результата. Как аналитический метод, этот принцип направлен на определение факторов, которые в наибольшей мере влияют на итоговые показатели. Для визуального отображения такого анализа применяется диаграмма Парето, способам построения которой посвящен данный материал.
Простой пример построения диаграммы Парето
Сразу же отметим, что построить такую диаграмму можно несколькими способами. По сути, это стандартная столбчатая диаграмма, только она имеет вспомогательную ось, на которой отображается доля от какого-либо общего показателя.
В данном примере анализу подвергаются группы товаров и их влияние на общую прибыль предприятия. Чтобы построить график, нужно знать доход от продажи каждой группы, а также долю каждой продукции в итоговой сумме прибыли. Исходную таблицу можно представить следующим образом. Отметим, что это довольно простой пример, который нужен для того, чтобы определить, как построить диаграмму Парето в Эксель.
Следующим шагом определим значение нарастающего процента прибыли для каждой из представленных групп. Нарастающий процент — это доля товара от общего дохода с нарастанием, то есть он учитывает и проценты от всех предыдущих позиций. Для этого необходимо разделить общий размер прибыли с самого начала списка до текущего значения группы на показатель, который отражает общую прибыль за все товары.
Также для построения диаграммы нам потребуется еще один столбец — порог эффективности. По закону Парето он составляет 80%. В нашем случае задача состоит в том, чтобы определить, какие группы товаров составляют 80% от общей прибыли. Создаем такой столбец и указываем в каждом значение «80%». Альтернативный вариант — указать дробный коэффициент 0.8. Два этих варианта при этом будут работать одинаково.
Теперь таблица содержит все необходимое и можно выполнить построение диаграммы Парето. Используем следующую инструкцию:
- Вначале выделим нужные данные в таблице. Затем переходим во вкладку «Главная», а потом в Панели инструментов выбираем команду «Фильтр». Далее применяем инструмент «Сортировка по убыванию». Такой вариант требуется для того, чтобы группы товаров в диаграмме отображались от большего к меньшему в зависимости от их доли в общей прибыли.
Выполняем сортировку по убыванию - Теперь выделяем все столбцы. Далее открывается вкладка «Вставка» и в Панели инструментов выбирается «Гистограмма с группировкой».
Вставляем гистограмму - В полученной диаграмме отображаются только данные из колонки «Суммарная прибыль». Это можно исправить, если правильно настроить отображение нужной группы данных. Нажимаем по названию ряда «Нарастающий процент прибыли». Затем в контекстном меню жмем «Формат ряда данных».
Нажимаем формат ряда данных - В поле «Построить ряд» ставим отметку напротив значения «По вспомогательной оси». В результате справа от диаграммы отобразится процентная шкала и новый набор столбцов.
Переносим ряд на вспомогательную ось - На классической диаграмме Парето процентный фактор отображается в виде графика, а не столбиков. Поэтому изменим способ визуализации нужным образом. Для этого снова выделяем ряд с нарастающим процентом, потом нажимаем и выбираем в команду «Изменить тип диаграммы для ряда».
Нажимаем изменить тип диаграммы ряда - В окошке параметров напротив названия ряда изменяем вид диаграммы на «График». Затем этого жмем . Здесь можно сразу же создать график и для ряда «Коэффициент эффективности». Ставим галочку в столбце «Вспомогательная ось» и изменяем вид диаграммы на график с маркерами.
Меняем параметры диаграммы ряда
В результате это получим классическую диаграмму Парето. На ней теперь можно определить, что 80% от суммарной прибыли приносят три первые группы товаров. Для этого достаточно найти на графике точку пересечения линии «Коэффициент эффективности» с линией «Нарастающий процент».
Отметим, что MS Excel 2016 и более новых версиях программы, диаграммы Парето доступны в стандартном наборе инструментов. Для их использования достаточно выбрать соответствующий тип графика во вкладке «Вставка». При этом для построения необязательно самостоятельно считать накопительный процент, так как программа применит необходимые формулы и коэффициент 0.8 автоматически.
Построение через сводную таблицу
Рассмотрим еще один пример создания диаграммы Парето. В данном случае потребуется сводная таблица, которую мы создадим из ранее использованного примера. Для работы потребуется только 2 столбца — «Наименование» и «Суммарная прибыль от продаж».
Для этого используем такой алгоритм действий:
- Выделяется любая клетка в изначальной таблице. Затем во вкладке «Вставка» нажимаем «Сводная таблица». В окошке параметров задаем диапазон для исходных данных. Для нашего случая это заполненные ячейки в столбцах А и В. Отчет сводной таблицы помещаем на новый лист, поставив отметку напротив соответствующий команды. Задав все параметры, жмем .
Задаем параметры сводной таблицы - Столбец «Наименование» перемещаем в поле «Строки», а «Суммарная прибыль» в поле «Значения». В результате этого получаем отчет, в котором описана прибыль по каждой группе товаров.
Настраиваем поля сводной таблицы - Далее необходимо выполнить сортировку значений таблицы по убыванию. Выделяем любую ячейку из колонки «Суммарная прибыль» и жмем . В отобразившемся списке нажимаем пункт «Сортировка», затем нажимаем «Сортировка по убыванию».
Сортируем по убыванию - Далее необходимо найти нарастающие процент. Предыдущий пример содержал такое поле изначально. В сводной таблице его можно вычислить иным способом. Для этого открываем конструктор полей и колонку «Суммарная прибыль от продаж» вставляем в поле «Значения» еще раз. Так мы получим дубликат колонки.
Создаем дубликат - Теперь выбираем любую ячейку в столбце дубликате. Нажимаем правую кнопку мышки и в контекстном меню выбираем «Дополнительные вычисления». Затем в выпадающем окне жмем команду «% от суммы с нарастающим итогом».
Вычисляем процент от суммы с нарастающим итогом - В результате чего мы получаем значение нарастающего процента. Остается только сделать еще одно действие — добавить коэффициент эффективности по аналогии с предыдущим примером. Для этого переходим во вкладку «Анализ» и выбираем «Вычисляемое поле». Прописываем имя для поля и в строке «Формула» указываем значение «80%». Нажимаем и в результате этого появится еще один столбец со значением 80% в каждой ячейке.
Добавляем вычисляемое поле с коэффициентом 80% - Теперь можно приступить непосредственно к оформлению диаграммы. Для этого переходим во вкладку «Анализ». Выбираем инструмент «Сводная диаграмма».
Создаем диаграмму - Теперь необходимо настроить отображение данных в диаграмме. В принципе это делается таким же образом, как в предыдущем примере. Выделяем название ряда и вызываем контекстное меню, где выбираем команду «Изменить тип диаграммы ряда».
Выбираем ряд и нажимаем изменить тип диаграммы ряда - Ряды «Суммарная прибыль» и «Коэффициент эффективности» переносим на вспомогательную ось. Далее заменяем диаграмму графиком. Для этого значение параметра «Тип диаграммы» меняем на «График» и «График с маркерами» соответственно.
Настраиваем параметры диаграммы
В результате этого мы получим диаграмму Парето из сводной таблицы. Рассмотрев данный пример, на первый взгляд может показаться, что способ построения очень сложный. На самом деле сделать диаграмму по исходным данным таким образом довольно просто.
Подсветка ключевых значений
Созданная диаграмма Парето в таблице Excel хорошо отображает, какие группы товаров обеспечивают большую часть прибыли. Однако, чтобы улучшить визуализацию, можно изменить подсвечивание различных категорий на графике. Для этого:
- В исходной таблице добавляется еще один столбик — «Подсветка». В ячейке прописывается формула, значение которой будет равняться 1, если показатель «Нарастающий процент прибыли» меньше 80%, и 0 в обратном случае.
Добавляем столбец для подсветки - Создаем новую гистограмму или добавляем новый ряд «Подсветка» на созданную ранее. Используем для этого функцию копировать-вставить. Альтернативный вариант — и .Добавляем ряд подсветка
- Выделяем ряд «Подсветка» на диаграмме нажатием кнопкой мыши. В окне параметров справа открываем вкладку «Заливка». Меняем цвет и прозрачность так, чтобы не перекрывались основные столбцы графика.
Меняем цвет и прозрачность заливки - Далее в окне параметров переходим во вкладку «Параметры ряда». Значение «Боковой зазор» ставим на ноль, чтобы заливка распространялась на всю область, а не только возле основных столбцов. В результате получаем подсветку, которая выделяет все группы товаров с долей прибыли в размере 80%.
Готовая диаграмма с подсветкой
Данный пример оформления диаграммы с подсветкой можно использовать и для сводных таблиц. Для этого необходимо будет добавить вычисляемый объект, в котором определяется, достигает ли показатель суммарной прибыли отметки в 80%. В остальном способ оформления такой диаграммы абсолютно идентичный.
Подводим итоги
Вопрос о том, как построить диаграмму Парето в Excel, довольно актуальный для пользователей данной программы. В новых версиях предусмотрен стандартный инструмент для оформления такого графика. Если же вы пользуетесь ранними версиями Эксель, можно создать диаграмму из обычной гистограммы с группировкой, однако для этого необходимо предварительно произвести вспомогательные вычисления и добавить их результаты в исходную таблицу.
Загрузка...
