Календарно-сетевой график в неделях в MS EXCEL
Построим Календарно-сетевой график с временным шагом 1 неделя. Наш график будет отображать: длительность работ, объем выполненных работ и прогноз длительности (в случае отклонения от планового завершения). В графике есть возможность связать работы между собой (тип связи конец-начало с запаздыванием).
О построении календарных графиков в EXCEL
Начнем с разочарований: EXCEL не предназначен для ведения календарных графиков — для этого нужно использовать специализированные программы. В EXCEL можно только вести простенькие графики, изредка показывая их руководству. Но, реализовать различные связи между работами, полноценное планирование ресурсов и ведение нескольких базовых графиков проекта в EXCEL практически невозможно (без написания соответствующих VBA программ, что не разумно, т.к. все уже давно написано в специализированных программах).
Но, конечно, несложные графики в EXCEL построить можно, как это сделать — продемонстрировано во многих статьях, в том числе и на сайте excel2.ru:
- График с ежедневной детализацией (с помощью Условного форматирования);
- График с месячной детализацией (с помощью Условного форматирования);
- Метод критического пути (график построен с помощью диаграммы). Есть возможность задать связи между работами, вычислить критический путь и показать временной запас;
- В статье про диаграмму Ганта показано как построить диаграмму с % завершения работ и связями (график построен с помощью диаграммы).
В этой статье построим график с недельной детализацией и указанием месяцев, как показано на рисунке выше. Статья базируется на идеях, изложенных в статье про график с месячной детализацией, поэтому некоторые пояснения будут опущены, чтобы не повторяться. Если логика построения графика кажется сложной, то лучше начать изучение графика с указанной статьи.
Совет: В данной статье диаграмма выполнена с помощью Условного форматирования (далее УФ). Для тех, кто мало знаком с этим инструментом MS EXCEL рекомендуется прочитать вот эту статью про УФ.
Построение графика
Для построения графика, изображенного выше, нам потребуются следующие исходные данные:
- Длительность каждой работы
- Предшественник работы
- Запаздывание относительно окончания работы-предшественника
- % выполнения
- прогноз завершения работы (в случае ее отклонения)
Всего используется 2 правила УФ: одно для отображения плановой длительности (зеленая заливка), другое — для прогноза завершения работы (оранжевая).
Процент выполнения реализован с помощью символа похожего на квадратик. Этот символ есть в шрифте Webdings, ему соответствует буква «g». Соответствующая формула введена во все ячейки диаграммы Ганта =ЕСЛИ(И($L15>=N$6;$J15<=N$6);"g";"")
Так как почти все работы связаны (тип связи Конец-Начало), то для вычисления начала работ используется формула = ЕСЛИ(ЕПУСТО(C15);$C$7;ИНДЕКС($M$15:$M$18;ПОИСКПОЗ(C15;$A$15:$A$18;0))+7+$G15)
Таким образом, вводить начало и конец работы не нужно, это вычисляемые столбцы. Достаточно задать связи и длительность работы, а также дату начала проекта. В столбце Запаздывание вводится поправка в днях, если нужно чтобы работа начиналась чуть позже или раньше окончания работы-предшественника. Столбец Прогноз содержит дополнительные дни, которые необходимо добавить к первоначальной длительности работы, чтобы ее завершить (в него вводятся значения если в ходе выполнения работы произошла задержка).
При построении графиков с шагом 1 неделя существует несколько сложностей:
- т.к. начало и конец работы указываются в днях, то нужно определить как отображать на диаграмме работы, которые начинаются/заканчиваются в середине недели (в данной статье принято, что если работа содержит хотя бы 3 рабочих дня недели, то эта неделя относится к периоду выполнения работы, если 2 или 1 — то не относится. Поэтому, задавать длительность работ менее 7 дней не нужно, работа может не отобразиться на диаграмме. В этом случае используйте файл с графиком, в котором шаг планирования равен 1 дню);
- отнесение недель к месяцу требует установления аналогичного правила: если 3 или более рабочих дня недели относятся к месяцу, то и вся неделя относится к месяцу;
- из-за предыдущего правила длительность месяцев получается разной — в одних получается 4, а в других 5 недель. Эта проблема решается формулами в строках 6, 7 и 12;
- из-за особенности отображения текстовых строк, названия месяцев требуется выводить по несколько символов в ячейке (если в соседней ячейке справа есть формула или значение, то текстовая строка обрезается на границе ячейки, чтобы не было наложения 2х значений друг на друга). Это реализовано формулой = ПСТР(ВПР(N8;служ!$B$4:$C$15;2;0);(N12-1)*ВПР(N8;служ!$B$4:$D$15;3;0)+1;ВПР(N8;служ!$B$4:$D$15;3;0))
В строке 14 на диаграмме указаны даты, которые выпадают на понедельники. В строке 13 — названия месяцев. В строке 12 ведется подсчет недель месяца. В строке 11 — сквозная нумерация недель проекта. В строке 10 напротив текущей недели отображается красный маркер.
Для удобства внизу графика выведен календарь, который показывает выбранный месяц проекта (он также выделяется Условным форматированием).
Календарь реализован одной формулой массива. Как это сделать показано в этой статье.
Результат
Очевидно, что даже такой относительно простой график получился перенасыщен формулами. Можно легко нарушить его работу, случайно удалив формулу.
Автоматическая сетевая диаграмма проекта в EXCEL
Построим сетевую диаграмму проекта на диаграмме MS EXCEL. Сетевая диаграмма будет автоматически перестраиваться при изменении связей между работами. Для этого нам потребуется автоматически определить все пути проекта (не только критические).
В статье Метод критического пути в MS EXCEL сетевая диаграмма проекта была построена на листе MS EXCEL.
К сожалению, при изменении связей между работами данную диаграмму необходимо перестраивать в ручную, что может быть достаточно трудоемко. Чтобы этого избежать, используем диаграмму типа Точечная (XY Scatter) , на которой точками обозначим работы, а стрелками — связи между работами.
СОВЕТ : Подробнее о построении диаграмм см. статью Основы построения диаграмм в MS EXCEL .
Постановка задачи
Предположим, что нам требуется отобразить связи проекта, состоящего из 7 работ (от А до G), также заданы вехи начала (Start) и окончания проекта (Finish).
Как видно из диаграммы, связи между работами заданы так, что существует 3 пути:
- Start-A-D-Finish
- Start-B-E-G-Finish
- Start-C-F-Finish
Изобразим на диаграмме типа Точечная эти работы и связи между ними.
Пусть между работами D и G требуется создать связь (выделено красным на диаграмме ниже).
Это приведет к тому, что число путей проекта увеличится с 3-х до 4-х: добавится путь Start-А-D-G-Finish.
Нам требуется, чтобы точечная диаграмма была автоматически перестроена и автоматически был произведен подсчет нового количества путей.
Эта статья о том как создать такую диаграмму (см. файл примера ). В дальнейшем, идеи этой статьи будут использованы для автоматического отображения на сетевой диаграмме критического (или критических) путей (см. статью Автоматическая сетевая диаграмма проекта с критическим путем в MS EXCEL ). Это удобно на этапе планирования проекта, когда уточняются связи между работами и длительности самих работ. В данной статье не используются длительности работ, а лишь связи между ними. Основной смысл статьи — показать как реализован автоматический подсчет путей и их отображение на диаграмме.
ВНИМАНИЕ! Построение данной сетевой диаграммы в этой статье приведено лишь с целью демонстрации технической реализуемости такого построения в MS EXCEL. Не ставилось целью сделать «удобную программу для пользователей». Это означает, что при изменении пользователем количества работ/ добавления связей между работами, переименовании листов, рядов диаграммы и других изменений в файле примера , может потребовать дополнительной настройки файла. Такая настройка от пользователя потребует серьезных знаний MS EXCEL и времени.
Задаем связи между работами
В отличие от подхода изложенного в статье Метод критического пути в MS EXCEL , где мы задавали для каждой работы ее предшественников, в этой статье зададим для каждой работы ее последователей.
В этой статье число последователей у каждой работы не может быть более 3. Последователей будем задавать с помощью выпадающего списка , содержащего наименования работ.
Также для каждой работы подсчитаем количество предшественников (работы-предшественники не потребуются для вычисления путей и построения диаграммы, поэтому при первом прочтении статьи можно переходить к следующему разделу).
Подсчет предшественников можно сделать с помощью формулы, которая состоит из 4-х частей:
Сначала, для каждой работы определим, для каких работ она является последователем. Это реализовано с помощью функции ЕСЛИ() : если работа является чьим-то последователем, то возвращается код работы-предшественника (совпадающий с номером позиции работы). В противном случае возвращается значение Пустой текст «» .
Функция ИНДЕКС() выводит коды работ-предшественников по номеру позиции. Функция ЕСЛИОШИБКА() , которая появилась в MS EXCEL 2007 , заменяет ошибки #ЧИСЛО! на значение Пустой текст «», которое очень удобно, т.к. ячейка выглядит при этом пустой.
При задании последователей (см. желтые ячейки) необходимо отслеживать, чтобы количество предшественников у каждой работы было не больше 3-х (см. столбец J).
Вычисление путей
Алгоритм вычисления путей следующий:
- Для вехи Start (код=1) определяются ее последователи, т.е. работы А, В и С, которые имеют коды соответственно 2, 3, 4 (см. шаг 0, строка 37), а также количество последователей у каждой из работ А, В и С. Кроме того, код вехи Start необходимо повторить в столбце В столько раз, сколько у нее последователей. На данном шаге количество путей равно 3, т.е. равно количеству последователей вехи Start;
- На следующем шаге определяются последователи работ А, В, С, т.е. работы D, E, F (коды 5, 6, 7). См. ячейку J53 . В соседнем столбце справа вычисляется количество последователей этих работ. Как видно из диаграммы выше, работа D имеет 2 последователя. Это приводит к тому, что количество путей проекта увеличивается до 4-х. Поэтому нужно обновить количество вех Start до 4-х (см. ячейку G53 ). Это можно сделать используя идеи из статьи Восстанавливаем последовательности из списка без повторов в MS EXCEL .
- Аналогично на следующих шагах определяются следующие работы-последователи и обновляется количество возможных путей проекта. По результатам каждого шага производится проверка достижения вехи Finish (код=9). Если все пути завершены, то в конце каждого пути должна быть веха Finish.
В файле примера максимальная длина пути от вехи Start до вехи Finish должна быть не более 5 (включая эти вехи). Под длиной пути понимается последовательность работ, например: Start — А — D — G — Finish. При необходимости нужно увеличить количество шагов, чтобы получить возможность вычислять более длительные пути. В столбце N с помощью Условного форматирования создан индикатор, который показывает завершение вычисления путей на определенном шаге.
Построение диаграммы
Сначала на диаграмме построим точки, представляющие собой работы.
Чтобы каждой точке присвоить надпись с названием работы нужно написать макрос или иметь MS EXCEL 2013 или последующую версию программы (см. статью Подписи для точечной диаграммы в MS EXCEL ). В файле примера также имеется макрос для присвоения надписей (также см. статью Ориентированный граф на диаграмме MS EXCEL ).
Чтобы создать пути (максимум 10) нам потребуется создать 10 рядов данных. Часть из этих рядов будет содержать значения #Н/Д, т.к. число путей может быть меньше 10.
В результате получим вот такую диаграмму, в которой 4 пути:
Предположим, что при планировании проекта выяснилось, что между работами F и G имеется связь (взаимосвязь работ в файле примера только Финиш-Старт, т.е. начало следующей работы после окончания предыдущей). Добавив эту связь в ячейку D28 , диаграмма автоматически обновится.
Как видно из диаграммы — также увеличилось число путей: с 4 до 5.
Календарно-сетевой график в месяцах в MS EXCEL
Создадим в MS EXCEL простейший календарно-сетевой график с шагом равным 1 месяц, затем сделаем связи между отдельными работами (тип связи Конец-Начало), потом введем запаздывание, отобразим % выполнения и наконец — прогноз завершения работы в случае ее отклонения от плана.
Под «календарно-сетевым графиком» будем понимать график работ проекта, состоящий из 2-х частей:
- календарный план работ проекта (перечень работ с датами начала, окончания и связями между работами)
- соответствующая плану диаграмма Ганта.
Совет: В данной статье диаграмма выполнена с помощью Условного форматирования (далее УФ). Для тех, кто мало знаком с этим инструментом MS EXCEL рекомендуется прочитать вот эту статью про УФ.
Примечание: в другой статье про диаграмму Ганта данная диаграмма построена с помощью инструмента Диаграмма.
Простейший график
Построим график именно в месяцах, т.к. в EXCEL он получается очень компактным и проще соотнести длительность работы (в месяцах) с ее началом и концом (не требуется дополнительных вычислений, как например для недель, когда в разных месяцах укладывается разное количество недель). Конечно, такая детализация не очень удобна для коротких проектов, но, чтобы укрупненно продемонстрировать прогресс проектов (с длительностью от 1 года), такой подход вполне будет приемлемым.
Для построения простейшего графика, изображенного выше, нам потребуются следующие исходные данные:
- Длительность каждой работы
- Начало каждой работы (связи сделаем позже)
Месяц окончания каждой работы будем вычислять с помощью формулы =C14+D14-1 (к началу прибавим длительность) и скорректируем на 1, т.к. в начальный месяц работа также выполняется.
Чтобы чуть украсить график, можно для разных работ задать разные цвета. Для этого потребуется создать столько правил УФ, сколько разных цветов у нас будет на графике. Формула также чуть усложнится, также добавится столбец с кодом цвета.
Текущий месяц также можно отразить на графике с помощью УФ. На картинке выше текущий месяц выделен штриховкой — это июль 2022 года.
График со связями работ
Простейший график строить легко, но он не очень полезен. Чаще всего работы связаны между собой. Смоделируем связь Конец-Начало.
Добавим столбец с кодами работ и столбец для указания предшественника. Также в столбце, где указано начало работы вставим формулу:
Первая часть формулы просматривает столбец Предшественник («предшеств.», столбец С) и если находит пустую ячейку (нет предшественника, т.е. первая работа проекта), то устанавливает начало этой работы равной месяцу начала проекта (задается вручную в отдельной ячейке С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. Формула, с помощью которой это реализовано, почти полностью совпадает с предыдущей формулой, которую мы использовали для вычисления начала работы-последователя
Теперь в столбце G у нас введена величина запаздывания, которая может быть отрицательной величиной. В этом случае работа-последователь начнет выполняться еще до завершения работы-предшественника.
График со связями работ, запаздыванием и прогрессом выполнения работ
Планирование работ — это половина дела, необходимо еще и отслеживать выполнение планов. Прогресс отслеживания завершения работы не сложно реализовать в MS EXCEL с помощью Условного форматирования.
Сначала, чтобы не перегружать количеством правил УФ, избавимся от кодов цветов — сделаем представление планов работ одним (голубым) цветом. Выполненные работы будем отмечать темно-синим цветом. Всего у нас будет 2 правила УФ: один для плана, другой для факта выполненных работ.
Нам особо ничего не придется переделывать на листе — столбец с кодом цвета теперь будет использоваться для хранения прогресса выполненных работ в месяцах. Фактически мы будем указывать сколько месяцев из общей длительности работы нужно закрасить другим цветом.
Формула в правиле УФ очевидна = И(I$11>=$E14;I$11<=$E14+$H14-1)
Закрашиваться будут только те ячейки (месяцы), которые уже начались (>=E14) у данной работы И выполнены (<=E14+H14-1).
Примечание: Конечно, выполнение работы корректнее отслеживать с помощью метода освоенного объема, но и данный упрощенный подход позволяет оценить прогресс выполнения работы.
Если значение выполнения работы больше чем ее длительность, то это означает, что имело место увеличение плановой длительности.
Процент выполнения работ
Иногда указывать прогресс выполнения задачи удобнее в процентах от общего объема работ (в нашем случае — от ее общей длительности). Этот вариант также реализован в файле примера на другом листе.
Если раньше на диаграмме ганта у нас были пустые ячейки, то теперь у нас введена довольно длинная формула:
Она может вернуть 4 разных значения:
- -1: ячейка (соответствующий месяц) лежит вне периода выполнения работы, закрашивается белым;
- 0: работа в этом месяце еще не выполнена, ячейка закрашивается салатовым цветом;
- от 1 до 99: работа в этом месяце выполнена частично, ячейка закрашивается гистограммой пропорционально значению (50 соответствует половине закрашенной ячейки);
- 100: работа в этом месяце выполнена полностью, ячейка полностью синим цветом.
Для выделения текущей даты используется вертикальная линия, ее нужно двигать вручную.
График со связями работ, запаздыванием, прогрессом выполнения работ и прогнозом окончания
Работы не всегда выполняются четко по плану, чаще фактическая длительность больше, чем плановая. По мере выполнения работы зачастую становится понятно насколько ее окончание сдвинется. Смоделируем эту ситуацию в нашем графике, добавив еще один столбец — Прогноз. В этом столбце будем указывать сколько еще месяцев дополнительно к плановой длительности нам потребуется, чтобы завершить весь объем работы.
Пусть изначально плановый график выглядел так. Предполагалось, что длительность первой работы (самая верхняя строка) скорее составит 7 месяцев, а последующая работа начнется сразу после ее окончания.
После 8 месяцев выполнения проекта стало понятно, что в плановый срок первую работу выполнить не удалось. Необходимо обновить планы, но при этом нужно сохранить изначальную информацию о длительности работы.
Как видно из рисунка, длительность первой работы (самая верхняя строка) скорее составит теперь не 7 месяцев как планировалось, а 9 (+2 месяца указано в столбце «прогн», т.е. прогноз). Эти дополнительные 2 месяца выделены темно-оранжевым цветом. После добавления этих двух месяцев изменятся сроки начала связанных работ. Теперь последующая работа должна начаться с 11 месяца, а не с 9.
Прогноз реализован с помощью еще одного правила УФ:
График можно усложнять и дальше: добавлять новые типы связей, учитывать ресурсы/ финансы, использовать более точный расчет процента выполнения и др. Если такая потребность появляется на практике, то это как раз то самое время, когда необходимо воспользоваться специализированной программой.
А мы далее рассмотрим создание графика с детализацией по дням, он больше подходит для более коротких проектов.