Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

Настоящая статья исследует  вопрос  экспресс визуализации  данных  по аренде нескольких автомобилей.
Построение модели визуализации  процессов в 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.

Рис. 3 - Модель экспресс визуализации графика аренды автомобилей
Вывод
 
Приведенный метод визуализации графика аренды может быть использован при разработке моделей любых процессов, требующих экспресс-визуализацию временных периодов.
Информация будет полезна аналитикам, изучающим методы и функции Excel, а также аналитикам баз данных.