جشنواره تابستانی اکسل پدیا (یک روز تا پایان)
سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

۵/۵ - (۱ امتیاز)

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

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

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

تعیین سه نفر برتر - داده های مسئله رتبه بندی داده تکراری

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

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

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

تعیین سه نمره برتر- تابع Large

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

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

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

تعیین مکان نمرات برتر در دیتابیس – تابع Match

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

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

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

تعیین نام افراد برتر-تابع Index

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

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

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

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

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

تعیین افراد برتر با امتیازات مشابه

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

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

=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 ثبت بشه. در ادامه نحوه کارکرد این فرمول رو با هم بررسی میکنیم:

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

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

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

($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  در هم ضرب میشن، نتیجه مجموعه ای صفر و یک خواهد بود. پس نتیجه ضرب دو آرایه بالا بصورت زیر خواهد بود:

{۰;۰;۰;۰;۰;۱;۱;۰;۰}

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

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 راه حل پیدا کنید.

126

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • Mehdi LOTFI ۲۶ اسفند ۱۳۹۹ / ۶:۰۰ ب٫ظ

    salam
    yek jadval sakhtam baray tolide mahsol ba sar titer asli
    ama azayei ke baray tolide mahsol hast dar hal taghir hastesh har hafteh ya har mah
    ajzaye jadidi ezafeh misheh b jadval va khili sakht o tolani mishe jadval
    mamnon misham agar ranamaii konid ya ide behtari dashtebashid dar in zamine

    ba tashakor

    • آواتار
      حسنا خاکزاد ۲۷ اسفند ۱۳۹۹ / ۱۱:۲۴ ق٫ظ

      درود
      سوالتون اصلا واضح نیست و معلوم نیست مشکل چیه . زحمت بکشید با مثال توضیح بدید و دقیق مشکل رو بیان کنید
      نکته دوم اینکه لطفا فارسی بنویسید
      ممنون

  • ميثم رزم آرا ۸ دی ۱۳۹۹ / ۱۱:۱۳ ق٫ظ

    ضمن اینکه در قسمت نتیجه تابع مچ جای صفر و یک اشتباه شده!
    MATCH(0,{0;0;0;0;0;1;1;0;0}, 1)

  • ميثم رزم آرا ۸ دی ۱۳۹۹ / ۱۰:۰۳ ق٫ظ

    سلام
    جالب بود واقعا، ممنون

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان