آموزش تابع QUARTILE در اکسل

معرفی تابع QUARTILE

تابع QUARTILE در اکسل برای محاسبه «چارک‌ها» (Quartiles) استفاده می‌شود؛ یعنی نقاطی که یک مجموعه داده را به 4 بخش مساوی تقسیم می‌کنند. با چارک‌ها می‌توانید بفهمید 25٪ پایین داده‌ها تا کجاست (چارک اول)، 50٪ یا میانه تا کجاست (چارک دوم)، و 75٪ پایین داده‌ها تا کجاست (چارک سوم). این تابع در تحلیل نمرات، فروش، حقوق، زمان انجام کار، و هر نوع داده عددی بسیار کاربردی است.

مثال ساده فارسی: فرض کنید نمرات یک کلاس در محدوده A2:A11 ثبت شده است و می‌خواهید چارک اول (Q1) را به دست آورید.

=QUARTILE(A2:A11;1)

خروجی این فرمول عددی است که نشان می‌دهد حدوداً 25٪ پایین نمرات تا این مقدار هستند.

کاربردهای اصلی تابع QUARTILE

• تقسیم‌بندی داده‌ها به چهار بخش برای تحلیل توزیع
• شناخت محدوده طبیعی داده‌ها و بررسی پراکندگی (در کنار IQR)
• تحلیل عملکرد: فروش، سود، نمرات، زمان پاسخگویی، حقوق و دستمزد
• پیدا کردن آستانه‌های 25٪، 50٪ و 75٪ برای دسته‌بندی افراد/اقلام
• کمک به شناسایی داده‌های پرت (Outlier) با استفاده از چارک‌ها
• ساخت گزارش‌های مدیریتی و داشبوردهای آماری

ساختار (Syntax)

فرمت انگلیسی:

=QUARTILE(array, quart)

فرمت رایج در اکسل فارسی (جداکننده ;):

=QUARTILE(محدوده_اعداد; شماره_چارک)

آرگومان‌ها

array (محدوده یا آرایه اعداد) / داده‌های عددی

محدوده‌ای از سلول‌ها یا یک آرایه از اعداد که می‌خواهید چارک آن محاسبه شود. مقادیر متنی و سلول‌های خالی معمولاً نادیده گرفته می‌شوند، اما وجود خطاها (مثل #DIV/0!) می‌تواند خروجی را خراب کند.

quart (شماره چارک) / تعیین اینکه کدام چارک محاسبه شود

عدد 0 تا 4 که مشخص می‌کند اکسل کدام نقطه را برگرداند:

• 0: کمترین مقدار (Min)
• 1: چارک اول (Q1 = 25٪)
• 2: چارک دوم (Q2 = 50٪ = Median)
• 3: چارک سوم (Q3 = 75٪)
• 4: بیشترین مقدار (Max)

مثال‌های ساده و پایه

مثال 1: محاسبه چارک اول، دوم و سوم

فرض کنید داده‌ها در A2:A11 هستند:

• چارک اول:

=QUARTILE(A2:A11;1)

• چارک دوم (میانه):

=QUARTILE(A2:A11;2)

• چارک سوم:

=QUARTILE(A2:A11;3)

نتیجه: سه عدد به دست می‌آورید که مرزهای 25٪، 50٪ و 75٪ داده‌ها را نشان می‌دهند و دید خوبی از توزیع نمرات/فروش/… می‌دهند.

مثال 2: حداقل و حداکثر با QUARTILE

به جای MIN و MAX هم می‌توانید از QUARTILE استفاده کنید:

=QUARTILE(A2:A11;0)

=QUARTILE(A2:A11;4)

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

مثال‌های کاربردی و واقعی

مثال 1: برچسب‌گذاری فروشندگان بر اساس چارک‌ها (کم/متوسط/بالا)

فرض کنید فروش هر فروشنده در ستون B (از B2 تا B100) است. می‌خواهید هر فروشنده را بر اساس جایگاه فروشش دسته‌بندی کنید:

• کم (<= Q1)
• متوسط (بین Q1 و Q3)
• بالا (>= Q3)

در سلول C2 (و کپی به پایین):

=IF(B2=QUARTILE($B$2:$B$100;3);"بالا";"متوسط"))

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

مثال 2: محاسبه IQR (دامنه بین چارکی) برای تحلیل پراکندگی

IQR برابر است با Q3 – Q1 و یکی از شاخص‌های مهم پراکندگی داده‌هاست:

=QUARTILE(A2:A11;3)-QUARTILE(A2:A11;1)

هر چه IQR بزرگ‌تر باشد، پراکندگی داده‌ها بیشتر است.

مثال 3: پیدا کردن تعداد فروش‌های «بالای چارک سوم» با COUNTIF

می‌خواهید بدانید چند مقدار فروش بالاتر یا مساوی Q3 هستند:

=COUNTIF(B2:B100;">="&QUARTILE(B2:B100;3))

این فرمول تعداد فروشندگان/روزهایی را می‌دهد که در 25٪ بالای توزیع قرار گرفته‌اند.

مثال 4: ترکیب با XLOOKUP برای پیدا کردن نام فرد نزدیک به چارک سوم

فرض کنید نام‌ها در A2:A100 و فروش در B2:B100 است. برای اینکه نام فردی را که فروشش دقیقاً برابر با Q3 است برگردانید (یا نزدیک‌ترین حالت در صورت نبود مقدار دقیق)، می‌توانید داده‌ها را مرتب کنید و سپس از XLOOKUP استفاده کنید. در ساده‌ترین حالت (وقتی مقدار Q3 دقیقاً وجود دارد):

=XLOOKUP(QUARTILE(B2:B100;3);B2:B100;A2:A100)

اگر مقدار چارک دقیقاً در داده‌ها نباشد، ممکن است خطا بدهد یا نتیجه ندهد؛ در این حالت بهتر است از روش‌های تکمیلی (مثل مرتب‌سازی و جستجوی تقریبی یا استفاده از MATCH با حالت تقریبی) استفاده کنید.

مثال 5: شرط‌گذاری منطقی با AND و OR (کنترل کیفیت)

فرض کنید زمان انجام کارها در D2:D200 است و می‌خواهید بررسی کنید آیا یک زمان خاص «در بازه میانی (بین Q1 و Q3)» قرار دارد یا نه. در E2:

=IF(AND(D2>=QUARTILE($D$2:$D$200;1);D2<=QUARTILE($D$2:$D$200;3));"در بازه نرمال";"نیاز به بررسی")

این کار برای کنترل کیفیت و تشخیص موارد غیرعادی بسیار مفید است.

ترکیب تابع QUARTILE با فرمول‌های دیگر

• QUARTILE + IF برای دسته‌بندی داده‌ها (Low/Medium/High)

  =IF(A2QUARTILE($A$2:$A$100;3);"بالا";"میانی"))

• QUARTILE + COUNTIF برای شمارش موارد بالاتر/پایین‌تر از یک چارک

  =COUNTIF(A2:A100;"<="&QUARTILE(A2:A100;1))

• QUARTILE + SUMIF برای جمع کردن مقادیر یک گروه بر اساس آستانه چارکی

  =SUMIF(B2:B100;">="&QUARTILE(B2:B100;3);B2:B100)

• QUARTILE + AND/OR برای قوانین کنترلی و پرچم‌گذاری

  =IF(OR(C2QUARTILE($C$2:$C$200;3));"خارج از محدوده";"عادی")

• QUARTILE + AVERAGE برای مقایسه میانگین با چارک‌ها (تحلیل سریع)

  =AVERAGE(A2:A100)-QUARTILE(A2:A100;2)

خطاهای رایج و روش رفع آنها

1) خطای #NUM! به خاطر مقدار نامعتبر برای quart

اگر آرگومان دوم را چیزی غیر از 0، 1، 2، 3 یا 4 وارد کنید، اکسل معمولاً خطای #NUM! می‌دهد.

راه‌حل: مطمئن شوید quart یکی از مقادیر مجاز است (0 تا 4). اگر از سلول برای quart استفاده می‌کنید، اعتبارسنجی داده (Data Validation) بگذارید.

2) خطای #VALUE! به خاطر ورودی غیرعددی یا اشتباه تایپی در آرگومان‌ها

اگر به جای عدد چارک، متن وارد شود یا فرمت آرگومان‌ها اشتباه باشد، ممکن است #VALUE! بگیرید.

راه‌حل: آرگومان quart را عددی وارد کنید. همچنین بررسی کنید جداکننده آرگومان‌ها در سیستم شما درست باشد (در اکسل فارسی معمولاً ;).

3) نتیجه غیرمنتظره به خاطر وجود خطا در داده‌ها (#N/A، #DIV/0! و …)

اگر داخل محدوده داده‌ها سلولی دارای خطا باشد، QUARTILE هم ممکن است خطا بدهد یا محاسبه را مختل کند.

راه‌حل: قبل از محاسبه، داده‌ها را پاک‌سازی کنید؛ خطاها را برطرف کنید یا داده‌ها را در یک ستون کمکی بدون خطا آماده کنید.

4) نتیجه متفاوت با منابع آماری دیگر (اختلاف روش محاسبه چارک)

چارک‌ها می‌توانند با روش‌های مختلفی محاسبه شوند (تعریف آماری و روش‌های درون‌یابی متفاوت). ممکن است نتیجه اکسل با برخی ماشین‌حساب‌ها یا نرم‌افزارهای آماری کمی متفاوت باشد.

راه‌حل: اگر دقت روش برایتان مهم است، از توابع جدیدتر QUARTILE.INC و QUARTILE.EXC استفاده کنید و روش موردنظر سازمان/گزارش را یکسان‌سازی کنید.

5) انتخاب محدوده اشتباه (شامل سرستون، جمع کل، یا داده‌های غیرمرتبط)

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

راه‌حل: محدوده را دقیق انتخاب کنید (فقط داده خام). بهتر است محدوده را به Table تبدیل کنید تا انتخاب‌ها ساخت‌یافته‌تر شوند.

نکات حرفه‌ای و ترفندهای مهم

• از QUARTILE.INC و QUARTILE.EXC در نسخه‌های جدید استفاده کنید چون QUARTILE در بسیاری از نسخه‌های جدید به عنوان تابع «سازگاری» (Compatibility) در نظر گرفته می‌شود.
• داده‌ها را پاک‌سازی کنید: سلول‌های دارای خطا، متن‌های عددنما، و مقادیر غیرمرتبط را قبل از تحلیل حذف/اصلاح کنید.
• محدوده را ثابت کنید تا هنگام کپی کردن فرمول، آدرس‌ها تغییر نکنند.

  =QUARTILE($A$2:$A$100;3)

• خوانایی گزارش: Q1، Q2، Q3 را در سلول‌های جداگانه محاسبه کنید و بعد در فرمول‌ها به آن سلول‌ها ارجاع دهید تا هم سریع‌تر و هم قابل فهم‌تر باشد.
• برای تحلیل داده‌های پرت از قانون 1.5*IQR استفاده کنید: پایین‌تر از Q1-1.5*IQR و بالاتر از Q3+1.5*IQR معمولاً پرت محسوب می‌شوند.

تفاوت تابع QUARTILE با توابع مشابه

• QUARTILE vs QUARTILE.INC: QUARTILE در عمل مشابه QUARTILE.INC است (شامل نقاط ابتدا و انتهای توزیع). در نسخه‌های جدید توصیه می‌شود مستقیم از QUARTILE.INC استفاده کنید.
• QUARTILE vs QUARTILE.EXC: در QUARTILE.EXC روش محاسبه «انحصاری» است و برای برخی حجم داده‌ها و استانداردهای آماری، خروجی متفاوتی می‌دهد.
• QUARTILE vs PERCENTILE: چارک‌ها در واقع درصدک‌های خاص هستند: Q1=25%، Q2=50%، Q3=75%. اگر به درصدهای دیگر نیاز دارید، PERCENTILE.INC/EXC مناسب‌تر است.
• QUARTILE(…;2) vs MEDIAN: چارک دوم همان میانه است؛ برای خوانایی بهتر معمولاً MEDIAN مستقیم‌تر و واضح‌تر است.
• QUARTILE(…;0/4) vs MIN/MAX: برای کمینه/بیشینه بهتر است از MIN و MAX استفاده شود چون هدف را واضح‌تر نشان می‌دهد.

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

• Excel 2007 تا Excel 2019 و Microsoft 365: تابع QUARTILE معمولاً وجود دارد، اما در نسخه‌های جدید در دسته توابع سازگاری قرار می‌گیرد.
• پیشنهاد در نسخه‌های جدید: استفاده از QUARTILE.INC و QUARTILE.EXC برای شفافیت روش محاسبه و استانداردسازی گزارش‌ها.
• جداکننده آرگومان‌ها: در بسیاری از سیستم‌های فارسی، جداکننده ; است. اگر اکسل شما انگلیسی باشد ممکن است , لازم باشد.

سؤالات پرتکرار درباره تابع QUARTILE

آیا QUARTILE داده‌های متنی را حساب می‌کند؟

خیر، مقادیر متنی و سلول‌های خالی معمولاً نادیده گرفته می‌شوند؛ اما اگر در محدوده خطا وجود داشته باشد، می‌تواند باعث خطا در نتیجه شود.

چارک دوم با چه چیزی برابر است؟

چارک دوم (quart=2) همان میانه (Median) است.

چرا نتیجه QUARTILE با یک سایت آماری فرق دارد؟

به خاطر تفاوت روش محاسبه چارک (درون‌یابی و تعریف آماری). در صورت نیاز، روش محاسبه را با QUARTILE.INC یا QUARTILE.EXC مشخص و یکسان کنید.

برای داده‌های خیلی کم (مثلاً 5 مقدار) قابل اعتماد است؟

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

جمع‌بندی و پیشنهاد یادگیری بعدی

تابع QUARTILE یکی از ابزارهای ساده و قدرتمند اکسل برای تحلیل توزیع داده‌هاست و به شما کمک می‌کند مرزهای 25٪، 50٪ و 75٪ داده‌ها را سریع پیدا کنید، افراد/اقلام را دسته‌بندی کنید و پراکندگی را بسنجید. برای حرفه‌ای‌تر شدن، یادگیری توابع QUARTILE.INC و QUARTILE.EXC و همچنین PERCENTILE.INC/EXC و مفهوم IQR را پیشنهاد می‌کنم؛ این‌ها در تحلیل آماری و تشخیص داده‌های پرت بسیار کاربردی هستند.