سبد خرید
0

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

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

نمودار نقطه ای متحرک در اکسل

نمودار پراکندگی - نمودار Scatter
۵/۵ - (۵ امتیاز)

چطور با نمودار پراکندگی یک نمودار جالب بسازیم

همونطور که قبلا هم صحبت کردیم، توانایی در رسم نمودارهای جذاب اهمیت زیادی داره. چون نمودار یکی از ابزارهای مهم بصری سازی داده ها و انتقال مفهوم داده هاست. قبلا نحوه رسم نمودار Scatter رو تشریح کردیم. حالا میخوایم ی مقدار به جزئیات بپردازیم و با نقاط مختلف روی نمودار کاری کنیم. در واقع وقتی بین داده های نمایش داده شده در یک نمودار Scatter (نمودار پراکندگی) میخواهیم روی یکی از نقاط تمرکز کنیم و بصورت مشخص نمایش بدیم، چکار باید بکنیم؟

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

فرض کنید نموداری مشابه شکل ۱ داریم که میزان فروش رو در قبال هزینه انجام شده برای تبلیغات نمایش میده.

نمودار نقطه ای برای مقایسه میزان فروش در برابر هزینه تبلیغات

شکل ۱- نمودار نقطه ای برای مقایسه میزان فروش در برابر هزینه تبلیغات

حالا میخوایم نقطه خاصی رو (با انتخاب ماه مورد نظر) روی نمودار مشخص کنیم. اگر تعداد نقاط کمتر بود میتونستیم با نمایش برچسب (Label) این کار و انجام بدیم. اما در نمودارهای Scatter عموما تعداد نقاط زیاد هست و امکان نمایش همه لیبل ها وجود نداره و نمودار شلوغ میشه. پس ما به دنبال راهی هستیم که یک نقطه دلخواه رو مشخص کنیم، و لیبل برخی نقاط رو نمایش بدیم.

مشخص کردن مقادیر X  و Y نقطه مورد نظر در نمودار پراکندگی

همونطور که میدونیم نمودار Scatter از یک سری نقطه هایی تشکیل شده که مقادیر X و Y آنها رو داریم. حالا برای اینکه یک نقطه مشخص رو فراخوانی کنیم باید اطلاعات مربوط به اون رو نیز فراخوانی کنیم.

برای این کار میتونیم از تابع Vlookup استفاده کنیم و اطلاعات مربوط به نقطه دلخواه رو فراخوانی کنیم:

فرمولی که برای فراخوانی داده های فروش استفاده میکنیم:

=VLOOKUP(E2; A2:C13 ; 2 ; 0)

فرمولی که برای فراخوانی داده های هزینه تبلیغات استفاده میکنیم:

=VLOOKUP(E2; A2:C13 ; 3 ; 0)

فراخوانی اطلاعات مربوط به نقطه مورد نظر

شکل ۲- فراخوانی اطلاعات مربوط به نقطه مورد نظر

اضافه کردن نقطه مورد نظر به نمودار پراکندگی

حالا که داده ها آماده شد باید نقطه جدید رو به عنوان یک سری عددی به نمودار Scatter قبلی که رسم شده اضافه کنیم. برای این کار مراحل زیر رو انجام میدیم:

  • روی یکی از محورها کلیک راست کرده ، روی select Data کلیک میکنیم و گزینه Add رو انتخاب میکنیم.

اضافه کردن داده های جدید به عنوان نقطه جدید

شکل ۳- اضافه کردن داده های جدید به عنوان نقطه جدید

  • اجزای سری جدید رو مطابق شکل ۳ تخصیص میدیم و Ok رو میزنیم.

در نتیجه یک نقطه با رنگی متفاوت روی نمودار ظاهر خواهد شد و اون نقطه همون ماه هدف هست.

تغییر تنظیمات گرافیکی نقطه مربوط به ماه هدف

حالا میخواهیم تنظیمات گرافیکی روی این نقطه انجام بدیم و نمودار رو جذاب تر کنیم. برای این کار باید روی نقطه مربوط به ماه هدف (نقطه نارنجی رنگ در شکل ۳) کلیک راست کنیم و گزینه Format Data Series رو بزنیم. با این کار پنجره ای سمت راست نرم افزار باز میشه.

تغییر فرمت مربوط به نقطه ماه هدف

شکل ۴- تغییر فرمت مربوط به نقطه ماه هدف

از پنجره باز شده روی گزینه Fill & Line (سطل رنگ) کلیک کرده و از قسمت Marker گزینهMarker Options رو انتخاب میکنیم.

تنظیمات رو مطابق شکل ۵ انجام میدیم. با این تنظیمات، یک دایره تو خالی به رنگ نارنجی اطراف نقطه اصلی نمودار قرار میگیره که نشان دهنده ماه انتخاب شده است.

تنظیمات مربوط به نقطه ماه هدف

شکل ۵- تنظیمات مربوط به نقطه ماه هدف

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

اضافه کردن برچسب (Label) مربوط به ماه هدف

برای اینکه داخل نمودار نشون بدیم که دقیقا کدوم نقطه انتخاب شده و مقدارش چی هست. برای این کار ابتدا نقطه مربوط به ماه مورد نظر رو انتخاب میکنیم و سپس مطابق شکل ۶، تنظیمات مربوط به لیبل رو انجام میدیم.

نمایش برچسب (Label) مربوط به نقطه ماه هدف

شکل ۶- نمایش برچسب (Label) مربوط به نقطه ماه هدف

بصورت پیش فرض، اکسل یکی از مقادیر رو به عنوان برچسب نشون میده که در اینجا مقدار Y رو نشون داده. اگر بخوایم X و Y رو با هم نمایش بده باید اول روی Label مورد نظر کلیک راست کرده و گزینه Format Data Labels رو بزنیم.

نمایش مقدار X و Y در برچسب نمودار

شکل ۷- نمایش مقدار X و Y در برچسب نمودار

حالا اگر بخوایم نام ماه رو روی لیبل نمایش بده باید بعد از انتخاب Format Data Labels گزینه Value From Cells رو بزنیم.

نمایش داده روی نمودار Scatter

شکل ۸- نمایش داده روی نمودار Scatter

اگر بخوایم فقط نام ماه نمایش داده بشه باید بعد از زدن OK تیک مربوط به مقدار X Value و Y Value رو بر میداریم که فقط نام ماه نمایش داده بشه. (شکل ۹)

نمایش نام ماه مورد نظر روی نمودار Scatter

شکل ۹- نمایش نام ماه مورد نظر روی نمودار Scatter

مشخص کردن مکان نقطه مربوط به ماه هدف

برای اینکه نمایش بهتری از نمودار داشته باشیم میتونیم مکان نقطه مورد نظر (ماه هدف) رو روی محور X و Y نمایش بدیم (تصویر نقطه ماه هدف روی محور X و Y). برای این کار مطابق شکل ۱۰ گزینه Error Bars رو انتخاب میکنیم.

استفاده از Error Bars برای نمایش مکان نقطه در نمودار

شکل ۱۰- استفاده از Error Bars برای نمایش مکان نقطه در نمودار

حالا روی میله (افقی) ایجاد شده روی نقطه مورد نظر کلیک راست کرده و Format Error Bars رو انتخاب میکنیم. از پنجره نمایش داده شده در سمت راست تنظیمات مطابق شکل ۱۱ انجام میدیم.

انجام تنظیمات Error Bars برای نمایش تصویر نقطه روی محور Y

شکل ۱۱- انجام تنظیمات Error Bars برای نمایش تصویر نقطه روی محور Y

همین مراحل رو برای Error Bar عمودی نیز انجام میدیم. در نهایت نمودار بصورت زیر (شکل ۱۲) نمایش داده خواهد شد.

 نمایش تضویر نقطه ماه هدف روی محور عمودی و افقی با استفاده از Error Barها

شکل ۱۲- نمایش تضویر نقطه ماه هدف روی محور عمودی و افقی با استفاده از Error Barها

نکته:
گاهی اوقات ممکنه انتخاب اجزای مختلف نمودار بخاطر رو هم قرار گیری مشکل باشه. در این مواقع از مسیر زیر و مطابق شکل ۱۳ میتونیم اجزای مختلف نمودار رو به راحتی انتخاب کنیم.

Chart Tools > Format> Current Selection

 

نحوه انتخاب اجزای مختلف نمودار

شکل ۱۳- نحوه انتخاب اجزای مختلف نمودار

در نهایت میتونیم تنظیمات گرافیکی مربوط به Error Bars رو به دلخواه تغییر بدیم. برای این کار روی خط مورد نظر کلیک راست کرده و Format Error Bars رو انتخاب میکنیم. از پنجره نمایش داده شده و از قسمت Fill & Line (سطل رنگ) تنظیماتی مطابق شکل ۱۴ انجام میدهیم.

تغییر فرمت خط عمودی Error Bar

شکل ۱۴- تغییر فرمت خط عمودی Error Bar

همین تنظیمات رو برای خط افقی هم انجام میدیم و در نهایت نموداری بصورت زیر خواهیم داشت:

نمایش یک نقطه در نمودار به عنوان Benchmark

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

 رسم نمودار Scatter داینامیک با یک مبنا-Benchmark

شکل ۱۵- رسم نمودار Scatter داینامیک با یک مبنا-Benchmark

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

دانلود فایل آموزش نمودار پراکندگی متحرک

برای دانلود فایل نمودار Scatter روی دکمه زیر کلیک کنید:

133

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

دیدگاه کاربران
  • safasafy ۱۹ دی ۱۳۹۷ / ۴:۱۷ ب٫ظ

    با سلام و عرض ادب
    می خواهم وقتی با موس روی نقطه ای از نمودار قرار می گیریم ، کادر نمایشی نام و مقدار نقطه که بصورت اتوماتیک ظاهر می شود را خودم ویرایش نمایم و داده های مورد نظرم را نمایش دهم . از لیبل پویا نیز استفاده کردم و این ها روی نمودار ثابت می مانند در حالی که می خواهم وقتی از روی نقطه ، موس برداشته شد کادر نیز نمایش داده نشود . لطفا اگر امکان دارد توضیح لازم را عنایت فرمایید
    با تشکر از زحمات شما و سرکار خانم مهندس خاکزاد
    اسحاقی – ۹۷۱۰۱۹

    • آواتار
      حسنا خاکزاد ۲۹ دی ۱۳۹۷ / ۲:۴۶ ب٫ظ

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

      https://excelpedia.net/excel-dashboards/

ارسال دیدگاه

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

توسط
تومان