دوشنبه , ۱ مهر ۱۳۹۸
صفحه نخست / توسعه مهارتهای اکسل / محاسبات شرطی با استفاده از فرمول SUMPRODUCT

محاسبات شرطی با استفاده از فرمول SUMPRODUCT

همه ما می دانیم که فرمول SUMIF برای جمع بستن چندین داده با قید یک شرط بکار میرود. ما همانطور که می­دانید این فرمول محدودیتهای خاص خودش را دارد. یعنی فقط می توانیم یک شرط برای آن تعیین کنیم. از طرفی برای محاسبات مجموع با قید چند شرط نیز ار فرمول SUMIFS استفاده می کنیم. این دو فرمول بسیار کاربردی و شناخته شده ای هستند. اما در اینجا میخواهیم روشهای دیگر محاسبات شرطی را با شما در میان بگذاریم. فرض کنید جدول داده هایمان به شکل زیر باشد:

 

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

همونطور که می دونید SUMPRODUCT لیستی از اعداد رو میگیره و در هم ضرب میکنه و مجموع رو به شما برمیگردونه.

خب ما هم سعی کردیم از سلولهایی که شامل متن هستند لیستی از اعداد درست کنیم که در نهایت بتونیم به نتیجه مورد انتظار برسیم:

=SUMPRODUCT(–($B$7:$B$16=$G$25);–($C$7:$C$16=$H$25);$D$7:$D$16)

این فرمول چطور کار میکنه؟

در این فرمول سه آرایه در هم ضرب شده اند. اجازه بدهید هر آرایه را جداگانه توضیح بدهیم:

آرایه اول فرمول  ($B$7:$B$16=$G$25)–

قسمت داخل پرانتز این نتیجه را برمیگرداند

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE} و دو علامت منفی پشت پرانتز برای این است که نتیجه نهایی مثبت باشد. که با احتساب علامتهای منفی پشت پرانتز نتیجه نهایی آرایه اول عبارتست از: {۱;۰;۰;۰;۱;۰;۰;۰;۰;۱}

آرایه دوم فرمول  ($C$7:$C$16=$H$25)–

قسمت داخل پرانتز این نتیجه را برمیگرداند

 

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE} و دو علامت منفی پشت پرانتز برای این است که نتیجه نهایی مثبت باشد. که با احتساب علامتهای منفی پشت پرانتز نتیجه نهایی آرایه اول عبارتست از: {۱;۰;۰;۰;۱;۰;۰;۱;۰;۱}

آرایه سوم فرمول $D$7:$D$16

آرایه سوم هم شامل اعداد ستون فروش می باشد :{۱۲۷;۱۳۹;۱۶۴;۱۵۳;۱۱۱;۹۸;۱۴۷;۱۲۳;۱۳۵;۱۱۸}

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

=SUMPRODUCT({1;0;0;0;1;0;0;0;0;1};{1;0;0;0;1;0;0;1;0;1};{127;139;164;153;111;98;147;123;135;118})

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

=(۱*۱*۱۲۷)+(۰*۰*۱۳۹)+(۰*۰*۱۶۴)+(۰*۰*۱۵۳)+(۱*۱*۱۱۱)+(۰*۰*۹۸)+(۰*۰*۱۴۷)+(۰*۱*۱۲۳)+(۰*۰*۱۳۵)+(۱*۱*۱۱۸)

 

در نتیجه حاصلجمع قسمتهای قرمز رنگ برابر با صفر و حاصلجمع قسمتهای سبز رنگ برابر با ۳۶۵ خواهد بود.


روشهای دیگر برای محاسبه این مثال:

محاسبات خاص و تریک های جالب با استفاده از فرمول sumifs

محاسبه مجموع فروش بین دو تاریخ مشخص

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

 

 

 

پاسخی بگذارید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

11 − یک =

Time limit is exhausted. Please reload CAPTCHA.