آموزش ساخت نمودار درصد پیشرفت در اکسل (تصویری + ویدیو)

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

پس اگر میخواهی رسم نمودار درصدی در اکسل را یاد بگیری با من همراه باش تا به صورت تصویری و ویدیویی با هم یک نمودار درصد پیشرفت بسازیم.

پیش نیاز: فرمول محاسبه درصد پیشرفت پروژه در اکسل

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

مثلا فرض کنید یک پروژه کلا ۲۰ فعالیت داره که تا این لحظه ۱۰ فعالیت آن تکمیل شده. در این شرایط فرمول به صورت زیر محاسبه میشود:

۲۰/۱۰=

نتیجه این فرمول ۰.۵ میشود که اگر فرمت سلول را روی Percentage قرار دهید اکسل عدد ۵۰٪ را نمایش میدهد. البته اگر اعداد تعداد کل کارهای پروژه را داخل سلول ها قرار داده اید بهتر است در این فرمول از آدرس سلول ها استفاده کنید.

B2/C2=

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

مثلا فرض کنید وضعیت ۲۰ فعالیت پروژه در محدوده F7:F26 قرار دارد که هر فعالیت انجام شده مقدار TRUE و هر فعالیت انجام نشده مقدار FALSE را نشان میدهد. در واقع تابع COUNTIF تعداد سلول هایی که مقدار آن ها TRUE باشد را میشمارد و در مقابل تابع COUNTA تعداد کل سلول های دارای مقدار را محاسبه میکند. سپس هر دو مقدار تقسیم بر یکدیگر میشود و درصد پیشرفت فعالیت ها مشخص میشود.

فرمول محاسبه درصد پیشرفت به شرح زیر می باشد:

COUNTIF(F7:F26,TRUE)/COUNTA(F7:F26)=

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

روش اول: نمایش درصد در نمودار اکسل با Conditional Formatting (سریع ترین روش)

اول از همه با سریع ترین روش یا همان ابزار Conditional Formatting (قالب بندی شرطی) شروع میکنیم. این ابزار کمک میکند تا ظاهر سلول ها را براساس مقدار یا شرط مشخصی تغییر دهید. برای اینکه بدون ساخت یک نمودار جداگانه بتوانیم مقدار پیشرفت را نشان دهیم باید مراحل زیر را طی کنیم:

1. ابتدا باید سلولی که درصد پیشرفت در آن قرار دارد را انتخاب کنیم.
2. از تب Home روی گزینه Conditional Formatting کلیک میکنیم.
3. داخل منوی باز شده وارد بخش Data Bars میشویم.
4. یکی از مدل های Gradiant Fill یا Solid Fill را انتخاب میکنیم.
5. اکنون اکسل متناسب با مقدار هر سلول یک نوار پیشرفت داخل آن نمایش میدهد.

فراموش نکنید که تنظیمات Minimum و Maximum را به درستی مشخص کنید. برای اینکار دوباره روی ابزار قالب بندی شرطی کلیک کنید و اینبار گزینه Edit Rule را بزنید. سپس داخل پنجره باز شده تنظیمات زیر را انجام دهید:

• مقدار Minimum را روی Number قرار دهید و عدد ۰ را وارد کنید.
• مقدار Maximum را روی Number قرار دهید.
• وقتی اعداد شما داخل اکسل مانند ۰.۵ به شکل ۵۰ درصد ذخیره شده اند باید عدد ماکسیموم را روی ۱ بگذارید.
• از طرفی اگر اعداد شما مستقیما به شکل ۰ تا ۱۰۰ وارد شده اند باید عدد ماکسیموم را روی ۱۰۰ قرار دهید.

نتیجه این روش اینگونه است که اگر از ۲۰ فعالیت ۱۰ فعالیت انجام شده باشد، نمودار پیشرفت عدد ۵۰٪ را نشان میدهد یعنی نوار نصف سلول را پر میکند. یا اگر از ۲۰ فعالیت ۱۵ تا انجام شده باشد، نمودار عدد ۷۵٪ را نشان میدهد که تقریبا سه چهارم سلول پر میشود.

خب حالا میخواهیم روش دوم را برای رسم نمودار درصدی در اکسل یاد بگیریم. در ادامه این روش معرفی میشود.

روش دوم: رسم نمودار درصدی درون سلولی با تابع REPT

داخل روش دوم قصد داریم از تابع REPT استفاده کنیم. به کمک این تابع این امکان را داریم تا بدون استفاده از ابزارهای نمودار داخل سلول نوار پیشرفت بسازیم. به طور مثال ما در سلول C5 پیشرفت پروژه را محاسبه کردیم و مقدار آن ۶۰ درصد است. اکنون باید فرمول زیر را در سلول کناری قرار دهیم:

REPT(“|”,C5*100)=

در واقع تابع REPT یک مقدار مشخص را تکرار میکند. بنابراین اینجا علامت «|» به تعداد درصد پیشرفت فعالیت ها داخل سلول تکرار میشود. پس با توجه به مقدار ۶۰ که به دست آوردیم، اکسل علامت «|» را ۶۰ بار داخل سلول تکرار میکند. معمولا این تابع زمانی استفاده میشود که در کنار هر فعالیت به یک نوار پیشرفت نیاز داشته باشید اما نخواهید از قالب های شرطی استفاده کنید.

روش سوم: ساخت نمودار دماسنجی پیشرفت (Thermometer Chart)

ساخت نمودار درصدی در اکسل با روش نمودار دماسنجی (Thermometer Chart) زمانی بیشترین کاربرد را دارد که بخواهید داخل یک داشبورد یا گزارش های مدیریتی، درصد پیشرفت را نشان دهید. این نمودار باعث میشود تا وضعیت پیشرفت، سریع تر و قابل فهم تر نشان داده شود. حالا بیایید این چارت را روی یک پروژه واقعی پیاده سازی کنیم. فرض میکنیم از ۲۰ فعالیت پروژه ما ۱۲ تای آن انجام شده که میشود که پیشرفت واقعی آن ۶۰ درصد میشود. اما هدف اصلی ما ۱۰۰ درصد است. به همین دلیل یک جدول مانند زیر ایجاد میکنیم.

حالا برای اینکه نمودار را بسازیم باید مراحل زیر را طی کنیم:

1. ابتدا سلول های مربوط به Actual یا Target را انتخاب میکنیم.
2. وارد تب Insert میشویم.
3. از بخش Column or Bar chart گزینه D Clustered Column-2 را انتخاب میکنیم.
4. اکنون روی یکی از ستون های نمودار کلیک میکنیم و وارد Format Data Series میشویم.
5. مقدار Serios Overlap را روی ۱۰۰ قرار میدهیم تا دو ستون روی هم قرار بگیرند.
6. در صورتی که نیاز بود مقدار Gap width را هم کاهش میدهیم تا ستون نمودار ضخیم تر و شبیه دماسنج شود.

پس از انجام این مراحل میبینیم که ستون target پس زمینه یا ظرفیت کامل پروژه را نشان میدهد و ستون Actual میزان پیشرفت واقعی را مشخص میکند. البته یک نکته بسیار مهم را باید در نظر داشته باشیم. اگر اکسل مقدار ۶۰٪ را به شما ۰.۶ نشان میدهد باید مقدار مینیموم محور را روی ۰ و ماکسیموم آن را روی ۱ قرار دهید.

روش چهارم: نمایش درصد با نمودار دونات (Doughnut) و عقربه ای (Gauge)

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

قبل از هر چیزی باید مانند روش قبلی یک جدول جدید بسازیم که من در ادامه این جدول را قرار میدهم.

جهت به کار بردن این نمودار تصور میکنیم مقدار درصد پیشرفت را قبلا در سلول C5 محاسبه کرده ایم. فقط کافیست از فرمول زیر استفاده کنید:

100%-C5=

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

1. مقادیر تکمیل شده و باقی مانده داخل جدول را انتخاب میکنیم.
2. وارد تب Insert میشویم.
3. از بخش Pie or Doughnut chart گزینه Doughnut را انتخاب میکنیم.
4. پس از آن روی نمودار کلیک میکنیم و از قسمت Format data Series ضخامت حلقه را با استفاده از Doughnut Hole Size تغییر میدهیم.
5. در آخر برای بخش تکمیل شده یک رنگ مشخص و برای بخش باقی مانده یک رنگ دیگر انتخاب میکنیم.

بریم سراغ ساخت نمودار نیم دایره ای یا Gauge در اکسل. من میخواهم یک ظاهر حرفه ای تر به نمودارم بدهم بنابراین میتوانم همان نمودار دوناتم را به نمودار نیم دایره ای تبدیل کنم. ولی قبل از آن باید جدولی که ساخته بودم را کمی تغییر دهم.

بعد از آن از این جدول یک Doughnut chart همانطور که آموزش دادم، میسازیم. بخش مربوط به مقدار ۱۰۰ را روی رنگ No Fill قرار میدهیم. بدین ترتیب نیمی از نمودار نامرئی میشود. سپس از قسمت Format Data Series مقدار Angel of first Slice را جوری تغییر میدهیم که قسمت قابل مشاهده نمودار در موقعیت مناسب قرار گیرد.

آیا مهارت های کاربردی مانند محاسبه درصد پیشرفت در اکسل برای مسیر شغلی شما اهمیت دارد؟ در این حالت با این مهارت ها را به صورت کامل و اصولی یاد بگیرید. از طرفی با دریافت مدرک ICDL میتوانید مهارت های خود را در Excel و سایر مهارت های کامپیوتری به شکل رسمی تری ارائه دهید. برای کسب اطلاعات بیشتر درباره شرایط، مراحل و نحوه دریافت این مدرک میتوانید به صفحه مدرک ICDL مراجعه کنید.

اشتباهات رایج در ساخت نمودار پیشرفت اکسل و راه حل آن ها

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

اشتباه گرفتن عدد ۵۰ با ۵۰٪

اولین خطا، اشتباه گرفتن عدد ۵۰ با ۵۰ درصد به شمار می آید. مثلا برخی اوقات ما عدد ۵۰ را به اشتباه وارد میکنیم درصورتی که منظورمان عدد ۵۰ درصد است. در حقیقت داخل اکسل اگر مقدار درصد را به شکل استاندارد وارد کنیم باید عدد ۵۰ درصد به صورت ۰.۵ نمایش داده شود. وقتی فرمت سلول را روی Percentage قرار دهید، به صورت خودکار ۵۰٪ ظاهر میشود. در نتیجه قبل از ساخت نمودار درصدی در اکسل حتما فرمت اعداد خود را بررسی کنید.

به هم ریختن نوار نمایش به دلیل تنظیم نبودن Rule

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

Home > Conditional Formatting > Manage Rules > Edit Rule

زمانی که وارد این پنجره شدیم باید مطمئن شویم که گزینه های Minimum و Maximum با هم هماهنگ باشند. مثلا اگر اعداد شما بین صفر و یک هستند، باید مینموم روی ۰ و ماکسیموم روی ۱ قرار بگیرند. اما اگر داده ها بین صفر و ۱۰۰ باشند باید ماکسیموم را روی ۱۰۰ قرار دهیم. از طرفی چنانچه چند قانون conditional formatting روی یک محدوده اعمال شده باشد، احتمالا رنگ ها یا نمایش نوارها با هم تداخل پیدا کنند. بنابراین در این شرایط بهتره قوانین اضافی را حذف یا اصلاح کنیم.

خطای ارجاع مطلق هنگام کپی کردن فرمول

شاید در اکسل با علامت $ مواجه شده باشید. بیشتر خطاهای رایج مربوط به درصد پیشرفت به خاطر علامت $ ایجاد میشوند. به طور مثال اگر فرمول ما به صورت B2/C2= باشد و آن را به سلول های پایینی کپی کنیم، Excel آن را به شکل B3/C3= تغییر میدهد. ولی وقتی فرمول را به شکل زیر نوشته باشیم:

B$2/$C$2 $=

وقتی این آدرس ها را کپی میکنیم، فرمول تغییر نمیکند. به همین دلیل قبل از کپی کردن باید مشخص کنید، کدام آدرس ثابت بماند و کدام آدرس همراه با ردیف تغییر کند.

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

اگر از این آموزش در پروژه های کاری خود استفاده کردید، خوشحال میشم نظرتان را در بخش کامنت ها برای من به اشتراک بگذارید. من تلاشم را میکنم تا در آموزش های بعدی مباحث کاربردی بیشتری را در زمینه آموزش های رایگان ICDL در اختیار شما قرار دهم.

سوالات متداول

• سوال ۱: چگونه می توانم اعداد درصد را در نوارهای پیشرفت درون سلولی (Data Bars) مخفی کنم؟

داخل بخش Edit Rule گزینه Show Bar Only را فعال کنید.

• سوال ۲: آیا می توان رنگ نوار پیشرفت را بر اساس میزان درصد به صورت خودکار تغییر داد؟

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

• سوال 3: چک باکس های نسخه های جدید اکسل چه تفاوتی با نسخه های قدیمی در پیگیری پیشرفت دارند؟

براساس نسخه جدید اکسل، چک باکس ها به صورت ساده تر و با مقادیر TRUE و FALSE نمایش داده میشوند. ولی در نسخه های قبلی باید از Form Controls و سلول لینک شده کمک میگرفتید.

• سوال 4: اگر درصد واقعی پیشرفت از ۱۰۰٪ عبور کند، چگونه ساختار نمودار دونات را حفظ کنیم؟

باید مقدار نوار نمایشی را با فرمول مثل MIN(C5,100%)= روی حداکثر عدد ۱۰۰ محدود کنید.

• سوال 5: چگونه جهت پر شدن نوار پیشرفت را در فرمت های شرطی از راست به چپ تنظیم کنیم؟

از بخش Edit Rule گزینه Bar Direction را روی Right-To-Left قرار دهید.