تابع SUMPRODUCT در اکسل

برای محاسبه مجموع حاصل‌ضرب اعداد متناظر (مقابل یکدیگر) یک یا چند محدوده می‌توانید از تابع قدرتمند SUMPRODUCT در اکسل استفاده کنید.

نحوه استفاده از تابع SUMPRODUCT در اکسل

به عنوان مثال استفاده از تابع SUMPRODUCT جهت محاسبه مبلغ کل که حاصل از مجموع حاصل‌ضرب قیمت در تعداد هر محصول است، به دست خواهد آمد:

توضیحات: تابع SUMPRODUCT محاسبات فوق را بصورت زیر انجام می‌دهد:

(1x2000) + (2x1500) + (2x3000) + (4x100) = 11400

1	(1x2000) + (2x1500) + (2x3000) + (4x100) = 11400

نکات مهم به هنگام استفاده از تابع SUMPRODUCT در اکسل

نکته شماره 1: به هنگام استفاده از تابع SUMPRODUCT محدوده‌های ورودی تابع باید ابعاد یکسانی نسبت به یکدیگر داشته باشند. در غیر این صورت با خطای #VALUE! در اکسل روبرو خواهید شد:

نمایش خطای #VALUE!:

نکته شماره 2: تابع SUMPRODUCT ورودی‌های غیرعددی را 0 درنظر می‌گیرد:

نکته شماره 3: اگر تنها یک محدوده را به عنوان ورودی برای تابع SUMPRODUCT درنظر بگیرید، نتیجه نهایی تابع SUMPRODUCT و تابع SUM یکسان خواهد بود:

از تابع SUMPRODUCT پیشرفته استفاده کنید

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

مثال شماره 1: تابع COUNTIF در اکسل تعداد سلول‌های حاوی عبارت Present را شمارش می‌کند:

حال تابع SUMPRODUCT نیز می‌تواند دقیقا همین نتیجه را به عنوان جایگزین تابع COUNTIF در اکسل محاسبه کند:

توضیحات: عبارت –(A1:A7=”Present”) در تابع SUMPRODUCT به آرایه زیر تبدیل خواهد شد:

--{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE}

1	--{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE}

علامت –- (دو تا منفی یا خط تیره)، مقدار False را به عدد 0 و مقدار TRUE را به عدد 1 تبدیل می کند. از این رو خواهیم داشت:

{1,0,1,1,1,0,0}

1	{1,0,1,1,1,0,0}

در ادامه مقادیر فوق به عنوان ورودی برای تابع SUMPRODUCT خواهند بود که مجموع آنها عدد 2 خواهد شد.

مثال شماره 2: تابع COUNTIF در مثالی که در ادامه آورده شده است تعداد دقیق سلول‌هایی که حاوی کارکتر star + 1 هستند را شمارش می‌کند. قابل توجه شما علاقه‌مندان، علامت سوال (؟) به این معنا است که بعد از عبارت Star دقیقا یک کارکتر وجود داشته باشد:

درحالیکه استفاده از کارکترهای ؟ و * در تابع SUMPRODUCT امکان‌پذیر نیست:

مثال شماره 3: فرمول آرایه‌ای که در ادامه آورده شده است، تعداد کاراکترهای یک محدوده را شمارش می‌کند:

نکته: درنظر داشته باشید، به منظور استفاده از فرمول‌های آرایه‌ای در اکسل می‌بایست کلید ترکیبی Ctrl+Shift+Enter را جایگزین کلید Enter پس از نوشتن فرمول نمایید. البته در اکسل نسخه 365 و اکسل نسخه 2021 دیگر نیاز به فشردن این کلید ترکیبی Ctrl+Shift+Enter نخواهد داشت. (آشنایی با فرمول‌نویسی آرایه‌ای در اکسل)

درحالیکه تابع SUMPRODUCT نیز می‌تواند بدو نیاز به فشردن کلید ترکیبی Ctrl+Shift+Enter دقیقا به همان نتیجه دست یابد:

توضیحات: آرایه {5;4;2;5} تعداد کارکترهای هرسلول که حاصل از فرمول‌ آرایه‌ای LEN(A:A4) است، می‌باشد از این رو جمع این آرایه در تابع SUM برابر با عدد 16 است. به عنوان نکته پایانی این بخش می‌توان اینگونه گفت که در تابع SUMPRODUCT نیاز به فشردن کلید ترکیبی Ctrl+Shift+Enter به منظور انجام محاسبات آرایه‌ای وجود ندارد.

مثال شماره 4: فرمول آرایه‌ای زیر فروش سال 2023 را نمایش می دهد:

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

توضیحات: نتیجه آرایه YEAR(A1:A5)=2023 بصورت زیر خواهد بود:

{TRUE,FALSE,FALSE,TRUE,FALSE}

1	{TRUE,FALSE,FALSE,TRUE,FALSE}

در ادامه نتیجه آرایه (YEAR(A1:A5)=2018)*B1:B5 بصورت زیر خواهد بود:

{TRUE,FALSE,FALSE,TRUE,FALSE}*{5,6,3,10,5}= {5,0,0,10,0}

1	{TRUE,FALSE,FALSE,TRUE,FALSE}*{5,6,3,10,5}= {5,0,0,10,0}

که حاصل‌جمع آرایه فوق در تابع SUMPRODUCT عدد 15 خواهد بود.

نکته: در محاسبات اکسل مقدار TRUE برابر با عدد 1 و مقدار FALSE برابر با عدد 0 درنظر گرفته خواهد شد. از این رو حاصل‌ضرب آرایه {TRUE,FALSE,FALSE,TRUE,FALSE} در {5,6,3,10,5} برابر است با آرایه {5,0,0,10,0}.