Автоматизированный график отпусков в Excel: создаем за 15 минут

Чтобы сделать график отпусков в Excel, создайте таблицу со списком сотрудников и датами года, используйте условное форматирование для визуализации периодов отдыха и добавьте формулы СЧЁТЕСЛИ или СУММПРОИЗВ для автоматического подсчета использованных дней. Это решение заменит хаос в чатах на прозрачную систему, где статус каждого сотрудника виден мгновенно. Ниже приведена полная инструкция по сборке такого файла с нуля, настройке цветовых маркеров и защите данных от случайных правок.

Шаг 1: Подготовка структуры и календарной сетки

Начните с чистого листа. Оптимальный диапазон для команды до 100 человек — строки 1–105 и столбцы A–AG (чтобы вместить все дни года).

1. Заголовки: В ячейку A1 впишите «ФИО», в B1 — «Должность».
2. Даты: В ячейку C1 введите дату начала года (например, 01.01.2026).
3. Автозаполнение: Выделите ячейку C1, наведите курсор на правый нижний угол (появится черный крестик) и протяните вправо до конца года.
   • Совет: Чтобы даты не сливались, выделите всю строку с датами, нажмите правой кнопкой мыши → Формат ячеек → вкладка Число → выберите формат Д (только число) или ДД.ММ.

Теперь у вас есть шапка таблицы. Закрепите её, чтобы заголовки были видны при прокрутке: перейдите во вкладку Вид → Закрепить области → Закрепить верхнюю строку.

Шаг 2: Визуализация и условное форматирование

Ручная закраска ячеек неудобна и подвержена ошибкам. Настройте автоматическую подсветку статусов. Допустим, мы будем использовать буквенные коды: «О» — отпуск, «Б» — больничный, «К» — командировка.

1. Выделите область дат (например, от C2 до последней ячейки года).
2. Перейдите на вкладку Главная → Условное форматирование → Создать правило.
3. Выберите тип: «Использовать формулу для определения форматируемых ячеек».
4. В поле формулы введите: =C2="О" (обратите внимание: ссылка на первую активную ячейку диапазона должна быть без знаков доллара $, чтобы правило применялось ко всей области).
5. Нажмите кнопку Формат, выберите вкладку Заливка и укажите зеленый цвет. Нажмите ОК.

Повторите процедуру для других кодов (например, красный для «Б», желтый для «К»). Теперь достаточно ввести букву в ячейку, и она окрасится автоматически.

Шаг 3: Автоматический подсчет дней и остатка

Самая важная часть — контроль лимитов. Добавьте справа от календарной сетки служебные столбцы для аналитики.

Подсчет использованных дней

В столбце после последнего дня года (допустим, это столбец AG) создайте заголовок «Отгуляно». В ячейке AG2 используйте формулу: =СЧЁТЕСЛИ(C2:AF2; "О") Эта формула посчитает количество ячеек с буквой «О» в строке конкретного сотрудника.

Если вы используете разные коды для основного и дополнительного отпуска (например, «О» и «ДО»), формула усложнится: =СЧЁТЕСЛИ(C2:AF2; "О") + СЧЁТЕСЛИ(C2:AF2; "ДО")

Расчет остатка

В следующем столбце (AH) назовите «Остаток». Предположим, норма отпуска — 28 дней. Формула в AH2: =28 - AG2

Протяните эти формулы вниз до конца списка сотрудников. Теперь при любом изменении в календаре цифры остатка обновляются мгновенно.

Шаг 4: Защита и финальная настройка

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

1. Разблокировка ячеек для ввода:
   • Выделите всю область, куда пользователи будут вносить данные (календарная сетка).
   • Нажмите Ctrl+1 (Формат ячеек) → вкладка Защита → снимите галочку «Защищаемая ячейка».
   • Нажмите ОК.
2. Включение защиты листа:
   • Перейдите во вкладку Рецензирование → Защитить лист.
   • Придумайте пароль (если нужно) и убедитесь, что разрешено только «Выделение ячеек» и «Изменение ячеек».
   • Теперь формулы в столбцах «Отгуляно» и «Остаток» изменить нельзя, а ставить отметки об отпуске — можно.

Сравнение методов ведения графика

Для большинства компаний оптимальным является гибридный вариант: условное форматирование для визуализации + формулы для подсчета дней.

Частые ошибки при составлении графика

• Игнорирование выходных в формулах. Если вы считаете дни функцией РАЗНДАТ или просто вычитанием дат, помните, что в графике отпусков обычно считаются календарные дни, включая выходные. Убедитесь, что ваша логика подсчета соответствует ТК РФ.
• Отсутствие резервной копии. Файл с графиком — критически важный документ. Сохраняйте версии с датами в названии (например, График_отпусков_2026_март.xlsx).
• Переполненная таблица. Не пытайтесь уместить комментарии к каждому отпуску в основную сетку. Для примечаний используйте всплывающие комментарии (правой кнопкой мыши → Добавить комментарий) или отдельный лист.

FAQ

Как учесть перенос праздничных дней? В самом графике (сетке дней) праздники уже учтены календарем. При подсчете дней отпуска формула СЧЁТЕСЛИ просто считает метки. Если вам нужно считать именно рабочие дни отпуска (для расчета зарплаты), используйте функцию ЧИСТРАБДНИ с указанием диапазона дат отпуска и списка праздников.

Можно ли сделать график совместным для нескольких пользователей? Да. Сохраните файл в облачное хранилище (OneDrive, Яндекс.Диск) и откройте доступ по ссылке с правом редактирования. Лучше всего использовать веб-версию Excel или аналог в Google Таблицах, так как десктопный Excel может блокировать файл при одновременном доступе, если не настроен режим соавторства.

Как распечатать график на одном листе А4? Перейдите в Файл → Печать. В настройках масштабирования выберите параметр «Вместить лист на одну страницу» или установите ширину в «1 страницу». Учтите, что при большом количестве сотрудников шрифт станет мелким, поэтому для печати лучше использовать сводную таблицу или фильтр по отделам.