Табель учета рабочего времени

"Добрый день. Есть время работы в месяц, и по этому календарю надо сделать табель учета рабочего времени, так как не все сотрудники работают одинаковое время, допустим Сидоров в понедельник отработал 6 часов, а Иванов 8 часов, вот и надо чтобы не считать на калькуляторе время, а просто ввел часы за месяц и табель уже раскидал время."

К письму прилагается табличка с табелем учета рабочего времени, фрагмент которой прилагаю.

Сразу видно, что табличка отражает не столько учет рабочего времени, сколько график работы. Табличка несомненно удобна для пользователя т.к. сразу видно сколько работников в одно время работают, что несомненно важно для организации работы, а вот считать рабочие часы в таком виде действительно неудобно из-за самого вида и текстового формата значений.

Учитывая, что основная задача обеспечить удобство пользования, принято решение не менять вид самой таблички, а добавить к ней дополнительные расчетные поля, которые обеспечат преобразование и расчет данных.

Первая задача преобразовать значения типа "8\17" в число отработанных часов. Кроме этого 17-8=9, а из таблички видно, что рабочий день 8-часовой, значит сюда заложен еще и перерыв на обед - 1 час. А если работа с "8/11" 11-8=3, то перерыва на обед нет. С учетом дополнительных условий, задачу можно решить с помощью следующей формулы.

=ЕСЛИ(ЕОШ(НАЙТИ("\";B7;1));0;ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))-ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))>4;1;0))

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

ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))

Опорой для этого служит знак "\", который разделяет значения начала и конца рабочего дня. Далее аналогично находится время начала рабочего дня и берется между ними разница.

ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))

Учитывая, что функция НАЙТИ("\";B7;1) возвращает ошибку "#ЗНАЧ!" для пустых ячеек таблицы, т.к. не находит опорный знак "\", в начало формулы добавляем условие, которое позволяет возвращать "0" если ячейка таблички вообще не заполнена.

=ЕСЛИ(ЕОШ(НАЙТИ("\";B7;1));0;

Теперь осталось разобраться с перерывом на обед. Условие такое, если рабочий день длится больше 4 часов, то перерыв есть и 1 час надо вычитать из общего рабочего времени, в противном случае перерыва нет. Эту задачу решает следующий фрагмент формулы.

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))>4;1;0)

Полученную формулу копируем в дополнительные расчетные ячейки.

Теперь осталось разобраться с графой "Ночь", т.е. с учетом ночных часов работы, видимо они оплачиваются отдельно. Дело хорошее, строго соответствует КЗОТ, поэтому надо что то придумывать. Условие такое: ночные часы считаются с 22.00 и рабочий день заканчивается в 24.00. За основу возьмем выше приведенную формулу.

=ЕСЛИ(ЕОШ(НАЙТИ("\";B7;1));0;ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))>22;ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-22;0)-ЕСЛИ(ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))<22;0;ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))))

Фрагмент формулы

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))>22;

проверяет оканчивается ли рабочий день в ночные часы, т.е. после 22.00.

Фрагмент формулы

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))<22;

проверяет начинается ли рабочий день в ночные часы, т.е. после 22.00. В зависимости от результата проверки указанные фрагменты возвращают либо "0" либо часы начала и конца работы в ночное время. Далее берется разница этих значений.

ЕСЛИ(ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))>22;ЗНАЧЕН(ПСТР(B7;НАЙТИ("\";B7;1)+1;2))-22;0)-ЕСЛИ(ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1))<22;0;ЗНАЧЕН(ПСТР(B7;1;НАЙТИ("\";B7;1)-1)))

Полученную формулу копируем в дополнительные расчетные ячейки.

Далее осталось только просуммировать часы работы за месяц и ночные часы. Это уже совсем просто.

Ограничения к применению.

1. В табеле не менять стиль заполнения рабочего времени, особенно это касается знака "\" по нему идет поиск и преобразование текстовых значений часов в числовые. Часы могут заполняться двух или одно-значными цифрами.

2. Формулы рассчитаны на приведенный график работы т.е. рабочий день с 8.00 до 24.00, после 22.00 считается ночное время, если график выйдет за указанные значения возможны ошибки.

Архив с файлом примера можно скачать.