نمودار داینامیک اکسل با خط شاخص متغیر (آموزش گام‌به‌گام + تصویر)

نظر شما در مورد این آموزش

آنچه در این مقاله میخوانید:

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

گام اول) انتخاب داده ها و رسم نمودار

گام دوم) تبدیل خط شاخص به نمودار خطی

گام سوم) محاسبه فاصله از خط مبنا با فرمول IF

گام چهارم) اضافه کردن سری جدید به نمودار داینامیک

گام آخر) داینامیک کردن خط شاخص در نمودار

دانلود فایل نمونه نمودار داینامیک

مشاهده ویدئو ساخت نمودار داینامیک

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

نمودار داینامیک در اکسل ابزاری قدرتمند برای تحلیل داده‌ها و ساخت داشبوردهای حرفه‌ای هست. در این آموزش گام‌به‌گام یاد می‌گیرید چگونه یک نمودار ستونی با خط شاخص متغیر طراحی کنید که فاصله مقادیر از خط مبنا را به صورت خودکار محاسبه و نمایش می‌دهد. این تکنیک برای گزارش‌های مدیریتی و ارائه‌ها بسیار کاربردی است!”

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

گام اول) انتخاب داده ها و رسم نمودار

داده ها رو انتخاب میکنیم و از تب insert روی نمودار Stacked Column کلیک میکنیم.

شکل ۱- داده های نمودار ستونی و خط مبنا

گام دوم) تبدیل خط شاخص به نمودار خطی

بعد از اینکه نمودار رسم شد، قسمت مربوط به خط بودجه را انتخاب میکنیم و از Chart Design روی گزینه change chart type کلیک کرده و از قسمت combo نوع نمودار رو Line انتخاب میکنیم.

شکل ۲- رسم نمودار اولیه با شاخص مبنای ثابت

گام سوم) محاسبه فاصله از خط مبنا با فرمول IF

حالا میخوایم هر کدوم از ماهها که کمتر از خط شاخص هستن، مقدارشون تا خط شاخص نمایش داده بشه با رنگ دیگه. مثلا اردیبهشت که مقداری معادل ۴۵ داره، تا خط مبنا یعنی ۷۰ فاصله ای معادل ۲۵ داره. پس میخوایم روی اردیبهشت، یک مستطیل به اندازه ۲۵ نمایش داده بشه. برای این کار باید فرمولی بنویسیم که بتونه این ختلاف رو محاسبه کنه. پس طبق تصویر زیر، فرمول if می نویسیم و سری جدید را ایجاد میکنیم.

=IF(B2<C2,C2-B2,NA())

شکل ۳- محاسبه فاصله مقادیر کمتر از خط مبنا

توجه داشته باشید که مقادیری که از خط مبنا بیشتر هستن، قرار نیست در نمودار نمایش داده شوند برای همین برای مثلا ماه خرداد که مقدار ۷۷ داره، این فرمول NA() نمایش داده است. همونطور که میدونیم، NA() در نمودار نمایش داده نمیشه.

گام چهارم) اضافه کردن سری جدید به نمودار داینامیک

حالا کافیه سری جدید را به نمودار اضافه کنیم. برای این کار روی نمودار کلیک راست کرده و از قسمت Select Data روی Add کلیک میکنیم و سری جدید را مطابق شکل زیر اضافه میکنیم:

شکل ۴- اضافه کردن سری جدید به نمودار

بعد از زدن ok سری جدید به نمودار اضافه میشه اگر نوع نمودار خطی بود باید مشابه مرحله ۲ نوع نمودار را به stacked column تغییر دهیم.

شکل ۵- اضافه کردن فاصله مقدار هر ماه تا خط مبنا

گام آخر) داینامیک کردن خط شاخص در نمودار

حالا در مرحله آخر میخواهیم خط شاخص رو داینامیک کنیم. در واقع بتونه با زدن دکمه ای بالا و پایین بره و این مقادیر رو بصورت داینامیک نمایش بده. برای این کار در ستون خط بودجه (مبنا)، روی سلول C2 عدد مورد نظر رو تایپ میکنیم و بقیه سلول های این ستون رو مساوی با سلول C2 قرار میدیم. مطابق شکل ۶.

شکل ۶- داینامیک کردن خط مبنا

در حال حاضر اگر مقدار سلول C2 رو تغییر بدیم بقیه سلول ها و به تبع اون فرمول های نوشته شده و مکان خط مبنا تغییر میکنه. حالا برای اینکه این تغییر دستی نباشه و حرفه ای تر بنظر برسه از ابزارهای فرم کنترل استفاده میکنیم. برای این کار، از تب Developer روی Spin Button کلیک کرده و یک شمارنده ایجاد میکنیم. حالا برای تنظیمات روی Spin Button کلیک راست کرده و از قسمت Format Control تنظیمات رو مطابق با شکل ۷ انجام میدیم. توجه کنید که Cell Link این ابزار باید سلول C2 باشه که با تغییر اون، بقیه هم تغییر کنن.