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

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

در مثال قبل مجموع فروش بین دو تاریخ را با استفاده از فرمول SUMIFS محاسبه کردیم. و میخواهیم همان مساله را با استفاده از فرمول SUMPRODUCT محاسبه نماییم.  اجازه بدهید یکبار دیگر مساله را باهم مرور کنیم:

فرض کنید کارشناس واحد برنامه ریزی فروش هستید و موظفید اطلاعات فروش روزانه محصولات را ثبت و تحلیل کنید. (بعدها نحوه تبدیل تاریخ های میلادی به شمسی را خواهید آموخت.) فرض کنید اطلاعاتی مانند جدول زیر در دست داشته باشید و از شما خواسته شده است مجموع فروش هفته دوم تیرماه را سریعا محاسبه کنید. یک راه ساده این است که بصورت دستی اینکار را انجام دهید. این کار وقت گیر و البته ممکن است با خطا همراه باشد.

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

=SUMPRODUCT(($B$6:$B$96>=$B$7)*($B$6:$B$96<=$B$14);$D$6:$D$96)

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

محدوده اول فرمول یعنی ($B$6:$B$96>=$B$7)*($B$6:$B$96<=$B$14) شامل دو قسمت است که در همدیگر ضرب شده اند.

قسمت اول B$6:$B$96>=$B$7 بدین معنی است که تاریخ های بعد یا مساوی با سلول B7 را در نظر میگیرد و قسمت دوم $B$6:$B$96<=$B$14 بدین معنی است که تاریخ های قبل یا مساوی با سلول B14 را در نظر میگیرد.

قسمت اول و دوم مجموعه ای از TRUE و FALSE ها را برمیگرداند که همه TRUEها معادل یک و همه FALSEها معادل صفر هستند:

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

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

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

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

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

{۰;۱;۱;۱;۱;۱;۱;۱;۱;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰;۰}

همانطور که مشاهده میکنید تنها در تاریخهای مورد نظر اعداد مساوی با یک و بقیه تاریخ ها صفر درج شده است. تا اینجا فقط آرایه اول فرمول SUMPRODUCT که همان تاریخها هستند را ساخته ایم و آرایه دوم یعنی اعداد فروش مانده است. حالا فقط کافیست ستون مربوط به فروش را نیز اضافه نماییم یعنی $D$6:$D$96

یعنی در قسمت دوم خواهیم داشت:

{۷۳۶۴.۵;۶۴۷۰.۵;۶۴۴۲;۷۳۶۳;۶۱۸۵.۵;۶۳۹۱.۵;۶۸۱۴.۵;۵۶۱۶.۵;۵۵۳۵;۷۱۹۴;۶۹۸۸.۵;۵۳۳۷.۵;۶۷۷۱.۵;۵۲۷۳.۵;۷۴۳۹;۷۰۴۴.۵;۶۶۴۹;۵۵۱۴.۵;۵۶۹۸.۵;۵۲۸۸.۵;۵۰۶۱;۶۲۷۰.۵;۶۰۳۶.۵;۶۶۷۲;۶۳۴۲;۶۲۱۵.۵;۵۶۲۰;۵۶۲۳.۵;۵۳۴۶;۶۱۹۱.۵;۵۵۷۰;۶۵۳۹;۷۴۱۵;۶۳۹۰;۵۲۳۹.۵;۶۷۶۵.۵;۶۱۶۱;۷۳۶۱;۵۳۳۸;۵۷۴۳;۶۲۹۳;۵۱۶۶.۵;۵۷۹۲.۵;۵۲۷۱;۶۵۸۵.۵;۵۰۹۱;۵۶۴۴.۵;۵۳۸۶;۵۷۶۹.۵;۵۵۶۴;۷۱۳۸;۶۵۱۰.۵;۶۲۱۶.۵;۵۶۴۰.۵;۵۴۶۲;۷۰۶۲.۵;۷۴۱۴.۵;۶۳۲۲;۵۶۰۰.۵;۷۲۶۱;۶۵۹۷;۵۸۸۶.۵;۵۶۲۲;۶۸۶۲.۵;۵۱۴۵;۶۹۸۶.۵;۵۲۹۹.۵;۵۲۶۳;۶۵۲۴;۵۵۹۳;۷۳۶۰;۶۸۹۲.۵;۷۴۹۵;۶۰۸۸;۶۱۸۶;۵۳۱۰.۵;۷۳۴۴.۵;۵۲۱۷;۷۱۶۰.۵;۵۴۵۰.۵;۶۷۶۱.۵;۷۱۰۰;۵۸۱۰.۵;۷۳۶۲.۵;۶۱۴۸.۵;۶۷۱۸;۷۳۲۲.۵;۷۲۶۱;۶۷۸۷.۵;۶۲۶۲;۵۸۴۶.۵}

حالا تصویر واضح تری داریم:

=SUMPRODUCT({0;1;1;1;1;1;1;1;1;0;….;0};{7364.5;6470.5;6442;7363;6185.5;6391.5;6814.5;5616.5;5535;……;5846.5}

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

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

در نتیجه همه قسمتهای قرمز رنگ حاصلی برابر با صفر و قسمتهای سبز رنگ که در محدوده شرط هستند نتیجه نهایی را خواهند ساخت. جواب نهایی در این سوال ۵۰۸۱۹ خواهد بود.

نکته: با توجه به اینکه فرمولی که در اینجا استفاده شد جزو فرمولهای آرایه ای می باشد در نتیجه در نهایت باید از Alt+Shift+Enter استفاده نماییم.

دیدگاهتان را بنویسید

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

3 × پنج =

Time limit is exhausted. Please reload CAPTCHA.