Метод критического пути в excel
Конечной целью этой главы является составление календарного плана проекта. Как вы уже убедились, многие этапы процесса управления проектом взаимосвязаны друг с другом. Мы начали с определения конечных результатов проекта и требований, затем поставили задачи, оценили продолжительность их выполнения и расположили эти задачи в правильной последовательности. Далее нам нужно определить критический путь проекта, что поможет выяснить степень гибкости календарного плана по отношению к каждой задаче.
Критический путь проекта — это самый длинный путь проекта. Он включает те задачи, которые вообще не обладают гибкостью, или резервом времени. Резерв времени — это время, на которое вы можете задержать начало выполнения какой-либо задачи, не нарушая конечный срок завершения проекта, или время, на которое вы можете задержать начало выполнения какой-либо задачи, не оказывая влияния на начало выполнения задачи-последователя.
Чтобы проиллюстрировать, как вычисляется критический путь проекта, мы воспользуемся конечным результатом под названием Подготовить офисные помещения/комнаты. Вы заметите, что в этом примере некоторые задачи связаны между собой зависимостью типа «завершение-начало», а другие — зависимостью типа «начало-начало». Например, выполнение задачи Смонтировать и протестировать кабельные разъемы не может начаться до тех пор, пока не будет завершена задача Проложить кабели. Однако задачи Смонтировать настольные устройства и Смонтировать настенные устройства не требуют, чтобы к моменту начала их выполнения была завершена задача Смонтировать перегородки, разделяющие комнату на отдельные рабочие помещения.
Рис. 1 Критический путь
Иными словами, как только начнется монтаж перегородок, разделяющих комнату на отдельные рабочие помещения (выполнение соответствующей задачи началось), можно начинать сборку настольных и настенных устройств (выполнение задачи началось). Все перегородки необязательно должны быть смонтированы до того, как будут скомпонованы все настольные устройства. Следовательно, между этими задачами существует зависимость типа «начало-начало». На рис. 1 показано, как мы вычисляли критический путь для конечного результата Подготовить офисные помещения/комнаты (Prepare Offices/Rooms).
Вычисление прохода в прямом направлении
Сейчас вы увидите, как были определены даты раннего начала и раннего окончания для конечного результата Подготовить офисные помещения/комнаты (Prepare Offices/Rooms). Это вычисление называют проходом в прямом направлении. Обратите внимание: в этом примере мы не учитываем выходные и праздничные дни, но вы могли бы легко включить их, добавляя или вычитая соответствующее количество дней там, где это необходимо.
Начните с ввода даты 18 авг. в ячейке Е3, которая находится в столбце Early Start (Раннее начало). Мы определили эту дату в результате общения с подрядчиком, который будет заниматься прокладкой кабеля, а также в результате обсуждения наличия ресурсов, проведенного командой исполнителей проекта. Мы еще вернемся к вопросу наличия ресурсов. В ячейке F3 (столбец Early Finish (Раннее окончание)) прибавьте к дате раннего начала (значение в ячейке Е3) продолжительность выполнения соответствующей задачи (значение в ячейке D3) и из полученного результата вычтите единицу. В итоге вы получите дату раннего окончания (2 сент.). На рис. 2 показана формула, содержащаяся в ячейке F3.
Рис. 2. Проход в прямом направлении
Excel выполнит это вычисление автоматически и укажет соответствующую дату, если к ячейкам столбца F применен формат даты. Скопируйте эту формулу в нижележащие ячейки столбца F.
Вычисление прохода в обратном направлении
Теперь нам нужно вычислить проход в обратном направлении. Выполнение этого прохода позволяет нам определить даты позднего начала и позднего окончания. Последняя задача, Смонтировать настенные устройства (Install wall units), не может завершиться позднее 14 сентября (столбец Late Finish). Поскольку переезд работников назначен на 15 сентября, монтаж настенных устройств должен быть завершен к 14 сентября. Эта задача зависит от начала выполнения задачи Смонтировать перегородки, разделяющие комнату на отдельные рабочие помещения (Install cubicle walls), а это означает, что мы не можем завершить монтаж настенных устройств позже 14 сентября (предполагается, что монтажники настенных устройств работают буквально за спинами у монтажников перегородок).
Теперь в столбце Late Start (Позднее начало) мы выполним вычисление, противоположное тому, которое выполняли ранее, чтобы определить дату позднего начала. На рис. 3 показана формула, содержащаяся в ячейке G9.
Рис. 3. Проход в обратном направлении
Выполнение задачи Смонтировать перегородки, разделяющие комнату на отдельные рабочие помещения (Install cubicle walls) может начаться не позднее 8 сентября; даже в этом случае она будет закончена к дате позднего завершения. Когда вы доберетесь до задачи Проложить электропроводку (Run wiring ), то увидите, что она должна завершиться не позднее 2 сентября, т.е. за один день до того, как может начаться выполнение задачи Смонтировать перегородки, разделяющие комнату на отдельные рабочие помещения (Install cubicle walls) (позднее начало).
Вычисление критического пути
Рис. 4. Резерв времени
Резерв времени есть у трех задач: Проложить электропроводку (Run wiring), Смонтировать настольные устройства (Install desk units) и Смонтировать настенные устройства (Install wall units). Резерв времени всех остальных задач равняется нулю. Следовательно, задачами критического пути для этого проекта являются Проложить кабели (Run cable), Смонтировать и протестировать кабельные разъемы (Install and test jacks), Произвести осмотр электропроводки и протестировать ее (Inspect and test wiring) и Смонтировать перегородки, разделяющие комнату на отдельные рабочие помещения (Install cubical walls). Сложив вместе длительность выполнения каждой задачи, мы получим суммарную продолжительность выполнения всех задач, равную 36 дням.
Если даты начала или завершения этих задач критического пути будут просрочены, это повлияет на дату пол учения соответствующего конечного результата. Нетрудно заметить, что в случае выхода задачи Проложить кабели (Run cable) за рамки отведенного для нее времени дата начала задачи Смонтировать перегородки, разделяющие комнату на отдельные рабочие помещения (Install cubicle walls) также будет просрочена, поскольку она зависит от завершения задачи Проложить кабели (Run cable). Одна задача отодвигает дату начала выполнения другой задачи, что отдаляет дату получения соответствующего конечного результата. Если этот конечный результат находится на критическом пути проекта, это приведет к задержке выполнения проекта в целом.
Построим автоматическую сетевую диаграмму проекта. Сетевую диаграмму изобразим на диаграмме MS EXCEL типа Точечная. На этой диаграмме выведем работы проекта в виде точек, стрелками изобразим связи между работами. Также изобразим на диаграмме критический(ие) путь(и). Сетевая диаграмма будет автоматически перестраиваться при изменении связей между работами и их длительности.
Данная статья является третьей статьей в цикле статей посвященных построению сетевой диаграммы проекта и является синтезом первых двух:
- В статье Метод критического пути в MS EXCEL показано как рассчитать длительность проекта, определить работы на критическом пути и как построить сетевую диаграмму проекта на листе MS EXCEL. Основной недостаток - при изменении связей между работами пользователю потребуется вносить серьезное изменение в сетевую диаграмму.
- В статье Автоматическая сетевая диаграмма проекта в MS EXCEL показано, как имея перечень работ и связи между ними, вычислить все пути проекта и отобразить их на стандартной диаграмме типа Точечная. При изменении связей - пути автоматически пересчитываются. Однако, критический путь не вычислялся в этой статье.
Рекомендуется прочитать вышеуказанные статьи перед первым прочтением.
Ниже показана диаграмма, которую мы будем создавать в этой статье. На диаграмме отображены все работы проекта (от А до U, синие точки), связи между ними (стрелки), работы на критическом пути (красные точки), критический путь (красные стрелки).
Примечание : Слово диаграмма используется в 2-х смыслах: сетевая диаграмма проекта (стандартный термин из управления проектом, Activity-on-node diagram ) и диаграмма MS EXCEL ( Excel Chart , см. вкладку Вставка , группа Диаграммы ). Диаграмма MS EXCEL типа Точечная будет использоваться для построения сетевой диаграммы проекта.
При изменении связей между работами и/или их длительности происходит перерасчет критического пути и сетевая диаграмма автоматически обновляется. Например, значительное увеличение длительности работы М (в нижнем пути сетевой диаграммы) приведет к изменению критического пути.
Такая сетевая диаграмма отображает критический путь даже нагляднее, чем стандартная диаграмма Ганта , хотя, конечно, не заменяет ее.
ВНИМАНИЕ! Построение данной сетевой диаграммы в этой статье приведено лишь с целью демонстрации технической реализуемости такого построения в MS EXCEL. Не ставилось целью сделать "удобную программу для пользователей". Это означает, что при изменении пользователем количества работ/ добавления связей между работами (например, использования более 5 последователей), переименовании листов, рядов диаграммы и других изменений, в файле примера может потребоваться дополнительная настройка формул. Такая настройка потребует от пользователя серьезных знаний MS EXCEL и времени.
Исходная информация
В качестве исходной информации дан перечень работ, их длительность и связи между работами.
Также вручную задаются координаты для отображения на диаграмме работ (диапазон ВА12:ВВ34 ).
Другой информации для построения сетевой диаграммы и вычисления критического пути не требуется.
Вычисление путей сетевой диаграммы
Как и в статье Автоматическая сетевая диаграмма проекта в MS EXCEL начнем построение сетевой диаграммы с вычисления путей. В отличие от указанной статьи, здесь увеличено количество рассчитываемых путей (до 15) и максимальная длина путей (до 10 работ, включая вехи начала и окончания). Алгоритм вычисления путей аналогичен, однако формулы изменены, чтобы позволить пользователю быстро увеличивать количество путей проекта и их длину.
На шаге №0 определяются последователи вехи Старт . Количество последователей определяет количество путей на этом шаге. Формулы в строках 81-95 работают только для этого шага и их не нужно копировать на другие шаги (поэтому они выделены красным).
Начиная с шага №1 (начиная со строки 96) формулы можно копировать вниз и вправо, чтобы при необходимости увеличить количество путей и их длину (количество шагов). На каждом шаге длина путей увеличиваются на одну работу. В случае наличия нескольких последователей у работы - увеличивается количество возможных путей.
После вычисления всех шагов, в диапазоне R62:AA76 выводится перечень всех путей проекта, содержащие входящие в них работы.
Построение сетевой диаграммы
Сначала на диаграмме выводятся работы (синие точки, ряд Работы ).
Затем выводятся все возможные пути проекта (ряды Путь_1, Путь_2, . Путь_15 ).
Все работы, находящиеся на критическом пути, отмечаются красным цветом. Также на диаграмму выводится критический путь. Если путей несколько, то выводятся все пути (в файле примера выводятся максимум 3 критических пути). Если длительность работы О увеличить до 8 дней, то 3 пути станут критическими с длительностью по 65 дней.
Метод критического пути (МКП) - это метод планирования выполнения работ, в основе которого лежит математический алгоритм. Рассчитаем критический путь для связанных между собой работ проекта в 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. Это позволяет автоматически перестраивать сетевую диаграмму при изменении связей между работами.
Очевидно, что использование Сетевой диаграммы неудобно для расчетов критического пути (но очень наглядно), поэтому для диаграммы Ганта рассчитаем критический путь с помощью тех же формул, но организованных несколько другим образом.
Построение диаграммы Ганта
Для расчета критического пути без использования построения Сетевой диаграммы будем использовать тот же подход: сначала вычислим Раннее Начало и Раннее Окончание, затем вычислим Позднее начало и Позднее окончание . Формулы для расчета этих дат те же, что мы использовали в Сетевой диаграмме.
Для позднего окончания нам придется вычислить для каждой работы все ее Последующие работы. Это можно сделать из информации о предшествующих работах с помощью формулы массива :
Например, веха Старт является предшествующей работой для работ А, С, Е. Следовательно, ее последователи - работы А, С, Е. Именно это и делает формула.
На основании вычисленных дат и диаграммы Линейчатая с накоплением создадим диаграмму Ганта.
Первый ряд будет являться просто датой Раннего начала, следующие 2 ряда (критический путь и Не на критическом пути) - это просто длительность работ. 2 ряда создано для того, чтобы критический путь отобразить красным цветом. Также можно отобразить запас для каждой работы.
Обратите внимание, что работы начинаются с 0:00 первого дня. Например, значение 5 на горизонтальной оси диаграммы означает, что прошло ровно 5 дней: с 0:00 первого дня до 23:59 четвертого дня. Этот подход отличается от принятого в MS Project, где длительность работ отображается более наглядно:
Диаграмму Ганта принято отображать не в рабочих днях, а в календарных, для пересчета используем функцию РАБДЕНЬ() . Все вычисления приведены в столбцах AS:AW файла примера , лист Гант.
Примечание : Все расчеты сверены с MS Project (длительность проекта, вычисление критического пути, сроки начала и завершения работ).
Метод критического пути позволяет планировать расписание проекта и управлять его сроками. В основе метода лежит определение наиболее длительной последовательности задач от начала проекта до его окончания с учетом их взаимосвязи. Определив критический путь проекта с помощью Поиска решения , сравним результат в MS Project.
Задача
Проект состоит из 14 работ (задач). Работы начинаются и заканчиваются вехами (всего их 9). Заданы длительности работ и связи их друг с другом (см. также статью Диаграмма Ганта в MS EXCEL с % выполнения и связями между работами ).
Создание модели
Создадим линейную модель для решения задачи с помощью Поиска решения .
Совет : Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь .
Переменные (выделено зеленым) . В качестве переменных модели следует взять ячейки, содержащие признак (бинарный): лежит работа на критическом пути (=1) или нет (=0). Ограничения (выделено синим) . Необходимо, чтобы все задачи на критическом пути были соединены через вехи (критический путь должен проходить через все вехи), т.е. количество задач оканчивающихся на вехе, должно быть равно количеству задач выходящих из вехи (=1). Исключением являются Веха-Старт (из нее задачи только выходят) и Веха-Финиш (только входят). Целевая функция (выделено красным) . Длительность критического пути должна быть максимальной.
Примечание : для удобства настройки Поиска решения используются именованные диапазоны .
Найденное Решение
Поиск решения определит работы, которые лежат на критическом пути. Найденное решение можно сравнить с расчетом критического пути выполненном в MS Project.
Убедившись, что критические пути, рассчитанные в обеих программах, совпадают, в MS Project можно рассчитать длительность проекта.
Она строится следующим образом: столбцы соответствуют работам, а строки событиям. Если для дуги (i - j) начало соответствует i, а конец дуги соответствует j , то элемент матрицы в строке i будет равен -1, в строке j равен 1, а все другие элементы столбца равны 0.
1. Откройте новую книгу MS Excel и сохраните в своей папке под именем Сетевое проектирование.xls.
2. Переименуйте Лист1в лист Матрица инцидентностей.
3. Для обеспечения проверки вводимых значений в диапазон ячеекB3:Q10создайте список подстановки. Для этого:
· Выделите диапазон ячеек.
· Выполните команду Данные/Проверка…
· В окне Проверка вводимых значений на вкладке Параметры задайте Тип данных Список.
· В поле Источник введите значения: -1;1
4. В диапазон ячеек A11:Q11 введите продолжительность работ.
Путь – любая последовательность работ, в которой конечное событие каждой работы совпадает с начальным событием следующей за ней работы. Среди различных путей сетевого графика наибольший интерес представляет полный путь L – любой путь, начало которого совпадает с исходным событием сети, а конец – с завершающим.
Полными путями являются пути:
Критический путь имеет максимальную продолжительность.
Для вычисления критического пути введем переменные хi = 0, если ребро не принадлежит пути и хi =1, если принадлежит. Такие переменные называются булевыми или двоичными.
Рассмотрим функцию U(хi)= , где Ti – исходные значения продолжительности работ.
По условию эта функция для критического пути должна быть максимальной. Построим систему ограничений. Все ограничения имеют вид:
где bj = -1 – для начальной вершины,
bj = 1 – для конечной вершины,
bj = 0 для всех промежуточных вершин,
aij – элементы строки матрицы инцидентностей
Для начального события Ѕ0 (вершина, исходящая для всех путей): -х1-х2-х3= -1
Для седьмого события Ѕ7 (завершающего) х15 +х16=1
Начальные значения всех переменных примем равными 1.
Составим модель для поиска критического пути: Для этого:
5. В строке 12 введите переменные xi, равные 1.
6. В столбце R рассчитайте , воспользовавшись функцией СУММПРОИЗ.
7. В столбец Sвведите ограничения bj, учитывая, что bj = -1 – для начальной вершины, bj = 1 – для конечной вершины, bj = 0 для всех промежуточных вершин.
8. В ячейке R11 рассчитайте .
9. Сравните полученный результат с рисунком 3.
Рис. 3. Матрица инцидентностей.
10. Для того, чтобы рассчитать критический путь (максимальную продолжительность проекта), воспользуйтесь возможностями MS Excel по поиску решений. Для этого:
· Выполните команду Сервис/Поиск решений (Если данный модуль отсутствует, то предварительно установите его, выполнив команду Сервис/Надстройки/Поиск решения).
· В диалоговом окне Поиск решения установите параметры поиска решения согласно рис.4.
· Установите параметры модели – Линейная и Неотрицательные значения, щелкнув по кнопке [Параметры] диалогового окна Поиск решения.
Рис. 4. Диалоговое окно Поиск решения.
Где: целевая ячейка – $R$11 (сумма произведений Ti xi).
изменяемые ячейки – $B$12:$Q$12 (переменные хi ).
ограничения – ячейки столбца Σaijxi= bj, а также $B$12:$Q$12 = двоичное.
· Установите параметры модели – Линейная и Неотрицательные значения, щелкнув по кнопке [Параметры] диалогового окна Поиск решения.
· Щелкните по кнопке[Выполнить]и в окнеРезультат поиска решенияустановите опцию«Сохранить найденное значение»и выберитеТип отчета –Результаты.
11. По результатам поиска определите критический путь и сравните с рис. 5.
Рис. 5. Результат поиска решения.
Значение целевой функции равно 57 ед.
Таким образом, критический путь включает работы Р01Р12 Р23 Р 34 Р 45 Р 56Р67.
Этот путь или подпроцесс имеет максимальную продолжительность, и работы находящиеся на нем не имеют ни каких резервов времени. Критический путь определяет полное время завершения всех работ.
Читайте также: