Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА
Циклы [4]
Циклы VBA
Операторы ветвления [2]
Модули классов [2]
Создание, поля, свойства, события, когда использовать
Работа с приложениями и библиотеками [2]
Создание надстроек для ексель на .net, вызов компонентов .net
Прикладное программирование [5]
Примеры разработки прикладных программ на VBA

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 57051
Главная » Статьи » Программирование на VBA » Прикладное программирование

Построение модели экспресс визуализации графика аренды автомобилей методами MS Excel

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

Категория: Прикладное программирование | Добавил: NikitaDvorets (12.12.2023) | Автор: Дворец Никита Никитович E
Просмотров: 102 | Теги: экспресс, визуализация | Рейтинг: 0.0/0


Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!