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

Создадим объект визуализации данных для HR-дашборда из двух блоков. Первый блок для итоговых показателей, а второй для детального анализа динамки изменения показателей на недельном графике.

Создание визуализации для итоговых данных за неделю

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

Создание шаблона гистограммы с недельными итогами

Таблица состоит из трех столбцов. Первый – это исходные значения, второй столбец – это отступ для декоративного оформления дизайна чарта, третий столбец – остаток от 100%.

На основе исходной таблицы создаем шаблон визуализации из горизонтальной гистограммы. При необходимости переключаем столбцы и строки.

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

Создаем две фигуры овала для заполнения средней части гистограммы.

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

Первую левую часть делаем прозрачным цветом, а третью правую часть заливаем черным цветом с полупрозрачным фоном 30%. Настраиваем ширину столбцов в параметрах настройки серии данных гистограммы.

Создание дизайна для фоновых элементов чарта

На отдельном листе «ДАШБОРД» создаем фигуру прямоугольника со скругленными углами. Устанавливаем размеры высоты 5.7, и ширины 6.4 дюйма. Делаем прямоугольнику градиентную заливку для фона и линии границы. Кода цветов и положения показаны на экране.

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

Копируем черный скругленный прямоугольник.

Шкалу прогрессии показателей сконструируем из обычных прямоугольников. Три малые вертикальные прямоугольники символизируют деления на шкале прогрессии. Все четыре прямоугольника группируем в одну группу объектов. Копируем группу для второго столбца горизонтальной гистограммы.

Сочетания шаблона гистограммы и декорации фигур

Переносим на лист «ДАШБОРД» шаблон чарта прогрессии из горизонтальной гистограммы. Накладываем шаблон на шкалы и выравниваем все объекты относительно друг друга в логическом расположении по дизайну. Также оптимизируем размеры гистограммы под положение объектов декорации чарта прогрессии.

При необходимости подстраиваем ширину столбцов под размеры и расстояние объектов.

Чтобы удобнее организовать размеры шаблона изменим одно значение на минимальное 0%, а второе на максимальное – 100%. Также не забываем настраивать величину отступа изменяя размер центральной части столбцов гистограммы.

Для наглядной настройки отступа центральной части сделаем ссылку на ячейку листа «ДАШБОРД» и уже из нее оптимизируем размер отступа под геометрию дизайна. После позиционирования и настройки всех объектов на дашборде возвращаем исходные значения в таблице. Добавляем текстовую надпись со ссылкой на название таблицы. И форматируем внешний вид подписи.

Пример как сделать недельный линейный график в Excel

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

Два столбца для двух типов значений, а первый столбец начинается не от первого дня недели, а со значения START. Чарт будет выходить за рамки блока и для этого нам понадобятся дополнительные 2 значения. Одно в самом начале и одно в самом конце. В результате каждый столбец исходной таблицы для недельного графика будет содержать не 7, а 9 значений.

Сначала для примера заполним таблицу случайными значениями.

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

Создание шаблона недельного графика в Excel

Выделяем диапазон столбцов таблицы со значениями B2:C11 и создаем чарт с областями. Добавляем еще две серии данных, повторно ссылаясь на эти же два столбца таблицы со значениями.

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

Кастомизируем дизайн недельного чарта

Для чарта динамики нам понадобится новый блок. Скопируем блок на листе дашборд и изменим немного цвет градиентной заливки, чтобы два блока отличались по внешнему виду.

Переносим комбинированный чарт на лист дашборд и располагаем его над блоком так, чтобы он первым и последним своим значением выходил за границы блока изменяя размеры чарта.

Оформляем чарт стильными, но соответственными цветами градиентов областей и линий, а также маркеров графиков.

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

Скопируем фигуру блока в приложение PowerPoint. Нам нужны его точные размеры. Поверх блока накладываем прямоугольник немного большим размером, чем блок. Выделяем обе фигуры, выравниваем их по центру и выбираем инструмент «Combine» для вычитания формы блока из прямоугольника. В результате мы создали новую фигуру с отверстием по центру в форме блока. Отверстие получилось с такими же размерами как блок.

Копируем созданную фигуру для маски из PowerPoint в Excel и аккуратно накладываем ее на блок. Выравниваем маску и наслаждаемся красотой результата.

Добавляем текстовую подпись со ссылкой на название исходной таблицы для чарта анализа динамики изменения HR-показателей за неделю.

Разработка элементов управления для выборки данных за неделю

Подключаем чарты к базе данных. На листе «Данные» выделите всю таблицу в диапазоне A1:H366 и присвойте ее диапазону имя «mydata». Далее создайте сводную таблицу для этого диапазона. При создании сводной таблицы в параметрах укажите «mydata» в качестве источника данных и место положение будущей сводной таблицы на листе «Control» в ячейке A3.

Собираем сводную таблицу в конструкторе. Поле «Строки» содержит значения из столбца «День». В поле «Значения» - данные всех столбцов со значениями.

Нам понадобиться еще одна сводная таблица для выборки значений из базы данных по условию. Выделяем и копируем ранее созданную сводную таблицу в ячейку A15. В конструкторе второй сводной таблице изменяем значения в поле «Строки» на «Месяц».

На основе сводных таблиц создаем элементы управления – срезы данных. Мы будем использовать их для управления дашбордом. Нам понадобятся сразу 2 среза данных по столбцам «День» и «Месяц».

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

Настраиваем внешний вид панелей срезов, чтобы они гармонично вписывались в дизайн дашборда. Используем для этого мастер настройки стилей срезов.

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

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

Проверяем работоспособность дашборда под управлением выборки данных с помощью срезов.

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

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