تابع SUMPRODUCT ، محدودهها یا آرایهها را در هم ضرب و مجموع حاصلضربها را بر میگرداند. شاید پیچیده و بیمعنی به نظر برسد، اما SUMPRODUCT یک تابع فوقالعاده همهکاره است که میتواند برای شمارش و جمع مانند توابع COUNTIFS یا SUMIFS استفاده شود، اما با انعطافپذیری بیشتر.
از کجا شروع کنم
هدف تابع
ضرب، سپس جمع آرایهها
خروجی تابع
نتیجه ضرب و جمع آرایهها
ترکیب عمومی تابع
= SUMPRODUCT ( array1 , [array2] , ... )
ورودی تابع (مولفهها)
array1 – اولین آرایه یا محدوده برای ضرب، سپس جمع.
array2 – [اختیاری] دومین آرایه یا محدوده برای ضرب، سپس جمع
نحوه استفاده از تابع
تابع SUMPRODUCT آرایهها را در هم ضرب میکند و حاصلجمع ضربها را بر میگرداند. اگر تنها یک آرایه ارائه شود، SUMPRODUCT عناصر موجود در آرایه را جمع میکند. تا 30 محدوده یا آرایه را میتوان به تابع ارائه داد.
وقتی برای اولین بار با SUMPRODUCT مواجه میشوید، ممکن است کسلکننده، پیچیده و حتی بیمعنی به نظر برسد. اما SUMPRODUCT یک تابع شگفتانگیز و همهکاره با کاربردهای فراوان است. از آنجایی که با آرایهها به خوبی کار میکند، میتوانید از آن برای پردازش محدوده سلولها به روشهای هوشمندانه و پیشرفته استفاده کنید.
ویژگیهای کلیدی
- بهصورت پیشفرض در نسخههای قدیمیتر اکسل با آرایهها کار میکند (نیازی به Ctrl+Shift+Enter نیست)
- میتواند منطق پیچیده توابع AND / OR را با استفاده از جبر بولی اعمال کند
- برای محاسبه جمعهای شرطی ، شمارشها و میانگینها کار میکند (میتواند جایگزین SUMIFS ، COUNTIFS ، AVERAGEIFS شود)
- بهطور خودکار مقادیر غیرعددی را به عنوان صفر در نظر میگیرد (مانند تابع SUM)
- میتواند مستقیماً از توابع دیگری مانند LEN ، ISBLANK ، ISTEXT، ISERROR و غیره استفاده کند
- در تمام نسخههای اکسل کار میکند
مثال کلاسیک SUMPRODUCT
این مثال نشان میدهد که چگونه میتوانید یک محاسبه را مستقیما و بدون ستون کمکی انجام دهید.
برای مثال، در برگه زیر، میتوانید از SUMPRODUCT برای بدست آوردن مجموع تمام اعداد ستون G بدون استفاده از مقادیر ستون G استفاده کنید.
برای انجام این محاسبه، SUMPRODUCT مستقیماً از مقادیر ستونهای F و E به صورت زیر استفاده میکند:
= SUMPRODUCT ( E3:E13 , F3:F13 ) // مقدار 8847 بازگردانده میشود
نتیجه مشابه جمع تمام مقادیر ستون F است. عملکرد فرمول به این صورت است:
= SUMPRODUCT ( E3:E13 , F3:F13 )
= SUMPRODUCT ( { 87; 81;75;90;65;88;69;83;82;79;85 } , { 8;10;11;9;8;10;11;9;18;6;10 } )
= SUMPRODUCT ( { 696;810;825;810;520;880;759;747;1476;474;850 } )
= 8847
این عملکرد SUMPRODUCT میتواند مفید باشد، به خصوص زمانی که با توجه به اندازه جدول گزارش جایی (یا نیازی) برای یک ستون کمکی با یک محاسبه میانی وجود ندارد. با این حال، رایجترین کاربرد SUMPRODUCT اعمال منطق شرطی در موقعیتهایی است که نیازبه انعطافپذیری بیشتری نسبت به توابعی مانند SUMIFS و COUNTIFS وجود دارد.
SUMPRODUCT برای جمع شرطی
کاربرد معمول تابع SUMPRODUCT محاسبه جمعهای شرطی است، دقیقاً مانند استفاده از تابعی مانند SUMIFS. در برگه زیر SUMPRODUCT برای محاسبه جمعهای شرطی با چهار فرمول جداگانه استفاده شده است:
= SUMPRODUCT ( - - ( C3:C13 = "قرمز" ) , G3:G13 ) // قرمز
= SUMPRODUCT ( - - ( D3:B13 = "تهران" ) , - - ( C3:C13 = "قرمز" ) , G3:G13 ) // تهران و قرمز
= SUMPRODUCT ( - - ( D3:D13 = "تهران" ) , - - ( C3:C13 = "آبی" ) , G3:G13 ) // تهران و آبی
= SUMPRODUCT ( - - ( C3:C13 <> "قرمز" ) , G3:G13 ) // قرمز نباشد
نتایج در سلولهای J3، J4، J5 و J6 قابل مشاهده هستند.
تابع SUMPRODUCT برای جمع و شمارش شرطی
تابع SUMPRODUCT میتواند برای جمع یا شمارش شرطی استفاده شود. برای نشان دادن نحوه کار این تابع ، به مثال بسیار ساده زیر توجه کنید. فرض کنید تعدادی داده در محدوده A2:B6 داریم که استان در ستون A و فروش در ستون B درج شده است:
با استفاده از SUMPRODUCT میتوانید کل فروش تهران را با این فرمول جمع کنید:
= SUMPRODUCT (- - ( A1:A6 = "تهران" ) , B1:B6 )
همچنین میتوانید کل فروش تهران را با این فرمول نیز محاسبه کنید:
= SUMPRODUCT ( - - (A1:A6 = "تهران" ) )
توجه داشته باشید که منطق شرطی ( "تهران" A1:A6= ) - - را از فرمول اول حفظ و آرایه دوم (فروش) را حذف کردهایم. این ایده اصلی شمارش با SUMPRODUCT است. شرط باقی میماند، اما آرایه دوم حذف میشود. با این حال، برای اینکه این کار عملی شود، به روشی نیاز داریم تا نتایج درست و نادرست را که شرط اعمال شده ایجاد میکند، به یکها و صفرها تبدیل کنیم. در ادامه به این موضوع خواهیم پرداخت.
SUMPRODUCT با منفی دوگانه (- -)
فرمولهای فوق از منفی دوگانه (- -) برای درست کار کردن منطق شرطی استفاده میکنند. برای درک اینکه چرا این امر ضروری است، بیایید دقیقاً بررسی کنیم که هنگام پردازش مثال فوق توسط SUMPRODUCT چه اتفاقی میافتد. وقتی اکسل عبارت ( "تهران" A1:A6= ) را ارزیابی میکند، بر اساس سلولهایی که با "تهران" مطابقت دارند، آرایهای از مقادیر TRUE و FALSE ایجاد میکند. در اینجا نحوه نمایش اولیه دو آرایه را مشاهده میکنید:
آرایه اول شامل TRUE / FALSE از نتایج شرط اعمال شده است، در حالی که آرایه دوم شامل مقادیر فروش مربوطه است. مشکل این است که SUMPRODUCT باید این آرایهها را در هم ضرب کند، اما مقادیر خام TRUE و FALSE را نمیتوان مستقیماً استفاده کرد زیرا آنها به عنوان صفر در نظر گرفته شده و باعث میشود نتیجه کل محاسبه صفر باشد.
اینجاست که منفی دوگانه مهم میشود. منفی دوگانه یکی از چندین روش برای تبدیل مقادیر TRUE و FALSE به معادلهای عددی آنها است: TRUE میشود ۱ و FALSE میشود ۰. این تبدیل عملیات منطقی بولی را در فرمول فعال میکند. پس از اعمال منفی دوگانه، نحوه کار جدول بالا به این صورت است:
حالا تابع SUMPRODUCT میتواند محاسبه را با موفقیت انجام دهد. به زبان آرایه، فرمول به صورت زیر ارزیابی میشود:
= SUMPRODUCT ( { 850,474,1476,747,759,880 } , { 0,0,0,1,1,0 } )
تابع SUMPRODUCT عناصر متناظر هر دو آرایه را در هم ضرب و سپس نتیجه را با هم جمع میکند:
= SUMPRODUCT ( { 0 ,0 , 0,747,759 , 0 } ) // مقدار 1506 بازگردانده میشود
همین اصل منفی دوگانه در مورد مثال شمارش شرطی نیز صدق میکند. به خاطر داشته باشید که میتوانیم تعداد فروشهای تهران را با استفاده از (( "تهران"= SUMPRODUCT ( - - ( A2:A6 = بشماریم. پس از اعمال منفی دوگانه، آرایه {0,0,0,1,1,0} را بدست میآوریم. از آنجایی که فقط یک آرایه را به SUMPRODUCT ارائه میدهیم (آرایه دومی برای ضرب در آن نداریم) SUMPRODUCT به سادگی مقادیر موجود در این آرایه را جمع میکند: 0+0+0+1+1+0=2 که نشان دهنده دو ردیفی است که در آنها استان برابر با "تهران" است. منفی دوگانه ضروری است زیرا بدون آن تابع {FALSE,FALSE,FALSE,TRUE ,TRUE, FALSE } را دریافت و با این مقادیر منطقی به عنوان صفر رفتار میکند و به جای تعداد صحیح 0 را برمیگرداند.
SUMPRODUCT با منطق تابع OR
همچنین میتوان از تابع SUMPRODUCT براساس منطق تابع OR استفاده کرد، یعنی اگر این یا آن باشد، جمع کند. ترفند این است که از منطق بولی استفاده کنید، که در آن منطق تابع OR با جمع (+) نمایش داده میشود. میتوانید این رویکرد را در برگه زیر مشاهده کنید:
فرمولهای موجود در J3:J5 به شرح زیر هستند:
= SUMPRODUCT ( - - ( ( C3:C13 = "قرمز" ) + ( C3:C13 = "آبی" ) > 0 ) , G3:G13 ) // قرمز یا آبی
= SUMPRODUCT ( - - ( ( D3:D13 = "تهران" ) + ( C3:C13 = "قرمز" ) > 0 ) ,G3:G13 ) // تهران یا قرمز
= SUMPRODUCT (- - ( ( D3:D13 = "تهران" ) + ( C3:C13 = "آبی" ) > 0) , G3:G13 ) // تهران یا آبی
توجه داشته باشید که دو عبارت منطقی با عمل جمع (+) به هم متصل شدهاند و آرایه حاصل با صفر (>0) مقایسه شده و سپس با استفاده از منفی دوگانه (--) به ۱ و ۰ تبدیل میشود.
ترکیب خلاصه شده SUMPRODUCT
اغلب فرمول زیر را که در بالا توضیح داده شد:
= SUMPRODUCT (- - ( A1:A6 = "تهران" ) ,B1:B6 )
به روشی متفاوت ، به صورت زیر خواهید دید:
= SUMPRODUCT ( ( A1:A6 = "تهران" ) * B1:B6 )
توجه داشته باشید که تمام محاسبات به array1 منتقل شدهاند. نتیجه یکسان است، اما این ترکیب چند مزیت دارد. اول، فرمول فشردهتر است، به خصوص با پیچیدهتر شدن منطق. دلیل این امر این است که دیگر نیازی به علامت منفی دوگانه (--) برای تبدیل مقادیر TRUE و FALSE نیست - عملگرهای ریاضی ضرب ( ) به طور خودکار مقادیر TRUE و FALSE را به 1 و 0 تبدیل میکند. اما مهمترین مزیت، انعطافپذیری است. هنگام استفاده از مولفههای جداگانه ، عمل ریاضی همیشه ضرب - * - است، زیرا SUMPRODUCT مجموع حاصلضربها را بر میگرداند. این کار فرمول را به منطق تابع AND محدود میکند زیرا ضرب مربوط به جمع در جبر بولی است. انتقال محاسبات به یک مولفه به این معنی است که میتوانید از جمع (+) برای منطق تابع OR، در هر ترکیبی، استفاده کنید. به عبارت دیگر، میتوانید عملگر ریاضی خود را انتخاب کنید که در نهایت منطق فرمول را تعیین میکند.
اگرچه دیگر نیازی به منفی دوگانه نیست، اما ضرری ندارد که آن را در فرمول باقی بگذارید.
با در نظر گرفتن مزایای فوق، یک عیب برای ترکیب خلاصه شده وجود دارد. SUMPRODUCT طوری برنامهریزی شده است که خطاهای ناشی از ضرب مقادیر متنی در آرایههایی که به عنوان مولفههای جداگانه داده میشوند را نادیده بگیرد. این عملکرد میتواند در موقعیتهای خاص مفید باشد. با ترکیب خلاصه شده، این مزیت از بین میرود، زیرا ضرب در داخل یک مولفه آرایهای واحد اتفاق میافتد. در این حالت، رفتار عادی اعمال میشود و مقادیر متنی خطاهای VALUE# ایجاد میکنند.
توجه: از نظر فنی، انتقال محاسبات به array1 یک "عملیات آرایهای" ایجاد میکند و SUMPRODUCT یکی از معدود توابعی است که میتواند یک عملیات آرایهای را به صورت بومی و بدون Control + Shift + Enter در نسخههای قدیمی اکسل انجام دهد.
نادیده گرفتن سلولهای خالی
برای نادیده گرفتن سلولهای خالی با SUMPRODUCT میتوانید از عبارتی مانند زیر استفاده کنید.
range <> " "
در مثال زیر، فرمولهای G3 و G4 هر دو سلولهای ستون C را که حاوی مقداری نیستند، نادیده میگیرند:
= SUMPRODUCT ( - - ( C3:C13 <> "" ) ) // شمارش
= SUMPRODUCT ( - - ( C3:C13 <> "" ) * E3:E13 ) // حاصل جمع
SUMPRODUCT با توابع دیگر
SUMPRODUCT میتواند مستقیما از توابع دیگر استفاده کند. ممکن است SUMPRODUCT را همراه با تابع LEN برای شمارش کل نویسههای یک محدوده یا با توابعی مانند ISBLANK ، ISTEXT و ... برای شمارش سلولهای خالی یک محدوده یا مقادیر متنی در یک محدوده ببینید. اینها معمولاً توابع آرایهای نیستند، اما وقتی محدودهای به آنها داده میشود، آرایهای از نتایج ایجاد میکنند. از آنجایی که SUMPRODUCT برای کار با آرایهها ساخته شده است، میتواند محاسبات را مستقیما روی آرایهها انجام دهد.
به مثال زیر توجه کنید که پنج آدرس ایمیل در محدوده B3:B7 داریم و از SUMPRODUCT با تابع LEN برای محاسبه کل نویسههای محدوده ، با فرمول زیر در سلول D3 استفاده کردهایم:
= SUMPRODUCT ( LEN ( B3:B7 ) )
همچنین میتوانستید یک ستون کمکی در ستون C اضافه کنید که از فرمول LEN(A1) استفاده کند تا تعداد نویسههای هر سلول ستون B را محاسبه کند. سپس از تابع SUM برای جمع هر 5 عدد استفاده کنید.
آرایهها و اکسل ۳۶۵
در اکسل ۲۰۱۹ و قبل از آن، میتوان از تابع SUMPRODUCT برای ایجاد فرمولهای آرایهای استفاده کرد که نیازی به Ctrl+Shift+Enter ندارند. این دلیل کلیدی بود که SUMPRODUCT در ۲۰ سال گذشته یا بیشتر، تا زمان معرفی فرمولهای آرایهای پویا، به طور گسترده برای ایجاد فرمولهای پیشرفته استفاده میشد. استفاده از SUMPRODUCT راهی برای کار کردن فرمولهای آرایهای در هر نسخهای از اکسل بدون نیاز به مدیریت خاص بود.
با این حال، در اکسل ۳۶۵، موتور فرمول، آرایهها را به صورت بومی مدیریت میکند. این یعنی شما اغلب میتوانید از تابع SUM به جای SUMPRODUCT در یک فرمول آرایهای با نتیجه یکسان استفاده کنید ، بدون اینکه نیازی به وارد کردن فرمول به روش خاصی باشد. با این حال، اگر همان فرمول در نسخههای قبلی اکسل باز شود، برای عملکرد صحیح همچنان به Ctrl + Shift + Enter نیاز خواهد داشت.
نکته اصلی این است که اگر قرار است از یک برگه اغلب در نسخههای قدیمیتر اکسل استفاده شود، SUMPRODUCT گزینه امنتری است.
یادداشتها
- SUMPRODUCT با ایتمهای غیر عددی در آرایهها به عنوان صفر رفتار میکند.
- مولفههای آرایه باید اندازه یکسانی داشته باشند. در غیر این صورت، SUMPRODUCT مقدار خطای VALUE# را تولید میکند.
- بررسیهای منطقی درون آرایهها مقادیر TRUE و FALSE ایجاد میکنند. در بیشتر موارد، نیاز است که این مقادیر به ۱ و ۰ تبدیل شود.
- SUMPRODUCT اغلب میتواند مستقیماً از نتیجه توابع دیگر استفاده کند.
لطفا دیدگاه خود را در کادر پایین با ما به اشتراک بگذارید.
آیا میخواهید:
- برنامههای قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
- فرصت های شغلی جدید کسب کنید.
کتابهای معرفی شده ، راهنمای گام به گام جامع و عملی برای افرادی است که (مانند شما) میخواهند به این اهداف برسند.
