В данном случае визуализация процессов рассматривается на примере данных по аренде нескольких автомобилей.
Построение модели визуализации процессов в Excel включает в себя: (1) настройку шкалы календарного графика формулами MS Excel и (2) применение пользовательских функций MS Excel.
Понимание задачи
Внесение временных интервалов, в базу данных и возможность их корректировки в большинстве случаев в большинстве случаев отображается на календарном графике.
Как правило, в MS Excel в качестве инструмента визуализации применяются различные графики и диаграммы (меню "ВСТАВКА" -> "ДИАГРАММЫ").
Альтернативно имеется возможность применения упрощенного календарного графика на текущем листе рабочей книги Excel (экспресс-визуалиация).
В качестве исходных данных примем условные значения календарного графика аренды автомобилей, см. Рис. 1.
Ключевыми данными для будущего графика будут: столбец C ("дата начала аренды") и столбец D ("дата окончания аренды").
Задача построения модели визуализации процессов включает в себя две решение двух задач:
1. Настройка календарного графика процессов и
2. Отображение на календарном графике периодов аренды автомобилей
1. Настройка календарного графика.
Для определения диапазона значений графика на листе "Параметры" внесем минимальную и максимальную даты всех периодов аренды автомобилей (см. Рис. 2).
Эти данные (глобальный период аренды) будут использованы при формировании настраиваемого календарного графика аренды.
В ячейку B2 листа "Параметры" вносится формула =МИН('График аренды'!C:C), вычисляя минимальную дату для всех перечисленных периодов аренды.
В ячейку B3 листа "Параметры" вносится формула =КОНМЕСЯЦА(МАКС('График аренды'!D:D);1), вычисляя максимальную дату для всех перечисленных периодов аренды. Формула( = КОНМЕСЯЦА([дата];[число месяцев]) позволяет создать последний день для месяца с максимальной датой.
Примечание: у версий программы ранее Excel 2007 отсутствует оператор КОНМЕСЯЦА. Обладатели этих версий Excel могут ознакомиться с решением этой проблемы по ссылке: https://lumpics.ru/formula-for-number-of-days-in-month-in-excel/.
Указанные параметры модели позволяют создать настраиваемый график аренды на листе исходных данных следующим образом:
(1) В ячейку H2 листа "График аренды" вносится формула =Параметры!B2, для установки начальной даты изменения календарных данных по аренде.
(2) В ячейки I2, J2, K2 и т.д. строки 2 вносятся формулы вида: =ЕСЛИОШИБКА(ЕСЛИ(КОНМЕСЯЦА(H2;1)<=Параметры!$B$3;КОНМЕСЯЦА(H2;1);"");"").
Как это работает?
Для ячейки I2 формула ЕСЛИ(КОНМЕСЯЦА(H2;1)<=Параметры!$B$3;КОНМЕСЯЦА(H2;1);"") сравнивает следующий (параметр "1" в формуле КОНМЕСЯЦА()) месяц после предыдущего столбца с максимальной датой периодов аренды.
Если дата этого месяца меньше максимальной даты по арендам, формула вносит эту дату в ячейку, а если уже больше, ячейка оставляется пустой.
Ячейки форматируются как "ДДД.ГГ" с целью в дальнейшем отображать на графике, входит ли этот месяц в период аренды для каждой строки (арендуемого авто).
Таким образом, при протягивании указанных формул вдоль строки 2, Excel отобразит в ней шкалу значений месяцев/годов, входящих в глобальный период аренды.
Даты, не входящие в глобальный период аренды, не отобразятся.
Отображение периодов аренды автомобилей
После построения шкалы периодов, отображение месяцев, которые вошли в каждый период аренды, реализовано в виде пользовательской функции TimeWithinRent, см. Рис. 3.
Функция TimeWithinRent размещает в ячейке символ "+", если календарный месяц входит в период аренды.
В обратном случае функция TimeWithinRent размещает в ячейке символ "-".
Следует отметить, что данный экспресс-график не указывает точные границы периода аренды, он указывает месяцы, входящие в него.
В диапазоне календарного графика H3:AH14 пользовательские функции водятся в каждую ячейку в формате =TimeWithinRent(U$2;$C5;$D5),
где:
U$2 - ячейка с проверяемым месяцем, строка 2 фиксирована
$C5 - ячейка с датой начала аренды, столбец "C" фиксирован
$D5 - ячейка с датой окончания аренды, столбец "D" фиксирован
Выделение диапазонов аренды автомобилей реализовано через инструмент условного форматирования MS Excel.
Календарный график аренды автомобилей представлен на Рис. 4.
Вывод
Приведенный метод визуализации графика аренды может быть использован при разработке моделей любых процессов, требующих экспресс-визуализацию временных периодов.
Информация будет полезна аналитикам, изучающим методы и функции Excel, а также аналитикам баз данных.