رسم نمودار کاربردی گلوله ای در اکسل

نمودار بولت در اکسل (Bullet Chart یا Bullet Graph)

نمودار گلوله ای یا Bullet Chart در اکسل، دقیقا اطلاعات مربوط به نمودار گیج یا نمودار عقربه ای رو نشون میده. اما نمودار بولت، فضای کمتری رو اشغال میکنه. در واقع نمودار بولت جایگزین خوبی برای نمودار عقربه ای یا Gauge بشمار میره.

این نمودار هم بصورت افقی رسم میشه و هم عمودی. در این مقاله هر دو نمودار رو شرح میدیم.

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

داده های مورد نیاز

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

شکل ۱- نمودار بولت – داده های مورد نیاز

حالا برای رسم این نمودار، باید داده ها رو آماده کنیم.

برای رسم این نمودار میخواهیم از ترکیب نمودار ستونی (Stacked Column) و نقطه ای (Scatter) استفاده کنیم. همونطور که میدونیم، برای اینکه رنگ های متفاوت در نمودار داشته باشیم، باید سری های متفاوتی برای هر رنگ تعریف کنیم. پس به شرح زیر عمل میکنیم:

داده های مورد نیاز برای نمایش مقدار محدوده نارضایتی از فروش:

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

شکل ۲- نمودار بولت – ایجاد داده های مورد نیاز- داده مربوط به محدوده نارضایتی از فروش

داده های مورد نیاز برای نمایش مقدار واقعی فروش:

مقدار واقعی فروش برابر است با سلول B6 و در قسمت وسط، فرمولی نوشته شده که در صورت بالا پایین شدن مقدار واقعی، نمودار به درستی رسم بشه. (در ادامه وقتی نمودار رسم بشه، با تغییر مقدار واقعی فروش، تحلیل و درک این فرمول ها ساده تر خواهد شد)

شکل ۳- نمودار بولت – ایجاد داده های مورد نیاز- داده مربوط به مقدار واقعی فروش

داده های مورد نیاز برای نمایش محدوده رضایت از فروش:

عینا محاسبات مربوط به محدوده نارضایتی رو برای این قسمت نیز انجام میدیم: (شکل ۴)

شکل ۴- نمودار بولت – ایجاد داده های مورد نیاز- داده مربوط به مقدار رضایت از فروش

حالا که داده های مور نیاز آماده شد، در ادامه، نحوه رسم نمودار و انجام تنظیمات مورد نیاز رو شرح خواهیم داد:

مراحل رسم نمودار گلوله یا Bullet Chart

برای رسم نمودار، محدوده A11:A13 رو انتخاب میکنیم و از تب Insert و از قسمت نمودارها، نمودار Stacked Column رو انتخاب میکنیم. نموداری مطابق با شکل ۵ رسم خواهد شد.

شکل ۵- نمودار بولت- رسم نمودار Stacked Column در نمودار گلوله

حالا باید فاصله بین ستون ها رو به صفر برسونیم. برای این کار روی یکی از ستون ها کلیک راست کرده و از قسمت Format Data Series مقدار Gap Width رو روی صفر تنظیم میکنیم.

شکل ۶- نمودار بولت – حذف فاصله بین ستون ها

محدوده نارنجی رنگ، محدوده نارضایتی، محدوده خاکستری، محدوده رضایت و ستون آبی رنگ مقدار واقعی فروش رو نشون میده. برای تعیین محدوده عالی فروش (یعنی میزان ۱۳۰) از پس زمینه نمودار یعنی Plot Area کمک میگیریم. به اینصورت که مقدار ماکزیمم محور عمودی رو روی ۱۳۰ تنظیم میکنیم و با انتخاب Plot Area رنگش رو تنظیم میکنیم. مطابق شکل ۷

شکل ۷ – نمودار بولت – تنظیمات مربوط به حد عالی فروش

حالا کافیه Gridline رو حذف کرده و رنگ Plot Area رو مطابق با خواستمون تنظیم میکنیم. در نهایت، به شکل ۸ میرسیم. در شکل ۸، محدوده عالی فروش یعنی حد فاصل بین ۱۰۰ تا ۱۳۰ محدوده پس زمینه نمودار یعنی Plot Area هست که تغییر رنگ داده شده. برای تغییر رنگ این محدوده کافیه روی پس زمینه نمودار کلیک کرده و از قسمت Format رنگ دلخواه رو انتخاب کنیم.

شکل ۸ – نمودار بولت – تغییر رنگ محدوده های مورد نظر

مرحله بعد اضافه کردن هدف فروش به نمودار هست. برای این قسمت باید مقدار مربوط به هدف فروش رو در قالب نمودار نقطه ای Scatter اضافه کنیم. پس ابتدا سری جدید رو به نمودار اضافه میکنیم و بعد نوع نمودار رو روی Scatter تنظیم میکنیم. رو ینمودار کلیک راست کرده و از قسمت Select Data داده های مربوط به هدف فروش رو تعیین میکنیم. در واقع نقطه ای رو به نمودار باید اضافه کنیم با این مختصات: (۲, B7) که دقیقا وسط نمودار قرار بگیره. بعد از اضافه کردن این محدوده به نمودار، نمودار به شکل ۹ تغییر میکنه.

شکل ۹- نمودار بولت – اضافه کردن سری مربوط به مقدار هدف به نمودار

حالا باید نمودار اضافه شده رو انتخاب کنیم و از تب Design و گزینه Change Chart Type نوع نمودار رو به Scatter With Straight Line تغییر میدیم.

شکل ۱۰ – نمودار بولت – تغییر نمودار مربوط به مقدار هدف فروش

حالا باید اصلاح کوچکی انجام بدیم. اونم اصلاح مقادیر X , Y نمودار نقطه ای. برای این کار روی Select Data کلیک کرده و Series4 رو انتخاب کرده و Edit رو میزنیم. در پنجره نمایش داده شده، مقدار X و Y نمودار رو مطابق شکل ۱۱ اصلاح میکنیم:

شکل ۱۱- نمودار بولت – اصلاح مختصات مربوط به نقطه هدف فروش

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

برای این کار، نقطه مورد نظر رو انتخاب کرده و از مسیر زیر More Error Bars رو انتخاب میکنیم:

Design / Add Chart Elements / Error Bars

از پنجره باز شده تنظیمات مطابق شکل ۱۲ انجام میدهیم. قبل از انجام تنظیمات، Error Bar عمودی رو Delete میکنیم و فقط Error Bar افقی رو نگه میداریم.

شکل ۱۲- نمودار بولت – انجام تنظیمات مربوط به مقدار هدف فروش

نقطه مورد نظر رو انتخاب کرده و از قسمت فرمت مربوط Marker Options روی None کلیک میکنیم. با این کار، نقطه مورد نظر رو نمودار نمایش داده نمیشه و فقط خط Error Bar مربوط به اون نقطه نمایش داده میشه. حالا با کلیک راست روی خط افقی، از قسمت Format تنظیمات مربوط به ضخامت خط و رنگ نمودار رو مطابق با شکل ۱۳ انجام میدیم.

شکل ۱۳ – نمودار بولت – تنظیمات گرافیکی مربوط به خط هدف فروش

در مرحله آخر هم کافیه محور افقی نمودار، عنوان نمودار و راهنمای اون رو حذف کنیم. تا نمودار به شکل ۱۴ دربیاد.

شکل ۱۴- نمودار بولت – نمودار گلوله – Bullet Chart

 برای رسم نمودار بولت افقی، باید همه مراحلی که تشریح کردیم رو، روی نمودار Stacked Bar انجام بدیم. اما یک تغییر کوچیک و البته مهم در این مراحل مورد نیاز هست. میتونید حدس بزنید چه تغییری؟

جواب رو در ادامه برای ما بصورت کامنت ثبت کنید.

چند مقاله جذاب دیگه که حتما پیشنهاد میکنم ببینی:

• نمودار نقطه ای متحرک در اکسل
• رسم نمودار ترکیبی (نمایش حدود) در اکسل
• مقایسه نحوه عملکرد واقعی و برنامه ای با نمودار در اکسل

دانلود فایل نمونه این آموزش

هر دو نمودار بولت عمودی و افقی در فایل زیر وجود داره و میتونید دانلود کنید.