График работы в Excel: как составить и автоматизировать

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

Разберём, как правильно составить график в Excel, какие формулы использовать и как могут помочь ИИ и автоматизированные системы.

Как составить график смен в Excel

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

1. Определить режим работы. Установить период планирования, количество смен в сутки, время их начала и окончания, продолжительность перерывов и применяемый способ учёта рабочего времени.

2. Рассчитать потребность. Определить, сколько сотрудников и с какими ролями должно работать в каждый день и временной интервал.

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

4. Создать шаблоны смен. Задать единые коды, время начала и окончания, оплачиваемую продолжительность и тип смены.

5. Распределить сотрудников. Сначала закрыть роли с обязательными квалификациями, затем укомплектовать остальные позиции и выровнять нагрузку.

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

7. Утвердить и довести график. Зафиксировать версию, дату утверждения и изменения, затем передать сотрудникам актуальный вариант.

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

Коротко о требованиях законодательства

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

1. Ст. 91 ТК РФ ограничивает нормальную продолжительность рабочего времени 40 часами в неделю (если у сотрудника не установлена сокращённая норма).

2. Ст. 103 ТК РФ обязывает доводить сменный график до сведения работников не позднее чем за один месяц до его введения в действие.

3. Ст. 108–110 ТК РФ регулируют перерывы в течение дня и непрерывный еженедельный отдых.

4. Фактически отработанное время фиксируется в табеле (унифицированные формы Т-12 или Т-13), и график часто становится основой для его заполнения.

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

СКАЧАТЬ ШАБЛОН ГРАФИКА РАБОТЫ 5/2

Скачать График работы 5/2

Как составить график смен 2/2 в Excel

Рассмотрим простой пример. Магазин работает ежедневно с 08:00 до 20:00. В смене должны находиться два сотрудника. Работников делят на две бригады: группа А работает первые два дня цикла, группа Б — следующие два. Затем цикл повторяется.

Обозначения: Д — дневная смена, В — выходной. Такой шаблон обеспечивает по два сотрудника ежедневно, но сам по себе ещё не гарантирует соблюдение нормы часов за учётный период.

Шаг 1. Соберите структуру книги

Для простого графика достаточно трёх листов:

Если график сложнее, отдельно добавляют листы «Потребность» и «Кадровые события». Это позволяет не смешивать план, отсутствия и фактические отметки.

Шаг 2. Настройте даты и дни недели

В одной ячейке укажите первый день периода, например B1 = 01.08.2026. Даты в календарной строке рассчитывайте последовательно:

C2 = $B$1; 

D2 = C2+1

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

=ТЕКСТ(C2;"ддд")

При смене даты начала периода вся календарная шапка обновится автоматически.

Шаг 3. Используйте единые коды

Создайте справочник кодов и подключите его к рабочим ячейкам через «Данные → Проверка данных → Список». Это исключит варианты вроде «в», «вых» и «выходной», которые формулы воспринимают как разные значения.

В примере 12-часовая смена содержит час неоплачиваемого перерыва, поэтому в расчёт входит 11 часов. Если перерыв оплачивается или имеет другую продолжительность, справочник нужно изменить.

Шаг 4. Сгенерируйте цикл 2/2

Если дата начала цикла указана в B1, рабочие и выходные дни можно расставить формулой:

=ЕСЛИ(ОСТАТ(C$2-$B$1;4)<2;"Д";"В")

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

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

Шаг 5. Рассчитайте смены, часы и отклонение

Количество дневных смен в строке сотрудника:

=СЧЁТЕСЛИ(C4:AG4;"Д")

Если каждая дневная смена равна 11 оплачиваемым часам:

=СЧЁТЕСЛИ(C4:AG4;"Д")*11

При нескольких типах смен удобнее использовать справочник кодов и часов:

=СУММПРОИЗВ(ЕСЛИОШИБКА(ВПР(C4:AG4;Справочник!$A$2:$B$7;2;ЛОЖЬ);0))

Отклонение от нормы:

=Фактические_часы-Норма_часов

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

Шаг 6. Проверьте укомплектованность

Для каждого дня полезно добавить итоговую строку. Например, количество сотрудников с дневной сменой в столбце C:

=СЧЁТЕСЛИ(C$4:C$20;"Д")

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

СКАЧАТЬ ШАБЛОН ГРАФИКА РАБОТЫ 2/2

Скачать График работы 2/2

Полезные формулы Excel для составления графика

Как использовать ИИ для составления графика работы

ИИ (Claude, ChatGPT, DeepSeek) можно использовать как помощника при составлении графика. Он помогает подготовить формулы, проверить исходные данные, найти пропуски и пересечения смен, рассчитать дефицит персонала и предложить вариант распределения сотрудников.

Что указать в запросе

Чтобы получить применимый результат, передайте ИИ:

• список сотрудников, их должности, ставки, квалификации и допуски;

• период планирования и параметры смен;

• потребность в персонале по дням, сменам и ролям;

• отпуска, больничные и доступность сотрудников;

• нормы рабочего времени и требования к продолжительности отдыха;

• дополнительные ограничения: запрет пересекающихся смен, работа только на определённых объектах, ограничения для отдельных категорий сотрудников;

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

Пример запроса. Составь график 2/2 на сентябрь для 12 сотрудников. Ежедневно в смене должны работать не менее четырёх операторов и одного администратора. Учти отпуска и доступность сотрудников, не допускай пересечения смен и проверь норму часов. Выведи график, незакрытые смены и отклонения по каждому сотруднику.

Ограничения ИИ для составления рабочего графика

При работе с ИИ важно учитывать следующие ограничения:

• Не знает внутренних правил компании. Если требования к сменам, отдыху, заменам или распределению нагрузки не указаны в запросе, ИИ может их не учесть.

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

• Не получает изменения автоматически. ИИ не узнает о новом больничном, замене или корректировке потребности, пока ему не передадут обновлённые данные.

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

Подробнее о том, как составить график работы с помощью искусственного интеллекта в статье — ИИ для создания графика работы.

Автоматизация графика работы в системе WFM

TARGControl — WFM-система для планирования смен, учёта рабочего времени и автоматического формирования табеля. Она объединяет весь процесс работы с графиками: от расчёта потребности в персонале до передачи табеля в 1С.

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

Ключевые функции TARGControl

Система позволяет:

• рассчитывать потребность в персонале по объектам, ролям, дням и часам;

• составлять графики с учётом норм рабочего времени и установленных ограничений;

• учитывать отпуска, больничные, доступность, квалификации и допуски;

• контролировать укомплектованность смен и заранее выявлять дефицит;

• доводить актуальный график до сотрудников;

• организовать согласование замен и передачу свободных смен;

• фиксировать приходы, уходы и перерывы;

• сравнивать график с фактически отработанным временем;

• выявлять опоздания, невыходы, ранние уходы и переработки;

• автоматически формировать табель;

• передавать подтверждённые данные в 1С и BI-системы.

Как работает система WFM

1. Получение исходных данных. Из 1С или другой кадровой системы в TARGControl поступают сведения о сотрудниках, структуре подразделений, должностях, нормах рабочего времени, отпусках и больничных. Данные о нагрузке можно получать с помощью интеграции с POS, WMS, MES или ERP либо загружать вручную.

2. Расчёт потребности. На основе нагрузки и нормативов система определяет, сколько сотрудников требуется на каждом объекте или участке по дням, часам и ролям.

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

4. Публикация графика и замены. Утверждённые смены доступны сотрудникам в личном кабинете мобильного приложения. Для замен и незакрытых смен используется Биржа смен.

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

6. Контроль отклонений и табель. TARGControl сопоставляет запланированные смены с фактическими отметками и показывает опоздания, невыходы, недоработки и переработки в режиме реального времени.

После проверки система формирует табель и передаёт подтверждённые часы в 1С и BI-системы.

Excel, ИИ или WFM-система

Вывод

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

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

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

А там, где Excel и точечная помощь ИИ перестают справляться по объёму, логично переходить на WFM-систему — она закрывает весь цикл целиком: рассчитывает потребность, формирует график, учитывает фактические выходы, показывает отклонения от плана, формирует табель и передаёт подтверждённые данные в 1С.