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

معرفی تابع PERCENTRANK

تابع PERCENTRANK در اکسل برای این استفاده می‌شود که «رتبه درصدی» (Percent Rank) یک عدد را نسبت به یک مجموعه داده به شما بدهد. خروجی این تابع عددی بین 0 تا 1 است:

• 0 یعنی مقدار موردنظر کمترین مقدار در بازه است.
• 1 یعنی مقدار موردنظر بیشترین مقدار در بازه است.
• مثلاً 0.75 یعنی مقدار شما از حدود 75% داده‌ها بزرگ‌تر یا مساوی است (در یک برداشت رایج: در صدک 75 قرار دارد).

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

مثال ساده فارسی: فرض کنید نمرات 10 دانش‌آموز در محدوده A2:A11 است و می‌خواهید بدانید نمره 17 (در سلول C2) از نظر درصدی در چه جایگاهی قرار می‌گیرد.

=PERCENTRANK(A2:A11;C2)

خروجی مثلاً اگر 0.8 باشد یعنی نمره 17 نسبت به این لیست، در حدود 80% بالایی قرار گرفته است.

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

• تعیین جایگاه درصدی نمرات دانش‌آموزان (صدک‌ها و رتبه‌بندی)
• تحلیل فروش: اینکه فروش یک روز/یک شعبه نسبت به کل داده‌ها در چه سطحی است
• ارزیابی عملکرد: جایگاه KPI یک کارمند نسبت به سایرین
• تقسیم‌بندی مشتریان (مثلاً مشتریان بالای صدک 90)
• مقایسه قیمت/هزینه: اینکه یک قیمت نسبت به بازار چقدر بالا/پایین است
• گزارش‌گیری مدیریتی و ساخت داشبوردهای رتبه درصدی

ساختار (Syntax)

فرم کلی:

=PERCENTRANK(array,x,[significance])

فرم فارسی آرگومان‌ها:

=PERCENTRANK(بازه_اعداد;عدد_هدف;[تعداد_اعشار_گردکردن])

آرگومان‌ها

array (بازه_اعداد) / مجموعه داده

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

x (عدد_هدف) / عددی که می‌خواهیم رتبه درصدی آن را پیدا کنیم

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

[significance] (تعداد_اعشار_گردکردن) / میزان دقت خروجی (اختیاری)

تعداد رقم اعشار برای گرد کردن رتبه درصدی. مثلاً اگر 2 بگذارید، خروجی مثل 0.83 نمایش داده می‌شود. اگر وارد نکنید، اکسل یک دقت پیش‌فرض در نظر می‌گیرد.

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

مثال 1: رتبه درصدی یک نمره در بین نمرات

فرض کنید در ستون A نمرات این‌ها را دارید:

• A2:A11 = نمرات دانش‌آموزان
• C2 = نمره‌ای که می‌خواهید رتبه درصدی‌اش را حساب کنید (مثلاً 17)

=PERCENTRANK(A2:A11;C2)

توضیح نتیجه: اگر خروجی 0.6 باشد، یعنی نمره C2 حدوداً از 60% نمرات لیست بالاتر/بهتر است (جایگاه بالاتری دارد).

مثال 2: گرد کردن خروجی به 2 رقم اعشار

=PERCENTRANK(A2:A11;C2;2)

توضیح نتیجه: خروجی به شکل 0.00 تا 1.00 نمایش داده می‌شود و خوانایی گزارش بهتر می‌شود.

مثال 3: تبدیل رتبه درصدی به درصد (0 تا 100)

PERCENTRANK عددی بین 0 و 1 می‌دهد. اگر بخواهید درصد ببینید:

=PERCENTRANK(A2:A11;C2)*100

توضیح نتیجه: مثلاً 0.83 تبدیل می‌شود به 83. (می‌توانید قالب سلول را هم Percentage کنید.)

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

مثال 1: تشخیص «فروش‌های عالی» (بالای صدک 90) با IF

فرض کنید فروش روزانه در A2:A101 است و فروش یک روز در C2 قرار دارد. می‌خواهیم اگر رتبه درصدی فروش C2 بالای 0.9 بود، برچسب «عالی» بدهیم.

=IF(PERCENTRANK(A2:A101;C2)>=0.9;"عالی";"عادی")

توضیح نتیجه: این فرمول به شما کمک می‌کند بدون رتبه‌بندی دستی، فروش‌های برتر را جدا کنید.

مثال 2: شرط‌گذاری با AND و OR (چند معیار هم‌زمان)

فرض کنید:

• A2:A101 = فروش
• B2:B101 = سود
• C2 = فروش یک رکورد
• D2 = سود همان رکورد

می‌خواهیم فقط وقتی «برتر» بنامیم که هم فروش بالای صدک 80 باشد و هم سود مثبت باشد.

=IF(AND(PERCENTRANK(A2:A101;C2)>=0.8;D2>0);"برتر";"غیر برتر")

حالا یک حالت دیگر: اگر فروش خیلی بالا بود یا سود خیلی بالا بود:

=IF(OR(PERCENTRANK(A2:A101;C2)>=0.9;PERCENTRANK(B2:B101;D2)>=0.9);"VIP";"معمولی")

مثال 3: شمارش تعداد آیتم‌های بالای یک آستانه رتبه درصدی با COUNTIF

فرض کنید در ستون E (E2:E101) رتبه درصدی فروش هر روز را محاسبه کرده‌اید. حالا می‌خواهید ببینید چند روز «بالای 70%» بوده‌اند.

=COUNTIF(E2:E101;">=0.7")

نکته: اگر هنوز ستون E را ندارید، می‌توانید ابتدا رتبه را برای هر ردیف بسازید و بعد COUNTIF کنید.

مثال 4: دسته‌بندی مشتریان و واکشی نام دسته با XLOOKUP

فرض کنید رتبه درصدی مشتری در C2 است (عددی بین 0 و 1). یک جدول دسته‌بندی دارید:

• E2:E5 = آستانه‌ها (0; 0.5; 0.8; 0.95)
• F2:F5 = برچسب‌ها (“معمولی”; “خوب”; “عالی”; “طلایی”)

حالا می‌خواهیم بر اساس نزدیک‌ترین آستانه کوچک‌تر یا مساوی، برچسب را برگردانیم.

=XLOOKUP(C2;E2:E5;F2:F5;;-1)

توضیح نتیجه: اگر C2 برابر 0.83 باشد، برچسب “عالی” برمی‌گردد.

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

• ترکیب با IF برای برچسب‌گذاری سطح عملکرد

  =IF(PERCENTRANK(A2:A101;C2)>=0.9;"Top 10%";"Other")

• ترکیب با AND/OR برای قوانین چندشرطی

  =IF(AND(PERCENTRANK(A2:A101;C2)>=0.8;C2>=5000000);"هدف‌دار";"نیازمند رشد")

• ترکیب با ROUND برای کنترل نمایش و گزارش

  =ROUND(PERCENTRANK(A2:A11;C2);3)

• ترکیب با SUM برای محاسبه جمع فروش «بالای صدک 80» (رویکرد کمکی با ستون رتبه)

  =SUMIF(E2:E101;">=0.8";A2:A101)

• ترکیب با COUNTIF برای شمارش افراد/روزهای برتر

  =COUNTIF(E2:E101;">=0.9")

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

1) خطای #N/A

این خطا معمولاً وقتی رخ می‌دهد که مقدار x خارج از محدوده داده‌ها باشد (کمتر از حداقل یا بیشتر از حداکثر) یا داده‌ها شرایط لازم را نداشته باشند.

• بررسی کنید x بین کمترین و بیشترین مقدار array باشد.
• اگر داده‌ها متن/خطا دارند، آن‌ها را پاکسازی کنید.

2) خطای #VALUE!

وقتی در بازه array یا در x مقدار غیرعددی (متن) وجود داشته باشد یا ورودی‌ها درست انتخاب نشده باشند، ممکن است #VALUE! بگیرید.

• مطمئن شوید array فقط شامل عدد است.
• x هم عدد باشد یا سلولی که عدد دارد.

3) خطای #NUM!

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

• significance را خالی بگذارید یا یک عدد صحیح معتبر وارد کنید (مثل 1، 2، 3).

4) نتیجه‌های غیرمنتظره به خاطر داده‌های تکراری

اگر داده‌ها مقدار تکراری زیاد داشته باشند، رتبه درصدی ممکن است با برداشت شما از «رتبه» متفاوت باشد، چون تابع روی جایگاه در توزیع داده کار می‌کند.

• اگر هدف شما «رتبه‌بندی ترتیبی» است، از RANK.EQ یا RANK.AVG هم استفاده کنید و در کنار PERCENTRANK تحلیل کنید.

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

• برای خوانایی، خروجی را درصدی نمایش دهید: یا قالب سلول را Percentage کنید یا در 100 ضرب کنید.
• از significance برای گزارش‌های مدیریتی استفاده کنید: معمولاً 2 یا 3 رقم اعشار کافی است.
• برای جلوگیری از خطا وقتی x خارج از بازه است، قبلش حداقل و حداکثر را کنترل کنید (رویکرد شرطی):

  =IF(OR(C2MAX(A2:A101));"";PERCENTRANK(A2:A101;C2;2))

• برای فایل‌های جدید، PERCENTRANK.INC و PERCENTRANK.EXC را هم بشناسید: در بسیاری از سناریوها انتخاب INC/EXC روی تفسیر مرزی (0 و 1) مهم است.
• داده‌ها را ثابت (Absolute) کنید تا هنگام کپی‌کردن فرمول به ردیف‌های دیگر، بازه تغییر نکند:

  =PERCENTRANK($A$2:$A$101;C2;2)

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

• PERCENTRANK:
  • خروجی بین 0 و 1 (رتبه درصدی)
  • برای فهم «جایگاه درصدی» یک مقدار نسبت به کل داده‌ها
• RANK.EQ / RANK.AVG:
  • خروجی یک «رتبه عددی» مثل 1، 2، 3…
  • برای رتبه‌بندی مستقیم (اول، دوم، سوم)
• PERCENTILE.INC / PERCENTILE.EXC:
  • برعکس PERCENTRANK عمل می‌کنند: به جای اینکه رتبه درصدیِ یک عدد را بدهند، عدد متناظر با یک درصدک را برمی‌گردانند.
• QUARTILE.INC:
  • تقسیم‌بندی داده به چارک‌ها (25%، 50%، 75%)
  • برای گزارش‌های سطح‌بندی ساده‌تر

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

• تابع PERCENTRANK در نسخه‌های قدیمی‌تر اکسل وجود دارد، اما در نسخه‌های جدیدتر، مایکروسافت توابع دقیق‌تر/شفاف‌تر را معرفی کرده است.
• در اکسل‌های جدید (Microsoft 365 و Excel 2019/2021)، معمولاً پیشنهاد می‌شود به جای آن از PERCENTRANK.INC یا PERCENTRANK.EXC استفاده کنید (بسته به نیاز محاسباتی).
• اگر فایل را بین کاربران با نسخه‌های مختلف جابه‌جا می‌کنید، بهتر است قبل از نهایی کردن، روی سیستم مقصد هم تست کنید.

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

آیا خروجی PERCENTRANK همیشه بین 0 و 1 است؟

بله، در حالت عادی خروجی در همین بازه است. اگر خطا بگیرید یعنی داده/ورودی مشکل دارد (مثلاً x خارج از بازه است).

چطور خروجی را به درصد (مثل 83%) تبدیل کنم؟

یا قالب سلول را Percentage کنید، یا خروجی را در 100 ضرب کنید.

=PERCENTRANK(A2:A11;C2)*100

اگر داده‌ها تکراری باشند، نتیجه چه می‌شود؟

PERCENTRANK بر اساس جایگاه در توزیع داده عمل می‌کند و در حضور تکرارها ممکن است برداشت شما از «رتبه» با خروجی متفاوت باشد. برای مقایسه، RANK.AVG هم می‌تواند کمک کند.

significance دقیقاً چه کاری می‌کند؟

فقط میزان گرد کردن نتیجه را کنترل می‌کند (برای نمایش و گزارش). روی منطق کلی رتبه درصدی اثر مفهومی ندارد، فقط دقت خروجی را تغییر می‌دهد.

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

تابع PERCENTRANK یک ابزار ساده و بسیار کاربردی برای فهم جایگاه «درصدی» یک عدد در بین یک مجموعه داده است. با آن می‌توانید به‌راحتی تحلیل‌هایی مثل «بالای 10%»، «میانگین»، «ضعیف/قوی»، و دسته‌بندی عملکرد را بسازید، مخصوصاً وقتی آن را با توابعی مثل IF، AND/OR، COUNTIF و XLOOKUP ترکیب کنید.

پیشنهاد یادگیری بعدی: بعد از تسلط به PERCENTRANK، سراغ این توابع بروید تا تحلیل آماری و رتبه‌بندی‌تان حرفه‌ای‌تر شود: PERCENTRANK.INC و PERCENTRANK.EXC، سپس PERCENTILE.INC و RANK.EQ / RANK.AVG.