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

رتبه: 5 ار 1 رای SSSSS
تابع سام پراداکت
نویسنده: تیم تولید محتوا زمان مطالعه 6 دقیقه
Banner Image

در این آموزش تابع 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)

Excel SUMPRODUCT

دو آرایه در تابع 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 single column

مثال ۴:

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

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

فرمول:

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

SUMPRODUCT count

در مثال بالا:

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

چرب زبان

با این آموزش اکسل صفر تا صد اکسل، رو توی کمترین زمان ممکن یاد بگیر.بهترین پک آموزش اکسل در ایران همین الان خرید و دانلود کنید!

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

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

SUMPRODUCT count T F

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

مثال ۵:

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

=SUMPRODUCT(A2:A7,B2:B7)

SUMPRODUCT non nuumeri

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

مثال ۶:

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

=SUMPRODUCT(A2:A7,B2:B5)

SUMPRODUCT dimensions

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

=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)

SUMPRODUCT filter sum

برای درک بهتر این موضوع، ستون چهارم (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))

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

SUMPRODUCT character count

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

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

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

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

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

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

SUMPRODUCT count spec

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

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

profile name
تیم تولید محتوا

بخندید کتاب بخونید و خوب باشید تا جامعه مون به آرامش برسه. لطفا ! هر سوالی دارید در بخش نظرات مطرح کنید. ما یا سایر هموطنان عزیز پاسخ خواهیم داد. برای کمک به سایت ما و گسترش آموزش در بین هموطنان، در سایتها، وبلاگ ها و شبکه های اجتماعی لینک سایت ما را درج کنید.

مطالب پیشنهادی برای شما

محصولات مرتبط

مشاهده همه

دیدگاهتان را بنویسید

1 2 3 4 5

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

  • اميني
    اميني آیا این دیدگاه مفید بود ؟

    با سلام
    ضمن تشکر از آموزشهای خوبتون من یه سوالی برام پیش اومده که ممنون میشم راهنماییم کنید.
    وقتی از بین داده های چند تا شیت میخوایم رتبه بندی انجام بدیم و برا رتبه هایی که مساوی میشن سلولی که در ویژگی دیگه ای پایین تره رتبه برتری داشته باشه از چه روشی باید استفاده کرد. البته من از ترکیب rank و countifs فرمول زیر رو نوشتم ولی نمیدونم چرا خطا میده؟
    (RANK.EQ(sheet1!Y2,sheet1:sheet10!Y2:Y100)+COUNTIFS(sheet1:sheet10!Y2:Y100,sheet1!Y2,sheet1:sheet10!H2:H100,”<&sheet1!H1
    آیا اشکالی تو فرمول نویسی هست یا چیز دیگه است؟ اگه فرموله ایراد داره چجوری متوجه بشم کجاشه؟
    البته شنيدم كه از SUMPRODUCT هم ميشه استفاده كرد ولي درست بلدش نيستم.
    ممنون

    پاسخ
مشاهده همه نظرات
سبد خرید
سبد خرید شما خالی است
× جهت نصب روی دکمه زیر در گوشی کلیک نمائید
آی او اس
سپس در مرحله بعد برروی دکمه "Add To Home Screen" کلیک نمائید