Календарь этапов проекта
Предположим, что нам необходимо оперативно и с наименьшими усилиями создать годовой календарь, на котором автоматически отобразить даты этапов проекта (или отпуска сотрудников, или тренинги и т.п.)
Заготовка
Начнем с заготовки:
Как видите, тут все просто:
- По строчкам - месяцы, по столбцам - дни.
- В ячейке А2 лежит год, для которого строится календарь. В ячейках A4:A15 - вспомогательные номера месяцев. И то и другое понадобится нам чуть позже для формирования дат в календаре.
- Справа от таблицы размещены названия этапов с датами начала и окончания. Можно предусмотреть заранее пустые ячейки для новых этапов, добавляемых в будущем.
Заполняем календарь датами и прячем их
Теперь давайте заполним наш календарь датами. Выделим ячейку первую ячейку C4 и введем туда функцию ДАТА (DATE), которая формирует дату из номера года, месяца и дня:
После ввода формулы, ее надо скопировать на весь диапазон c 1 января до 31 декабря (C4:AG15). Поскольку ячейки узкие, то вместо созданных дат мы увидим решетки (#). Тем не менее, при наведении мыши на любую такую ячейку во всплывающей подсказке можно увидеть ее реальное содержимое:
Чтобы решетки нам не мешали, их можно скрыть с помощью хитрого пользовательского формата. Для этого выделите все даты, откройте окно Формат ячейки и на вкладке Число (Number) выберите вариант Все форматы (Custom). Затем в поле Тип введите подряд три точки с запятой (без пробелов!) и нажмите ОК. Содержимое ячеек будет скрыто и решетки пропадут, хотя даты в ячейках, на самом деле, останутся - это только видимость.
Подсветка этапов
Теперь с помощью условного форматирования добавим подсветку этапов к ячейкам со скрытыми датами. Выделяем все даты в диапазоне C4:AG15 и выбираем на вкладке Главная - Условное форматирование - Создать правило (Home - Conditional formatting - Create Rule). В открывшемся окне выбираем вариант Использовать формулу для определения форматируемых ячеек (Use formula to defer which cells to format) и вводим формулу:
Эта формула проверяет каждую ячейку с датой, начиная с С4 и до конца года на предмет попадания в интервал между началом и концом каждого этапа. Единичка на выходе будет получаться только тогда, когда оба проверяемых условия в скобках (C4>=$AJ$4:$AJ$13) и (C4<=$AK$4:$AK$13) будут выдавать логическую ИСТИНУ, которую Excel интерпретирует как 1 (ну, а ЛОЖЬ - как 0, естественно). Также обратите особое внимание на то, что ссылки на начальную ячейку С4 относительные (без $), а на диапазоны этапов - абсолютные (с двумя $).
После нажатия на ОК мы увидим этапы в нашем календаре:
Подсветка пересечений
Если даты некоторых этапов пересекаются (внимательные читатели, должно быть, уже заметили этот момент для 1-го и 6-го этапов!), то лучше бы подсветить этот конфликт в нашем графике другим цветом с помощью еще одного правила условного форматирования. Оно, практически, один-в-один похоже на предыдущее за исключением того, что мы ищем ячейки, которые входят больше, чем в один этап:
После нажатия на ОК такое правило наглядно подсветит перехлест дат в нашем календаре:
Убираем лишние дни в месяцах
Само-собой, не во всех месяцах по 31 дню, поэтому лишние дни февраля, апреля, июня и т.д. неплохо бы визуально отметить, как неактуальные. Функция ДАТА, формирующая наш календарь, в таких ячейках будет автоматически переводить дату в следующий месяц, т.е. "30 февраля" 2016 г. превратиться в 1 марта. То есть номер месяца для таких лишних ячеек будет не равен номеру месяца в столбце А. Этим и можно воспользоваться при создании правила условного форматирования для выделения таких ячеек:
Добавляем выходные
По желанию можно добавить на наш календарь и выходные. Для этого можно воспользоваться функцией ДЕНЬНЕД (WEEKDAY), которая будет вычислять номер дня недели (1-пн, 2-вт...7-вс) для каждой даты и подсвечивать те из них, что попадают на субботы и воскресенья:
Для корректного отображения не забудьте правильно настроить правильный порядок следования друг за другом правил в окне Главная - Условное форматирование - Управление правилами (Home - Conditional formatting - Manage Rules), т.к. правила и заливки работают именно в той логической последовательности, которую вы сформируете в этом диалоге:
Ссылки по теме
- Видеоурок по использованию условного форматирования в Excel
- Как создать график проекта (диаграмму Ганта) с помощью условного форматирования
- Как создать временную шкалу проекта (project timeline) в Excel
Всем желающим в 100500-й раз мне сказать, что лучше все это делать не в Excel, а в Project - я в курсе
Project - вещь хорошая.
Но не у всех он установлен, во-первых.
А во-вторых, покупать Project, если вам нужен только календарь проекта - это как покупать целую свинью ради порции холодца.
Если вы эту свинью, конечно, честно покупаете, а не скачиваете с торрентов
Николай, можно ли как-то сделать сплывающее меню или другим образом уведомлять юзера когда курсор мышки наведен на зеленые (проектные) дни? Тогда бы ему давали информацию об имени Этапа, а в случае употребления данного примера для отпусков - фамилию сотрудника.
Иначе чуточку не информативно.
Возможно еще добавить выпадающий список , к примеру разделить обязанности сроки по отделам и датам.
Буду признателен!!!!
=СУММПРОИЗВ((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
Форматирование все равно будет работать в пределах введенного нами периода, а мы получаем чистый от формул календарик, для ввода других данных, например отметок о прогрессе выполнения проекта))
- подсвечивалось, когда дата отгрузки(в даном случае окончания проекта) сегодня;
- когда дата отгрузки уже прошла сегодняшнюю дату, т.е. просрочено(в даному случае проект просрочен или уже не активен).
Я в курсе, за видеоурок Николая по Датам отгрузки, но там не совсем актуально для календаря. Спасибо.
Но эта проблема возникает не только конкретно с данным примером. Пытаюсь в других своих файлах ввести условное форматирование, например, для подсветки предстоящих событий по датам, иногда работает, а иногда - нет. Иногда в одном столбце срабатывает правило, а в соседнем другое правило не срабатывает. Эта проблема связана с моей версией эксель или с кривыми руками? У меня стоит лицензированная версия Microsoft office 2007
С их помощью кучу отдельных дат одним махаом отформатировал.
функции И и ИЛИ не помогали.
Но дальше шагнуть тяму не хватает:
Выодные должны высвечиваться только в периодах, и сами пероды тоже.
Спасибо.
Нашел ответ сам, посмотрев «Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ».
Там я увидел, куда надо втыкать доп. Условие.
И это здорово!
Функция СУММПРОИЗВ «безразмерная»!
Втыкай условий, сколько хочешь и перемножай их.
В итоге для высвечивания выходных в этапах:
=СУММПРОИЗВ((C4>=$AJ$$4$AJ$13)*(C4<=$AK$4:$Ak$13*(ДЕНЬНЕД(C4;2)>5))=1
Очень долго искал решение проблемы по всем разноязычным форумам.
Вчера попал на эту страницу – ГОТОВО!!!
СПАСИБО!
Подскажите пожалуйста как добавить подсветку определённых дат, которые будут выписаны в столбец АО