Календарь этапов проекта
Предположим, что нам необходимо оперативно и с наименьшими усилиями создать годовой календарь, на котором автоматически отобразить даты этапов проекта (или отпуска сотрудников, или тренинги и т.п.)
Начнем с заготовки:
По строчкам - месяцы, по столбцам - дни.
В ячейке А2 лежит год, для которого строится календарь. В ячейках A4:A15 - вспомогательные номера месяцев. И то и другое понадобится нам чуть позже для формирования дат в календаре.
Справа от таблицы размещены названия этапов с датами начала и окончания. Можно предусмотреть заранее пустые ячейки для новых этапов, добавляемых в будущем.
Заполняем календарь датами и прячем их
Теперь давайте заполним наш календарь датами. Выделим ячейку первую ячейку C4 и введем туда функцию ДАТА, которая формирует дату из номера года, месяца и дня:
После ввода формулы, ее надо скопировать на весь диапазон c 1 января до 31 декабря (C4:AG15). Поскольку ячейки узкие, то вместо созданных дат мы увидим решетки (#). Тем не менее, при наведении мыши на любую такую ячейку во всплывающей подсказке можно увидеть ее реальное содержимое:
Чтобы решетки нам не мешали, их можно скрыть с помощью пользовательского формата. Для этого выделите все даты, откройте окно Формат ячейки и на вкладке Число выберите вариант Все форматы. Затем в поле Тип введите подряд три точки с запятой (без пробелов!) и нажмите ОК. Содержимое ячеек будет скрыто и решетки пропадут, хотя даты в ячейках, на самом деле, останутся - это только видимость.
Подсветка этапов
Теперь с помощью условного форматирования добавим подсветку этапов к ячейкам со скрытыми датами. Выделяем все даты в диапазоне C4:AG15 и выбираем на вкладке Главная - Условное форматирование - Создать правило. В открывшемся окне выбираем вариант Использовать формулу для определения форматируемых ячеек и вводим формулу:
Эта формула
=СУММПРОИЗВ((C4>=$AJ$4:$AJ$13)*(C4<=$AJK$4:$AK$13))=1
проверяет каждую ячейку с датой, начиная с С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-вс) для каждой даты и подсвечивать те из них, что попадают на субботы и воскресенья:
Для корректного отображения не забудьте правильно настроить правильный порядок следования друг за другом правил в окне Главная – Условное форматирование - Управление правилами, т.к. правила и заливки работают именно в той логической последовательности, которую вы сформируете в этом диалоге:
Временная шкала проекта
Предположим, мы работаем над долгим и сложным проектом, состоящим из нескольких этапов. Задача – наглядно показать всю хронологию работ по проекту, расположив ключевые моменты проекта (вехи, milestones) на оси времени. Примерно вот так:
В теории управления проектами подобный график обычно называют календарем или временной шкалой проекта (project timeline), хотя я также встречал еще один русскоязычный аналог -"лента времени". В любом случае, главное - не как назвать, а как построить.
Шаг 1. Исходные данные
Для построения нам потребуется оформить исходную информацию по вехам проекта в виде следующей таблицы:
Обратите внимание на два дополнительных служебных столбца:
Линия - столбец с одинаковой константой около нуля по всем ячейкам. Даст на графике горизонтальную линию, параллельную оси Х, на которой будут видны узлы - вехи проекта. В принципе, можно было бы использовать и полный ноль, но тогда график совпадает с осью X, что дает проблемы потом с настройкой внешнего вида диаграммы в Excel 2007-2010. Новый Excel 2013 нули воспринимает спокойно.
Выноски - невидимые столбцы для поднятия подписей к вехам на заданную (разную) величину, чтобы подписи не накладывались. Значения 1,2,3 и т.д. задают уровень поднятия подписей над осью времени и выбираются произвольно.
Шаг 2. Строим основу
Теперь выделяем в таблице все, кроме первого столбца (т.е. диапазон B1:D13 в нашем примере) и строим обычный плоский график с маркерами на вкладке Вставка - График - График с маркерами
Убираем линии сетки, вертикальную и горизонтальную шкалы и легенду. Сделать это можно вручную (выделение мышью и клавиша Delete) или отключив ненужные элементы на вкладке Макет. В итоге должно получиться следующее:
Теперь выделите ряд Выноски (т.е. ломаную оранжевую линию) и на вкладке Макет выберите команду Линии – Линии проекции
От каждой точки верхнего графика будет опущен перпендикуляр на нижний. В новом Excel 2013 эта опция находится на вкладке Конструктор - Добавить элемент диаграммы.
Шаг 3. Добавляем названия этапов
Эта часть будет простой у тех, кто уже осмелился на установку нового Excel 2013 и более сложной у тех, кто еще работает со старыми версиями. В Excel 2013 все просто. Он умеет делать подписи к точкам данных просто беря текст из любого заданного пользователем диапазона. Для этого нужно выделить ряд с данными (оранжевый) и на вкладке Конструктор выбрать Добавить элемент диаграммы - Подписи - Дополнительные параметры, а затем в появившейся справа панели установить флажок Значения из ячеек и выделить диапазон A2:A13:
В версиях Excel 2007-2010 и старше такой возможности нет, но у вас есть вариант: Добавьте любые подписи к оранжевому графику (значения, например). Затем выделяйте по очереди каждую подпись, ставьте в строке формул знак "равно" и щелкайте по ячейке с названием этапа из столбца А. Текст
выделенной подписи будет автоматически браться из выделенной ячейки:
Шаг 4. Прячем линии и наводим блеск
Внесем последние правки, чтобы довести нашу уже почти готовую диаграмму до полного и окончательного шедевра:
Выделяем ряд Выноски (оранжевую линию), щелкаем по ней правой кнопкой мыши и выбираем Формат ряда данных. В открывшемся окне убираем заливку и цвет линий. Оранжевый график, фактически, исчезает из диаграммы - остаются только подписи. Что и требуется. Добавляем подписи-даты к синей оси времени на вкладке Макет - Подписи данных – Дополнительные параметры подписей данных - Имена категорий. В этом же диалоговом окне подписи можно расположить под графиком и развернуть на 90 градусов, при желании.