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

جمع و ضرب در اکسل : اگر با فرمول ‌نویسی در اکسل آشنا باشید و آن را در کاربرگ‌هایتان استفاده کرده باشید، متوجه خواهید شد که یکی از هنرهای افراد در این است که هرچه می‌توانند فرمول نویسی را کوتاه‌تر بکنند.  مزایای این روش این است که اولاً حجم فرمول‌نویسی کاهش می‌یابد.، دوماً حجم فایل مدنظر کم شده و در نهایت محاسبات با دقت و سرعت بیشتری انجام می‌شود.  تابع جمع و ضرب یا همان SUMPRODUCT یکی از توابع کاربردی اکسل می باشد که شما را در راه رسیدن به مزایای فرمول نویسی کوتاه کمک می‌کند.

 

 

تابع جمع و ضرب در اکسل

تابع SUMPRODUCT در  دسته توابع Excel Math و Trigonometry طبقه بندی می شود. این تابع اجزای متناظر یک آرایه داده شده را ضرب کرده و سپس مجموع ضرب ها را به عنوان خروجی برمی‌گرداند. از تابع جمع و ضرب برای محاسبه میانگین وزن دار استفاده می‌شود.

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

این تابع همچنین به محاسبه داده ها با چندین معیار نیز کمک می‌کند که در بخش‌های بعدی به طور مفصل در این مورد صحبت خواهد شد.

ساختار(Syntex) استفاده از تابع  SUMPRODUCT در اکسل

ساختار(Syntex) استفاده از تابع   SUMPRODUCTدر اکسل به شرح زیر می‌باشد:

 

=SUMPRODUCT(array1,[array2],[array3],…)

این تابع اجزای (سلول های) متناظر در آرایه را در یکدیگر ضرب کرده و سپس آن‌ها را با یکدیگر جمع می زند.

تابع جمع و ضرب از آرگومان های زیر استفاده می‌کند:

پارامتر اجباری:

Array1

(آرگومان لازم) – این اولین آرایه یا دامنه‌ای است که قراراست ضرب شده و سپس جمع بشود.

پارامترهای اختیاری:

Array2, Array 3 ,…

وارد کردن این آرایه‌ها اختیاری بوده و تعداد این آرگومان ها می تواند از ۲ تا ۲۵۵ آرگومان باشد.

  

 

مثال‌های کاربردی از تابع جمع و ضرب

مثال اول

داده‌های زیر را درنظر بگیرید.

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

علت استفاده از دو علامت منفی در تابع جمع و ضرب اکسل این است که اکسل را وادار به تبدیل مقادیر True و False به ۱ و ۰ بکنیم.

یک نمایش مجازی از دو آرایه‌ای که توسط تابع جمع وضرب پردازش می‌شوند در عکس زیر آورده شده است.

آرایه‌ی اول شامل مقادیر TRUE/FALSE بوده که این مقادیر خروجی عبارت شرطی C4:C12=”WEST” هستند و دومین آرایه شامل محتوای D4:D12 می‌باشد. هر مولفه در آرایه اول در مولفه متناظر در آرایه دوم ضرب می‌شود.

اگرچه، در این حالت خروجی تابع جمع و ضرب اکسل برابر صفر استً چون که با مقادیر TRUEو FALSE به خاطر غیرعددی بودنشان، به مانند عدد صفر رفتارشده است. بنابراین باید این  مقادیر به ۰ و۱ تبدیل شوند و در اینجا دوگانه منفی یاری‌رسان می‌شود و به جای TRUE یک و به جای False مقدار عددی صفر را درنظر می‌گیرد..

و نتایج حاصل عبارتند از:

مثال دوم: محاسبه میانگین وزن‌دار با تابع جمع و ضرب

یکی از کاربردهای رایج تابع ‌SUMPRODUCT در اکسل، محاسبه میانگین وزن دار برای زمانی که هر مقدار وزن مخصوص به خود را دارد، است.

داده‌های زیر را در نظر بگیرید:

باتوجه به تصویر زیر، مقادیر در سلول‌های C2:C8  و وزن متناظر با آن‌ها در سلول‌های  D2:D8 قرار دارند، محاسبه میانگین وزن‌دار به شرح زیر می باشد:

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

تابع جمع و ضرب با چندین معیار در اکسل

SUMPRODUCT با چندین معیار در اکسل به مقایسه آرایه‌های مختلف با چندین معیار کمک می‌کند.

1. ساختار استفاده از تابع جمع و ضرب در اکسل با چندین معیار مشابه قبل است اما با این تفاوت که در این‌جا

چندین معیار برای چندین دامنه داشته و سپس ضرب‌ها جمع می‌شوند.

2. در هنگام استفاده از تابع جمع و ضرب در اکسل با چندین شرط، بایستی از دو علامت منفی(–) استفاده کنید. این دوعلامت منفی از نظر فنی عملگر دوتایی unary نیز نامیده می‌شود. منفی دوگانه، توابع

(False=0; True=1)Boolean را به مقدار عددی صفر و یک می‌برد.

ساختار SUMPRODUCT با یک معیار

=sumproduct(–(array1 <Condition> array2)

یا

=sumproduct((array1 <Condition> array2)*1)

ساختار SUMPRODUCT با چندین معیار

 

=sumproduct((array1 <Condition1> array2)*(array3 <condition2>))

 

• می توان به جای تابع جمع و ضرب در اکسل با چندین شرط از  فرمول‌هایی مانند COUNTIF ، SUMIFو … استفاده کرد.
• از تابع SUMPRODUCT در اکسل می‌توان برای ایجاد فرمول‌های پیچیده ای که تمام آرایه های  هردوی سطرها و ستون‌ها را جمع کند، استفاده نمود.
• این تابع با عملگرهای منطقی مثل ANDو ORو.. قابل استفاده است.

آشنایی با عملکرد تابع جمع و ضرب در اکسل با چندین شرط در قالب مثال‌های متنوع

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

مثال اول

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

(Plantinum)فروخته شده فروش کمتری نسبت به تعداد برنامه ریزی شده داشته اند؟

در این حالت، ما دو شرط داریم: اولاً، پیدا کردن تعداد محصولاتی که تعداشان کمتر از تعداد برنامه ریزی شده می‌باشد و ثانیاً، این تعداد بایستی  فقط مربوط به محصول پلاتین  باشد.

حالا نوبت استفاده از تابع جمع و ضرب با چندین شرط فرا می رسد.

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

مثال دوم

در این مثال ستون دوم جدول شامل لیستی از محصولات یک کمپانی بوده و لیست مناطق جغرافیایی در ستون سوم وارده شده و در ستون چهارم تعداد محصولاتی که برای فروش برنامه ریزی شده اند آورده شده و در نهایت ستون پنجم تعداد واقعی محصولات به فروش رفته می‌باشد.

این شرکت می‌خواهد بداند که چه تعداد از محصولات پلاتین فروخته شده در منطقه شمال تعداد کمتری از تعداد برنامه ریزی شده را داشته اند؟

داده‌های مدنظر در عکس زیر قابل مشاهده‌است.

در این حالت ما با ۳ شرط روبرو هستیم:

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

برای رسیدن به جواب مطلوب از تابع SUMPRODUCT با چندین معیار در اکسل استفاده می‌کنیم.

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

نکاتی در مورد تابع جمع و ضرب در اکسل

1. کاراکترهای Wild card مانند ستاره (*) ، علامت سوال (؟)در هنگام استفاده از فرمول SUMPRODUCT معتبر نیستند.
2. در همه آرایه‌ی فرمول SUMPRODUCT بایستی تعداد ردیف ها و ستون ها به همان اندازه باشد. در غیر این صورت ، خطا برمی‌گرداند به عبارت بهتر در تابع جمع و ضرب در اکسل، آرایه ها بایستی دارای ابعاد مساوی باشند. در غیر این صورت تابع SUMPRODUCT مقدار خطای#VALUE!  را در خروجی نشان می‌دهد.

۳٫تابع جمع و ضرب در اکسل با تمامی مقادیر غیرعددی مانند عدد صفر رفتار‌می‌کند.

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

5. همان‌طور که در مثال مشاهده شد، از تابع جمع و ضرب در اکسل در محاسبه میانگین وزن‌دار استفاده می‌شود.
6. در آخرین نسخه‌های بالاتر از اکسل ۲۰۰۷، تابع جمع و ضرب حداکثر ۲۵۵ کارکتر می‌پذیرد این در حالی است که در نسخه های قبلی اکسل این سقف برابر عدد ۳۰ بود.
7. از آن‌جایی که تست‌های منطقی در آرایه‌ها مقادیر True و False ایجاد می‌کنند، بهتر است که این مقادیر به ۰ و ۱ تبدیل شود.
8. از SUMPRODUCT و MONTH می‌توان برای به دست آوردن مجموع مقادیر برای یک ماه خاص استفاده کرد.

۹٫تابع جمع و ضرب در اکسل اغلب می‌تواند از نتیجه توابع دیگر در اکسل به طور مستقیم بهره ببرد.
این تابع معمولا ازتابع LEN و یا توابع دیگری همچون ISBLANK ، ISTEXT ، VLOOKUP و..استفاده می‌کند.

در قسمت بعدی در مورد این ویژگی بیشتر صحبت می کنیم.

 

 

جستجوی دو طرف در اکسل

تابع SUMPRODUCT در جستجوی مقداری در اشتراک یک سطرو ستون مشخص شده کمک می‌کند.

شمارش کاراکتر ها در اکسل

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

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

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

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

استفاده از توابع SUMPRODUCT و COUNTIF به شمارش مقادیر تکراری و مقادیر منحصر به فرد در یک ستون یا بین دو ستون کمک می‌کند.

استفاده از تابع جمع و ضرب در اکسل به عنوان تابع VBA

تابع جمع و ضرب در اکسل  می‌تواند به عنوان تابع VBA مورد استفاده قرار بگیرد. بدین منظور داریم:

Sub abc()
MsgBox Evaluate(“=SUMPRODUCT(($A$1:$A$5=””Tanuj””)*($B$1:$B$5))”)
End Sub

تا به این جای کار، شما با تابع جمع و ضرب در اکسل و مزایای آن آشنا شدید و بدون شک به عنوان یک تحلیلگر بارها و بارها از این تابع برای بهره برداری از مزایایش استفاده خواهیدکرد. لازم به ذکر است که برای داشتن تجربه فرمول نویسی بهتر، بایستی به نکات ریزی که در استفاده از این تابع ذکر شد توجه کنید.