تابع RANK.AVG با سناریوی کاربردی

دیدگاه‌ خود را بنویسید / از /
بازگشت به نتایج جستجو
تابع RANK.AVG با سناریوی کاربردی
ویژه⭐ محبوب✨

مقدمه

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

تابع RANK.AVG در اکسل رتبه یک مقدار را در میان یک محدوده می‌دهد و در صورت تساوی، میانگین رتبه‌های مربوطه را به هر یک از مقادیر مساوی اختصاص می‌دهد.

کلمات کلیدی

تابع RANK.AVG، آموزش RANK.AVG در اکسل، رتبه‌بندی در اکسل، تابع رتبه‌بندی، آموزش توابع اکسل، میانگین رتبه، رتبه در اکسل، آموزش اکسل، سناریوی کاربردی اکسل

سینتکس

RANK.AVG(number, ref, [order])

  • number: مقدار یا سلول مورد نظر برای رتبه‌بندی.
  • ref: محدوده‌ای که رتبه در آن محاسبه می‌شود.
  • order: اختیاری؛ 0 یا حذف = رتبه‌بندی نزولی (بزرگ‌ترین عدد رتبه 1)، 1 = رتبه‌بندی صعودی (کوچک‌ترین عدد رتبه 1).

مثال ساده با توضیح

داده‌ها در A1:A5 = 90, 80, 90, 70, 60

  • فرمول:
    RANK.AVG(A1, A1:A5, 0)=
    نتیجه: 1.5 چون دو مقدار 90 رتبه‌های 1 و 2 را اشغال کرده‌اند و میانگین 1.5 به هر دو داده تعلق می‌گیرد.
  • اگر بخواهیم صعودی:
    RANK.AVG(A5, A1:A5, 1)=
    (برای مقدار 60) نتیجه: 1 چون 60 کوچک‌ترین است.

این تابع از نسخه Excel 2010 به بعد در دسترس است و جایگزینی برای تابع قدیمی‌تر RANK محسوب می‌شود.

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

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

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

نام دانش‌آموز

نمره نهایی

علی

18

زهرا

20

مهدی

18

سارا

17

نازنین

20

برای محاسبه رتبه با استفاده از تابع RANK.AVG، در سلول C2 فرمول زیر را وارد می‌کنیم:

=RANK.AVG(B2, $B$2:$B$6, 0)

سپس این فرمول را برای سایر سلول‌های ستون C کپی می‌کنیم. نتیجه به این صورت خواهد بود:

نام دانش‌آموز

نمره نهایی

رتبه

علی

18

3.5

زهرا

20

1.5

مهدی

18

3.5

سارا

17

5

نازنین

20

1.5

همان‌طور که مشاهده می‌کنید، زهرا و نازنین که نمره یکسانی دارند، رتبه میانگین 1.5 را دریافت کرده‌اند. این ویژگی باعث می‌شود رتبه‌بندی منصفانه‌تری در داده‌های تکراری داشته باشیم.

مثال پیشرفته با تساوی‌های چندگانه

داده‌ها: 100, 90, 90, 90, 80

  • مقادیر 90 سه‌گانه رتبه‌های 2، 3 و 4 را اشغال می‌کنند؛ RANK.AVG برای هر 90 مقدار (2+3+4)/3 = 3 باز می‌گرداند.

ترکیب‌های کاربردی

  • رتبه درون گروه (مثال: رتبه در هر بخش):
    RANK.AVG(value, FILTER(all_values, group_range=group), 0)
  • رتبه بدون محاسبه سلول‌های خالی یا متن:
    RANK.AVG(number, IF(ISNUMBER(range), range), order)

    • به صورت آرایه‌ای (در نسخه‌های جدید اکسل کافی است فرمول وارد شود).

نکات و ترفندها

  • اگر می‌خواهید تساوی‌ها را بدون میانگین و به صورت اولین وقوع رتبه‌بندی کنید، از RANK.EQ استفاده کنید.
  • RANK.AVG در نسخه‌های مدرن اکسل (Office 365 / Excel 2010 به بعد) موجود است؛ در نسخه‌های قدیمی ممکن است در دسترس نباشد.
  • هنگام استفاده با فرمول‌های آرایه‌ای، از ساختارهای FILTER یا IF برای حذف مقادیر نامعتبر استفاده کنید تا نتایج درست بگیرید.
  • برای نمایش رتبه‌های ترتیبی بدون پرش پس از تساوی (1,2,2,3 به جای 1,2,2,4) باید از روش‌های جایگزین مثل شمارش کمترها و مساوی‌ها (COUNTIF) استفاده کنید.

 جدول نمونه داده‌ها

A1:F1 عناوین ستون‌ها
A2:A11 ستون Employee
B2:B11 ستون Department
C2:C11 ستون Score

مثال (داده‌ها را دقیقاً در شیت وارد کنید):

A B C
Employee Department Score
Ali Sales 88
Sara Sales 92
Reza HR 75
Mina Sales 92
Neda HR 75
Kamran IT 85
Laleh IT 85
Omid Sales 70
Parisa HR  

رتبه کلی با RANK.AVG (نزولی)

در سلول D2 فرمول را وارد و تا D11 کپی کنید:

=IF(ISNUMBER(C2), RANK.AVG(C2, $C$2:$C$11, 0), "")
  • توضیح: عدد غیرقابل‌محاسبه یا خالی را نادیده می‌گیرد و برای مقادیر عددی، رتبه نزولی در کل مجموعه را محاسبه می‌کند.

رتبه درون گروه (Department) —

روش مدرن با FILTER (Office 365 / Excel 2021+)

در سلول E2 فرمول را وارد و تا E11 کپی کنید:

=IF(ISNUMBER(C2),RANK.AVG(C2,FILTER($C$2:$C$11,$B$2:$B$11=B2),0),"")
  • توضیح: با FILTER فقط امتیازهای همان دپارتمان را برای هر سطر می‌گیرد و سپس RANK.AVG محاسبه می‌شود.

رتبه درون گروه

روش سازگارتر بدون FILTER (برای نسخه‌های قدیمی)

در سلول E2 فرمول را وارد و تا E11 کپی کنید:

=IF(ISNUMBER(C2),RANK.AVG(C2,IF($B$2:$B$11=B2,$C$2:$C$11),0),"")

فرمول را به‌صورت آرایه‌ای وارد کنید (در نسخه‌های قدیمی با Ctrl+Shift+Enter) در غیر این‌صورت اگر Excel شما آرایه‌های پویا پشتیبانی می‌کند کافی است Enter بزنید.

رتبه ترتیبی بدون پرش پس از تساوی (سریال متوالی)

اگر می‌خواهید بعد از تساوی رتبه‌ها پُرش نکند (مثال: 1,2,2,3 به‌جای 1,2,2,4) از فرمول مبتنی بر COUNTIFS. در سلول F2 بنویسید و تا F11 کپی کنید:

رتبه کلی ترتیبی (نزولی):

=IF(ISNUMBER(C2),1+COUNTIFS($C$2:$C$11,">"&C2),"")

رتبه درون گروه ترتیبی (نزولی):

=IF(ISNUMBER(C2),1+COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,">"&C2),"")
  • توضیح: این فرمول تعداد رکوردهای بزرگ‌تر از مقدار فعلی را می‌شمارد و 1 اضافه می‌کند تا رتبه ترتیبی بدون پرش بسازد.

قالب بندی و نکات پایانی

  • برای نمایش رتبه‌ها به‌عنوان عدد صحیح، ستون رتبه‌ها را فرمت General یا Number بگذارید.
  • اگر می‌خواهید تساوی‌ها را با ترتیب ورود (اولویت تاریخ یا ID) بشکنید، از یک کلید ثانویه در COUNTIFS استفاده کنید (مثلاً مقایسه تاریخ یا ID).
  • برای نشان‌دادن همزمان رتبه کلی و رتبه درون گروه، ستون‌های D و E را کنار هم داشته باشید تا پنل گزارش‌محور ایجاد شود.

سئوالات متداول:

مشخصات و دانلود

دیدگاهتان را بنویسید

این سایت از اکیسمت برای کاهش جفنگ استفاده می‌کند. درباره چگونگی پردازش داده‌های دیدگاه خود بیشتر بدانید.

سبد خرید
پیمایش به بالا