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

نمودار هیستوگرام یک ابزار رایج برای تحلیل داده در دنیای تجارت به حساب می‌آید. هیستوگرام در واقع یک نمودار ستونی است که تداوم وقوع یک متغیر را در بازه‌ای مشخص به نمایش می‌گذارد. یک مثال ساده از چنین نموداری، هیستوگرامی است که توزیع نمرات به دست آمده در یک آزمون را نشان می‌دهد. می‌توانید خیلی ساده به رسم نمودار هیستوگرام در اکسل مشغول شوید و به عنوان مثال ببینید چند دانشجو نمره‌ای کمتر از ۳۵ داشته‌اند، چند دانشجو به نمره‌ای بین ۳۵ الی ۵۰ دست یافته‌اند، چند دانشجو نمره‌ای بین ۵۰ تا ۶۰ دریافت کرده‌اند و به همین ترتیب تا آخر.

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

• اگر از اکسل ۲۰۱۶ استفاده می‌کنید، یک گزینه داخلی برای رسم این نمودار وجود دارد که کار را آسان می‌کند.
• اگر از اکسل ۲۰۱۳، اکسل ۲۰۱۰ یا نسخه‌های قدیمی‌تر استفاده می‌کنید نیز می‌توانید به سراغ ابزار Data Analysis Toolpack بروید یا از تابع FREQUENCY استفاده کنید.

در ادامه این مقاله هر سه روش را به صورت جداگانه توضیح داده‌ایم. به مطالعه ادامه دهید. ناگفته نماند که اگر تمایل دارید نرم افزار اکسل را به صورت اصولی یاد بگیرید می‌توانید از مطلب آموزش اکسل مجله فرادرس یا فیلم های آموزش اکسل فرادرس بهره بگیرید. در عین حال برای آشنایی با نحوه رسم تمام نمودارهای اکسل می‌توانید سری به مقاله رسم نمودار در اکسل بزنید و اگر می‌خواهید اصول فرمول‌نویسی در اکسل را یاد بگیرید کافی است به مطلبی که در همین رابطه در مجله فرادرس تهیه شده است رجوع کنید.

رسم نمودار هیستوگرام در اکسل ۲۰۱۶

اکسل ۲۰۱۶ از افزونه‌ای جدید در بخش «نمودارها» (Charts) برخوردار شد و امکان رسم نمودارهای هیستوگرام را به صورت بومی فراهم کرد. در صورتی که نرم‌افزار اکسل ۲۰۱۳ یا نسخه‌های قدیمی‌تر روی سیستم شما نصب است، به سراغ بخش‌های بعدی این مقاله بروید و در غیر این صورت، همین بخش را مطالعه کنید.

فرض می‌کنیم مانند آنچه در پایین قابل مشاهده است، یک مجموعه داده در اختیار دارید. این مجموعه داده، نمرات ۴۰ دانشجو در آزمونی به‌خصوص را در بر می‌گیرد و بالاترین نمره نیز ۱۰۰ است.

رسم نمودار هیستوگرام در اکسل ۲۰۱۶ بدین شکل خواهد بود:

۱. تمام مجموعه داده خود را انتخاب کنید.

۲. روی زبانه Insert کلیک کنید.

۳. در گروه Charts، روی گزینه «Insert Static Chart» کلیک کنید.

۴. در گروه Histogram، روی نماد نمودار Histogram کلیک کنید.

با پشت سر گذاشتن گام‌های بالا، یک نمودار هیستوگرام بسته به مجموعه داده شما ترسیم می‌شود.

حالا می‌توانید با راست کلیک روی محور عمودی و انتخاب گزینه Format Axis، به شخصی‌سازی نمودار خود مشغول شوید.

با این کار کادری به‌خصوص در سمت راست باز می‌شود که تمام گزینه‌های مربوط به محور نمودار را در بر می‌گیرد.

• مطالب پیشنهادی برای مطالعه:
  • رسم نمودار خطی در اکسل
  • رسم نمودار منحنی در اکسل
  • رسم نمودار راداری در اکسل
  • رسم نمودار میله ای در اکسل
  • رسم نمودار آبشاری در اکسل

برخی از کارهایی که می‌توان برای شخصی‌سازی نمودار انجام داد به شرح زیر است:

۱. By Category: این گزینه زمانی استفاده می‌شود که دسته‌بندی‌هایی متنی و تکراری دارید و می‌خواهید از تعداد یا مجموعه دسته‌بندی‌ها باخبر باشید. برای مثال اگر داده‌های مربوط به فروش مواردی مانند پرینتر، لپ‌تاپ، ماوس و اسکنر را در اختیار داشته باشید و بخواهید از مجموع فروش هر مورد باخبر شوید، می‌توانید از گزینه By Category استفاده کنید. این گزینه در مثال ما کاربردی نیست، زیرا تمام دسته‌بندی‌ها متفاوت از یکدیگر و منحصر به فرد ظاهر می‌شوند.

۲. Automatic: این گزینه به صورت خودکار راجع به سطل‌هایی (Bins یا همان ستون‌های موجود در نمودار) که باید در هیستوگرام ساخته شوند تصمیم‌گیری می‌کند. برای مثال در نمودار ما، این گزینه تصمیم گرفته که باید چهار سطل داشته باشیم. با استفاده از گزینه‌های «Bin Width» یا «Number of Bins» که در ادامه به سراغ آن‌ها خواهیم رفت، قادر به ایجاد تغییرات در این ساختار خواهید بود.

۳. Bin Width: این گزینه ابعاد سطل‌های شما را تعیین می‌کند. اگر در اینجا عدد ۲۰ را وارد کنیم، سطل‌هایی مانند ۵۶-۳۶ و ۷۶-۵۶ و ۹۶-۷۶ و ۱۱۶-۹۶ خواهیم داشت.

۴. Number of Bins: در اینجا می‌توانید تعیین کنید که چند سطل می‌خواهید. سپس به صورت خودکار نموداری حاوی همان تعداد سطل تحویل داده می‌شود. برای مثال اگر به سراغ عدد ۷ برویم، نمودار پایین گیرمان می‌آید. در هر لحظه فقط یا می‌توان Bin Width را تعیین کرد یا Number of Bins را و نه هردو.

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

۶. Underflow Bin: این گزینه ساختاری مشابه Overflow Bin دارد، اما به صورت معکوس. بنابراین اگر بخواهیم بدانیم چند دانشجو نمره‌ای پایین‌تر از ۴۰ دریافت کرده‌اند، عدد ۴۰ را به عنوان مقدار وارد می‌کنیم و نمودار پایین به دست می‌آید.

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

کلیک کنید

رسم نمودار هیستوگرام در اکسل با Data Analysis Tool Pack

روشی که در این بخش از مقاله به سراغ آن می‌رویم با تمام نسخه‌های اکسل (از جمله نسخه ۲۰۱۶) سازگاری دارد. با این حال اگر از اکسل ۲۰۱۶ استفاده می‌کنید، پیشنهاد ما این است که به سراغ ابزار داخلی آن برای رسم هیستوگرام بروید.

برای رسم نمودار هیستوگرام با استفاده از ابزار Data Analysis Tool Pack، ابتدا باید افزونه Analysis Toolpak را نصب کرده باشید.

این افزونه به شما اجازه می‌دهد که با وارد کردن داده و همینطور بازه داده (سطل‌ها)، به شکلی سریع هیستوگرام ترسیم کنید.

نصب Data Analysis Tool Pack

برای نصب افزونه Data Analysis Toolpak:

۱. روی زبانه File کلیک و سپس گزینه Options را انتخاب کنید.

۲. در پنجره Excel Options، گزینه Add-ins را انتخاب کنید.

۳. در منوی کشویی Manage، به سراغ گزینه Excel Add-ins رفته و Go را بزنید.

۴. در کادر Add-ins، گزینه Analysis Toolpak را انتخاب و روی OK کلیک کنید.

با این گام‌ها Analysis ToolPak نصب می‌شود و می‌توانید در زبانه Data و گروه Analysis به آن دسترسی پیدا کنید.

ساخت هیستوگرام با Data Analysis Toolpak

زمانی که Analysis Toolpak را فعال کردید، می‌توانید از آن برای رسم نمودار هیستوگرام در اکسل بهره ببرید.

فرض می‌کنیم مثل آنچه در پایین می‌بینید، یک مجموعه داده دارید که نمرات ۴۰ دانشجو (از ۱۰۰) در یک آزمون را نشان می‌دهد.

برای ساخت هیستوگرام با این داده، لازم است تناوب‌های داده‌ای بسازیم تا بسامد داده به دست آید. به این‌ها «سطل» یا «Bin» گفته می‌شود.

با مجموعه داده بالا، سطل‌ها تناوب نمرات خواهند بود.

لازم است این سطل‌ها را به صورت جداگانه در ستونی اضافه که در تصویر پایین آمده تعیین کنید:

حالا که تمام داده‌ها سر جای خود هستند، بیایید ببینیم چطور می‌توان یک هیستوگرام ساخت:

۱. روی زبانه Data کلیک کنید.

۲. در گروه Analysis به سراغ گزینه Data Analysis بروید.

۳. در پنجره Data Analysis، گزینه Histogram را از درون فهرست انتخاب کنید.

۴. OK را بزنید.

۵. در پنجره Histogram:

• بازه ورودی (Input Range) را انتخاب کنید (در مثال ما، تمام امتیازها)
• بازه سطل را انتخاب کنید (سلول‌های D2:D7)
• چک‌باکس Labels را خالی و بدون تیک بگذارید (زمانی باید تیک آن را بزنید که لیبل‌ها یا برچسب‌هایی در مجموعه داده منتخب خود داشته باشید)
• اگر می‌خواهید هیستوگرام را در همان کاربرگ تحویل بگیرید، بازه خروجی (Output Range) را هم تعیین کنید. در عیر این صورت گزینه New Worksheet/Workbook را برگزینید تا نمودار را در یک کاربرگ جداگانه تحویل بگیرید.
• گزینه Chart Output را برگزینید.

۶. روی OK کلیک کنید.

با این گام‌ها، جدول بسامد توزیع و نمودار در همان جایی که تعیین کرده‌اید قرار می‌گیرند.

اکنون باید از چند نکته راجع به نمودارهای هیستوگرام ساخته شده با Analysis Toolpak باخبر باشید:

• نخستین سطل تمام مقادیر زیر آن را در برمی‌گیرد. در این مثال، ۳۵ نشان‌دهنده ۳ مقدار است و این یعنی سه دانشجو نمره‌ای پایین‌تر از ۳۵ به دست آورده‌اند.
• آخرین سطل مشخص شده، ۹۰ است، اما اکسل به صورت خودکار یک سطل دیگر نیز اضافه می‌کند. این سطل حاوی هرگونه داده‌ای خواهد بود که بعد از آخرین سطل مشخص شده از راه برسد. در مثال ما این بدان معناست که ۲ دانشجو نمره‌ای بالاتر از ۹۰ به دست آورده‌اند.
  • توجه داشته‌ باشید که حتی اگر سطل آخر را با رقم ۱۰۰ وارد کنیم نیز همچنان یک سطل اضافه تحویل خواهیم گرفت.
• نمودار پیش‌فرض همواره از بهترین قالب‌بندی ممکن برخوردار نیست. اگر با هرگونه مشکلی روبه‌رو شدید، می‌توانید قالب‌بندی (Formatting) را مثل هر نمودار دیگری دستخوش تغییر کنید.
• بعد از ساخت نمودار، نمی‌توان از ترکیب دکمه‌های Ctrl + Z برای بازگشت به گام‌های قبلی استفاده کرد. بنابراین باید جدول و نمودار را به صورت دستی پاک کنید.

در نهایت باید افزود که اگر هیستوگرام را بدون تعیین کردن سطل‌ها (یا به عبارت دیگر، با خالی گذاشتن Bin Range) بسازید، همچنان یک هیستوگرام خواهید داشت. اکسل به صورت خودکار شش سطل با فضای برابر می‌سازد و از داده برای ساخت هیستوگرام بهره می‌برد.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

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

اگر می‌خواهید هیستوگرامی پویا بسازید که بعد از هر تغییراتی در داده به‌روزرسانی می‌شود، لازم است به سراغ فرمول‌های اکسل بروید. در این بخش از مقاله خواهید آموخت که چطور می‌توان با استفاده از تابع FREQUENCY، یک هیستوگرام پویا در اکسل ساخت.

بار دیگر به سراغ نمرات دانشجویان می‌رویم و لازم است تناوب داده (سطل‌) را تعیین کنید تا بسامد به نمایش درآید.

تابعی که بسامد هر تناوب را محاسبه می‌کند بدین شکل است:

1=FREQUENCY(B2:B41,D2:D8

از آن‌جایی که این یک فرمول آرایه‌ای است، لازم است به جای فشردن صرف دکمه Enter، از ترکیب دکمه‌های Control + Shift + Enter استفاده کنید.

گام‌های زیرا را دنبال کنید تا مطمئن شوید به نتیجه درست می‌رسید:

• تمام سلول‌های مجاور سطل‌ها را انتخاب کنید. در این مثال، سلول‌ها E2:E8 هستند.
• دکمه F2 را فشار دهید تا وارد حالت ویرایش برای سلول E2 شوید.
• فرمول بسامد را وارد کنید: FREQUENCY(B2:B41,D2:D8)=
• دکمه‌های Control + Shift + Enter را به صورت همزمان بزنید.

با نتیجه‌ای که به دست می‌آورید، اکنون قادر به ساخت یک هیستوگرام خواهید بود (که چیزی نیست جز یک نمودار ستونی ساده).

دو نکته مهم که هنگام استفاده از تابع FREQUENCY باید مد نظر داشته باشید:

• نتیجه کار یک نمودار آرایه‌ای است و نمی‌توانید هیچ بخشی از یک آرایه را پاک کنید. اگر نیاز به چنین کاری بود، تمام سلول‌های حاوی تابع FREQUENCY را پاک کنید.
• وقتی یک سطل ۳۵ است، تابع FREQUENCY نتیجه‌ای حاوی مقدار ۳۵ را تحویل می‌دهد. بنابراین ۳۵ به معنای امتیازهای ۱ تا ۳۵ است و ۵۰ به معنای امتیازهای بالاتر از ۳۵ و پایین‌تر از ۵۰.

علاوه بر این، بیایید فرض کنیم که می‌خواهید تناوب داده تا عدد ۸۰ ادامه پیدا کند و می‌خواهید تمام نتایج بالاتر از ۸۰ کنار یکدیگر قرار بگیرند. این کار را نیز می‌توان با تابع FREQUENCY انجام داد. در این حالت، یک سلول بیشتر از تعداد سطل‌ها انتخاب کنید. برای مثال اگر ۵ سطل دارید، در آن صورت مطابق تصویر پایین، ۶ سلول را انتخاب کنید.

تابع FREQUENCY به صورت خودکار تمام مقادیر بالاتر از ۸۰ را محاسبه کرده و نتیجه شمارش را تحویل می‌دهد.