Создадим в MS EXCEL простейший календарно-сетевой график с шагом равным 1 месяц, затем сделаем связи между отдельными работами (тип связи Конец-Начало), потом введем запаздывание, отобразим % выполнения и наконец - прогноз завершения работы в случае ее отклонения от плана.

Под "календарно-сетевым графиком" будем понимать график работ проекта, состоящий из 2-х частей:

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

Совет: В данной статье диаграмма выполнена с помощью Условного форматирования (далее УФ). Для тех, кто мало знаком с этим инструментом MS EXCEL рекомендуется прочитать вот эту статью про УФ.

Примечание: в другой статье про диаграмму Ганта данная диаграмма построена с помощью инструмента Диаграмма.

Простейший график

Построим график именно в месяцах, т.к. в EXCEL он получается очень компактным и проще соотнести длительность работы (в месяцах) с ее началом и концом (не требуется дополнительных вычислений, как например для недель, когда в разных месяцах укладывается разное количество недель). Конечно, такая детализация не очень удобна для коротких проектов, но, чтобы укрупненно продемонстрировать прогресс проектов (с длительностью от 1 года), такой подход вполне будет приемлемым.

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

• Длительность каждой работы
• Начало каждой работы (связи сделаем позже)

Месяц окончания каждой работы будем вычислять с помощью формулы =C14+D14-1 (к началу прибавим длительность) и скорректируем на 1, т.к. в начальный месяц работа также выполняется.

Правило УФ также примитивно: =И(G$11>=$D14;G$11<=$E14). В строке 26 специально введена эта формула, чтобы убедиться, что все работает (ячейка закрашивается, только если формула возвращает ИСТИНА). При изменении длительности работы или ее начала, график будет автоматически перестраиваться. 

Чтобы чуть украсить график, можно для разных работ задать разные цвета. Для этого потребуется создать столько правил УФ, сколько разных цветов у нас будет на графике. Формула также чуть усложнится, также добавится столбец с кодом цвета.

Текущий месяц также можно отразить на графике с помощью УФ. На картинке выше текущий месяц выделен штриховкой - это июль 2022 года.

График со связями работ

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

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

=ЕСЛИ(ЕПУСТО(C14);$C$6;ИНДЕКС($F$14:$F$19;ПОИСКПОЗ(C14;$A$14:$A$19;0))+1)

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

Вторая часть формулы ИНДЕКС($F$14:$F$19;ПОИСКПОЗ(C14;$A$14:$A$19;0))+1 ищет в столбце А код предшественника, указанный в столбце С. После нахождения нужной строки, берет срок конца работы-предшественника (столбец F) и подставляет его в начало работы (столбец Е).

Примечание: Срок конца работы-предшественника (столбец F) вычисляется с помощью опять же столбца Е (также добавлен 1 месяц, чтобы работа-последователь начиналась без пересечения, т.е. с последующего месяца, как показано на диаграмме выше).

Обратите внимание, что циклической ссылки не возникает, хотя формально мы используем значения столбца Е (начало работы), чтобы вычислять опять же начало, но уже другой работы. Все дело в том, что это разные ячейки. Например, для вычисления начала работы с кодом 20 (ячейка Е15) мы использовали значение начала работы с кодом 10, которое находится в ячейке Е14. Главное - не делать перекрестных ссылок, например, когда предшественником работы 20 является работа 30, а у работы 30 предшественником является работа 20 (даже опосредованно, через другие работы).

График со связями работ и запаздыванием

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

На рисунке выше показано как выглядит задержка 2 месяца работы 20, относительно работы 10. Формула, с помощью которой это реализовано, почти полностью совпадает с предыдущей формулой, которую мы использовали для вычисления начала работы-последователя

 =ЕСЛИ(ЕПУСТО(C14);$C$6;ИНДЕКС($F$14:$F$19;ПОИСКПОЗ(C14;$A$14:$A$19;0))+1+$G14)

Теперь в столбце G у нас введена величина запаздывания, которая может быть отрицательной величиной. В этом случае работа-последователь начнет выполняться еще до завершения работы-предшественника.

График со связями работ, запаздыванием и прогрессом выполнения работ

Планирование работ - это половина дела, необходимо еще и отслеживать выполнение планов. Прогресс отслеживания завершения работы не сложно реализовать в MS EXCEL с помощью Условного форматирования.

Сначала, чтобы не перегружать количеством правил УФ, избавимся от кодов цветов - сделаем представление планов работ одним (голубым) цветом. Выполненные работы будем отмечать темно-синим цветом. Всего у нас будет 2 правила УФ: один для плана, другой для факта выполненных работ.

Нам особо ничего не придется переделывать на листе - столбец с кодом цвета теперь будет использоваться для хранения прогресса выполненных работ в месяцах. Фактически мы будем указывать сколько месяцев из общей длительности работы нужно закрасить другим цветом.

Формула в правиле УФ очевидна =И(I$11>=$E14;I$11<=$E14+$H14-1)

Закрашиваться будут только те ячейки (месяцы), которые уже начались (>=E14) у данной работы И выполнены (<=E14+H14-1).

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

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

Процент выполнения работ

Иногда указывать прогресс выполнения задачи удобнее в процентах от общего объема работ (в нашем случае - от ее общей длительности). Этот вариант также реализован в файле примера на другом листе.

Если раньше на диаграмме ганта у нас были пустые ячейки, то теперь у нас введена довольно длинная формула:

=ЕСЛИ(И(I$8>=$E11;I$8<=$F11);ЕСЛИ((I$8-$E11)>$AH11-1;ЕСЛИ((I$8-$E11-$AH11)>0;0;$AI11);100);-1)

Она может вернуть 4 разных значения: 

• -1: ячейка (соответствующий месяц) лежит вне периода выполнения работы, закрашивается белым;
• 0: работа в этом месяце еще не выполнена, ячейка закрашивается салатовым цветом;
• от 1 до 99: работа в этом месяце выполнена частично, ячейка закрашивается гистограммой пропорционально значению (50 соответствует половине закрашенной ячейки);
• 100: работа в этом месяце выполнена полностью, ячейка полностью синим цветом.

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

График со связями работ, запаздыванием, прогрессом выполнения работ и прогнозом окончания

Работы не всегда выполняются четко по плану, чаще фактическая длительность больше, чем плановая. По мере выполнения работы зачастую становится понятно насколько ее окончание сдвинется. Смоделируем эту ситуацию в нашем графике, добавив еще один столбец - Прогноз. В этом столбце будем указывать сколько еще месяцев дополнительно к плановой длительности нам потребуется, чтобы завершить весь объем работы.

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

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

Как видно из рисунка, длительность первой работы (самая верхняя строка) скорее составит теперь не 7 месяцев как планировалось, а 9 (+2 месяца указано в столбце "прогн", т.е. прогноз). Эти дополнительные 2 месяца выделены темно-оранжевым цветом. После добавления этих двух месяцев изменятся сроки начала связанных работ. Теперь последующая работа должна начаться с 11 месяца, а не с 9.

Прогноз реализован с помощью еще одного правила УФ:

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

А мы далее рассмотрим создание графика с детализацией по дням, он больше подходит для более коротких проектов.