روش محاسبه درصد پیشرفت پروژه در اکسل به همراه فایل نمونه PMS (سیستم پیشرفت پروژه)

روش محاسبه درصد پیشرفت برنامه ای

در فایل ارائه شده سه درصد پیشرفت برنامه‌ای قرار داده شده است که وابسته به تاریخ شروع، مدت زمانی و تاریخ پایان است که توسط کاربر تنظیم می‌شود.

همان‌طور که در معرفی این فایل اشاره شد؛ از توانمندی‌های ارزشمند این فایل محاسبه درصد پیشرفت برنامه‌ای و واقعی بر اساس تنظیمات زمانی است که کاربر وارد می‌کند. روشی که برای محاسبه درصد پیشرفت برنامه‌ای و واقعی به‌کاررفته است کاملاً باهم متفاوت است که در اینجا قصد داریم روش محاسبه درصد پیشرفت برنامه‌ای را توضیح دهیم. با این فایل عملاً به یکی از دغدغه‌های همیشگی کارشناسان برنامه‌ریزی و کنترل پروژه که می‌خواهند در مقاطع مختلف پروژه درصدهای پیشرفت را محاسبه نمایند، پاسخ داده شده است.

نکته حائز اهمیت برای درک محاسبه درصد پیشرفت برنامه‌ای این است که بین درصدهای پیشرفت فعالیت و پیشرفت در WBS منطق کاملاً متفاوت است. منطقی که برای پیشرفت فعالیت در نظر گرفته می‌شود بر اساس زمان فعالیت‌هاست ولی منطق پیشرفت WBS بر اساس فعالیت‌های زیرمجموعه آن است.

همچنین برای درک بهتر منطق کار توجه نمایید که در این فایل روش محاسبه درصد پیشرفت از دو بخش تشکیل شده است. یک بخش که نقش پشتیبانی دارد و تمامی اطلاعات در آنجا قرار گرفته است و دیگری بخش نهایی که اطلاعات مربوط به پیشرفت را نشان می‌دهد.

معرفی ستون «فعالیت»

به‌عنوان یک نکته کاربردی دیگر در تنظیم PMS سعی کنید سطح آخر فعالیت با یک رنگ خاص تنظیم شود. با این کار و از طریق فیلتر رنگ می‌توانید به‌راحتی فعالیت‌ها را از WBS تمیز دهید. در این فایل سطح آخر بدون رنگ است و هرگاه نیاز باشد که فعالیت‌ها انتخاب شود کافی است ردیف‌های بدون رنگ را انتخاب نمود.

جهت بالا بردن کارایی و همچنین برای انتخاب فعالیت‌ها در Pivot Table یک ستون بانام «فعالیت» در ستون I اکسل تعریف‌شده است که به‌راحتی می‌توان ردیف‌های فعالیت یا غیر فعالیت را انتخاب نمود.

اگر در دوره رایگان کارآموزی مجازی شرکت کرده باشید، می‌دانید که در درس دوم یک روش آسان برای تعریف ستون «فعالیت» توضیح داده شده است. اگر علاقه‌مند هستید می‌توانید از این آدرس در این دوره شرکت کنید.

روش محاسبه درصد پیشرفت فعالیت‌ها در بخش پشتیبانی

بخش پشتیانی که از ستون‌های FI تا JO تعریف شده است و در آن اطلاعات درصد پیشرفت بر اساس تاریخ شروع و پایان فعالیت قرار داده شده است.

به‌عنوان‌مثال به سلول F7 توجه کنید. فرمول آن به‌صورت زیر است (از شکل ظاهری آن نترسید به‌زودی به آن مسلط می‌شوید):

=IF(FK$2 <= $AX7 ; 0 ; IF(FK$2 > $AY7 ; 100% ; (FK$2 - $AX7) / ($AY7 - $AX7))) / $T7 - IF(FJ$2 <= $AX7 ; 0 ; IF(FJ$2 > $AY7 ; 100% ; (FJ$2 - AX7) / ($AY7 - $AX7))) / $T7

اول (FK$2<=$AX7) چک می‌کند که آیا تاریخ آن ستون (سلول FK2 معادل 04/09/2015) کمتر یا مساوی تاریخ شروع آن فعالیت (سلول AX7 معادل 05/09/2015) است و یا خیر؟ درصورتی‌که کمتر بود نتیجه صفر را برمی‌گرداند. شرط دوم (FK$2>$AY7) چک می‌کند که اگر تاریخ آن ستون بزرگ‌تر از تاریخ انتهای آن فعالیت (سلول AY7 معادل 05/09/2015) بود مقدار 100% را بازمی‌گرداند. فکر می‌کنم که تا حدودی روش کار مشخص شد. اگر بازه شروع و اتمام فعالیت با تاریخ آن ستون اشتراک نداشته باشد بسته به اینکه تاریخ فعالیت شروع، قبل و یا تاریخ انتها، بعد از ستون تاریخ باشد مقدار صفر و یا 100% را بازمی‌گرداند. اگر این دو شرط منتفی شد، یعنی اینکه بازه شروع و اتمام فعالیت با تاریخ ستون، اشتراک دارد و عملاً باید بسته به اینکه تاریخ آن ستون کجای بازه شروع تا پایان قرار می‌گیرد، درصد پیشرفت محاسبه شود. در این حالت تاریخ ستون (سلول FK2 معادل 04/09/2015) از تاریخ شروع (سلول AX7 معادل 05/09/2015) که می‌شود و بر بازه کل شروع تا پایان (تفاضل سلول FY7 از سلول AX7) فعالیت تقسیم می‌شود تا نسبت درصد پیشرفت برای آن فعالیت محاسبه شود. حاصل این محاسبه در وزن فعالیت (سلول T7) ضرب می‌شود تا محاسبات آتی راحت‌تر انجام شود. اگر خوب توجه کنید درصدی که از این تقسیم به دست می‌آید مقداری است تجمعی؛ بدین معنی که مقادیر آن از ابتدای پروژه تا تاریخ ستونی که محاسبه در آن صورت می‌گیرد به‌صورت تجمعی نشان داده می‌شود. بنا به ملاحظات محاسباتی، نیاز است که درصد پیشرفت به‌صورت دوره‌ای نمایش داده شود؛ یعنی مقادیر پیشرفت مربوط به این دوره از دوره قبل کم شود. محاسباتی که در ادامه فرمول قرار گرفته دقیقاً مقادیر تجمعی برای ستون قبلی را محاسبه می‌کند و حاصل این دو از هم کم می‌شود تا درصد پیشرفت به‌صورت دوره‌ای نمایش داده شود. همچنین توجه نمایید در ستون اول چون عملاً تفاضل از دوره قبل معنی نمی‌دهد، فقط قسمت اول فرمول نشان داده درج شده است.

درصد پیشرفت برنامه‌ای «تا دوره قبل» مربوط به فعالیت‌ها در بخش نهایی

اگر به ستون X تا Z اکسل دقت نمایید جایی که محاسبات نهایی درصدهای پیشرفت برنامه‌ای قرار دارد از فرمول‌های آرایه‌ای برای محاسبه نهایی کمک گرفته شده است. به سلول Z7 توجه نمایید. فرمول آن به‌صورت زیر است:

{=IFERROR(SUM($FJ7:$JO7 * ($P$2 - 1 >= $FJ$2:$JO$2)) / $T7 ; 0)}

ساختار محاسباتی به‌صورت آرایه‌ای است پس برای درک آن باید به‌صورت مجموعه‌ای از اعدادی که آن را تشکیل می‌دهد، توجه نمود. از بخش داخلی فرمول شروع می‌کنیم.

با فرمول ($P$2-1>=$FJ$2:$JO$2) عملاً آرایه‌ای از مقادیر تشکیل داده‌ایم که حاصل آن یا True است یا False. این عبارت درواقع از تاریخ شروعی که توسط کاربر تنظیم شده است (سلول P2 معادل 30/07/2016) یک روز کم می‌کند و با کل سلول‌های FJ تا JO مقایسه می‌کند. حاصل کار مجموعه یا آرایه‌ای از مقادیر است که یا True است و یا False. اگر یک روز قبل از تاریخ شروع از مقدار آرایه اول بزرگ‌تر بود مقدار True (مقدار 1) و اگر نبود مقدار False (مقدار 0) را برمی‌گرداند. این کار برای کل محدوده FJ تا JO انجام می‌شود و آرایه‌ای با مقادیر True و False برمی‌گرداند. حاصل این آرایه در آرایه دیگر ضرب می‌شود که محدوده FJ تا JO است.

به عبارت (($FJ7:$JO7*($P$2-1>=$FJ$2:$JO$2) توجه کنید. این دو آرایه‌هایی هستند که درایه‌های آن‌ها یکسان هستند و در هم ضرب می‌شود.

دقت کنید آرایه قبل مجموعه‌ای از True و False ها یا به عبارتی مجموعه‌ای از یک و صفرهاست. آرایه دوم هم در بخش قبل توضیح داده شد که مجموعه‌ای از درصدهای پیشرفت است. تک‌تک عناصر در هم ضرب می‌شود. چون آرایه اول وابسته به تاریخ شروعی است که توسط کاربر وارد شده و درایه‌های آن اگر بیشتر از تاریخ شروع کاربر باشد یک است و در غیر این صورت صفر، وقتی در آرایه دوم ضرب می‌شود تبدیل به آرایه‌ای می‌شود که اعضای آن اگر کمتر از تاریخ وارد شده توسط کاربر باشد صفر و اگر بیشتر باشد معادل درصدهای پیشرفت است.

ادامه کار دیگر ساده است. تک‌تک عناصر باهم جمع شده و بر وزن آن فعالیت تقسیم می‌شود. اگر وزن فعالیت صفر بود چون خطای تقسیم‌بر صفر (#Div!) برمی‌گرداند از طریق IFERROR کنترل می‌شود و در صورت بروز خطا مقدار صفر بازگردانده می‌شود.

درصد پیشرفت برنامه‌ای «در این دوره» مربوط به فعالیت‌ها در بخش نهایی

همانند محاسبات قبل در این بخش نیز از فرمول‌های آرایه‌ای استفاده شده است. به فرمول سلول Y7 توجه نمایید:

=IFERROR(SUM($FJ7:$JO7 * ($Q$2 >= $FJ$2:$JO$2) ; -$FJ7:$JO7 * ($P$2 - 1 >= $FJ$2:$JO$2)) / $T7 ; 0)

در این فرمول روش کار تقریباً مانند مثال قبلی است با این تفاوت که محاسبات پیشرفت از تاریخ شروعی که کاربر وارد نموده تا تاریخ انتهایی که آن نیز کاربر تنظیم نموده است، انجام می‌گیرد.با توجه به توضیحات قسمت قبل فرمول ($Q$2>=$FJ$2:$JO$2) ساده خواهد بود. آرایه‌ای از درایه‌های True و False که در صورتی True هست که تاریخ انتهایی که توسط کاربر تنظیم شده است از آرایه‌های آن بزرگ‌تر باشد.

با توجه به فرمول (($FJ7:$JO7*($Q$2>=$FJ$2:$JO$2) مشخص است که همانند قبل این آرایه در درصدهای پیشرفت برنامه‌ای ضرب شده و آرایه‌ای تشکیل می‌دهد که عناصر آن یا صفر است و یا درصدهای پیشرفتی است که تاریخ ستون‌های آن کمتر از تاریخ انتهایی تنظیم شده توسط کاربر باشد.

قسمت دوم فرمول که در بخش قبل توضیح داده شد. این فرمول (($FJ7:$JO7*($P$2-1>=$FJ$2:$JO$2) آرایه‌ای از درصدهای پیشرفت که یک روز کمتر از تاریخ شروع کاربر است تشکیل می‌دهد. به علامت منفی آن توجه کنید. درنهایت نتیجه این آرایه از آرایه اول کم می‌شود. برای روش شدن موضوع به مثال زیر توجه کنید:

Array A: {0.1 , 0.1 , 0.1 , 0.1 , 0.1 , 0.1 , 0.1 , 0.0 , 0.0 , 0.0}
Array B: {0.1 , 0.1 , 0.1 , 0.1 , 0.0 , 0.0 , 0.0 , 0.0 , 0.0 , 0.0}
Array C: A - B
Array C: {0.0 , 0.0 , 0.0 , 0.0 , 0.1 , 0.1 , 0.1 , 0.0 , 0.0 , 0.0}

آرایه‌ای که حاصل تفاضل آرایه A با آرایه B خواهد بود، آرایه‌ای است که فصل مشترک این دو آرایه است. باکمی تأمل مشخص است که با این روش ما به هدف خود که همانا برآورد درصد پیشرفت در بازه زمانی شروع تا پایان تاریخ‌های تنظیم‌شده توسط کاربر دست پیداکرده‌ایم.

همانند توضیحات قبل، حاصل مجموع اعداد، بر وزن آن تقسیم‌شده و درصورتی‌که با خطای تقسیم روبرو شود از طریق فرمول IFERROR کنترل می‌شود.

درصد پیشرفت برنامه‌ای «تجمعی» مربوط به فعالیت‌ها در بخش نهایی

قسمت‌های سخت را پشت سر گذاشته‌اید. با جمع کردن نتایج دو بخش قبل به درصد پیشرفت تجمعی دست پیدا خواهید کرد.

روش محاسبه درصد پیشرفت برای WBS ها در بخش پشتیبانی و نهایی

در بخش های قبل توضیحات مفصلی در خصوص روش محاسبه درصد پیشرفت فعالیت‌ها چه در بخش پشتیبانی و چه در بخش نهایی ارائه گردید. محاسبه درصد پیشرفت WBS چه در بخش پشتیبانی و چه در بخش نهایی بسیار ساده است.

در بخش پشتیبانی برای محاسبه درصد پیشرفت WBS از فرمول SUBTOTAL استفاده است. از طریق این فرمول کلیه درصدهای پیشرفت فعالیت‌ها با هم جمع شده و در WBS قرار می‌گیرد.

در بخش نهایی برای محاسبه درصد پیشرفت WBS از حاصل ضرب پیشرفت در اوزان برای تک‌تک عناصر یک سطح پایین تر از هر WBS، استفاده شده است. یک راه حل دیگر استفاده از SUMPRODUCT است که فقط برای سطح WorkPackage جواب می‌دهد و نمی‌توان در سطوح بالاتر از آن استفاده نمود. در اینجا به خاطر همگنی فرمول ها از همان فرمول ضرب و جمع آن عناصر با همدیگر استفاده شده است.

سخن پایانی

اگرچه در ظاهر فرمول‌های به کار رفته پیچیده به نظر می رسد ولی اگر منطق کار درک شود، استفاده از این فرمول ها بسیار ساده است. نکته حائز اهمیت دیگر نمایش قدرت فرمول‌های آرایه‌ای در اینجاست. اگر این فرمول‌هایآرایه‌ای نبود، استفاده از فرمول ها یا خیلی پیچیده می شد و یا حتی برخی از این نتایج غیر قابل پیاده سازی به نظر می رسید.