Шаблон графика отпусков (или графика обучения или иного графика) в MS Excel файле

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

    Не делать ненужную работу и все что можно автоматизировать для меня жизненный принцип. В данной статье хочу поделится опытом создания 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-й строки).

    На этом все. Благодарю за внимание
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 32

      0
      Здорово, обязательно воспользуюсь!
      Разве что, формулировку «Требуемый кадровиками формат...» я бы заменил на «требования федерального законодательства».
        0
        Почему было бы не написать небольшой макрос, который автоматизировал бы данное действие?
          0
          Честно говоря не силен в макросах, а в данном случае все работает и без них
          +7
          Зачем тут мануал по экселю на хабре?
            +7
            Я работаю в IT компании и мне приходится выполнять эту функцию (составлять и контролировать график отпусков). Опубликовал потому, что считаю опыт универсальным и ценным. Интерес к данной статье я вижу.
              0
              А зачем статьи по Mathemathica и 1С на хабре?
                0

                мне тоже интересно. Как и по Excel.


                Вот вы сравните статьи давностью порядка пяти лет и текущие.


                Деградация на лицо :(

                  0
                  Деградация на лицо :(

                  в том числе и в письменной речи
                    0

                    Прошу пардону, был «напуган».
                    // случайно вышло

            0

            А в R есть пакет для подготовки таких графиков?

            0
            Работает же)) это главное)
              +1
              Можете выложить xlsx файл с примером?
                0
                В 1С ЗУП же есть средства для ведения графиков отпусков. Если конечно организация пользуется ей. А кадровики сидят потом перевносят эти данные.
                  0
                  Вот они у нас и «перевнесли» это все на рук. отделов. 1С я не располагаю — делаю чем могу
                  0
                  В начале статьи есть ссылка — я выложил на Гугл Диске и расшарил. Не работает? Как то иначе выложить?
                  0
                  Как можно распланировать свой отпуск на год вперед?
                    0
                    — никак. :) но можно «предположить» хотя бы по кварталам, это нужно для бюджета.
                    +3

                    В нашем отделе мы пользуемся таким шаблоном для дальнейшей передачи в отдел кадров.
                    Шаблон подготавливается, затем к нему открывается доступ всем сотрудникам на редактирование. После того как все заполнят, начальник анализирует список, и, если требуется, то совместно с сотрудниками меняет даты в проблемные периоды, после чего блокирует доступ на редактирование, тем самым фиксируя график отпусков.
                    Для заполнения и анализа встроены дополнительные "плюшки" (день недели "с" и "по", чтобы выбирать период так, чтобы начало или конец не выпадали на выходной).
                    Обратите внимание и на другие листы книги.

                      0
                      Вау! Круто…
                        0
                        Жирный плюс в карму! :) намного более современное решение, чем у ТС
                          0
                          Ну я бы сказал — более продвинутое (технология тут та же), но я решал только свою задачу и занимался этим хотя и на работе, но в инициативном порядке (в условиях внедренного у нас поминутного учета рабочего времени и это непросто :). Я также плюсую данное решение.
                            0
                            Я даже для своих собственных нужд на автомате создаю Гугл-документ. Просто потому, что высока вероятность появления необходимости его показать или начать совместное редактирование. Excel не открывал уже наверное года два.

                            Ну и сотрудников в компании потихоньку пересаживаем на ГДокс, очень удобно.
                          0
                          У Рождества на листе enum_holidays дата некорректна.
                            0

                            Спасибо, поправил.

                            0
                            Отличный шаблон.

                            Но есть небольшое замечание: неправильно работают формулы в столбцах от 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$8

                            тогда не возникает проблем с копированием, и формулы становятся короче, а главное намного удобнее
                            читаются/изменяются
                            =ЕСЛИ(E2="";""; ЕСЛИ(СЧЁТЕСЛИМН(_enum_holidays;E2)>0; ВПР(E2; _tbl_enum_holidays;2; ЛОЖЬ); ВПР(ДЕНЬНЕД(E2;2);_tbl_enum_daysweek;2; ЛОЖЬ)))

                              0

                              Да, во время процесса "обезличивания" шаблона я ошибся с формулами.
                              Ну а вообще, использование именованных диапазонов это действительно лучшее решение.
                              Спасибо, шаблон модифицировал.

                            0
                            А можно задачку «со звёздочкой» подкинуть? ) Такую: есть 20 человек из группы А и 100 человек из группы Б. Нужно, чтобы каждый человек из группы Б пообщался 15 минут с каждым человеком из группы А. Как проще всего организовать подобный шаблон-шахматку, чтобы было понятно (а главное — наглядно) понятно, кому в какое время время и к кому подходить?
                              0
                              А что на выходе из общения должно быть? Если сам факт проведения 15минутки, то что мешает просто сделать матрицу 100x20 в Гугл-таблице?
                                0
                                Интересно:
                                1. 20*100*15 = 30000 минут чистого общения :) (Я правильно понял Вас? Это правда нужно? Может лучше анкеты раздать?)
                                2. Какие тут еще ограничения? Например:
                                Ограничения на распараллеливание:
                                — сколько есть мест где они могут разговаривать?
                                — сколько человек из групп А и Б единовременно могут общаться (без ущерба для работы всей группы)?
                                Ограничения на время:
                                — у каждого свое собственное расписание дел или они готовы отложить все дела и общаться?
                                — есть только определенное время суток когда это общение возможно (рабочее время)?
                                без этих вещей задача нереалистичная

                                Далее решение в составлении расписаний каждого участника, расписаний каждого места и поиске возможных совпадений по времени. Потом уже на базе этих возможностей составляем расписание. Как сделать в Экселе пока не представляю.
                                0
                                Спасибо!

                                Only users with full accounts can post comments. Log in, please.