حل مسئله رتبه بندی داده های تکراری

تعیین شاگرد اول تا سوم یک کلاس

مسئله رتبه بندی در اکسل یکی از مسائل پرکاربرد هست. در واقع رتبه بندی تقریبا در هر زمینه ای وجود داره. رتبه بندی مشتریان، رتبه بندی فروش محصول، رتبه بندی محصولات برتر و … . رتبه بندی در اکسل بسته به شرایط داده ها و منطق مورد نظر ما در رتبه بندی متفاوت هست. در این خصوص حتما مقاله فوق العاده مربوط به تابع Rank رو مطالعه کنید. در این مقاله میخواهیم سعی کنیم با استفاده از تابع Large نام سه نفر برتر یک کلاس رو مشخص کنیم (به عبارتی قصد داریم مسئله رتبه بندی داده تکراری رو در این مقاله بررسی کنیم).

فرض کنید داده هایی مشابه شکل یک داریم و میخواهیم رتبه بندی رو انجام بدیم.

شکل 1- تعیین سه نفر برتر (رتبه بندی داده تکراری) – داده ها

برای این کار، ابتدا از تابع Large استفاده میکنیم و سه نمره برتر رو مشخص میکنیم.

=LARGE($B$2:$B$10,ROW(A1))

شکل 2 – تعیین سه نمره برتر- تابع Large

حالا که با استفاده از تابع Large سه نمره برتر رو تعیین کردیم حالا کافیه مکان این نمرات رو در دیتابیس پیدا کنیم. برای این کار از تابع Match استفاده میکنیم.

=MATCH(D2,$B$2:$B$10,0)

شکل 3- تعیین مکان نمرات برتر در دیتابیس – تابع Match برای حل مسئله رتبه بندی داده تکراری

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

=INDEX($A$2:$A$10,E2)

شکل 4 – تعیین نام افراد برتر-تابع Index برای حل مسئله رتبه بندی داده تکراری

حالا میتونیم هر سه تابع رو با هم ترکیب کنیم و فرمول زیر رو بنویسیم (بدون نیاز به سلول کمکی):

=INDEX($A$2:$A$10,MATCH(LARGE($B$2:$B$10,ROW(A1)),$B$2:$B$10,0))

اگر نمرات تکراری بود چطوراین کار رو انجام میدیم (رتبه بندی داده تکراری)؟

روشی که در بالا تشریح شد برای داده هایی کار میکنه که منحصربفرد و غیرتکراری باشند. اگر دو نفر نمره مشابه داشته باشند، هر بار در تابع Match اولین نفر پیدا میشه و نفر دوم با نمره مشابه توسط تابع Match قابل ردیابی کردن نیست.

شکل 5- تعیین افراد برتر با امتیازات مشابه

برای اینکه بتونیم بین داده های تکراری سه رتبه برتر رو به همراه نام پیدا کنیم، از روش زیر استفاده میکنیم:

=INDEX ($A$2:$A$10, MATCH(1, ($B$2:$B$10=LARGE($B$2:$B$10, ROW(A1))) * (COUNTIF(D$1:D1, $A$2:$A$10)=0), 0))

این فرمول آرایه ای هست و باید با Ctrl+Shift+Enter ثبت بشه. در ادامه نحوه کارکرد این فرمول رو با هم بررسی میکنیم:

شکل 6 – تعیین افراد برتر با امتیازات مشابه (رتبه بندی داده تکراری) با فرمول آرایه ای

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

($B$2:$B$10=LARGE($B$2:$B$10, ROW(A1)))

خروجی این فرمول آرایه ای هست از True و False که نشون میده چند عدد مشابه و برابر با Nاولین عدد بزرگ وجود داره.(دقت کنید که عدد تکراری لزوما دو عدد اول نیست. میتونه اعداد بعدی هم باشه). در این مثال خروجی این قسمت به شکل زیر خواهد بود:

{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}

قسمت دوم فرمول یعنی Countif در مجموعه بالای سرش (به نوع آدرس دهی $ دقت کنید) سرچ میکنه که عبارت پیدا شده وجود نداشته باشه. یعنی تعدادش صفر باشه. در واقع با این قسمت چک میشه که اسم تکراری نیاد.

(COUNTIF(D$1:D1, $A$2:$A$10)=0)

نتیجه این قسمت نیز به صورت زیر خواهد بود:

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

حالا نتیجه دو قسمت بالا در هم ضرب میشه و نتیجه میشه سلول هایی که معادل با Large مورد نظر هستند و تکراری نیستن. وقتی مجموعه ای از عبارات Logical  در هم ضرب میشن، نتیجه مجموعه ای صفر و یک خواهد بود. پس نتیجه ضرب دو آرایه بالا بصورت زیر خواهد بود:

{0;0;0;0;0;1;1;0;0}

این مجموعه داره مکان دو سلولی که مقدار مشابه دارند و از طرفی جزو چند عدد بزرگ هستند رو نشون میده. پس کافیه با تابع Match مکان اولین عدد 1 رو پیدا کنیم. نتیجه به شکل زیر خواهد بود:

MATCH(0,{0;0;0;0;0;1;1;0;0}, 1)

خروجی تابع Match مکان اولین سلول مورد نظر رو تعیین میکنه.

نتیجه تابع Match میاد در تابع Index قرار میگیره و داده مرتبط با رکورد مورد نظر نمایش داده میشه.

=INDEX($A$2:$A$10,6)

در واقع قسمت Countif فرمول کمک میکنه که اسم تکراری پیدا شده نمایش داده نشه. چون در بالا قبلا خروجی فرمول بوده است.

خب در این مقاله تونستیم سه عدد بزرگتر رو با داده های متناظر مشخص کنیم. با این روش میتونیم هر مجموعه ای از N عدد بزرگ رو تعیین کنیم. توجه داشته باشید که اگر بخوایم N عدد کوچک رو تعیین کنیم باید از تابع Small استفاده کنیم.

مقاله Rank رو حتما مطالعه کنید و سعی کنید برای این مسئله با استفاده از تابع Rank راه حل پیدا کنید.