График отпусков в Excel: формулы, календарь и пересечения

1. Что должен решать хороший график отпусков

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

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

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

Практичная книга состоит из четырёх листов. «Сотрудники» хранит табельный номер, ФИО, отдел, должность, руководителя и группу взаимозаменяемости. «Отпуска» — отдельную строку на каждый период: сотрудник, начало, окончание, статус, комментарий и тип отсутствия. «Календарь» показывает год по дням или неделям. «Контроль» собирает конфликты, лимиты и показатели по отделам.

Не создавайте колонки «Отпуск 1», «Отпуск 2», «Отпуск 3». Если Иванов отдыхает 3–9 июня и 12–18 августа, это две строки журнала. Такой формат нормально фильтруется, расширяется и подходит для сводных таблиц. Для выбора сотрудника и статуса используйте проверку данных, а даты храните именно как даты Excel, а не текст вида «03.06–09.06».

3. Формулы продолжительности и рабочих дней

Если B2 — дата начала, C2 — дата окончания, календарная длительность периода считается формулой =C2-B2+1. Например, отпуск с 3 по 9 июня даст 7 календарных дней. Для защиты от ошибочно перевёрнутых дат можно использовать =ЕСЛИ(C2<B2;"Ошибка дат";C2-B2+1). Так некорректная строка не попадёт незаметно в итоговые показатели.

Для управленческого планирования полезно отдельно считать рабочие дни отсутствия: =ЧИСТРАБДНИ(B2;C2;Праздники!$A$2:$A$20). Если в периоде семь календарных дней, но пять рабочих, именно пять дней уменьшают доступную рабочую мощность команды при обычной пятидневке. Диапазон праздников лучше держать на отдельном листе и ссылаться на него во всех формулах.

Остаток планового лимита можно считать как =ПланДней-СУММЕСЛИМН(Отпуска[Дни];Отпуска[Сотрудник];[@Сотрудник];Отпуска[Статус];"Согласован"). Не смешивайте этот управленческий показатель с официальным кадровым балансом, если правила расчёта в вашей организации отличаются.

4. Как построить календарь и подсветку автоматически

В календаре строки удобно отдать сотрудникам, а колонки — датам. В D4 поставьте первую дату года, в E4 — =D4+1 и протяните вправо. Если в строке 5 находятся начало и окончание выбранного периода, правило условного форматирования может выглядеть так: =И(D$4>=$B5;D$4<=$C5). Оно подсветит все даты между границами и автоматически перестроится после переноса отпуска.

Для нескольких периодов одного сотрудника лучше не пытаться держать все даты в одной строке исходника. Календарь можно строить из журнала через СЧЁТЕСЛИМН: =СЧЁТЕСЛИМН(Отпуска[Сотрудник];$A5;Отпуска[Начало];"<="&D$4;Отпуска[Окончание];">="&D$4)>0. Если результат ИСТИНА, дата относится хотя бы к одному отпуску сотрудника.

Выходные можно подсветить отдельным правилом =ДЕНЬНЕД(D$4;2)>5. Важно задать приоритет правил так, чтобы отпуск оставался заметным и на выходных. Цвет — только визуальное представление; источником истины всегда остаются даты и статус в журнале.

5. Контроль пересечений и нехватки людей

Сам факт одновременного отпуска двух людей ещё не означает проблему. Сначала задайте лимит: например, в отделе продаж из 8 сотрудников одновременно могут отсутствовать максимум 2. Для даты в D4 число отсутствующих можно считать через СЧЁТЕСЛИМН по отделу, статусу и диапазону дат, а затем сравнить результат с лимитом отдела.

Если журнал содержит столбцы Отдел, Начало, Окончание и Статус, логика проверки такая: посчитать строки, где Отдел равен выбранному, Начало не позже проверяемой даты, Окончание не раньше неё, а Статус — «Согласован». Значение 3 при лимите 2 должно давать предупреждение «Конфликт» и попадать на лист «Контроль».

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

6. Практический пример с числами

Допустим, в отделе продаж работают Анна, Борис, Виктор и Галина, а лимит одновременного отсутствия — один человек. Анна планирует отпуск 8–14 июня, Борис — 12–18 июня, Виктор — 6–10 июля. У Анны 7 календарных дней, у Бориса 7, у Виктора 5. Пересечение Анны и Бориса приходится на 12–14 июня.

В контрольной строке для 12 июня формула считает два активных периода. Поскольку 2>1, ячейка получает статус «Конфликт». Руководитель переносит начало отпуска Бориса на 15 июня. После изменения одной даты календарь автоматически убирает пересечение, а контроль показывает максимум одного отсутствующего.

Дополнительно можно оценить рабочую нагрузку. Если в июне 21 рабочий день, а у Анны отпуск забирает 5 рабочих дней, её доступность для планирования составляет 16 рабочих дней. Для команды из четырёх человек базовая мощность месяца — 84 человеко-дня; отпуск Анны уменьшает её до 79 ещё до учёта отпусков остальных сотрудников.

7. Переносы, статусы и история изменений

Минимальный набор статусов: «Черновик», «На согласовании», «Согласован», «Перенесён», «Отменён». В календарь фактической доступности обычно стоит включать только согласованные периоды. Черновики можно показывать отдельным цветом, чтобы видеть потенциальные конфликты до утверждения.

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

8. Типичные ошибки и проверки перед использованием

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

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

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

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

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

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