CC BY
65
Посилання на статтю___________________________________________________________
Кузьмичов А.І. Електронно-таблична реалізація мережевої моделі проекту / А.І. Кузьмичов / / Управління проектами та розвиток виробництва: Зб.наук.пр. - Луганськ: вид-во СНУ ім. В.Даля, 2009. - № 3 (31). - С. 38-47.__________________________
УДК 338.24 А.І. Кузьмичов
ЕЛЕКТРОННО-ТАБЛИЧНА РЕАЛІЗАЦІЯ МЕРЕЖЕВОЇ МОДЕЛІ ПРОЕКТУ
Запропоновано зручний і ефективний спосіб обчислення часових характеристик мережевої моделі проекту в середовищі табличного процесора Excel. Спосіб рекомендований для використання студентами та менеджерами-практиками в управлінні малими та середніми проектами. Рис. 10, дж. 11.
Ключові слова: електронно-табличне моделювання, мережева модель
проекту, проектна мережа, мережевий графік, технології AoA/AoN, метод CPM/PERT, функції для роботи з масивами, діаграма Гантта.
А.И. Кузьмичев
ЭЛЕКТРОННО-ТАБЛИЧНАЯ РЕАЛИЗАЦИЯ СЕТЕВОЙ МОДЕЛИ ПРОЕКТА
Предложен удобный и эффективный способ расчета временных характеристик сетевой модели проекта в среде табличного процессора Excel. Способ рекомендован для использования студентами и менеджерами-практиками в управлении малыми и средними проектами. Рис. 10, дж. 11.
А.I.Kuzmychov IMPLEMENTING PROJECT NETWORKS IN SPREADSHEETS
The useful and effective way to calculate time characteristics of the project net model using means of the table processor Excel is proposed. It is recommended for students and practice managers in small and middle projects.
Постановка проблеми у загальному вигляді та її зв’язок з важливими науковими чи практичними завданнями. Проектний менеджмент вже досить давно є стандартним розділом практичної оптимізації, який відрізняється універсальністю й, відповідно, сприяє широкому розповсюдженню проектного підходу до дослідження будь-яких динамічних процесів [1-3]. Щоб скористатися цим підходом, достатньо досліджуваний процес представити системою взаємопов’язаних робіт (задач, операцій, заходів, activities) у вигляді т. зв. проектної таблиці, які мають певну тривалість й використовують для свого здійснення матеріальні, кадрові та фінансові ресурси, запаси яких обмежені. Проектна таблиця - це список відомостей про роботи, що має не менше 4 стовпців, де вказується: код (номер) роботи; назва роботи; коди попередніх робіт; тривалість.
Ставиться задача визначення тривалості здійснення проекту та оптимального розкладу виконання робіт с метою завершення проекту якнайшвидше чи не пізніше заданого (директивного) терміну із врахуванням
ресурсних обмежень та певних організаційних умов. Для розв’язання цієї задачі необхідно сформувати мережеву модель проекту й провести розрахунок часових характеристик робіт і усього проекту, користуючись обчислювальними методами потокового програмування.
Аналіз останніх досліджень і публікацій, в яких започатковано розв’язання даної проблеми і на які спирається автор. 50 років тому для управління проектною діяльністю було винайдено ефективний підхід щодо організації, планування, управління та аналізу проектної діяльності на основі математичних моделей потокового програмування у вигляді фундаментальних методів CPM та PERT1. За цим підходом модель проекту представляють мережею (зваженим направленим графом без контурів), де треба знайти конфігурацію й довжину максимального шляху від початку до кінця мережі, в проектному менеджменті цей шлях названо критичним, критичними (з позицій керування ними) є дуги, що йому належать, а довжина критичного шляху визначає мінімальну тривалість здійснення усіх робіт проекту.
Отже, мережева модель проекту - сукупність вузлів та дуг, яка відтворює розмаїття зв’язків між роботами, тривалість їх виконання та ресурсну забезпеченість. На сьогодні існує близька 200 різних мережевих моделей проектів, що розрізняються за призначенням, елементами опису, алгоритмам та засобами їх реалізації.
Існує два фундаментальних типи мереж, відповідно, два типи мережевих моделей проекту та технологій їх побудови та реалізації:
- «робота-дуга» (AoA, Activity-on-Arc);
- «робота-вузол» (AoN, Activity-on-Node).
Перший з них вважається класичним, бо визначив теорію й технологію управління проектами, базуючись на розв’язанні задач пошуку екстремальних шляхів у мережах2, його опис за традицією домінує в навчальній літературі; другий тип, розроблений для безмашинної реалізації проектних розрахунків в умовах тодішнього дефіциту комп'ютерних потужностей3 й невідомий з підручників, є об'єктом професійних наукових досліджень [2] й, за іронією долі, став основою сучасних програмних продуктів з управління проектами (MS Project, Primavera, Spider тощо).
За висновком експертів та за багаторічним досвідом практиків технологія AoN вважається простішою й, головне, однозначною, розрахованою на колективну роботу з керування крупними проектами. Побудова ж класичної мережевої моделі проекту за технологією АоА змушує застосовувати штучні об'єкти - події, а також вводити фіктивні роботи та події для коректного відображення відношення передування робіт згідно заданої проектної таблиці. Це загалом характеризується складністю, необхідністю попередньої побудови мережевого (сіткового) графіка, що для крупних проектів зробити непросто, суб’єктивністю й неоднозначністю процесу мережевого моделювання проектів, саме тому перша за появою й традиційна технологія АоА ще використовується де-не-де лише за сталою традицією, але не у сучасних програмних продуктах з управління проектами.
Формулювання цілей статті. Таким чином, виникла педагогічна й практична дилема реалізації, практичного використання та порівняльної оцінки
1 Kelley J., Walker M. Critical Path Planning and Scheduling, Proc. of the Eastern Joint Comp. Conf., 1959. PERT. Summary Report Phase 1, Special Project Office, Bureau of Ordinance, Dept. of the Navy, 1958.
2 Ford L.R., Fulkerson D.R. Flows in Networks, Princeton Univ. Press, Princeton, N.J., 1962.
Fulkerson D.R. Expected critical path lengths in PERT networks// OR, vol. 10, № 3, 1962.
3 Fondahl J. Non-Computer Approach to the Critical Path Method for the Construction Industry, Stanford
Univ., 1961. _____________________________________________________________________________
функціональних можливостей та сфер застосування цих двох технологій. Сучасна практика проектного менеджменту пропонує кілька шляхів комп'ютерної реалізації мережевих моделей проектів, це:
а) універсальні системи управління проектами, розраховані на рядового користувача (лідер - програма MS Project у складі офісного пакету MS Office);
б) спеціалізовані програмні продукти, що розробляються на певній апаратній, операційній та мовній платформах на замовлення;
в) розробка розрахункового документа типу електронної таблиці на платформі наймасовішого за світовим досвідом використання, потужного й ефективного програмного продукту - табличного процесора MS Excel.
Виділення невирішених раніше частин загальної проблеми, котрим присвячується означена стаття. Технологія електронно-табличного моделювання (spreadsheet modeling) превалює у світовій системі бізнес-освіти [47], бо є ідеальним засобом інтеграції даних у вигляді табличних баз даних, організації табличних обчислень для реалізації принципу «Що, якщо ...?» та отримання розв’язку задач лінійної, потокової та нелінійної оптимізації за допомогою програми-надбудови Excel Solver (Поиск решения). Правда, фахівці, зокрема з проектного аналізу та менеджменту, звертають увагу на явну затримку у появі ефективних засобів мережевого моделювання проектів на тлі потужного й масштабного розвитку й впровадження універсальних засобів електронно-табличного моделювання. Тож ця робота ставить за мету скоротити цю затримку й надати рядовим користувачам досить дієвий інструментар для ефективного здійснення проектної діяльності.
Виклад основного матеріалу дослідження з повним обґрунтуванням отриманих наукових результатів. У цій роботі на прикладі конкретного проекту пропонується простий й доступний підхід до мережевого моделювання проектної діяльності методом критичного шляху з використанням звичайних операцій та функцій Excel шляхом створення відповідної електронної таблиці для мереж за технологіями AoN та АоА. Відкритість моделі з-за відсутності будь-яких додаткових програмних модулів (макросів, надбудов) дозволяє на цій основі будь-кому, у першу чергу студентам та менеджерам-практикам, власноруч, досить просто і прозоро організувати й реалізувати процес моделювання проекту за принципом «Що, якщо ...?» чи, можливо, шляхом побудови відповідної імітаційної моделі з отриманням статистичних характеристик часових характеристик проекту в умовах невизначеності й ризику.
З літератури з проектного менеджменту відомо, що пошук критичного шляху у проектній мережі зводиться до пошуку максимальних (критичних) шляхів від її початку до усіх проміжних й кінцевого вузлів, що здійснюється «прямим ходом» мережею від її початкового до кінцевого вузла шляхом обчислення відповідних максимумів для вузлів. Для цього за технологією AoN розраховуються часові моменти раннього початку (РП або EST, Early Start Time) та раннього завершення (РЗ або EFT, Early Finish Time) кожної роботи за формулами:
tpn(i) = max{tP3(k)}, кєР(і),
де P(i) - підмножина робіт, що передують і-ій роботі; treCi) = tmO) + t(i), t(i) - тривалість і-ої роботи.
«Зворотним ходом» мережею від її кінцевого до початкового вузла розв’язується задача про пошук часткових мінімальних шляхів, для цього обчислюються відповідні мінімуми для вузлів та часові моменти пізнього початку (ПП або LST, Late Start Time) та пізнього завершення (ПЗ або LFT, Late Finish Time) кожної роботи за формулами:
tn3(i) = min{tnn(j)}, jeS(i),
де S(i) - підмножина робіт, що слідують за і-ою роботою
tnn(i) = Vi3(i) - tOX
тоді загальний резерв часу (Slack) роботи обчислюється за формулою:
r(i) = ірз(і) - Ірп(і) = tn3(i) - tnn (і) ,
усього - відшукується 5 часових характеристик робіт (рис. 1).
Таким чином, мережеве моделювання проекту зводиться до покрокового пошуку максимальних та мінімальних шляхів у мережі методом динамічного програмування. Користуючись програмою Excel, є можливість здійснити ці розрахунки без застосування спеціальних програмних засобів.
Технологія AoN. Просто й однозначно будується мережева модель проекту (рис. 2, б), де роботи представлені вузлами, а дуги між ними вказують на існуючі технологічні зв’язки, відповідний табличний документ має вигляд (рис. 3), де за вказаними формулами визначені часові характеристики усіх робіт й знайдено критичний шлях, утворений роботами: B - C - D - E - J, який зображено на мережевому графіку (рис. 4, а). На рис. 4, б зображені формули, за якими обчислюються п’ять часових характеристик робіт.
Рис. 1. Часові характеристики робіт
Приклад. Проект з 10 робіт задано проектною таблицею (рис. 2, а), де перша та третя колонки визначають відношення передування робіт, ця таблиця є основою побудови мережевого графіка та виконання усіх наступних обчислень.
(а)
(б)
Рис. 2. Проектна таблиця та вузлова мережа
Цей же обчислювальний процес можна представити наочною комбінацією розрахунків та засобів графіки за технологією т.зв. кліткових автоматів, рис. 5, де кожна робота представляється за
РП ПП
Роб Т
РЗ ПЗ
Рез=
допомогою шаблону, у його складі: заданий код роботи (Роб) та її тривалість (Т), результати обчислень за відповідними формулами п’яти часових характеристик (РП, ПП, РЗ, ПЗ, Рез).
Рис. 3. Мережева модель типу розрахункової таблиці
(а)
Рис. 4. Конфігурація критичного шляху
(б) Зворотній хід
(в) розрахункові формули Рис. 5. Мережева модель типу кліткового автомату
Наведений приклад організації табличних обчислень часових характеристик робіт простий, але вимагає вручну й дуже уважно увести по 10 (по кількості робіт у проекті) унікальних формул для обчислення двох фундаментальних показників
- РП та ПЗ кожної роботи (для певного спрощення тут уведені функції МАКС та МИН навіть для одного аргументу, які можна замінити прямим зверненням до клітинки), добре, що інші формули один раз уводяться й копіюються у діапазон клітинок (рис. 4, б).
Ясно, що для крупного проекту уводити ці унікальні формули досить непросто, тож саме тут виникла серйозна практична проблема розробки єдиної формули для кожної з цих двох колонок (РП та ПЗ) для скорочення часу й ручної праці, щоб таку формулу один раз увести й скопіювати у діапазон будь-якого розміру.
На цьому шляху зроблено багато спроб, скажімо, одна з них [8] з метою одноразового уведення та багаторазового застосування розрахункових формул пропонує мережеву модель типу AON до 200 робіт представити квадратною матрицею зв’язків «робота-робота» розміром до 200x200 (автор орієнтувався на максимальну кількість у 256 стовпців в Excel 2003), де можна застосувати універсальні формули для елементів матриці. Очевидний недолік цього підходу -обмежений розмір задачі (200 робіт4), ручне створення й форматування крупної матриці зв’язків з заданої проектної таблиці, проблеми з формулами обробки матриць, зате цей приклад підкреслює непростий характер поставленої проблеми пошуку «двох формул».
У цій роботі пропонується для практичного використання вдосконалена й ефективна методика табличного розрахунку характеристик певної мережевої моделі, яка дозволяє суттєво зменшити кількість унікальних формул, зокрема, для мережевого моделювання проектів, побудованих за технологією AoN, вдалося знайти дві шукані уніфіковані формули, єдині на увесь табличний документ незалежно від розміру проектної задачі, завдяки чому кардинально зменшується обчислювальна робота для проектів практично будь-якого розміру й спрощується процедура модифікації моделі - це стало можливим завдяки застосуванню функцій для роботи с масивами (Array Formulas) та формул, побудованих на їх основі.
4 Використання Excel 2007 зі значно більшим числом стовпців електронної таблиці приведе до
необхідності працювати з таблицею колосальних розмірів______________________________
Відомо, що функції цього типу найчастіше застосовують у лінійній алгебрі для реалізації матричних (М...) операцій (МОБР, МОПРЕД, МУМНОЖ), адже вони характеризуються надзвичайною потужністю й універсальністю, правда, їх дія часто залишається мало зрозумілою особами, які не мають досвіду з комп'ютерного програмування. Уведення будь-якої формули з масивами чи її редагування завершуються натисканням комбінації трьох клавіш: <Ctrl>+<Shift>+<Enter>, формула уводиться один раз і Excel автоматично охоплює її фігурними дужками «{}», діє в межах попередньо визначеного масиву даних й повертає єдиний результат у вигляді масиву даних, який формується циклічним алгоритмом [9-10].
Ці дві формули теж реалізують циклічний (ітераційний) алгоритм обробки даних для:
- максимізації прямих шляхів для обчислення РП (прямий хід мережею);
- мінімізації зворотних шляхів для обчислення ПЗ робіт (зворотний хід мережею)
й, відповідно, визначення усіх інших часових характеристик.
Формули з масивами, про що йдеться, часто породжують циклічні посилання (коли елементи формули у певній клітинці посилаються самі на себе прямо чи опосередковано через інші формули), на ці посилання Excel стандартно
повідомляє про помилку виведенням панелі інструментів Циклические ссылки, дозволити ж такі посилання можна фіксацією пункту Итерации командою Сервиса Параметры^Вычисления.
Отже, тепер у таблиці (рис. 7) у кожній клітинці стовпця РП (це робота-вузол мережі) обчислюється максимум серед потоків, що входять у вузол, за
допомогою єдиної складеної формули:
{=МАКС(ЕСЛИ(ЕОШ(НАЙТИ($А$2:$А$11 ,C2)),0,$F$:$F$11))}, де застосовано 4 вкладених функції: НАЙТИ(), ЕОШ(), ЕСЛИ() та МАКС(), які утворюють цикл - послідовність операцій, що повторюються, звертаючись на кожному кроці до одних й тих же даних:
- функція НАЙТИ (FIND) для заданих робіт (у стовпці А) відшукує їх
входження у набір робіт-попередників (стовпець С), якщо таких не знаходить, виводить повідомлення про помилку #ЗНАЧ!, інакше виводить число - номер першої позиції
- функція ЕОШ (ISERR) перевіряє наявність помилки (рос. Есть ОШибка?) й виводить логічне значення ИСТИНА (є) чи ЛОЖЬ (нема) в залежності від перевірки
- функція ЕСЛИ (IF) формує масив з 10 чисел для кожної поточної роботи, це нулі для ИСТИНА та значення РЗ робіт-попередників
- функція МАКС (MAX) для кожної поточної роботи знаходить максимум серед значень сформованого масиву з 10 чисел.
Аналогічно діє друга єдина формула мінімізації, це також циклічна конструкція, яка зворотним ходом знаходить мінімальні шляхи з кінця мережі до її початку:
{=МИН(ЕСЛИ(ЕОШ(НАЙТИ(А2;$С$2:$С$11));МАКС^$2^$11)^$2^$11))}.
Принципова різниця - тут функція ЕСЛИ у випадку результату ИСТИНА залишає максимальне значення ПЗ, інакше визначається мінімум зі значень ПП робіт. Усі інші три часові характеристики, як відомо, визначаються за однаковими формулами.
Таким чином, проблема «двох формул» вирішена - запропоновано підхід із застосування двох унікальних формул, які, правда, дещо громіздкі, зате уводяться один раз, для пошуку максимуму та мінімуму у вузлах мережі, що дозволяє досить просто отримати результати з моделювання проекту будь-якого розміру.
За цими результатами тепер досить просто можна побудувати діаграму Гантта, яка у наочній формі ілюструє роботи проекту (тривалості і повні резерви) та їх взаємодію, рис. 6.
Рис. 6. Діаграма Гантта
Рис. 7. Прямий хід алгоритму
Рис. 8. Зворотний хід алгоритму
Технологія АоА. За цією класичною технологією побудова мережевого графіка, що складається з робіт-дуг та подій-вузлів, є обов’язковою процедурою, оскільки лише на графіку можна побачити порушення одного з базових правил методу критичного шляху (СРМ), а саме - відсутність паралельних робіт між будь-якою парою вузлів. На рис. 9,а зображено початковий варіант, що реалізує задану проектну таблицю, де видно, що робота С вимушено зображена двічі, а дві роботи G та Н - паралельні, тож після корекції мережевий графік (рис. 9, б) має 9 вузлів-подій, з них вузол 8 фіктивний, та 12 дуг-робіт, з них дуги 5-3 та 8-7 -фіктивні5. Лише тепер можна задати оновлену проектну таблицю, на основі якої формуються дві розрахункові таблиці: для подій та робіт. З-за неоднозначності технології АоА ці дві таблиці, якщо розроблені різними виконавцями, можуть бути різними, що практично унеможливлює колективну роботу з управління крупним проектом.
(а) (б)
Рис. 9. Побудова мережевого графіка (АоА)
Обмежимось реалізацією цього мережевого графіка у вигляді кліткового автомату, для цього кожен вузол-подію представимо його шаблоном, елементами якого є характеристики самої події та робіт, що входять у цей вузол [11].
Розрахунок часових характеристик мережі базується на фундаментальному принципі потокового програмування - збереження (балансу) потоку, що проходить через вузол, саме цей принцип, що застосовується для пошуку екстремальних шляхів у мережі, став основою методу критичного шляху (и КеІІеу, 1957-1959). За цим принципом відшукуються максимальні шляхи до кожного j-го вузла за формулою:
1® = тах{ОД + у,
Рис. 10. Сітьова діаграма
Висновки з даного дослідження і перспективи подальших розвідок у даному напрямку. Основним результатом даного дослідження стали дві уніфіковані формули, за допомогою яких з’явилась можливість здійснювати мережеве моделювання навчальних чи реальних проектів порівняно невеликих розмірів (до 500 робіт) у доступному для усіх користувачів середовищі Excel. Наведена методика доповнена звичайними розрахунками за унікальними формулами, побудовою розрахункової документації на основі кліткових автоматів та діаграми Гантта.
Наступні етапи дослідження з проблематики мережевого моделювання динамічних процесів стосуються розробки математичних моделей задач оптимізації, формування та прийняття управлінських рішень на результатах моделювання. Щодо управління проектами мова йде, зокрема, про розробку комбінованих (формульних та оптимізаційних) методик та специфічних (неальтернативних [2]) моделей, які враховують різноманітні відносини між роботами, ресурсні обмеження та вартісні оцінки параметрів проекту.
ЛІТЕРАТУРА
1. Управление проектом. Основы проектного управления: учебник/ кол. авт.; под ред. проф. М.Л. Разу. - М.: КНОРУС, 2007. - 768 с.
2. Математические основы управления проектами: уч. пос./ С.А. Баркалов и др. Под ред.
В.Н. Буркова. - М.: Высш. шк., 2005. - 423 с.
3. Kuzmychov A. Operations Research: Optimization models for Decision-making: навч. пос. / A. Kuzmychov - К.: АМУ, 2007. - 270 с.
4. Hillier F.S. Introduction to Management Science/ F.S. Hillier, M.S. Hillier, G.J. Lieberman. -Boston, MA, Irwin-McGraw-Hill, 2000. - 986 р.
5. Ragsdale C. Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Management Science, 3-nd ed./ C. Ragsdale. - Cincinnati, Ohio: South-Western College Publishing, 2000. - 744 p.
6. Мур Дж. Экономическое моделирование в MS Excel / Дж. Мур, Л. Уэдерфорд, 6-е изд.: Пер. с англ. - М.: «Вильямс», 2004. - 1024 с.
7. Кузьмичов А.І. Математичне програмування в MS Excel: навч. пос. / А.І. Кузьмичів. - К.: Вид-во Європ. ун-ту, 2005. - з20 с.
8. Seal K.C. A Generalized PERT/CPM Implementation in а Spreadsheet / K.C. Seal // INFORMS Transactions On Education, Vol. 2, No 1, 2001, pp. 16-26.
9. Ragsdale C. The Current State of Network Simulation in Project Management Theory and Practice, Omega / C. Ragsdale // The International J. of Management Science, Vol. 17, No.
1, 1989, pp. 21-25.
10. [Електронний ресурс]. - Режим доступу:
www .cpearson.com/excel/ArrayFormulas.aspx.
11. Кузьмичов А.І. Табличний аналог електронних моделей задач потокової оптимізації / А.І. Кузьмичов та ін. // Електроніка та системи управління. - 2008. - №4(18). - С.121-129.
Стаття надійшла до редакції 10.08.2009 р.
“Управління проектами та розвиток виробництва”, 2009, № 3(31)
11
