Как построить сетевую модель в excel
Перейти к содержимому

Как построить сетевую модель в excel

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

history 30 декабря 2017 г.
    Группы статей

  • Диаграммы и графики
  • Управление проектами

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

В статье Метод критического пути в MS EXCEL сетевая диаграмма проекта была построена на листе MS EXCEL.

К сожалению, при изменении связей между работами данную диаграмму необходимо перестраивать в ручную, что может быть достаточно трудоемко. Чтобы этого избежать, используем диаграмму типа Точечная (XY Scatter) , на которой точками обозначим работы, а стрелками — связи между работами.

СОВЕТ : Подробнее о построении диаграмм см. статью Основы построения диаграмм в MS EXCEL .

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

Предположим, что нам требуется отобразить связи проекта, состоящего из 7 работ (от А до G), также заданы вехи начала (Start) и окончания проекта (Finish).

Как видно из диаграммы, связи между работами заданы так, что существует 3 пути:

  1. Start-A-D-Finish
  2. Start-B-E-G-Finish
  3. 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).

Вычисление путей

Алгоритм вычисления путей следующий:

  1. Для вехи Start (код=1) определяются ее последователи, т.е. работы А, В и С, которые имеют коды соответственно 2, 3, 4 (см. шаг 0, строка 37), а также количество последователей у каждой из работ А, В и С. Кроме того, код вехи Start необходимо повторить в столбце В столько раз, сколько у нее последователей. На данном шаге количество путей равно 3, т.е. равно количеству последователей вехи Start;
  2. На следующем шаге определяются последователи работ А, В, С, т.е. работы D, E, F (коды 5, 6, 7). См. ячейку J53 . В соседнем столбце справа вычисляется количество последователей этих работ. Как видно из диаграммы выше, работа D имеет 2 последователя. Это приводит к тому, что количество путей проекта увеличивается до 4-х. Поэтому нужно обновить количество вех Start до 4-х (см. ячейку G53 ). Это можно сделать используя идеи из статьи Восстанавливаем последовательности из списка без повторов в MS EXCEL .
  3. Аналогично на следующих шагах определяются следующие работы-последователи и обновляется количество возможных путей проекта. По результатам каждого шага производится проверка достижения вехи 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 Project

Цель: изучить возможности по использованию MS Excel и MS Project для решения задач планирования экономических процессов.

Научиться строить сетевой график выполнения проекта и определять критический путь средствами MS Excel.

Изучить возможности MS Project как средства управления проектами.

В настоящее время система сетевого планирования и управления (СПУ) является одним из эффективных методов по организации и управлению проектами. Система СПУ позволяет:

Формировать календарные планы реализации проектов;

Определять наиболее проблемные операции при реализации проектов;

Выявлять резервы времени, трудовые, материальные и финансовые ресурсы.

Задача.

При составлении проекта работ выделено 8 событий: (0,1,2,3,4,5,6,7), которые связаны работами (i – j ), где i,j 0,1,2,3…,7 и i ≠ j, например, событие 1 связано с событием 2 работой (1-2).

Исходные данные по продолжительности работ

Работа 0-1 0-2 0-3 1-2 1-3 1-4 2-3 2-4 2-5 3-4 3-5 4-5 4-6 5-6 5-7 6-7
Длит. дни

Требуется:

Построить сетевой график выполнения проекта.

Определить критический путь.

Ход выполнения:

Данная задача относится к классу задач сетевого планирования и решается методами булева программирования.

Задание 1. Построение сетевого графика выполнения проекта.

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

Так как исходные данные представлены работами, то из их анализа видно, что процесс начинается событием Ѕ0 и заканчивается событием Ѕ7. Все остальные события являются промежуточными.

Нарисуем график процесса, размещая события в последовательности: событие Ѕ0 – крайне левое, Ѕ7 – крайнее правое, если событие имеет номер i≤j ,то оно изображается левее, любые события связываются одной стрелкой. С каждой стрелкой свяжем число, продолжительность работы (рис.8.1).

Рис.8.1. Сетевой график проекта

Получим рисунок, который называется сетевым графиком проекта.

Задание 2. Определение критического пути в MS Excel

С сетевым графиком связана таблица, которая называется матрицей инцидентностей (рис.8.2).

Рис. 8.2. Матрица инцидентностей

Она строится следующим образом: столбцы соответствуют работам, а строки событиям. Если для дуги (i — j) начало соответствует i, а конец дуги соответствует j , то элемент матрицы в строке i будет равен -1, в строке j равен 1, а все другие элементы столбца равны 0.

Откройте новую книгу MS Excel и сохраните в своей папке под именем Сетевое проектирование.xls.

Переименуйте Лист1 в лист Матрица инцидентностей.

Для обеспечения проверки вводимых значений в диапазон ячеек B3:Q10 создайте список подстановки. Для этого:

Выделите диапазон ячеек.

Выполните команду Данные/Проверка…

В окне Проверка вводимых значений на вкладке Параметры задайте Тип данных Список.

В поле Источник введите значения: -1;1

В диапазон ячеек A11:Q11 введите продолжительность работ.

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

Полными путями являются пути:

Критический путь имеет максимальную продолжительность.

Для вычисления критического пути введем переменные хi = 0, если ребро не принадлежит пути и хi =1, если принадлежит. Такие переменные называются булевыми или двоичными.

Рассмотрим функцию U(хi)= , где Ti – исходные значения продолжительности работ.

По условию эта функция для критического пути должна быть максимальной. Построим систему ограничений. Все ограничения имеют вид:

где bj = -1 – для начальной вершины,

bj = 1 – для конечной вершины,

bj = 0 для всех промежуточных вершин,

aij – элементы строки матрицы инцидентностей

Для начального события Ѕ0 (вершина, исходящая для всех путей):

Для седьмого события Ѕ7 (завершающего) х1516=1

Начальные значения всех переменных примем равными 1.

Составим модель для поиска критического пути:

В строке 12 введите переменные xi, равные 1.

В столбце R рассчитайте , воспользовавшись функцией СУММПРОИЗ.

В столбец S введите ограничения bj, учитывая, что bj = -1 – для начальной вершины, bj = 1 – для конечной вершины, bj = 0 для всех промежуточных вершин.

В ячейке R11 рассчитайте .

Сравните полученный результат с рисунком 8.3.

Рис. 8.3. Матрица инцидентностей

Для того, чтобы рассчитать критический путь (максимальную продолжительность проекта), воспользуйтесь возможностями MS Excel по поиску решений. Для этого:

Выполните команду Сервис/Поиск решений (Если данный модуль отсутствует, то предварительно установите его, выполнив команду Сервис/Надстройки/Поиск решения).

В диалоговом окне Поиск решения установите параметры поиска решения согласно рис.8.4.

Установите параметры модели – Линейная и Неотрицательные значения, щелкнув по кнопке [Параметры] диалогового окна Поиск решения.

Рис. 8.4. Диалоговое окно Поиск решения

Где: целевая ячейка – $R$11 (сумма произведений Ti xi).

изменяемые ячейки – $B$12:$Q$12 (переменные хi).

ограничения – ячейки столбца Σaijxi= bj, а также $B$12:$Q$12 = двоичное.

Установите параметры модели – Линейная и Неотрицательные значения, щелкнув по кнопке [Параметры] диалогового окна Поиск решения.

Щелкните по кнопке [Выполнить] и в окне Результат поиска решения установите опцию «Сохранить найденное значение» и выберите Тип отчета – Результаты.

По результатам поиска определите критический путь и сравните с рис. 8.5.

Рис. 8.5. Результат поиска решения

Значение целевой функции равно 57 ед.

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

Задание 3.Построение сетевого графика и определение критического пути в MS Project.

Программа MS Project предназначена для создания и управления графиками выполнения проектов на основе технологий сетевого планирования.

Окно системы приведено на рис. 8.6.

Рис. 8.6. Окно MS Project

Слева расположена Панель консультанта (Вид /Панель инструментов/Консультант).

В рабочей области находится Диаграмма Ганта (Вид/Диаграмма Ганта), которая состоит из Панели для ввода задач (работ) и Панели протяженности работ (диаграмма выполнения проекта)

Рассмотрим выполнение проекта представленного выше.

Ход выполнения:

Установите дату начала выполнения проекта, выполнив команду Проект/Сведения о проекте, согласно рис. 8.7.

Рис. 8.7. Сведения о проекте

Введите названия задач и их продолжительность (исходные данные) согласно рис 8.8. Столбцы Начало и Окончание заполнятся автоматически, столбец Предшественники заполняется перечислениями через точку с запятой номерами предшествующих работ.

Рис. 8.8. Область задач диаграммы Ганта

В крайнем правом столбце отражается диаграмма выполнения работ (рис. 8.9).

Рис. 8.9. Фрагмент диаграммы Ганта

Рассмотрите сетевой график (Вид/Сетевой график). Работы критического пути отражены на сетевом графике красным цветом, но можно их рассмотреть отдельно, выполнив команду Проект/Фильтр /Критические задачи.

Определите, какова продолжительность критического пути, и какие работы он в себя включает. Сравните полученный результат с расчетами, проведенными в MS Excel. Для определения сроков выполнения проекта можно также выполнить команду Проект/Сведения о проекте (в диалоговом окне кнопка Статистика.)

Для того чтобы уточнить продолжительность рабочей недели, выберите на панели пункт меню Задачи , и далее пункт «Определение рабочего времени проекта».

На первом шаге мастера укажите шаблон календаря «Стандартный».

На втором шаге мастера укажите рабочие дни проекта.

На третьем шаге мастера выберите пункт «Изменить рабочее время» и сделайте нерабочими днями 7.11.08, 25.12.08, 26.12.08, 01.01.09, 02.01.09, 07.01.09. Для дней 20.12.08 и 10.01.09 установите опцию Нестандартное рабочее время.

На четвертом шаге мастера определите единицы времени.

На пятом шаге сохраните внесенные изменения.

Для того чтобы связать с сетевым графиком ресурсы (например, работников и их зарплату), выберите на панели пункт меню Ресурсы, и далее перейдите по ссылке «Выбор людей и оборудования для проекта», на следующем шаге выберите опцию «Ввести ресурсы вручную». Создайте каталог трудовых ресурсов согласно рис.8.10. (Для всех ресурсов в диалоговом окне «Сведения о ресурсе» на вкладке «Общие» укажите тип ресурса «Трудовой»)

Рис. 8.10. Трудовые ресурсы проекта

После того, как создан каталог трудовых ресурсов, нажмите на кнопку [Готово].

Для того чтобы связать ресурсы с задачами в окне Ресурсы перейдите по ссылке «Назначение людей и оборудования задачам». Выделите задачу 0-1 и выполните команду «Назначить ресурсы». Назначьте ресурсы задачам согласно рис.8.11.

Рис. 8.11. Назначение ресурсов задачам

После того, как ресурсы назначены, нажмите кнопку [Готово] и просмотрите лист ресурсов, выполнив команду Вид/Лист ресурсов. Обратите внимание, что Иванов и Сидоров на листе ресурсов выделены красным цветом.

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

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

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

Для добавления новых столбцов в область задач на диаграмме Ганта, необходимо выделить столбец, перед которым желаете вставить новый, из контекстного меню выбрать команду Вставить столбец и в диалоговом окне «Определение столбца» указать имя вставляемого поля. Добавьте столбец Трудозатраты после поля Длительность.

Для определения суммарных трудовых и финансовых затрат выполните команду Проект/Сведения о проекте (в диалоговом окне кнопка Статистика).

Для отслеживания хода выполнения проекта выберите на панели пункт меню Отслеживание, перейдите по ссылке «Подготовка к отслеживанию хода работы над проектом», на первом шаге мастера установите опцию Нет, на втором шаге мастера выберите способ отслеживания «Всегда отслеживать путем указания процента завершения по трудозатратам» и новом поле «% завершения по трудозатратам» для работ 0-1, 0-2, 0-3 установите 100% — е завершение. Вернитесь в окно Отслеживание и перейдите по ссылке «Проверка хода выполнения проекта». Указав любую дату, просмотрите индикатор выполнения задач проекта.

MS Project позволяет формировать различные виды отчетов. Для составления отчетности выберите на панели пункт меню Отчет, установите опцию «Напечатать отчет о проекте» и перейдите по ссылке «Показать отчеты». В диалоговом окне выберите категорию отчета, например, Загрузка и укажите вид отчета «Использование ресурсов». Просмотрите другие виды отчетности.

Задание 4. (самостоятельно).

При составлении проекта работ выделено 8 событий:(0,1,2,3,4,5,6,7), которые связаны работами (i –j ), где i,j 0,1,2,3…,7 и i ≠ j , например событие 1 связано с событием 2 работой (1-2).Определено штатное расписание для выполнения проекта в составе:

Руководитель проекта (РП), стандартная ставка – 70$/день;

Ведущий инженер (ВИ), стандартная ставка — 60$/день;

Исполнитель 1 (И1), стандартная ставка — 50$/день;

Исполнитель 2 (И2), стандартная ставка — 50$/день;

Рабочий день исполнителя 8 часов при 5 дневной рабочей неделе.

Построить сетевой график выполнения проекта.

Определить критический путь.

Провести анализ использования ресурсов.

Провести анализ стоимости проекта.

Исходные данные по продолжительности работ и закрепленные работы приведены в таблице.

Контрольные вопросы

1. Опишите технологию построения сетевого графика выполнения проекта.

2. Как определить критический путь средствами MS Excel?

3. Что такое матрица инцидентностей?

4. Перечислите основные возможности MS Project как средства управления проектами.

Библиографический список

1. Васильев А. Excel 2010 на примерах [Text] / А. Васильев. — СПб. : БХВ-Петрбург, 2010. — 432 с. : ил. эл. опт. диск (CD-ROM). — ISBN978-5-9775-0578-9 : 233.26

2. Гвоздева В.А. Базовые и прикладные информационные технологии [Text] : учебник / В.А. Гвоздева. — М. : И.Д. ФОРУМ: ИНФРА-М, 2014. — 383 с. — (Высшее образование). — ISBN978-5-8199-0572 : 900.02

3. Гобарева Я.Л. Бизнес-аналитика средствами Excel [Text] : учебное пособие / Я.Л. Гобарева, О.Ю. Городецкая, А.В. Золотарюк. — М. : Вузовский учебник, ИНФРА-М, 2014. — 336 с. — ISBN978-5-9558-0282-4. — ISBN978-5-16-006229-7 : 420.09

4. Голицина О.Л. Информационные технологии [Text] : учебник / О.Л. Голицына и др. — 2-е изд., перераб. и доп. — М. : ФОРУМ, ИНФРА-М, 2014. — 608 с. : ил. — ISBN978-5-91134-178-7. — ISBN978-5-16-003207-8 : 435.05

5. Козлов А.Ю. Статистический анализ данных в MS EXCEL [Text] : учебное пособие / А.Ю. Козлов, В.С. Мхитарян, В.Ф. Шишов. — М. : ИНФРА — М, 2012. — 320 с. — (Высшее образование). — ISBN978-5-16-004579-5 : 295.02

6. Коноплева И.А. Информационные технологии [Text] : учебное пособие / И.А. Коноплева, О.А. Хохлова, А.В. Денисов. — 2-е изд., перераб. и доп. — М. : Проспект, 2011. — 328 с. — ISBN978-5-392-01410-1 : 214.00

7. Корнеев И.К. Информационные технологии [Text] : учебник / И.К. Корнеев, Г.Н. Ксандопуло, В.А. Машурцев. — М. : ТК Велби ; М. : Проспект, 2009. — 224 с. — ISBN978-5-482-01401-1 : 76.45

8. Лялин В.С. Статистика: теория и практика в Excel [Text] : учебное пособие / В. С. Лялин, И. Г. Зверева, Н. Г. Никифорова. — М. : Финансы и статистика, 2010. — 448 с. : ил. — ISBN978-5-279-03381-2 : 470.03

9. Мельников В.П. Информационные технологии [Text] : учебник / В.П.Мельников. — 2-е изд., стер. — М. : Издательский центр "Академия", 2009. — 432 с. — ISBN978-5-7695-6646-2 : 443.30.

10. Михеева Е.В. Практикум по информационным технологиям в профессиональной деятельности [Text] : учебное пособие / Е. В. Михеева. — 11-е изд., испр. — М. : Академия, 2012. — 256 с. — (Среднее профессиональное образование). — ISBN978-5-7695-8744-3 : 304.70

11. Соболь Б.В. Практикум по статистике в Excel [Text] : учебное пособие / Б. В. Соболь [и др.]. — Ростов-на-Дону : Феникс, 2010. — 381 с. : ил. — (Высшее образование). — 264.99

12. Титоренко Г.А. Информационные системы и технологии управления [Text] : учебник / Под ред. проф.Г.А. Титоренко. — 3-е изд., перераб. и доп. — М. : ЮНИТИ-ДАНА, 2010. — 591 с. — ISBN978-5-238-01766-2 : 400.00.

Добавить комментарий

Ваш адрес email не будет опубликован.