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