سه شنبه , ۳ مرداد ۱۳۹۶
صفحه نخست / توسعه مهارتهای اکسل / فرمولهای پیشرفته و پرکاربرد / جمع n عدد بزرگتر یا n عدد کوچکتر در یک محدوده

جمع n عدد بزرگتر یا 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 عدد اول یا آخر در یک محدوده

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

۲ دیدگاه

  1. مصطفی طالبی

    سلام

    ضمن تشکر فرآوان از مطالب کاربردی و مفیدی که تا کنون قرار داده اید خواهش می کنم فایل اکسل این مطلب را هم قراربدهید. “دانلود فایل اکسل “جمع n عدد بزرگ در یک محدوده” فاقد لینک می باشد.

    با احترام

  2. ممنون از مطالبی که گذاشتین
    سوالی که مطرح است این است که اگر دو شهر فروشه یکسانی داشته باشن با فرمولهMatch/index دومین شعبه نمایش داده نمی شود.
    برای این؛ راه حلی وجود داره؟

پاسخ دهید

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

ده + هجده =

Time limit is exhausted. Please reload CAPTCHA.