برای درک بهتر تابع SUMPRODUCT در اکسل ، بهترین روش می تواند حل مثالها و تمرینهای مختلف باشد که در این آموزش رایگان توابع اکسل به همین موضوع خواهیم پرداخت .
مثال ساده تابع SUMPRODUCT در اکسل
فرض می کنیم که داده هایی در اکسل داریم مشابه تصویر زیر که در واقع یک ستون قیمت و یک ستون تعداد فروش می باشد ، اگر ما بخواهیم جمع کل فروش را داشته باشیم چکار باید بکنیم؟
اگر با تابع SUMPRODUCT آشنا نباشیم ، بهترین کار این است که یک ستون کمکی اضافه کنیم و مبلغ فروش کل هر ردیف را محاسبه کنیم و سپس جمع آن ستون کمکی را محاسبه کنیم .
ولی کافیست که دو محدوده داده تعداد و قیمت را در این تابع وارد کنیم و فرمول را به شکل زیر بنویسیم .
=SUMPRODUCT(J4:J7,I4:I7)
همانطور که میبییند ردیفهای J و I به صورت نظیر به نظیر در هم ضرب شده اند و با هم جمع شده اند ، این دقیقا همان کاریست که تابع SUMPRODUCT انجام میدهد . ولی کاربرد بیشتر این تابع زمانی مشخص می شود که بخواهیم برای ضربها شرط قرار دهیم که در ادامه انجام خواهیم داد .
تابع SUMPRODUCT به همراه شرط
مثال دیگری از این تابع بزنیم که در آن از شرط استفاده شود ، به ادامه توجه کنید اگر محدوده ای داشته باشیم که شامل نام کالا و قیمت واحد و تعداد فروش باشد و اینبار یک ستون دیگر هم داشته باشیم که مشخص کند که آیا تخفیف داشته است یا خیر و بخواهیم جمع فروش کالاهای با تخفیف را داشته باشیم.
| نام کالا | قیمت واحد | تعداد فروش | تخفیف ؟ |
| کالای شماره یک | 100 | 2 | بله |
| کالای شماره دو | 200 | 3 | خیر |
| کالای شماره سه | 300 | 2 | خیر |
| کالای شماره چهار | 400 | 2 | بله |
با فرض اینکه محدوده از A1 شروع شده باشد فرمول را باید به شکل زیر بنویسید .
=SUMPRODUCT((D2:D5="بله")*C2:C5*B2:B5)
در توضیح فرمول بالا باید گفت که از آرگومان اول تابع SUMPRODUCT استفاده شده است ، اگر ما بجای * مابین محاسبات از جداکننده فرمول استفاده کنیم باید محاسباتی که نتیجه آن TRUE یا FALSE استفاده کنیم باید از دو خط تیره قبل از آن استفاده کنیم که خروجی تبدیل به 0 یا 1 شود و فرمول به شکل زیر می شد .
=SUMPRODUCT(--(D2:D5="بله"),C2:C5,B2:B5)
مثال دیگری با شرطهای بیشتر
خب حالا مثالی بزنیم با شروط بیشتر ، فرض کنیم همان داده های قبلی را داریم با این تفاوت که ستون گروه محصول هم به آن اضافه شده و میخواهیم مجموع فروشهایی را داشته باشیم که گروه1 باشد و تعداد فروش بالای 5 و قیمت زیر 15 باشد.
=SUMPRODUCT((A2:A7="گروه1")*(B2:B7>5)*(C2:C7<15)*B2:B7*C2:C7)
مثال جذاب از تابع SUMPRODUCT
در این مثال فرض ما بر این است که یک ستون داریم که تاریخ و یک ستون هم داریم که میزان فروش در آن وارد شده است می خواهم با انتخاب نام ماه ، فروش همون ماه با هم جمع بشن و نمایش داده بشن .
البته به یاد داشته باشید که نمیخواهیم از ستون کمکی استفاده کنیم .
ابتدا با تابع MID شماره ماه را از تاریخ استخراج می کنیم و سپس با تابع CHOOSE نام ماه متناسب با آن شماره را به دست می آوریم و بعد از آن هم با SUMPRODUCT فرمول را تکمیل میکنیم.
