Как создать недельный линейный график в Excel для дашборда

Пример создания в Excel недельного Line Chart с графическим дизайном и интерактивными возможностями. На этом видеоуроке мы узнаем, как создать интерактивный линейный график в Excel. В данном примере рассмотрим применения эффекта резинового пластика на линейном графике для презентации и визуализации данных.

Разработка визуализации данных с графическим дизайном в Excel

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

Далее три столбца с исходными значениями. Наибольшие значения в Rang1. А построение значений на чарте будут идти по возрастанию. Поэтому первый столбец называется Rang3.

Заполним таблицу случайными исходными значениями для примера. При этом в первом столбце наименьшие случайные значения, во втором средние и в третьем Rang1 наибольшие случайные значения. Используем функцию RANDBETWEEN с разными параметрами в аргументах для разных столбцов соответственно рангу.

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

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

Построение шаблона презентации данных за неделю

Выделяем диапазон таблицы B1:H10 и создаем чарт с областями. Insert – Charts – 2D Area.

Как видно на подписях легенды чарта у нас 6 серий данных. Нам нужны еще 3 серии. Используем инструмент Select Data и добавляем еще три серии данных с именем Line 3,2,1 и ссылками на диапазоны столбцов Rang 3,2,1 - соответственно.

Теперь у нас 9 серий данных.

Для трех серий Lines изменяем тип чарта на 2D Line.

Аналогичным образом для трех серий Cursors изменяем тип чарта на Scatter.

У нас добавились по умолчанию дополнительные вертикальная и горизонтальная ось координат. Нужно их убрать. Для этого используем инструмент изменения типа чарта. В появившемся окне в меню слева выбираем самую последнюю опцию внизу с названием «Combo». В крайнем правом столбце «Secondary Axis» снимаем все флажки на против всех серий данных и нажимаем кнопку «Ок» для подтверждения.

В результате мы отключили все дополнительные оси координат.

Теперь нам нужно убрать лишние маркеры внизу на отметке 0 по си Y, со всех Scatter Charts. Для этого используем инструмент «Select Data». В появившемся окне в нижнем левом углу жмем на кнопку «Hidden and Empty Cells». В дополнительном окне нужно снять флажок с опции «Show #N/A as an empty cell».

В результате на комбинированном чарте отображаются маркеры только для курсоров со значениями. То есть три маркера в диапазоне дня недели «среда». Согласно данным в исходной таблице. Если данные изменить, чарт динамически обновит визуализацию. Но об этом позже рассмотрим на этапе создания интерактивных функций.

Как видно на подписях легенды у нас 9 серий данных, представлены в трех типах чартов, линейный, с областями и Scatter.

Создаем графический дизайн для недельного линейного графика

На отдельном листе создаем основу для графического дизайна недельного линейного чарта.

Из фигуры прямоугольник со скругленными углами создаем панель с градиентной заливкой фона и границы.

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

Создаем графический дизайн чарту с эффектом резинового пластика. Но сначала настраиваем порядок слоев областей серий данных Rang. Для каждой области чарта делаем градиентную заливку с полупрозрачными цветами. Важно чтобы цвета были разными.

Теперь выбираем линейные чарты и настраиваем параметры линий. Цвет, толщина линии, а также включаем опцию «Smoothed line».

Внимание! Теперь у нас возникает небольшая проблема. В Excel Чарт с областями не имеет опции сглаживания. Чтобы скрыть изъяны – зазоры между сглаженной кривой и чарта с областями, мы частично устраняем их за счет толщины линии и в значительной мере за счет наложения тени. Благодаря темному фону на заднем плане и падающей тении со сглаженной кривой, эффектно скрываются нежелательные зазоры между разными типами чартов. Чтобы усилить эффект тени уменьшим прозрачность до 35% в настройках параметров.

Выполняем аналогичные действия и настройки для остальных кривых линий.

В графическом дизайне часто используется эффект маски. Создадим фигуру для маски в программе PowerPoint, так как там редактор фигур обладает расширенными возможностями. Сначала копируем в PowerPoint фигуру панели из прямоугольника со скругленными углами, созданную ранее в Excel. Затем в PowerPoint по центру накладываем еще одну фигуру прямоугольника с немного большими размерами. Выделяем обе фигуры и выбираем в редакторе инструмент «Merge Shapes» - «Combine».

У нас получилась новая фигура с отверстием в результате слияния. Копируем фигуру маски обратно в Excel и накладываем ее на чарт выравнивая так чтобы отверстие находилось точно над прямоугольником со скругленными углами. В результате части чарта которые выходили за границы – будут скрыты.

Добавляем интерактивные кнопки для управления дашбордом

На основе таблицы листа базы данных создаем сводную таблицу в Excel. В конструкторе таблицы настраиваем поля. В строках сокращенные названия дней недели, а в поле значения столбцы Rang. Копируем сводную таблицу и на ее основе создаем кнопки срезов данных. Мы будем использовать их как элементы управления недельным линейным чартом с графическим дизайном.

Должно быть три среза Первый «Месяц», второй «Неделя» должны быть подключены только ко второй сводной таблице. А третий срез «День» должен быть подключен к управлению только первой сводной таблицей.

Создаем дизайн внешнего вида для кнопок и панели срезов в шаблонах путем дублирования и модификации уже готовых вариантов.

С помощью функции GETPIVOTDATA подключаем данные из сводной таблицы к исходной таблице в диапазоне столбцов Rang. В диапазоне столбцов Cursor используем логическую формулу, которая проверяет какие дни недели используются для выборки данных, в таких днях курсор будет показан на чарте. Если же день не выбран логическая формула возвращает функцию NA.

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

Добавляем формулы для подписей данный на чарте. Суммирование значений по выбранным дням недели для трех показателей. Общая сумма выборки за указанный период.

Создаем объекты TextBox для добавления подписей на визуализацию. В каждом TextBox через строку формул указываем ссылку на ячейку с источником соответственных данных.

Презентация недельного линейного графика

Скачать недельный линейный график в Excel

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