Создание плана-графика в excel

Создание плана-графика ExcelСоздание плана-графика в Excel.

При планировании мероприятий, особенно, если работа происходит в группе, важно иметь наглядный план-график.

Конечно, существуют программы, типа MS Project или, бесплатный,  OpenProj. Но гибкость, которая присуща Excel при планировании проекта, не сравнится ни с какой специализированной программой.

Да и требуется ли приобретать и учиться работе в новой программе, ради обычного составления графика Ганта? На мой взгляд, если ты не являешься профессиональным менеджером проекта, то и погружаться в дебри специализированных программ тебе совершенно ни к чему. Лучше потратить время на развитие в более близких твоему профилю областях.

Итак, допустим, нам необходимо спланировать и распределить по ответственным задачи по какому-либо проекту. В качестве образца возьмем один из проектов, план-график к которому мне довелось составлять.

Создание структуры таблицы

Мы имеем перечень мероприятий, ответственных по каждому мероприятию и сроки.

Для начала продумаем структуру таблицы. Помимо временной шкалы, она будет состоять из следующих колонок:

  1. Порядковый номер мероприятия;
  2. Наименование мероприятия;
  3. Ответственный исполнитель;
  4. Дата начала мероприятия;
  5. Здесь можно сделать дату окончания, но мне кажется компактнее и нагляднее использовать значение продолжительности мероприятия в днях. (берем календарные дни. Для более продвинутых, можно запрограммировать условие расчета по рабочим дням, но это отдельная тема.)
  6. Примечания — как бы полно мы ни называли задачу, все равно будут возникать нюансы, которые целесообразно писать в примечаниях.

Над таблицей предусмотрим две строки:

  • Наименование проекта;
  • Строка для отображения текущей даты.

Построение шкалы времени

Далее выстраиваем шкалу времени. У меня нет строгих требований к ее длине. Я, скорее, ориентируюсь на удобство восприятия. Обычно выделяю 30 колонок, и сужаю их размер, чтобы ячейки стали квадратными.

Над таблицей выделяем место для определения шага временной шкалы. Это понадобиться нам для того, чтобы гибко подходить к размерам страницы. Допустим, если проект будет общей продолжительностью три месяца, то сделав шаг в один день, мы слишком растянем временную шкалу, и нам потребуется не 30, а более 90 ячеек по горизонтали. Это сделает таблицу трудночитаемой. В подобных случаях можно сделать шаг, допустим, в три дня, или в одну неделю, и шкала окажется вполне компактной.

В строке заголовков будут даты. Увеличиваем высоту строки с датами, чтобы их значения, сориентированные вертикально, полностью помещались в ячейке. Теперь нужно задать ориентацию текста в этих ячейках. Выделяем все, которые мы хотим настроить, нажимаем правую кнопку мыши и в открывшемся окне выбираем пункт «Формат ячеек«. Открываем закладку «Выравнивание» и находим блок «Ориентация«. На мой взгляд, удобно будет, если мы установим ориентацию в 45 градусов. Так и сделаем.

Изменяемой будет только самое левое значение. Сюда мы вносим дату начала проекта. А в ячейку справа от нее вносим формулу =G3+$O$2, где второе слагаемое, это адрес ячейки с шагом временной шкалы. Далее просто протягиваем эту формулу — то есть выделяем ячейку с формулой, наводим курсор мыши на ее нижний правый угол (появиться черный крестик), зажимаем левую кнопку мыши, и ведем мышкой вправо до конца строки. Таким образом, формула будет скопирована автоматически.

Проверяем — внесем в ячейку с шагом временной шкалы число 2. Смотрим на шкалу — все даты должны отображаться через день — если в одной ячейке 1 января, то в следующей ячейке должно быть 3 января.

Настройка условного форматирования

Теперь самое интересное и полезное для плана-графика проекта — настройка условного форматирования. Нам нужно, чтобы на временной шкале по каждому мероприятию цветом отображался интервал с даты начала этого мероприятия до его окончания. Кроме того, на мой взгляд, полезным будет настроить что-то вроде вертикальной полоски, которая будет отображаться в колонке с текущей (сегодняшней) датой.

Самое сложное в этом — продумать формулу, которая будет являться условием окрашивания ячеек. Логически, все — просто. Должно быть окрашено то, что позднее или равно дате начала, но раньше или равно дате окончания. В Excel для этого используется функция «И», которая проверяет соответствие нескольким условиям.

условное форматированиеВыделяем все ячейки временной шкалы. Находим в меню Excel «Условное форматирование» и выбираем пункт «Создать правило«. Выбираем в открывшемся окне пункт «Использовать формулу для определения форматируемых ячеек«. В строку для формул вносим: =И(G$3>=$D4;G$3<=($D4+$E4)). Цвет заливки выберем, например, зеленый.формула шкалы

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

Формула выглядит так: =И($C$2>=G$3;$C$2<H$3). На этот раз цвет выбираем красный.

Итак, проверяем работоспособность таблицы. Заносим все данные, и смотрим, что у нас получилось. Как видим — все работает, а полученный шаблон можно использовать для любых других проектов.

Делитесь материалом со своими друзьями, нажав на кнопки ниже. Наверняка кому-то он будет полезен.

Усовершенствование плана-графика

Связанные задачи

Вопрос от читателя по имени Олег (см. комментарии ниже) натолкнул меня на усовершенствование файла. Действительно, почему бы не предусмотреть возможность связывания задач, когда дата начала последующей задачи будет начинаться на следующий день после предыдущей? Кроме того, было бы неплохо предусмотреть сигнализирование в случаях, когда происходит задержка выполнения той или иной задачи.

Что было сделано:

В прежней версии плана-графика дата старта проекта указывалась вначале колонки с датами. Удобнее было бы, если бы дата старта проекта указывалась в заголовке. Так и сделаем — добавим строку в заголовке, создадим там поле «Дата старта проекта«, а от той ячейки, куда мы раньше заносили дату, сделаем ссылку на это поле.

Как видно из рисунка, после колонки «Ответственный» добавлена колонка «№ предшествующей задачи«. Суть ее понятна — в нее будет вносится номер той задачи, к которой должна быть привязана текущая задача (после которой должно начинаться ее выполнение). А в ячейку первой строки колонки с датой начала занесем следующую формулу:

=ЕСЛИ(ЕПУСТО(D5);$C$3;ВПР(D5;$A$5:$F$21;5;ЛОЖЬ)+ВПР(D5;$A$5:$F$21;6;ЛОЖЬ)+1)

Протянем ее до нижней ячейки колонки. Суть формулы в том, что при отсутствии ссылки на предшествующую задачу будет отображена дата старта проекта. В случае, если номер предшествующей задачи будет указан, то будет указана дата этой задачи плюс значение ее продолжительности плюс 1 (на следующий день). Все, привязка выполнена. Теперь, если начнется аналогичный проект, достаточно будет сменить дату старта проекта, а все прочие даты выполнения задач сдвинутся автоматически.

Следующая задача — настроить индикацию задержек. Видов задержек может быть два:

  • задача выполнена, но с запозданием;
  • задача до сих пор не выполнена.

В первом случае в колонке «Дата фактического выполнения» (ее мы добавили к новой версии файла) будет внесена дата, позднее даты начала выполнения задачи плюс значение колонки «Продолжительность«. Во втором случае текущая дата будет позднее даты начала задачи плюс значение колонки «Продолжительность«, но в колонке «Дата фактического выполнения» ячейка будет пуста.

Предусмотрим для разных случаев различные сообщения:

  • Задержек нет — цвет заливки зеленый;
  • Задержка на … дней — надпись жирным красным шрифтом;
  • Срок выполнения задачи вышел — цвет заливки красный.

Для отображения соответствующих записей, внесем в верхнюю ячейку колонки «Сообщение о ходе выполнения» следующую формулу:

=ЕСЛИ(ЕПУСТО(G5);ЕСЛИ($C$2>(E5+F5);»срок выполнения задачи вышел»;»»);ЕСЛИ(F5<РАЗНДАТ(E5;G5;»d»);»задержка на «&РАЗНДАТ(E5;G5;»d»)-F5&» дней»;»задержек нет»))

Теперь осталось настроить условное форматирование для разных вариантов текста, и файл готов. Чтобы проще было ориентироваться, все ячейки, в которые данные заносятся с клавиатуры, я закрасил желтым цветом. Вот что у меня получилось:

таблица ексель

Скачать файл можно по ссылке.

По просьбе читателя по имени Данила, добавлена функция отображения сообщения о завершении задачи с опережением срока. Скачать файл с этой функцией можно по ссылке.

Уточнение срока

И еще одно дополнение. В графике отсчет всех сроков начинается со следующего дня. Например, если задача стартовала 11-го января, а закончилась 12-го, то таблица укажет, что продолжительность задачи 1 день, и все расчеты будет осуществлять исходя из этого принципа. Однако, по логике, 11-е число тоже должно считаться, и, в этом случае, следовало бы считать два дня. Возможно, это не принципиально, но для полноты картины выкладываю файл, который первый день срока выполнения задачи тоже учитывает при расчете ее продолжительности.

График с почасовым учетом

Благодаря моим уважаемым читателям, план-график обрастает все новыми возможностями. Так, по просьбе Александра, решил доработать файл, чтобы он вел учет не по дням, а по часам. Стало чуть менее удобно вносить данные о дате и времени, но, зато, теперь все считается в почасовом формате. Принимайте новый файл.

Учет выходных дней

И еще одно улучшение — читательница Вера предложила считать продолжительность задач в рабочих днях. Что и было сделано в этом файле.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *