Как составить график отпусков в Excel

Что такое график отпусков

График отпусков — это таблица или текстовый документ с информацией о том, кто, когда и на сколько уходит в ежегодный оплачиваемый отпуск.

График отпусков — обязательный документ для работодателей и работников (ст. 123 ТК РФ). Если его нет, компания может получить предупреждение или штраф. А руководителя могут дисквалифицировать на срок до трех лет ― запретить вести предпринимательскую деятельность или занимать руководящие места (ст. 5.27 КоАП РФ).

Не составлять график отпусков могут только маленькие некоммерческие организации и индивидуальные предприниматели. Но только если они заменят график отпусков на письменное соглашение с сотрудниками (ст. 309.1 и 309.2 ТК РФ).

График отпусков составляют не только потому, что этого требует закон. Он нужен, чтобы:

1. Заранее спланировать задачи и нагрузку.
2. Избежать ситуаций, когда ключевые сотрудники уходят в отпуск одновременно.
3. Обеспечить непрерывность бизнес-процессов.
4. Вовремя выплатить отпускные.

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

Если правильно составить документ, он упростит работу линейным сотрудникам, руководителям и HR-менеджерам. Поэтому, когда составляете график отпусков, учитывайте, что:

1. В него не вносят отпуска за свой счет, больничные, праздничные дни и отгулы. А еще — членов команды, которые работают не по трудовому договору.
2. Некоторые работники имеют право на отпуск больше 28 дней в году. Например, учителя и несовершеннолетние.
3. Сотрудник может уйти в отпуск только после шести месяцев непрерывной работы. Исключение: несовершеннолетние и совместители.
4. Отпуск может начаться в любой день недели, необязательно в понедельник. Закончиться — тоже.

Как составить график отпусков в Excel

До 2013 года работодатели составляли график отпусков по стандартной форме Т‑7. Сейчас подходит любой формат, но работодатели до сих пор по привычке используют типовую форму. Ее просто использовать, и с ней не нужно дополнительно составлять приказ. Но есть и минусы.

Форма Т-7 выглядит как список сотрудников и дат отпусков. Она не дает наглядное представление о том:

• в какие месяцы отдыхают сотрудники;
• у кого пересекаются отпуска;
• какой интервал между отпусками;
• сколько сотрудников одновременно находятся в отпуске.

Кроме того, данные в форме Т-7 неудобно обновлять и дополнять. Для этого больше подходит формат Excel-таблицы и диаграммы Ганта. С их помощью можно визуально отобразить график отпусков, автоматизировать заполнение и поддержать актуальность.

Рассмотрим, как составить график отпусков в Экселе — с разбивкой по месяцам и визуализацией в виде графика.

Собираем исходные данные для графика отпусков

За основу возьмите типовую форму Т-7. Если составляете график отпусков по собственной форме, можете использовать ее или наш шаблон.

1. Соберите данные о сотрудниках и их отпусках. Вам понадобятся:

   — ФИО сотрудников;
   — должности;
   — даты начала и окончания отпуска.

2. Введите заголовки столбцов в первой строке:

   — A1: ФИО;
   — B1: Должность;
   — C1: Общее количество дней отпуска;
   — D1: Дата начала первого отпуска;
   — E1: Дата окончания первого отпуска;
   — F1: Количество дней первого отпуска;
   — G1: Дата начала второго отпуска;
   — H1: Дата окончания второго отпуска;
   — I1: Количество дней второго отпуска;
   — J1: Дата начала третьего отпуска;
   — K1: Дата окончания третьего отпуска;
   — L1: Количество дней третьего отпуска.

3. Данные из графика отпусков перенесите в столбцы A1, B1, D1, E1, G1, H1, J1, K1.
4. Убедитесь, что даты введены в правильном формате (например, дд-мм-гггг). Выделите столбцы с датами (D, E, G, H, J, K) и примените к ним формат ячеек «Дата» через меню «Формат ячеек».

5. В ячейке F2 (количество дней первого отпуска) введите формулу:

   =ЕСЛИ(ЕПУСТО(D2);0;E2-D2+1)

   Протяните формулу для всех ячеек в столбце F.

6. В ячейке I2 (количество дней второго отпуска) введите формулу:

   =ЕСЛИ(ЕПУСТО(G2);0;H2-G2+1)

   Протяните формулу для всех ячеек в столбце I.

7. В ячейке L2 (количество дней третьего отпуска) введите формулу:

   =ЕСЛИ(ЕПУСТО(J2);0;K2-J2+1)

   Протяните формулу для всех ячеек в столбце L.

8. В ячейке С2 (общее количество дней отпуска) введите формулу:

   =СУММ(F2;I2;L2)

   Протяните формулу для всех ячеек в столбце С.

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

   График отпусков. Таблица в Excel:

   

Разбиваем график по месяцам

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

1. Добавьте столбцы для каждого месяца. Например:

   — M1: Январь;
   — N1: Февраль;
   — O1: Март.
   — И так далее до последнего месяца (X1: Декабрь).

2. В ячейке M2 (первая ячейка столбца «Январь» для первого сотрудника) введите формулу:

   =ЕСЛИ(ИЛИ(И(C2<=ДАТА(2025;1;31);D2>=ДАТА(2025;1;1));И(E2<=ДАТА(2025;1;31);F2>=ДАТА(2025;1;1)); И(G2<=ДАТА(2025;1;31);H2>=ДАТА(2025;1;1)));"Отпуск";"")

   Она проверяет, попадает ли хотя бы одна из дат начала или окончания отпуска в январь.

3. Протяните формулу вниз по столбцу M для всех сотрудников.

4. Примените формулы к другим месяцам. Для этого вам нужно будет изменить номер месяца в функции ДАТА. Например, так будет выглядеть формула для февраля (столбец N):

   =ЕСЛИ(ИЛИ(И(C2<=ДАТА(2025;2;31);D2>=ДАТА(2025;2;1));И(E2<=ДАТА(2025;2;31);F2>=ДАТА(2025;2;1)); И(G2<=ДАТА(2025;2;31);H2>=ДАТА(2025;2;1)));"Отпуск";"")

5. Примените условное форматирование, чтобы выделить отпуска сотрудников.
   - Выделите диапазон ячеек с месяцами (M2:X16, если у вас 15 сотрудников).
   - Перейдите на вкладку Главная → Условное форматирование → Правила выделения ячеек → Текст содержит.

   

   - Введите слово «Отпуск». Установите форматирование, например заливку ячейки цветом.

   

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

График отпусков. Образец Excel:



Если у вас много данных и вы хотите автоматизировать процесс, используйте макросы или Power Query. Можно также создать сводную таблицу, чтобы детальнее анализировать данные по месяцам.

Визуализируем график отпусков с помощью диаграммы Ганта

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

Чтобы сделать график отпусков в Excel в виде диаграммы Ганта, следуйте инструкции:

Шаг 1. Скопируйте таблицу с исходными данными графика отпусков на отдельный лист.

Шаг 2. Скройте столбцы с датами окончания отпусков (E, H, K).

Шаг 3. Откройте вкладку Вставка → Гистограмма → Линейчатая гистограмма с накоплением.

Шаг 4. Выделите область построения диаграммы и с помощью правой кнопки мыши перейдите на вкладку Выбрать данные.

Шаг 5. На открывшейся панели нажмите кнопку Добавить. В строке Имя ряда введите «Дата начала первого отпуска», в строке Значения выберите диапазон с датами начала отпусков.

Шаг 6. В качестве подписей выберите значения с ФИО сотрудников.

Шаг 7. В полученном графике выберите столбец с ФИО сотрудников → Формат оси → Обратный порядок категорий. Даты окажутся сверху таблицы.

Шаг 8. В полученном графике выберите столбец с ФИО сотрудников → Формат оси → Обратный порядок категорий. Даты окажутся сверху таблицы.

Шаг 9. В полученном графике выберите столбец с ФИО сотрудников → Формат оси → Обратный порядок категорий. Даты окажутся сверху таблицы.

Выделите горизонтальную ось значений → перейдите на вкладку Параметры оси → в параметрах границ укажите минимальную и максимальную дату графика отпусков в числовом формате. Если составляете график на 2025 год, в минимальном значении укажите 45658, а в максимальном — 46022. Это особый формат хранения дат в Excel. Эти числа говорят программе начать отсчет с 1 января 2025 года, а закончить 31 декабря.

Шаг 10. В Делениях укажите значение 29,0.

Шаг 11. В разделе Число выберите тип отображения Месяц.

Шаг 12. Уберите заливку и границы синих линий.

Шаг 13. Добавьте в диаграмму данные по второму отпуску. Для этого:

• Добавьте новый столбец.
• Рассчитайте количество дней между первым и вторым отпуском по формуле =G2-D2.
• Продублируйте формулу до конца столбца.
• Вставьте полученные значения в диаграмму с помощью Ctrl + C / Ctrl + V.

Шаг 14. Скопируйте данные из столбца I (количество дней второго отпуска) и вставьте в диаграмму с помощью Ctrl + C / Ctrl + V.

Шаг 15. Уберите заливку и границу у серых линий. Получаем интервалы второго отпуска для всех сотрудников.

Шаг 16. Добавьте интервалы третьего отпуска. Для этого повторите шаги 13-15.

Шаг 17. Получаем график отпусков в Экселе в виде наглядного графика — диаграммы Ганта.

Шаг 18. График можно визуально улучшить, если добавить сетку для каждого столбца и подписи с ФИО сотрудников.

График отпусков в Excel. Шаблон:

Ошибки при составлении графика отпусков в Excel

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

1. Используют неточные формулы. Часто из-за невнимательности или неопытности сотрудники допускают ошибки в формулах для расчета дней отпуска или его продолжительности. Если вовремя не заметить ошибку, придется считать все заново.

   Как исправить: проверяйте ошибки в формулах с помощью инструмента Проверка формул (Формулы → Проверка ошибок).

2. Не форматируют данные. Например, вводят даты в разных форматах или используют текстовые значения вместо дат. Из-за этой ошибки некоторые формулы не срабатывают, а в расчетах появляются неточности.

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

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

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

   В Битрикс24 есть система кадрового электронного документооборота. Через сервис удобно составлять и согласовывать график отпусков. А сотрудники могут подписать график и лист ознакомления электронной подписью прямо с телефона.

   Битрикс24 КЭДО + Госключ

   Подписывайте кадровые документы онлайн. Без доплат за каждого сотрудника.

   Узнать подробнее

4. Не делают резервные копии. Если удалить, повредить или вовремя не сохранить Excel-файл, восстановить данные будет трудно.

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

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

   Как исправить:

   - Узнайте, какие данные необходимы для вашего графика отпусков.
   - Решите, какие функции вам нужны. Например, автоматическое обновление данных, уведомления о пересечении отпусков, анализ оставшихся дней отпуска.
   - Напишите макросы для автоматизации рутинных задач, например обновления данных по нажатию кнопки.
   - Если у вас есть другие системы управления персоналом, рассмотрите возможность интеграции данных через импорт/экспорт файлов или API.

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

   Как исправить:

   - Определите сотрудников, которые будут отвечать за актуальность графика отпусков. Это могут быть менеджеры отделов или HR-специалисты.
   - Создайте регулярный график обновлений, например ежемесячно.
   - Защитите ячейки с формулами и важной информацией от случайных изменений.
   - Настройте уведомления в Excel или используйте внешние инструменты для напоминаний о предстоящих обновлениях.
   - Интегрируйте Excel с другими системами управления персоналом через импорт/экспорт файлов.
   - Используйте API, чтобы автоматически обновлять данные из других систем.
   - Подготовьте подробные инструкции по тому, как использовать шаблон и вносить изменения.