رسم نمودار Box & Whisker در اکسل

نمودار Box & Whisker از جمله پر کاربردترین نمودارها در اکسل برای مطالعات آماری میباشد. شکل زیر یک نمونه نمودار باکس اند ویسکر در اکسل را نشان میدهد:

Box-&-Whisker-Chart-in-Excel

برای یادگیری و درک کاربرد نمودار Box & Whisker در اکسل در ابتدا نیاز است کمی در مورد مبحث مطالعه توزیع داده ها در آمار آشنا شویم و سپس روش ترسیم نمودار Box & Whisker در اکسل را با یک مثال فرا میگیریم.

مطالعه توزیع داده ها

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

Box-&-Whisker-Table

همانگونه در در جدول بالا مشاهده میکنید، تعداد کالای فروخته شده توسط حامد در ۵۵ هفته ثبت شده است، برخی هفته ها فروش بیشتر و برخی کمتر است، حال میخواهیم بدانیم بیشترین و کمترین تعداد فروش چه بوده است؟ اختلاف بین آنها یا Range فروش چقدر بوده است؟ در اکثر هفته ها چه تعدادی فروخته شده است یا به عبارتی معمولاً در هر هفته چقدر فروش داشته است و میانگین فروش در هر هفته چقدر بوده است؟ پاسخ به این سوالات در واقع بررسی توزیع داده ها میباشد.

مفاهیم اساسی نمودار Box & Whisker

در بررسی توزیع داده ها و رسم نمودار Box & Whisker با مفاهیم زیر آشنا میشویم:

  • Maximum، که در واقع بیشینه اعداد میباشد، بزرگترین عدد در مجموعه اعداد مورد مطالعه، (ماکزیمم توسط تابع MAX در اکسل محاسبه میشود.)
  • Minimum، که در واقع کمینه اعداد میباشد، کوچکترین عدد در مجموعه اعداد مورد مطالعه (مینیمم توسط تابع MIN در اکسل محاسبه میشود.)
  • Range، اختلاف بین بزرگترین و کوچکترین عدد میباشد و نشان میدهد داده های عددی ما در چه پهنه ای گسترده میباشند.
  • Median یا میانه، عددی است که تعداد مساوی از اعداد مجموعه از آن بزرگتر و کوچکتر باشند، به عنوان مثال اگر شما ۵۵ عدد در مجموعه خود دارید و آنها را به ترتیب کوچک به بزرگ مرتب نمایید، عدد بیست و هشتم میانه میباشد، زیر ۲۷ عدد از آن بزرگتر و ۲۷ عدد از آن کوچکتر هستند. اگر ۵۶ عدد در مجموعه داشته باشیم، میانگین اعداد بیست و هشتم و بیست و نهم میانه میباشد، زیرا ۲۸ عدد از این عدد کوچکتر و ۲۸ عدد از آن بزرگتر هستند. (میانه توسط تابع MEDIAN در اکسل محاسبه میشود.)
  • Average یا میانگین، میانگین عددی اعداد مجوعه میباشد، یعنی مجموع اعداد تقسیم بر تعداد آنها (میانگین توسط تابع AVERAGE در اکسل محاسبه میشود.)
  • First Quartile یا Q1، این عدد در واقع میانه یا Median اعداد بین Minimum مجموعه و Median آن میباشد، در ذهن خود اعداد کل مجموعه را از کوچک به بزرگ مرتب نمایید، حال اعداد را در دو دسته تقسیم کنید، دسته اول از عدد Minimum تا میانه (Median) کل اعداد و دسته دوم از میانه کل تا Maximum، حال برای هر دسته میانه یا Median را بدست بیاورید و هر دسته را به دو دسته تقسیم نمایید، به این ترتیب Q1 میانه دسته اول و Q3 میانه دسته دوم میباشد.
  • Third Quartile یا Q3،  این عدد در واقع میانه یا Median اعداد بین Maximum مجموعه و Median آن میباشد.
  • Interquartile Range، یا IQR اختلاف بین Q1  و Q3 میباشد.

برای محاسبه Q1 و Q3 دو رویکرد وجود دارد، در رویکرد اول عدد میانه کل را نیز در محاسبه دخیل میکنیم که به آن Inclusive Quartile میگوییم و در رویکرد دوم عدد میانه را در محاسبات در نظر نمیگیریم که آن را Exclusive Quartile مینامیم، تصور کنید ۵۵ عدد را مرتب کرده اید و عدد ۲۸ میانه است، حال اگر Q1 را با در نظر گرفتن عدد اول تا بیست و هشتم محاسبه نمایید (میانگین عدد ۱۴ و ۱۵) در این حالت Inclusive میباشد، اگر Q1 را با در نظر گرفتن عدد اول تا ۲۷ محاسبه کنید (عدد چهاردهم) در این حالت Exclusive محاسبه کرده اید. Q1 و Q3 توسط توابع QUARTILE.INC و QUARTILE.EXC در اکسل محاسبه میشوند.

در جدول نمایش داده شده در زیر، داده های مربوط به فروش حامد در ۵۵ هفته مورد مطالعه، به صورت صعودی از کمترین به بیشترین مرتب شده است و دسته بندی داده ها نمایش داد شده است:

Sample-Table-Sorted-Chart

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

Box-&-Whisker-Samlpe-Calculation

 معرفی نمودار Box & Whisker

نمودار Box & Whisker در واقع از یک Box و دو Whisker تشکیل شده است که توزیع داده ها را بر اساس موارد ذکر شده در این مقاله نمایش میدهد، شکل نمودار Box & Whisker به صورت زیر است:

Box-&-Whisker-sample

در نمودار بالا، Lower Quartile همان Q1 میباشد و Upper Quartile در واقع Q2 است، Box بین Q1 و Q3 تعریف میشود و هر چه ارتفاع باکس بیشتر باشد نشان دهنده تمرکز کمتر داده ها و گستردگی بیشتر آنها میباشد، لیکن میتوان در نظر گرفت ۵۰ درصد داده ها در محدوده Box قرار دارند. در شکل فوق Minimum و Maximum به ترتیب با Lower Extreme و Upper Extreme نمایش داده شده اند.

گاهی در مطالعه داده های پیش می آید که عددی خارج از Range میباشد به صورتی که داده پرت به حساب آید، این عدد با Outlier نمایش داده میشود. در واقع اعداد کمتر از Q1-1.5*IQR و بزرگتر از Q+1.5*IQR در نمایش داده ها Outlier به شمرده میشوند.

شکل زیر ارتباط بین نمودار Box & Whisker و توزیع نرمال داده ها و انحراف معیار را نمایش میدهد:

Box-&-Whisker-and Standard Deviation

نمودار Box & Whisker یا بکس اند ویسکر نشان دهنده توزیع داده های مربوط به فروش حامد در ۵۵ هفته مورد بررسی میباشد که در ابتدای مقاله به ان اشاره شد:

Box-&-Whisker-Chart-Sample

رسم نمودار Box & Whisker در اکسل

تا اینجا با مفاهیم اساسی و دلیل ترسیم نمودار Box & Whisker آشنا شدیم، حال میخواهیم روش ترسیم این نمودار را در اکسل فرا بگیریم، داده های زیر را در نظر بگیرید، در این جدول فهرستی از ۶۱۰ فروش یک شرکت قرار دارد، هر سطر این جدول فروش یکی از افراد این شرکت را نشان میدهد، این شرکت ۸ فروشنده دارد و تعداد فروخته شده در هر سطر در ستون No. of Sale نمایش داده شده است. که میخواهیم با استفاده از نمودار Box & Whisker در اکسل توزیع داده های عددی مربوط به هر فروشنده را نمایش دهیم.

برای تمرین روش رسم نمودار Box & Whisker در اکسل، با کلیک کردن بر روی این خط، فایل اکسل نمونه را دانلود کنید.

شکل زیر نمایی از جدول داده ها را نمایش میدهد:

Sample-Table

برای شروع ترسیم نمودار Box & Whisker در اکسلف ابتدا بر روی یک سلول خالی کلیک نمایید، سپس در تب Insert و در گروه Charts بر روی نمودارهای آماری کلیک نموده و نمودار Box & Whisker را انتخاب کنید:

Insert-Tab-Box-&-Whisker-Char

بر روی نمودار ایجاد شده کلیک راست نمایید و گزینه Select Data را انتخاب کنید، پنجره  Select Data Source نمایش داده میشود، در قسمت سمت چپ بر روی گزینه Add کلیک کنید و پنجره Edit Series را فعال نمایید، در این پنجره در قسمت Series Name نام دلخواه را تایپ کنید و یا آدرس سلول حاوی نام دلخواه را انتخاب نمایید. در قسمت Series Value ستون No. of Sale (محدوده G5:G614) جدول داده ها را انتخاب نمایید:

Add-Series-for-Box-&-Whisker-Chart

سپس در پنجره select Data Source در قسمت سمت راست بر روی گزینه Edit کلیک و در پنجره Axis Lable سلول های ستون Sales person (محدوده C5:C614) را انتخاب کنید.

Add-Category-for-Box-&-Whisker-Chart

پس از انجام این کار، رسم نمودار Box & Whisker در اکسل به پایان رسیده است، ویرایش این نوع نمودار مشابه ویرایش سایر نمودارها در اکسل میباشد که در مقالات پیشین مفص در مورد آنها صحبت شده است. نمودار رسم شده به صورت زیر است:

Box-&-Whisker-Result-Chart

برای تمرین روش رسم نمودار Box & Whisker در اکسل، با کلیک کردن بر روی این خط، فایل اکسل نمونه را دانلود کنید.

دوره آموزش تصویری رسم نمودارهای آماری در اکسل

مدرس دوره: حامد قدیمی

در این دوره آموزشی رسم نمودارهای آماری در اکسل (یا Statistical Charts) را فرا میگیریم، نمودارهای Statistical در اکسل شامل نمودارهای هیستوگرام یا Histogram، نمودار پارتو یا Pareto و نمودار Box & Whisker میباشد. در پایان این دوره آموزشی با نمودارهای Histogram، Pareto و نمودار Box & Whisker در اکسل آشنا میشویم و روش ترسیم و ویرایش این نمودارها را فرامیگیریم. علاوه بر این، در آموزش نمودار Box & Whisker با برخی مفاهیم و توابع آماری مربوط به مطالعه توزیع داده ها نیز آشنا میشویم و از آنها برای درک کاربرد نمودار Box & Whisker استفاده میکنیم.

برای خرید دوره آموزشی رسم نمودارهای آماری در اکسل بر روی این خط کلیک نمایید.

در فیلم زیر سرفصل های دوره رسم نمودارهای آماری در اکسل را توضیح داده شده است:

حاصل دوره:

آشنایی کامل با نمودارهای آماری یا Statistical Charts در اکسل و روش رسم و ویرایش نمودارهای Histogram، Pareto و Box & Whisker

سرفصل های دوره:

  • معرفی محتوای دوره و نمودارهای Histogram، Pareto و Box & Whisker
  • آشنایی با کاربرد نمودار Histogram
  • روش رسم و ویرایش نمودار Histogram در اکسل
  • دسته بندی داده ها در Category های مختلف، Bin ها، تعیین Bin Width و غیره
  • آشنایی با نمودار Pareto و روش رسم و ویرایش آن در اکسل
  • آشنایی با مفهوم توزیع داده ها و مفاهیم Max، Min، Mean or Average، Median، Range، Q1، Q2، Q3، IQR و اصول نمودار Box & Whisker
  • اجزای نمودار Box & Whisker
  • روش رسم و ویرایش نمودار Box & Whisker در اکسل

اشتراک گذاری در شبکه های اجتماعی: share on facebook share on google plus share on linkedin

۲ نظر

دیدگاهتان را بنویسید؟