فرمول SUMPRODUCT در اکسل

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

ساختار فرمول SUMPRODUCT و روش استفاده از آن

این فرمول ساختار بسیار ساده ای دارد

=SUMPRODUCT(list1,list2,…)

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

برای درک این مطلب به مثال ساده زیر توجه نمایید.

اگر شما دارای داده هایی به شکل {2,3,4} در یک لیست و {5,10,20} در لیست دیگر باشید و فرمول SUMPRODUCT را روی این لیست ها به کار ببرید نتیجه تابع عدد 120 خواهد بود (زیرا 2*5+3*10+4*20 برابر 120 خواهد شد)

SUMPRODUCT (A1:A3,B1:B3)

=2*5+3*10+4*20

=10+30+80=120

در اینجا نیز به نظر می رسد که این تابع کارایی زیادی نداشته باشد. اما اگر به خواندن ادامه دهید، نظر شما عوض خواهد شد.

فرمول SUMPRODUCT و آرایه ها در excel

فرض کنید شما جدولی از اطلاعات فروش دارید و ستونها با عنوان های  (نام – منطقه –  مقدار فروش) نامگذاری شده اند. شما قصد دارید بفهمید که تعداد محصولات فروخته شده توسط فروشنده ای به نام “رضا” چقدر است؟

این یک مسئله ساده است. برای این کار می توانید از یک فرمول SUMIF استفاده کنید به طوری که در آن criteria range برابر “نام” و sum range آن برابر “مقدار فروش” باشد.

اگر با فرمول SUMIF آشنایی ندارید به جمع شرطی در اکسل مراجعه نمایید.

اما اگر بخواهید مقدار فروش فروشنده ای به نام “سعید” را در منطقه غرب پیدا کنید چکار خواهید کرد؟

شما در اینجا دو راه دارید.

1. استفاده از فرمول های آرایه ای
2. استفاده از جدول محوری
   

اما راه سومی نیز وجود دارد. استفاده از تابع SUMPRODUCT

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

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

فرض کنید اطلاعات فروش ما در ناحیه A1:C10 قرار داشته باشند. در ستون A اسم فروشنده، در ستون B نام منطقه و در ستون C مقدار فروش قرار دارند.

فرمول SUMPRODUCT به صورت زیر نوشته می شود.

=SUMPRODUCT(–(A1:A10=”سعید“),–(B1:B10=”غرب“),C1:C10)

توضیح روش کار فرمول

 (–(A1:A10=”سعید“)

این قسمت در ستون نام فروشنده به دنبل کلمه “سعید” می گردد. در صورتی که آن را پیدا کند نتیجه عدد 1 در غیر این صورت عدد 0 خواهد بود.

(–(B1:B10=”غرب“)

این قسمت نیز عملی مشابه مرحله قبل را انجام می دهد. اما این بار روی ناحیه B1:B10 به دنبال عبارت “غرب” می گردد.

C1:C10

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

این سه قسمت باهم کار می کنند و به صورت مجزا کاری انجام نمی دهند.