Вариант пересчета срока исполнения заявок в формат 9 часового рабочего дня методами MS Excel

Статья знакомит читателя с технологией пересчета срока исполнения заявок в формат 9-часового рабочего дня по формулам EXCEL.

1. Краткое описание проблемы

Диспетчерская служба условной организации общественного питания регулярно предоставляет своему руководству сведения по срокам исполнения заявок.
В состав этих данных входят четыре ключевых показателя:
1.      Номер заявки
2.      Дата / время (чч.мм) создания заявок Дсз
3.      Дата / время (чч.мм) закрытия заявок Дзз
4.      Время выполнения заявок Воз

Срок на исполнение заявок Воз в настоящее время рассчитывается следующим образом: Воз = Дзз – Дсз.

Отметим, что Воз - это формально рассчитанная разность времён закрытия и открытия заявки в предположении непрерывного 24-часового графика работы, что является логической ошибкой
Воз не учитывает 9-ти часовой график работы организации, а также выходные и нерабочие/праздничные дни.
При такой методике учета реально списанное время на исполнение заявок будет в общем случае занижено.
Таким образом, показатели эффективности работы менеджеров диспетчерской службы окажутся искусственно завышены.

В связи с данной проблемой целесообразна разработка технологии пересчета "общего" срока исполнения заявок в "реальные" сроки, с учетом 9-ти часового графика работы, а также выходных и нерабочих/праздничных дней.


2. Методика расчета реального времени выполнения заявки
При выборе методики расчетов реального времени был выбран формат аналитической таблицы.
Причина этого выбора - наглядное представление промежуточных результатов пересчета исходных данных в "реальное" время выполнения заявки.
Форматы расчета реального времени путем применения формул или пользовательских функций Excel, в отличие от аналитической таблицы, сразу выдают конечный результат и тем самым усложняют контроль расчетов.
Ниже представлена разработанная автором методика расчета чистого срока исполнения заявки (см. Табл. 1).

Табл. 1 - Методика расчета реального срока исполнения заявки

Примечание:
WeekDay - день недели даты создания заявки [6]        
Period - период заявки  [10]

Рис. 1 Код VBA пользовательской функции NumWeekendDays

3. Расчет реального срока исполнения заявок, формат 9 час/сут.

Указанная методика расчета реального срока исполнения заявок была реализована в форме аналитической таблицы на примере условных данных.
Технология расчета с применением формул Excel для работы с датами и временем, а также пользовательской функции Excel приведена на Рис. 2.

Рис. 2 - Аналитическая таблица расчета реального срока исполнения заявок, формат 9 час/сут.

В таблице на Рис. 2 отдельно выделены компоненты ДНИ, ЧАСЫ, МИНУТЫ общего и реального сроков исполнения заявок.

Результаты расчетов были выведены в отдельную таблицу, позволяющие сравнить сроки исполнения заявок в двух форматах времени (см. Рис. 3).

Рис. 3 - Суммарные сроки исполнения заявок, формат 24 час/сут и 9 час/сут.

Для наглядности результаты отображены на комбинированной диаграмме
(см. Рис. 4) , где сопоставлены сроки исполнения заявок в двух рассмотренных форматах времени (основная вертикальная шкала), а также их отношение друг к другу (вспомогательная вертикальная шкала).

Рис. 4 - Комбинированная диаграмма соотношение сроков исполнения заявок

4. Выводы

Анализ Рис. 4 позволяет сделать следующие выводы:

(1) Расчет сроков исполнения заявок в формате 9 час/сут приводит (в среднем) к их завышению по сравнению с форматом 24 час/сут.
(2) Наличие праздничных дней в сроки исполнения заявок приводит к обратной пропорции: срок исполнения заявок в формате 9 час/сут занижен относительно формата 24 час/сут.
(3) Технология расчета срока исполнения заявок должна быть основана на данных по чистому сроку исполнения заявок в формате 9-часового рабочего дня.
Подробная интерпретация результатов расчета чистого срока исполнения заявок выходит за рамки данной статьи.

Информация будет полезна аналитикам, изучающим функции Excel для работы с датами и временем, а также аналитикам баз данных.