رتبه بندی اعداد در اکسل
تفاوت تابع Rank.EQ و Rank اکسل چیه؟
خیلی وقت ها پیش میاد که بخوایم بدونیم یک عدد مشخص، بین یک مجموعه عدد، چه رتبه ای داره (در واقع چندمین عدد هست؟)
برخی افراد ترجیح میدن اعداد رو مرتب (سورت) کنن (کوچک به بزرگ یا برعکس) و بعد از روی ترتیب جدید ببینن که عدد مورد نظر چندمین عدد هست. اما این روش ابزار هست، یعنی با تغییر هر عدد، باید دوباره مراحل رو انجام بدیم. اگر نیاز باشه که بصورت اتومات آپدیت بشه باید از تابع مخصوص برای اینکار استفاده کنیم. در این مقاله میخوایم روش رتبه بندی با استفاده از تابع Rank اکسل رو شرح بدیم.
تابع Rank تابع رتبه بندی در اکسل هست. در واقع جایگاه یک عدد رو بین یک سری اعداد به ما میده. در ادامه با آرگومان های این تابع آشنا میشیم.
Number: عددی هست که میخوایم ببینیم چه جایگاهی داره و چندمین عدد هست.
Ref: محدوده ای از اعداد که میخوایم رتبه بندی بین اونها انجام بشه. هم میتونه آرایه ای از اعداد باشه و هم یک رنج(Range)
Order: این آرگومان اختیاری (optional) است عدد 0 یا 1 رو میگیره. در واقع با این دو عدد مشخص میکنیم که منطق رتبه بندی چطور باشه؟
صفر: بصورت نزولی رتبه بندی انجام میشه. یعنی اعداد از بزرگ به کوچک در نظر گرفته میشه و بزرگترین عدد رتبه یک میگیره الی آخر.
یک: بصورت صعودی رتبه بندی انجام میشه. یعنی اعداد از کوچک به بزرگ در نظر گرفته میشه و کوچکترین عدد رتبه یک میگیره الی آخر.
اگر آرگومان سوم رو مشخص نکنیم اکسل بصورت پیشفرض، صفر در نظر میگیره و در واقع با منطق نزولی رتبه بندی رو انجام میده.
Rank.EQ: این تابع حالت پیشرفته تابع rank هست و از ورژن 2010 به اکسل اضافه شده. آرگومان ها مشابه با تابع rank هست، اما در خروجی با هم متفاوت هستن. به اینصورت که اگر چند عدد وجود داشته باشه که جایگاه یکسان داشته باشن، برای همه، بزرگترین رتبه در نظر گرفته میشه.
Rank.AVG: این تابع حالت پیشرفته تابع rank هست و از ورژن 2010 به اکسل اضافه شده. آرگومان ها مشابه با تابع rank هست، اما در خروجی با هم متفاوت هستن. به اینصورت که اگر چند عدد وجود داشته باشه که رتبه یکسان داشته باشن، خروجی این تابع میانگین رتبه ها خواهد بود.
4 نکته ای که باید راجع به تابع rank اکسل بدونید
- توابع رتبه بندی در اکسل فقط روی اعداد کار میکنن. اعداد منفی، مثبت، صفر، تاریخ و زمان. داده های غیر عددی در نظر گرفته نمیشن.
- توابع رتبه بندی، برای داده های تکراری یک رتبه یکسان رو نمایش میدن و به ازای داده های تکراری یک رتبه رو در نظر میگیرن. (به مثال اول دقت کنید)
- در ورژن 2010 توابع EQ و Rank.AVG به اکسل اضافه شدن. اما همچنان تابع Rank برای حفظ سازگاری با نسخه های قبلی در اکسل وجود داره. اما ممکنه د رآینده حذف بشه.
- اگر در مجموعه REF عددی پیدا نشه، خروجی تابع #N/A خواهد بود.
مثال اول- حالت ساده و اصلی تابع RANK
فرض کنید میخواهیم افراد رو بر اساس نمره کسب شده، رتبه بندی کنیم. هر سه تابع رتبه بندی رو در شکل 1 می بینید. به خروجی توابع در خصوص اعداد تکراری دقت کنید.
شکل 1- رتبه بندی با سه تابع رتبه بندی
چند نکته راجع به مثال اول:
- همونطور که مشاهده میکنید، برای داده های تکراری، رتبه یکسان تکرار شده.
- خروجی تابع RANK و EQ یکسان هست. در واقع تابع RANK میتونه کم کم حذف بشه.
- وقتی دو عدد رتبه 3 دارن، در واقع رتبه 3 و 4 اشغال شده. برای همین بعد از دو تا رتبه 3، رتبه بعدی 5 هست و ما اصلا رتبه 4 نداریم.
- خروجی تابع AVG رتبه 3.5 هست. چون بین عدد 3 و 4 (رتبه دو عدد مشابه) میانگین گرفته شده.
مثال دوم-کاربرد تابع RANK در حالت صعودی یا Order=1
در این حالت تابع، اعداد رو طوری رتبه بندی میکنه که بیشترین عدد رتبه کمتری بگیره.
مثلا میخواهیم دانش آموزان رو بر اساس ساعت ورود به مدرسه رتبه بندی کنیم. هر کس زودتر آمده، رتبه بهتر و هر کس دیرتر آمده رتبه بدتر نمایش داده بشه.
=RANK (B3 ; $B$3:$B$9 ; 1)
شکل 2- رتبه بندی دانش آموزان بصورت صعودی با تابع Rank اکسل
همونطور که میدونید داده های تاریخ و زمان، مقدار عددی هستند. پس تابع RANK براحتی میتونه کار رتبه بندی رو انجام بده.
مثال سوم- رتبه بندی داده های تکراری بصورت منحصربفرد
همونطور که ملاحظه کردید، توابع رتبه بندی برای داده های تکراری، رتبه یکسان نمایش میدهند. حالا اگر بخوایم مثلا دو عدد که رتبه 3 دارن رو بصورت 3 و 4 ببینیم چکار باید بکنیم؟
برای حل این موضوع باید تعداد عدد مورد نظر رو به رتبه خروجی اضافه کنیم. به شکل 3 دقت کنید:
=RANK (B3;$B$3:$B$9) + COUNTIF ($B$2:B2;B3)
شکل 3- رتبه بندی اعداد تکراری با تابع Rank اکسل
در مثال بالا، تابع Countif تعداد عدد مورد نظر رو در محدوده بالای سرش به خروجی RANK اضافه میکنه. در واقع فرمولی که در ردیف 8 نوشته شده به اینصورت عمل میکنه:
تعداد عدد 56 در محدوده B3:B7 شمرده میشه و نتیجه میشه 1. این مقدار به خروجی RANK که عدد 3 هست اضافه میشه و میشه 4.
مثال چهارم- رتبه بندی بر اساس شروط چندگانه
فرض کنید میخواید دانش آموزان رو بر اساس نمره (معیار اول) رتبه بندی کنید. هر چند دانش آموزی که نمره یکسان داشتن، بر اساس نمره انضباط (معیار دوم) رتبه بندی بشن.
به شکل 4 دقت کنید. دانش آموز E و F نمره یکسان 56 کسب کردن که رتبه هر دو 3 میشه. حالا میخوایم کسی که انضباط بهتری داره، رتبه 3 و کسی انضباطش پایینتره رتبه 4 بگیره. برای این کار از تابع Countifs استفاده میکنیم و شرط های مورد نظر رو اعمال میکنیم.
=RANK.EQ ( $B2; $B$2:$B$8)+COUNTIFS ( $B$2:$B$8;$B2;$C$2:$C$8; “>”&$C2)
شکل 4- رتبه بندی بر اساس شروط چندگانه با استفاده از تابع Rank اکسل
در واقع قسمت Countifs فرمول، میاد دو شرط رو بررسی میکنه و خروجی این تابع اضافه میشه به خروجی تابع رتبه بندی.
خب در این مقاله با توابع رتبه بندی آشنا شدیم. یکی از توابع پرکاربرد در تهیه داشبورد همین توابع رتبه بندی هستن که با ترفندها و روش های مختلف مور استفاده قرار میگیرند.
پبیشنهاد میکنم مقاله مربوط به تابع Small و تابع Large رو مطالعه کنید و تفاوت ها رو خوب درک کنید. سعی کنید این دو تابع رو با حالت های مختلف تابع Rank اکسل تطبیق بدید.
دانلود فایل آموزش رتبه بندی اعداد در اکسل
برای دانلود فایل این آموزش روی دکمه زیر کلیک کنید:
برچسب:تابع Rank, تابع Rank.EQ
39 نظر
سلام و درود
یک گزارش تولید محصولات هست که قراره بر حسب ستون بیشترین فروش و ستون کمترین موجودی انبار اولویت بندی بشه
ممنون میشم بفرمایید از چه طریقی می توان اولویت بندی را انجام داد تاهمزمان این دو پارامتر رو مورد سنجش قرار بده ؟ از سورت بندی با دو شرط استفاده شد که جواب منطقی نبود ممنونم
درود
وزن بدید به هر فاکتور و به یک شتخص برسید…. اونو اولویت بندی کنید
با سلام و احترام، چطور میتوان در یک مزایده برنده مزایده را در بین چندین نفر مشخص کرد. در یک سلول دیگر نام برنده و مبلغ پیشنهادی که از دیگران بالاتر است را نشان داد. سپاس
درود بر شما
میتونید max استفاده کنید و بیشترین قیمت رو تعیین کنید و بعد با match مکانشو پیدا کنید و بذارید داخل index که اسم شخص رو بهتون بده
مقالات مربوط به match و index رو داخل سایت مطالعه کنید
سلام وقت بخیر من یکسری داده دارم براشون نمودار پاراتو رو رسم کردم و حالا میخوام با توجه به قانون 20 80 آنهارا کلاس بندی ABC کنم و روی نمودار دایره نشان دهم چگونه این کلاس بندی را انجام دهم در اکسل
درود
سپاس
منظورم گرد کردن هوشمند هست. مثلا
8.5 نزدیک به 9 هست ولی من میخوام 8 بشه یا
1.4 که نزدیک به یک هست ولی میخوام بشه 2
چون دامنه عددها بین 1 تا 9 هست و نمیخوام عدد مشابه داشته باشم (مگر دو عدد مبنا یکسان باشند)
روش AHP رو یکم بهینه کردم ولی نمیخوام دستی عدد بدم یا فرمول نویسی کنم.
بازم از لطفتون سپاس گزارم.
درود
نهایتا میتونید یا فقط به پایین یا فقط به بالا گرد کنید
که بشه از این توابع استفاده کرد
ولی چیز یکه میخواید قاعده مشخصی نداره و با این توابع نمیشه
مگر اینکه الگوریتمی براش در بیارید و کدنویسی کنید براش
درود
در روش AHP وقتی میخواهیم یک ماتریس 4 در 4 رو رتبه دهی کنیم جواب گزینه ها به ترتیب مثلا:
1. گزینه a=2.185.
2. گزینه b=2.481.
3.گزینه c=1 .
4. گزینه d=9.
باشد چطور تعریف کنم که مقدار گرد شده به این شکل بشه:
گزینه a=2.
گزینه b=3.
گزینه c=1.
گزینه d=9.
آخه از ترکیب rank , countif , switch نشد.
راهی نیست بدون استفاده از VBA یا تابع شخصی این مشکل رو حل کنم؟!
فعلا فرصت برنامه نویسی رو ندارم و باید بدون کد نویسی باشه.
ممنون میشم راهنمایی کنید.
سپاس از آموزشهای ارزشمندتون.
درود
اگر فقط منظورتون گرد کردن عدد هست از توابع مخصوص گرد کردن استفاده کنید
سلام
عذر میخوام من تعداد 101 سطر و تعداد 8 سط به نام های که ردیف نام و نام خانوادگی مبلغ فیش واریزی مرحله اول مبلغ فیش واریزی مرحله دوم مبلغ فیش واریزی مرحله سوم دارم که هر مرحله با مبلغش رو برای این 101 نفر جدا مرتب سازی براساس تاریخ واریزی انجام دادم
العان میخوام بین این سه مرحله مبلغ واریزی برای این 101 نفر اولویت بندی کنم باید چیکار کنم ؟
درود
سوالتون واضح نیست
اگر منظورتون رتبه بندی هست، که خب تابعضو در همین مقاله مطالعه کردید.
مشکل رو بفرمایید کجاست.
سلام – دو عدد تکراری را میخوام با یک رتبه نشون بده ولی یکیشو 3 و دیگری را 4 نشون میده. چکار کنم ؟
سلام
از تابع RANK.EQ استفاده کنید.
سلام وقت بخیر
ببخشید یک ستون اعداد دارم که 0 و 1 و 2 داره مثلا، بعد میخوام اعداد رو اولویت بندی کنم که مثلا سلول بیشترین عدد اویت 1 بگیره و در ضمن سلول صفر هم جزو اولویت بندی ما نباشه،
ممنون میشم راهنمایی کنین
درود
سوال گنگه
اگه اعداد 1 و 2 هست فقط که رتبه اونها معلومه
اگر نه که باید منطق مشخص بشه
بصورت کلی میتونید با if صفر ها رو بذارید کنار و روی نتیجه رتبه بندی انجام بدید
سلام فرض کنید ما 6 استراتژی از جداول SWOT استخراج نموده ایم و با توزیع پرسشنامه و تکنیک فرایند تحلیل سلسله مراتب فازی و مقایسات زوجی با مقادیر کمی 1 تا 9 را انجام داده ایم . حال چگونه اولویت استراتژیها را بر اساس وزن AHP از طریق ماتریس QSPM حساب کنیم
سلام، مسئله شما بیشتر راه حل محاسباتی لازم داره تا اکسلی
قطعا با استفاده از روش هایی که ذکر کردید برای هر استراتژی یک امتیاز بدست آورده اید.
به صورت کلی عرض کنم با ترکیب توابع Match و Index میتونید استراتژی برتر رو انتخاب کنید.
با سلام. یه سوال داشتم. می خواستم ببینم میشه مثلا در یک کارنامه رتبه اول که مشخص شد، نام و نام خانوادگی شخص هم در کنارش نمایش داده بشه؟
اگر راهی هست لطفا راهنمایی کنید. ممنون.
با ترکیب match و index میشه
اما اگر داده ها تکراری باشن باید از فرمول نویسی آرایه ای استفاده کنید
سلام
من میخوام رتبه بندی کشورها رو انجام بدم که هر کدوم چندین امتیاز داره و اول باید امتیازها رو جمع کنه و بعد کشورها رو بر اساس امتیاز کلی رتبه بندی کنه برای این کار باید چیکار کنم؟
درود بر شما
همینکه فرمودید رو انجام بدید
اول جمع کنید
بعد رتبه بندی معمولی انجام بدید
سلام
فایل قابل دانلود نیست و در صورت دانلود باز نمیشه
آیا برای تاریخ نیز میشه Rank داشته باشیم؟
درود بر شما
شاید در دانلود مشکلی باشه. چون فایل مشکلی نداره
(حتما هم از آخرین نسخه RAR استفاده کنید
بله میشه اگر میلادی باشه(چون تاریخ در واقع عدد هست که از 1/1/1900 شمرده میشه)
با سلام
من یه فایل فروش دارم که میخوام روزانه ازش گزارش بگیرم که بیاد بر اساس آپدیت روزانه مقدار فروش ، درصد رضایتمندی و مقدار تقاضا، سطر مورد نظر رو با توجه به رتبه محصول به سطر بالاتر یا پایین تر جابجا کند.
مثل یه جدول رده بندی فوتبال که با تغییر تعداد برد یا باخت رتبه تیم عوض میشه.
درود بر شما
وقتی داده ها در یک شیت ذخیره شده باشه و گزارش در یک شیت دیگه.فرمول نویسی و گزارشگیری هم به درستی انجام شده باشه، به خواستتون میرسید. اما شیتهای جداگانه د رنظر بگیرید. نه اینکه بخواید روی دیتابیس و در همون شیت گزارش رو بسازید.
برای گزارش گیری هم بسته به شرایط، روش های مختلف وجود داره
pivot tablr
فمرول نویسی حرفه ای
پاور ها
بسته به نیاز و تسلط، از این روش ها برای تهیه گزارش و داشبوردتون استفاده کنید
با سلام مجدد
با چه فرمول و تابعی میشه اینکار انجام داد؟
یک تابع خاص و مشخص نیست
مجموعه ای از توابع، فرمول نویسی، ابزارها و …. باید استفاده بشه تا گزارش دربیاد. بسته به چین شداده ها و خروجی های مد نظر متفاوت هست
سلام
یک سوال داشتم خدمت شما ، در صورت امکان راهنمایی فرمایید .
فرض کنید فردی در کارگاهی کار میکند و ساعت ورود ایشان 7 صبح تا 4 بعد از ظهر است و همین فرد در هفته بعد ساعت 4 عصر ورود و ساعت یازده شب خروج دارد و در هفته سوم یازده شب تا 7 صبح ورود و خروج دارد .
با فرض اینکه روز از ساعت 6 صبح تا 10 شب است و شب از ساعت 10 شب تا 6 صبح ، چگونه می توان ساعت کارکرد روز و شب این فرد را از طریق اکسل جدا کرد ؟
ممنونم یونسی
درود بر شما
اول از همه باید منطق محاسبات ساعت در اکسل رو یاد بگیرید. برای این موضوع لینک زیر رو مطالعه کنید:
https://excelpedia.net/excel-time-calculation/
بعد مطابق با شرایط خودتون، با تابع if محاسبات رو انجام بدید
https://excelpedia.net/if-function/
سلام ممنون از آموزش خوبتون
من یک سوالی برام پیش اومده
من دوتا رتبه 1 دارم، و طبیعتا دیگه رتبه 2 رو نشون نمیده و بعدی از 3 هست، منتها چون دو عدد دوم هم برابرند، بجای اینکه جفتش رو 3 نشون بده چهار نشون میده، یعنی رتبه بالاتره نشون میده، راهی هست 3 رو نشون بده؟
درود بر شما
رتبه بندی که با تابع rank انجام میشه، طبق توضیحات شما بصورت زیر خواهد بود:
عدد رتبه
20 1
20 1
30 3
30 3
برای اینکه رتبه 2 رو نشون بده برای اعداد 30 چه باید کرد؟
درود
این مقاله رو مطالعه کنید
https://excelpedia.net/match-nth-large/
سلام
سلول Sدراکسل دارای اعدادی است که باخروف میباشدوحروف ازیک تابیست درهرستون متفاوت میباشد که بصورت روزانه دریافت میکنم که میخواهم درهروز که دریافت میکنم بصورت عدد باشه ضمنا 4روشی که گفته بودید امتحان کردم نتیجه ندادلطفا اگه مقدوره با تابعIF راهنمایی کنید .باتشکر
درود بر شما
سوال بسیار گنگ و نامفهوم هست
در سلولی که فرمت آن text هست (A1) دو عدد به این طریق 8*190 وارد شده ، من میخوام نتیجه حاصل ضرب این دو عدد رو در سلول A2 ببینم .چیکار کنم ؟ .ممنون
درود بر شما
یا باید با توابع متنی تفکیک و بعد ضرب کنید
یا اینکه از Evaluate و محدوده نامگذاری استفاده کنید. که نمونه اون در کانال تلگرامی اکسل پدیا موجود هست. سرچ کنید Evaluate
خواهشن سریع بخونید و جواب بدید
با سلام
سوالم مربوط به این بحث نمیشه ولی از اون جایی که پست مورد نظرم قدیمی بود گفتم شاید جواب ندید محبور شدم بخاطر ضروری و فوری بودن سوالم این جا بپرسم
سوالم اینه که
من میخوام اعداد a1 تا a 20 جمع بشه و وقتی که عدد a21 اضافه شد عدد a1 دیگه جمع نشه و اعداد a2 تا a21 جمع بشه یجور حلقه شرطی
ممنون میشم این جا جواب بدید یا به ایمیلم جواب بدید اگه جواب رو به ایمیل میفرستد همین جا اعلام کنید که من ایمیلم رو چک کنم
فقط خواهشن سریع بخونید و جواب بدید
درود بر شما
اگر منظور جمع زدن ده سلول آخر هست، از این فرمول استفاده کنید:
-آرایه ای- با ctrl+shift+enter ثبت کنید.
فرض اینه که داده ها در محدوده A1:A100 وارد میشن.
اگر ردیف خالی هم بین اعداد باشه این فرمول درست کار میکنه