Содержание:
Возможно, вы слышали о законе Парето или принципе 20/80. В конце XIX века итальянский социолог и экономист Вильфредо Парето обнаружил, что распределение богатства в обществе неравномерно и подчинено определенной зависимости: с ростом богатства число богатых людей уменьшается в геометрической прогрессии с постоянным коэффициентом ( среди итальянских домохозяйств 19% дохода приходилось на 80% семей). Позже эту идею развил в своей книге Ричард Кох, предложивший формулировку универсального «Принципа 20/20» (80% усилий дают 20% результата). На практике этот закон обычно не выражается в таких красивых цифрах (почитайте «Длинный хвост» Криса Андерсона), но наглядно показывает неравномерность распределения ресурсов, прибылей, затрат и т. д.
В бизнес-анализе для отражения этой неравномерности часто строят диаграмму Парето. Его можно использовать, например, для визуального отображения того, какие продукты или клиенты приносят наибольшую прибыль. Обычно это выглядит так:
Его основные особенности:
- Каждый синий столбец гистограммы представляет прибыль от продукта в абсолютных единицах и располагается вдоль левой оси.
- Оранжевый график представляет совокупный процент прибыли (т.е. долю прибыли в совокупном выражении).
- На условной границе 80% для наглядности обычно проводят пороговую горизонтальную линию. Все товары слева от точки пересечения этой линии с графиком накопленной прибыли приносят нам 80% денег, все товары справа – оставшиеся 20%.
Давайте посмотрим, как построить диаграмму Парето в Microsoft Excel самостоятельно.
Вариант 1. Простая диаграмма Парето на основе готовых данных
Если исходные данные пришли к вам в виде аналогичной таблицы (то есть уже в готовом виде):
… тогда делаем следующее.
Отсортируйте таблицу по убыванию прибыли (вкладка Данные — сортировка) и добавим столбец с формулой расчета накопленного процента прибыли:
Эта формула делит общую накопленную прибыль от начала списка до текущего пункта на общую прибыль по всей таблице. Также добавляем столбец с константой 80%, чтобы создать горизонтальную пороговую пунктирную линию на будущем графике:
Выбираем все данные и строим на вкладке обычную гистограмму Вставка – Гистограмма (Вставка – Столбчатая диаграмма). Это должно выглядеть примерно так:
Процентный ряд на результирующем графике необходимо отправить по вторичной (правой) оси. Для этого вам необходимо выделять строки мышкой, но это может быть затруднительно, так как их плохо видно на фоне больших столбцов прибыли. Поэтому лучше использовать выпадающий список на вкладке для выделения Планировка or Формат:
Затем щелкните правой кнопкой мыши по выделенной строке и выберите команду Форматировать ряд данных и в появившемся окне выберите опцию На вторичной оси (Secondary Axis). В результате наша диаграмма будет выглядеть так:
Для серий «Доля накопленной прибыли» и «Порог» необходимо изменить тип диаграммы со столбцов на линии. Для этого нажмите на каждую из этих строк и выберите команду Изменить тип диаграммы серии.
Остается только выделить горизонтальную строку «Порог» и отформатировать ее так, чтобы она выглядела как линия обрезки, а не как данные (т. е. удалить маркеры, сделать линию пунктирной красной и т. д.). Все это можно сделать, щелкнув правой кнопкой мыши по строке и выбрав команду Форматировать ряд данных. Теперь диаграмма примет окончательный вид:
По нему можно сделать вывод, что 80% прибыли приносят первые 5 товаров, а на все остальные товары справа от картофеля приходится только 20% прибыли.
В Excel 2013 это можно сделать еще проще — используйте новый встроенный тип комбинированной диаграммы сразу при построении:
Вариант 2: сводная таблица и сводная диаграмма Парето
Что делать, если готовых данных для построения нет, а есть только исходная необработанная информация? Предположим, что вначале у нас есть таблица с данными о продажах, такая:
Чтобы построить по ней диаграмму Парето и узнать, какие товары продаются лучше всего, сначала придется проанализировать исходные данные. Проще всего это сделать с помощью сводной таблицы. Выберите любую ячейку исходной таблицы и используйте команду Вставка – Сводная таблица (Вставка – Сводная таблица). В появившемся промежуточном окне ничего не меняйте и нажмите кнопку OK, затем в появившейся справа панели перетащите поля исходных данных сверху вниз по областям макета будущей сводной таблицы:
В результате должна получиться сводная таблица с суммарной выручкой по каждому товару:
Отсортируйте его в порядке убывания дохода, установив активную ячейку в столбец. Сумма в поле Доход и используя кнопку сортировки От Я до А (От Я до А) таб Данные.
Теперь нам нужно добавить вычисляемый столбец с накопленными процентными доходами. Для этого перетащите поле еще раз Выручка в район Наши ценности на правой панели, чтобы получить дубликат столбца в сводной таблице. Затем щелкните правой кнопкой мыши клонированный столбец и выберите команду Дополнительные расчеты – % от промежуточной суммы в поле (Показать данные как – % от промежуточной суммы в). В появившемся окне выберите поле Фамилия, на котором процент дохода будет накапливаться сверху вниз. Вывод должен выглядеть следующим образом:
Как видите, это практически готовая таблица из первой части статьи. Не хватает только для полного счастья столбца с пороговым значением 80% для построения светотеневой линии на будущей диаграмме. Такой столбец можно легко добавить, используя вычисляемое поле. Выделите любое число в сводке и нажмите на вкладку. Главная – Вставка – Вычисляемое поле (Главная – Вставка – Вычисляемое поле). В открывшемся окне введите имя поля и его формулу (в нашем случае константу):
После нажатия на OK в таблицу добавится третий столбец со значением 80% во всех ячейках, и она окончательно примет необходимый вид. Затем вы можете использовать команду Сводная диаграмма (Сводная диаграмма) таб параметры (Параметры) or Анализ (Анализ) и настраиваем график точно так же, как и первый вариант:
Выделение ключевых продуктов
Чтобы выделить наиболее влияющие факторы, то есть столбцы, расположенные слева от точки пересечения оранжевой кривой накопленных процентов с горизонтальной линией отсечения 80%. Для этого вам придется добавить в таблицу еще один столбец с формулой:
Эта формула выводит 1, если произведение находится слева от точки пересечения, и 0, если оно находится справа. Тогда вам нужно сделать следующее:
- Добавляем на диаграмму новый столбец – проще всего это сделать простым копированием, т.е. выделением столбца. подсветка, скопируйте его (Ctrl + C), выберите диаграмму и вставьте (Ctrl + V).
- Выделите добавленную строку и переключите ее по вторичной оси, как описано выше.
- Тип диаграммы серии подсветка перейти к столбцам (гистограмме).
- Убираем боковой зазор в свойствах ряда (кликните правой кнопкой мыши по ряду Освещение – Формат строки – Боковой зазор), чтобы столбцы слились в единое целое.
- Убираем границы столбцов, а заливку делаем полупрозрачной.
В результате мы получаем вот такое приятное выделение лучших продуктов:
PS
Начиная с Excel 2016, диаграмма Парето добавлена в стандартный набор диаграмм Excel. Теперь, чтобы его построить, достаточно выбрать диапазон и на вкладке Вставить (Вставить) выберите подходящий тип:
Один клик – и схема готова:
- Как построить отчет с помощью сводной таблицы
- Настройка вычислений в сводных таблицах
- Что нового в диаграммах в Excel 2013
- Статья в Википедии о законе Парето