کریستین دارای محدوده 18 سلولی است که حاوی مقادیر هستند. او می‌خواهد مجموع 12 مقدار بزرگ را در آن محدوده پیدا کند و به این فکر می‌کند که چگونه این کار را انجام دهد.
راه های مختلفی برای حل این مشکل وجود دارد. برای مثال می‌توانید مقادیر را فیلتر کنید تا فقط دوازده مقدار بالا را داشته باشید و سپس آنها را جمع کنید. روش‌های دیگر شامل استفاده از ستون‌های اضافی برای ذخیره مقادیر میانی است، اما من فرض می‌کنم که شما رویکردی را ترجیح می‌دهید که از ستون‌های اضافی استفاده نمی‌کند.
برای شروع، فرض کنید محدوده 18 سلولی شما A1:A18 است. برای یافتن بزرگترین مقادیر می توانید از تابع LARGE استفاده کنید. به عنوان مثال، با استفاده از این فرمول، دومین مقدار بزرگ در محدوده پیدا می شود:
=LARGE(A1:A18,2)
این پارامتر دوم توابع است که مشخص می کند به ترتیب کدام مقدار را می خواهید. بنابراین، شما می توانید با استفاده از فرمولی مانند این، مجموع 12 مقدار بزرگ را پیدا کنید:
=LARGE(A1:A18,1)+LARGE(A1:A18,2)+LARGE(A1:A18,3)+LARGE(A1:A18,4)
+LARGE(A1:A18,5)+LARGE(A1:A18,6)+LARGE(A1:A18,7)+LARGE(A1:A18,8)
+LARGE(A1:A18,9)+LARGE(A1:A18,10)+LARGE(A1:A18,11)+LARGE(A1:A18,12)
با این حال، فرمول های کوتاه تری وجود دارد که می توانید از آنها برای انجام کار استفاده کنید. به عنوان مثال، شما می توانید به سادگی شش مقدار کوچک را از مجموع محدوده کم کنید، به این ترتیب:
=SUM(A1:A18)-SMALL(A1:A18,1)-SMALL(A1:A18,2)-SMALL(A1:A18,3)
-SMALL(A1:A18,4)-SMALL(A1:A18,5)-SMALL(A1:A18,6)
همچنین در صورت تمایل می‌توانید از تابع SUMIF برای مقایسه مقادیر استفاده کنید و تنها در صورتی که معیاری که مشخص کرده‌اید برآورده شود، آنها را جمع کنید. به عنوان مثال، این دو فرمول را در نظر بگیرید:
=SUMIF(A1:A18,">="&LARGE(A1:A18,12))
=SUMIF(A1:A18,">"&SMALL(A1:A18,6))
فرمول اول تمام مقادیری را که بزرگتر یا مساوی دوازدهمین مقدار بزرگ در محدوده هستند جمع می کند. دومی در اثر مشابه است. تمام مقادیر بزرگتر از ششمین مقدار کوچک را جمع می کند.
اگر مقادیر تکراری در "مرز" تعیین شده وجود نداشته باشد، این دو فرمول عالی کار می کنند. با این حال، اگر بیش از یک مقدار وجود داشته باشد که واجد شرایط دوازدهم بزرگ یا ششمین مقدار باشد، فرمول ها مبالغ مورد انتظار شما را برمی گردانند. در فرمول اول مجموع خیلی بزرگ خواهد بود (زیرا همه مقادیر تکراری به جمع اضافه می شوند) و فرمول دوم مجموع بسیار کوچک خواهد بود (زیرا همه مقادیر تکراری از جمع حذف می شوند).
راه حل این است که یا به یکی از فرمول های قبلی برگردید (آنهایی که از SUMIF استفاده نمی کنند)، یا فرمول SUMIF را طوری تغییر دهید که امکان مقادیر تکراری را در نظر بگیرد:
=SUMIF(A1:A18,">"&LARGE(A1:A18,12))+LARGE(A1:A18,12)
*(12-COUNTIF(A1:A18,">"&LARGE(A1:A18,12)))
شاید بهترین فرمول برای به دست آوردن نتیجه دلخواه، استفاده قبلی از تابع LARGE را تغییر دهد:
=SUM(LARGE(A1:A18,{1,2,3,4,5,6,7,8,9,10,11,12}))
این فرمول از یک آرایه (قسمت درون پرانتز) استفاده می کند، اما یک فرمول آرایه نیست. کاری که انجام می دهد این است که از آرایه به عنوان دومین پارامتر تابع LARGE استفاده می کند، بنابراین تمام دوازده مقدار بزرگ را برمی گرداند. سپس اینها جمع می شوند و یک مقدار واحد برگردانده می شود.
