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

روش محاسبه درصد پیشرفت پروژه از طریق PMS (سیستم پیشرفت پروژه) یکی از دغدغه های تیم مدیریت پروژه بوده و در کنار برنامه زمانبندی، مدرک PMS – Progress Measurement System یکی از زمانبر ترین مدارکی است که می بایست در ابتدای پروژه توسط تیم مدیریت پروژه تکمیل شود.

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

PMS-Excel

روش محاسبه درصد پیشرفت پروژه از طریق PMS

در قراردادهای برنامه ریزی و کنترل پروژه که با شرکت های مختلف منعقد می کنیم، نیاز است که در ابتدای کار، فایل PMS: Progress Measurement System (سیستم پیشرفت پروژه) که در واقع روش محاسبه درصد پیشرفت است تنظیم شود و در آن روند پیشرفت پروژه در مقاطع مختلف پروژه محاسبه گردد. این فایل به قدری برای ما با اهمیت است که به محض تایید برنامه زمانبندی آن را تهیه و تنظیم می کنیم. ظرف چند سال گذشته که با این فایل کار کردیم، راهکارهای متنوعی در آن قرار دادیم که این راهکارها با توجه به نیاز انواع کارفرما های مختلف بوجود آمده است. روش های ابتکاری و بعضا نوینی که کمتر جایی مورد استفاده قرار گرفته، در این فایل گنجانده شده است. تیم وب سایت مدیر پروژه تصمیم گرفت به منظور ارتقای دانش خود و همراهان وب سایت مدیر پروژه، فایل PMS که در آن روش محاسبه درصد پیشرفت پروژه نشان داده شده است را به اشتراک بگذارد. برخی از ویژگی های این فایل عبارت است از:

  1. سه وزن مختلف در آن طراحی شده که کمتر از ۱۰ ثانیه پیشرفت ها را بر اساس پیشرفت زمانی، هزینه ای و فیزیکی به شما می دهد (این قابلیت قابل توسعه برای وزن های دیگر نیز می باشد). در این روش محاسبه درصد پیشرفت بر اساس نیاز کاربر انجام می شود.
  2. در هر مقطعی از پروژه به شما درصدهای پیشرفت برنامه ای و واقعی را برای تاریخ ها و مدت زمان ذکر شده تنها با تغییر تاریخ و زمان به نمایش در می آورد. با این ترفند روش محاسبه درصد پیشرفت نسبت به فایل های مشابه کاملا دگرگون شده است.
  3. ترسیم انواع نمودار های مختلف شامل نمودار کل پروژه، نمودار انواع دیسیپلین ها، نمودار انواع قسمت های مختلف پروژه تنها با یک کلیک ساده. روش محاسبه درصد پیشرفت و نموداری که ترسیم می شود به صورت کامل وابسته به انتخاب کاربر است.
  4. نمایش نمودار ها به صورت پیش فرض ماهیانه بوده ولی به محضی که گروه بندی اطلاعات برای یک یا چند ماه خاص به صورت هفتگی تغییر دهید، نمودار به صورت خودکار اطلاعات هفتگی را در آن یک یا چند ماه به صورت ریز نمایش می دهد. روش محاسبه درصد های این نمودار کاملا خلاقانه بوده که با مراجعه به فایل می توانید به آن پی ببرید.
  5. از ترکیب جداول محوری و صفحه بندی کاربرگ ها جهت تعامل قدرت و زیبایی استفاده شده است.
  6. مبنای تاریخ های وارد شده برای کلیه محاسبات، تاریخ شمسی است که همیشه برای کاربران (با پیش فرض تقویم شمسی) مشکل آفرین بوده است.
  7. استفاده از فرمول های آرایه ای به جای فرمول های ساده برای سرعت بخشین و ساده تر شدن فرمول ها. به جرات می توان گفت بدون این روش محاسبه درصد پیشرفت با این همه انعطاف پذیری به هیچ عنوان امکان پذیر نبود.
  8. همه این قابلیت ها بدون نوشتن یک خط کد در اکسل انجام شده است.

ناگفته نماند چون ماهیت کار مهم بوده و هدف ارائه روش محاسبه درصد پیشرفت پروژه بوده، فایل مزبور حاوی پروژه ای است که مربوط به یک پروژه ساختمانی است و واضح است که این پروژه نسبت به پروژه های صنعتی و نفت و گاز از پیچیدگی کمتری برخودار است. ناگفته نماند که در نهایت منطق کار چه برای پروژه های پیچیده تر و چه برای پروژه های ساده تر همان است، ولی توانمندی این فایل برای پروژه هایی با حدود ۱۰ تا ۱۵ هزار ردیف نیز مورد آزمایش قرار گرفته و جواب داده است.

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

با توجه به بازخوردهای مثبتی که از فایل ارائه شده دریافت شد، قصد داریم به مرور و به صورت خلاصه ترفندها و یا به عبارت دیگر روش محاسبه درصد پیشرفت در فایل PMS، به صورت خلاصه توضیح دهیم. امیدواریم که این اطلاعات مفید واقع شود.

فرمول های آرایه ای

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

مقدمه ای بر فرمول های آرایه ای

فرمول های آرایه ای یا به عبارتی Array Formulas راهکاری است که برخی از فرمول های اکسل از آن پشتیبانی می کنند.

اگر دقت کرده باشید در حالتی که فرمول های اکسل را به کار می برید یک یا چند داده به فرمول می دهید و در نهایت آن فرمول با پردازش بر روی آن داده به شما یک خروجی می دهد (منظورم از داده می تواند آدرس سلول و یا یک مقدار ثابت است).

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

استفاده از فرمول های آرایه ای

اما هدف ما توضیح در رابطه با روش تهیه PMS است و می خواهم در رابطه با اینکه این فرمول کجا استفاده شده است توضیح دهم.

اگر به کاربرگ PMS در فایل مراجعه کنید در سلول J5 این فرمول قرار داده شده است:

{=SUM(EXACT($J$2;$K$3:$M$3)*$K5:$M5)}

همانطور که می دانید در این فایل سه نوع وزن (زمانی، فیزیکی و هزینه ای) تعریف شده است و تمام محاسبات پیشرفت بر اساس آن وزنی است که کابر انتخاب کرده است. برای اینکار نیاز است یک ستون تعریف شده و بر اساس انتخاب کاربر (سلول J2) آن را در این ستون قرار دهد. یک راه آسان استفاده از VLookup و یا ترکیب فرمول های Index با Match است که فکر می کنم اکثر دوستان با آن آشنایی دارند. روشی که در این فایل بکار رفته استفاده از فرمول های آرایه ای است.

در این روش ابتدا از فرمول Exact استفاده شده تا بر اساس انتخاب کاربر در سلول J2 یک آرایه را بر گرداند. این آرایه بر اساس انتخابی که کاربر انجام می دهد یا (True,False,False) یا (False,True,False) و یا (False,False,True) خواهد بود. اگر در رابطه با فرمول Exact سوالی داشتید باید بگویم که این فرمول دو داده را باهم مقایسه می کند اگر این دو داده با هم برابر بودند True و گرنه False رو بر می گرداند. در واقع در فرمول بالا Exact سلول J5 را با سلول های K3, L3, M3 مقایسه می کند و همانطور که گفته شد بسته به اینکه کاربر در سلول J2 چه انتخابی را کرده باشد یک آرایه را بر می گرداند.

اگر دقت کنید در فرمول بالا Exact در محدوده K5:M5 ضرب شده است. این ضرب در واقع ضرب دو آرایه در همدیگر است. آرایه اول که بالا توضیح داده شد اما آرایه دوم محدوده ای است که اطلاعات مربوط به سه وزن در آن گنجانده شده است. در اکسل مقادیر True و False به ترتیب 1 و 0 در نظر گرفته می شود و وقتی که دو آرایه را در هم ضرب می کنید تبدیل به آرایه ای می شود که تمامی عناصر اون 0 است به جز آن عنصری که در 1 ضرب شده است. به عنوان مثال

(0/11% , 0/11% , 3/46%) x (0 , 1 , 0) = (0 , 0/11% , 0)

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

نتیجه گیری

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

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

جداول محوری – Pivot Table

بعد از ارائه مطب در خصوص انواع اوزان فایل ارائه‌شده در ادامه بحث به جداول محوری و یا Pivot Table اختصاص یافته است.

یکی از ابزارهای فوق‌العاده کاربردی که در این فایل از آن استفاده شده است ابزار Pivot Table است. این ابزار آن‌قدر کاربردی است که تقریباً در هر فایلی که در آن تحلیلی قرار داشته باشد هر کاربر حرفه‌ای را قانع می‌کند تا از آن ابزار استفاده کند. سرعت بالا، کم بردن منابع مصرفی، انعطاف‌پذیری همه از دلایل مهمی است که کاربران حرفه‌ای اکسل را راضی می‌کند تا از این ابزار استفاده نماید.

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

نکته 1 – ساختار جدول

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

نکته 2 – استفاده از Refresh

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

نکته 3 – اضافه شدن ستون

توجه نمایید که در بعضی شرایط نیاز است که در داده‌های ورودی یک ستون اضافه کنید تا تحلیل نتایج خروجی در Pivot Table راحت‌تر باشد. به‌عنوان‌مثال در فایل ارائه‌شده در کاربرگ PMS یک ستون با عنوان فعالیت (ستون I اکسل) تعریف شده است که صرفاً جنبه اطلاع‌رسانی برای Pivot Table دارد. بدین منظور که برخی محاسبات صورت گرفته بر روی فعالیت‌های پروژه می‌باشد و دیگر نیاز نیست که این محاسبات در WBS صورت گیرد.

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

اضافه شدن ستون‌های AP تا AV در فایل ارائه‌شده نیز به همین دلیل اضافه شده است. محاسبات مربوط به پیشرفت با اعمال وزن‌های مختلف و درصدهای پیشرفت با توجه به ساختار تحلیلی Pivot Table از این طریق امکان‌پذیر بود. در کاربرگ PMS درصدهای پیشرفت در سطح فعالیت بدون اعمال وزن‌های در نظر گرفته‌شده در فایل محاسبه می‌کند. بدین منظور این ستون اضافه شده که وزن فعالیت در درصد پیشرفت ضرب شود و درنهایت این محاسبات در نتایج جداول محوری اعمال شود.

ترکیب جداول محوری و کاربرگ های معمولی

یکی دیگر از نکاتی که مورداستفاده قرار گرفته استفاده از کاربرگ های معمولی برای نمایش اطلاعات جداول محوری است. هرچند فرمت‌های متنوعی برای نمایش اطلاعات در اکسل برای جداول محوری تعریف شده است ولی این نحوه نمایش به مزاج خیلی‌ها خوش نمی‌آید. یک راهکار ساده فرمت دهی جداول محوری با توجه به نیاز خروجی اطلاعات بوده و درنهایت لینک آن در یک کاربرگ دیگر است. اگر توجه نمایید یک کاربرگ به نام Phase Location CS که درصدهای پیشرفت پروژه در فازهای مختلف پروژه نمایش می‌دهد، در این فایل تنظیم شده است. کافی است در این کاربرگ بر روی یک سلول که اطلاعات را نمایش می‌دهد کلیک کنید تا متوجه شوید که کلیه اطلاعات از یک کاربرگ دیگر به نام Phase Location Data (که مخفی شده است) می‌خواند.

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

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

برای علاقه‌مندان باید عرض نمایم که حتی اگر ساختار Pivot Table نیز اگر اصلاح گردد با ترکیب Link و استفاده از Conditional Formatting اکسل می‌توان نتایج خارق‌العاده‌ای را خلق نمود.

استفاده از تاریخ شمسی در PMS

زمان و انرژی خود را بیهوده تلف نکنید. خیلی راحت از تاریخ میلادی استفاده کنید.

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

ساختار شمسی سازی در فایل PMS

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

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

روش به‌کارگیری تاریخ شمسی در PMS

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

رویه کار بسیار ساده است بدین‌صورت که لیست تاریخ‌های شمسی به‌عنوان یک لیست Validation در سلول S1 در کاربرگ PMS تعریف شده است و اجازه می‌دهد تنها مقادیر شمسی که در کاربرگ Date تعریف است نمایش دهد. پس از انتخاب هر تاریخ شمسی با استفاده از یک فرمول جستجو اطلاعات این تاریخ شمسی به تاریخ میلادی تبدیل می‌شود که در سلول P2 قرار می‌گیرد. به همین ترتیب تاریخ اتمام نیز در سلول Q2 وارد می‌شود.

کلیه محاسبات مربوط به زمان همیشه وابسته به یکی از این دو سلول خواهد بود. به‌عنوان‌مثال برای محاسبه پیشرفت برنامه‌ای فعالیت‌ها در دوره قبل (سلول Z7) و همچنین پیشرفت برنامه‌ای این دوره (سلول Y7)، وابسته به دو سلول تاریخ میلادی است که در بالا توضیح داده شد.

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

اگر هنوز مطلب شمسی سازی پریماورا (P6) یا شمسی کردن تاریخ های پریماورا را مطالعه نکردید، می توانید آن را از طریق این لینک مطالعه کنید.

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

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

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

نکته حائز اهمیت برای درک محاسبه درصد پیشرفت برنامه‌ای این است که بین درصدهای پیشرفت فعالیت و پیشرفت در 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 جواب می‌دهد و نمی‌توان در سطوح بالاتر از آن استفاده نمود. در اینجا به خاطر همگنی فرمول ها از همان فرمول ضرب و جمع آن عناصر با همدیگر استفاده شده است.

سخن پایانی

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

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