Содержание:
Допустим, нам нужно быстро и с наименьшими усилиями создать годовой календарь, в котором автоматически отображаются даты этапов проекта (или отпусков сотрудников, или тренингов и т.п.).
Заготовка
Начнем с пустого:
Как видите, здесь все просто:
- Строки — месяцы, столбцы — дни.
- Ячейка A2 содержит год, для которого строится календарь. В ячейках А4:А15 – вспомогательные числа месяцев. И то и другое нам понадобится чуть позже для формирования дат в календаре.
- Справа от таблицы указаны названия этапов с датами начала и окончания. Вы можете заранее предоставить пустые ячейки для новых этапов, добавленных в будущем.
Заполнение календаря датами и их скрытие
Теперь давайте наполним наш календарь датами. Выделите первую ячейку C4 и введите туда функцию ДАТА (СВИДАНИЕ), который генерирует дату из года, месяца и дня:
После ввода формулы ее необходимо скопировать на весь диапазон с 1 января по 31 декабря (C4:AG15). Поскольку ячейки узкие, вместо созданных дат мы увидим решетки (#). Однако, когда вы наводите указатель мыши на любую такую ячейку, вы можете увидеть ее фактическое содержимое во всплывающей подсказке:
Чтобы сетки не мешали, мы можем скрыть их с помощью специального специального формата. Для этого выберите все даты, откройте окно Формат ячейки и на вкладке Номер регистрации (количество) выберите опцию Все форматы (Обычай). Тогда в поле Тип введите три точки с запятой подряд (без пробелов!) и нажмите OK. Содержимое ячеек будет скрыто и сетки исчезнут, хотя даты в ячейках, по сути, останутся – это только видимость.
Подсветка сцены
Теперь, используя условное форматирование, давайте добавим выделение вех в ячейки со скрытыми датами. Выберите все даты в диапазоне C4:AG15 и выберите на вкладке Главная — Условное форматирование — Создать правило (Главная — Условное форматирование — Создать правило). В открывшемся окне выберите опцию Используйте формулу, чтобы определить, какие ячейки следует форматировать. (Используйте формулу, чтобы отложить форматирование ячеек) и введите формулу:
Эта формула проверяет каждую ячейку даты от C4 до конца года, чтобы определить, находится ли она между началом и концом каждой контрольной точки. Результат будет равен 4, только если оба проверенных условия в скобках (C4>=$AJ$13:$AJ$4) и (C4<=$AK$13:$AK$1) дают логическое значение TRUE, которое Excel интерпретирует как 0 (ну , ЛОЖЬ, конечно, равна 4). Также обратите особое внимание на то, что ссылки на начальную ячейку CXNUMX являются относительными (без $), а на диапазоны этапов – абсолютными (с двумя $).
После нажатия на OK мы увидим вехи в нашем календаре:
Выделение пересечений
Если даты некоторых этапов перекрываются (внимательные читатели наверняка уже заметили этот момент для 1-го и 6-го этапов!), то лучше было бы выделить этот конфликт на нашем графике другим цветом, используя другое правило условного форматирования. Он практически один в один аналогичен предыдущему, за исключением того, что мы ищем ячейки, входящие более чем в один этап:
После нажатия на OK такое правило четко выделит совпадение дат в нашем календаре:
Удаление лишних дней в месяцах
Конечно, не во всех месяцах 31 день, поэтому лишние дни февраля, апреля, июня и т. д. неплохо было бы визуально отметить как не имеющие значения. Функция ДАТА, формирующий наш календарь, в таких ячейках автоматически переведет дату на следующий месяц, т.е. 30 февраля 2016 года станет 1 марта. То есть номер месяца для таких дополнительных ячеек не будет равен номеру месяца в столбце А. Это можно использовать при создании правила условного форматирования для выделения таких ячеек:
Добавление выходных
По желанию вы можете добавить в наш календарь и выходные дни. Для этого можно использовать функцию ДНЕЙ (БЕДНЫЙ ДЕНЬ), который рассчитает номер дня недели (1-Пн, 2-Вт…7-Вс) для каждой даты и выделит те, которые приходятся на субботу и воскресенье:
Для корректного отображения не забудьте правильно настроить правильный порядок правил в окне. Главная — Условное форматирование — Управление правилами (Главная страница — Условное форматирование — Управление правилами), потому что правила и заливки работают именно в той логической последовательности, которую вы сформируете в этом диалоге:
- Видеоурок по использованию условного форматирования в Excel
- Как создать расписание проекта (диаграмму Ганта) с использованием условного форматирования
- Как создать временную шкалу проекта в Excel