آموزش تابع SUMPRODUCT در اکسل (به زبان ساده)

در این آموزش تابع SUMPRODUCT در اکسل را معرفی خواهیم کرد و شیوه استفاده از آن را همراه با مثال های مختلف به طور کامل بررسی می کنیم.

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

لیست همه دروس کاربردی اکسل ما

تابع SUMPRODUCT

تابع SUMPRODUCT جزء توابع ریاضی و مثلثاتی در اکسل طبقه بندی می شود. این تابع عناصر آرایه داده شده را ضرب کرده و سپس مجموع حاصلضرب ها را برمی گرداند. به طور معمول اگر بخواهید از تابعی به عنوان یک فرمول آرایه ای استفاده کنید باید پس از نوشتن فرمول آن را با استفاده از کلیدهای میانبر Ctrl + Shift + Enter تأیید کنید. اما برای تابع SUMPRODUCT نیازی به این کار نیست زیرا این تابع برای پردازش آرایه ها طراحی شده است.

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

ساختار کلی تابع SUMPRODUCT به صورت زیر است:

SUMPRODUCT(array1, [array2], [array3], …)

• array1 اولین آرگومان تابع و محدوده ای است که می خواهید عناصر آن را ضرب کرده و حاصل جمع را به دست آورید. این آرگومان الزامی است.
• [array2], [array3], … آگومان های اختیاری هستند.

نکات:

• تابع SUMPRODUCT موارد غیر عددی در آرایه و محدوده را صفر در نظر می گیرد.
• اگر تابع بیش از یک آرگومان دارد، ابعاد آرایه ها باید یکسان باشد در غیر اینصورت، خطای !VALUE# رخ می دهد.

مثال های تابع SUMPRODUCT

مثال ۱:

در این مثال می خواهیم حاصل Quantity * price را به دست آوریم.

فرمول زیر را در سلول مورد نظر می نویسیم:

=SUMPRODUCT(A2:A6,B2:B6)

دو آرایه در تابع SUMPRODUCT نوشته شده است. آرایه اول A2:A6 و دومی B2:B6 است. حاصلضرب ها به شکل زیر محاسبه خواهد شد و عناصر متناظر از هر دو ستون در هم ضرب می شوند:

A2 * B2 = 30

A3 * B3 = 11

A4 * B4 = 29

….

سپس مجموع این حاصلضرب ها به دست می آید، نتیجه برابر با ۵۷۰ می باشد.

مثال ۲:

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

=SUMPRODUCT(A2:B5,C2:D5)

تابع SUMPRODUCT به ترتیب زیر، ضرب ها را محاسبه می کند:

A2 * C2 و B2 * D2

A3 * C3 و B3 * D3

A4 * C4 و B4 * D4

A5 * C5 و B5 * D5

در نهایت، تابع حاصل همه ضرب ها با هم جمع کرده و به عنوان نتیجه برمی گرداند.

مثال ۳:

اگر در تابع SUMPRODUCT فقط از یک آرگومان و آرایه استفاده شود، عناصر آرایه به سادگی با یکدیگر جمع می شوند. در مثال زیر، فقط محدوده ستون price در فرمول آورده شده است:

=SUMPRODUCT(B2:B7)

مثال ۴:

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

برای نشان دادن این مفهوم، یک ستون دیگر به نام Product Name اضافه کرده ایم. در این مثال با استفاده از تابع SUMPRODUCT تعداد تکرار مقدار Rice را در این ستون به دست می آوریم:

فرمول:

=SUMPRODUCT(–(A2:A7=”Rice”))

در مثال بالا:

“Rice” سه بار در آرایه تکرار شده است.

نکته: اگر از یک آرایه به عنوان آزمون منطقی در تابع SUMPRODUCT استفاده کنید، مقادیر TRUE و FALSE را برمی گرداند. می توانید با استفاده از علامت (–) مقادیر TRUE و FALSE را به ۱ و ۰ تبدیل کنید.

اگر پس از انتخاب A2:A7=”Rice” در نوار فرمول، کلید F9 را فشار دهید، SUMPRODUCT مقادیر TRUE / FALSE قابل مشاهده خواهد بود.

همانطور که مشاهده می کنید، در مواردی که مقدار سلول در محدوده برابر با “Rice” می باشد، مقدار TRUE و در غیر اینصورت “FALSE” نشان داده شده است.

مثال ۵:

همانطور که گفتیم، تابع SUMPRODUCT موارد غیر عددی در آرایه را صفر در نظر می گیرد. در مثال زیر نشان داده شده است:

=SUMPRODUCT(A2:A7,B2:B7)

سلول های B5 و B7 مقادیر غیر عددی هستند ولی خطایی رخ نداده است چراکه تابع SUMPRODUCT آنها را صفر در نظر گرفته است. برای بقیه موارد مقدار مجموع حاصلضرب دو ستون محاسبه شده است (۹۱٫۷۵).

مثال ۶:

در تابع SUMPRODUCT ابعاد آرایه ها باید یکسان باشد. در غیر اینصورت، خطای !VALUE# رخ می دهد. در مثال زیر ابعاد آرایه ها متفاوت می باشد:

=SUMPRODUCT(A2:A7,B2:B5)

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

=SUMPRODUCT(A4:A7,B2:B5)

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

A4 * B2

A5 * B3

A6 * B4

A7 * B5

در نهایت نیز مجموع آنها را به عنوان نتیجه برمی گرداند.

مثال ۷:

در مثال ۴ نشان دادیم که چگونه می توان با استفاده از تابع SUMPRODUCT یک آزمایش منطقی برای به دست آوردن تعداد تکرار یک مقدار خاص (“Rice”) استفاده کرد. علاوه بر این می توان آن را گسترش داده و فقط مجموع یک مورد خاص را محاسبه کنید.

در مثال زیر، تابع SUMPRODUCT مورد “Rice” را فیلتر کرده و حاصل جمع (price * quantity) را فقط برای این مقدار محاسبه می کند. به فرمول زیر و نتیجه خروجی دقت کنید:

=SUMPRODUCT(–(A2:A7=”Rice”),B2:B7,C2:C7)

برای درک بهتر این موضوع، ستون چهارم (Totals) را اضافه کرده ایم و در آن مقدار (price * quantity) برای همه مورد در ستون A محاسبه شده است. می توانید خودتان مجموع را محاسبه کرده و با نتیجه SUMPRODUCT مقایسه کنید.

محاسبه تعداد کاراکترها با تابع SUMPRODUCT و LEN

تابع LEN تعداد کاراکترهای موجود در سلول را برمی گرداند:

=LEN(A2)

اما در تابع LEN نمی توانید از محدوده استفاده کنید. باید از تکنیک تابع SUM استفاده کنید و اندازه سلول ها را یکی یکی با استفاده از فرمول زیر جمع کنید تا تعداد کاراکترهای کل محدوده را به دست آورید:

=SUM(LEN(A2), LEN(A3),LEN(A4))

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

=SUMPRODUCT(LEN(A2:B3))

نتیجه به شکل زیر است:

سلول B5 تعداد کل کاراکترهای موجود در سلول های A2 تا B3 را نمایش می دهد که برابر با مقدار ۱۷ است. روش ساده و جالبی نیست؟

شمارش کاراکترهای خاص

با استفاده از توابع SUMPRODUCT، LEN و SUBSTITUTE می توانید تعداد تکرار یک کاراکتر خاص را در آرایه یا محدوده سلول های داده شده به دست آورید.

در مثال و فرمول زیر، تعداد تکرار کاراکتر”h” در محدوده A2:B6 محاسبه شده است:

=SUMPRODUCT(LEN(A2:B6)-LEN(SUBSTITUTE(A2:B6,”h”,””)))

نتیجه به شکل زیر است:

کافیست کاراکتر مورد نظر خود را با کاراکتر “h” در فرمول جایگزین کنید.

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه