Учет графика рабочего времени с дневными и ночными сменами в Excel

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

Вот один из примеров присланного письма, его и постараемся рассмотреть в упрощенном варианте.

А кто-нибудь занимался составлением графика учета рабочего времени с дневными и ночными сменами? Мне нужно сделать так, чтоб отдельно считались дневные и ночные часы. То есть, если в графике стоит 12, значит 12 считаются как дневные, а вот с ночными сложности...Человек заступает на смену в 20:00, и до 22:00 идут 2 часа дневных, и так же утром с 6:00 до 8:00 тоже 2 часа дневных... в графике ставят 4 и 8 часов в разные дни, но из них по 2 часа получаются дневные... вот как бы задать такое условие, чтобы дневные часы считались в одной ячейке, а ночные в другой...

Обычно, данные вопросы поступают в рубрику функции «ЕСЛИ», я бы хотел рассмотреть задачу на примере функции «ВПР». Конечно, я рассматриваю только подсчет одного дня, обычно же это календарный месяц, но никто не мешает вам сделать все то же самое на все дни. Да, придется немного повозиться, но потом можно использовать его как шаблон на все последующие месяцы. В добавок,  в отличии от функции «ЕСЛИ», если будут вводиться другие смены, то вы без труда сможете их добавить, просто приписав к таблице со сменами, не изменяя саму формулу.

Итак, у нас есть три смены, в нашем случае они обозначены как 12,8,4 (но это не обязательно, можно сделать вполне удобочитаемые буквы или сокращения смен для наглядности).
Таблица графика рабочих смен сотрудников за один день.

Для решения задачи мы вводим два поля «день» и «ночь», где будут считаться соответствующие часы (это не обязательно, можно всю формулу прописать в столбце «Всего») и с помощью функции ВПР находим то количество часов, которое соответствует смене.

Учет рабочего времени с ночными сменами

В данном случае, искомое значение будет «Смена», таблица с данными это таблица со сменами (не забудьте закрепить ее, нажав «F4», чтобы формулу можно было протянуть на всех сотрудников).

И в конце считаем общее количество часов, умножив ночные часы на повышающий коэ-т за ночные смены.

Пример файла - Скачать

Поделиться:
13 Комментариев
  • Алия:

    вложение  .xlsx

    Добрый день, помогите пожалуйста в графике работ формулы забить, чтобы автоматически считали дни и часы, нам приходится вручную считать рабочие дни и отдых, у работника вахта 21 день работа и 21 день отдыха, в начале или конце года разбиваем график чтобы не превышал баланс и лимит по сверхурочке

    • excel:

      Добрый день! Из вашего описания не понятно, что вам необходимо считать автоматически. Какие дни и часы?

      В каких ячейках вам необходима формула автоматического подсчета? Как она должна считаться?

  • Андрей:

    Здравствуйте. Огромнейшее спасибо за помощь!!! Думаю этот вариант может пригодиться очень многим, кто занимается этой темой!!!

  • Андрей:

    Здравствуйте. Осмелюсь ещё раз побеспокоить по этой теме. Свой график я Вам выслал. Сможете ли помочь добавить в него подсчет дневных и ночных часов? Спасибо.

    • excel:

      Вот сделал https://yadi.sk/i/SFUVBUMCdFcjQ

      Принцип такой: делаем справочник смен

      Далее считаем кол-во смен в месяце по отдельности (с помощью функции СЧЁТЕСЛИ) и умножаем на кол-во ночных и дневных часов по отдельности.

    • excel:

      Правильно я распределил рабочие часы?

      смены

    • excel:

      А нет, немного не так понял. Думал 4 и 8 это разные смены. А вы отмечаете одну смену, просто в разных ячейках. Буду думать

    • excel:

      Письмо с файлом попало в спам, поэтому не увидел.

      Вам необходимо посчитать ОБЩЕЕ количество ночных и дневных смен за весь месяц?

      С учетом того, что если в конце месяца 31 числа сотрудник выходит в ночную смену в 20:00, то в этот месяц должно пойти 2 часа с 20:00 до 22:00 дневных и 2 часа с 22:00 до 00:00 ночных. Так?

      То есть человек вышел на полную смену, но в этот месяц должно пойти только 4 часа два из которых ночные. Так?

  • Андрей:

    Спасибо за помощь, но у меня немного другая задача... могу ли я Вам послать свой табель, чтобы было понятнее, что мне нужно сделать?

    • Римма:

      Добрый день. Я тоже заинтересовалась графиками работы. У нас вообще круглосуточный график с разными сменами, у меня вопрос, я не знаю как из графика сделать выборку по одному дню сотрудников в этот день.

      • excel:

        Делаете обозначения смен:

        П1 — 12

        П2 — 9

        П3 — 3

        Далее пишите формулу счетесли и считаете кол-во п1, п2, п3 и так далее умножая на кол-во часов.

        Будет что-то в этом роде, но только с нормальной формулой счётесли

        счётесли(п1)*12+счётесли(п1)*9+счётесли(п1)*3

    • excel:

      Присылайте, но быстрый ответ не гарантирую — убегаю

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.