Метод критического пути в EXCEL

Метод критического пути (МКП) - это метод планирования выполнения работ, в основе которого лежит математический алгоритм. Рассчитаем критический путь для связанных между собой работ проекта в MS EXCEL.

Метод критического пути (Critical Path Method) позволяет вычислить наиболее длительную последовательность работ, необходимую для завершения проекта в целом. Увеличение длительности любой из работ, лежащих на критическом пути приводит к увеличению длительности проекта. Работы, которые не лежат на критическом пути имеют определенный запас времени.

Постановка задачи

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

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

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

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

Для этого нам потребуется функция РАБДЕНЬ() , которая возвращает дату отстоящую от исходной даты на указанное количество рабочих дней. Подробности построения диаграммы Ганта приведено в соответствующем разделе этой статьи .

Исходные данные

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

Каждая работа имеет Название (для предотвращения загромождения текстом диаграммы здесь использованы латинские буквы от A до U) и код, который представляет собой последовательные числа от 1 до 21. Две работы не имеют длительности: Старт и Окончание.

Чтобы ввести связи - для каждой работы укажем предшествующие работы (максимум 5, столбцы D:H).

Примечание : Связи между работами можно задавать указывая не предшественников, а последователей. Это реализовано в статье Автоматическая сетевая диаграмма проекта в MS EXCEL .

Все остальные вычисления будут сделаны на основании этих данных.

Вычисление критического пути с помощью сетевой диаграммы

Длительность критического пути можно найти двумя способами. Первый, наиболее простой, - это определить все возможные пути от Старта до Окончания, вычислить их длительность и затем выбрать путь с максимальной длительностью. Если таких путей два или более, то оба пути будут критическими. Такой подход был реализован в статье Оптимизация графика работ по проекту с помощью Поиска решений в MS EXCEL .

В этой статье сделаем все "по-взрослому", т.е. воспроизведем в MS EXCEL математический алгоритм расчета критического пути. Для этого для каждой работы вычислим по 4 даты: Раннее Начало (Early Start), Позднее Начало (Late Start), Раннее Окончание (Early Finish) и Позднее Окончание (Late Finish). Затем, вычислим Запас по времени выполнения (Float или Slack) для каждой работы. Работа не лежащая на критическом пути будет иметь Запас . Для работ лежащих на критическом пути Запас равен 0. Запас вычисляется как Позднее Окончание минус Раннее окончание или как Позднее начало минус Раннее Начало .

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

Для этого каждую работу представим в виде таблички:

Название работы выведем в таблички с помощью ссылок на лист с исходными данными. Длительность можно получить с помощью формулы =ВПР(D9;Гант!$B$12:$C$32;2;0)

Сначала вычислим Раннее Начало и Раннее Окончание двигаясь от вехи Старт до вехи Окончание (прямой проход). Затем вычислим Позднее начало и Позднее окончание двигаясь от вехи Окончание до вехи Старт (обратный проход).

Примечание : Если бы проект состоял из 21 работы, которые следовали бы последовательно друг за другом, то путь выполнения работ был бы единственным и соответственно критическим. Но, вся проблема в том, что часть работ проекта могут выполняться параллельно (точнее - частично параллельно), например, С, Е и А.

На сетевой диаграмме сначала нарисуем веху Старт, ее длительность =0, и пусть она выполняется в день 0 (для удобства расчетов). Далее нарисуем вехи С, А, Е и их последователей: соответственно, D и E, F, M. Рассчитаем Раннее начало для этих и последующих работ:

• Раннее начало работы равно максимальной дате Раннего окончания всех предшествующих работ. Для всех работ С, А, Е единственной предшествующей работой является веха Старт, поэтому можно просто сослаться на ячейку В8 . Например, работа С начинается в 0:00 первого дня и заканчивается в 23:59 девятого дня. Можно, конечно начинать считать и с 1-го дня, а не с 0. В этом случае к дате Раннего окончания предшествующей работы нужно прибавить 1, а от даты Раннего окончания нужно отнять 1. Но, мы так делать не будем, т.е. еще одной причиной начинать с 0 является удобство отражения на диаграмме Ганта (см. этот раздел );
• Раннее начало работ K, R, S, U рассчитывается с помощью функции МАКС() , т.к. у этих работ более одного предшественника;
• Раннее окончание рассчитывается очень просто: Раннее начало + Длительность ;
• Веха Окончание содержит значение 65, т.е. проект будет длиться 65 рабочих дней;
• Теперь двинемся в обратную сторону - от вехи Окончание и вычислим Позднее окончание и Позднее начало ;
• Позднее окончание вычисляется как минимальная дата Позднего начала последующих работ. Если последующая работа только одна, то Позднее окончание равно Позднему началу последующей работы. Для последних работ, те что перед вехой Окончание , Позднее окончание равно дате вехи Окончание ;
• Позднее начало рассчитывает очень просто: Позднее окончание - Длительность .

Осталось вычислить Запас : Позднее окончание - Раннее окончание .

С помощью Условного форматирования выделим те работы, у которых Запас =0. Это критический путь.

Примечание : В статье Автоматическая сетевая диаграмма проекта с критическим путем в MS EXCEL показано как построить Сетевую диаграмму не на отдельном листе, а на диаграмме MS EXCEL. Это позволяет автоматически перестраивать сетевую диаграмму при изменении связей между работами.

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

Построение диаграммы Ганта

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

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

=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ($A11=$J$12:$N$33;СТРОКА($J$12:$J$33)-СТРОКА($J$11);"");AB$9);"")

Например, веха Старт является предшествующей работой для работ А, С, Е. Следовательно, ее последователи - работы А, С, Е. Именно это и делает формула.

На основании вычисленных дат и диаграммы Линейчатая с накоплением создадим диаграмму Ганта.

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

Обратите внимание, что работы начинаются с 0:00 первого дня. Например, значение 5 на горизонтальной оси диаграммы означает, что прошло ровно 5 дней: с 0:00 первого дня до 23:59 четвертого дня. Этот подход отличается от принятого в MS Project, где длительность работ отображается более наглядно:

Диаграмму Ганта принято отображать не в рабочих днях, а в календарных, для пересчета используем функцию РАБДЕНЬ() . Все вычисления приведены в столбцах AS:AW файла примера , лист Гант.

Примечание : Все расчеты сверены с MS Project (длительность проекта, вычисление критического пути, сроки начала и завершения работ).