کاربرد تابع SUMPRODUCT در اکسل

در این مطلب به کاربرد های ساده و پیشرفته تابع sumproduct در اکسل میپردازیم. همچنین تعدادی مثال ارائه خواهیم کرد و در آن آرایه هایی را با استفاده از تابع sumproduct در اکسل مقایسه میکنیم، به صورت مشروط جمع میزنیم، میانگین وزنی می گیریم و … .

ممکن است بار اول که اسم تابع sumproduct را می شنوید فکر کنید تابعی، کم کاربرد است که محصولات مختلف را با هم جمع میکند. ولی اسم این تابع حتی جزء کوچکی از ظرفیت های این تابع را نشان نمی دهد.

در حقیقت، sumproduct تابعی فراگیرنده است و کاربرد های بسیار زیادی دارد. به دلیل قابلیت منحصر به فرد این تابع در استفاده از آرایه ها به صورت هوشمندانه، sumproduct برای مقایسه داده ها در دو یا چند محدوده و محاسبه داده ها با وجود چندین شرط، بسیار کاربردی است. مثال های زیر قدرت تابع sumproduct را به شما نشان خواهند داد و آن را به خوبی روشن خواهند کرد.

• تابع sumproduct در اکسل . ترکیب و کاربرد
• کاربرد های sumproduct در اکسل – مثال های فرمولی
• sumproduct با شرط های چندگانه (مقایسه آرایه ها)
• گرفتن میانگین، مجموع، میانگین سلول ها، به صورت مشروط
• استفاده از تابع sumproduct در اکسل برای گرفتن میانگین وزنی
• تابع sumproduct  در اکسل بعنوان جایگزینی برای فرمول های آرایه ای

 

تابع SUMPRODUCT در اکسل – ترکیب و کاربرد

عموماً SUMPRODUCT اعداد موجود در آرایه های مختلف را در هم ضرب میکند و مجموع آنها را برمیگرداند.

ترکیب تابع SUMPRODUCT ساده است:

= SUMPRODUCT(ARRAY1,[ARRAY2],[ARRAY3],…)

که در این تابع ARRAY1 ,… آرایه هایی هستند که شما می بایست مقادیر آنها را در هم ضرب کنید و سپس مجموع آنها را محاسبه نمائید. حداقل تعداد آرایه 1 است که در این صورت SUMPRODUCT تمام آرایه ها را جمع میکند و مجموع را برمیگرداند. حداکثر تعداد آرایه ها در اکسل 2010 تا 2016، 255 آرایه است و در ورژن های قدیمی تر اکسل 30 آرایه است.

اگرچه sumproduct از آرایه ها استفاده میکند ولی در این تابع نیازی به استفاده از میانبر آرایه (CTRL+ALT+SHIFT) نیست.

نکته:

• تمام آرایه ها در تابع sumproduct می بایست از تعداد سطر و ستون یکسان برخوردار باشد وگرنه با خطای #VALUE! مواجه خواهید شد.
• اگر هرکدام از آرایه ها، دارای مقادیر غیر عددی باشند بعنوان صفر در نظر گرفته میشود.
• در صورتی که یک آرایه آزمون منطقی(logical test) باشد، نتیجه مقادیر true , false میشود. در این صورت می بایست با استفاده از عملگر(double unary –) آن را تبدیل به عدد کرد.
• sumproduct از کاراکتر های wildcard پشتیبانی نمیکند.

  کاربرد های sumproduct در اکسل

برای بدست آوردن درک صحیح از نحوه کارکردن sumproduct در اکسل به مثال زیر توجه کنید.

فرض کنید مقادیری در سلول های A2:A4 دارید. قیمت ها در سلول های B2:B4 هستند و میخواهید مجموع را بدست آورید. در صورتی که میخواستید این سؤال را در امتحان ریاضی مدرسه حل کنید می بایست مقادیر را در قیمت ضرب میکردید و سپس پاسخ ها را با هم جمع میکردید تا جواب نهایی بدست آید. ولی در اکسل میتوانید پاسخ را با استفاده از یک تابع بدست آورید.

= SUMPRODUCT(A2:A4,B2:B4)

تصویر زیر به صورت عملی این مثال را نشان میدهد.

در پشت پرده این تابع اتفاقات زیر رخ میدهد:

• فرمول عدد اول در آرایه اول را انتخاب میکند و آن را در عدد اول در آرایه دوم ضرب میکند. سپس عدد دوم در آرایه اول را میگیرد و در عدد دوم در آرایه دوم ضرب میکند و الی آخر.
• زمانی که تمام اعداد آرایه ها جمع شد فرمول مقادیر بدست آمده را جمع میکند و مجموع بدست آمده را برمیگرداند.

به عبارت دیگر فرمول SUMPRODUCT عملیات ریاضی زیررا انجام میدهد:

=A2*B2 + A3*B3 + A4*B4

حال در نظر بگیرید اگر این مسئله به جای سه ردیف داده سیصد ردیف یا سه هزار ردیف داشت، حل این مسئله به صورت دستی به چه مدت زمان احتیاج دارد.

نحوه استفاده از sumproduct در اکسل، مثال های فرمولی

ضرب کردن مقادیر مختلف و بدست آوردن مجموع آنها ساده ترین کاری است که این تابع در اکسل انجام میدهد ولی این تنها کارکرد این تابع در اکسل نیست. زیبایی این تابع این است که میتواند بسیار فراتر از هدفی که برای آن ایجاد شده عمل کند. در بخش های دیگر این مطلب، مثال هایی عنوان شده است که کاربردهای پیشرفته تر SUMPRODUCT را به شما نشان میدهد. بنابراین دست از خواندن نکشید.

sumproduct با شرط های چندگانه

عموماً در اکسل برای حل یک مسئله راه حل های متفاوتی وجود دارد، اما زمانی که بخواهید آرایه های متفاوت را با هم مقایسه کنید استفاده از SUMPRODUCT ممکن است تنها روش موجود نباشد ولی بهترین روش خواهد بود. در این صورت شما می بایست بین فرمول ARRAY و SUMPRODUCT یکی را انتخاب کنید.

فرض کنید لیستی از اقلام را در ستون A دارید، ارقام پیش بینی شده فروش در ستون B و میزان فروش واقعی در ستون C قرار دارد. هدف شما این است که ببینید چند قلم کالا کمتر از پیش بینی فروش داشته اند. برای این کار از یکی از فرمول های SUMPRODUCT زیر استفاده کنید.

= SUMPRODUCT(–(C2:C10<B2:B10))

یا

= SUMPRODUCT((C2:C10<B2:B10)*1)

که در این فرمول C2:C10 فروش واقعی و B2:B10 فروش پیش بینی شده است.

ولی اگر بیش از یک شرط داشته باشید چه؟  برای مثال میخواهید بدانید سیب چند بار کمتر از پیش بینی، فروش داشته است؟ راه حل این است که یک شرط دیگر به فرمول sumproduct اضافه کنید:

= SUMPRODUCT(–(C2:C10<B2:B10),–(A2:A10="سیب"))

یا از ترکیب زیر استفاده کنید:

= SUMPRODUCT((C2:C10<B2:B10)* (A2:A10="سیب"))

حال دقیقه ای به این موضوع فکر کنید که فرمول های بالا چگونه کار میکنند. به نظر من این کار بسیار مفید است زیرا بسیاری از فرمول های SUMPRODUCT با همین منطق کار میکنند.

فرمول sumproduct با یک شرط

برای افرای که تازه با این تابع آشنا شده اند، این مسئله را میشکافیم و آن را تبدیل به فرمول های کوچکتری میکنیم که به صورت ردیف به ردیف نشان میدهد ستون C  چه مقدار از ستون B کمتر است:

=SUMPRODUCT(–(C2:C10<B2:B10))

در صورتی که در FORMULA BAR  این فرمول را انتخاب کنید و f9 را فشار دهید تا مقادیر زیر آن را مشاهده کنید، آرایه های زیر را خواهید دید:

= SUMPRODUCT(–{false;true;false,false,true,false,true,false,false})

 

چیزی که در اینجا داریم آرایه ای از مقادیر Boolian است که شامل true و false می شود که در آن true به این معنی است که شرط تعریف شده برقرار است و false به معنی عدم صحت شرط است.

دومنفی قبل از آن، که به صورت فنی double unary گفته میشود، true و false را تبدیل به 0و 1   میکند.: {0,1,0,0,1,0,1,0,0}

راه دیگر برای تبدیل مقادیر منطقی به مقادیر عددی ضرب کردن آن در یک است.

=SUMPRODUCT( (C2:C10<B2:B10)*1)

در هر دو شیوه از آنجایی که فقط یک آرایه در فرمول SUMPRODUCT است، به سادگی 1ها را در  آرایه جواب اضافه میکند و ما نتیجه دلخواه را بدست می آوریم.

فرمول sumproduct با چندین شرط

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

اگر به یاد داشته باشید ما با استفاده از فرمول زیر تعداد اختلاف فروش واقعی (ستون C) را از فروش پیش بینی (ستون B) شده بدست آوردیم.

 

= SUMPRODUCT(–(C2:C10<B2:B10),–(A2:A10="سیب"))

یا

 

= SUMPRODUCT((C2:C10<B2:B10)* (A2:A10="سیب"))

تنها تفاوت فنی بین این دو فرمول، تفاوت در شیوه تبدیل true  و false به مقادیر عددی است. در نتیجه، ما دو آرایه از 0 و 1 بدست می آوریم.

= SUMPRODUCT({0,1,0,0,1,0,1,0,0},{1,1,0,0,1,1,0,1,1}

عملیات ضرب که توسط تابع SUMPRODUCT انجام میگیرد این دو آرایه را تبدیل به یک آرایه میکند و از آنجایی که ضرب در صفر همیشه صفر میشود، 1 فقط زمانی بدست می آید که هر دو شرط صادق باشد و در نتیجه فقط این ردیف ها محاسبه میشود.

گرفتن میانگین، مجموع، میانگین سلول ها، همراه با شرط های چندگانه

در اکسل 2003 و ورژن های قدیمیتر، که دارای تابع های مشهور به IFs نیستند، یکی از کاربردهای مهم تابع SUMPRODUCT جمع کردن و شمردن سلول ها به صورت مشروط است. اما از اکسل 2007 به بعد مایکروسافت تعدادی از توابع  از جمله COUNTIF، SUMIF و … را برای این کار ایجاد کرده است.

ولی حتی در ورژن های جدید اکسل نیز، فرمول SUMPRODUCT میتواند گزینه جایگزین خوبی باشد. برای مثال، برای جمع کردن یا شمردن مشروط سلول هایی که دارای منطق OR هستن. در زیر مثال هایی آورده شده است که این کاربرد را به روشنی توضیح میدهند.

1. فرمول sumproduct با منطق And

فرض کنید مجموعه های داده زیر را در اختیار دارید، لیست مناطق در ستون A، لیست اقلام در ستون B و ارقام فروش در ستون C قرار دارد.

و شما میخواهید تعداد، مجموع و میانگین فروش سیب را در منطقه شرق بدست آورید.

در ورژن های اخیر اکسل میتوان به سادگی این کار را با استفاده از فرمول های SUMIF، COUNTIF و AVERAGEIF به دست آورد. در صورتی که به دنبال راه آسانتر نیستید یا از ورژن های 2003 به قبل اکسل استفاده میکنید، میتوانید نتایج خود را با استفاده از SUMPRODUCT به دست آورید.

برای شمارش فروش سیب در منطقه شرق :

= SUMPRODUCT(–(A:A12="شرق"),–(B2:B12,"سیب"))

یا

= SUMPRODUCT((A:A12="شرق")*(B2:B12,"سیب"))

برای جمع زدن میزان فروش APPLES در منطقه شرق:

= SUMPRODUCT(–(A:A12="شرق"),–(B2:B12,"سیب"),C2:C12)

یا

= SUMPRODUCT((A:A12="شرق")*(B2:B12,"سیب"),C2:C12)

 

برای بدست آوردن میانگین فروش سیب در منطقه شرق:

=SUMPRODUCT(–(A:A12="شرق"),–(B2:B12,"سیب"),C2:C12)/SUMPRODUCT(–A:A12="شرق"),–(B2:B12,"سیب"))

 

برای ایجاد انعطاف پذیری بیشتر در فرمول خود میتوانید با نوشتن اقلام و مناطق مورد نظر در سلول های جداگانه این کار را انجام دهید و سپس آدرس آن سلول ها را  به صورتی که در شکل زیر آمده است: در فرمول خود وارد کنید.

نحوه عمل تابع sumproduct در جمع زدن به صورت شرطی

با توجه به فرمول گذشته، احتمالاً تا به حال نحوه کار تابع SUMPRODUCT برای شمارش سلول ها، با شرط های چندگانه را فهمیده اید، در صورتی که این مطلب را فراگرفته باشید ، درک منطق جمع کردن با استفاده از این فرمول نیز بسیار ساده خواهد بود.

یادآوری میکنیم که از فرمول زیر برای بدست آوردن مجموع فروش سیب در منطقه شمال استفاده شد.

 

= SUMPRODUCT(–(A:A12="شمال"),–(B2:B12,"سیب"),C2:C12)

نتیجه فرمول بالا سه آرایه زیر خواهد شد:

= SUMPRODUCT({0;1;1;1;0;1;0;0;1;0;0},{1;0;0;1;0;1;0;0;1;0;1},{285;280;335;260;245;255;255;280;285;335;285})

در آرایه اول، 1 نشان دهنده شمال و 0 نشان دهنده دیگر مناطق است.

در آرایه دوم، 1 نشان دهنده سیب و 0 نشان دهنده دیگر محصولات است.

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

با توجه به اینکه ضرب در صفر همیشه صفر میشود و ضرب در 1 همیشه همان عدد میشود. آرایه آخر بدست خواهد آمد به این صورت که شامل اعداد واقعی و صفر میشود. در صورتی مقدار فروش نشان داده میشود که مقدار متناظر در دو آرایه اول 1 بوده باشد. به این معنی که هر دو شرط مشخص شده صحیح باشد در غیر این صورت 0 می شود:

بدست آوردن مجموع مقادیر بدست آمده نتیجه مورد نظر را بدست خواهد داد – مقدار کل سیب به فروش رسیده در منطقه شمال.

مثال 2. فرمول  sumproduct با منطق OR

جمع کردن یا شمارش سلول ها با منطق OR بین آرایه ها از (+) استفاده میکند.

در فرمول های SUMPRODUCT اکسل، هچنین در فرمول های Array، علامت جمع، شبیه به عملگر or کار میکند و در صورتی که یکی از شرط ها صحیح باشد، true را برمیگرداند.

برای مثال، برای شمارش مقدار کل سیب و لیمو به فروش رفته، بدون توجه به منطقه آن از این فرمول استفاده میشود:

= SUMPRODUCT(B2:B12="سیب")+(B2:B12="لیمو"))

که ترجمه آن به فارسی  میشود: سلول ها شمارش میشوند در صورتی که،

B2:B12 =”سیب”

باشند یا

 B2:B12 = “لیمو”

باشند.

برا بدست آوردن مجموع سیب و لیمو به فروش رسیده می بایست ستونی که دارای مقادیر فروش است را نیز به فرمول اضافه کرد:

= SUMPRODUCT(B2:B12="APPLES")+(B2:B12="LEMONS"),c2:c12)

تصویر زیر همین فرمول را در عمل نشان میدهد.

فرمول sumproduct با منطق and و or

در بعضی مواقع ممکن است نیاز باشد که با استفاده از منطق and و or به صورت همزمان، سلول ها را جمع کنید یا شمارش کنید . حتی در آخرین ورژن های اکسل نیز فرمول های IFs قادر به انجام چنین کاری نیستند.

یکی از راه حل ها، ترکیب دو یا چند تابع است برای مثال، SUMIFS+SUMIFS ، COUNTIFS+COUNTIFS.

راه حل دیگر استفاده از SUMPRODUCT به این صورت است که:

• علامت* بعنوان And به کار برود.
• علامت + بعنوان or به کار برده شود.

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

برای شمارش تعداد دفعاتی که سیب و لیمو در منطقه شمال به فروش رفته است میتوان از فرمول زیر استفاده کرد:

=countif((region=”شمال”) and ((قلم=”سیب”) or (قلم = “لیمو”)))

 

به کار بردن، SUMPRODUCT مناسب این فرمول به این شکل درخواهد آمد:

= SUMPRODUCT(A2:A12="شمال")*(B2:B12="سیب")+(B2:B12="لیمو")))

برای جمع زدن مقدار کل فروش این دو محصول نیز میتوانید ستون میزان فروش را به فرمول بالا اضافه کنید:

= SUMPRODUCT(A2:A12="شمال")*(B2:B12="سیب")+(B2:B12="لیمو")*C2:C12))

برای اینکه این فرمول ها کمی مختصر تر شود، میتوانید متغیرهای خود را در سلول های جداگانه قرار دهید –  برای مثال منطقه در F1، محصول در F2 و H2 و سپس با آوردن سلول ها در فرمول خود آن را خلاصه تر نمائید.

فرمول sumproduct برای بدست آوردن میانگین وزنی

در یکی از مثال های قبل، در مورد استفاده از SUMPRODUCT برای بدست آوردن میانگین شرطی بحث کردیم. یکی دیگر از کاربرد های SUMPRODUCT به دست آوردن میانگین وزنی در زمانی است که هرکدام از مقادیر دارای وزن خاصی است.

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

SUMPRODUCT(VALUES,WEIGHTS)/SUM(WEIGHTS)

 

با در نظر داشتن اینکه مقادیر در سلول های B2:B7 هستند و وزن ها در سلول های C2:C7 هستند، فرمول SUMPRODUCT به شکل زیر خواهد شد:

= SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)

تابع sumproduct بعنوان جایگزینی برای فرمول های ARRAY

حتی در صورتی که این مطلب را برای داشتن اطلاعات عمومی میخوانید و احتمالاً جزئیات آن از ذهنتان خارج خواهد شد ولی این نکته را حتماً به یاد داشته باشید که، ” تابع SUMPRODUCT در اکسل با آرایه ها سرو کار دارد” و از آنجایی که تابع SUMPRODUCT قدرت بیشتری نسبت به فرمول Array دارد میتواند جایگزین خوبی برای آن باشد.

مزیت این تابع در چیست؟ اساساً با استفاده از تابع SUMPRODUCT بهتر میتوانید فرمول های خود را مدیریت کنید و بعد از هر بار وارد کردن فرمول جدید یا اصلاح فرمول های قبلی نیازی به استفاده از کلیدهای ترکیبی ctrl+shift+alt نخواهد بود.

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

{=sum(len(range))}

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

= SUMPRODUCT(len(range))

تابع sumproduct در اکسل، مثال های فرمولی پیشرفته

• SUMPRODUCT برای vlookup حساس به حروف بزرگ و کوچک:

نحوه انجام یک جستجوی حساس به حروف ،و برای برگرداندن شماره کاراکترهای منطبق برای انجام یک جستجو که برای همه انواع داده مناسب باشد از ترکیب فرمول های match و index استفاده کنید.

• جستجوی دوراهی در اکسل:

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

• نحوه شمارش کاراکترها در اکسل:

فرمول هایی که تمام کاراکترها یا کاراکترهای خاص را در یک محدوده مشخص شمارش میکنند.

• SUMPRODUCT برای شمارش کلمات در اکسل

فرمول هایی برای شمردن کلمات خاص یا همه کلمات در یک محدوده مشخص

• شمارش مقادیر منحصر به فرد

تعداد مقادیر متفاوت را در یک محدوده نشان میدهد.

• شمارش مقادیر منحصر به فرد و دوتایی در یک محدوده

SUMPRODUCT/ countif برای شمارش مقادیر منحصر به فرد و دوتایی در یک محدوده در یک ستون یا بین دو ستون

• نحوه بدست آوردن مجموع داده ها به صورت ماهانه

فرمول های SUMPRODUCT/month  برای بدست آوردن مجموع داده ها به صورت ماهانه

• استخراج ارقام فقط از رشته های الفبا عددی

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