همه چیز در مورد توابع Sumif و sumifs|آکادمي استاد آموز

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 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SUMIF(B2:E8;12)=

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

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

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″)=

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

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

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

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

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

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

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

SUM_RANGE:

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

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

CRITERIA_RANGE1:

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

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

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

CRITERIA1:

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

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

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

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

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

CRITERIA_RANGE2:

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

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

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

CRITERIA2:

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

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

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

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 می باشد.