Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
учет времени рабочих и выходных дней, как подсчитать время работы в выходные и рабочие дни при сменной, суточной работы.
 
#1
20.04.2022 09:38:36
Всем привет! 2 дня шарюсь по форуму с темой "учет рабочего времени" Много конечно интересного увидел, почерпнул. Но вот решения для своей задачи не нашел. Суть в следующем: Есть табель учета рабочего времени. Все работает, все считает, автоматом меняет выходные, праздничные, рабочие дни при смене названия месяца или года в заглавии табеля. Так же автоматом проставляет выходные дни в случае переноса дней. (на вкладке "Праздники" в календаре тупо в ручную поменять "р" на "в" или наоборот). Вообщем все устраивает, рисовал сам под свои нужды. (Может кому понадобится - берите) Так вот, имеется один не корректный подсчет, это подсчет времени работы в выходные дни. График смешанный, начало работы в 09:00 завершение в 18:30 для тех кто работает только днем (но с ними проблем нет) Так же есть кто работает только сутками с 09:00 до 09:00 следующего дня и кто сутками и днем. Так вот, выходной день (как и любой другой) начинается в 00 часов. Следовательно те кто заступил на сутки в рабочий день, а сменяется в выходной у него 15 часов в рабочий день и 9 часов в выходной, и наоборот, кто заступил в последний день выходных у них 15 часов выходных и 9 часов в рабочие. Вот и задумал исправить эту проблему, что бы в итоговом столбце "Выходные и праздничные" часы считались корректно с учетом дня заступления на дежурство. Но вот знаний не хватает как это реализовать В рабочем поле таблицы проставляются только часы согласно графика, ну или реально отработанное время (бывает больше бывает меньше) все привязки формул идут к строкам 14; 15; 16 в самом табеле. пробовал функцией ДЕНЬНЕД и ВРЕМЯ - не фига не получается. Может есть кто знает как это реализовать? Выручайте братцы!
Изменено: Юрий - 20.04.2022 09:43:01 (исправление грамотической ошибки)
 
 
 
#2
20.04.2022 11:32:50
Здравствуйте, Юрий!
Правильно ли я понимаю, что Вам требуется к итоговому расчёту "выходных/праздничных часов" добавить по 9 часов за каждый полностью отработанный предпраздничный день (24 часа колонке, левее коричневой) и вычесть по 9 часов за каждый полностью отработанный праздничный день накануне рабочего (24 часа в колонке левее белой).
Этого можно добиться, например, при помощи формул вида:
Код
=9*(
    СУММПРОИЗВ(($D17:$AH17=24)*($D$16:$AH$16="в")*($E$16:$AI$16="р"))
   -СУММПРОИЗВ(($D17:$AH17=24)*($D$16:$AH$16="р")*($E$16:$AI$16="в"))
    )
но для корректной их работы в последний день месяца потребуется в ячейке AI16 указывать является ли рабочим или выходным первый день следующего месяца.
 
 
 
#3
20.04.2022 13:12:22
Цитата
написал:
Правильно ли я понимаю, что Вам требуется к итоговому расчёту "выходных/праздничных часов" добавить по 9 часов за каждый полностью отработанный предпраздничный день (24 часа колонке, левее коричневой) и вычесть по 9 часов за каждый полностью отработанный праздничный день накануне рабочего (24 часа в колонке левее белой).
Не совсем так. Такой пример: Пупкин заступил на дежурство в пятницу в 9 утра и сменился в субботу в 9 утра. Так как его смена в пятницу, по графику, то и в табеле ему ставят 24 в пятницу. Итог: он отработал свои положенные 24 часа, из которых 15 часов это в рабочий день (пятница с 09:00 до 24:00) и 9 часов уже в выходной (суббота с 00:00 до 09:00). К учету, согласно трудового законодательства, входят: 15 часов в рабочий день обычная оплата по часовой ставке, 9 часов в выходной день по 1,5 ставке и 8 часов (с 22:00 до 06:00) ночные тоже по 1,5 часовой ставке. Но нам ставки считать не надо, это делает бухгалтерия. Наша задача чтоб в табеле было отражено общее количество отработанных часов из которых ночных, столько то, в выходные столько то, сверхурочных столько то. С ночными и сверхурочными проблем нет. Задача в выходных и праздничных.
Попробовал подставить вашу формулу, дает нулевой результат.
 
 
 
#4
20.04.2022 17:37:01
Юрий, покажите вручную в каких ячейках какой результат Вам требуется получить и по какому правилу этого можно достичь.
Я, к сожалению, по своей невнимательности не нашел даже в какой колонке складываются рабочие часы в будни...

Предложенная формула возвращает 0 корректировку для Пупкина потому, что он отработал 16-го и 30-го: в первом случае он захватил 9 часов понедельника, а во втором - столько же часов за субботу. В предложенном Вами примере только Ромашкин (2-го числа) отработал в будни по ставке выходного дня. В остальных случаях взаимные переработки компенсируют друг друга.
Логика работы предложенной формулы следующая: функция СУММПРОИЗВ возвращает сумму произведений первых, вторых и последующих членов векторов, записанных в качестве её аргумента; при этом каждый из векторов состоит двухиз логических нулей и единиц, являющихся результатом сравнения значений в указанных ячейках с заданными константами. Таким образом, каждая из двух функций СУММПРОИЗВ суммирует столько единиц, сколько раз одновременно совпадают три условия: человек отработал 24 часа, текущий день был выходным, а следующий день был праздничным (либо строго наоборот). И в конце концов на 9 часов умножается разница результатов вычисления двух переработок: за будни и за выходные дни.
Попробуйте адаптировать её к Вашей конкретной задаче.

ВАЖНО: в предложенную выше формулу следует внести поправку: умножить результат на -1. Либо использовать любые другие соответствующие правки.
В актуальном виде она возвращает положительную корректировку за превышение количества отработанных часов в будни над праздничными часами; а следует использовать обратную корректировку.
 
 
 
#5
21.04.2022 06:42:00
Цитата
написал:
Юрий, покажите вручную в каких ячейках какой результат Вам требуется получить и по какому правилу этого можно достичь.Я, к сожалению, по своей невнимательности не нашел даже в какой колонке складываются рабочие часы в будни...
Здравствуйте! Спасибо за отклик и старания помочь.
Именно будничные часы считать нет необходимости, подсчитывается общее количество отработанных часов.
Во вложении тот же табель и с описанием логики по которой должен выводится результат.
 
 
 
#6
21.04.2022 11:53:59
Юрий,
правильно ли я понимаю описанную текстом логику:
- количество ночных часов - это 8 часов, умноженные на количество отработанных суточных смен (по 24 часа в табеле)
- количество часов в выходные/праздничные дни складывается из следующих слагаемых:
-- ПЛЮС сумма часов в графике, отнесённом к выходному дню (литера "в" а 16 строке): в примере присутствуют только 24 смены в праздники... возможны ли обычные 8 часовые смены?
-- МИНУС сумма ночных часов в выходные/праздничные дни - по существующей логике эти часы учитываются дважды: как ночные, так и праздничные
-- ПЛЮС 9 часов за каждую полную смену (24 часа) в предпраздничный день (текущий день рабочий, а следующий - выходной)
-- МИНУС 9 часов за каждую полную смену в праздничный день накануне рабочего (наоборот: текущий день имеет литеру "в" в 16й строке, а следующий - "р")

Если Вам не нравится функция СУММПРОИЗВ, то тот же функционал можно реализовать на базе СЧЁТЕСЛИМН()
Код
=СУММЕСЛИ($D$16:$AH$16;"в";D17:AH17)
-8*СЧЁТЕСЛИМН($D$16:$AH$16;"в";$D17:$AH17;">0")
+9*СЧЁТЕСЛИМН($D17:$AH17;">8";$D$16:$AH$16;"р";$E$16:$AI$16;"в")
-9*СЧЁТЕСЛИМН($D17:$AH17;">8";$D$16:$AH$16;"в";$E$16:$AI$16;"р")
не забудьте добавить литеру "р"/"в" в ячейку AI16
 
 
 
#7
21.04.2022 15:44:42
, Спасибо большое за участие и помощь в решении этой задачи. Покумекав над предложенной вами формулой и поняв что она вычисляет
я объединил свою формулу с вашей. Получилось очень длинно и сложно но работоспособно. Проверил с разными часовыми данными при разных
ситуациях и графиках, все считает корректно. В итоге получилось, что из результата моей формулы вычитается результат вашей формулы и как итог корректные данные.
Цитата
написал:
- количество ночных часов - это 8 часов, умноженные на количество отработанных суточных смен (по 24 часа в табеле)
Да, при условии что количество часов больше 16.
Цитата
написал:
в примере присутствуют только 24 смены в праздники... возможны ли обычные 8 часовые смены?
Возможны, но сейчас совместная формула справляется с этим.
Цитата
написал:
МИНУС сумма ночных часов в выходные/праздничные дни - по существующей логике эти часы учитываются дважды: как ночные, так и праздничные
Совершенно верно. Согласно ТК  если часы совпадают выходные с ночными они и оплачиваются раздельно, не что не из чего не вычитается.
Еще раз спасибо за помощь!

Выкладываю готовый рабочий график. Может кто то тоже столкнется с данной задачей. А может кто предложит как модернизировать.
Изменено: Юрий - 21.04.2022 15:46:57
 
 
 
Страницы: 1
Наверх