В статье мы расскажем, как создать график отпусков в Excel с разбивкой по месяцам и визуализацией в виде диаграммы Ганта. Мы предоставим шаблоны таблиц и формул, а также практические советы, которые помогут ускорить и автоматизировать работу HR-менеджеров.
Что такое график отпусков
График отпусков — это таблица или текстовый документ, содержащий информацию о том, кто, когда и на сколько уходит в ежегодный оплачиваемый отпуск.
График отпусков является обязательным документом для работодателей и работников (ст. 123 ТК РФ). Отсутствие графика может привести к предупреждению или штрафу для компании, а руководителя могут дисквалифицировать на срок до трех лет, запретив вести предпринимательскую деятельность или занимать руководящие должности (ст. 5.27 КоАП РФ).
Маленькие некоммерческие организации и индивидуальные предприниматели могут не составлять график отпусков, если заменят его письменным соглашением с сотрудниками (ст. 309.1 и 309.2 ТК РФ).
График отпусков составляют не только для соблюдения закона. Он также необходим для:
- Планирования задач и нагрузки заранее.
- Избежания ситуаций, когда ключевые сотрудники уходят в отпуск одновременно.
- Обеспечения непрерывности бизнес-процессов.
- Своевременной выплаты отпускных.
Сотрудники могут использовать график отпусков, чтобы определить, когда лучше брать отпуск, — чтобы получить больше отпускных и не пересечься с коллегами.
Правильно составленный документ упростит работу линейным сотрудникам, руководителям и HR-менеджерам. Поэтому при составлении графика отпусков учитывайте следующие моменты:
- В график не включают отпуска за свой счет, больничные, праздничные дни и отгулы, а также членов команды, работающих не по трудовому договору.
- Некоторые сотрудники имеют право на отпуск более 28 дней в году, например, учителя и несовершеннолетние.
- Сотрудник может уйти в отпуск только после шести месяцев непрерывной работы, за исключением несовершеннолетних и совместителей.
- Отпуск может начинаться и заканчиваться в любой день недели, не обязательно в понедельник.
Как создать график отпусков в Excel
До 2013 года работодатели составляли график отпусков по стандартной форме Т-7. Сейчас можно использовать любой формат, но многие работодатели по привычке продолжают использовать типовую форму. Она проста в использовании и не требует дополнительного составления приказа. Однако у нее есть и недостатки.
Форма Т-7 представляет собой список сотрудников и дат их отпусков. Она не дает наглядного представления о:
- месяцах, в которые отдыхают сотрудники;
- пересечениях отпусков;
- интервалах между отпусками;
- количестве сотрудников, находящихся в отпуске одновременно.
Кроме того, данные в форме Т-7 неудобно обновлять и дополнять. Для этого лучше подходит формат Excel-таблицы и диаграммы Ганта. С их помощью можно визуально отобразить график отпусков, автоматизировать заполнение и поддерживать актуальность данных.
Рассмотрим, как создать график отпусков в Excel с разбивкой по месяцам и визуализацией в виде графика.
Собираем исходные данные для графика отпусков
За основу возьмите типовую форму Т-7. Если вы используете собственную форму графика отпусков, можете использовать ее или наш шаблон.
Важно: Собственную форму графика отпусков нужно утвердить в отдельном документе — локальном нормативном акте.
Соберите данные о сотрудниках и их отпусках. Вам понадобятся:
- ФИО сотрудников;
- должности;
- даты начала и окончания отпуска.
Введите заголовки столбцов в первой строке:
- A1: ФИО;
- B1: Должность;
- C1: Общее количество дней отпуска;
- D1: Дата начала первого отпуска;
- E1: Дата окончания первого отпуска;
- F1: Количество дней первого отпуска;
- G1: Дата начала второго отпуска;
- H1: Дата окончания второго отпуска;
- I1: Количество дней второго отпуска;
- J1: Дата начала третьего отпуска;
- K1: Дата окончания третьего отпуска;
- L1: Количество дней третьего отпуска.
Перенесите данные из графика отпусков в столбцы A1, B1, D1, E1, G1, H1, J1, K1.
Убедитесь, что даты введены в правильном формате (например, дд-мм-гггг). Выделите столбцы с датами (D, E, G, H, J, K) и примените к ним формат ячеек «Дата» через меню «Формат ячеек».
В ячейке F2 (количество дней первого отпуска) введите формулу:
`=ЕСЛИ(ЕПУСТО(D2);0;E2-D2+1)`
Протяните формулу для всех ячеек в столбце F.
В ячейке I2 (количество дней второго отпуска) введите формулу:
`=ЕСЛИ(ЕПУСТО(G2);0;H2-G2+1)`
Протяните формулу для всех ячеек в столбце I.
В ячейке L2 (количество дней третьего отпуска) введите формулу:
`=ЕСЛИ(ЕПУСТО(J2);0;K2-J2+1)`
Протяните формулу для всех ячеек в столбце L.
В ячейке C2 (общее количество дней отпуска) введите формулу:
`=СУММ(F2;I2;L2)`
Протяните формулу для всех ячеек в столбце C.
В итоге получится таблица с основными данными об отпусках сотрудников. При желании фамилии сотрудников можно расположить в алфавитном порядке или распределить по отделам.
График отпусков. Таблица в Excel:
Разбиваем график по месяцам
Предположим, что нам нужно понять, кто из сотрудников в какой месяц идет в отпуск. Дополним таблицу с исходными данными, которую подготовили ранее.
Добавьте столбцы для каждого месяца. Например:
- M1: Январь;
- N1: Февраль;
- O1: Март;
- И так далее до последнего месяца (X1: Декабрь).
В ячейке 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)));"Отпуск";"")`
Эта формула проверяет, попадает ли хотя бы одна из дат начала или окончания отпуска в январь.
Протяните формулу вниз по столбцу M для всех сотрудников.
Примените формулы к другим месяцам. Для этого вам нужно будет изменить номер месяца в функции ДАТА. Например, так будет выглядеть формула для февраля (столбец N):
`=ЕСЛИ(ИЛИ(И(C2<=ДАТА(2025;2;28);D2>=ДАТА(2025;2;1));И(E2<=ДАТА(2025;2;28);F2>=ДАТА(2025;2;1)); И(G2<=ДАТА(2025;2;28);H2>=ДАТА(2025;2;1)));"Отпуск";"")`
Примените условное форматирование, чтобы выделить отпуска сотрудников:
1. Выделите диапазон ячеек с месяцами (M2:X16, если у вас 15 сотрудников).
2. Перейдите на вкладку Главная → Условное форматирование → Правила выделения ячеек → Текст содержит.
- Введите слово «Отпуск». Установите форматирование, например заливку ячейки цветом.
Таким образом, мы получаем график отпусков с разбивкой по месяцам. Этот метод позволяет определить, какие сотрудники находятся в отпуске в каждом месяце, и упрощает планирование работы команды.
График отпусков. Образец Excel:
Если у вас много данных и вы хотите автоматизировать процесс, используйте макросы или Power Query. Также можно создать сводную таблицу для более детального анализа данных по месяцам.
Визуализируем график отпусков с помощью диаграммы Ганта
Диаграмма Ганта позволяет наглядно увидеть пересечения отпусков, их продолжительность и интервалы. Давайте рассмотрим, как создать такой график в Excel:
Чтобы создать график отпусков в Excel в виде диаграммы Ганта, выполните следующие шаги:
Шаг 1. Скопируйте таблицу с исходными данными графика отпусков на отдельный лист.
Шаг 2. Скрыть столбцы с датами окончания отпусков (E, H, K).
Шаг 3. Перейти на вкладку Вставка → Гистограмма → Линейчатая гистограмма с накоплением.
Шаг 4. Выделите область построения диаграммы, затем щелкните правой кнопкой мыши и выберите вкладку Выбрать данные.
Шаг 5. На открывшейся панели нажмите кнопку Добавить. В поле Имя ряда введите «Дата начала первого отпуска», а в поле Значения выберите диапазон с датами начала отпусков.
Шаг 6. Для подписей выберите значения с именами сотрудников.
Шаг 7. В полученном графике выберите столбец с именами сотрудников, затем перейдите в Формат оси и установите Обратный порядок категорий. Даты окажутся сверху таблицы.
Шаг 8. В созданном графике выберите столбец с именами сотрудников, затем перейдите в Формат оси и установите Обратный порядок категорий. Даты будут отображаться в верхней части таблицы.
Шаг 9. В созданном графике выберите столбец с именами сотрудников, затем перейдите в Формат оси и установите Обратный порядок категорий. Даты будут отображаться в верхней части таблицы.
Выделите горизонтальную ось значений, перейдите на вкладку Параметры оси и в параметрах границ укажите минимальную и максимальную дату графика отпусков в числовом формате. Если вы создаете график на 2025 год, в минимальном значении укажите 45658, а в максимальном — 46022. Это особый формат хранения дат в Excel, где эти числа обозначают период с 1 января 2025 года по 31 декабря 2025 года.
Шаг 10. В Делениях укажите значение 29,0.
Шаг 11. В разделе Число выберите формат отображения Месяц.
Шаг 12. Удалите заливку и границы синих линий.
Шаг 13. Добавьте данные по второму отпуску в диаграмму. Для этого:
1. Добавьте новый столбец.
2. Рассчитайте количество дней между первым и вторым отпуском с помощью формулы =G2-D2.
3. Примените формулу ко всему столбцу.
4. Вставьте полученные значения в диаграмму, используя Ctrl + C / Ctrl + V.
Шаг 14. Скопируйте данные из столбца I (количество дней второго отпуска) и вставьте их в диаграмму с помощью Ctrl + C / Ctrl + V.
Шаг 15. Удалите заливку и границы у серых линий. Таким образом, вы получите интервалы второго отпуска для всех сотрудников.
Шаг 16. Добавьте интервалы третьего отпуска, повторив шаги 13-15.
Шаг 17. В результате вы получите график отпусков в Excel в виде наглядной диаграммы Ганта.
Шаг 18. Для улучшения визуального восприятия графика добавьте сетку для каждого столбца и подписи с именами сотрудников.
График отпусков в Excel. Шаблон:
Ошибки при составлении графика отпусков в Excel
При составлении графика отпусков в Excel сотрудники могут допускать как технические, так и управленческие ошибки. Наиболее распространенные из них:
Использование неточных формул. Часто из-за невнимательности или недостатка опыта сотрудники допускают ошибки в формулах для расчета дней отпуска или его продолжительности. Если ошибка не будет замечена вовремя, придется пересчитывать все заново.
Как исправить: проверяйте формулы на ошибки с помощью инструмента Проверка формул (Формулы → Проверка ошибок).
Отсутствие форматирования данных. Например, даты вводятся в разных форматах или используются текстовые значения вместо дат. Это может привести к тому, что некоторые формулы не будут работать, а расчеты станут неточными.
Как исправить: используйте единый формат для всех ячеек с датами — ДД.ММ.ГГГГ — и применяйте условное форматирование для выделения важных значений.
Отсутствие автоматизации процесса. Например, график отпусков ведется без использования автоматических функций Excel или графики рассылаются сотрудникам вручную.
Как исправить: при составлении графика отпусков в Excel используйте условное форматирование, фильтры и сводные таблицы. Для быстрого согласования и утверждения графика используйте системы кадрового электронного документооборота (КЭДО).
В Битрикс24 есть система кадрового электронного документооборота. С помощью этого сервиса удобно составлять и согласовывать график отпусков. Сотрудники могут подписать график и лист ознакомления электронной подписью прямо с телефона.
Не создают резервные копии. Если файл Excel будет удален, поврежден или не сохранен вовремя, восстановить данные будет сложно.
Как исправить: регулярно создавайте резервные копии файла с графиком отпусков. Используйте облачные сервисы или автоматическое сохранение.
Не адаптируют график отпусков под нужды компании. Готовые шаблоны графика отпусков в Excel упрощают и ускоряют работу HR-специалистов. Однако стандартные решения могут не подходить, особенно если в компании работает несколько тысяч сотрудников.
Как исправить:
- Определите, какие данные необходимы для вашего графика отпусков.
- Решите, какие функции вам нужны, например, автоматическое обновление данных, уведомления о пересечении отпусков, анализ оставшихся дней отпуска.
- Напишите макросы для автоматизации рутинных задач, таких как обновление данных по нажатию кнопки.
- Если у вас есть другие системы управления персоналом, рассмотрите возможность интеграции данных через импорт/экспорт файлов или API.
Не поддерживают график в актуальном состоянии. В течение года график отпусков может несколько раз изменяться: сотрудники увольняются, на их место приходят новые, кто-то переносит отпуск. Если не обновлять график отпусков и не добавлять новую информацию, может возникнуть путаница. В худшем случае работодатель может нарушить трудовой закон, если вовремя не выплатит отпускные или неправильно рассчитает отпускные дни.
Как исправить:
- Назначьте ответственных за актуальность графика отпусков. Это могут быть менеджеры отделов или HR-специалисты.
- Создайте регулярный график обновлений, например, ежемесячно.
- Защитите ячейки с формулами и важной информацией от случайных изменений.
- Настройте уведомления в Excel или используйте внешние инструменты для напоминаний о предстоящих обновлениях.
- Интегрируйте Excel с другими системами управления персоналом через импорт/экспорт файлов.
- Используйте API для автоматического обновления данных из других систем.
- Подготовьте подробные инструкции по использованию шаблона и внесению изменений.
