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

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

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

На одном комбинированном чарте мы имеем возможность визуально анализировать данные в разрезе каждого дня недели. Очень удобный чарт и часто применяется на практике. Эффективно визуализирует данные.

Формулы таблицы исходных данных

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

Строка с заголовком «Top» содержит формулу суммирования всех категорий их внутридневных значений по каждому дню недели. Строка с заголовком «Down» просто содержит нулевые значения. Она необходима нам для оформления комбинированного и функционального дизайна.

Строка с заголовком «Курсор Y» в дальнейшем будет содержат логическую формулу работающей по условию. Формулу пропишем при подключении исходной таблицы к базе данных, а на текущий момент имитируем значения логической формулы. Один день «Среда» содержит итоговое значение, а все остальные дни с ошибкой нет данных.

Создание шаблона чарта с дневными и внутридневными барами

Выделяем все со значения таблицы для создания шаблона комбинированного чарта. Выберите инструмент «Insert», «Charts», «2D-Column», «Clustered Column».

Мы видим, что структура чарта по умолчанию составлена не подходяще для нашей задачи. Перевернем структуру чарта воспользовавшись кнопкой «Switch Row and Column».

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

Для серии данных с названием «Top» изменим тип чарта на линейный график. Аналогично выполним действия и для серии данных «Down» изменив тип чарта на линейный.

Для серии данных «Курсор Y» изменим тип чарта на Scatter. При этом в нашу структуру шаблона чарта добавились не нужные нам вспомогательные вертикальные и горизонтальные оси координат. Отключим их с помощью инструмента «Change Chart Type». В разделе «Combo» снимите флажки в столбце «Secondary Axis» для всех серий данных. И нажмите кнопку «OK».

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

Теперь выберите серию данных «Top» и поставьте флажок на последнею опцию в списке выпадающего меню «Up and Down Bars».

Далее вызываем дополнительное окно настройки параметров «Format Data Series» и устанавливаем ширину между барами 3%.

Настраиваем цвета и параметры градиентной заливки для всех баров. Обратите внимание на коды цветов.

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

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

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

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

Настройте дизайн интерактивного недельного чарта анализа продаж.

Уберем линию и сделаем прозрачный фон шаблона. Линию графика серии «Top» сделаем прозрачной. Также потом и для серии «Down» отключим линию из видимости. Для серии «Top» добавим элементы подписей данный сверху. Настроим цвет и размер шрифта подписей.

Как сделать график с интерактивным курсором

Пришло время заняться разработкой дизайна курсора на недельном чарте продаж. Выберите серию данных «Corsor Y» и добавьте ему из выпадающего меню из плюса редко используемую опцию «Error Bars». Но добавим его не с помощью флажка, а с помощью выпадающего под-меню и опции «More Options». Обратите внимание, что по умолчанию добавились горизонтальные «X Error Bars», но нас интересует вертикальные. Поэтому следует выбрать «Y Error Bars» из выпадающего списка группы инструментов Excel «Current Selection». Далее в дополнительном окне параметров «Error Bar Options» в разделе «Error Amount» выбираем опцию «Custom» и жмем кнопку «Specify Value», чтобы во втором поле ввода «Negative Error Value» указать ссылку на диапазон значений в исходной таблице на строке «Cursors Y» – это: =Processing!$B$10:$H$10. Первое поле «Positive Error Value» должно быть абсолютно пустым. Затем выделяем одним кликом мышки горизонтальные бары и жмем клавишу «Delete» на клавиатуре, чтобы удалить их. Также очень важно в дополнительном окне параметров «Error Bar Options» в разделе опцию «Vertical Error Bar», «Direction» выбрать опцию «Minus».

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

Теперь выберем серию данных «Курсор Y» и добавим ему элементы подписей данных сверху. Размер шрифта подписей курсора должен совпадать с размерами шрифтов остальных подписей данных на чарте. После чего кликнув правой кнопкой по области подписей данных курсора из появившейся контекстного меню выбираем опцию «Change Data Label Shapes» после откроется выпадающее меню с панелью фигур «Data Label Shapes». Там выбираем фигуру «Rectangle: Rounded Corners» для оформления дизайна подписи данных курсора и сразу делаем градиентную заливку и изменяем размеры. Чтобы изменять размеры нужно сначала два раза кликнуть по фигуре подписи. А сам маркер серии курсора можно скрыть из видимости отключив ему заливку фона и границу.

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

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

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

Снова подключаем дневные внешние бары серии «Top».

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

На рабочем листе «Data» находится таблица с реальными данными о показателях продаж по всем категориям на протяжении целого года. Объединим все значения таблицы в одну базу данных с помощью именного диапазона и назовем «mydata» для подключения. Выделяем диапазон ячеек A1:I366 и в поле ввода «NameBox» указываем имя для этого диапазона «mydata».

Не снимаем выделенную область, а сразу переходим к созданию сводной таблицы, но в параметрах вместо ссылки на этот диапазон указываем имя только что созданной базы данных «mydata» в поле ввода «Table Range». Во втором поле ввода «Location» указываем ссылку на ячейку A3 на заранее уже созданном рабочем листе с названием «Control».

В конструкторе сводной таблицы в качестве строк используем данные из поля «Day» и в качестве значений все категории продаж.

Нам понадобятся две такие сводные таблицы, поэтому копируем уже созданную таблицу в ячейку A17. Теперь на основе второй скопированной сводной таблицы создаем 3 среза данных. Сразу по трем полям «Month», «Week» и «Day». Это будут наши интерактивные элементы управления недельным чартом продаж.

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

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

Подключаем исходную таблицу к базе данных через формулы со ссылками на сводные таблицы. Для получения внутридневных значений по категориям продаж мы ссылаемся на вторую сводную таблицу A17. В аргументах формулы указываем соответственные ссылки на наименование строк и столбцов. Значения в аргументах формулы для подключения к сводной таблице должны быть текстового типа. Поэтому используем сцепление пустой строки вместе с адресом ссылки. А строка курсора содержит логическую формулу со ссылкой на первую сводную таблицу A3. Формула проверяет выбран ли текущий день недели если да, возрадуется итоговое значение суммы всех категорий, такое же как для дневного бара. А если нет, формула возвращает ошибку «нет данных».

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

Создание холодного дизайна чарта

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

Добавим два текстовых блока для создания меню переключения между горячим и холодным стилем дизайна недельного чарта продаж. Чтобы из текстовых блоков получилось интерактивное меню нужно задать ссылки для каждого блока на соответствующий лист. Для этого кликаем правой кнопкой мышки по текстовому блоку и из появившегося контекстного меню выбираем опцию «Link». В появившемся окне указываем лист и ячейку адреса пути ссылки внутри документа Excel. В ссылках лучше указывать ссылки на ячейки C3 чтобы скрыть курсор Excel за блоком визуализации при переключении между горячим и холодным дизайном дашбордов.

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

Недельный график для презентации отчетов в Excel

В результате получился очень стильный дизайн в двух версиях: холодные и горячие цвета внутридневных баров.

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

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

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