Бесплатный шаблон графика смен для сотрудников в Excel с автоматическим калькулятором рабочего времени.

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

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

Краткий обзор шаблона

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

Эта структура намеренно проста. Вы можете вставить эти заголовки в Excel, преобразовать диапазон в таблицу и позволить формулам автоматически заполняться по мере добавления строк. В документации Microsoft указано, что таблицы Excel могут использовать структурированные ссылки и автоматически расширять вычисляемые столбцы по мере изменения данных таблицы. См. руководство Microsoft по структурированным ссылкам в таблицах Excel .

Формула расчета часов для использования

Если время начала указано в ячейке D2, время окончания — в ячейке E2, а неоплачиваемый перерыв (мин.) — в ячейке F2, эта формула рассчитывает оплачиваемые часы в десятичном формате, а также учитывает смену, заканчивающуюся после полуночи:

=IF(OR(D2="",E2=""),"",MAX(0,MOD(E2-D2,1)*24-F2/60))

Почему это работает: Excel хранит время в виде долей суток. Microsoft рекомендует вычитать одно время из другого, чтобы рассчитать прошедшее время. Умножение результата на 24 преобразует эту долю в десятичные часы. Эта MODчасть предотвращает превращение ночной смены, например, с 22:00 до 6:00, в отрицательную продолжительность. Microsoft описывает вычитание времени в разделе «Расчет разницы между двумя временами в Excel» .

Пример: обычная дневная смена

Предположим, Джордан работает с 9:00 до 17:30 с 30-минутным неоплачиваемым перерывом.

Прошло 8,5 часов. Вычтя 0,5 часа на перерыв, получаем 8,0 оплачиваемых часов.

Пример: ночная смена

Предположим, Кейси работает с 22:00 до 6:00 с 30-минутным неоплачиваемым перерывом. Простая =(E2-D2)*24формула может дать отрицательный результат, потому что время окончания отображается на часах раньше, чем время начала. MODФормула, основанная на , правильно рассматривает это как 8-часовой ночной период до вычета перерыва, оставляя 7,5 оплачиваемых часов.

Когда не следует использовать формулу MOD

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

=IF(OR(D2="",E2=""),"",MAX(0,(E2-D2)*24-F2/60))

В этой версии D2 может содержать, 3/3/2025 10:00 PMа E2 может содержать 3/4/2025 6:00 AM. Microsoft также рекомендует использовать полные даты и время при расчете прошедшего времени по датам. Такой подход устраняет неоднозначность и является более безопасным вариантом для длительных смен или многодневной работы.

Как подсчитать общее количество отработанных сотрудником часов за неделю

Предположим, в ваших данных о расписании столбцы A — дата, B — сотрудник, G — оплачиваемые часы. Вставьте дату начала недели в ячейку K1, а имя сотрудника — в ячейку K2. Затем используйте:

=SUMIFS($G:$G,$B:$B,K2,$A:$A,">="&$K$1,$A:$A,"<"&$K$1+7)

Эта функция суммирует только те строки с оплачиваемыми часами, которые соответствуют сотруднику и попадают в семидневный период, начинающийся в K1. Microsoft описывает эту SUMIFSфункцию как функцию для суммирования значений, отвечающих нескольким критериям, что делает ее подходящей для еженедельных итогов по сотрудникам. См. руководство Microsoft по функции SUMIFS .

Если ваш график всегда составляет ровно одну неделю, и каждый сотрудник занимает отдельную строку в еженедельном отчете, вы можете использовать более простые SUMформулы. Microsoft отмечает, что SUMэто предпочтительнее, чем вручную добавлять множество ссылок на ячейки, поскольку это проще в обслуживании и менее подвержено опечаткам. См. документацию по функции SUM от Microsoft .

Полезный еженедельный обзор

Хорошая рабочая тетрадь должна отвечать на три вопроса, не заставляя менеджера проверять каждую строку:

• Сколько часов работы запланировано на эту неделю для каждого сотрудника?
• Сколько всего отработанных часов запланировано?
• Какие сотрудники близки к пороговому значению, которое вы используете для оценки, или превышают его?

Простая сводная таблица может содержать информацию о сотруднике, запланированных часах, плановых часах и часах для проверки сверхурочной работы. Для команды из США формула планирования может выглядеть следующим образом:

Regular planning hours: =MIN(L2,40)
Over-40 review hours:   =MAX(L2-40,0)

Эти формулы полезны в качестве ориентира при планировании рабочего времени, но их не следует рассматривать как инструмент принятия решений по заработной плате. В соответствии с Законом США о справедливых трудовых стандартах, работники, на которых распространяются соответствующие положения и которые не освобождены от них, как правило, получают оплату за сверхурочную работу после отработки более 40 часов в неделю, однако могут применяться исключения и другие правила, а также могут устанавливаться дополнительные требования в соответствии с законодательством штата или местным законодательством. Министерство труда США разъясняет федеральные базовые нормы в информационном бюллетене № 23 о сверхурочной оплате .

Не менее важно и то, что запланированное количество часов не обязательно совпадает с фактически отработанным временем. В информационном бюллетене Министерства труда № 22, посвященном отработанному времени, поясняется, что компенсация зависит от фактического оплачиваемого рабочего времени. Используйте электронную таблицу для планирования штатного расписания; используйте утвержденный процесс учета рабочего времени и расчета заработной платы для определения оплачиваемых часов.

Как должен выглядеть хороший результат

Шаблон хорошо работает, когда расписание проходит несколько практических проверок.

Перед тем как доверить этой таблице реальное расписание, проведите следующие проверки. Электронная таблица, которая выглядит безупречно, но неправильно рассчитывает ночные смены, хуже, чем обычная, проверенная таблица.

Рекомендуемая структура рабочей книги

Лист 1: Расписание

Оставьте здесь таблицу смен с разбивкой по строкам. Добавьте фильтры, чтобы менеджер мог просматривать данные по одному сотруднику, должности, отделу или диапазону дат. Преобразуйте диапазон в таблицу Excel, чтобы новые строки наследовали формулы и форматирование.

Лист 2: Еженедельный отчет

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

Лист 3: Списки

Храните утвержденные имена сотрудников, должности, отделы и названия смен в одном месте. Эти списки могут поддерживать проверку данных, что помогает уменьшить количество орфографических ошибок, таких как «Front Desk», «Front desk» и «FrontDesk», которые в противном случае нарушили бы работу формул сводных данных.

Когда этот шаблон Excel подходит

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

Это также хорошо подходит в случаях, когда расписание меняется лишь изредка, но не постоянно. Менеджер может отредактировать одну строку, и сводка по часам обновится немедленно.

Когда следует отказаться от Excel

Переключитесь на специализированное программное обеспечение для планирования или управления персоналом, если электронные таблицы становятся источником повторяющихся ошибок или ручной работы. Признаки неисправности включают:

• в нескольких местах, где менеджеры редактируют разные копии;
• Частые замены в последнюю минуту, которые трудно согласовать;
• Необходимы автоматизированные правила трудового законодательства или механизмы контроля за соблюдением перерывов в работе;
• Сотрудникам необходима возможность самообслуживания и выбора смен;
• Вам необходимы данные учета рабочего времени, экспорт данных о заработной плате или журналы аудита;
• Необходимо оптимизировать численность персонала в соответствии с прогнозами спроса;
• Вы тратите больше времени на поддержание формул в актуальном состоянии, чем на составление графиков работы сотрудников.

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

Небольшие улучшения, повышающие безопасность шаблона.

• Используйте реальные значения времени в Excel. Вводите запятую 9:00 AM, а не текст, например 9am shift, .
• Перерывы в работе магазина измеряются в одном подразделении. Менеджерам легко вводить данные в минутах; формулу можно разделить на 60.
• Заблокируйте столбцы с формулами. Если рабочая книга используется многими пользователями, защитите формулы для расчета оплачиваемых часов и сводных данных от случайных изменений.
• Используйте только одно написание фамилии сотрудника. Проверка данных или идентификационный номер сотрудника уменьшают количество некорректных итоговых сумм.
• Разделяйте запланированное и фактическое рабочее время. Не перезаписывайте расписание данными о заработной плате, если только это не предусмотрено в вашем рабочем процессе.
• Обязательно тщательно проверяйте работу, выполненную за ночь. Это одно из самых распространенных мест, где в электронных таблицах учета времени могут возникнуть ошибки.
• Храните чистую основную копию. Дублируйте ее для каждого периода планирования, вместо того чтобы постоянно редактировать старую неделю, полную скрытых предположений.

И последний пример

Представьте себе команду поддержки из пяти человек. Алекс работает с 8:00 до 16:30 с 30-минутным перерывом с понедельника по пятницу. Калькулятор выдает 8 часов в день и 40 часов в неделю. Морган работает две смены с 12:00 до 20:00, две смены с 14:00 до 22:00 и одну смену с 22:00 до 6:00, каждая с 30-минутным неоплачиваемым перерывом. Один и тот же шаблон, основанный на строках, может последовательно рассчитывать каждую смену, включая ночную, а еженедельный отчет объединяет результаты в одну общую сумму по сотрудникам.

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

Официальные ссылки

• Служба поддержки Microsoft: Как рассчитать разницу между двумя временными точками в Excel .
• Служба поддержки Microsoft: Использование структурированных ссылок в таблицах Excel .
• Служба поддержки Microsoft: Суммирование значений на основе нескольких условий .
• Министерство труда США: Информационный бюллетень № 22, Рабочее время в соответствии с Законом о справедливых трудовых стандартах (FLSA) .
• Министерство труда США: Информационный бюллетень № 23, Требования к оплате сверхурочной работы .