Не делать ненужную работу и все что можно автоматизировать для меня жизненный принцип. В данной статье хочу поделится опытом создания 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-й строки).
На этом все. Благодарю за внимание
Комментарии (32)
maxtm
03.12.2016 08:19+7Зачем тут мануал по экселю на хабре?
Basych
03.12.2016 08:26+7Я работаю в IT компании и мне приходится выполнять эту функцию (составлять и контролировать график отпусков). Опубликовал потому, что считаю опыт универсальным и ценным. Интерес к данной статье я вижу.
Zagrebelion
03.12.2016 11:25+1Можете выложить xlsx файл с примером?
Zverienish
03.12.2016 13:46В 1С ЗУП же есть средства для ведения графиков отпусков. Если конечно организация пользуется ей. А кадровики сидят потом перевносят эти данные.
Basych
03.12.2016 13:48Вот они у нас и «перевнесли» это все на рук. отделов. 1С я не располагаю — делаю чем могу
Basych
03.12.2016 13:50В начале статьи есть ссылка — я выложил на Гугл Диске и расшарил. Не работает? Как то иначе выложить?
YouHim
03.12.2016 19:01Как можно распланировать свой отпуск на год вперед?
vasyzpetya
03.12.2016 21:24— никак. :) но можно «предположить» хотя бы по кварталам, это нужно для бюджета.
igorxut
03.12.2016 21:24+3В нашем отделе мы пользуемся таким шаблоном для дальнейшей передачи в отдел кадров.
Шаблон подготавливается, затем к нему открывается доступ всем сотрудникам на редактирование. После того как все заполнят, начальник анализирует список, и, если требуется, то совместно с сотрудниками меняет даты в проблемные периоды, после чего блокирует доступ на редактирование, тем самым фиксируя график отпусков.
Для заполнения и анализа встроены дополнительные "плюшки" (день недели "с" и "по", чтобы выбирать период так, чтобы начало или конец не выпадали на выходной).
Обратите внимание и на другие листы книги.
dohlik
03.12.2016 22:42Жирный плюс в карму! :) намного более современное решение, чем у ТС
Basych
04.12.2016 11:46Ну я бы сказал — более продвинутое (технология тут та же), но я решал только свою задачу и занимался этим хотя и на работе, но в инициативном порядке (в условиях внедренного у нас поминутного учета рабочего времени и это непросто :). Я также плюсую данное решение.
dohlik
04.12.2016 12:42Я даже для своих собственных нужд на автомате создаю Гугл-документ. Просто потому, что высока вероятность появления необходимости его показать или начать совместное редактирование. Excel не открывал уже наверное года два.
Ну и сотрудников в компании потихоньку пересаживаем на ГДокс, очень удобно.
badnew5
04.12.2016 11:46Отличный шаблон.
Но есть небольшое замечание: неправильно работают формулы в столбцах от Q до W
Формулы скорее всего скопированы со столбца P предварительно не зафиксировав диапазоны enum_holidays!A:A и enum_holidays!A:B.
В таких случаях очень удобно «обозвать» нужные диапазоны понятным именемнапример«_enum_holidays» — это enum_holidays!$A2:$A27,
«_tbl_enum_holidays» – это enum_holidays!$A2:$B27,
«_tbl_enum_daysweek» – это enum_daysweek!$A$1:$B$8igorxut
04.12.2016 17:33Да, во время процесса "обезличивания" шаблона я ошибся с формулами.
Ну а вообще, использование именованных диапазонов это действительно лучшее решение.
Спасибо, шаблон модифицировал.
Boomburum
04.12.2016 01:15А можно задачку «со звёздочкой» подкинуть? ) Такую: есть 20 человек из группы А и 100 человек из группы Б. Нужно, чтобы каждый человек из группы Б пообщался 15 минут с каждым человеком из группы А. Как проще всего организовать подобный шаблон-шахматку, чтобы было понятно (а главное — наглядно) понятно, кому в какое время время и к кому подходить?
dohlik
04.12.2016 12:45А что на выходе из общения должно быть? Если сам факт проведения 15минутки, то что мешает просто сделать матрицу 100x20 в Гугл-таблице?
Basych
04.12.2016 15:17Интересно:
1. 20*100*15 = 30000 минут чистого общения :) (Я правильно понял Вас? Это правда нужно? Может лучше анкеты раздать?)
2. Какие тут еще ограничения? Например:
Ограничения на распараллеливание:
— сколько есть мест где они могут разговаривать?
— сколько человек из групп А и Б единовременно могут общаться (без ущерба для работы всей группы)?
Ограничения на время:
— у каждого свое собственное расписание дел или они готовы отложить все дела и общаться?
— есть только определенное время суток когда это общение возможно (рабочее время)?
без этих вещей задача нереалистичная
…
Далее решение в составлении расписаний каждого участника, расписаний каждого места и поиске возможных совпадений по времени. Потом уже на базе этих возможностей составляем расписание. Как сделать в Экселе пока не представляю.
AstorS1
Здорово, обязательно воспользуюсь!
Разве что, формулировку «Требуемый кадровиками формат...» я бы заменил на «требования федерального законодательства».