تابع SUMPRODUCT زیرمجموعه توابع ریاضی (Math & Trig) است. این تابع، محدوده یا آرایهها را با هم ضرب کرده و مجموع حاصل را برمیگرداند. SUMPRODUCT برای پردازش آرایهها طراحی شده است و ما را از کلیدهای ترکیبی «ctrl + shift + enter» بینیاز میکند.
هدف تابع
جمع حاصلضرب اعداد
خروجی
مجموع حاصلضرب مقادیر ارائه شده
ساختار
=SUMPRODUCT(array1,[array2],...)
آرگومانها
array1: اولین آرایه یا محدوده جهت ضرب در محدوده دیگر.
array2 [اختیاری]: دومین آرایه یا محدوده.
*تابع SUMPRODUCT حداکثر ۲۵۵ آرگومان میپذیرد.
کاربرد
معمولا برای جمع فاکتور، از ستون کمکی جهت ضرب تعداد و نرخ استفاده میشود و در نهایت اعداد حاصل با هم جمع شده تا مبلغ فاکتور مشخص گردد؛ اما با تابع SUMPRODUCT دیگر نیازی به ستون کمکی نداریم. برای روشنتر شدن مطلب، فرض کنید محدوده C5 تا C10 تعداد فروش و محدوده D5 تا D10 نرخ کالا را نشان میدهد. برای مشخص کردن مجموع فاکتور کافیست فرمول زیر را بنویسیم:
=SUMPRODUCT(C5:C10,D5:D10)
ترجمه: سلولهای متناظر دو محدوده را در هم ضرب کن و مجموع حاصلضربها را نشان بده.
علاوه بر کاربرد پیشفرض، از SUMPRODUCT میتوان برای شمارش و جمع شرطی نیز استفاده نمود. بعنوان مثال، میخواهیم تعداد فروش و جمع فروش در استان تهران را بدست آوریم.
در واقع SUMPRODUCT به صورت شرطی عمل کرده و سلولهای حاوی “تهران” را TRUE در نظر میگیرد:
| B | C | |
| 2 | استان | فروش |
| 3 | TRUE | 2,000,000 |
| 4 | TRUE | 3,500,000 |
| 5 | FALSE | 4,500,000 |
| 6 | FALSE | 7,800,000 |
| 7 | TRUE | 8,500,000 |
| 8 | FALSE | 5,500,000 |
و با دو علامت منها پشت آن، TRUE/FALSEها به ۰ و ۱ تبدیل شده و در فرمول اول تعداد و در فرمول دوم، جمع فروش را محاسبه میکند:
| B | C | ||
| 2 | استان | فروش | |
| 3 | 1 | 2,000,000 | 2,000,000 |
| 4 | 1 | 3,500,000 | 3,500,000 |
| 5 | 0 | 4,500,000 | 0 |
| 6 | 0 | 7,800,000 | 0 |
| 7 | 1 | 8,500,000 | 8,500,000 |
| 8 | 0 | 5,500,000 | 0 |
| تعداد | =3 | میزان فروش | =14,000,000 |
فرمول جمع فروش در استان تهران را میتوان به این صورت نیز نوشت:
=SUMPRODUCT((B3:B8="تهران")*C3:C8)
*علامت ضرب، TRUE/FALSEها را به ۰ و ۱ تبدیل کرده و دیگر نیازی به “–” نیست.
با این تابع میشود چند شرط را اعمال کرد که علامت «*» میتواند جایگزین تابع AND و علامت «+» جایگرین تابع OR شود. برای روشنتر شدن موضوع، به تصویر زیر توجه کنید.
در فرض اول تابع باید به دنبال مواردی باشد که هم استان آن “تهران” باشد و هم میزان فروش آن “بالای ۵میلیون” اما در فرض دوم، برقراری یک شرط نیز کفایت میکند. همچنین هنگامیکه بخواهیم سلولهای خالی را در شمارش و جمع نادیده بگیریم، فرمول را شکل زیر مینویسیم:
ورژن مورد نیاز
اکسل ۲۰۰۳ و بالاتر
پینوشت
*اگر تابع SUMPRODUCT بیش از یک آرگومان داشته باشد، میبایست ابعاد آرایهها یکسان باشد؛ در غیر اینصورت خطای #VALUE! رخ میدهد.
*زمانیکه SUMPRODUCT فقط یک آرگومان داشته باشد، عناصر آرایه با یکدیگر جمع میشوند.
*مقادیر غیرعددی در این تابع، ۰ درنظر گرفته میشوند.
*SUMPRODUCT از کاراکترهای wildcard پشتیبانی نمیکند.
2 در مورد “آموزش تابع SUMPRODUCT در اکسل”
برای جمع شرطی بهتر نیست از sumif استفاده کرد؟
تابع sumifs از منطق and پیروی میکنه و تو این شرایط هم راحتتره و هم سریعتر. ولی زمانیکه منطق شرطمون or باشه فقط میتونیم از تابع sumproduct استفاده کنیم.