График смен в Excel: 2/2, 3/3, формулы и практический пример

Что должен решать рабочий график смен

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

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

Рекомендуемая структура Excel-файла

Практичный файл удобно разделить на четыре листа. На листе «Сотрудники» храните ФИО, отдел, должность, норму часов и дату начала индивидуального цикла. На листе «Смены» задайте справочник кодов: например, Д — дневная 12 часов, Н — ночная 12 часов, В — выходной 0 часов. Здесь же можно хранить начало и конец смены.

Лист «График» содержит календарь: строки — сотрудники, столбцы — даты, в ячейках — коды смен. Справа выводятся итоги часов, ночных смен, рабочих дней и отклонения от нормы. Четвёртый лист «Контроль» показывает покрытие по датам и сменам, дефицит персонала и несколько ключевых показателей. Такая структура заметно надёжнее одного огромного листа, где справочники, календарь и итоги смешаны вместе.

Как автоматически построить режим 2/2 и 3/3

Для циклического режима не нужно вручную проставлять рабочие и выходные дни на каждый месяц. Пусть B2 содержит дату начала цикла сотрудника, а F1 — дату календарного дня. Для режима 2/2 можно определить рабочий день формулой =ЕСЛИ(ОСТАТ(F$1-$B2;4)<2;"Д";"В"). Первые два дня четырёхдневного цикла будут рабочими, следующие два — выходными.

Для режима 3/3 длина цикла равна шести дням: =ЕСЛИ(ОСТАТ(F$1-$B2;6)<3;"Д";"В"). Если требуется чередование дневных и ночных смен, лучше хранить последовательность кодов в отдельном справочнике и выбирать код по номеру дня цикла. Главное — привязать расчёт к реальной дате старта конкретного сотрудника, иначе одинаковый режим у разных бригад ошибочно начнётся в один день.

Даты месяца тоже лучше генерировать автоматически. В первой ячейке календаря можно поставить =ДАТА($B$1;$C$1;1), где B1 — год, C1 — номер месяца, а следующую дату получить как =F1+1. Тогда при смене месяца календарь перестраивается без ручного переписывания заголовков.

Как считать часы, ночные и отклонение от нормы

Если код смены находится в календаре, количество часов лучше получать из справочника, а не вводить вручную. Например, при таблице кодов на листе «Смены» можно использовать =ВПР(F2;Смены!$A$2:$B$5;2;ЛОЖЬ) или современный вариант =XLOOKUP(F2;Смены!$A$2:$A$5;Смены!$B$2:$B$5;0). Для месячного итога часы по всем датам суммируются.

Если дневная и ночная смены обе по 12 часов, количество ночных часов можно считать через =СЧЁТЕСЛИ(F2:AJ2;"Н")*12. Количество рабочих смен — =СЧЁТЕСЛИ(F2:AJ2;"Д")+СЧЁТЕСЛИ(F2:AJ2;"Н"). Отклонение от установленной нормы периода удобно показывать как =AK2-AL2, где AK2 — фактические часы, AL2 — норма.

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

Практический пример: магазин с двумя сменами

Представим магазин, которому ежедневно нужны минимум два сотрудника в дневную смену. Анна и Борис работают 2/2 по 12 часов, но циклы сдвинуты на два дня. В первые четыре дня месяца у Анны получатся коды Д, Д, В, В, а у Бориса — В, В, Д, Д. За один полный четырёхдневный цикл каждый отработает 24 часа.

Если в месяце сотруднику назначено 15 смен по 12 часов, фактическая загрузка составит 180 часов. При норме 168 часов отклонение равно 180−168=12 часов. Это не означает автоматически «12 оплачиваемых сверхурочных»: показатель лишь сигнализирует руководителю, что график нужно проверить до утверждения.

Теперь добавим третьего сотрудника и требование «минимум 2 человека днём». Если на 10 сентября код Д стоит только у Анны, формула покрытия покажет 1, а дефицит — 2−1=1 сотрудник. Руководитель увидит проблему заранее и сможет переставить смену, а не искать замену в день выхода.

Контроль покрытия по каждой дате

Для подсчёта дневной смены по конкретной дате используйте СЧЁТЕСЛИ по столбцу календаря. Если коды сотрудников за 10 сентября находятся в P2:P20, формула =СЧЁТЕСЛИ(P2:P20;"Д") вернёт число людей в дневной смене. Для ночной смены меняется только критерий на «Н».

Минимальную потребность лучше хранить отдельным числом, например 3 человека. Тогда дефицит можно рассчитать как =МАКС(0;3-СЧЁТЕСЛИ(P2:P20;"Д")). Условное форматирование подсвечивает только даты, где результат больше нуля. Если отделов несколько, используйте СЧЁТЕСЛИМН: одновременно проверяйте код смены и подразделение сотрудника.

На листе «Контроль» полезно вывести количество дат с дефицитом, максимальный дефицит, сотрудников с превышением нормы и число незаполненных ячеек. Это даёт руководителю короткий список проблем вместо необходимости визуально просматривать весь календарь.

Как организовать замены и план/факт

Если сотрудники регулярно меняются сменами, одного планового календаря недостаточно. Самый понятный вариант — хранить утверждённый план отдельно от факта. План показывает, как было запланировано покрытие, факт — кто действительно вышел. Так можно корректно передавать отработанные часы дальше и анализировать частоту замен.

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

Типичные ошибки и проверки файла

Первая ошибка — свободный ввод кодов смен. Опечатка «НН» вместо «Н» не попадёт в расчёт часов. Используйте проверку данных со списком допустимых кодов. Вторая ошибка — ручное изменение итоговых часов после перестановки: итог должен зависеть только от календаря и справочника смен.

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

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

Как собрать график быстрее и связать с другими таблицами

Небольшой график на несколько человек можно сделать вручную по формулам выше. Если сотрудников, подразделений и правил много, быстрее сначала описать структуру: режимы 2/2 или 3/3, длительность смен, минимальное покрытие, нужные итоги и отдельный план/факт. В SmartXLSX можно сформулировать это обычными словами и получить персональный XLSX с формулами и оформлением.

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