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

تفاوت تابع Rank.EQ و Rank اکسل چیه؟

خیلی وقت ها پیش میاد که بخوایم بدونیم یک عدد مشخص، بین یک مجموعه عدد، چه رتبه ای داره (در واقع چندمین عدد هست؟)
برخی افراد ترجیح میدن اعداد رو مرتب (سورت) کنن (کوچک به بزرگ یا برعکس) و بعد از روی ترتیب جدید ببینن که عدد مورد نظر چندمین عدد هست. اما این روش ابزار هست، یعنی با تغییر هر عدد، باید دوباره مراحل رو انجام بدیم. اگر نیاز باشه که بصورت اتومات آپدیت بشه باید از تابع مخصوص برای اینکار استفاده کنیم. در این مقاله میخوایم روش رتبه بندی با استفاده از تابع Rank اکسل رو شرح بدیم.

تابع Rank تابع رتبه بندی در اکسل هست. در واقع جایگاه یک عدد رو بین یک سری اعداد به ما میده. در ادامه با آرگومان های این تابع آشنا میشیم.

Number: عددی هست که میخوایم ببینیم چه جایگاهی داره و چندمین عدد هست.

Ref: محدوده ای از اعداد که میخوایم رتبه بندی بین اونها انجام بشه. هم میتونه آرایه ای از اعداد باشه و هم یک رنج(Range)

Order: این آرگومان اختیاری (optional) است عدد ۰ یا ۱ رو میگیره. در واقع با این دو عدد مشخص میکنیم که منطق رتبه بندی چطور باشه؟

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

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

Rank.EQ: این تابع حالت پیشرفته تابع rank هست و از ورژن ۲۰۱۰ به اکسل اضافه شده. آرگومان ها مشابه با تابع rank هست، اما در خروجی با هم متفاوت هستن. به اینصورت که اگر چند عدد وجود داشته باشه که جایگاه یکسان داشته باشن، برای همه، بزرگترین رتبه در نظر گرفته میشه.

Rank.AVG: این تابع حالت پیشرفته تابع rank هست و از ورژن ۲۰۱۰ به اکسل اضافه شده. آرگومان ها مشابه با تابع rank هست، اما در خروجی با هم متفاوت هستن. به اینصورت که اگر چند عدد وجود داشته باشه که رتبه یکسان داشته باشن، خروجی این تابع میانگین رتبه ها خواهد بود.

۴ نکته ای که باید راجع به این تابع بدونید

• توابع رتبه بندی در اکسل فقط روی اعداد کار میکنن. اعداد منفی، مثبت، صفر، تاریخ و زمان. داده های غیر عددی در نظر گرفته نمیشن.
• توابع رتبه بندی، برای داده های تکراری یک رتبه یکسان رو نمایش میدن و به ازای داده های تکراری یک رتبه رو در نظر میگیرن. (به مثال اول دقت کنید)
• در ورژن ۲۰۱۰ توابع EQ و Rank.AVG به اکسل اضافه شدن. اما همچنان تابع Rank برای حفظ سازگاری با نسخه های قبلی در اکسل وجود داره. اما ممکنه د رآینده حذف بشه.
• اگر در مجموعه REF عددی پیدا نشه، خروجی تابع #N/A خواهد بود.

مثال اول- حالت ساده و اصلی تابع RANK

فرض کنید میخواهیم افراد رو بر اساس نمره کسب شده، رتبه بندی کنیم. هر سه تابع رتبه بندی رو در شکل ۱ می بینید. به خروجی توابع در خصوص اعداد تکراری دقت کنید.

شکل ۱- رتبه بندی با سه تابع رتبه بندی

چند نکته راجع به مثال اول:

• همونطور که مشاهده میکنید، برای داده های تکراری، رتبه یکسان تکرار شده.
• خروجی تابع RANK و EQ یکسان هست. در واقع تابع RANK میتونه کم کم حذف بشه.
• وقتی دو عدد رتبه ۳ دارن، در واقع رتبه ۳ و ۴ اشغال شده. برای همین بعد از دو تا رتبه ۳، رتبه بعدی ۵ هست و ما اصلا رتبه ۴ نداریم.
• خروجی تابع AVG رتبه ۳.۵ هست. چون بین عدد ۳ و ۴ (رتبه دو عدد مشابه) میانگین گرفته شده.

مثال دوم-کاربرد تابع RANK در حالت صعودی یا Order=1

در این حالت تابع، اعداد رو طوری رتبه بندی میکنه که بیشترین عدد رتبه کمتری بگیره.

مثلا میخواهیم دانش آموزان رو بر اساس ساعت ورود به مدرسه رتبه بندی کنیم. هر کس زودتر آمده، رتبه بهتر و هر کس دیرتر آمده رتبه بدتر نمایش داده بشه.

=RANK (B3 ; $B$3:$B$9 ; 1)

شکل ۲- رتبه بندی دانش آموزان بصورت صعودی با تابع Rank اکسل

مثال سوم- رتبه بندی داده های تکراری بصورت منحصربفرد

همونطور که ملاحظه کردید، توابع رتبه بندی برای داده های تکراری، رتبه یکسان نمایش میدهند. حالا اگر بخوایم مثلا دو عدد که رتبه ۳ دارن رو بصورت ۳ و ۴ ببینیم چکار باید بکنیم؟

برای حل این موضوع باید تعداد عدد مورد نظر رو به رتبه خروجی اضافه کنیم. به شکل ۳ دقت کنید:

=RANK (B3;$B$3:$B$9) + COUNTIF ($B$2:B2;B3)

شکل ۳- رتبه بندی اعداد تکراری با تابع Rank اکسل

در مثال بالا، تابع Countif تعداد عدد مورد نظر رو در محدوده بالای سرش به خروجی RANK اضافه میکنه. در واقع فرمولی که در ردیف ۸ نوشته شده به اینصورت عمل میکنه:

تعداد عدد ۵۶ در محدوده B3:B7 شمرده میشه و نتیجه میشه ۱. این مقدار به خروجی RANK که عدد ۳ هست اضافه میشه و میشه ۴.

مثال چهارم- رتبه بندی بر اساس شروط چندگانه

فرض کنید میخواید دانش آموزان رو بر اساس نمره (معیار اول) رتبه بندی کنید. هر چند دانش آموزی که نمره یکسان داشتن، بر اساس نمره انضباط (معیار دوم) رتبه بندی بشن.

به شکل ۴ دقت کنید. دانش آموز E و F نمره یکسان ۵۶ کسب کردن که رتبه هر دو ۳ میشه. حالا میخوایم کسی که انضباط بهتری داره، رتبه ۳ و کسی انضباطش پایینتره رتبه ۴ بگیره. برای این کار از تابع Countifs استفاده میکنیم و شرط های مورد نظر رو اعمال میکنیم.

=RANK.EQ ( $B2; $B$2:$B$8)+COUNTIFS ( $B$2:$B$8;$B2;$C$2:$C$8; “>”&$C2)

شکل ۴- رتبه بندی بر اساس شروط چندگانه با استفاده از تابع Rank اکسل

در واقع قسمت Countifs فرمول، میاد دو شرط رو بررسی میکنه و خروجی این تابع اضافه میشه به خروجی تابع رتبه بندی.

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

پبیشنهاد میکنم مقاله مربوط به تابع Small و تابع Large رو مطالعه کنید و تفاوت ها رو خوب درک کنید. سعی کنید این دو تابع رو با حالت های مختلف تابع Rank اکسل تطبیق بدید.

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

برای دانلود فایل این آموزش روی دکمه زیر کلیک کنید: