Сравнительный анализ графиков продаж в Excel скачать шаблон

Создавая график, состоящий из множества рядов данных, мы часто попадаем в ловушку графиков типа – спагетти. Рассмотрим способ представления нескольких рядов данных на графике с помощью интерактивной визуализации данных. Представленное решение позволяет перенести проанализированные данные на первый план, таким образом экспонируя их на фоне остальных показателей. При этом появляется возможность удобного сравнения их с другими рядами данных визуальным анализом.

График для визуального анализа сравнения по двум показателям в Excel

В нашем примере мы сравниваем продажи двух продуктов, представляя их на фоне оставшегося предложения. Для этого из приведенной ниже таблицы мы создаем линейный график, а затем устанавливаем для всех его рядов одинаковый цвет (например, серый), слегка уменьшая толщину линии графика.

Для этого вносим следующие изменения для каждого формата линии данных. Для этого щелкните левой кнопкой мышки по одной из линий, чтобы выделить ее и выберите инструмент:

1. «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Формат выделенного».

2. В появившемся дополнительном окне справа от графика «Формат ряда данных» перейдите на закладку «Заливка и границы» и в инструменте «ЛИНИЯ» выберите:

• опция – «Сплошная линия»;
• цвет – Белый, Фон 1, более темный оттенок 35%;
• ширина – 1,5 пт.

Основные линии сетки для вертикальной оси значений можно удалить, выделив их и нажав клавишу «Delete» на клавиатуре. Таким же образом можно удалить легенду графика и вертикальную ось его значений.

Интерактивный шаблон графика спагетти в Excel

Далее, рядом с графиком на листе «График-СПАГЕТТИ», мы создаем два раскрывающихся списка, источником которых являются заголовки столбцов продукта. Поля, выбранные в списках, позволят нам выбрать и выделить два ряда данных на графике. Для этого выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных». В появившемся диалоговом окне «Проверка вводимых значений» на вкладке «Параметры» в разделе опций «Тип данных:» выберите опцию «Список». А в поле ввода «Диапазон:» укажите ссылку которая ведет к диапазону ячеек заголовков столбцов таблицы $B$2:$H$2 на втором листе с именем «Данные». Конструкция внешней ссылки выглядит следующим образом =Данные!$B$2:$H$2 как показано ниже на рисунке:

Повторите эти же действия и для создания второго выпадающего списка:

Выборка двух групп значений на график

В ячейках рядом с таблицей на листе «Данные», используем такие функции, как ГПР. Таким образом, мы создаем два дополнительных столбца, из которых будем извлекать данные в зависимости от параметра, выбранного в раскрывающемся списке.

В заголовках столбцов используем формулы:

1. Для данных линии синего цвета формула в ячейке J2 на листе «Данные»: ="Синяя "&'График-СПАГЕТТИ'!G2
2. Для данных линии красного цвета формула в ячейке K2: ="Красная "&'График-СПАГЕТТИ'!I2

Формулы в ячейках столбцов для синей линии:

Теперь мы добавим два новых ряда данных (синий и красный) на график, которые появятся вместо их серых аналогов. Для этого выделите график кликнув по нем мышкой и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные». А в появившемя диалоговом окне «Выбор источника данных» в левой секции «Элементы легенды (ряды)» нажмите на кнопку «Добавить» и укажите диапазон ячеек столбца значений для синей линии, а потом выполните эти же действия для красной линии:

В результате получим график типа «спагетти» с экспонированными двумя линиями значений для сравнения при визуальном анализе данных по показателям.

Информативные подписи значений с анализом динамики изменения

Вы также можете добавить метки подписей значений на график, благодаря чему мы узнаем, как изменились продажи за анализируемый период. Чтобы отобразить на графике конечные показатели с изменением в процентном соотношении для наглядной динамики, на втором листе создадим еще два столбца с формулами:

1. M3 – ссылка на ячейку: =J3.
2. N3 – ссылка на ячейку: =K3.
3. M14 – формула: =J14&"|"&ТЕКСТ((J14/J3-1);"+0%;-0%;0%").
4. N14 – формула: =K14&"|"&ТЕКСТ((K14/K3-1);"+0%;-0%;0%").

В результате у нас имеются исходные значения для подписей данных на графике. В ячейках M14 и N14 мы вычислили разницу между первым и последним показателем в процентном соотношении для конкурентной группы сравниваемых товаров. Теперь добавим на график подписи данных, которые будут отображать значения из этих же столбцов. Сначала выделяем синею линию и выбираем инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Подписи данных»-«Справа» и выделим их все щелкнув один раз по любой подписи. В результате получим следующий вид графика спагетти с подписями:

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

Далее выбираем «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Формат выделенного». В дополнительном окне «Формат подписей данных» из раздела «Параметры подписей» отмечаем первую опцию «значение из ячеек»:

В появившемся окне «Диапазон меток данных» указываем ссылку на диапазон столбца подписей для синей линии на втором листе, то есть: =Данные!$M$3:$M$14. После чего снимаем все остальные галочки с этого раздела опций.

Осталось только выровнять по левому краю первую подпись. Для этого выделяем только первую подпись левым щелчком мышки по ней. После правым щелчком мышки по ней вызываем контекстное меню из которого выбираем опцию: «Формат подписей данных»-«Параметры подписей»-«Положение метки»-«Справа». После чего измените цвет шрифта для обеих меток на синий. Выделяем их и выбираем: «ГЛАВНАЯ»-«Шрифт»-«Цвет текста»-«Синий».

Выполняем аналогичное действие для красной линии, только там указываем соответственную ссылку на диапазон данных меток подписей для красной линии: =Данные!$N$3:$N$14. В результате получаем финальную версию графика спагетти с экспонированием двух просматриваемых историй изменения показателей для сравнения:

Скачать шаблон сравнительного анализа продаж на графике в Excel

Согласитесь, что такой интерактивный график намного удобнее не только для сравнительного анализа данных одновременно по 7-ми типам продуктов на протяжении 12-ти месяцев.