Кадровый учёт 26 января’24

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

Одна из задач руководителя отделения или организации — заранее запланировать отдых сотрудников. Нужна золотая середина: удовлетворить желания работников и не оставить предприятие с дефицитом кадров. Для этого до утверждения графика отпусков составляют предварительный план с визуализацией для внесения корректировок. Рассказываем, как составить наглядную диаграмму в Excel за 15-20 минут.

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

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

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

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

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

  1. Обсудить с работниками желаемые периоды отдыха. Работодатели не обязаны это делать, но в корпоративной среде так принято. Это позволяет поддерживать в коллективе хорошую атмосферу и взаимоотношения.
  2. Составить черновик. Проверить, чтобы в моменте не отсутствовало слишком много работников, особенно со схожими функциями. Например, работа затруднится, если одновременно в отпуск уйдут все продажники или производственные рабочие.
  3. Заполнить табель по форме Т-7 — утверждена постановлением Госкомстата РФ от 05.01.2004 года № 1. Это чистовой вариант, подписанный руководителем компании или предпринимателем. Если в организации есть профсоюз, то график предварительно согласовывают с представителем.
Скачать график отпусков по форме Т-7
doc

Скачать doc

xls

Скачать xls

pdf

Скачать pdf

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

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

Попробовать бесплатно

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

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

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

Пример графика Пример графика

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

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

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

  • ФИО сотрудников;
  • дата начала первого отпуска;
  • количество дней первого отпуска;
  • дата начала второго отпуска;
  • количество дней второго отпуска;
  • дата начала третьего отпуска;
  • количество дней третьего отпуска.

Готовим исходные данные Готовим исходные данные

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

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

Линейчатая гистограмма с накоплением Линейчатая гистограмма с накоплением

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

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

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

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

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

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

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

Первый ряд Первый ряд

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

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

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

  • Правой кнопкой мыши нажмите на вертикальную ось с фамилиями сотрудников.
  • В открывшемся списке выберите «Формат оси».
  • В появившемся меню поставьте галочку напротив «Обратный порядок категорий».

Корректировки во внешний вид Корректировки во внешний вид

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

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

Ряд с количеством дней отпуска по сотрудникам Ряд с количеством дней отпуска по сотрудникам

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

Кадровые документы отнимают много времени! Зачем заниматься этим самостоятельно, если специалисты «Моё дело» готовы всё делать за вас. Выгоднее, чем штатные сотрудники, и остаётся время на бизнес.

Узнать подробнее

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

  • Нажмите левой кнопкой мыши на любую дату, чтобы вокруг горизонтальной оси появилась обводка.
  • Кликните правой кнопкой мыши, в открывшемся списке выберите «Формат оси».
  • Раскройте вкладку «Параметры оси». Работаем со строками «Минимум» и «Максимум» в подразделе «Границы».

Горизонтальная ось Горизонтальная ось

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

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

  • В двух свободных ячейках напишите: 01.01.2024 и 31.12.2024.
  • Выделите ячейку левой кнопкой мыши.
  • Сверху найдите блок с выбором формата ячейки, сейчас там указано «Дата».
  • Откройте список и выберите «Число».
  • Сделайте то же самое со второй ячейкой.

Числовой формат Числовой формат

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

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

Диаграмма Диаграмма

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

  • Откройте вкладку «Число».
  • Найдите строку «Тип».
  • Выберите значение, в котором указан только месяц. На горизонтальной оси отобразятся названия месяцев.

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

  • Нажмите левой кнопкой мыши на любой синий столбец.
  • Справа откроется меню. Выберите вкладку «Заливка и границы».
  • Во вкладке «Заливка» нажмите на «Нет заливки».
  • Во вкладке «Граница» нажмите «Нет линий».

Уберём синие столбцы Уберём синие столбцы

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

Не хватает времени, чтобы решать операционные бизнес-задачи? Автоматизируйте расчёт налогов, подготовку первички и формирование деклараций с сервисом «Моё дело». Тратьте 5-10 минут на то, что раньше занимало 2-3 часа.

Попробовать бесплатно

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

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

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

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

Добавляем второй отпуск на диаграмму (1) Добавляем второй отпуск на диаграмму (1)

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

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

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

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

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

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

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

Составьте правильный график отпусков с экспертами. Специалисты «Моё дело» помогут спланировать отдых сотрудников в соответствии с актуальным законодательством.

Узнать подробнее

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

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

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

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

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

Добавление сетки Добавление сетки

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

Итоговый график Итоговый график

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

Скачать образец графика отпусков в Excel
doc

Скачать doc

xls

Скачать xls

pdf

Скачать pdf

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

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

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

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

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

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

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

Узнать подробнее

Подпишитесь на новостную рассылку

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

Спасибо! Подтвердите подписку на почте.