جدول فراوانی در اکسل — راهنمای کاربردی

یکی از مفاهیمی که در اولین آموزش‌ها و درس‌های آماری مورد اشاره قرار می‌گیرد، «جدول فراوانی» (Frequency Table) است. آمارشناسان اغلب از جدول فراوانی برای خلاصه کردن داده‌ها و نمایش آن‌ها استفاده می‌کنند. این شیوه نمایش مقادیر و داده‌های جمع‌آوری شده، کمک زیادی به درک رفتار داده‌های یک متغیره یا تک بُعدی می‌کند. به همین علت در این نوشتار از مجله فرادرس می‌خواهیم نحوه ترسیم جدول فراوانی در اکسل را آموزش داده و روش‌ها یا تکنیک‌های مختلف ایجاد چنین جدولی را معرفی کنیم.

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

جدول فراوانی چیست؟

همانطور که اشاره کردیم، جدول فراوانی یک شیوه برای نمایش و خلاصه ‌سازی داده‌ها است. با توجه به تبدیل نمایش داده‌های تک بُعدی به حالت دو بُعدی، مقداری پیچیدگی اطلاعاتی بوجود می‌آید ولی در عوض، درک و شناخت نسبت به آن‌ها افزایش می‌یابد. بعلاوه براساس «بافت‌نگار» یا «نمودار فراوانی» (Histogram) که طبق جدول فراوانی ترسیم می‌شود، سرعت انتقال اطلاعات حاصل از داده‌ها نیز بیشتر شده و بیشترین میزان کسب دانش از آن‌ها (البته بدون انجام هیچ محاسبه‌ای) حاصل می‌شود.

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

کلیک کنید

شاید بتوان هدف از ایجاد جدول فراوانی را توسط فهرست زیر مشخص کرد.

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

نکته: با توجه به یک بُعدی بودن مقادیر، دسته‌بندی و گروه‌بندی مقادیر توسط جدول فراوانی یکی از روش‌های ساده و البته کارا برای «خوشه‌بندی داده‌ها» (Data Clustering) در «یادگیری غیرنظارتی» (Unsupervised Machine Learning) محسوب می‌شود.

در تصویر ۱، نمونه‌ای از یک جدول فراوانی را برای داده‌های جدول ۱ مشاهده می‌کنید. می‌خواهیم در ادامه این متن، چنین جدولی را براساس توابع یا ابزارهای کاربردی اکسل ایجاد کنیم.

جدول ۱

همانطور که در تصویر ۱ مشاهده می‌کنید، یک جدول فراوانی در حالت ساده، دارای دو ستون اصلی است که ستون‌های بعدی براساس آن‌ها بدست می‌آیند. ستون اول که «رده» یا «دسته» (Bin) نامیده می‌شود، برای نمایش هر یک از دسته مقادیر یا نماینده هر رده مورد استفاده قرار می‌گیرد. در ستون دوم نیز «تعداد تکرار» یا «فراوانی» (Frequency) هر مقدار در رده، قرار گرفته که مجموع این ستون با تعداد مقادیر یا مشاهدات باید برابر باشد.

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

• ستون فراوانی نسبی (Relative Frequency): این ستون که به فراوانی درصدی نیز معروف است، براساس تقسیم فراوانی هر رده بر جمع کل فراوانی‌ها حاصل می‌شود. به این ترتیب مشخص است چند درصد از مقادیر در رده مورد نظر قرار دارند. معمولا از نماد $$r_i$$ برای این ستون استفاده می‌کنند. بنابراین اگر $$f_i$$ را فراوانی رده $$i$$ام در نظر بگیریم، فراوانی نسبی به صورت زیر بدست می‌آید.

$$\large r_i = \dfrac{f_i}{\sum f_i }$$

• ستون فراوانی تجمعی (Cumulative Frequency): اگر فراوانی هر رده را با فراوانی رده‌های قبلی جمع کنیم، مشخص می‌شود تا رده جاری، چه تعداد فراوانی وجود دارد. چنین ستونی را گاهی با نام فراوانی جمعی نیز می‌شناسند. این ستون معمولا با نماد $$s_i$$ در جدول فراوانی مشخص می‌شود. در این صورت برای رده $$i$$‌، داریم:

$$\large s_i = \sum_{k=1}^i f_k$$

• ستون فراوانی نسبی تجمعی (Cumulative Relative Frequency): برای بدست آوردن مجموع فراوانی‌های نسبی تا رده مورد نظر، فراوانی نسبی هر رده را با فراوانی نسبی رده‌های قبلی جمع می‌کنیم. برای مثال در جدول بالا، مشخص است که 48٪ مشاهدات کمتر از ۱۵ بوده یا ۱2٪ مقادیر (که از تفاضل ۰٫۸۸ از ۱ بدست می‌آید) بزرگتر از ۲۰ هستند. اگر نماد $$g_i$$‌ را برای این ستون از جدول فراوانی در نظر بگیریم، داریم:

$$\large g_i = \sum_{k=1}^i s_k$$

جدول فراوانی در اکسل

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

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

کلیک کنید

ابتدا به روش ترسیم جدول فراوانی به کمک «جعبه ابزار تحلیل داده‌های اکسل» (Data Analysis) خواهیم پرداخت. سپس از فرمول و توابع اکسل برای ساختن یک جدول فراوانی کمک خواهیم گرفت.

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

در این تصویر، رده اول یا سطر اول جدول فراوانی، شامل مقادیری است که از ۱۰ کوچکتر یا مساوی هستند. همچنین رده دوم نیز مقادیری در بازه $$(10,15]$$ را می‌شمارد. رده سوم هم مقادیر بزرگتر از ۱۵ و کوچکتر یا مساوی با ۲۰ را شمارش کرده و رده آخر نیز به مقادیر بزرگتر از ۲۰ اختصاص دارد. همانطور که دیده می‌شود همه رده‌ها، دارای کران‌هایی به صورت زیر هستند.

$$\large (a,b]$$

که در آن $$a$$ مقداری است که در ستون Bin در ردیف قبل از ردیف جاری نوشته شده و $$b$$ نیز مقداری است که در رده جاری در ستون Bin دیده می‌شود. واضح است که در رده اول مقدار $$a$$ منفی بی‌نهایت و مقدار $$b$$ نیز برابر با ۱۰ در نظر گرفته شده، همچنین برای رده دوم، مقدار $$a$$ برابر با ۱۰ و مقدار $$b$$ همان ۱۵ خواهد بود. از طرفی در رده آخر، مقادیر بزرگتر از کران بالای رده قبلی شمارش می‌شود. پس مقدار $$a$$ برای رده آخر، ۲۰ و مقدار $$b$$، مثبت بی‌نهایت است.

ایجاد جدول فراوانی در اکسل با جعبه ابزار تحلیل داده

ابزارهای زیادی برای اکسل به صورت «افزونه» (Add-ins) وجود دارد که یکی از آن‌ها افزونه Analysis ToolPak است که طرفداران زیادی در بین کاربران محاسبات آماری دارد. این افزونه قادر به انجام بسیاری از تحلیل‌های آماری است که به واسطه توابع اکسل به راحتی امکان‌پذیر نخواهد بود. اغلب این افزونه‌ها به کمک ماکرو نویسی (Macro) و زبان برنامه‌نویسی VBA تولید و در اختیار کاربران قرار می‌گیرند. ابتدا نحوه نصب این افزونه را بازگو کرده، سپس به کمک گزینه Histogram در آن، جدول فراوانی را تشکیل می‌دهیم.

نصب افزونه تحلیل داده (Data Analysis)

از فهرست File در اکسل، گزینه Options را کلیک کنید. از پنجره Excel Options که در تصویر ۳ دیده می‌شود، گزینه Add-ins را از کادر سمت چپ انتخاب کرده و در بخش Manage که در پایین صفحه قرار گرفته، گزینه Excel Add-ins را انتخاب و دکمه ...Go را بزنید.

با فعال کردن گزینه Analysis ToolPak به مانند تصویر ۴، امکانات محاسبات آماری را به اکسل اضافه کرده و ایجاد جدول فراوانی به کمک دستورات آن، میسر می‌شود. کافی است در این پنجره، دکمه OK را کلیک کنید تا به فهرست دستورات برگه Data گزینه Data Analysis اضافه شود.

حال که افزونه بسته تحلیل داده‌ها را به اکسل اضافه کردیم، برگه Data را فعال کرده و دستور Data Analysis را اجرا می‌کنیم. پنجره‌ای به مانند تصویر 5 ظاهر شده که با انتخاب گزینه Histogram، امکان تشکیل جدول فراوانی را فراهم می‌سازد. در پنجره Histogram، تنظیمات را مطابق با تصویر 6 انجام داده و دکمه OK را بزنید. به این ترتیب به سادگی یک جدول فراوانی ساده در محل انتخابی از کاربرگ، ایجاد خواهد شد.

جدول زیر به معرفی گزینه‌های مربوط به دستور Histogram پرداخته است.

نکته: با توجه به انتخاب گزینه محاسبه ستون «فراوانی نسبی تجمعی» (Cumulative Percentage) کار محاسبه ستون‌های دیگر ساده‌تر خواهد شد.

نکته: اگر در قسمت Bin Range، مقدار یا ناحیه‌ای را مشخص نکنید، به طور خودکار نقاط برش و تقسیم‌بندی مقادیر به دسته یا رده‌ها برای جدول فراوانی در اکسل انجام خواهد شد.

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

ستون فراوانی نسبی: برای محاسبه این ستون، ابتدا یک ستون جدید در بین دو ستون «فراوانی» (Frequency) و ستون «فراوانی نسبی تجمعی» (% Cumulative) ایجاد کنید. حال به کمک فرمول زیر در ستون جدید، مقدارهای فراوانی نسبی را محاسبه کنید.

$$ \large C2:\;\; = B2/ (sum($B$2 : $B$5) $$

ستون فراوانی تجمعی: یک ستون به قبل از ستون فراوانی نسبی تجمعی اضافه کرده، سپس کافی است طبق تغییر کوچکی که در فرمول گفته شده در قسمت ابتدایی متن، ایجاد می‌کنیم، این ستون را محاسبه نماییم.

$$ \large D2:\;\; = E2 \times (sum ($B$2 : $B$5) )   $$

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

$$\large B6:\;\; = sum(B2: B5) , \;\;\; C6:\;\; = sum(C2 : C6)$$

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

نکته: همانطور که در تصویر 7 مشاهده می‌کنید، عبارت More در رده آخر، به شکل خودکار در سلول A5 قرار گرفته است. همچنین از آنجایی که محل قرارگیری جدول فراوانی را یک کاربرگ جدید معرفی کرده‌ایم، اولین سلول کاربرگ جدید یعنی A1، اولین سلول و محل آغازین برای جدول فراوانی خواهد بود.

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

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

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

کلیک کنید

به این ترتیب در ناحیه A5 تا A7، مقادیر مربوط به دسته‌ها (Bin) را مطابق با تصویر 7 وارد می‌کنیم. البته ورود مقدار More در انتها، لازم نیست. حال ناحیه B5 تا B8 را انتخاب کرده و تابع Frequency را فراخوانی می‌کنیم. به عنوان پارامتر اول این تابع، ناحیه اطلاعاتی یعنی ناحیه A1 تا J2‌ به همراه A3 تا E3 است.

نکته: ناحیه مربوط به پارامتر اول، یک ناحیه مربع مستطیلی یا پیوسته نیست. برای انتخاب آن می‌توان ابتدا قسمت اول (A1 تا J2) را با «تکنیک کشیدن و رها کردن» (Drag and Drop) انتخاب کرده، سپس با نگه داشتن کلید Ctrl، روی ناحیه A3 تا E3، نشانگر را بکشید. به این ترتیب یک ناحیه غیر پیوسته، انتخاب می‌شود.

پارامتر دوم این تابع نیز اختصاص به ناحیه مربوط به دسته‌ها (Bin) دارد. مشخص است که سلول‌های A5 تا A8 را به این کار اختصاص داده‌ایم. این ناحیه را به عنوان پارامتر دوم تعیین کنید.

برای ثبت تابع نیز به روش برداری عمل کرده و کلیدهای Ctrl+Shift+Enter را هم‌زمان فشار می‌دهیم. تابع به صورت برداری ثبت شده و محاسبات مربوط به ستون فراوانی کامل می‌شوند. برای بدست آوردن ستون‌های دیگر در جدول فراوانی، به روشی‌ که در تکنیک قبلی گفته شد، مراجعه کنید، زیرا مبنای محاسبات برای همه آن‌ها، یکسان است. به تصویر ۸ توجه کنید.

نکته: به طور خودکار تابع Frequency، برای رده آخر، فراوانی برای مقادیر بزرگتر از ۲۰ را شمارش می‌کند. می‌توانید عبارت More را در سلول A9 به طور دستی وارد کنید.

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

تا اینجا با دو شیوه، یکی به کمک ابزار و دیگری به کمک فرمول نویسی، جدول فراوانی برای داده‌ها را مشخص کردیم. ولی تکنیک‌های دیگری نیز وجود دارند که برای تولید جدول فراوانی به کار می‌روند. البته پیچیده‌تر بودن مراحل انجام کار و احتمالا تغییر در ساختار اطلاعات اولیه برای این تکنیک‌ها، ممکن است کار را برای کاربران مبتدی سخت کرده ولی با توجه به قابلیت‌های بیشتر در این زمینه، برای کاربران حرفه‌ای جذابیت بیشتری داشته باشند. برای مثال می‌توان با استفاده از تابع «شمارش شرطی» (Countifs) یا «جمع‌بندی شرطی» (SumIFs) و همچنین «جدول محوری» (Pivot Table) شبیه جدول فراوانی تولید شده در تصویر ۱ را ایجاد کرد. البته نتیجه خروجی همه این روش‌ها، یکسان خواهد بود ولی در نوع استفاده از توابع یا ابزارهای اکسل، ممکن است تفاوت‌های مهم و اساسی وجود داشته باشد.

خلاصه و جمع‌بندی

همانطور که خواندید، به کمک توابع و ابزارهای کاربردی اکسل می‌توان به سادگی و سرعت، یک جدول فراوانی برای داده‌های یک متغیره ایجاد کرد. البته برای مشاهداتی که بیش از یک بُعد داشته باشند، کار کمی پیچیده‌تر شده و شاید درک جدول فراوانی دو یا چند بعُدی ترسیم شده در اکسل توسط ابزار «جدول محوری» (Pivot Table)، کمی مشکل به نظر برسد. ولی نمایش داده‌ها مطابق با چنین جدول‌هایی، بیش از داده‌های خام، اطلاعات به کاربران داده و برای محاسبه شاخص‌های آماری نیز بسیار سریع‌تر عمل می‌کنند. طبق این نوشتار نشان دادیم که اکسل، به عنوان ابزار همه فن حریف در حوزه تحلیل داده‌ها، از عهده ترسیم و ایجاد جدول‌های فراوانی یک و چند متغیره به خوبی برآمده و کاربران را در این جهت، یاری می‌رساند. در نوشتارهای دیگر مجله فرادرس، به ترسیم جدول‌های فراوانی چند بُعدی (جدول محوری چند طرفه) خواهیم پرداخت.