همه چیز در مورد توابع Sumif و sumifs
تابع Sumif چیست و چه کاربردی دارد ؟
تابع Sumif ، در اکسل ترکیبی از تابع Sum و تابع شرطی IF می باشد.
تابع Sumif یا (جمع شرطی) مجموع مقادیری که شرط خاصی درمورد آن صحیح می باشد را محاسبه و نمایش می دهد .
براي آموزش ساير توابع پرکاربرد مالي و حقوق و دستمزد از بسته کاربرد اکسل در حسابداري استفاده نماييد .
برای مثال فرض کنید طبق شکل زیر جدولی از میزان فروش محصولات در شهرهای مختلف داریم و می خواهیم جمع کل فروش تن ماهی ها را در کل شهرها بدست آوریم .
اگر از تابع SUM در نرم افزار اکسل برای مجموع استفاده کنیم جمع کل تعداد فروش ها بدست می آید که نتیجه ای که می خواهیم حاصل نمی شود .
پس برای جمع شرطی از تابع SUMIF که مجموع مقادیری که دارای شرط خاصی هستند را محاسبه می کند استفاده می کنیم .
در این مثال باید مقادیر ستون “تعداد فروش” را با هم جمع کنیم اما فقط مقادیری از این ستون را جمع می کنیم که در ستون “نام محصول” نام آنها ، تن ماهی است .
یعنی قسمت هایی که باید جمع کنیم در شکل زیر با رنگ آبی مشخص شده اند
.
فرمول تابع SUMIF بصورت زیر می باشد :
SUMIF(RANGٍٍE;CRITERIA;[SUM_RANGE])
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 می باشد.