همه چیز در مورد توابع Sumif  و sumifs

تابع Sumif چیست و چه کاربردی دارد ؟

تابع Sumif ، در اکسل ترکیبی از تابع Sum و تابع شرطی IF می باشد.

تابع Sumif  یا (جمع شرطی) مجموع مقادیری که شرط خاصی درمورد آن صحیح می باشد را محاسبه و نمایش می دهد .

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

محاسبه مجموع فروش محصول با تابع sumif

محاسبه مجموع فروش محصول با تابع sumif

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

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

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

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

sumifمحاسبه مجموع فروش محصول با تابع

محاسبه مجموع فروش محصول با تابعsumif

فرمول تابع SUMIF بصورت زیر می باشد :

SUMIF(RANGٍٍE;CRITERIA;[SUM_RANGE])

آرگومان های تابع Sumif

آرگومان های تابع Sumif

RANGE:

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

می تواند شامل مقادیر عددی، متن و…. باشد.

در مثال بالا آرگومان اول تابع SUMIF شامل ستون نام محصول است که  ازسلول  B5  تا B12  با درگ انتخاب می کنیم .

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

یعنی مقادیر 300 ، 500 ، 640 از ستون میزان فروش را با هم جمع می شوند .

CRITERIA:

در این قسمت شرط تابع وارد می شود  .

می تواند عدد، متن ، ادرس سلول ، تابع و عبارات ریاضی باشد

وقتی شرط ، متن یا عبارات ریاضی می باشد حتما باید بین ”  ” قرار گیرد.

در این مثال شرط، “تن ماهی” است  که چون متن است باید حتما دربین دبل کتیشن ” ” قرار گیرد .

SUM_RANGE:

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

این آرگومان اختیاری می باشد .

در صورتی از این آرگومان استفاده می کنیم که آرگومان اول، (RANGE) شامل مقادیر عددی نباشد .

بنابراین آرگومان SUM_RANGE ، فقط مقادیر عددی را قبول می کند .

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

لذا برای محاسبه مجموع در این آرگومان باید مقدار عددی به عنوان ورودی وارد کنیم که شامل مقادیر ستون سوم ، میزان فروش که در بازه ( D5  تاD12)  قرار دارد .

در پایان فرمول مثال بالا بصورت زیر کامل می شود .

(D5:D12;”تن ماهی”;B5:B12)SUMIF=

در مثال بالا بجای آرگومان دوم که بصورت متنی وارد شد “تن ماهی” می توانیم آدرس خانه را هم درج کنیم .

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

(D5:D12;F1;B5:B12)SUMIF=

مثال ها ی مختلف از شرط در تابع sum if 

sample of sumif

حل مثال از تابع sumif

مثال دوم :می خواهیم مجموع نمرات کوچکتر از 10 را بدست آوریم.

آرگومان اول (RANGE)شامل کل نمرات می شود  (B2  تا E8)که قرار است شرط کوچکتر از 10 روی آنها بررسی شود .

آرگومان دوم  criteria شامل شرط می باشد که در این مثال شرط ، (مقادیر کوچکتر از 10) است .

عبارات 10> را در بین ” ” باید قرا دهیم . یعنی بصورت “10>” می نویسیم .

نکته1 :اگر از عملگرهای مقایسه ای مانند کوچکتر(>) ، بزرگتر (<)، بزرگتر مساوی (=<) ،کوچکتر مساوی (=>)،مساوی(=) و نامساوی (<>) در شرط استفاده کنیم حتما باید دربین ” ” قرارگیرد .

نکته 2:اگر از عبارات متنی در شرط استفاده کنیم حتما باید در بین ” ” قرار می دهیم .همانند مثال قبل که شرط متنی  را در بین “تن ماهی” نوشتیم .

در این مثال نیازی به آرگومان آخر SUM_RANGE  ( اختیاری ) نداریم . و فقط از آرگومان اول و دوم استفاده شد.چون مقدار آرگومان اول و سوم با هم برابر هستند پس می توانیم آرگومان سوم را خالی بگذاریم  .

نکته3:  زمانیکه مقدار آرگومان اول مقادیر عددی بود نیازی به نوشتن آرگومان اخر نیست .

فرمول این تابع در آخر بصورت زیر می شود :

SUMIF(B2:E8;”<10″)=

sample of sumif

حل مثال از تابع sumif

 

sample of sumif

حل مثال از تابع sumif

مثال سوم : می خواهیم جمع نمرات بزرگتر و مساوی 15 را بدست آوریم .

مقدار آرگومان اول (RANGE)، شامل کل نمرات B2 تا  E8 می شود .

مقدار آرگومان دوم (criteria )، “15=<” است.

مقدار آرگومان سوم (SUM_RANGE) چون مشابه آرگومان اول است و آرگومان اول هم عددی بود نیازی به وارد کردن نیست چون دقیقا مشابه آرگومان اول است .

فرمول مثال فوق بصورت زیر می شود :

SUMIF(B2:E8;”>=15″)

حل مثال از تابع sumif

حل مثال از تابع sumif

مثال چهارم: می خواهیم جمع نمرات مساوی 12 را بدست آوریم .

مقدار آرگومان اول (RANGE)، شامل کل نمرات B2 تا  E8 می شود .

مقدار آرگومان دوم (criteria )، 12 است . و می توانیم عملگر مساوی را ننویسیم.

اگر از عملگر مساوی استفاده نکنیم نیازی به ” ” نداریم .

مقدار آرگومان سوم (SUM_RANGE) چون دقیقا مشابه آرگومان اول است نیازی به وارد کردن ندارد .

نکته :مقادیر عددی نیازی به استفاده از ” ” ندارد .

 

فرمول مثال فوق بصورت زیر می شود :

SUMIF(B2:E8;12)=

حل مثال از تابع sumif

حل مثال از تابع sumif

اگر از عملگر مساوی استفاده کنیم .

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

SUMIF(B2:E8;”=12″)=

SUMIF(B2:E8;”=”& 12)=

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

فرض کنید عدد 12 در سلول C4 قرار دارد.

SUMIF(B2:E8;C4)=

SUMIF(B2:E8;”=C4″)=

SUMIF(B2:E8;”=”&C4)=

تمام  فرمول بالا صحیح می باشد و مساوی  فرمول اول است.

مثال پنجم: می خواهیم جمع نمرات مخالف 12 را بدست آوریم .

مقدار آرگومان اول (RANGE)، شامل کل نمرات B2 تا  E8 می شود .

مقدار آرگومان دوم (criteria )، “12<> ” می باشد .

مقدار آرگومان سوم (SUM_RANGE) همانند مثال های قبل ، چون دقیقا مشابه آرگومان اول است نیازی به وارد کردن ندارد .

فرمول مثال فوق بصورت زیر می شود :

SUMIF(B2:E8;”<>12″)=

حل مثال از تابع sumif

حل مثال از تابع sumif

تابع sumifs چیست و چه کاربردی دارد؟

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

فرمول تابع SUMIFS بصورت زیر می باشد.

SUMIFS(SUM_RANGE;CRITERIA_RANGE1;CRITERIA1;[CRITERIA_RANGE2];[CRITERIA2];…..)=

 سه آرگومان اول اجباری و بقیه آرگومان ها  اختیاری هستند .

در این تابع 127 شرط  می توانید استفاده کنید.

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

تابع sumifs

تابع sumifs

SUM_RANGE:

آرگومان اول تابع SUMIFS ، مجموع یک بازه از اعداد را محاسبه می کند .

در این مثال ، مقادیر ستون میزان فروش از آدرس   D2 تا D11  که در فصل تابستان  باشند جمع بسته می شوند .

CRITERIA_RANGE1:

آرگومان دوم تابع  SUMIFS ،بازه ای که شرط اول تابع روی آن ارزیابی می شود .

در این مثال میزان فروشی که در فصل تابستان باشند جمع بسته می شوند بازه ای که شرط اول تابع روی آن اعمال می شود شامل ستون نام فصل می باشد . (A2 تا A11)

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

CRITERIA1:

آرگومان سوم  تابع SUMIFS ،شامل شرط اول تابع است .

شرط اول تابع فصل تابستان می باشد.

که می توانیم  عبارت متنی  “تابستان ” را بنویسیم.

و یا آدرس سلولی که عبارت  تابستان در آن نوشته شده است  مثلا C7 را انتخاب کنیم .

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

CRITERIA_RANGE2:

شامل بازه ی دوم ، که شرط دوم تابع روی آن اعمال و صحت شرط چک می شود.

اگر شرط دومی هم داشته باشیم می توانیم بازه ای که شرط دوم روی آن اعمال می شود را انتخاب کنیم .

در این مثال شرط دوم نداشتیم.

CRITERIA2:

شامل شرط دوم تابع می باشد .

وبه ترتیب می توانیم براساس شرط های مساله بازه های دیگری که شرط های  بعدی روی آن اعمال می شود و آرگومان بعدی آن که شامل شرط است و… داشته باشیم .

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

حل مثال از تابع sumifs

حل مثال از تابع sumifs

SUM_RANGE:

این آرگومان مجموع اعداد ستون فروش (D2 تا D11 )که تمام شرط ها ی تابع Sumifs در آن صحیح باشد را محاسبه می کند .

CRITERIA_RANG1:

شامل بازه ای که شرط اول تابع Sumifs   در این بازه  مورد ارزیابی قرار می گیرد .

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

CRITERIA1:

شرط اول تابع Sumifs  ، فصل پاییز می باشد .

که می توانیم متن “پاییز” را در قسمت شرط اول بنویسیم .

یا آدرس خانه ای که مقدار پاییز در آن نوشته شده است را انتخاب کنیم مثلا F2

CRITERIA_RANG2:

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

در این مثال ،شرط دوم بستی است و بازه مورد بررسی کل بازه نام محصول می باشد از خانه B2 تا B11 می باشد.

CRITERIA2:

شرط دوم تابع ، محصول بستنی می باشد.

که می توان نام محصول یا آدرس سلول مربوط به محصول بستنی را استفاده کنیم مثلا F5.

CRITERIA_RANG3:

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

شرط سوم ، نام فروشنده B می باشد .

بنابراین ستون نام فروشنده از خانه  C2 تا C11 مورد ارزیابی قرار می گیرد .

CRITERIA3:

شرط سوم تابع Sumifs ، نام فروشنده B می باشد.