آموزش تابع SUMIF و SUMIFS در اکسل | تفاوت، مثال و نکات حرفه ای جمع شرطی

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

در این آموزش از سایت اهداف سبز با ما همراه باشید تا به صورت کامل با نحوه استفاده از تابع SUMIF و SUMIFS در اکسل آشنا شوید، تفاوت آن ها را درک کرذه و مثال های عملی و کاربردی این توابع را ببینید.

تابع SUMIF در اکسل چیست؟

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

نحوه استفاده از تابع SUMIF

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

ساختار کلی این تابع به صورت زیر است:

=SUMIF(range, criteria, [sum_range])

=SUMIF(range, criteria, [sum_range])

• range: محدوده ای است که شرط روی آن اعمال می شود.

• criteria: شرطی که باید بررسی شود (مثل “>100” یا “=تهران”).

• sum_range: محدوده ای که قرار است جمع شود. اگر وارد نشود، همان range جمع می شود (اختیاری).

مثال عملی از استفاده تابع SUMIF

همان طور که در مثال زیر مشاهده می کنید، فروش تعدادی از شهرها در تاریخ های متختلف آورده شده است. حال فرض کنید می خواهیم جمع فروش هر شهر در کل تاریخ هایی که فروش داشته است را در جدولی جداگانه محاسبه کنیم. برای این کار کافی است از تابع SUMIF استفاده کنیم. به این صورت که در بخش اول این تابع، محدوده شهرها یعنی ستون “شهر”، در بخش دوم شهر مورد نظر که در مثال ما به ترتیب سلول های ستون F هست و در بخش سوم تابع، محدوده ستون “مبلغ فروش” را انتخاب می کنیم.

تابع SUMIFS در اکسل چیست؟

تابع SUMIFS نسخه پیشرفته‌تری از SUMIF است و زمانی استفاده می شود که بخواهیم جمع را بر اساس چند شرط همزمان انجام دهیم.

نحوه استفاده از تابع SUMIFS

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

ساختار کلی این تابع به صورت زیر است:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range: محدوده ای که باید مقادیر آن جمع شوند.

• criteria_range1: محدوده ای که شرط اول در آن بررسی می شود.

• criteria1: شرط اول

• و همین طور می توان چندین جفت شرط دیگر هم اضافه کرد.

مثال عملی از استفاده تابع SUMIFS

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

• بخش اول تابع: محدوده ستون “مبلغ فروش” یا همان D2:D15
• بخش دوم تابع: محدوده شهرها یعنی ستون “شهر” یا همان B2:B15
• بخش سوم تابع: شهر مورد نظر که در مثال ما به ترتیب سلول های ستون G هست (مثلاً G5 و G6 و …)
• بخش چهارم تابع: محدوده محصول ها یعنی ستون “محصول” یا همان C2:C15
• بخش پنجم تابع: محصول مورد نظر که در مثال ما به ترتیب سلول های ستون H هست (مثلاً H5 و H6 و …)

محدودیت‌های تابع SUMIF و SUMIFS در اکسل:

1) حجم شرط ها محدود است: در SUMIFS تعداد شرط ها نباید از 127 جفت بیشتر شود.

2) هم سایز بودن محدوده ها: محدوده هایی که در شرط ها استفاده می کنید باید دقیقاً هم اندازه با محدوده جمع باشند.

3) عدم پشتیبانی از OR مستقیم: تابع SUMIFS نمی تواند چند شرط OR را به طور مستقیم اجرا کند (برای این کار باید از توابع کمکی استفاده کنید).

4) در اکسل های قدیمی (قبل از 2007) تابع SUMIFS وجود ندارد.

نکات کاربردی:

1) می‌ توانید از علائم مقایسه ای مثل > و < و <> و =* و => و =< در criteria استفاده کنید. نحوه استفاده از این علائم به صورت زیر است:

=SUMIF(A:A,">" & 40,B:B)

=SUMIF(A:A,">" & 40,B:B)

=SUMIF(A:A,"<=" & 40,B:B)

=SUMIF(A:A,"<=" & 40,B:B)

2) برای جمع مقادیر در یک بازه (مثلاً بین دو عدد) باید از SUMIFS استفاده کنید.

3) می توانید برای خواناتر شدن و ساده تر شدن فرمول ها، محدوده ها را نامگذاری کنید.

4) اگر به شرط های OR نیاز دارید، می توانید از چند SUMIF مجزا استفاده کنید و نتیجه ها را جمع بزنید. مثلاً:

=SUMIF(A2:A10,"تهران",B2:B10) + SUMIF(A2:A10,"شیراز",B2:B10)

=SUMIF(A2:A10,"تهران",B2:B10) + SUMIF(A2:A10,"شیراز",B2:B10)

جمع بندی

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

• با SUMIF می‌ توانید بر اساس یک شرط، مقادیر را جمع کنید.

• با SUMIFS می توانید بر اساس چند شرط همزمان مقادیر را جمع کنید.

با شناخت تفاوت ها، محدودیت ها و مثال های کاربردی، حالا می توانید از این توابع برای ساخت گزارش های دقیق، هوشمند و حرفه ای در اکسل استفاده کنید.

امیدواریم این آموزش از سایت اهداف سبز برای شما مفید بوده باشد.

اگر سوال یا نظری درباره آموزش تابع SUMIFS و SUMIF در اکسل دارید، خوشحال می شویم در بخش دیدگاه ها با ما به اشتراک بگذارید.