آموزش جامع فرمول FREQUENCY در گوگل شیت

• چگونه می‌توان فراوانی وقوع مقادیر عددی را در بازه‌های مشخص در گوگل شیت شمارش کرد؟
• تفاوت فرمول FREQUENCY با توابع شمارشی دیگر مانند COUNTIF یا COUNTIFS در چیست و چه مزایایی دارد؟
• آرگومان‌های اصلی فرمول FREQUENCY کدامند و چگونه باید آن‌ها را تنظیم کرد؟
• چه نکات مهمی را باید هنگام استفاده از فرمول FREQUENCY در نظر گرفت تا از بروز خطا جلوگیری شود؟
• آیا فرمول FREQUENCY تنها برای داده‌های عددی کاربرد دارد یا می‌توان از آن در سناریوهای پیچیده‌تر نیز استفاده کرد؟

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

آشنایی با فرمول FREQUENCY در گوگل شیت: تعریف و کاربردها

فرمول FREQUENCY در گوگل شیت ابزاری قدرتمند برای تحلیل توزیع داده‌های عددی است. این فرمول به شما کمک می‌کند تا تعداد دفعاتی که مقادیر موجود در یک مجموعه داده (Data Set) در بازه‌های (Bins) مشخصی قرار می‌گیرند را شمارش کنید. تصور کنید یک لیست بلند از نمرات دانش‌آموزان، داده‌های فروش ماهانه، یا نتایج یک نظرسنجی دارید. اگر بخواهید بدانید چند نمره بین ۱۰ تا ۱۲، چند نمره بین ۱۳ تا ۱۵ و الی آخر وجود دارد، یا چند محصول در بازه قیمتی خاصی فروخته شده‌اند، فرمول FREQUENCY بهترین گزینه است.

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

نحوه نگارش و آرگومان‌های فرمول FREQUENCY

برای استفاده از فرمول FREQUENCY در گوگل شیت، باید ساختار آن را به درستی بدانید. فرمول FREQUENCY به دو آرگومان اصلی نیاز دارد:

=FREQUENCY(data, classes)

آرگومان اول: data (داده‌ها)

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

آرگومان دوم: classes (دسته‌ها یا بازه‌ها)

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

نکته مهم: آرگومان classes، حد بالایی هر بازه را مشخص می‌کند. به عنوان مثال، اگر classes شامل اعداد ۱۰، ۲۰، ۳۰ باشد:

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

فرمول آرایه‌ای (Array Formula) و خروجی FREQUENCY

یکی از ویژگی‌های مهم فرمول FREQUENCY در گوگل شیت این است که یک فرمول آرایه‌ای (Array Formula) است. این بدان معناست که به جای تولید یک نتیجه در یک سلول، یک آرایه از نتایج را تولید می‌کند که در چندین سلول سرریز (Spill) می‌کند.

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

مثال‌های کاربردی از فرمول FREQUENCY

برای درک بهتر کاربرد فرمول FREQUENCY در گوگل شیت، به چند مثال عملی می‌پردازیم.

مثال ۱: شمارش نمرات دانش‌آموزان در بازه‌های مشخص

فرض کنید نمرات تعدادی دانش‌آموز را در ستون A از A2 تا A11 دارید. می‌خواهید بدانید چند دانش‌آموز نمره بین ۰-۱۰، ۱۱-۱۵، ۱۶-۲۰ گرفته‌اند.

داده‌ها (Data):

نمره
۱۴
۹
۱۸
۱۱
۶
۲۰
۱۳
۱۵
۷
۱۲

بازه‌ها (Classes) در ستون B:

حد بالایی
۱۰
۱۵
۲۰

در یک سلول خالی (مثلاً C2) فرمول زیر را وارد کنید:

=FREQUENCY(A2:A11, B2:B4)

خروجی:

فراوانی
۳
۵
۲
۰

تفسیر خروجی:

• ۳: سه نمره کوچکتر یا مساوی ۱۰ (۹، ۶، ۷)
• ۵: پنج نمره بزرگتر از ۱۰ و کوچکتر یا مساوی ۱۵ (۱۴، ۱۱، ۱۳، ۱۵، ۱۲)
• ۲: دو نمره بزرگتر از ۱۵ و کوچکتر یا مساوی ۲۰ (۱۸، ۲۰)
• ۰: هیچ نمره‌ای بزرگتر از ۲۰ وجود ندارد (این سلول اضافی است).

مثال ۲: تحلیل داده‌های فروش بر اساس قیمت

فرض کنید لیست قیمت محصولات فروخته شده را در ستون D از D2 تا D15 دارید و می‌خواهید آن‌ها را در بازه‌های قیمتی ۰-۵۰، ۵۱-۱۰۰، ۱۰۱-۱۵۰ و بیش از ۱۵۰ گروه‌بندی کنید.

داده‌ها (Data):

قیمت
۴۵
۱۲۰
۷۸
۲۰۰
۳۰
۹۵
۱۶۰
۵۰
۱۱۰
۸۵
۲۵
۱۳۵
۷۰
۱۸۰

بازه‌ها (Classes) در ستون E:

حد بالایی
۵۰
۱۰۰
۱۵۰

در یک سلول خالی (مثلاً F2) فرمول زیر را وارد کنید:

=FREQUENCY(D2:D15, E2:E4)

خروجی:

فراوانی
۳
۵
۳
۳

تفسیر خروجی:

• ۳: سه محصول با قیمت کوچکتر یا مساوی ۵۰.
• ۵: پنج محصول با قیمت بین ۵۱ تا ۱۰۰.
• ۳: سه محصول با قیمت بین ۱۰۱ تا ۱۵۰.
• ۳: سه محصول با قیمت بزرگتر از ۱۵۰.

نکات مهم و اشتباهات رایج در استفاده از FREQUENCY

برای استفاده موثر از فرمول FREQUENCY در گوگل شیت، به نکات زیر توجه کنید:

1. مرتب بودن آرگومان Classes: این یکی از مهمترین نکات است. مقادیر در دامنه classes باید حتماً به ترتیب صعودی (از کوچک به بزرگ) مرتب شده باشند. اگر این شرط رعایت نشود، نتایج اشتباهی خواهید گرفت.
2. فرمول آرایه‌ای: همیشه به یاد داشته باشید که FREQUENCY یک فرمول آرایه‌ای است. خروجی آن به طور خودکار در سلول‌های پایین گسترش می‌یابد. بنابراین، مطمئن شوید که سلول‌های زیر سلولی که فرمول را وارد می‌کنید، خالی هستند تا فرمول بتواند نتایج خود را نمایش دهد.
3. دامنه خروجی: تعداد سلول‌های خروجی همیشه یک واحد بیشتر از تعداد سلول‌های آرگومان classes خواهد بود. این سلول اضافی، فراوانی مقادیری را نشان می‌دهد که بزرگتر از آخرین حد بالایی در classes هستند.
4. نادیده گرفتن مقادیر غیرعددی: فرمول FREQUENCY در گوگل شیت، فقط روی مقادیر عددی کار می‌کند. هرگونه سلول حاوی متن، خطا (مثل #DIV/0!)، یا مقادیر منطقی (TRUE/FALSE) در آرگومان data نادیده گرفته می‌شود.
5. حدود بازه‌ها: هر مقدار در آرگومان classes به عنوان حد بالایی (Upper Bound) یک بازه در نظر گرفته می‌شود. به عبارت دیگر، هر بازه شامل اعدادی می‌شود که بزرگتر از حد بالایی بازه قبلی و کوچکتر یا مساوی حد بالایی بازه فعلی هستند.

تفاوت با سایر توابع شمارشی (COUNTIF, COUNTIFS)

ممکن است این سوال پیش بیاید که تفاوت فرمول FREQUENCY در گوگل شیت با توابعی مانند COUNTIF یا COUNTIFS چیست. هرچند هر سه برای شمارش استفاده می‌شوند، اما کاربردهای متفاوتی دارند:

• COUNTIF: این تابع برای شمارش سلول‌هایی استفاده می‌شود که یک شرط خاص را برآورده می‌کنند. مثلاً: =COUNTIF(A:A, ">10") نمره‌های بالای ۱۰ را می‌شمارد. این تابع برای یک شرط واحد و مشخص مناسب است.
• COUNTIFS: این تابع برای شمارش سلول‌هایی استفاده می‌شود که چندین شرط را به طور همزمان برآورده می‌کنند. مثلاً: =COUNTIFS(A:A, ">10", A:A, "<=20") نمره‌های بین ۱۰ تا ۲۰ را می‌شمارد. این تابع برای چندین شرط مشخص و با مرزهای دقیق کارآمد است.
• FREQUENCY: تفاوت اصلی در این است که FREQUENCY به طور خاص برای گروه‌بندی و شمارش فراوانی در بازه‌های عددی طراحی شده است. به جای تعریف تک تک شرط‌ها، شما یک لیست از "حدود بالایی" بازه‌ها را به آن می‌دهید و فرمول به طور خودکار همه بازه‌ها را پردازش می‌کند. این باعث می‌شود که FREQUENCY برای تحلیل توزیع داده‌ها و ساخت هیستوگرام بسیار کارآمدتر و ساده‌تر باشد، به خصوص زمانی که تعداد بازه‌ها زیاد باشد. برای انجام همین کار با COUNTIFS، باید برای هر بازه یک فرمول جداگانه بنویسید که کاری زمان‌بر و مستعد خطا است.

نکات پیشرفته و ترکیب با توابع دیگر

فرمول FREQUENCY در گوگل شیت را می‌توان با توابع دیگر ترکیب کرد تا قابلیت‌های تحلیلی بیشتری را ارائه دهد:

۱. استفاده از FREQUENCY با ARRAYFORMULA برای ایجاد بازه‌های دینامیک

اگرچه FREQUENCY خودش یک فرمول آرایه‌ای است، اما ترکیب آن با ARRAYFORMULA می‌تواند در برخی سناریوها مفید باشد، به خصوص زمانی که می‌خواهید آرگومان classes را به صورت دینامیک تولید کنید. برای مثال، اگر بخواهید بازه‌هایی با فواصل ثابت (مثلاً هر ۱۰ واحد) از حداقل تا حداکثر مقدار داده‌هایتان ایجاد کنید، می‌توانید از ترکیب SEQUENCE و MIN/MAX برای ساخت classes استفاده کنید.

۲. نمایش برچسب‌های بازه

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

۳. ایجاد هیستوگرام (Histogram)

پس از استفاده از فرمول FREQUENCY در گوگل شیت، داده‌های فراوانی به دست آمده را می‌توان به راحتی در یک نمودار هیستوگرام (نمودار ستونی) نمایش داد تا توزیع داده‌ها به صورت بصری و گویا نشان داده شود. برای این کار، کافیست ستون برچسب‌های بازه و ستون نتایج FREQUENCY را انتخاب کرده و از منوی "درج" (Insert) گزینه "نمودار" (Chart) و سپس "نمودار ستونی" (Column Chart) را انتخاب کنید.

۴. تحلیل توزیع درصد

می‌توانید نتایج FREQUENCY را بر کل تعداد داده‌ها تقسیم کنید تا فراوانی نسبی (درصد) هر بازه را به دست آورید. این کار به شما کمک می‌کند تا سهم هر بازه را از کل مجموعه داده‌ها درک کنید.

=FREQUENCY(data, classes) / COUNT(data)

و سپس این سلول‌ها را با فرمت درصد نمایش دهید.

نتیجه‌گیری

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