جمع n عدد بزرگتر یا n عدد کوچکتر در یک محدوده

اگر از شما بخواهند مجموع ده فروش اول مشتریانتان را محاسبه کنید چکار میکنید؟ اگر از شما بخواهند مجموع تعداد ساعات بیشترین اضافه کاریها را محاسبه کنید چطور؟ اگر بخواهید بدانید میانگین ده فروش بزرگ چقدر است از چه فرمولی استفاده می کنید؟

ممکن است در نگاه اول کمی پیچیده بنظر بیاید اما با من همراه باشید تا ببینید چقدر ساده می توان به این سوالات پاسخ داد. فرض کنید جدولی مشابه با آنچه در زیر می بینید در اختیار داشته باشید:

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

راه اول: ترکیب فرمولهای SUMIFS و LARGE/SMALL

اول از همه میدانیم که میخواهیم بر اساس یک شرط مشخص مجموع یک محدوده را محاسبه کنیم پس باید از فرمول SUMIFS یا SUMIF استفاده کنیم.

دوم اینکه در شرط باید چه فرمولی بنویسیم که بتواند ۵ عدد بزرگتر را شناسایی کند؟

فرمول LARGE و فرمول SMALL:

این فرمولها از دو قسمت تشکیل شده است:

LARGE(array,k)= امین عدد بزرگ را می آوردK

SMALL(array,k)= امین عدد کوچک را می آوردK

حال برای شرط فرمول می توانیم پنجمین عدد بزرگ محدوده را با فرمول LARGE شناسایی کنیم و سپس با یک علامت بزگتر مساوی مساله مان را براحتی حل نماییم:

=SUMIFS($B$2:$B$13,B2:B13,”>=”&LARGE($B$2:$B$13,5))

اگر میخواستیم میانگین پنج فروش بزرگ را محاسبه کنیم از فرمول AVERAGEIFS استفاده میکردیم:

=AVERAGEIFS($B$2:$B$13,B2:B13,”>=”&LARGE($B$2:$B$13,5))

اگر میخواستیم مجموع سه فروش پایین را محاسبه کنیم:

=SUMIFS($B$2:$B$13,B2:B13,”<=”&SMALL($B$2:$B$13,5))

راه دوم: ترکیب SUM و LARGE/SMALL

میتوانیم بجای SUMIFS از ترکیب فرمولهای SUM و LARGE/SMALL استفاده کنیم.

مثلا میخواهیم مجموع سه فروش پایین را محاسبه کنیم:

 =SUM(SMALL(B2:B13,{1,2,3}))

راه سوم: فرض کنید میخواهیم بدانیم دومین فروش بزرگ شهر تهران چقدر است؟

بسیار ساده است فقط کافیست از ترکیب فرمولهای IF و LARGE استفاده کنید:

 =LARGE(IF($A$2:$A$13=”تهران”,$B$2:$B$13,” “),2)

نکته اضافی: فرض کنید از یک دیتابیس بزرگ یک گزارش تهیه کرده اید و از بزرگترین تا پنجمین فروش بزرگ و برتر را زیر هم مرتب کرده اید اما میخواهید بدانید هر یک از این فروشها مربوط به چه کسی است؟

مثلا در همین مثال ابتداً  اولین تا پنجمین فروش را با استفاده از فرمول LARGE محاسبه کرده و زیر هم مینویسیم و برای اینکه بتوانیم نام شهر مرتبط به هر فروش را نیز بدست بیاوریم از ترکیب فرمولهای INDEX و MATCH استفاده میکنیم:

دانلود فایل اکسل “جمع n عدد بزرگ در یک محدوده”