رسم نمودار Histogram و Pareto در اکسل

نمودار Histogram از جمله پر کاربردترین نمودارها در اکسل برای مطالعات آماری میباشد. کاربران اکسل معمولاً از این نمودار کمتر استفاده میکنند، مطمئناً بعد از خواندن این مقاله و آشنایی با کاربردهای گسترده نمودار Histogram این نمودار را در حرفه خود به کار خواهید برد. همچنین نمودار Pareto در اکسل نیز مشابه نمودار Histogram میباشد و روش رسم این دو نمودار نیز کاملاً یکسان است. در این مقاله با نمودار Pareto و کاربرد آن هم آشنا میشویم. شکل زیر یک نمونه نمودار هیستوگرام یا Histogram در اکسل را نشان میدهد:

Histogram chart in Excel

برای یادگیری و درک کاربرد نمودار Histogram و Pareto در اکسل رسم این نمودار را با یک مثال یاد میگیریم و کاربرد آن را درک میکنیم.

رسم نمودار Histogram در اکسل

برای یادگیری روش رسم نمودار Histogram در اکسل از یک مثال کاربردی استفاده میکنیم. داده های زیر را در نظر بگیرید، در این جدول فهرستی از ۶۱۰ فروش یک شرکت قرار دارد، هر سطر این جدول فروش یکی از افراد این شرکت را نشان میدهد، این شرکت ۸ فروشنده دارد که ۸ کالای مختلف را در ۱۰ شهر متفاوت به صورت نقدی یا غیر نقدی در سال های مختلف به فروش رسانده اند و تعداد فروخته شده در هر سطر در ستون No. of Sale نمایش داده شده است. تصور کنید میخواهیم در یک نمودار ستونی میزان فروش افراد مختلف را با هم مقایسه کنیم، یا اینکه میخواهم میزان فروش در شهرهای مختلف را در یک نمودار ستونی با یکدیگر مقایسه نماییم. مانند شکل بالا (شکل اول مقاله).

پیش از امکان ایجاد نمودار هیستوگرام یا Histogram Chart در اکسل و در نسخه های پیشین (نسخه های قبل از نسخه ۲۰۱۳ اکسل)، برای رسم چنین نموداری، یک کاربر اکسل نیاز دارد که اسم شهرهای مختلف را لیست نماید و با استفاده از تابعی مانند SUMIF مجموع فروش هر در هر شهر را بدست آورد و سپس نمودار ستونی مورد نظر را ترسیم کند، با استفاده از نمودار Histogram در اکسل توزیع داده های عددی مربوط به هر شهر یا کالا در قالب نمودار ستونی به راحتی قابل ترسیم است.

برای تمرین روش رسم نمودار Histogram و Pareto در اکسل، با کلیک کردن بر روی این خط، فایل اکسل نمونه را دانلود کنید.

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

Histogram Sample Data

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

Insert Histogram Chart

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

با توجه به اینکه داده ها در فایل اکسل نمونه به صورت Table ذخیره شده اند، به جای ستون حاوی اعداد در کادر Series Value میتوان عبارت زیر را نوشت (نام جدول در فایل اکسل نمونه ExcelSample است که در تب Design قابل خواندن است):

ExcelSample[No. of Sale]

عبارت بالا به معنی انتخاب تمام داده های Field یا ستون “No. of Sale” جدول “ExcelSample” میباشد. به این ترتیب با به روز شدن جدول نمودار نیز به صورت خودکار به روز میشود و نیازی به به روز رسانی داده های وردی نمودار نمیباشد.

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

سپس در پنجره Select Data Source در قسمت سمت راست بر روی گزینه Edit کلیک و در پنجره Axis Labels سلول های ستون City (محدوده E5:E614) را انتخاب کنید.

با توجه به اینکه داده ها در فایل اکسل نمونه به صورت Table ذخیره شده اند، به جای ستون حاوی نام شهرها در کادر Axis Label Range میتوان عبارت زیر را نوشت (نام جدول در فایل اکسل نمونه ExcelSample است که در تب Design قابل خواندن است):

ExcelSample[City]

عبارت بالا به معنی انتخاب تمام داده های Field یا ستون “City” جدول “ExcelSample” میباشد. به این ترتیب با به روز شدن جدول نمودار نیز به صورت خودکار به روز میشود و نیازی به به روز رسانی داده های وردی نمودار نمیباشد.

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

پس از انجام این کار، رسم نمودار Histogram در اکسل به پایان رسیده است. نمودار رسم شده به صورت زیر است:

Histogram chart in Excel

اگر به جای رسم نمودار مقایسه شهرها بخواهیم فروش افراد مختلف را با هم مقایسه نماییم، کافیست تنها در قسمت Horizontal Axis به جای ستون شهرها ستون فروشنده ها را انتخاب کنیم، به این ترتیب نمودار زیر ترسیم میشود:

Histogram Sample- Sales Person

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

Histogram Sample Goods

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

Histogram Sample-Years

و برای مقایسه روش های نقدی و غیر نقدی نمودار Histogram ترسیم شده به صورت شکل زیر است:

Histogram Sample- status

برای تمرین روش رسم نمودار Histogram و Pareto در اکسل، با کلیک کردن بر روی این خط، فایل اکسل نمونه را دانلود کنید.

 دسته بندی اعداد در نمودار هیستوگرام

تا اینجا به یکی از کاربردی ترین جنبه های نمودار هیستوگرام یا Histogram آشنا شدیم، نمودار هیستوگرام برای دسته بندی اعداد نیز مورد استفاده قرار میگیرد، در جدول مثال بالا و در ستون تعداد فروش، کوچکترین عدد ۱۰۵ و بزرگترین عدد ۴۹۹ میباشد. تصور کنید در مثال بالا میخواهید بدانید تعداد فروش های در بازه ۱۰۰ تا ۲۰۰ در مقایسه با تعداد فروش های در بازه ۲۰۰ تا ۳۰۰ چقدر بوده است و این دو بازه (Bin) را در یک نمودار ستونی مشاهده نمایید. به این ترتیب نیاز به تعریف Bin دارید که در واقع همان بازه های عددی هستند و داده های عددی متناسب با هر Bin در آن قرار گیرند. برای رسم نمودار Histogram به منظور انجام این مقایسه روش رسم کاملاً مشابه روش توضیح داده شده در بالا میباشد با این تفاوت که دیگر احتیاجی به انتخاب Horizontal Axis Data نیست، اگر هم انتخاب شده باشد اشکالی در رسم پیش نمیاید.

برای مطالعه و مقایسه داده ها به صورت توضیح داده شده در بالا، پس از تکمیل رسم نمودار Histogram در اکسل بر روی محور افقی کلیک راست نمایید و گزینه Format Axis را انتخاب کنید، به این صورت پنجره Format Axis بر روی صفحه اکسل باز میشود که تنظیمات محور افقی به شکل توضیح داده شده در زیر قابل انجام است.

Histogram Chart Axis Option

پنجراه Format Axis در قسمت Axis Option گزینه های زیر را برای انتخاب در اختیار کاربران قرار میدهد:

  • By Category: برای نمایش نمودار Histogram به صورتی که در محور افقی نام شهرها، نام افراد، نام کالاها و سالها (مانند موارد توضیح داده شده در بالا باشد) این گزینه را باید انتخاب نمایید.
  • Automatic: اکسل بر اساس داده های انتخاب شده نمودار مورد نظر خود را ترسیم مینماید، ان گزینه مطلوب ما نیست.
  • Bin Width: در صورت انتخاب گزینه Bin Width، در کادر روبروی این گزینه یک عدد باید نوشته شود، اکسل به صورت اتوماتیک داده های عددی انتخاب شده را در Bin هایی به اندازه تعیین شده توسط کاربرد تقسیم کرده و ستون های نمودار Histogram را ترسیم مینماید، ارتفاع هر ستون تعداد داده های عددی قرار گرفته در آن Bin میباشد.
  • Number of Bins: در صورت انتخاب گزینه Number of Bins، در کادر روبروی این گزینه یک عدد باید نوشته شود، اکسل به صورت اتوماتیک داده های عددی انتخاب شده را به تعداد Bin های مشخص شده دسته بندی میکند، ارتفاع هر ستون تعداد داده های عددی قرار گرفته در آن Bin میباشد.
  • Overflow & Underflow Bins: اگر داده های عددی پرت دارید، برای قرارگیری آن داده های پرت در یک Bin گزینه های Overflow و Underflow وجود دارد. تیک زدن گزینه Overflow و نوشتن یک عدد مورد نظر در کادر روبروی آن، داده های عددی بزرگتر از مقدار مشخص شده در کادر روبرو را در این Bin قرار میدهد.Underflow Bin دقیقاً معکوس آن عمل میکند و یک Bin برای قراردادن داده های عددی خیلی کوچکتر از بازه مورد مطالعه بر روی نمودار Histogram ترسیم شده ایجاد میکند.

رسم نمودار Pareto در اکسل

نمودار Pareto در اکسل مشابه نمودار Histogram میباشد و  روش رسم آن نیز کاملاً یکسان است، نمودار Pareto در واقع همان نمودار هیستوگرام است که ستون های به ترتیب نزولی (از بلند به کوتاه) مرتب شده باشند. علاوه بر این، نمودار Pareto در اکسل دارای یک منحنی میباشد که محور عمودی سمت راست Chart مربوط به این منحنی میباشد، محور عمودی مربوطه از ۰ تا ۱۰۰ درصد علامت گذاری شده است، مجموع کل ارتفاع ستون ها ۱۰۰ میباشد، و نمودار منحنی از درصد مربوط به بلندترین ستون شروع شده و تا ۱۰۰ ادامه میابد.

برای رسم نمودار Pareto، میتوان دقیقاً روش رسم توضیح داده شده در بالا را کاملاً مشابه نمودار Histogram انجام داد و یا اینکه پس از ترسیم نمودار Histogram بر روی ان کلیک کرد و در تب Design بر روی گزینه Change Chart Type کلیک کرده و از پنجره باز شده نمودار Pareto را انتخاب نمود.

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

Pareto Chart Sample

 

 

 

 

 

 

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

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