Всем привет! 👋
В продолжении темы визуализации и автоматизации процесса создания графиков в Excel рассмотрим пример автоматического форматирования графика в зависимости от периода.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Исходные данные
О том, что Excel - отличный инструмент для прогнозирования мы подробно обсуждали в предыдущих уроках:
А потому, за основу примера возьмем отчет об объемах продаж, где объем продаж предстоящих периодов рассчитан с использованием функции ТЕНДЕНЦИЯ:
🔘 Для визуального отображения информации добавим график типа гистограмма с группировкой:
🔘 Изменить стиль диаграммы вы можете по вашему усмотрению выбрав нужный на вкладке "Стили диаграмм":
🔘 Дополнительно: определим ячейку для управления периодом (в ней будет происходить выбор месяца):
🔘 В условиях примера пусть это будет ячейка С2 которой присвоим имя "Period":
* Заданное имя в дальнейшем будет использовано при автоматизации
▶️ Цели и задачи
Для нашего примера решим следующую задачу: при выборе месяца (периода) соответствующий диапазон на графике должен окрашиваться в контрастный цвет.
Прим. автора: Безусловно, задать подобное форматирование мы можем в ручную установив нужный формат точки (ряда) данных:
Но если таких периодов много, а если они меняются или хочется видеть динамику и т.п.?
Согласитесь, править в ручную, ну такое себе удовольствие!? 🤔 А потому, давайте автоматизируем процесс!
▶️ Автоматическое форматирование графика в зависимости от периода
Важной составляющей процесса является следующий макрос:
Public Sub RepaintChart() - вызывается для перерисовки графика. Для выполнения этой задачи используется функция RepaintChart_
Заданные аргументы: nDate - значение даты и oChartRow - объект графика
Изменения цвета точек на графике происходит в зависимости от того, больше ли дата точки, чем значение nDate.
Также используются другие переменные и операторы:
- XValues - массив значений по оси X на графике.
- nPoint - циклическая переменная для итерации через точки на графике.
- isPlan - булевская переменная, указывающая, является ли точка в плане (дата больше nDate).
В цикле For...Next происходит изменение цвета точек на графике в зависимости от значения isPlan. Если isPlan равно True, то точки получают один цвет, а если isPlan равно False, то другой цвет.
Код будет полезным для визуального обозначения разных фаз выполнения проекта на графике: точки, соответствующие будущим датам, могут быть одного цвета, а точки с прошедшими датами - другого.
🔔 Обязательным условием для автоматического запуска макроса при изменениях на листе является наличие события Worksheet_Change.
А потому в модуль листа добавим код:
Теперь при каждом изменении месяца в ячейке С2 заданный период на графике будет окрашен в контрастный цвет:
▶️ Дополнения
Чтобы "окрасить" определенный период между выбранными датами следует несколько изменить код:
🔔 Обратите внимание! Для выбора периода используются значения, полученные из именованных диапазонов "PeriodStart" (ячейка С2) и "PeriodEnd" (ячейка С3).
⚠️ Важно! Убедитесь, что ячейки, к которым привязаны именованные диапазоны, содержат корректные даты без каких-либо дополнительных символов.
Далее, вызывается функция RepaintChart_1, которая проходит по точкам на диаграмме и окрашивает те, которые соответствуют выбранному периоду, в определенный цвет.
Теперь пользователь может визуально выделять интересующий его временной диапазон на графике:
Используя соответствующие условные операторы можно изменять условие, например, включить выбранную дату в визуализируемый диапазон:
📝 Теперь, благодаря такому подходу можно с легкостью задавать форматирование соответствующие выбранному периоду, а ваш отчет сделать более наглядным!
📍 Рекомендуемые статьи 🔽
