نسخه 3 افزونه تقویم شمسی با امکانات بیشتر منتشر شد!!! (برای مشاهده اینجا کلیک کن)
سبد خرید
0

محصولی در سبد خرید نیست.

بازگشت به فروشگاه
تمام دسته ها
  • تمام دسته ها
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • Power BI
  • Power Pivot
  • Power Query
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

نمودار گلوله
نظر شما در مورد این آموزش

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

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

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

Bullet Chart

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

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

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

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

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

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

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

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

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

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

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

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

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

داده مربوط به مقدار واقعی فروش

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

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

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

داده مربوط به مقدار رضایت از فروش

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

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

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

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

رسم نمودار 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

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

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

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

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

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

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

134

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان