График отпусков в Excel: как сделать таблицу для планирования

Что нужно знать про график отпусков?

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

График обязаны составлять все работодатели. Если не предоставить документ по запросу трудовой инспекции, можно получить предупреждение или штраф до 70 тысяч рублей. А руководителя могут дисквалифицировать на срок до 3 лет.

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

Порядок составления графика отпусков:

Когда в фирме 3-5 сотрудников, график составляется легко — можно быстро понять, что в моменте есть «просадка» по кадрам. С большим количеством сложнее — поэтому во избежание проблем составляют наглядные черновики с графическим отображением периодов. Как это сделать, расскажем далее.

Как составить наглядный график в Excel?

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

Пример графика отпусков в Excel:

Рассмотрим построение графика в Excel на примере организации «Черзово»: в штате 10 человек, директор составляет план отдыха на 2024 год.

Шаг 1. Готовим исходные данные

Составьте таблицу с исходными данными:

Сразу сделаем основу графика. Воспользуемся диаграммой Ганта, которая наглядно покажет отрезки отсутствия сотрудников.

«Вставка» → «Гистограммы» → «Линейчатая гистограмма с накоплением».

Пока оставляем область пустой — данные начнём вносить на следующем шаге.

Шаг 2. Добавляем первый отпуск на диаграмму

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

1. Добавьте первый ряд. В открывшемся окне под блоком «Элементы легенды» нажмите плюсик. Имя: «Дата начала 1 отпуска». В строке «Имя» справа нажмите на иконку и выберите соответствующую ячейку. Тогда при изменении названия столбца аналогичная корректировка произойдёт в диаграмме.

«Значения Y»: выделить область с датами начала отпусков сотрудников.

«Подписи горизонтальной оси (категории)»: выберите диапазон с ФИО работников. Нажмите «ОК».

Не волнуйтесь, что пока график не даёт понимания об отпусках. Это лишь «болванка», которая вскоре станет полноценной диаграммой Ганта.

2. Внесём корректировки во внешний вид. Во-первых, удалим верхнюю надпись (название диаграммы) — «Дата начала 1 отпуска». Нажмите на текст и после появления выделения вокруг него нажмите «Delete» или «Backspace».

Перенесём отрезки с датами вверх. Для этого:

3. Добавим ряд с количеством дней отпуска по сотрудникам. Это можно сделать как на предыдущем этапе: перейти в раздел «Выбрать данные» и добавить новый ряд. Но есть более простой и быстрый способ:

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

4. Отредактируем горизонтальную ось, на которой указаны временные отрезки.

Минимум и максимум — это даты начала и окончания диаграммы. Однако, просто указать первый и последний дни не получится, потому что значения в ячейках указаны в числовом формате.

Чтобы узнать числовой формат нужных дат:

Числовые форматы переносим в подраздел «Границы» в строки с максимумом и минимумом. Ось с датами обновится, теперь диапазон выровняется: станет с 01.01.2024 до 31.12.2024 (или любой другой выбранный вами период).

Не переживайте, что последняя дата на оси — 16 декабря. Это из-за особенностей деления, фактически диаграмма настроена до 31 декабря. В параметрах оси в подразделе «Деления» можно поменять значение в строке «Основные». Путём подбора найдёте число, с которым на графике появится 31 декабря. Но это делать необязательно.

Здесь же можно настроить, чтобы на горизонтальной оси показывались названия месяцев. Для этого:

5. Уберём синие столбцы. Эти отрезки показывают количество дней от начала периода до начала отпуска. Мы для наглядности оставим только периоды отдыха:

Диаграмма стала наглядной — теперь видны периоды первых отпусков сотрудников. Остаётся добавить вторые и третьи отпуска.

Шаг 3. Добавляем второй отпуск на диаграмму

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

1. Вставим столбец после «Дата начала 2 отпуска», назовём «Разница между 1 и 2» — или любое другое по вашему усмотрению. Вручную считать ничего не нужно.

В первую строку вписываем формулу: =D3-B3 (проверьте соответствие ячеек по своей таблице). Так рассчитается разница в днях между началом второго и первого отпусков. Не забудьте задать ячейке числовой формат. Результат протяните на остальные ячейки, чтобы не вводить формулу в каждой строке.

2. Добавим полученную разницу на график, чтобы разделить отрезки первого и второго отпусков.

Это можно сделать через «Конструктор», функция «Выбрать данные» — мы рассматривали данный вариант в начале. Но проще скопировать значения столбца, выделить диаграмму левой кнопкой мыши и нажать «CTRL + V». Значения автоматически подставятся в диаграмму. Выбирайте вариант, который вам нравится больше.

3. Добавляем интервалы вторых отпусков на график. Выделите столбец с продолжительностью вторых отпусков, скопируйте значения и вставьте через «CTRL + V» в диаграмму.

4. Убираем заливку у промежуточного отрезка:

Шаг 4. Добавляем третий отпуск на диаграмму

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

Алгоритм добавления данных на диаграмму такой же, как для первого и второго:

Диаграмма с планом отпусков технически готова. Чтобы упростить анализ, добавьте сетку. Нажмите левой кнопкой мыши в любую область графика и дальше: «Конструктор» → «Добавить элементы диаграммы» → «Сетка». Выберите «Промежуточные горизонтальные по основной оси» и «Промежуточные вертикальные по основной оси».

Должен получиться график, который показали в начале статьи:

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

Что делать с графиком?

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

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

График переносят в форму Т-7 и утверждают по нормам и требованиям трудового законодательства.

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

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