اکسللرن | مرجع پروژههای کاربردی اکسل |
تابع SUMPRODUCT در اکسل
برای به‌دست آوردن مجموع حاصل‌ضرب اعداد متناظر در یک یا چند محدوده، می‌توان از تابع SUMPRODUCT در اکسل استفاده کرد.
استفاده ساده
بیایید با یک مثال ساده شروع کنیم! معمولاً تابع SUMPRODUCT اعداد متناظر در دو محدوده را در هم ضرب می‌کند و سپس مجموع حاصل‌ضرب‌ها را محاسبه می‌نماید.
به عنوان نمونه، تابع SUMPRODUCT زیر مقدار کل هزینه انجام‌شده را محاسبه می‌کند.
توضیح: تابع SUMPRODUCT محاسبه‌ی زیر را انجام می‌دهد:
(2×1000)+(4×250)+(4×100)+(2×50)=3500(2 × 1000) + (4 × 250) + (4 × 100) + (2 × 50) = 3500
تمامی محدوده‌ها باید دارای اندازه‌های یکسان باشند؛ در غیر این صورت، اکسل خطای #VALUE! را نشان خواهد داد.
تابع SUMPRODUCT مقادیر غیرعددی را به‌صورت صفر در محاسبات لحاظ می‌کند
اگر فقط یک محدوده به تابع SUMPRODUCT بدهید، نتیجه‌ی آن کاملاً مشابه با خروجی تابع SUM خواهد بود
استفاده پیشرفته
تابع SUMPRODUCT یکی از توابع بسیار منعطف در اکسل است و قادر است نتایجی مشابه با بسیاری از توابع داخلی و حتی فرمول‌های آرایه‌ای ارائه دهد.
به‌عنوان مثال، تابع COUNTIF زیر تعداد سلول‌هایی را می‌شمارد که دقیقاً شامل عبارت (ستاره)باشند.
تابع SUMPRODUCT نشان‌داده‌شده، دقیقاً همان خروجی را ارائه می‌دهد
توضیح: عبارت
–(A1:A7=”star”)
به آرایه‌ی زیر تبدیل می‌شود:
–{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}
علامت منفی دوبل — مقادیر منطقی (Boolean) را به ۱ و ۰ تبدیل می‌کند (TRUE=1 و FALSE=0). نتیجه:
{1;0;0;0;0;1;0}
این آرایه به‌عنوان ورودی برای تابع SUMPRODUCT استفاده می‌شود و حاصل برابر با 2 خواهد بود.
تابع COUNTIF زیر، تعداد سلول‌هایی را می‌شمارد که دقیقاً شامل عبارت star به‌علاوه یک کاراکتر دیگر باشند.
علامت سؤال ? دقیقاً با یک کاراکتر مطابقت دارد.
تابع SUMPRODUCT کامل نیست!
در هنگام استفاده از آن نمی‌توان از کاراکترهای جایگزین مانند ? و * استفاده کرد.
فرمول آرایه‌ای زیر مجموع تعداد کاراکترها را در یک محدوده سلول‌ها محاسبه می‌کند
نکته: برای وارد کردن یک فرمول آرایه‌ای، باید CTRL + SHIFT + ENTER را فشار دهید تا اکسل به‌طور خودکار کروشه‌های {} را اضافه کند.
در Excel 365 یا Excel 2021 کافی است فقط Enter را بزنید و دیگر کروشه‌ها نمایش داده نمی‌شوند.
تابع SUMPRODUCT زیر دقیقاً همان نتیجه را ارائه می‌دهد.
نکته: آرایه ثابت {6;5;4;5} به‌عنوان ورودی برای تابع SUMPRODUCT استفاده شده و حاصل برابر با 20 است. تابع SUMPRODUCT به‌طور طبیعی آرایه‌ها را مدیریت می‌کند، بنابراین نیازی به فشار دادن CTRL + SHIFT + ENTER نیست.
