Календарь этапов проекта

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

Заготовка

Начнем с заготовки:

project-calendar1.png

Как видите, тут все просто:

  • По строчкам - месяцы, по столбцам - дни.
  • В ячейке А2 лежит год, для которого строится календарь. В ячейках A4:A15 - вспомогательные номера месяцев. И то и другое понадобится нам чуть позже для формирования дат в календаре.
  • Справа от таблицы размещены названия этапов с датами начала и окончания. Можно предусмотреть заранее пустые ячейки для новых этапов, добавляемых в будущем.

Заполняем календарь датами и прячем их

Теперь давайте заполним наш календарь датами. Выделим ячейку первую ячейку C4 и введем туда функцию ДАТА (DATE), которая формирует дату из номера года, месяца и дня:

project-calendar2.png

После ввода формулы, ее надо скопировать на весь диапазон c 1 января до 31 декабря (C4:AG15). Поскольку ячейки узкие, то вместо созданных дат мы увидим решетки (#). Тем не менее, при наведении мыши на любую такую ячейку во всплывающей подсказке можно увидеть ее реальное содержимое:

project-calendar3.png

Чтобы решетки нам не мешали, их можно скрыть с помощью хитрого пользовательского формата. Для этого выделите все даты, откройте окно Формат ячейки и на вкладке Число (Number) выберите вариант Все форматы (Custom). Затем в поле Тип введите подряд три точки с запятой (без пробелов!) и нажмите ОК. Содержимое ячеек будет скрыто и решетки пропадут, хотя даты в ячейках, на самом деле, останутся - это только видимость.

project-calendar4.png

Подсветка этапов

Теперь с помощью условного форматирования добавим подсветку этапов к ячейкам со скрытыми датами. Выделяем все даты в диапазоне C4:AG15 и выбираем на вкладке Главная - Условное форматирование - Создать правило (Home - Conditional formatting - Create Rule). В открывшемся окне выбираем вариант Использовать формулу для определения форматируемых ячеек (Use formula to defer which cells to format) и вводим формулу:

project-calendar5.png

Эта формула проверяет каждую ячейку с датой, начиная с С4 и до конца года на предмет попадания в интервал между началом и концом каждого этапа. Единичка на выходе будет получаться только тогда, когда оба проверяемых условия в скобках (C4>=$AJ$4:$AJ$13) и (C4<=$AK$4:$AK$13) будут выдавать логическую ИСТИНУ, которую Excel интерпретирует как 1 (ну, а ЛОЖЬ - как 0, естественно). Также обратите особое внимание на то, что ссылки на начальную ячейку С4 относительные (без $), а на диапазоны этапов - абсолютные (с двумя $).

После нажатия на ОК мы увидим этапы в нашем календаре:

project-calendar6.png

Подсветка пересечений

Если даты некоторых этапов пересекаются (внимательные читатели, должно быть, уже заметили этот момент для 1-го и 6-го этапов!), то лучше бы подсветить этот конфликт в нашем графике другим цветом с помощью еще одного правила условного форматирования. Оно, практически, один-в-один похоже на предыдущее за исключением того, что мы ищем ячейки, которые входят больше, чем в один этап:

project-calendar7.png

После нажатия на ОК такое правило наглядно подсветит перехлест дат в нашем календаре:

project-calendar8.png

Убираем лишние дни в месяцах

Само-собой, не во всех месяцах по 31 дню, поэтому лишние дни февраля, апреля, июня и т.д. неплохо бы визуально отметить, как неактуальные. Функция ДАТА, формирующая наш календарь, в таких ячейках будет автоматически переводить дату в следующий месяц, т.е. "30 февраля" 2016 г. превратиться в 1 марта. То есть номер месяца для таких лишних ячеек будет не равен номеру месяца в столбце А. Этим и можно воспользоваться при создании правила условного форматирования для выделения таких ячеек:

project-calendar9.png

Добавляем выходные

По желанию можно добавить на наш календарь и выходные. Для этого можно воспользоваться функцией ДЕНЬНЕД (WEEKDAY), которая будет вычислять номер дня недели (1-пн, 2-вт...7-вс) для каждой даты и подсвечивать те из них, что попадают на субботы и воскресенья:

project-calendar10.png

Для корректного отображения не забудьте правильно настроить правильный порядок следования друг за другом правил в окне Главная - Условное форматирование - Управление правилами (Home - Conditional formatting - Manage Rules), т.к. правила и заливки работают именно в той логической последовательности, которую вы сформируете в этом диалоге:

project-calendar11.png

Ссылки по теме





22.02.2016 19:47:33
По опыту прошлых уроков...
Всем желающим в 100500-й раз мне сказать, что лучше все это делать не в Excel, а в Project - я в курсе :)
Project - вещь хорошая.
Но не у всех он установлен, во-первых.
А во-вторых, покупать Project, если вам нужен только календарь проекта - это как покупать целую свинью ради порции холодца.
Если вы эту свинью, конечно, честно покупаете, а не скачиваете с торрентов :)
23.02.2016 13:04:32
Очень полезное решение, спасибо!
Николай, можно ли как-то сделать сплывающее меню или другим образом уведомлять юзера когда курсор мышки наведен на зеленые (проектные) дни? Тогда бы ему давали информацию об имени Этапа, а в случае употребления данного примера для отпусков - фамилию сотрудника.
Иначе чуточку не информативно.
23.02.2016 15:30:02
Спасибо!
23.02.2016 16:24:33
Всегда пожалуйста ;)
24.02.2016 09:38:38
Отлично !!! Как всегда на высоте!!!! Спасибо Николай!!!
Возможно еще добавить выпадающий список , к примеру разделить обязанности сроки по отделам и датам.
Буду признателен!!!!
28.02.2016 22:43:43
Спасибо, Николай! Не буду покупать . Можно еще такой штукой пользоваться.
21.03.2016 20:55:04
а как каждый этап окрасить в разный цвет?
22.03.2016 12:55:17
Для того, чтобы окрасить каждый этап в свой цвет, я делала отдельное условное форматирование для каждого этапа. Немного изменяем формулу Николая :
=СУММПРОИЗВ((C4>=$AJ$4)*(C4<=$AK$4))=1 для первого этапа, цвет зеленый например.

=СУММПРОИЗВ((C4>=$AJ$5)*(C4<=$AK$5))=1 для второго, цвет желтый и и.д.

Также обнаружила еще одну приятную вещь - не обязательно заполнять календарик формулами, можно прямо в усл. форматировании использовать вместо ссылки С4 формулу ДАТА($А$2;$А4;С$3). Получиться примерно так: =СУММПРОИЗВ((ДАТА($А$2;$А4;С$3)>=$AJ$4)*(ДАТА($А$2;$А4;С$3)<=$AK$4))=1
Форматирование все равно будет работать в пределах введенного нами периода, а мы получаем чистый от формул календарик, для ввода других данных, например отметок о прогрессе выполнения проекта))
21.04.2016 19:02:21
А не подскажете формулы, чтоб:
- подсвечивалось, когда дата отгрузки(в даном случае окончания проекта) сегодня;
- когда дата отгрузки уже прошла сегодняшнюю дату, т.е. просрочено(в даному случае проект просрочен или уже не активен).
Я в курсе, за видеоурок Николая по Датам отгрузки, но там не совсем актуально для календаря. Спасибо.
22.04.2016 14:13:09
Уже не актуально. Сам разобрался.
13.05.2016 10:01:17
Добрый день. Подскажите как будет выглядеть формула, если в заголовке по горизонтали вместо дат будет время с 00:00:00 до 00:00:00 с интервалом 00:15:00 ( 00:00:00, 00:15:00, ... 23:45:00, 00:00:00), а по вертикали вместо месяцев будут даты с днями недели ( Пн, 09.05.2016, Вт, 10.05.2016, ... Вс, 15.05.2016)? Делаю недельный график. Заранее спасибо.
25.07.2016 07:24:49
Николай, не пойму почему у меня не срабатывает условное форматирование. Делаю все по инструкции. А скачиваю Ваш файл с примером - все работает.
Но эта проблема возникает не только конкретно с данным примером. Пытаюсь в других своих файлах ввести условное форматирование, например, для подсветки предстоящих событий по датам, иногда работает, а иногда - нет. Иногда в одном столбце срабатывает правило, а в соседнем другое правило не срабатывает. Эта проблема связана с моей версией эксель или с кривыми руками? У меня стоит лицензированная версия Microsoft office 2007
12.10.2016 00:39:49
Спасибо за материал :)