Каждое лето приходится согласовывать график отпусков отдела. У каждого свои планы на отдых, но работа должна идти своей чередой. Поэтому решено было, что каждую неделю в отделе должно быть минимум 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 читайте нашу статью про функцию ВПР. Она помогает менеджерам экономить часы при анализе данных за разные периоды командировок.