تابع SumProduct در اکسل، یکی از توابع پیشرفته محاسباتی است که میتواند به شما در تجزیه و تحلیل دادههای پیچیده کمک کند. در این مقاله، به بررسی این تابع و کاربرد آن در اکسل میپردازیم.
تابع SumProduct در اکسل چیست؟
تابع SumProduct در اکسل، یک تابع محاسباتی است و برای محاسبه حاصل ضرب دو ردیف یا ستون از دادههای یک محدوده استفاده میشود ، می بایست در مباحث گسترده آموزش اکسل به فراگیران آموزش داده شود .
برای استفاده از این تابع، شما باید ابتدا محدوده داده را انتخاب کرده و سپس نام آن را به عنوان آرگومان به تابع بدهید.
نکته :در این تابع، هر دو محدوده داده باید دارای تعداد ستون و ردیف یکسان باشند.
آرگومان تابع Sumproduct
SUMPRODUCT(array1, [array2], [array3], …)
در این رابطه، آرایهها نام محدوده داده هستند که میخواهید آنها را با یکدیگر ضرب کنید و سپس جمع کنید.
مثالهای کاربردی تابع SumProduct
مثال اول : فرض کنید دو لیست از اعداد دارید .
به صورت بسیار ساده می توان باکمک یکی از توابع اکسل به نام تابع Sumproduct زیگمای A*B را به صورت نمایش داده شده محاسبه نمود .
در این مرحله ممکن است به صورت یک تابع تقریبا بی فایده به نظر برسد اما با مطالعه ادامه مطلب خواهید دید که همه چیز تغییر خواهد کرد و شما نیز به مفید و کاربردی بودن این تابع پی خواهید برد .
فرض کنید شما یک لیست از اطلاعات فروش شامل ستون های نام و نام خانوادگی ، منطقه ، محصول و فروش دارید.
میخواهید بدانید که چه تعداد کالا به آقای Luke فروخته شده است؟
ساده است، شما با استفاده از فرمول SUMIF براحتی می توانید این مسئله را حل کنید .
اما صبر کنید ، اگر بخواهید بدانید چه تعداد کالا به آقای Luke در منطقه غرب «west» به فروخته شده است چه خواهید کرد ؟
ما به شما استفاده از تابع کاربردی Sumproduct را پیشنهاد می کنیم.
هرچند راه های دیگری از قبیل استفاده از تابع Sumifs که در آفیس 2007 به بعد گنجانده شده وجود دارد اما در اینجا قصد آموزش تابع 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)
این فرمول به معنی آن است که اگر مقدار سلول مورد نظر برابر با ۵ باشد، مقدار آن سلول را برمیگرداند و در غیر این صورت، مقدار صفر را برمیگردانیم.
خطاهای تابع SumProduct
تابع SumProduct ، میتواند به خطاهای مختلفی منجر شود. در ادامه، به برخی از این خطاها و راههای برطرف کردن آنها اشاره خواهیم کرد:
۱. خطای #VALUE!: این خطا معمولاً به دلیل این است که تعدادی از آرگومانهای تابع SumProduct به شکل نامعتبر وارد شدهاند. برای رفع این خطا، مطمئن شوید که تمامی آرگومانها به درستی و با نوع داده مناسب وارد شده باشند.
۲. خطای #REF!: این خطا بیانگر این است که محدوده دادهای که به تابع SumProduct وارد شده، به شکل نامعتبر تعریف شده است. برای رفع این خطا، مطمئن شوید که محدوده دادهای که به تابع وارد میکنید، به درستی تعریف شده باشد.
۳. خطای #DIV/0!: این خطا معمولاً به دلیل این است که مقدار یک یا چند عنصر در محدوده داده برابر با صفر است. برای رفع این خطا، مطمئن شوید که مقادیر دادههای ورودی خود را بررسی کنید و در صورت نیاز، مقادیر را تغییر دهید.
۴. خطای #NUM!: این خطا معمولاً به دلیل این است که محاسبات درون تابع به شکل نامعتبری انجام شده است. برای رفع این خطا، مطمئن شوید که تمامی محاسبات داخل تابع به درستی و با نوع داده مناسب انجام شده باشند.
۵. خطای #NAME?: این خطا معمولاً به دلیل این است که نام تابع SumProduct به شکل نادرست وارد شده است. برای رفع این خطا، مطمئن شوید که نام تابع به درستی و به همراه تمامی آرگومانهای مورد نیاز وارد شده باشد.
۶. خطای #NULL!: این خطا معمولاً به دلیل این است که محدوده دادهای که به تابع وارد شده، خالی است یا با خانههای دیگری همپوشانی دارد. برای رفع این خطا، مطمئن شوید که محدوده دادهای که به تابع سام پروداکت وارد میکنید، صحیح و با ابعاد مناسب تعریف شده باشد.
۷. خطای #N/A: این خطا معمولاً به دلیل این است که یکی از عناصر دادهای که در محاسبات تابع استفاده شده، با مقدار نامعتبر یا وجود نداشتن در دادههای ورودی همراه است. برای رفع این خطا، مطمئن شوید که تمامی عناصر دادهای مورد استفاده، در دادههای ورودی موجود هستند و با نوع داده مناسبی تعریف شدهاند.
