1. Упрощенное формирование структуры декомпозиции работ (СДР)
2. Автоматическое определение связи начала работ по задачам с другими задачами
3. Добавление примечаний
4. Отслеживание выполнения работ
5. Построение диаграммы Ганта
5.1. Выделение общей длительности работ
5.2. Выделение процента завершенности работ
5.3. Выделение выходных дней
5.4. Выделение текущей даты
6. Отметка вех

Для данной статьи доступно видео, в котором вы сможете наблюдать за созданием плана проекта.
Смотреть видео

В нашем магазине доступна продвинутая версия данного плана проекта.
Перейти в магазин

1. Упрощенное формирование структуры декомпозиции работ (СДР)

Для удобства формирования СДР выделим дополнительно 5 столбцов (A:E), где каждый столбец соответствует уровню вложенности работы. Обычно, пяти уровней вложенности достаточно, но вы легко можете скорректировать количество уровней, применительно к вашему плану проекта.

Стоблец "СДР" (F), который остался от предыдущей таблицы, будет заполняться автоматически. Для этого мы используем следующую формулу (для ячейки F9):

=СЦЕПИТЬ(ЕСЛИ(A9<>"";A9&".";"");ЕСЛИ(B9<>"";B9&".";"");ЕСЛИ(C9<>"";C9&".";"");ЕСЛИ(D9<>"";D9&".";"");ЕСЛИ(E9<>"";E9&".";""))

Функция СЦЕПИТЬ объединяет значения ячеек A9, B9, C9, D9, E9, предварительно проверив их на наличие значения. После каждого уровня ставиться точка.

Упрощенное формирование структуры декомпозиции работ

2. Автоматическое определение связи начала работ по задачам с другими задачами

Определить наличие связи с другими задачами мы можем, поняв, ссылается ли ячейка с датой начала работы (столбец "Начало", L) на другие ячейки или применяются ли дополнительные расчеты.

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

Определить наличие формулы в ячейке стандартными средствами мы не можем, поэтому используем следующий макрос:

Function IsFunction(cell As Range) As Boolean
    If InStr(1, cell.Formula, "=", vbTextCompare) <> 0 Then
        ' Если первый символ в формуле ячейки – знак равно – значит, это расчетная ячейка
        IsFunction = True
    Else
        ' В противном случае, значение в ячейку введено вручную
        IsFunction = False
    End If
End Function

Создадим новый столбец "Начало не ранее" (G), в ячейку которого впишем формулу:

=ЕСЛИ(ИЛИ($L9="";IsFunction($L9));"";"¸")

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

Для отображения символа циферблата, примените к ячейкам шрифт Wingdings, входящий в стандартную поставку всех компьютеров на базе ОС Microsoft Windows.

3. Добавление примечаний

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

Проведем подготовительные мероприятия для создания связи между планом проекта и вновь создаваемым листом с примечаниями.

В плане проекта выделим диапазон F9:F25 (столбец "СДР"), присвоим ему имя "сдр".

Присвоение имени диапазону СДР

Затем выделим диапазон J9:J25 (столбец "Задача") и присвоим ему имя "задачи".

Присвоение имени диапазону задач

Создадим лист "Примечания", содержащий столбцы "СДР", "Наименование задачи" и "Примечание". Теперь сделаем так, чтобы мы могли выбрать нужную задачу из списка задач по ее номеру (СДР).

Подготовка столбца СДР

Выберем ячейку A2 (первая свободная ячейка в столбце "СДР") и перейдем на ленте во вкладку Данные – группа Работа с данными – Проверка данных. В открывшемся окне введем:

  • Тип данных: Список
  • Источник: =сдр

Подтвердим ввод, нажав на кнопку OK.

Проверка вводимых значений для СДР

Теперь при выделении данной ячейки справа от нее будет высвечиваться стрелка, означающая возможность выбора значения из списка значений диапазона "сдр" (его мы определили ранее).

Выбор значения СДР из списка

Подготовка столбца "Наименование задачи"

Теперь, зная СДР задачи, мы можем найти ее наименование, которое будем использовать в справочных целях.

Введем в ячейку B2 (первая свободная ячейка в столбце "Наименование задачи") следующую формулу:

=ИНДЕКС(задачи;ПОИСКПОЗ(A2;сдр;0))

Разберем формулу по частям:

  • ПОИСКПОЗ(A2;сдр;0) – находим порядковый номер вхождения значения ячейки A2 в диапазоне "сдр"
  • ИНДЕКС(задачи;ПОИСКПОЗ(…)) – находим соответствующее порядковому номеру СДР наименование задачи в диапазоне "задачи"

В качестве альтернативы мы могли бы использовать функцию ВПР для нахождения наименования задачи.

Подготовка таблицы примечаний для заполнения

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

Для создания Таблицы перейдем во вкладку Вставка – группа Таблицы – Таблица (Ctrl+T) и во всплывающем окне проверим, что указанное расположение данных соответствует нашей таблице примечаний, а также отмечена опция, что наша таблица включает заголовки.

Создание Таблицы примечаний

Ссылка на примечание из плана проекта

Выделите одну из ячеек таблицы примечаний, перейдите во вкладку Конструктор, где укажите в поле Имя таблицы значение "Примечания", и подтвердите ввод клавишей Enter. Теперь к нашей таблице можно будет обращаться иначе, чем мы привыкли, работая с обычными диапазонами. Рассмотрим это на следующем этапе: создадим ссылки на примечания в плане проекта.

Создадим столбец "Примечание" (H), который заполним следующей формулой (ниже пример для первой строки плана проекта):

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(F9;Примечания[СДР];0));"";")")

  • ПОИСКПОЗ(F9;Примечания[СДР];0) – производим поиск номера СДР, соответствующего строке плана проекта, в столбце СДР таблицы Примечаний. Если примечание для данной задачи отсутствует, формула выдает ошибку #Н/Д, поэтому для обработки такого результата используем дополнительно обработчик ошибок – функцию ЕОШИБКА
  • ЕОШИБКА(ПОИСКПОЗ(…)) – данная функция проверяет вложенные в нее вычисления на наличие ошибок (#Н/Д, #ЗНАЧ!, #ССЫЛ!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!) и возвращает логическое значение (ИСТИНА / ЛОЖЬ) в зависимости от результата проверки: ИСТИНА – при наличии ошибки во вложенных вычислениях, ЛОЖЬ – при отсутствии ошибок
  • ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(…));"";")") – результат функции ЕОШИБКА легко обработать функцией ЕСЛИ. Таким образом, при отсутствии ошибок (т.е. наличии примечания в таблице Примечаний для задачи плана проекта) на экран будет выведен указанный нами символ. При отсутствии ошибок не будет выведено ничего

Используя автозаполнение скопируйте введенную формулу в ячейки ниже.

Выделите диапазон H9:H25 (столбец "Примечание"), установите шрифт Wingdings 2.

4. Отслеживание выполнения задач

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

Создадим столбец "Завершено, %" (O), установим для ячеек столбца (P9:P25) процентный числовой формат.

Изменение формата чисел для столбца Завершено, %

Отслеживание завершенности задач

Создадим столбец "Завершено" (N), в котором будет автоматически отображаться индикатор завершенности работы. Введем в ячейку N9 формулу:

=ЕСЛИ(O9=1;1;-1)

Таким образом, если процент завершения задачи равен 100%, результат формулы будет 1, в противном случае: -1.

Теперь применим такой формат ячеек, при котором для завершенной задачи вместо цифры "1" будет отображаться, например, флажок. Выделите диапазон N9:N25, установите формат ячеек "ü;;" и примените шрифт Wingdings.

Изменение формата ячеек для столбца Завершено

Используя автозаполнение скопируйте введенную формулу и форматирование в ячейки ниже. Теперь напротив каждой завершенной задачи будет отображаться индикатор ее завершенности.

Отображение индикатора завершенности задачи

5. Создание диаграммы Ганта

Диаграмма Ганта (Gantt chart или ленточная диаграмма, график Ганта) является популярным типом столбчатых диаграмм (гистограмм), который используется для иллюстрации плана, графика работ по какому-либо проекту. Диаграмма Ганта используется в качестве метода планирования проектов.

По сути, диаграмма Ганта состоит из полос, ориентированных вдоль оси времени. Каждая полоса на диаграмме представляет отдельную задачу в составе проекта (вид работы), ее концы — моменты начала и завершения работы, а протяженность — длительность работы. Вертикальной осью диаграммы служит перечень задач. Кроме того, на диаграмме могут быть отмечены совокупные задачи, проценты завершения, указатели последовательности и зависимости работ, метки ключевых моментов (вехи), метка текущего момента времени "сегодня" и др.

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

Создание временной шкалы

В зависимости от проекта календарный график проекта может быть растянут во времени и находиться как в пределах одного месяца, так и длиться годами. Хоть новые версии Excel и предоставляют нам возможность растянуться на 16 с лишним тысяч столбцов, это не является лучшим выходом из ситуации и не будет удобно в использовании в конечном итоге.

Для начала создадим временную шкалу. Выделим для диаграммы столько столбцов, сколько нам нужно для отображения. В данном примере нам необходимо видеть задачи в пределах одного месяца. Поэтому выделяем под диаграмму 31 столбец (R:AV) – по максимальному количеству дней в месяце.

В строке заголовка (R7:AV7) укажем даты проекта:

  • начальная дата диаграммы (R7) должна соответствовать дате начала проекта (J4)

=J4

  • каждая последующая дата (S7, T7 и т.д.) должна зависеть от предыдущей даты, чтобы при изменении даты начала проекта они автоматически пересчитывались.

=R7+1 – для ячейки S7

Применим к оси дат следующее форматирование:

  • изменим числовой формат на более лаконичный, например "ноя 05, 13", для чего введем в поле Тип шаблон "МММ ДД, ГГ"

Изменение формата дат временной оси

  • для экономии места повернем текст в ячейках на 90 градусов.

В строке ниже (R8:AV8) укажем кратко дни недели. Excel имеет встроенный функционал перевода даты в дни недели применив специальный формат ячейки ("ДДД"), однако такой формат возвращает двухсимвольное сокращение (например, "Вт"), что для нас будет излишне – достаточно одной буквы. Чтобы максимально сократить место сделаем следующее:

  • Создадим новое имя "weekdays". Перейдем на вкладку Формулы – группа Определенные имена – Присвоить имя
  • Введем имя – "weekdays" и значение диапазона:

={"Понедельник":"Вторник":"Среда":"Четверг":"Пятница":"Суббота":"Воскресенье"}

Создание переменной weekdays

Это означает, что мы создали переменную, которая доступна на любом листе книги и будет содержать массив дней недели.

  • В строку R8:AV8 введем следующую формулу:

=ЛЕВСИМВ(ИНДЕКС(weekdays;ДЕНЬНЕД(R7;2));1) – для ячейки R8

  • ДЕНЬНЕД(R7;2) – узнаем день недели для даты, находящейся в ячейке R7, при этом второй параметр функции ("2") означает, что понедельник является первым днем недели (Пн = 1, Вт = 2, …, Вс = 7)
  • ИНДЕКС(weekdays;ДЕНЬНЕД(…)) – находим в ранее созданной переменной "weekdays" текстовое значение дня недели, соответствующее искомой дате
  • ЛЕВСИМВ(ИНДЕКС(…);1) – возвращаем в ячейку первый символ дня недели

Таким образом, мы получим следующую ось дат (дата начала проекта равна 01.11.2013):

Итоговая временная ось

Создание механизма перемещения по временной шкале

Теперь создадим универсальный механизм перемещения по временной шкале. Добавим на лист с диаграммой Ганта полосу прокрутки: на ленте перейдем на вкладку Разработчик – группа Элементы управления – Вставить – Полоса прокрутки. Нарисуем на листе над временной шкалой горизонтальную полосу.

Добавление полосы прокрутки

Кликнув правой кнопкой мыши на созданной полосе прокрутке перейдем в ее свойства (Формат объекта…). Изменим следующие параметры:

  • Максимальное значение: будет соответствовать количеству дней, на которое мы сможем переместиться от даты начала проекта.

Данное значение не должно быть меньше предполагаемой длительности всего проекта. Например, если проект длится 3 месяца, то необходимо указать значение 93 (31 день * 3 месяца).

  • Связь с ячейкой: введем ссылку на ячейку, в которой будет содержаться текущее положение ползунка полосы прокрутки. Связанная ячейка будет возвращать текущее значение, соответствующее положению ползунка.

Мы используем значение ползунка в формуле временной шкалы, чтобы реагировать на изменение положения ползунка – смещать диаграмму во времени.

Установим связь с ячейкой R6.

После добавления полосы прокрутки изменим формулу первой даты на временной шкале (ячейка R7) на следующую:

=J4+R6

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

Создание диаграммы Ганта

Диаграмма будет располагаться в диапазоне, соответствующему количеству задач – по строкам и количеству периодов – по столбцам. В нашем случае: R9:AV25.

Выделим весь диапазон, включая временную шкалу (R7:AV25) и добавим правила условного форматирования, по которым будет строиться диаграмма. Перейдем на вкладку Главная – раздел Стили – Условное форматирование – Управление правилами…

Правило 1: Общая длительность задачи

Создадим новое правило с использованием формулы для определения форматируемых ячеек: выделение общей длительности задачи.

Правило условного форматирования для выделения общей длительности задач

=И(R$7>=$L7;R$7<=$M7)

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

Обратите внимание на фиксирование строк и столбцов в ссылках на ячейки:

  • R$7 – при применении условного форматирования Excel будет оперировать только строкой 7, на которой располагается временная шкала
  • $L7, $M7 – при применении условного форматирования Excel будет оперировать только столбцами L и M, в которых введены даты начала и окончания задач

Правило 2: Процент завершения задачи

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

Правило условного форматирования для выделения завершенной части работ

=И(R$7>=$L7;R$7<($L7+ОКРУГЛ(($M7-$L7+1)*$O7;0)))

  • ОКРУГЛ(($M7-$L7+1)*$O7;0) – переведем процент завершения задачи в календарные дни: вычислим общую длительность задачи в календарных днях и умножим на процент ее завершенности. Полученный результат округлим до целого значения
  • $L7+ОКРУГЛ(…) – определим календарную дату, соответствующую проценту завершения задачи.

Правило 3: Выделение выходных дней

Создадим новое правило с использованием формулы для определения форматируемых ячеек: выделение выходных дней.

Правило условного форматирования для выделения выходных дней

=ДЕНЬНЕД(R$7;2)>5

В том случае, если дата на временной шкале является субботой или воскресеньем, она будет заштрихована (подробнее о функции ДЕНЬНЕД см. раздел Создание временной шкалы данной статьи).

Правило 4: Выделение текущего дня

Создадим новое правило с использованием формулы для определения форматируемых ячеек: выделение текущей даты.

Правило условного форматирования для выделения текущей даты

=R$7=СЕГОДНЯ()

В случае совпадения даты на временной шкале с текущей датой, такой столбец будет окрашен розовым цветом с красной границей.

Расположение правил форматирования

В диспетчере правил условного форматирования расположите созданные нами правила в следующем порядке:

  • Форматирование столбцов плана проекта,
  • Форматирование процента завершенности задачи,
  • Форматирование общей длительности задачи
  • Форматирование выходных дней,
  • Форматирование текущей даты.

Перемещайте правила используя кнопки вверх и вниз.

Затем напротив каждого правила снимите флажок в графе Остановить, если истина.

Расположение правил условного форматирования

6. Отметка вех

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

Для добавления возможности указания вех создадим столбец "Веха" (I), в который будем вводить цифру "1", если конкретная работа является вехой.

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

Копирование символа вехи

Выделите ячейки столбца Веха и перейдите в меню Формат ячеек. Выберите последний пункт из списка Числовых форматов и в поле Тип введите: "♦;;". Первая часть введенного формата отвечает за формат числа больше нуля, вторая (после знака ";") – за формат отрицательного числа, третий – за формат нулевого значения.

Таким образом, при вводе в ячейку любого числа больше 0 Excel заменит его на указанный нами знак, а при вводе любого другого числа (0 и меньше) – оставит ячейку пустой.

Форматирование ячеек для столбца Веха

Теперь, чтобы проставлять соответствующие отметки на диаграмме Ганта, необходимо:

  • Доработать ранее рассмотренные правила условного форматирования

Ранее мы использовали следующее правило для индикации продолжительности задачи:

=И(R$7>=$L7;R$7<=$M7)

Видоизменим это правило так, чтобы вехи не выделялись цветовой заливкой (изменения выделены жирным шрифтом):

=И(R$7>=$L7;R$7<=$M7;$I7<>1)

  • Ввести в ячейки того же диапазона формулы и применить форматирование ячеек, рассмотренное выше (изменить шрифт на Verdana и применить формат "♦;;").

=ЕСЛИ(И($I9=1;R$7=$L9);1;0)

Введите формулу в ячейку R9 и скопируйте ее в другие ячейки диапазона или воспользуйтесь автозаполнением.

Добавление вех на диаграмму Ганта


Ждем ваших предложений по функционалу, который вы хотели бы видеть в плане проекта на базе Microsoft Excel.

В нашем магазине доступна продвинутая версия данного плана проекта.
Перейти в магазин

Скачать для Excel 2007+

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


Защитный код
Обновить

Комментарии   

Veritas
0 # Veritas 14.04.2016 21:20
Автору благодарность за четкое и понятно изложение материала!!!
В данном случае показывается процент выполнения задачи. Однако, при выполнении проекта хорошо иметь представление укладывается ли в сроки ресурс (или наоборот задачи выполняются быстрее). И если не укладывается (опережает), то насколько это повлияет на дату окончания проекта. Скажите, пожалуйста, как это можно реализовать?
Буду признательна за ответ!
Ответить | Ответить с цитатой | Цитировать
вера
0 # вера 16.10.2017 08:07
Благодарю, все понятно и доступно. Для людей которые не знают хорошо XL, просто находка. Я построила по вашей инструкции календарь проекта . До этого весь инет обыскала, чтобы хоть как то вменяемо объяснили. Спасибо.
Ответить | Ответить с цитатой | Цитировать
Ева
0 # Ева 29.10.2017 10:00
Все отлично написано, очень доступно, наверное даже тем, кто не знает Ecxel, но у меня проблема, не могли вы помочь?! я строю проект не по дням в неделе, а по календарным неделям, но работы могут быть выполнены за 1 день, как в этом случае быть, программа не считывает этот один день и не закрашивает нужную ячейку! Помогите, плиииз!
Ответить | Ответить с цитатой | Цитировать
Вверх