Как создать график отпусков в excel пошаговая инструкция

Время на прочтение
3 мин

Количество просмотров 239K

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

Не делать ненужную работу и все что можно автоматизировать для меня жизненный принцип. В данной статье хочу поделится опытом создания MS EXCEL файла графика. Возможно полученный шаблон или данный опыт окажется полезным и вам.

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

Для интересующихся конструкцией нижеследующее описание.

Возникновение задачи

Итак. Требуемый кадровиками формат изображен на картинке ниже(все фамилии и должности вымышленные):

Особенности данного формата:

1. В таблицу отдельными строками включаются отдельные периоды отпусков
2. В таблице указываются даты начала отпуска и продолжительность
3. Список упорядочен по алфавиту фамилий сотрудников и по возрастанию дат начала

График — это график

График отпусков моих сотрудников нужен и мне, но я бы хотел иметь его в виде наглядного календарного графика (диаграммы), где вдоль оси времени отражаются периоды отпусков сотрудников. И я его в итоге и сделал — вот таким:

Как это сделано

Для создания графика такой формы я использовал встроенный в MS EXCEL конструктор диаграмм и тип диаграммы «Линейчатая с накоплением».

Для того чтобы горизонтальная ось диаграммы имела вид шкалы времени нужны следующие настройки:

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

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

На самом деле диаграмма отражает не только периоды отпусков, но и разрывы между ними (настройками выполнено отображение отпусков зеленым цветом заливки, а разрывов — без заливки то есть прозрачными).

Первый ряд

Это отображаемый прозрачным период от начала времен до даты начала первого отпуска в году. Используется значение в столбце «Начало1».

Второй ряд

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

У меня столбец «Дней 1» рассчитывается по формуле:

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

Третий ряд

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

Также считается формулой, а так как данной значение не имеет пользовательской ценности — столбец в таблице максимально сужен.

Последующие ряды

Тут хочется просто сказать «ну и так далее…», в общем зеленые периоды отпусков строятся аналогично ряду 2, а прозрачные промежутки между ними — аналогично ряду 3. Для моей задачи хватило 5-ти периодов — это текущее ограничение шаблона, которое можно преодолеть, продолжая таблицу в ширину (насколько у вас хватит терпения).

А как быть с кадровиками?

Им же нужен просто список!?

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

Такими ссылками заполнены в каждой строке ячейки с В по Е. Для каждой строки из исходной таблицы (каждого сотрудника) созданы соответственно числу возможных отпускных периодов — 5 строк в данной таблице. Например поле E «Кол-во календ. дней», для первого сотрудника заполнено:

1-я строка — «=График!G5»
2-я строка — «=График!K5»
3-я строка — «=График!O5»
4-я строка — «=График!S5»
5-я строка — «=График!W5»

Для следующего сотрудника ссылки будут на те же столбцы и на следующую строку (это довольно трудоемко было заполнять из за того, что форма транспонирована, а как копировать формулы с транспонированием я не разобрался).

Обратите внимание, что в столбце Е стоит фильтр. Он нужен для того, что бы выводить только заполненные периоды отпусков (настроено не выводить 0).

Еще осталось автоматизировать нумерацию строк (первый столбец). В первой строке руками проставлена цифра «1», для остальных использую формулу «=A6+ЕСЛИ(E7=0;0;1)» (на примере 2-й строки).

На этом все. Благодарю за внимание

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

Шаг 1: Создание таблицы данных

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

Шаг 2: Форматирование данных

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

Шаг 3: Создание графика отпусков

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

Шаг 4: Настройка осей и меток

После создания графика отпусков вы можете настроить оси и метки для более удобного и понятного отображения. Например, вы можете изменить масштаб оси X для отображения определенного периода времени или добавить метки на ось Y с именами сотрудников.

Шаг 5: Добавление дополнительных функций

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

Шаг 6: Обновление данных

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

Заключение

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

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

Решение задачи

В нашем примере будет 5 человек. В отпуск они идут два раза за лето, по 2 недели подряд. Начнем с классической таблицы. Конец периода окончания по формуле: начало плюс период продолжительность отпуска, в дни.

Теперь строим стол по неделям. Она будет похожа на диаграмму ганта — окрашенные волокна, длина которых зависит от длительности отпуска. Я построил ее справа от таблицы.

Для начала построим колонки недели. В первые две недели ставим начало недели: 14.06, 21.06. Далее протягиваем мышкой, используя автозаполнение.

Теперь строим саму диаграмму. Вот формула, которую нужно напечатать в ячейке (3, 8):

=ЕСЛИ(ИЛИ(И(R2C>=RC2;R2C=RC5;R2C

Расшифрую:

Если дата начала недели сокращается в первый или второй период отпуска, ставится цифра 1 иначе — 0. Обратите внимание, что фиксируются только номера колонок. Это необходимо для протягивания пормулы по всем ячейкам терминалов. Получилась таблица из 1 и 0. Выделяем ее и накладываем Условное форматирование.

Получается вот такая картина.

Добавим последний штрих — контроль показа отпусков. Для этого в нижней строке суммируем числовые значения 1 в столбце. Обычной формулой СУММА(). А потом на эту статью тоже накладываем Условное форматирование. Только в этот раз нам нужно покрасить красные значения, большие 3. Они полагают неделям, когда на работе остается меньше 2-х человек.

Все, график готов!

На построение потрачено не больше 10 минут, а сэкономлены часы труда. Буду использовать этот график и в следующем году. Теперь на его составление будет потрачено всего 2 минуты: даты под редактирование и новые границы отвусвода

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