Как построить сетевой график в excel

Календарно-сетевой график в неделях в 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 напротив текущей недели отображается красный маркер.

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

Календарь реализован одной формулой массива. Как это сделать показано в этой статье.

Результат

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

Создание сетевого графика в классической версии Project

Классический клиент Project Online Project профессиональный 2021 Project стандартный 2021 Project профессиональный 2019 Project стандартный 2019 Project профессиональный 2016 Project стандартный 2016 Project профессиональный 2013 Project стандартный 2013 Project 2010 Project стандартный 2010 Еще. Меньше

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

• Чтобы найти представление «Сетевой график», выберите «>сетевой график».

• Добавление легенды
• Автоматическое изменение способа окна
• Изменение способа окна вручную
• Изменение типа линии между полями
• Выберите тип сведений о задаче, которые нужно отбирать

Добавление легенды

1. Выберите «Файл>«>страницы».
2. На вкладке «Легенда» определите, как должна выглядеть легенда, на каких страницах она должна быть видна, а затем наклейте метки.
3. Нажмите кнопку ОК.

Автоматическое изменение способа окна

1. Выберите «>сетевой график».
2. Выберите «Формат >макета».

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

Изменение способа окна вручную

Если вы дошли до этого места, но по-прежнему не нравится расположение полей, нажмите кнопку «Формат > Макет»,выберите «Разрешить расположение полей вручную», нажмите кнопку «ОК», а затем перетащите поля в нужное место.

Если вы изменили положение задачи вручную, то можете изменить макет связанных с ней задач или подзадач, щелкнув задачу правой кнопкой мыши и выбрав «Макет связанных задач сейчас».

Изменение типа линии между полями

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

1. Выберите «>сетевой график».
2. Выберите «Формат >макета».
3. В области «Стиль ссылки»выберите «Прямоугольник» или «Прямой». Прямоугольные ссылки выглядят так

, а прямые ссылки выглядят так, как

Выберите тип сведений о задаче, которые нужно отбирать

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

1. Выберите «>сетевой график».
2. Выберите «Формат >полей».
3. В параметрах стиля для списка выберите задачу, которую вы хотите изменить.

Автоматическая сетевая диаграмма проекта с критическим путем в EXCEL

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

Данная статья является третьей статьей в цикле статей посвященных построению сетевой диаграммы проекта и является синтезом первых двух:

1. В статье Метод критического пути в MS EXCEL показано как рассчитать длительность проекта, определить работы на критическом пути и как построить сетевую диаграмму проекта на листе MS EXCEL. Основной недостаток — при изменении связей между работами пользователю потребуется вносить серьезное изменение в сетевую диаграмму.
2. В статье Автоматическая сетевая диаграмма проекта в 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 дней.

Одновременно, работы проекта отображаются на диаграмме Ганта.

ОПТИМИЗАЦИЯ СЕТЕВОГО ГРАФИКА КОМПЛЕКСА РАБОТ В EXCEL Текст научной статьи по специальности «Компьютерные и информационные науки»

Аннотация научной статьи по компьютерным и информационным наукам, автор научной работы — Катаргин Н.В.

Предлагается методика оптимизации сетевого графика по времени и числу работников с использованием сервиса Excel и методика построения плотности распределения времени окончания проекта методом Монте-Карло с Visual Basic при любом распределении вероятностей времени выполнения работ.

i Надоели баннеры? Вы всегда можете отключить рекламу.

Похожие темы научных работ по компьютерным и информационным наукам , автор научной работы — Катаргин Н.В.

Оптимизация сетевого графика выполнения комплекса работ
КОЛЕБАТЕЛЬНЫЕ ПРОЦЕССЫ В ЭКОНОМИЧЕСКИХ СИСТЕМАХ
СОСТАВЛЕНИЕ РАСПИСАНИЯ ЗАГРУЗКИ ОБОРУДОВАНИЯ В СРЕДЕ MS EXCEL
РЕШЕНИЕ НЕКОТОРЫХ СЕТЕВЫХ ЗАДАЧ В СРЕДЕ EXCEL
ФОРМИРОВАНИЕ СИСТЕМЫ КОНТРОЛЛИНГА КАК ЭФФЕКТИВНОГО ИНСТРУМЕНТА УПРАВЛЕНИЯ ПРЕДПРИЯТИЕМ
i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.
i Надоели баннеры? Вы всегда можете отключить рекламу.

Текст научной работы на тему «ОПТИМИЗАЦИЯ СЕТЕВОГО ГРАФИКА КОМПЛЕКСА РАБОТ В EXCEL»

Bulletin of the Research Center of Corporate Law, Management and Venture Capital of Syktyvkar State University. 2018. № 2

СВЯЗЬ И ИНФОРМАТИЗАЦИЯ

COMMUNICATION AND INFORMATIZATION

Оптимизация сетевого графика комплекса работ в Excel

Optimization of Network Schedule of Set of Operations in Excel

Н. В. Катаргин, Финансовый университет при Правительстве РФ (Россия, Москва)

N. V. Katargin, Financial University under the Government of Russian Federation

Предлагается методика оптимизации сетевого графика по времени и числу работников с использованием сервиса Excel и методика построения плотности распределения времени окончания проекта методом Монте-Карло с Visual Basic при любом распределении вероятностей времени выполнения работ.

Ключевые слова: сетевой график, оптимизация плана, Excel, VBA, стохастический процесс, метод Монте-Карло.

The method of optimization of the network schedule by time and number of employees using the Excel service and the method of building the density of the distribution of the project end time by Monte Carlo with Visual Basic method at any distribution of the probabilities of the work time are proposed.

Keywords: network, optimization, plan, Excel, VBA, stochastic process, method Monte-Carlo.

Задача сетевого планирования — построение рационального плана проведения слож-

© Н. В. Катаргин, 2018

ного комплекса работ (операций], при котором выполнение некоторых работ нельзя начать раньше, чем будут завершены другие, опорные работы. Под оптимизацией понимается сокращение затрат, срока завершения проекта и/или сокращение количества работников (или единиц техники], занятых в проекте, путем перераспределения ресурсов и работников. Предполагается, что время выполнения каждой работы уменьшается при увеличении ресурсов (денег] или числа работников. Во всех учебниках предлагается пошаговое перераспределение ресурсов с целью сокращения критического пути, т. е. времени завершения проекта [1—3]. В данной работе предложен принципиально новый подход к оптимизации по времени и работникам, основанный на использовании сервиса Поиск решения Excel, который использует итерационную градиентную процедуру (метод Ньютона, ОПГ] или же эволюционный (генетический алгоритм]. В результате все пути становятся одинаковыми или почти одинаковыми. Время выполнения работы — случайная величина, как и время выполнения всего проекта, с распределением вероятностей, возможно отличающемся от нормального

Вестник Научно-исследовательского центра корпоративного права, управления и венчурного инвестирования Сыктывкарского государственного университета. 2018. № 2

(Гаусса]. Если все пути одинаковы, невозможно применить традиционные статистические методы и требуется метод Монте-Карло. В данной работе он реализован в среде Visual Basic for Applications (Excel).

На рисунке 1 приведён пример сетевого графика, соответствующего выполнению некоего проекта.

Обычно оптимизацию плана комплекса работ проводят после нахождения критических работ, напряжённостей работ и ресурсов, которые можно перебросить с некритических работ на критические. В данном случае критический путь 1 => 4 => 6 => 7 => 10, соответственно, новое время выполнения комплекса работ t(X), где X — дополнительные ресурсы:

+t6(X) + t7(X) + tw(X). (1]

Предполагается, что время выполнения работ можно сократить, вкладывая дополнительные ресурсы, причём сокращение времени пропорционально дополнительным ресурсам Х:

Под ресурсами можно понимать деньги, людей, технику. На рисунке 2 видно, что используемая линейная зависимость время/затраты справедлива в некотором диапазоне; существует асимптота слева (затраты, при которых работа никогда не будет сделана] и справа (минимальное время выполнения работы при любых затратах].

Рис. 2. Зависимость времени работы от дополнительных затрат

О 5 Ю 15 20 25 30

Рис. 1. Сетевой график до (А) и после оптимизации по времени (Б)

Bulletin of the Research Center of Corporate Law, Management and Venture Capital of Syktyvkar State University. 2018. № 2

Ограничения дополнительных затрат обозначены Х- (сколько можно вычесть] и Х+ (сколько можно добавить]. Эти величины могут отличаться по модулю. Можно ввести ограничения по времени выполнения работ, но они будут пропорциональны дополнительным затратам. Величины Cmin и Cmax — полные стоимости работ для обеспечения их выполнения за максимальное tmax и минимальное время tmin соответственно.

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

В данной работе предлагается оптимизация сетевого графика, основанная на использовании итерационной градиентной процедуры, включённой в сервис Поиск решения (Solver) электронных таблиц Excel. Исходные данные, соответствующие сетевому графику (рис. 1, А], и расчётные формулы размещаются в таблице 1. t(X) работ вычисляется по формуле (2], в данном примере b = 0,1. Без дополнительных затрат tKpum = 32. Целевая функция t^um, её надо минимизировать, изменяя ячейки вектора X. Ограничения: X > Х-, X < Х+, S Х = 0, то есть ресурсы перераспределяются внутри проекта, дополнительных затрат нет. В данном примере ограничения затрат Х- = -50, Х+ = 50. В ре-

альных проектах ограничения и коэффициенты эффективности затрат Ь устанавливаются экспертами для каждой работы отдельно, в таблице появляются ещё три столбика-вектора:

Если мы запустим Поиск решения, в результате максимальных вложений в критические работы путь 1=>4=>6=>7=>10 сократится до 12, но другие пути удлинятся, и время выполнения проекта удлинится до 35,31. Очевидно, в ограничения Поиска решения надо вводить недопустимость удлинения других путей по сравнению с критическим. Но даже в нашей простой задаче это приводит большому количеству ограничений, так как надо предусмотреть все возможные пути. Поэтому предлагается принципиально новая технология расчёта, основанная на понятии опорных событий, а не опорных работ, как обычно, и вычислении времён наступления событий. Опорные события — это события, непосредственно предшествующие событию, время наступления которого вычисляется, и связанные с ним стрелками-работами. В таблице 1 представлены результаты расчётов, а на рисунке 1Б — соответствующий сетевой график. ЦХ) событий вычисляются в столбцах G, Н, I, J. Если имеется только одно опорное событие, то время наступления события складыва-

C D E F G H I J K L

5 События Опорные события Работа t (X] работ Макс t(X) t0 работ Х

6 событий t X) событий

7 1 .1-2 12,08 0 10 -20,85

8 .1-3 8,60 8 -6,048

9 .1-4 3,34 6 26,575

10 2 1 .2-5 4,12 12,08 5 8,7497

11 3 1 .3-5 7,60 8,60 7 -6,048

12 4 1 .4-б 7,б5 3,34 9 13,448

13 5 2, 3 .5-7 4,69 16,21 16,2 16,20 6 13,036

14 .5-8 6,94 6 -9,425

15 б 4 .б-7 9,90 10,99 10,99 8 -19,08

16 .6-8 5,58 4 -15,89

17 .6-9 5,06 5 -0,619

18 7 5, 6 .7-10 б,2б 20,90 20,90 20,90 9 27,385

19 8 5, 6 .8-10 4,01 23,15 23,15 16,58 3 -10,15

20 9 6 .9-10 11,10 16,06 16,06 11 -1,079

21 10 7, 8 , 9 27,168 27,16 27,16 27,16

Вестник Научно-исследовательского центра корпоративного права, управления и венчурного инвестирования Сыктывкарского государственного университета. 2018. № 2

ется из времени наступления опорного события и времени соответствующей работы ^(Х). Если опорных событий несколько, то время наступления события вычисляется по всем опорным событиям в столбцах Н, I, J, и максимум по этим ячейкам принимается за ЦК) события в столбце G. Если для каких-либо событий опорных событий больше, то и количество соответствующих столбцов должно быть больше. Дополнительные ограничения: время критического пути, вычисленное по формуле (1), должно быть больше или равно времени наступления конечного события 10, вычисленных в последних трёх ячейках соответствующей строки ^23 > Н21, 121, J21). Окно Поиска решения представлено на рисунке 3, результаты расчётов — в таблице 1. Полученный результат: 1крит = 27,168, остальные пути, приводящие к событию 10, то есть к окончанию проекта, имеют ту же длину.

Пояснения: 1(Х) событий = Макс@(Х) событий (опорных) + ЦХ) работ). Здесь 010 = F7, 011 = F8, 012 = F9, Н13 = 010 + F10, 113 = 011 +

F11, 013 = МАКС(Н13:Д3), скопировать эту формулу до 021, Н15 = 012 + F12, Н18 = 013 + F13, 118 = = 015 + F15, Н19 = 013 + F14, 118 = 015 + F16, Н20 = = 015 + F17, Н21 = 018 + F18, 121 = 019 + F19, 121 = = 020 + F20, F23 = F9 + F12 + F15 + F18.

В Поиске решения Целевая ячейка №рит F23, минимизировать, Изменяя ячейки X (Ь7^20), убрать галочку в «Сделать переменные без ограничений неотрицательными».

Для удобства расчётов столбцы Ь0 работ и Х расположены справа, т. к. они не задействованы в расчётах Макс 1(Х) событий.

Возможна другая постановка задачи: вычислить и минимизировать количество дополнительных ресурсов ЕХ для достижения заданной величины 1крит. В этом случае целевой ячейкой Поиска решения становится ЕХ и устанавливается ограничение < крит.

Задав ЕХ = 10, можно оценить коэффициент эффективности дополнительных ресурсов Ь для проекта в целом (Ь = Л1крит /ЕХ). В данном случае Ь = 0,03, т. е. втрое меньше Ь, для отдельной работы.

Параметры поиска решения

Оптимизировать целевою функцию: До:

О Максимум @ Минимум Изменяя ячейки переменных:

В соответствии с ограничениями:

Рис. 3. Окно Поиска решения 132

Bulletin of the Research Center of Corporate Law, Management and Venture Capital of Syktyvkar State University. 2018. № 2

В данном случае события 2 и 6, 5 и 9 происходят почти одновременно. Х — изменение количества работников на операциях, целое. К таблице 1 в Excel добавляется таблица 2. В ней Интервал времени соответствует рисунку 4, Рабочих0 — исходное количество работников, Рабочих(Х) = = Рабочих0 + Х, 2рабочих формируется в соответствии с работами в интервалах времени рисунка 4. Столбец Работа скопирован для удобства. Целевая функция — минимальное количество работающих в любом из временных интервалов, указанных на рисунке 4 (после оптимизации по времени), здесь — минимум по столбцу О (2 рабочих). Ограничено 1крит, в данном случае

0 5 Ю 15 20 25 30

Рис. 4. Оптимизированный по времени сетевой график с временными интервалами

1 b 1 Интервал времени Х рабочих Рабочих0 Рабочих (Х) Работа

2 X- -2 7 .1-4 =Q7+Q8+Q9 10 8 .1-2

3 X+ 2 8 .4-3 =Q7+Q8+Q12 8 10 .1-3

9 .3-2,6 =Q7+Q11+Q12 6 8 .1-4

23 Шрит 30 10 .2,6-5,9 =Q7+Q11+Q15+ Q16+Q17 5 7 .2-5

25 30 12 .7-8 =Q14+Q16+Q18+ Q20 9 9 .4-6

13 .8-10 =Q18+Q19+Q20 6 5 .5-7

16 Целевая =МАКС(О7:О13) 4 2 .6-8

i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

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

Оптимизация по количеству работников

Оптимизация по количеству работников позволяет распределить работников по операциям таким образом, чтобы их количество на каждом временном интервале было минимальным. Её можно проводить как после оптимизации по времени, так и независимо от неё.

Вестник Научно-исследовательского центра корпоративного права, управления и венчурного инвестирования Сыктывкарского государственного университета. 2018. № 2

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

Оценка времени выполнения оптимизированного проекта методом Монте-Карло

Время выполнения каждой работы и всего проекта — случайные величины. Поэтому обычно для каждой работы задаются три оценки: оптимистическая (а), наиболее вероятная (т), пессимистическая (Ь).

Среднюю продолжительность работы Ье вычисляют по формулам Ье = (а+4т+Ь)/6 или Ье = = (2а + 3Ь)/5.

Стандартное отклонение продолжительности операции вычисляют по формуле стс = (Ь — а)/6.

Дисперсию времени выполнения проек-

та можно оценить по формуле а21хит = Ео2н, где ст2и — дисперсии продолжительностей критических работ.

Если длительности работ не зависят друг от друга и подчиняются нормальному закону распределения, то можно вычислить вероятности различных сроков завершения проекта и вероятность превышения срока по сравнению с заданным (квантиль). Если сетевой график оптимизирован и длительности различных путей совпадают, то любой путь может оказаться критическим, и сложность расчётов резко возрастает. Кроме того, в экономике часто работает не закон нормального распределения (Гаусса), а закон распределения с «толстыми хвостами», например логнормальный. «Толстый хвост» означает, что вероятности аномальных длительностей работ (>3ст) достаточно велики.

Предлагаемая технология позволяет построить распределение вероятностей длительности проекта для любого распределения вероятных длительностей работ, полученного на основании экспертных оценок. Предположим, что на основе экспертных оценок построено распределение возможных длительностей работ, представленное на рисунке 4, и для каждой работы оценены масштабные коэффициенты «ширины» распределения Si, аналоги стандартного отклонения, представленные в таблице 4. Для проведения расчётов удобен метод Монте-Карло: имитированные длительности всех работ Ьимит многократно варьируются случайным образом в соответствии с законом распределения и Si :

Событие t события Ц рабочих

С D Е F 0 Н I I К

События Работа S ^имит Ъмит событий ^плаи

1 1-2 2 12,1 12,08

2 2-5 2 2,9 12,1 4,12

3 3-5 2 6,2 8,8 7,60

4 4-6 3 6,8 3,3 7,65

5 5-7 2 4,7 15,0 15,0 15,0 4,69

6 6-7 2 10,1 10,1 9,90

7 7-10 2 5,3 20,2 19,7 20,2 6,26

8 8-10 1 4,4 22,2 22,2 15,4 4,01

9 9-10 3 11,1 15,2 11,10

10 Т проекта 26,6 25,5 26,6 26,3

Bulletin of the Research Center of Corporate Law, Management and Venture Capital of Syktyvkar State University. 2018. № 2

где % — плановая длительность работы, G — случайная величина, распределённая по закону, представленному на рис. 5.

■1 -0,5 0 0,5 1 1,5 2 2,5

Рис. 5. Эмпирический закон распределения длительности работы

Затем вычисляются времена наступления событий, в том числе конечного (Т проекта), которое может отличаться от длительности старого критического пути tKpum. таблица 4 создаётся копированием таблицы 1, и алгоритм в столбцах G, H, I, J работает как «чёрный ящик», формируя Т проекта по tUMum. Процедура повторяется многократно (1000 и более раз), получаемые значения Т проекта сохраняются и по ним строится гистограмма частотных распределений (используя в Excel сервис Анализ данных — Гистограмма), а также, если надо, вычисляются среднее значение и стандартное отклонение (используя функции СРЗНАЧ и СТАНДОТКЛОН). Для генерации случайной величины G и tUMUm, а также сохранения вычисленных значений Т проекта использован программный модуль на языке Visual Basic.

Процедура повторена 1000 раз. Результаты одного из циклов процедуры представлены в таблице 4, гистограмма вероятностей длительности проекта представлена на Рисунке 6. По частотам длительностей можно оценить, например, вероятность длительности проекта более 34 дней в 3,5 %, а более 35 дней — в 1,8 %. Гистограмма показывает, что при стохастическом характере длительностей работ время выполнения проекта увеличивается, причём может увеличиться очень существенно — на 10 дней.

В приложении 1 представлен программный модуль на языке Visual Basic for Applications (Excel) для имитации timit и сохранения Т проекта, а в Приложении 2 — таблица, используемая программой после преобразования в 4 столбца. Нормальное распределение создаётся проще: в ячейке, например N9, помещается функция =НОРМ.СТ.ОБР(СЛЧИС()), в программе q=Range(«N9») aa(j,3)=aa(j)+s*q ‘Формирование timit строки между ними и Next j удаляются.

Использование сервиса Excel Поиск решения (Solver) позволяет эффективно оптимизировать сетевой график выполнения комплекса работ. Метод Монте-Карло позволяет оценить вероятности сроков окончания проекта при любом законе распределения длительностей работ. Данную работу можно использовать для планирования на производстве, а также в качестве лабораторной работы для обучения студентов.

1. Горошникова Т. А., Сунчалин А. М. Математические методы в управлении проектами: уч. пособие. М.: Финансовый университет, 2013. С. 46—94.

2. Исследование операций в экономике: уч. пособие / под ред. Н. Ш. Кремера. М.: Юрайт, 2013. С. 315— 356. (URL: http://static.my-shop.ru/product/pdf/134/1332999.pdf)

3. Экономико-математическое моделирование / под ред. И. Н. Дрогобыцкого. М.: Экзамен, 2004. С. 347—

1. Goroshnikova T. A., Sunchalin A. M. Matematicheskie metodi v upravlenii proektami [Mathematical Methods at the Project Management]. Moscow, Financial University, 2013, p. 46—94.

2. Issledovanie operatsii v ekonomike [Investigation of operations in economics]. Edited by N. Sh. Kremer. Moscow, Jurait, 2013. p. 315—356.

3. Ekonomiko-matematicheskoe modelirovanie [Economy-Mathematical Modelling]. Edited by I. N. Drogobit-ski. Moscow, Ekzamen, 2004, p. 347—354.

Вестник Научно-исследовательского центра корпоративного права, управления и венчурного инвестирования Сыктывкарского государственного университета. 2018. № 2

Программный модуль на языке Visual Basic for Applications (Excel)

Private Sub CommandButton1 Click()

Создание трёх массивов-диапазонов ячеек Excel Массив t работ, S, timit . В S30 число 12,1

Массив для сохранения tкрит, в любом месте Массив Таблица 5, в А2 0 число -0,9 Количество имитаций в N10

Цикл по столбцам массива аа Случайное число в диапазоне 0. 1 Преобразование q в случайную величину, распределение которой табулировано в массиве gg

2) ‘ Формирование timit ‘ (Расчёт Т проекта происходит в таблице Excel) ‘ Сохранение T проекта

Dim aa, dd, gg As Range Set aa = Range(«S30») Set dd = Range(«F20») Set gg = Range(«A20») nn = Range(«N10») For N = 1 To nn For j = 1 To 14 q = Rnd() For k = 2 To 31 If gg(k, 2) >= q Then

s =( gg(k) + gg(k-1))/2 Exit For End If : Next k aa(j, 3) = aa(j) + s * aa(j, Next j

dd(N) = Range(«V44») Next N End Sub

Преобразование эмпирических частот y(t) в кумулятивное распределение £P(t), нормированное на 1. P(t) — плотности вероятностей, полученные делением y(t)/£y(t), £P(t) — интеграл от P(t), формируется добавлением P(t) к предыдущему значению £P(t)

t S P(t) y(t) t S P(t) y(t)

-0,9 0,0019 0,0019 1 0,7 0,9193 0,0106 5,6

-0,8 0,0057 0,0038 2 0,8 0,9296 0,0102 5,4

-0,7 0,0114 0,0057 3 0,9 0,9382 0,0085 4,5

-0,6 0,0228 0,0114 6 1 0,9458 0,0076 4

-0,5 0,0438 0,0209 11 1,1 0,9517 0,0059 3,1

-0,4 0,0762 0,0324 17 1,2 0,9588 0,0070 3,7

-0,3 0,1296 0,0533 28 1,3 0,9641 0,0053 2,8

-0,2 0,2134 0,0838 44 1,4 0,9689 0,0047 2,5

-0,1 0,3602 0,1467 77 1,5 0,9733 0,0043 2,3

0 0,5346 0,1744 91,5 1,6 0,9790 0,0057 3

0,1 0,7024 0,1677 88 1,7 0,9847 0,0057 3

0,2 0,8009 0,0985 51,7 1,8 0,9904 0,0057 3

0,3 0,8511 0,0501 26,3 1,9 0,9942 0,0038 2

0,4 0,8816 0,0304 16 2 0,9980 0,0038 2

0,5 0,8968 0,0152 8 2,1 1 0,0019 1

0,6 0,9086 0,0118 6,2 Сумма 524,6

Для цитирования: Катаргин Н. В. Оптимизация сетевого графика комплекса работ в Excel // Корпоративное управление и инновационное развитие экономики Севера: Вестник Научно-исследовательского центра корпоративного права, управления и венчурного инвестирования Сыктывкарского государственного университета. 2018. № 2. C. 129—136.

For citation: Katargin N. V Optimization of Network Schedule of Set of Operations in Excel // Corporate governance and innovative economic development of the North: Bulletin of the Research Center of Corporate Law, Management and Venture Capital of Syktyvkar State University. 2018. № 2. P. 129—136.