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

کلمات کلیدی:

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

منظور از تابع frequency در اکسل چیست؟

تابع Frequency در اکسل، فقط یکی از چندین تابع آمار توصیفی است که به ما در تحلیل داده‌ها کمک می‌کند. بنابراین، شما می‌توانید علاوه بر این تابع، از دیگر توابع فراوانی و تحلیل داده‌ها برای بررسی داده‌ها استفاده کنید. همچنین می‌توانید با توجه به اهداف و نیازهای خود، این تابع را با توابع دیگری مانند تابع MOD در اکسل یا توابع Count a، Countif و غیره ترکیب نمایید.

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

1. data_array
2. bins_array

data_array شامل داده‌هایی است که می‌خواهیم فراوانی آن‌ها را بشماریم. bins_array نیز حدود بازه‌هایی است که می‌خواهیم داده‌ها را در آن‌ها دسته‌بندی کنیم.

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

پیشنهاد مطالعه: نحوه درج امضا در اکسل

این تابع چگونه عمل می‌کند؟

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

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

=FREQUENCY(data_array, bins_array)

• data_array: محدوده‌ای از داده‌های عددی که می‌خواهید تحلیل کنید.
• bins_array: محدوده‌ای از بازه‌ها که داده‌ها در آن دسته‌بندی می‌شوند.

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

مثال: اگر داده‌های نمرات دانش‌آموزان در محدوده A2:A20 قرار داشته باشد و بخواهید آن‌ها را در بازه‌های 0–10، 11–20، 21–30 و … دسته‌بندی کنید، کافی است بازه‌ها را در سلول‌های B2:B6 وارد کرده و فرمول FREQUENCY را در محدوده C2:C7 اجرا کنید.

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

نحوه کار با تابع Frequency در اکسل + مثال

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

پس از آن، در یک سلول خالی، تابع Frequency را به صورت زیر فراخوانی کنید:

=Frequency (Data_Array; Bins_Array)

دقت کنید که در فرمول برای محاسبه فراوانی، باید به جای Data Array، ستون داده‌های خود یا همان نمرات دانش آموزان را انتخاب کنید. به جای Bins_array نیز باید آستانه سن دانش آموزان را انتخاب کنید. پس از تعریف فرمول، روی Enter بزنید.

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

آموزش ایجاد جدول فراوانی در اکسل

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

رسم این جدول کار چندان سختی نیست. برای رسم جدول فراوانی باید از افزونه‌های موجود در اکسل استفاده کنید. افزونه Analysis ToolPak یکی از ابزارهای مفید و محبوب اکسل بوده که برای انجام محاسبات آماری پیچیده طراحی شده است.

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

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

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

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

در این مرحله، یک کادر باز می‌شود که شما باید از گزینه‌های موجود در آن، گزینه Analusis ToolPak را انتخاب کرده و روی OK بزنید. با این کار، این افزونه کاربردی را در نرم افزار Excel خود، فراخوانی خواهید کرد.

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

در پنجره Histogram، تنظیمات را مطابق با تصویر زیر اعمال کنید و سپس دکمه OK را فشار دهید. در این صورت یک جدول فراوانی ساده در یک Sheet جدید، ساخته خواهد شد.

چنانچه در کادر هیستوگرام، تیک گزینه chart Output را در قسمت پایین کادر، فعال کنید، رسم نمودار هیستوگرام نیز در کنار جدول فراوانی برای شما انجام خواهد شد.

همانطور که مشاهده می‌کنید، در جدول فراوانی، علاوه بر FREQUENCY، مقدار Cumulative% یا نمایش ستون فراوانی نسبی تجمعی نیز نشان داده شده است. منظور از فراوانی نسبی تجمعی در اکسل، مقداری است که نشان می‌دهد چه تعداد از داده‌های یک مجموعه، کمتر یا مساوی یک مقدار خاص هستند.

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

درصد فراوانی

منظور از درصد فراوانی در اکسل، نسبت تعداد دفعات تکرار یک مقدار در یک مجموعه داده‌های آماری به کل تعداد داده‌ها است. برای مثال، اگر در یک کلاس ۲۰ دانش‌آموز وجود داشته باشد و ۵ نفر از آن‌ها نمره ۱۸ را کسب کنند، درصد فراوانی نمره ۱۸ برابر با ۵/۲۰ یا ۲۵ درصد خواهد بود. برای محاسبه درصد فراوانی در اکسل، می‌توان از تابع COUNTIF استفاده کرد. این تابع تعداد دفعاتی را شمارش می‌کند که یک شرط در یک محدوده داده‌ها برقرار باشد. سپس، باید تعداد کل داده‌ها را با تابع Count به دست آورده و نتیجه را تقسیم بر هم کنیم. فرمول عمومی برای محاسبه درصد فراوانی در اکسل به شکل زیر است:

=COUNTIF(محدوده، شرط)/COUNT(محدوده)

به عنوان مثال، اگر نمرات دانش‌آموزان در ستون A قرار داشته باشند، می‌توان با فرمول زیر درصد فراوانی نمره ۱۸ را محاسبه کرد:

=COUNTIF(A:A,18)/COUNT(A:A)

فراوانی تجمعی

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

تفاوت تابع FREQUENCY با تابع COUNTA در اکسل

تفاوت تابع FREQUENCY با تابع COUNTA در اکسل این است که تابع FREQUENCY تعداد داده‌هایی را می‌شمارد که در بازه‌های مشخصی قرار دارند، در حالی که تابع COUNTA تعداد داده‌های غیر خالی را می‌شمارد. برای مثال، اگر یک ستون شامل نمرات دانشجویان باشد، می‌توان با تابع FREQUENCY تعداد دانشجویانی را که نمره‌شان بین ۱۰ تا ۲۰، ۲۰ تا ۳۰ و غیره است را محاسبه کرد. اما با تابع COUNTA فقط می‌توان تعداد دانشجویانی را که نمره دارند را محاسبه کرد.

تفاوت تابع FREQUENCY با تابع COUNTIF در اکسل

تفاوت تابع FREQUENCY با تابع COUNTIF در اکسل این است که تابع COUNTIF به عنوان یک تابع شرطی در اکسل برای شمارش تعداد داده‌هایی استفاده می‌شود که شرط خاصی را برآورده می‌کنند؛ برای مثال، اگر بخواهیم بدانیم که چند نفر از دانشجویان یک کلاس نمره بالای ۱۸ را کسب کرده‌اند، می‌توانیم از تابع COUNTIF استفاده کنیم و شرط را برابر با “>18” قرار دهیم.

اما اگر بخواهیم بدانیم که چند نفر از دانشجویان در بازه‌های نمراتی مثل ۱۰ تا ۱۲، ۱۳ تا ۱۵ و ۱۶ تا ۲۰ قرار دارند، می‌توانیم از تابع FREQUENCY استفاده کنیم و بازه‌های مورد نظر را به عنوان آرگومان وارد کنیم.

استفاده از تابع توزیع فراوانی بدون استفاده از منوی Formula

به جای استفاده از منوی فرمول‌ها، می‌توانید تابع‌های مورد نیاز خود را مستقیماً در نوار Function اکسل تایپ کنید و با فشردن کلیدهای Ctrl+Shift+Enter، فرمول آرایه خود را اجرا کنید. برای مثال، برای محاسبه توزیع فراوانی و درصدهای آن، از توابع زیر استفاده کنید:

{=FREQUENCY(Data_array،Bins_array)}

{=FREQUENCY(Data_array،Bins_array)/COUNT(Data_array)}

توجه داشته باشید که این توابع به عنوان فرمول‌های آرایه در نظر گرفته می‌شوند و بنابراین باید با کلیدهای Ctrl+Shift+Enter وارد شوند. اگر در پیرامون فرمول آکولاد {} دیدید، یعنی فرمول به صورت آرایه وارد شده است.

سناریوی کاربردی

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

• 0 تا 10
• 11 تا 20
• 21 تا 30
• 31 تا 40
• بیشتر از 40

برای این کار مراحل زیر را انجام می‌دهید:

1. در ستون B مقادیر بازه‌ها را وارد می‌کنید: 10، 20، 30، 40
2. در ستون C محدوده خروجی را انتخاب می‌کنید (5 سلول)
3. فرمول زیر را وارد کرده و با Ctrl + Shift + Enter تأیید می‌کنید:

=FREQUENCY(A2:A100, B2:B5)

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

جمع‌بندی

در این مقاله سعی کردیم به بررسی تابع Frequency در اکسل بپردازیم. این تابع به محاسبه توزیع فراوانی داده‌ها در بازه‌های مشخص می‌پردازد. در ادامه، به نحوه رسم جدول فراوانی، تابع توزیع جمعی در اکسل و نمودار Histogram نیز اشاره کردیم. با این حال، توابع آمار توصیفی بسیار زیاد هستند و برای تحلیل دقیق داده‌ها به همه آنها نیاز خواهید داشت. همچنین می‌توانید با ترکیب توابع مختلف مانند تابع choose در اکسل با تابع Frequency، به نتایج و تحلیل دقیق‌تری دست پیدا کنید. بنظر شما با ترکیب این دو ابزار، چه نتایجی بدست خواهد آمد؟ نظر خود را درباره این مقاله با ما به اشتراک بگذارید.

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