Как сделать график отпусков в Excel с пересечением отпусков сотрудников

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

Бланк в WordWord, 11 КБ Образец в WordWord, 11 КБ Образец в PDFPDF, 59 КБ

Проверка актуальности

Проверьте документ перед подписанием

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

Образец заполнения

ООО «Ромашка»

УТВЕРЖДАЮ

Генеральный директор П. П. Петров

20 ноября 2026 г.

График отпусков

на 2027 год

20 ноября 2026 г. № 12

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

Работники ознакомлены с графиком под подпись.

Начальник отдела кадров

Начальник отдела кадров

____________ / А. С. Смирнова /

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

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

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

Как построить график с пересечениями

Лист «Периоды». Столбцы: фамилия и инициалы, должность, отдел, количество дней, дата начала, дата окончания. Одна строка — один период отпуска; если сотрудник делит отпуск на части, строк будет несколько. Даты вводите настоящими датами Excel, а не текстом: формула их не прочитает иначе.
Лист «Календарь». В строке 1 — даты года: в первой ячейке 01.01.2027, следующая равна предыдущей плюс один, протяните до 31.12.2027. В столбце A — фамилии сотрудников без повторов, по одной строке на человека. В ячейке B2 будет формула, которая ставит 1, если человек в этот день в отпуске, и 0, если работает.
Формула на пересечение. В B2: =ЕСЛИ(СЧЁТЕСЛИМН(Периоды!$A:$A;$A2;Периоды!$E:$E;"<="&B$1;Периоды!$F:$F;">="&B$1)>0;1;0). Она считает строки, где фамилия совпадает и дата дня попадает между началом и концом отпуска. Протяните её на весь календарь: 365 столбцов и столько строк, сколько сотрудников.
Сумма по дню. Под календарём, в строке 367, поставьте =СУММ(B2:B366) — сколько человек в отпуске в этот день. Эта строка и есть карта пересечений: видно, в какие даты отдел теряет больше людей, чем может.
Подсветка. Выделите календарь и включите «Условное форматирование» → «Правила выделения ячеек» → «Равно» → 1, заливка зелёным. Для строки суммы задайте правило «Больше» с порогом по отделу: для отдела из шести человек при пределе двух одновременно отдыхающих — больше 2, заливка красным.
Сводка по сотруднику. Справа от календаря, в столбце после 31 декабря, поставьте =СУММ(B2:NB2) — сколько дней отпуска у человека по календарю. Сверьте с количеством дней в листе «Периоды»: расхождение значит, что период введён с ошибкой или захватывает соседний год.

Что проверить перед утверждением

Сумма дней по календарю совпадает с днями в листе «Периоды» у каждого сотрудника.
Ни в один день не отдыхает больше людей, чем допускает работа отдела; красные ячейки в строке суммы разобраны.
У каждого работника есть отпуск не меньше 28 календарных дней за год, если он не относится к категориям с удлинённым отпуском.
Право на отпуск в удобное время учтено: статья 262.2 ТК РФ даёт его работнику с тремя и более детьми до 18 лет, пока младшему не исполнилось 14 лет.
График утверждён не позднее чем за две недели до 1 января; о начале каждого отпуска работника извещают под роспись не позднее чем за две недели — статья 123 ТК РФ.
Право на отпуск за первый год обычно возникает после шести месяцев непрерывной работы. По соглашению сторон отпуск можно предоставить раньше, а некоторым работникам его обязаны предоставить до истечения шести месяцев по их заявлению.

Почему формула не срабатывает

Первая причина — даты записаны текстом. Excel отличает 17.05.2027 как дату от тех же цифр как строки: формула сравнения вернёт 0. Проверьте выравнивание — дата в ячейке прижимается вправо, текст влево. Лечится «Данные» → «Текст по столбцам» с форматом даты.

Вторая — фамилия в календаре и в периодах набрана по-разному: лишний пробел, другая буква, инициалы через точку и без. СЧЁТЕСЛИМН сравнивает текст точно. Возьмите фамилии в календарь копированием из листа «Периоды», а не набором руками.

Третья — диапазоны в формуле не закреплены. Знаки $ перед столбцом A и строкой 1 нужны, чтобы при протягивании формула не съезжала с листа «Периоды» и с шапки календаря.

Как считать пересечения на больших отделах

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

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

Третий — выгрузка в Power Query: запрос разворачивает каждый период в список дат, и пересечения считаются группировкой по дате. Этот путь удобен, когда график ведут в Excel, а исходные данные приходят из кадровой системы.

Частые вопросы

Можно ли вести график отпусков только в Excel?

Ведут и так, но утверждённый график — документ: его печатают, подписывает руководитель, работники знакомятся под подпись. Excel удобен как рабочий инструмент для расчёта пересечений, а сам график по статье 123 ТК РФ утверждают отдельным документом.

Как формулой посчитать, сколько человек в отпуске в конкретный день?

СУММ по столбцу этого дня в календаре: каждая ячейка уже содержит 1 или 0. Если календарь построен по-другому, подойдёт СЧЁТЕСЛИМН по листу периодов с той же проверкой даты между началом и концом.

Что делать, если отпуска всё равно пересекаются?

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

Нужно ли учитывать праздники при подсчёте дней отпуска?

Нерабочие праздничные дни в число календарных дней отпуска не входят — статья 120 ТК РФ. В календаре Excel это значит, что период, захвативший праздник, длиннее по датам, чем число дней отпуска. Сумма дней в листе «Периоды» и сумма по календарю разойдутся на число праздников — это нормально, но проверять расхождение нужно с учётом этой нормы.

Можно ли сделать такой календарь в Google Таблицах?

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

Когда график собирают вручную каждый месяц

Чтобы календарь отпусков собирался сам

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

Я настраиваю ИИ-агентов, которые принимают заявки на отпуск, сверяют даты с загрузкой отдела и приносят руководителю готовый график с подсвеченными пересечениями.

Читать об услуге «ИИ-агенты под задачу компании»