معرفی تابع sumproduct اکسل

اگر در اکسل بخواهیم حاصل‌ضرب چند مقدار را محاسبه کنیم و سپس نتایج به‌دست‌آمده را با هم جمع کنیم، یکی از بهترین گزینه‌ها استفاده از تابع SUMPRODUCT است.

این تابع در بسیاری از فایل‌های فروش، حسابداری، انبار و گزارش‌گیری کاربرد دارد و حتی می‌توان از آن برای انجام برخی محاسبات شرطی بدون استفاده مستقیم از توابعی مانند SUMIFS نیز استفاده کرد.

نام تابع از ترکیب دو مفهوم ساخته شده است:

SUM + PRODUCT

یعنی ابتدا مقادیر متناظر را در یکدیگر ضرب می‌کند و سپس حاصل‌ضرب‌ها را با هم جمع می‌کند.

این تابع اعداد موجود در آرایه های مختلف را در هم ضرب می‌کند و مجموع آنها را برمی گرداند. فقط ذکر چند نکته در مورد این تابع مهم است.

-1تمام آرایه ها در تابع sumproduct می بایست از تعداد سطر و ستون یکسان برخوردار باشد وگرنه با خطای !VALUE# مواجه خواهید شد.

-2 اگر هرکدام از آرایه ها، دارای مقادیر غیر عددی باشند به عنوان صفــر در نظر گرفته می‌شود.

-3 این تابع از کاراکتر های wildcard پشتیبانی نمی کند.

ساختار کلی تابع به شکل زیر است:

= SUMPRODUCT(ARRAY1,[ARRAY2],[ARRAY3],…)

در این فرمول، array به محدوده یا آرایه‌ای از مقادیر اشاره دارد که قرار است در محاسبه استفاده شوند.

اگر دو محدوده داشته باشیم، اکسل مقادیر متناظر آنها را در یکدیگر ضرب کرده و سپس حاصل‌ضرب‌ها را جمع می‌کند.

یک مثال ساده از SUMPRODUCT

فرض کنید در یک جدول فروش، تعداد فروش و قیمت هر محصول را داریم:

محصول	تعداد	قیمت واحد
محصول A	2	100000
محصول B	3	150000
محصول C	4	200000

اگر بخواهیم مبلغ کل فروش را محاسبه کنیم، می‌توانیم ابتدا برای هر ردیف تعداد را در قیمت ضرب کنیم و سپس نتایج را جمع کنیم.

روش معمول می‌تواند استفاده از یک ستون محاسباتی باشد:

=B2*C2

و سپس جمع کردن نتایج.

اما با SUMPRODUCT می‌توانیم کل محاسبه را با یک فرمول انجام دهیم:

=SUMPRODUCT(B2:B4,C2:C4)

اکسل محاسبه زیر را انجام می‌دهد:

(2 × 100000) + (3 × 150000) + (4 × 200000)

نتیجه:

1,250,000

این یکی از کاربردهای بسیار مهم تابع SUMPRODUCT در اکسل است.

SUMPRODUCT برای محاسبه مبلغ کل فروش

فرض کنید یک جدول فروش بزرگ داریم که ستون B تعداد و ستون C قیمت واحد را شامل می‌شود.

به جای ایجاد یک ستون جدید برای محاسبه مبلغ هر ردیف، می‌توانیم مستقیماً بنویسیم:

=SUMPRODUCT(B2:B100,C2:C100)

این فرمول تعداد هر ردیف را در قیمت همان ردیف ضرب کرده و تمام مبالغ را با هم جمع می‌کند.

در نتیجه، بدون نیاز به ستون محاسباتی اضافی، می‌توانیم مجموع مبلغ فروش را به دست آوریم.

این ویژگی در فایل‌های گزارش‌گیری و داشبوردهای اکسل بسیار کاربردی است.

SUMPRODUCT با چند آرایه

تابع SUMPRODUCT فقط محدود به دو محدوده نیست.

برای مثال اگر سه محدوده داشته باشیم:

تعداد
قیمت
ضریب

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

=SUMPRODUCT(B2:B10,C2:C10,D2:D10)

اکسل در هر ردیف مقادیر متناظر را در یکدیگر ضرب کرده و سپس مجموع تمام نتایج را محاسبه می‌کند.

به عنوان مثال:

(تعداد × قیمت × ضریب) + …

این قابلیت باعث می‌شود SUMPRODUCT برای محاسبات چندمرحله‌ای بسیار مفید باشد.

استفاده از SUMPRODUCT برای محاسبات شرطی

یکی از ویژگی‌های جذاب SUMPRODUCT امکان استفاده از شرط‌ها در محاسبات است.

فرض کنید جدولی از فروش محصولات داریم و می‌خواهیم فقط فروش مربوط به محصول لپ‌تاپ را محاسبه کنیم.

محصول	تعداد	قیمت
لپ‌تاپ	2	30000000
مانیتور	3	8000000
لپ‌تاپ	1	32000000
موس	5	500000

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

=SUMPRODUCT((A2:A5="لپ‌تاپ")*B2:B5*C2:C5)

در این فرمول ابتدا بررسی می‌شود که کدام ردیف‌ها مربوط به لپ‌تاپ هستند و سپس فقط تعداد و قیمت همان ردیف‌ها در محاسبه شرکت می‌کنند.

این روش یکی از کاربردهای حرفه‌ای‌تر SUMPRODUCT است.

SUMPRODUCT برای شمارش شرطی

جالب است بدانید که می‌توان از SUMPRODUCT برای شمارش سلول‌های دارای یک شرط مشخص نیز استفاده کرد.

برای مثال اگر بخواهیم تعداد ردیف‌هایی را که در ستون A عبارت «لپ‌تاپ» دارند بشماریم:

=SUMPRODUCT(--(A2:A100="لپ‌تاپ"))

در اینجا عبارت شرطی:

(A2:A100="لپ‌تاپ")

برای هر ردیف نتیجه TRUE یا FALSE ایجاد می‌کند و -- این مقادیر منطقی را به 1 و 0 تبدیل می‌کند.

در نهایت SUMPRODUCT آنها را با هم جمع می‌کند.

شمارش با دو شرط

می‌توان چند شرط را نیز هم‌زمان در SUMPRODUCT استفاده کرد.

فرض کنید می‌خواهیم تعداد فروش‌های مربوط به لپ‌تاپ در فروردین را محاسبه کنیم:

=SUMPRODUCT((A2:A100="لپ‌تاپ")*(B2:B100="فروردین"))

در این حالت فقط ردیف‌هایی که هر دو شرط را داشته باشند، مقدار 1 دریافت می‌کنند.

این تکنیک برای تحلیل داده‌ها بسیار کاربردی است.

SUMPRODUCT برای محاسبه میانگین وزنی

یکی دیگر از کاربردهای مهم این تابع، محاسبه میانگین وزنی است.

فرض کنید سه محصول با قیمت‌ها و تعداد فروش متفاوت داریم:

محصول	قیمت	تعداد
A	100	2
B	150	3
C	200	5

برای محاسبه میانگین وزنی قیمت می‌توانیم از رابطه زیر استفاده کنیم:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

در این فرمول، ابتدا قیمت هر محصول در تعداد آن ضرب می‌شود، سپس مجموع آنها بر مجموع تعداد تقسیم می‌شود.

این روش در تحلیل قیمت، فروش، موجودی و بسیاری از گزارش‌های مدیریتی کاربرد دارد.

تفاوت SUMPRODUCT با SUM

تابع SUM فقط برای جمع کردن مقادیر استفاده می‌شود:

=SUM(B2:B10)

اما SUMPRODUCT می‌تواند قبل از جمع کردن، مقادیر متناظر را در یکدیگر ضرب کند:

=SUMPRODUCT(B2:B10,C2:C10)

بنابراین:

SUM = جمع کردن مقادیر

SUMPRODUCT = ضرب مقادیر متناظر و سپس جمع کردن نتایج

یک نکته مهم درباره محدوده‌ها

محدوده‌هایی که در SUMPRODUCT استفاده می‌شوند، باید از نظر تعداد ردیف و ستون با یکدیگر سازگار باشند.

برای مثال، استفاده از:

=SUMPRODUCT(A2:A10,B2:B20)

مناسب نیست؛ زیرا دو محدوده تعداد ردیف یکسانی ندارند.

بهتر است محدوده‌ها مانند مثال زیر هم‌اندازه باشند:

=SUMPRODUCT(A2:A10,B2:B10)

جمع‌بندی

تابع SUMPRODUCT یکی از توابع بسیار کاربردی اکسل برای انجام محاسباتی است که در آنها باید مقادیر متناظر را در یکدیگر ضرب و سپس نتایج را جمع کنیم.

ساختار اصلی آن:

=SUMPRODUCT(array1,array2)

است.

از این تابع می‌توان برای موارد مختلفی استفاده کرد، از جمله:

محاسبه مبلغ کل فروش
محاسبه تعداد × قیمت
محاسبات چندآرایه‌ای
شمارش شرطی
محاسبات چندشرطی
محاسبه میانگین وزنی
تحلیل داده‌های فروش و انبار
انجام محاسبات بدون ایجاد ستون کمکی

اگر با اکسل در زمینه حسابداری، فروش، انبار یا گزارش‌گیری کار می‌کنید، یادگیری SUMPRODUCT می‌تواند به شما کمک کند بعضی از فرمول‌های طولانی را با یک فرمول ساده‌تر انجام دهید.