تابع SUMIF در اکسل

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

در ادامه با ساختار و کاربرد های تابع sumif آشنا می شویم. توجه داشته باشد که در صورت درک کامل این تابع، برای استفاده از توابع دیگر این گروه از جمله countif (شمارش شرطی) و avarageif (میانگین شرطی) و غیره مشکلی نخواهید داشت. البته در نسخه 2019 اکسل توابع دیگری از جمله SMAXIF و MINIFS هم به این گروه اضافه شده اند که در مقاله دیگری آموزش داده شده است.

تابع SUMIF در اکسل و آرگومان ها

همانطور که روش به کارگیری فرمول ها را در مقدمات فرمول نویسی آموختیم، برای دسترسی به تابع SUMIF می توانیم در یک سلول علامت = و سپس چند حرف از این تابع را تایپ کنیم. سپس با نشان کردن تابع مورد نظر و فشردن کلید Tab فرمول را آماده دریافت آرگومان ها می کنیم.

همچنین می توانیم در تب Formulas، گروه Math &Trig را انتخاب و روی SUMIF کلیک کنیم. با این کار پنجره‌ای برای وارد کردن آرگومان ها ظاهر می‌شود.

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

=SUMIF(range, criteria, [sum_range])

همانطور که مشاهده می کنید این تابع سه آرگومان دارد، دو آرگومان اول ضروری هستند و سومی اختیاری است.
RANGE: محدوده ای است که شرط مورد نظر ما در آن مورد ارزیابی قرار می گیرد.
CRITERIA: شرطی است که باید تحقق یابد تا تعیین کند تابع روی کدام سلول ها اجرا شود.
SUM RANGE: سلول هایی که در صورت وجود شرط باید با هم جمع شوند.

توجه داشته باشید که شرط مورد نظر ما می تواند به صورت اعداد، متن، تاریخ، عبارت منطقی، یا دیگر توابع اکسل بیان شود.

تابع SUMIF با معیارهای متنی

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

مثال 1- فرض کنید می خواهیم در داده های جدول زیر، مقادیر در محدوده D2:D30 را به شرطی که سلول های مربوطه در محدوده B2:B30 دقیقاً Methyl باشند،جمع کنیم. بنابراین تابع SUMIF را به صورت زیر تکمیل می کنیم:

=SUMIF(B2:B30;”Methyl”;D2:D30)

اگر تمایل به ایجاد شرط داینامیک دارید می توانید در یک سلول دیگر Methyl را تایپ کنید و آدرس این سلول را بعنوان Criteria به تابع بدهید. سپس با تغییر Methyl به هر یک از کالاهای دیگر به راحتی می توانید مجموع مبلغ کل را برای آن کالا به دست آورید.

مثال 2- فرض کنید می خواهیم تابع SUMIF مقادیری را در محدوده D2:D30 در نظر بگیرد که سلول های متناظر در محدوده B2:B30 هر چیزی غیر از Methyl باشند. به این منظور از عملگر <> برای استثنا قرار دادن Methyl استفاده می کنیم و تابع را به صورت زیر می نویسیم:

=SUMIF(B2:B30;”<>Methyl”;D2:D30)

تابع SUMIF همراه با کاراکترهای WILD CARD

در صورتی که میخواهید با استفاده از یک شرط متنی عمل جمع را انجام دهید و در نظر دارید که از مطابقت جزئی استفاده کنید، می بایست از کاراکترهای WILD CARD استفاده کنید:

از کاراکترهای Wildcard زیر میتوان استفاده کرد.

مثال 3- تابع SUMIF مقادیر در محدوده D2:D30 را در صورتی که سلول های مربوطه در محدوده B2:B30 دقیقاً حاوی Carboplatin به علاوه یک کاراکتر هستند. علامت سؤال (؟) دقیقاً با یک کاراکتر مطابقت دارد.

مثال 4- تابع SUMIF زیر مقادیر موجود در محدوده D2:D30 را در صورتی که سلول های مربوطه در محدوده B2:B30 شامل یک هیچ یا چند کاراکتر به علاوه ml باشند. ستاره (*) این عمل را انجام می دهد.

تابع SUMIF در اکسل با معیارهای عددی

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

مثال 5- در داده های جدول زیر می خواهیم تابع SUMIF را به گونه ای به کار گیریم که برای سلول هایی از ستون “تعداد” که بیشتر از 5 باشد مجموع مبلغ کل را محاسبه کند. پس تابع SUMIF را به صورت زیر می نویسیم:

تابع SUMIF زیر نتیجه دقیقاً مشابه را می دهد. اما مشاهده می کنید که در قسمت Criteria عدد موجود در سلول E2 نوشته شده است که باید همراه با & نوشته شود. مزیت این کار این است که تابع شما به سلول E1 وابسته می شود و با تغییر این عدد شرط شما به صورت داینامیک تغییر می کند و نتیجه شرط تغییر می کند بدون نوشتن مجدد فرمول. از عملگر & برای پیوستن نماد “بیشتر از” به مقدار سلول C1 استفاده می شود.

تابع SUMIF در اکسل با معیارهای تاریخ

همچنین می توانید از تابع SUMIF در اکسل استفاده کنید تا سلول ها را بر اساس تاریخ هایی که معیارهای خاصی را برآورده می کنند جمع کنید.

مثال 5- می خواهیم تابع SUMIF فروش پس از 1 ژانویه 2019 را محاسبه کند.

بنابراین تابع SUMIF را به صورت زیر کامل می کنیم:

=SUMIF(A2:A30;”>”&DATE(2018,1,1);D2:D30)

توجه داشته باشید که تابع DATE در اکسل سه آرگومان می پذیرد: سال، ماه و روز.

همچنین برای محاسبه مجموع فروش امروز از تابع زیر استفاده می کنیم:

=SUMIF(B2:B30;TODAY();D2:D30)

نکاتی که باید در مورد تابع SUMIF بدانید!

1- تابع SUMIF آرگومان دوم که Criteria هست را در قالب متن می پذیرد. بنابراین اگر بخواهیم مقایسه مستقیم با یک عدد یا تاریخ داشته باشیم باید شرط را در داخل دابل کوتیشن (“”) قرار دهیم.

2- اگر آرگومان سوم که اختیاری است را به تابع ارائه ندهیم، تابع از همان Range استفاده می کند. پس در صورت متفاوت بودن محدوده های Range و Sumrange باید آرگومان سوم را وارد کنید.

3- شروع و پایان محدوده مشخص شده برای آرگومان های Range و Sumrange باید یکسان باشد. در غیر این صورت نتیجه صحیحی از تابع دریافت نمی کنید. برای درک بهتر به شکل زیر توجه کنید.