Как построить линейный график для сравнения данных в Excel
Комбинированные графики – это лучшее решения для сравнительного анализа однотипных данных в одном блоке визуализации Excel. Их пользу сложно переоценить. Комбинированный линейный график можно использовать как самостоятельно, так и в сочетании с другими чартами на интерактивном дашборде Excel.
Как сделать интерактивный комбинированный линейный чарт в Excel
Главная задача комбинированных графиков предоставить пользователю визуализации данных возможность сравнения значений в пределах одной и той же области видимости. Чтобы не расходились глаза глядя одновременно на два и больше чартов при выполнении визуального сравнительного анализа данных.
Для примера возьмем простую таблицу с исходными данными с одной серией данных:
Нам необходимо презентовать с помощью визуализации данных сравнение выборки группы значений с общими однотипными показателями. Например, нужно сравнить второй квартал по отношению к целому текущему году. Какой процент продаж приходился на второй квартал, какую долю затрат пришлось закрыть во втором квартале. То есть делается выборка данных за учетный период – второй квартал и визуально сравниваем с текущим годом.
Это очень удобно если делать на одном и том же графике. Но для реализации данной возможности потребуется расширить функционал чарта с помощью создания интерактивных возможностей.
В этом примере мы будем использовать фильтрацию исходных значений для выборки и кнопки панели срезов данных для управления чартом. Чтобы реализовать такую возможность в Excel рациональным способом (без использования макросов), нам потребуется сводная таблица. Выделите диапазон ячеек всей исходной таблицы A1:B13 или просто поместите курсор Excel на любую ячейку из этого диапазона и выберите инструмент: «Insert» - «Tables» - «PivotTable».
Далее мы создадим панель для управления фильтрацией сводной таблицы с помощью элемента среза Excel. Фактически мы будем управлять выборкой данных за любой желаемый учетный период в пределах текущего года.
Создание панели кнопок для интерактивного управления чартом в Excel
Для этого поместите курсор Excel на любую ячейку в границах диапазона сводной таблицы A15:B28 и выберите инструмент: «Insert» - «Filters» - «Slicer». В появившемся окне «Insert Slicer» поставьте флажок на опции «Months» и нажмите на кнопку OK.
Так выглядит выборка за второй квартал. Но чтобы элемент срез гармонично влился в дизайн графика сделаем его горизонтальным, так как мы будем его эргономично использовать в области подписей горизонтальной оси X (месяцы). Для этого следует сделать несколько настроек среза. Выделите срез одним кликом левой кнопкой мышки и перейдите на появившеюся дополнительную вкладу в главном меню «Slicer». В разделе опций «Buttons» следует изменить параметр «Columns» на 12, так как в году двенадцать месяцев. А в разделе «Size» настраиваем ширину и высоту панели.
Чтобы скрыть заголовок панели и сделать ее вид более минималистичным сделайте два шага действий:
- Кликните по срезу правой кнопкой мышки и в появившемся контекстном меню выберите опцию «Slicer Settings».
- В появившемся окне в разделе «Header» снимите флажок с опции «Display header» и нажмите ОК.
Возвращаемся к исходной таблице, чтобы настроить ее для линейного графика.
Добавьте в исходную таблицу еще один столбец «Val Level 2» и заполните его формулами со ссылками на сводную таблицу:
=IFERROR(GETPIVOTDATA("Value",$A$15,"Months",A2),NA())
Этот столбец будет использован для второй серии данных комбинированного чарта. Нам нужен еще один столбец для третьей серии данных. Добавляем последний столбец «Cursor» и заполняем его формулами:
=IF(COUNTIF($C$2:$C$13,">0")=1,C2,NA())
Задача логической формулы в этом столбце – выводить значение при условии, что выбран только 1 месяц иначе #N/A.
Таблица настроена можно переходить к созданию и настройки шаблона для комбинированного графика.
Создание шаблона комбинированного линейного чарта в Excel
Поместите курсор Excel на любую ячейку в диапазоне исходной таблицы A1:D13 и нажмите комбинацию горячих клавиш CTRL+A (только один раз) – в результате будут выделены все ячейки в границах исходной таблицы. Далее выберите инструмент: «Insert» - «Charts» - «2-D Line».
Как видно выше на рисунке уже на данном этапе можно заметить преимущество линейного комбинированного графика. Осталось лишь настроить и экспонировать все серии данных.
Добавление типа графика Line with Markers в комбинацию визуализации
Одним кликом левой кнопкой мышки выделите на чарте кривую, связанную со столбцом «Val Level 2». Задайте этой серии данных новый тип чарта не снимая выделения выбрав инструмент: «Insert» - «Charts» - «Line with Markers».
Далее у нас нет возможности выделить третью серию данных с помощью клика мышкой по линейному графику. Ведь при выборке более одного месяца данные в последнем столбце не отображаются.
Добавление и настройка типа чарта Scatter в композиции визуализации
Мы получим доступ третей серии с помощью других опций в интерфейсе Excel. Например, можно кликнуть по любой видимой серии на чарте и нажать комбинацию горячих клавиш CTRL+1 или правой кнопкой мышки вызвать контекстное меню и выбрать опцию «Format Data Series». В любом случае мы получим доступ к дополнительному окну, где в разделе «Series Option» можно выбрать желаемый ряд данных из выпадающего списка опций.
Или можно просто выделить одним кликом сам чарт и в дополнительной вкладке меню «Format» перейти в раздел «Current Selection» и выбрать из выпадающего списка нужную серию данных.
Для выбранной необходимой третьей серии данных «Cursor» указываем другой тип чарта выбрав инструмент: «Insert» - «Charts» - «Scatter».
Первое что нам нужно сделать для отображения третей серии данных – это указать только один месяц для выборки данных на панели срезов. В таком случае формула будет отображать значения в столбце «Cursor» иначе #N/A - согласно условиям формулы.
Третий тип чарта необходимо настроить. Scatter Chart имеет в своих параметрах сразу два поля ввода для указания значений оси Y и X. Переходим в параметры. Для этого одним кликом выделяем область графика и в дополнительных вкладках главного меню выбираем инструмент: «Chart Design» - «Data» - «Select Data».
В появившемся окне «Select Data Source» в разделе «Legend Entries (Series)» жмем на кнопку «Edit» и в дополнительном окне «Edit Series» заполняем параметры полей соответсвенными ссылками на диапазоны ячеек исходной таблицы:
- Series X values:
- Series Y values:
=Sheet1!$A$2:$A$13
=Sheet1!$D$2:$D$13
Далее нам необходимо убрать вспомогательные оси координат. Кликните по большому плюсу возле выделенного чарта и в появившемся выпадающем контекстном меню снимите флажки с опций:
- «Chart Elements» - «Axes» - «Secondary Horizontal»;
- «Chart Elements» - «Axes» - «Secondary Vertical».
В этом же меню настроим декоративный стиль курсора. Но сначала мы должны его выделить кликом левой кнопкой мышки на графике.
Создание интерактивного курсора на линейном графике Excel
Жмем плюс и устанавливаем флаг на опцию: «Chart Elements» - «Error Bars» после чего из выпадающего меню выбираем опцию «More Options».
В результате появится дополнительное окно настроек «Format Error Bars». Из выпадающего списка в области параметров «Error Bar Options» выбираем опцию «Series Cursor Y Error Bars». Так мы будем настраивать вертикальные линии, а горизонтальные нужно убрать, выделив их кликом мышки и нажать на клавиатуре клавишу Delete.
Далее в области параметров «Vertical Error Bars» в разделе «Error Amount» отмечаем опцию «Custom» и жмем на кнопку «Specify Value» чтобы вызвать дополнительное окно «Custom Error Bars». В поле ввода «Negative Error Value» указываем ссылку на диапазон ячеек последнего столбца исходной таблицы: =Sheet1!$D$2:$D$13.
Дизайн для презентации комбинированного линейного чарта в Excel
Весь функционал шаблона настроен, теперь необходимо его просто раскрасить стильными цветами для презентации. Пример динамического дизайна интерактивной визуализации для сравнения значений.
Как говорят в Японии: «Пол дела сделать – дело продать!». Мы должны не просто создавать эффективный функционал, но и придать ему привлекательный внешний вид. Пользователь должен купиться на духовную энергию красоты дизайна визуализации. В результате ваш линейный чарт будет гармонично сливаться с любым дашбордом. Например, как показано в этом шаблоне:
Дашборд для управления KPI планами в Excel.
