تابع سام پراداکت

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

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” در فرمول جایگزین کنید.

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

تفاوت پک های حرفه ای ما با آموزشهای رایگان: بطور خلاصه از زمین تا آسمان! 1-پکها، جدیدترین نسخه نرم افزارها را آموزش می دهند با قابلیت های بسیار بیشتر. 2-پکها توسط متخصص آن نرم افزار، به صورت کاملا پروژه محور و با حل چالش هایی که در مسیر کار عملی و حرفه ای با آن روبرو می شوید تهیه شده اند و بعد از استفاده، کاملا برای بازار کار آماده اید! 3- متد این پکها کاملا کار شده و تا ماهها، در ذهن تان ماندگارند و یادگیری بسیار سریعتر و کاملتری خواهید داشت. آموزشهای رایگان فقط دستورات نرم افزارها را (آنهم ناقص) بیان می کنند و تازه برای ورود به بازار باید ماهها تجربه عملی هم کسب کنید !!

turned_in

چرب زبان

با این اپلیکیشن ساده، هر زبانی رو فقط با 5 دقیقه در روز، توی 80 روز مثل بلبل حرف بزن! بهترین متد روز، تقویت حافظه، آموزش تصویری. یادگیری زبان کلید یادگیری هر مهارتی در قرن 21 !


حتما بخوانید!

1 دیدگاه. ارسال دیدگاه جدید

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

    پاسخ

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

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این فیلد را پر کنید
این فیلد را پر کنید
لطفاً یک نشانی ایمیل معتبر بنویسید.

فهرست