اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

مسائل کاربردی

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

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

  • ارسال شده توسط سامان چراغی
  • دسته بندی مسائل کاربردی
  • تاریخ ۱۱ آذر ۱۳۹۹
  • نظرات ۲ دیدگاه ها
5 / 5 ( 1 امتیاز )

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

حتما بخوانید:  بدست آوردن Kامین عدد بزرگ با تابع Large

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

=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 در مجموعه بالای سرش (به نوع آدرس دهی $ دقت کنید) سرچ میکنه که عبارت پیدا شده وجود نداشته باشه. یعنی تعدادش صفر باشه. در واقع با این قسمت چک میشه که اسم تکراری نیاد.

حتما بخوانید:  ترکیب توابع با تابع Vlookup در اکسل

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

برچسب:آرایه ای, تابع Countif, تابع Index, تابع Large, تابع Match

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

مطلب قبلی

ترکیب Mail Merge ورد و اکسل
۱۱ آذر ۱۳۹۹

مطلب بعدی

تفکیک اعداد با استفاده فرمت دهی شرطی
۱۱ آذر ۱۳۹۹

ممکن است همچنین دوست داشته باشید

External-Links-Cover
مدیریت لینک ها در اکسل
۲۷ بهمن, ۱۳۹۹
WildCards-Cover
جستجو پیشرفته در اکسل با استفاده از Wildcard
۲۲ بهمن, ۱۳۹۹
Conditional-Formatting-Nth-Large-Small-Cover
تفکیک اعداد با استفاده فرمت دهی شرطی
۴ بهمن, ۱۳۹۹

    2 نظر

  1. profile avatar
    ميثم رزم آرا
    ۸ دی ۱۳۹۹
    پاسخ

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

  2. profile avatar
    ميثم رزم آرا
    ۸ دی ۱۳۹۹
    پاسخ

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

نظر بدهید لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

از طریق ایمیل، مطالب آموزشی درباره اکسل و برنامه نویسی وی بی و سایر رویدادهای مهم را دریافت خواهید کرد.
  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

آخرین مطالب

نصب Power Query
فعال کردن Power Query در ورژن های مختلف اکسل
۰۷اسفند۱۳۹۹
3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل
۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل
۰۱اسفند۱۳۹۹
مدیریت لینک
مدیریت لینک ها در اکسل
۲۷بهمن۱۳۹۹
جستجو پیشرفته با Wild Card
جستجو پیشرفته در اکسل با استفاده از Wildcard
۲۲بهمن۱۳۹۹

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

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

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

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

info@excelpedia.net

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما