Вариант 1. Простая диаграмма Парето по готовым данным
Если исходные данные попали к вам в виде подобной таблицы (т.е. уже в готовом виде):
... то делаем следующее.
Сортируем таблицу по убыванию прибыли (вкладка Данные - Сортировка) и добавляем столбец с формулой для расчета накопленного процента прибыли:
Эта формула делит суммарную накопленную прибыль с начала списка до текущего товара на общую прибыль по всей таблице. Также добавляем столбец с константой 80% для создания в будущей диаграмме горизонтальной пороговой пунктирной линии:
Выделяем все данные и строим обычную гистограмму на вкладке Вставка - Гистограмма (Insert - Column Chart). Должно получиться примерно следующее:
Ряды с процентами на полученной диаграмме нужно отправить по вторичной (правой) оси. Для этого нужно выделить ряды мышью, но это может быть сложно, поскольку их плохо видно на фоне больших столбцов прибыли. Так что лучше воспользоваться для выделения выпадающим списком на вкладке Макет (Layout) или Формат (Format):
Затем щелкнуть по выделенному ряду правой кнопкой мыши и выбрать команду Формат ряда (Format Data Series) и в появившемся окне выбрать опцию По вторичной оси (Secondary Axis). В итоге наша диаграмма начнет выглядеть так:
Для рядов Накопленная доля прибыли и Порог надо поменять тип диаграммы со столбцов на линию. Для этого щелкните по каждому из этих рядов и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type).
Останется выделить горизонтальный ряд Порог и отформатировать его так, чтобы он стал похож на линию отсечки, а не на данные (т.е. убрать маркеры, сделать линию красной пунктирной и т.д.). Все это можно сделать, щелкнув по ряду правой кнопкой мыши и выбрав команду Формат ряда (Format Data Series). Теперь диаграмма примет окончательный вид:
По ней можно сделать вывод, что 80% прибыли приносят 5 первых товаров, а на все остальные товары правее картофеля приходится только 20% прибыли.
В Excel 2013 можно поступить еще проще - воспользоваться новым встроенным комбинированным типом диаграммы сразу при построении графика:
Вариант 2. Сводная таблица и сводная диаграмма Парето
Что же делать, если для построения нет готовых данных, а есть только исходная необработанная информация? Предположим, что в начале у нас есть таблица с данными продаж вот такого вида:
Чтобы построить по ней диаграмму Парето и выяснить какие товары лучше всего продаются, придется сначала проанализировать исходные данные. Проще всего это сделать с помощью сводной таблицы. Выделим любую ячейку в исходной таблице и воспользуемся командой Вставка - Сводная таблица (Insert - Pivot Table). В появившемся промежуточном окне ничего не меняем и жмем ОК, затем в появившейся справа панели переносим мышью поля исходных данных из верхней части в нижние области макета будущей сводной таблицы:
В итоге должна получиться сводная таблица с суммарной выручкой по каждому товару:
Сортируем ее по убыванию выручки, установив активную ячейку в столбец Сумма по полю Выручка и используя кнопку сортировки От Я до А (From Z to A) на вкладке Данные (Data).
Теперь нужно добавить вычисляемый столбец с накопленной процентной выручкой. Для этого еще раз перетащите поле Выручка в область Значения (Values) на правой панели, чтобы получить дубликат столбца в сводной. Затем щелкните по клонированному столбцу правой кнопкой мыши и выберите команду Дополнительные вычисления - % от суммы с нарастающим итогом в поле (Show Data As - % Running Total In). В появившемся окне выберите поле Наименование, по которому сверху-вниз будут накапливаться проценты выручки. На выходе должна получиться вот такая таблица:
Как легко заметить - это уже практически готовая таблица. В ней только не хватает для полного счастья столбца с пороговым значением 80% для построения линии отсечки в будущей диаграмме. Такой столбец можно легко добавить с помощью вычисляемого поля. Выделите любое число в сводной и затем нажмите на вкладке Главная - Вставить - Вычисляемое поле (Home - Insert - Calculated Field). В открывшемся окне введем имя поля и его формулу (в нашем случае - константу):
После нажатия на ОК в таблицу добавится третий столбец со значением 80% во всех ячейках и она, наконец, примет требуемый вид. Дальше можно воспользоваться командой Сводная диаграмма (Pivot Chart) на вкладке Параметры (Options) или Анализ (Analysis) и настроить диаграмму совершенно аналогично первому варианту:
Подсветка ключевых товаров
Для подсветки самых влияющих факторов, т.е. столбцов находящихся левее точки пересечении оранжевой кривой накопленных процентов с горизонтальной линией отсечки в 80% можно использовать подсветку. Для этого придется добавить к таблице еще один столбец с формулой:
Эта формула выдает на выходе 1, если товар находится левее точки пересечения и 0 - если правее. Затем нужно сделать следующее:
Добавляем новый столбец к диаграмме - проще всего это сделать простым копированием, т.е. выделить столбец Подсветка, скопировать его (Ctrl+C), выделить диаграмму и произвести вставку (Ctrl+V).
Выделяем добавленный ряд и переключаем его по вторичной оси, как было описано выше.
Тип диаграммы для ряда Подсветка меняем на столбцы (гистограмму).
Убираем боковой зазор в свойствах ряда (правой кнопкой мыши по ряду Подсветка - Формат ряда - Боковой зазор), чтобы столбцы слились в единое целое.
Убираем границы столбцов, а заливку делаем полупрозрачной.
На выходе получим вот такую симпатичную подсветку наилучших товаров:
