مهارتهای اکسل (تابع Sumproduct)

فرض کنید داده ها در محدوده A1:A11 قرار دارد .(نام و نام خانوادگی در ستون A ، منطقه در ستون B و فروش در ستون C )

فرمول به صورت زیر خواهد بود .

SUMPRODUCT((A2:A11=”Luke Skywalker”)*(B2:B11=”west”);C2:C11)

همانطور که احتمالا متوجه شده اید تحلیل فرمول فوق به شکل زیر خواهد بود .

مثال 2:
در این مثال، ما می‌خواهیم مجموع ضرایب دو ستون از یک محدوده را محاسبه کنیم.

برای این کار، ابتدا محدوده داده را انتخاب کرده و سپس نام آن را به عنوان آرگومان اول به تابع SumProduct می‌دهیم.

سپس محدوده داده دوم را انتخاب کرده و به عنوان آرگومان دوم به تابع SumProduct می‌دهیم.

حالا تابع را با فشردن کلید Enter اجرا می‌کنیم و جواب را دریافت می‌کنیم.

(SUMPRODUCT(A1:A10

مثال 3:
در این مثال، ما می‌خواهیم مجموع حاصلضرب دو ستون از دو محدوده داده متفاوت را محاسبه کنیم.

برای این کار، ابتدا محدوده داده اول را انتخاب کرده و سپس نام آن را به عنوان آرگومان اول به تابع SumProduct می‌دهیم.

سپس محدوده داده دوم را انتخاب کرده و به عنوان آرگومان دوم به تابع SumProduct می‌دهیم.

در نهایت، تابع را با فشردن کلید Enter اجرا می‌کنیم و جواب را دریافت می‌کنیم.

SUMPRODUCT(A1:A10, B1:B10)

مثال4:
در این مثال، ما می‌خواهیم مجموع ضرایب یک ستون از یک محدوده داده را محاسبه کنیم، در حالی که سطرهای خاصی از این محدوده را نادیده بگیریم.

برای این کار، ابتدا محدوده داده را انتخاب کرده و سپس یک محدوده دیگر را انتخاب کرده و در آن سطرهای خاصی که می‌خواهیم نادیده بگیریم را به صورت فرمول Exclude ردیف‌ها نوشته و آن را به عنوان آرگومان دوم به تابع SumProduct می‌دهیم.

در نهایت، تابع را با فشردن کلید Enter اجرا می‌کنیم و جواب را دریافت می‌کنیم.

SUMPRODUCT(A1:A10, (ROW(A1:A10)<>3)(ROW(A1:A10)<>7)(ROW(A1:A10)<>9))

در این مثال، فرمول Exclude ردیف‌ها به شکل زیر است:

(ROW(A1:A10)<>3)(ROW(A1:A10)<>7)(ROW(A1:A10)<>9)

این فرمول به معنی آن است که تمام سطرهایی که شماره آنها برابر با 3 یا 7 یا 9 نیست، برابر با یک قرار بگیرد و تمام سطرهایی که شماره آنها برابر با 3 یا 7 یا ۹ نیست، برابر با صفر قرار بگیرد.

سپس تابع SumProduct این آرایه‌ها را با هم ضرب کرده و حاصل را با هم جمع می‌کند.

به این ترتیب، حاصل مجموع ضرایب ستون A در محدوده داده A1:A10، با نادیده گرفتن سطرهای ۳، ۷ و ۹، محاسبه شده است.

مثال 5:
در این مثال، ما می‌خواهیم مجموع حاصلضرب دو ستون از یک محدوده داده را محاسبه کنیم، در حالی که فقط سطرهایی را در نظر می‌گیریم که شامل عدد ۵ هستند.

برای این کار، ابتدا محدوده داده را انتخاب کرده و سپس با استفاده از تابع IF، برای هر سلول در آن محدوده، بررسی می‌کنیم که آیا آن سلول شامل عدد ۵ است یا نه.

اگر شامل عدد ۵ باشد، مقدار آن سلول را با مقدار متناظر در ستون دیگر ضرب کرده، در غیر این صورت مقدار صفر را در نظر می‌گیریم.

سپس این مقادیر را به عنوان آرایه اول به تابع SumProduct می‌دهیم و ستون دوم را نیز به عنوان آرگومان دوم.

در نهایت، تابع را با فشردن کلید Enter اجرا می‌کنیم و جواب را دریافت می‌کنیم.

SUMPRODUCT(IF(A1:A10=5, A1:A10, 0), B1:B10)

در این مثال، تابع IF برای بررسی اینکه آیا سلول مورد نظر شامل عدد ۵ است یا نه، به شکل زیر استفاده شده است:

IF(A1:A10=5, A1:A10, 0)

این فرمول به معنی آن است که اگر مقدار سلول مورد نظر برابر با ۵ باشد، مقدار آن سلول را برمی‌گرداند و در غیر این صورت، مقدار صفر را برمیگردانیم.