محاسبه فراوانی در اکسل با تابع FREQUENCY (+ دانلود فیلم آموزش گام به گام)

تابع FREQUENCY در اکسل یا همان تابع فرکانس در اکسل امکان شمارش تعداد دفعاتی که مقادیر در یک بازه خاص جای گرفته‌اند را فراهم ساخته است. برای نمونه اگر در یک اسپردشیت، سن یک دسته از افراد را ذخیره کرده باشید، با بهره‌گیری از این تابع می‌توانید دریابید که چه تعداد از افراد در بازه‌های سنی مختلف جای می‌گیرند. در ادامه به روش محاسبه «توزیع‌های فراوانی» و با کمی تغییر «درصدهای فراوانی» می‌پردازیم.

تابع فرکانس اکسل چه کاری انجام می‌دهد؟

تابع آرایه FREQUENCY در اکسل امکان محاسبه توزیع فراوانی یک مجموعه داده را به ما می‌دهد. اگر مجموعه داده عددی (که در عمل سلول‌هایی هستند که به عنوان منبع استفاده می‌شوند) و یک فهرست از آستانه‌های دسته (که دسته‌بندی‌هایی هستند که داده‌ها را در آن مرتب‌سازی می‌کنیم) را در اختیار دارید، برای استفاده از این تابع، می‌توانید دکمه‌های میانبر Ctrl+Shift+Enter را بزنید.

چگونه از تابع FREQUENCY در اکسل استفاده کنیم؟

در این بخش یک مثال ساده ارائه می‌کنیم. فرض کنید معلمی هستید و یک اسپردشیت تهیه کرده‌اید که نمرات عددی آزمون دانش‌آموزان در آن ذخیره شده است. با استفاده از تابع FREQUENCY می‌توانید دریابید که چه تعداد از دانش آموزان نمره حرفی D ،C ،B ،A یا F دریافت کرده‌اند. نمرات عددی تست مجموعه داده و رتبه‌های حرفی همان «آستانه دسته» (bin threshold) هستند.

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

کلیک کنید

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

اگر نمرات را تا یک رقم اعشار گرد کنیم، بازه‌های مورد نظر به صورت زیر هستند:

F <= 59.9 < D <= 69.9 < C <= 79.9 < B <= 89.9 < A

بدین ترتیب اکسل نمره 79.9 را متعلق به بازه C می‌داند در حالی که نمره 98.2 در بازه A جای می‌گیرد. بنابراین اکسل همه مقادیر لیست نمرات را بررسی کرده و تعداد کل نمراتی که در یک بازه قرار می‌گیرند را می‌شمارد و یک آرایه از پنج سلول بازگشت می‌دهد که تعداد کل نمرات هر بازه را به نمایش می‌گذارد.

تابع FREQUENCY به دو آرایه برای ورودی نیاز دارد که یکی Data_array و دیگری Bins_array است. این آرایه‌ها در واقع لیست‌هایی از مقادیر هستند. Data_array باید شامل مقادیری مانند نمرات عددی دانش آموزان باشد تا اکسل بتواند آن‌ها را یک سری از آستانه‌های تعریف در Bins_array که در این مثال رتبه‌های حرفی آزمون هستند، مقایسه کند.

بررسی یک مثال

در مثالی که بررسی می‌کنیم، به بررسی توزیع فراوانی و درصد فراوانی یک مجموعه 18 عددی بین 0 و 10 می‌پردازیم. این صرفاً یک تمرین نمونه است که قصد داریم در آن شمار آن اعدادی را که بین یک و دو، دو و سه و همین طور تا آخر جای می‌گیرند تعیین کنیم.

فیلم آموزش اکسل – فیلتر و کاربرد آن در ساخت گزارش - مقدماتی و پیشرفته در فرادرس

کلیک کنید

در اسپردشیت نمونه ما دو ستون به نام‌های Data_array و Bins_array وجود دارند:

ستون Data_array شامل اعداد و ستون Bins_array شامل آستانه‌های دسته‌هایی است که استفاده خواهیم کرد. دقت داشته باشید که یک سلول را در ابتدای ستون Bins_array خالی گذاشته‌ایم تا تعداد مقادیر موجود در نتیجه آرایه را بشماریم و همواره شامل یک مقدار بیش از ستون Bins_array خواهد بود.

همچنین قصد داریم ستون سومی به نام Results نیز ایجاد کنیم که نتایج در آن ذخیره خواهند شد.

ابتدا سلول‌هایی را که قصد داریم نتایج در آن جای بگیرند انتخاب می‌کنیم. اینک به منوی Formulas رفته و روی دکمه More Functions کلیک می‌کنیم. در منوی بازشدنی مربوطه، به زیرمنوی Statistical رفته و پس از کمی اسکرول به سمت پایین روی تابع FREQUENCY کلیک می‌کنیم.

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

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

برای ویرایش فرمول یک آرایه یا حذف آن، باید ابتدا «همه» سلول‌های آرایه را انتخاب کنید. سپس روی کادر Bins_array کلیک کنید و در ادامه سلول‌های پر شده در ستون Bins_array را انتخاب کنید.

روی دکمه OK کلیک کنید.

اینک می‌بینید که تنها سلول نخست ستون Results مقدار دارد و بقیه خالی هستند.

برای مشاهده مقادیر دیگر باید روی نوار Formula کلیک کرده و سپس دکمه‌های Ctrl+Shift+Enter را بزنید.

ستون Results اینک مقادیر مفقود قبلی را نمایش می‌دهد.

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

محاسبه درصدهای فراوانی

تا به اینجا همه چیز به خوبی پیش رفته است؛ اما اگر بخواهیم به جای تعداد نتایج، درصدهای آن‌ها را ببینیم و بدانیم که برای مثال چه درصدی از اعداد بین یک تا دو قرار می‌گیرند چه باید بکنیم؟

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

کلیک کنید

برای محاسبه درصدهای فراوانی هر دسته می‌توانیم فرمول آرایه را با استفاده از نوار فرمول اکسل اصلاح کنیم. ابتدا همه سلول‌های موجود در ستون RESULTS را هایلایت کرده و سپس عبارت زیر را به انتهای فرمول موجود در نوار FUNCTION اضافه کنید:

/COUNT(B3:B20)

فرمول نهایی به صورت زیر خواهد بود:

=FREQUENCY(B3:B20،C3:C20)/COUNT(B3:B20)

اینک دوباره کلیدهای Ctrl+Shift+Enter را بزنید.

فرمول جدید هر عنصر آرایه نتایج را بر تعداد مقادیر ستون Data-array تقسیم می‌کند.