Скачать шаблон календаря Excel для визуализации данных

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

Пошаговая инструкция как сделать интерактивный календарь в Excel

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

Шаг 1. Подготовка исходных данных

Допустим у нас есть исходные данные о продажах за учетный период 1 год (2023):

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

Для месяцев:

Для дней:

Каждый столбец является полем для сводной таблицы. По последним двум (с формулами) будут построены два среза для переключения между месяцами и выборкой данных по определенным дням и неделям. Также выборку можно будет реализовывать поквартально, но об это далее…

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

Во втором аргументе функции указываем параметр определяющий первый день недели – воскресенье для США (1) или понедельник для Европейского региона (2). В зависимости от формата календаря:

Описание устройства разрыва:

1. Размер разрыва равен количеству дней в неделе до первого дня недели месяца. То есть если следующий месяц начинается с 4-го дня недели значит разрыв равен 7-4=3 строки. Как показано в данном случае на рисунке.
2. Разрыв в столбце месяц заполняется названием следующего месяца.
3. Ячейки в разрыве столбца продажи не должны быть пустыми их следует заполнить значением 0.
4. Пустые ячейки после разрыва в столбце день следует заполнить числовыми значениями меньше чем число 1, но в порядке возрастания. Это важно для сортировки кнопок будущего среза по данному полю сводной таблицы.

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

Шаг 2. Создание и настройка сводной таблицы по исходным данным

Выделите диапазон ячеек с исходными данными A1:E404 и выберите инструмент: Instert → Tables → PivotTables → From Table/Range.

В появившемся окне «PivotTable from table or range» просто нажмите OK.

Далее делаем настройку полей сводной таблицы по схеме как показано ниже на рисунке:

• Filters → Month;
• Rows → Day;
• Values → Sales;

Шаг 3. Создание и настройка срезов в сводной таблице

Теперь создаем непосредственно 2 среза: один для переключения по месяцам, а другой для выборки данных по дням. Для этого сначала выберите любую ячейку в диапазоне сводной таблицы и выберите инструмент – Insert → Filters → Slicer:

Отмечаем флажком опции Month и Day, жмем ОК.

Теперь необходимо настроить в первую очередь срез для выборки данных по дням. Для этого сначала на срезе Month жмем на кнопку FEBRUARY. А только потом выбираем срез Day одним кликом левой кнопкой мыши по заголовку среза и в главном меню появиться новая опция Slicer в самом конце. В разделе параметров «Buttons» в поле «Columns» указываем значение 7, так как в одной неделе 7 дней. Затем в разделе «Size» задаем параметры высота 7,7 cm и ширина среза 7,7 cm.

Дальше кликаем правой кнопкой мышки по срезу Day и из появившегося контекстного меню выбираем опцию – Slicer Settings

В появившемся окне снимаем флажок с опции «Display header» и устанавливаем флажок на опции «Hide items with no data».

Шаг 4. Оформление панели управления календарем

Теперь нам нужно красиво расположить срезы и оформить подписи дней недели для календаря. Для этого добавим текстовую надпись выбрав инструмент – Insert → Text → TextBox:

Интерактивный шаблон календаря в Excel – ГОТОВ!

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

Стиль оформления внешнего вида среза также можно настраивать в разделе инструментов – Slicer Styles. В результате можно создавать эффектные календари для дашбордов с интерактивной визуализацией данных:

Скачать шаблон календаря Excel для дашборда

Если для среза Month установить в параметрах количество колонок кнопок значение 3 тогда будет удобно сегментировать данные по квартально. Ведь каждый квартал состоит из 3-х месяцев и начинается с Января, Апреля, Июля, Октября. Сложился пазл. Скачайте файл с примером и убедитесь на сколько эффективная эта идея. Очень полезная для разработчиков презентаций в Excel.

Таблица паролей доступа для переключения между пользователями дашборда:

Name	Email	Password
Alex	alex19XX@gmail.com	a12345
Mark	markmanager2-19XX@gmail.com	m12345
Elizabeth	elizabeth20XX@gmail.com	e12345
Yuna	yuna19XX@gmail.com	y12345
Administrator	admin	admin