آموزش رایگان آموزی بزرگترین مرجع آموزشی رایگان
برای محاسبه مجموع محصولات بر اساس تعداد آنها در یک یا چند محدوده، از تابع قدرتمند SUMPRODUCT در اکسل استفاده کنید.
دانلود فایل اکسل این آموزش sumproduct
استفاده پایه ای
1. به عنوان مثال، تابع SUMPRODUCT زیر مقدار کل قیمت را محاسبه می کند.
توضیح: تابع SUMPRODUCT این محاسبه را انجام می دهد: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.
2. محدوده ها باید ابعاد مشابهی داشته باشند، در غیر این صورت اکسل خطای !VALUE# را نمایش می دهد.
3. تابع SUMPRODUCT مقدار هر ورودی که عددی نباشد را به عنوان صفر در نظر می گیرد.
4. اگر یک محدوده واحد را ارائه می دهید، تابع SUMPRODUCT نتیجه دقیقاً همانند تابع SUM را تولید می کند.
استفاده پیشرفته
تابع SUMPRODUCT یک تابع فوق العاده چند منظوره است و می تواند همان نتیجه ای را با بسیاری از توابع ساخته شده در اکسل و حتی فرمول های آرایه ایجاد کند!
1a. به عنوان مثال، تابع COUNTIF زیر شمارش سلول هایی که دقیقا ستاره هستند را شمارش می کند.
1b. تابع SUMPRODUCT زیر نتایج مشابهی تولید می کند.
توضیح: –(A1:A7=”star”) به ثابت آرایه زیر کاهش می یابد:
–{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}
منفی دوگانه — این Booleans را به 1 و 0 می برد (TRUE = 1، FALSE = 0). نتیجه:
{1؛ 0؛ 0؛ 0؛ 0؛ 1؛ 0}
این ثابت آرایه به عنوان یک استدلال برای تابع SUMPRODUCT استفاده می شود، نتیجه 2 را ارائه می دهد.
1c. تابع زیر COUNTIF تعداد سلول هایی که دقیقا star + 1 کاراکتر دارند را شمارش می کند. یک علامت سوال (؟) دقیقا یک کاراکتر دارد.
1d. تابع SUMPRODUCT کامل نیست! شما نمی توانید از کاراکتر SUMPRODUCT از کاراکترهای علامت (؟ و *) استفاده کنید.
2a. فرمول آرایه زیر شمارش تعداد کاراکترها در یک سلول را نشان می دهد.
توجه داشته باشید: با فشار دادن CTRL + SHIFT + ENTER، یک فرمول آرایه را خاتمه دهید. اکسل پرانتزهای مجزای {} را اضافه می کند.
2b. تابع SUMPRODUCT در زیر نتایج مشابهی تولید می کند.
توجه داشته باشید: ثابت {9;4;6;5} به عنوان یک استدلال برای تابع SUMPRODUCT، نتیجه 24 را به کار می برد. تابع SUMPRODUCT آرایه ها را بومی می کند بنابراین شما لازم نیست که CTRL + SHIFT + ENTER فشار داهید. این یکی از بزرگترین مزایای تابع SUMPRODUCT است، زیرا پرانتزهای مجزا {} بسیاری از مبتدیان اکسل را به اشتباه میاندازد!
3a. فرمول زیر مجموع آرایه ها را در سال 2018 بر می گرداند.
3b. تابع SUMPRODUCT زیر نتایج مشابهی تولید می کند.
توضیح: YEAR(A1:A5)=2018)*B1:B5) به:
({2018;2017;2018;2017;2017}=2018)*{10;1;7;20;3}
تبدیل می شود و این به موارد زیر کاهش می یابد:
{TRUE;FALSE;TRUE;FALSE;FALSE}*{10;1;7;20;3}
ما به یک دو منفی نیاز نداریم (به مثال 1b توجه کنید) به خاطر این که اپراتور ضرب * به طور خودکار بولین ها را به 1 و 0 اعمال می کند (TRUE = 1، FALSE = 0). نتیجه:
{10;0;7;0;0}
این ثابت آرایه به عنوان یک استدلال برای تابع SUMPRODUCT استفاده می شود، نتیجه 17 را ارائه می دهد.