جمع شرطي Sumif و sumifs در اكسل

پنجشنبه ۰۴ خرداد ۹۶ | ۰۹:۵۹ ۱,۳۸۸ بازديد

تابع SUM عمليات جمع زدن را انجام ميدهد. اما تابع SUMIF عمليات جمع زدن با داشتن شرط دلخواه را انجام مي دهد.

شكل كلي اين تابع به شكل (SUMIF(range,criteria,sum_range=مي باشد.

در اين تابع range يك سلول يا محدوده اي از سلولها مي باشد. Criteria شرطي است كه بايد سلول يا محدوده سلولهاي range داشته باشند و sum_range جمع سلول يا محدوده سلولها مي باشد به شرطي كه اين محدوده داراي شرط خواسته شده باشد.

به عنوان مثال در يك فروشگاه لوازم التحرير تعدادي فروشنده فعاليت مي كنند. گزارش فعاليت آنها به شرح جدول ذيل مي باشد.

اكنون مي خواهيم با استفاده از تابع SUMIF به سئوالات ذيل پاسخ دهيم.

1- تعداد كل كتابهاي فروخته شده توسط تمام فروشندگان چند جلد بوده است؟

2- قيمت كل مدادتراشهاي فروخته شده چند ريال مي باشد؟

3- مبلغ كل فروش آرش چند ريال مي باشد؟

4- مقدار فروش كل اقلامي كه تعداد آنها از 15 عدد بيشتر باشد؟

براي جمع زدن كل كتابهاي فروخته شده فرمول را به صورت زير مي نويسيم.

=SUMIF(P2:P14,”=كتاب”,R2:R14)

همانطور كه در شكل نشان داده شده است به جاي range محدوده P2:P14 و به جاي criteria يا شرط كلمه “كتاب=” و به جاي sum_range محدوده R2:R14 قرار گرفته است.

به زبان ساده تر: در صورتي محدوده R (تعداد فروخته شده) را جمع بزن كه در ستون P(شرح كالا) كلمه كتاب وجود داشته باشد.

تذكر: شرط بايد در داخل علامت ” ” قرار گيرد.

نكته: اگر پس از نوشتن فرمول اسم يكي از اقلام ديگر را به كتاب تغيير دهيد مشاهده مي كنيد كه تعداد آن به جمع اضافه خواهد شد (و برعكس)

براي جمع زدن قيمت كل مدادتراشهاي فروخته شده اين بار نيز به جاي range ستون شرح را انتخاب نموده و criteria را معادل كلمه “مدادتراش =” قرار مي دهيم و به جاي sum_range ستون قيمت كل را انتخاب مي كنيم.

(T2:T14,”مدادتراش=”,SUMIF(P2:P14=

براي محاسبه مبلغ كل فروش (آرش) به جاي range ستون فروشنده را انتخاب نموده و criteria را معادل كلمه “آرش=” قرار مي دهيم و به جاي sum_range ستون قيمت كل را انتخاب مي كنيم.

(T2:T14,”آرش=”,SUMIF(Q2:Q14=

براي جمع زدن اقلامي كه تعداد فروخته شده آنها بيش از 15 عدد باشد فرمول را به شكل زير مي نويسيم.

(SUMIF(R2:R14,”>15″,T2:T14=

براي محاسبه جمع فروش اقلامي كه تعداد فروخته شده آنها بيش از 15 عدد باشد به جاي range ستون تعداد فروخته شده را انتخاب نموده و criteria را معادل ” 15<” قرار مي دهيم و به جاي sum_range ستون قيمت كل را انتخاب مي كنيم.

يادآوري: تابع sumif براي جمع زدن مواردي كه داراي يك شرط باشند به كار ميرود. اگر تعداد شروط دو يا بيشتر باشند از تابع sumifs استفاده ميگردد كه در مبحثي جداگانه به آن پرداخته خواهد شد.

گفتيم كه اين تابع براي جمع زدن يك ناحيه از اعداد با اعمال شرط خاصي به كار مي رود. اين تابع فقط يك شرط را مي پذيرد. حال اگر بخواهيم جمع را با اعمال بيش از يك شرط داشته باشيم بايد از تابع SUMIFS استفاده نماييم.

شكل كلي اين تابع به صورت

[SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2=

در اين تابع بر خلاف SUMIF اول بايد محدوده اي را كه قصد جمع زدن داده هاي آن را داريم (داده هاي نوع عديي) وارد نماييم و سپس محدوده هاي شرط را تعيين كنيم.

در مثال شكل زير نحوه انجام كار مشخص شده است.

مي خواهيم جمع كل مبلغ فروش فروشنده اي به اسم “علي” را به شرطي كه تعداد كل فروشها بيشتر از 10 عدد باشد بدست آوريم.

همانطور كه ملاحظه ميگردد، در اين مثال بايد دو شرط را با هم داشته باشيم .آموزش اكسل شرط اول نام فروشنده كه بايد “علي” باشد و شرط دوم اينكه تعداد فروش از 10 عدد بيشتر باشد.

همانطور كه در جدول ديده مي شود فرمول بايد جمع كل فروش رديف هاي 4 و 13 جدول را نمايش دهد.

فرمول را به صورت زير وارد مي كنيم.

( “R2:R14,”>10,”علي=”,SUMIFS(T2:T14,Q2:Q14=