بدست آوردن Kامین عدد بزرگ با تابع Large
تابع Large اکسل
مرتب سازی داده ها در اکسل یکی از مسائل بسیار پر کاربرد به حساب میاد. مرتب کردن داده ها مخصوصا اعداد یکی از مسائل مهم در تهیه داشبورد هاست. چرا که عموما در داشبوردها، قسمتی رو برای نمایش بزرگترین ها، بیشترین ها، کمترین ها و … اختصاص میدیم. همونطور که قبلا توضیح داده شده، یکی از راه های مرتب کردن داده ها ابزار Sort هست. اما باید به این موضوع هم دقت داشته باشیم که ابزارها داینامیک نیستن و با تغییر داده های مرجع، نتیجه Sort خودبه خود تغییر نمیکنه و باید دوباره ابزار اجرا بشه. که البته این خاصیت همه ابزارها در اکسل هست و خیلی جاها هم کاربردهای خودشون رو دارن. در مقاله تابع Small رو آموزش دادیم و گفتیم که برای مرتب کردن داده ها از کوچیک به بزرگ میتونیم از این تابع استفاده کنیم. در این مقاله تابع Large اکسل رو با هم می بینیم. این تابع دقیقا برعکس تابع Small هست و Kامین عدد بزرگ رو بر میگردونه.
آرگومان های این تابع به شرح زیر است:
Array: محدوده اعداد که میخواهیم از بین آنها کوچکترین ها رو انتخاب کنیم. جنس این آرگومان Range هست.
K: K امین عدد بزرگ از محدوده مورد نظر. جنس این آرگومان عدد هست.
اگر بخوایم تعریف یک خطی از این تابع ارائه بدیم، باید بگیم که بین یک مجموعه عدد، Kامین عدد بزرگ رو بر میگردونه.
مثال: فرض کنید داده هایی مطابق شکل 1 داریم و میخوایم این داده ها رو از بزرگ به کوچک مرتب کنیم.
شکل 1- مرتب کردن اعداد از کوچک به بزرگ- تابع Large اکسل با ستون کمکی
همونطور که در شکل 1 نمایش داده شده، تابع Large بصورت زیر نوشته شده:
=LARGE($A$2:$A$13,C2)
تفسیر این تابع این هست که در محدوده A2:A13 اولین عدد بزرگ رو نمایش بده. (در سلول C2 نوشته شده 1 که یعنی اولین و چون تابع Small هست یعنی اولین عدد بزرگ).
علامت های $ هم بدلیل درگ کردن تابع گذاشته شده که برای مطالعه منطق و چگونگی این کار، حتما مقاله اصول فرمول نویسی حرفه ای در اکسل رو مطالعه کنید.
همونطور که در شکل 1 مشاهده کردید، برای تعیین K از سلول کمکی (C2:C13) استفاده کردیم. حالا برای اینکه از سلول کمکی استفاده نکنیم، میتونیم از تابع ROW() استفاده کنیم. برای مشاهده نحوه استفاده از این تابع به جای سلول کمکی مقاله مربوط به تابع Small رو مطالعه کنید.
حالا میخوایم یک مثال آرایه ای از کاربرد این تابع شرح بدیم:
فرض کنید مطابق شکل 2 داده های مربوط به فروش شعب مختلف یک فروشگاه رو داریم. میخوایم مجموع فروش 3 شعبه برتر رو محاسبه کنیم:
شکل 2- استفاده از تابع Large اکسل بصورت آرایه ای
همونطور که در شکل 2 نمایش داده شده، تابع استفاده شده برای محاسبه مجموع فروش سه شعبه برتر به صورت زیر می باشد:
=SUM(LARGE(B2:B13,{1,2,3}))
این فرمول بدون نیاز به سلول کمکی و بصورت آرایه ای محاسبات رو انجام میده (توجه داشته باشید که برای ثبت فرمول آرایه ای باید از ترکیب کلید Ctrl+shift+Enter استفاده بشه). این فرمول اول میاد سه عدد بزرگ رو پیدا میکنه و بعد روی اون مجموع حساب میکنه. برای درک بهتر، به تصویر زیر دقت کنید:
تو آموزش محاسبه مرحله به مرحله فرمول (دیباگ) در مورد نحوه ارزیابی و بررسی فرمول ها صحبت کردیم.
اگر از حالت آرایه ای استفاده نکنیم، باید از 3 سلول کمکی استفاده کنیم و سه عدد بزرگ رو مشابه مثال 1 بدست بیاریم و بعد سه سلول حاصل را جمع بزنیم. اما بصورت آرایه ای نیازی به سلول کمکی نیست.
برچسب:آرایه ای, تابع Large, متوسط
8 نظر
ممنون ، لطفاً بفرمایید د برخورد با مقادیری که مقدار یکسان دارند ما چطور میتونیم عددهای خاصی رو جایگزین کنیم.
مثلاً من دو ستون نام و نمره دارم و میخواهم اونها رنگ بندی کنم از بیشترین به کمترین ولی جایی که دو عدد یکسان هست افراد دارای نمره یکسان به ترتیب الفبا لیست بشن
ممنون میشم راهنمایی بفرمایید
درود
این مقاله رو مطالعه کنید
https://excelpedia.net/match-nth-large/
یا اینکه کل اعدادتون رو با یک عدد خیلی کوچیک منحصربفرد (مثل 0.000001*row()) جمع کنید که اثری روی مقدار نذاره و از طرفی غیر تکراری بشه، و بعد رتبه بندی انجام بدید
با سلام و تشکر از سایت خوبتون
یک سوالی داشتم. اگر داده های زیر را در دو ستون داشته باشیم چگونه میشه با یک فرمول در یک سلول، دومین جمع بزرگ محصول را نشان بدهد.(با فرمول دیگر هم نام محصول جلویش نشان داده بشه.
محصول الف 10
محصول الف 15
محصول ب 5
محصول ب 11
با توجه به اهمیت موضوع ممنون میشم پاسخ به من ایمیل(اطلاع رسانی) بشه. با سپاس
درود بر شما
اول جمع ها رو حساب کنید با sumif بعد با تابع large میتونید تعیین کنید
با عرض سلام
فرمول زیر رو هرجور که تایپ میکنمخطا میده چیکار باید بکنم؟
درود بر شما
احتمالا جداکننده آرگومان در سیستم شما چیز دیگری هست
خیلییییییییییی ممنونم بینظیر بود
با سلام
و کسب اجازه
برای اینکه در وارد کردن آرایه ها امکان داره کاربر به جای فشار دادن همزمان کلیدهای کنترل و شیفت و اینتر (cse)، فرمول رو به صورت معمولی ( فقط اینتر) وارد کنه و به نتیجه اشتباهی برسه.
پیشنهاد میشه برای جلوگیری از اشتباه در فرمول به جای sum از sumproduct استفاده بشه.
الان این فرمول که نوشتین بدون cse درست جواب میده ولی وقتی به جای {1,2,3} اگه از (1:3)row استفاده بشه، اونوقت sum معمولی درست جواب نمیده و باید به صورت cse وارد بشه.