Шаблон графіка відпусток (або графіка навчання або іншого графіка) в 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-го рядка).

На цьому все.
Дякую за увагу
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.