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

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

جمع شرطی چیست؟

جمع شرطی یعنی محاسبه مجموع چند عدد البته با توجه به شرط یا شروط موجود.

برای انتقال بهتر مفهوم جمع شرطی، در ادامه با ذکر چند مثال این مفهوم را شرح می‌دهیم.

مثال ۱) در محدودهA1:A6  از جدول زیر جمع اعداد کوچکتر از ۲۸ را محاسبه کنید.

برای محاسبه این جمع از تابع sum نمی‌توان استفاده کرد زیرا یک شرط وجود دارد و آن این است که جمع اعدادی را محاسبه کنید که کوچکتر از ۲۵ باشند. برای حل این مشکل می‌توان از تابع Sumif استفاده کرد.

• ساختار تابع Sumif به شکل زیر می‌باشد:

=SUMIF(range, criteria, [sum_range])

range: همان محدوده‌ای می‌باشد که قصد داریم شرط را بر آن اعمال کنیم. این محدوده می‌تواند شامل عدد، نام، آرایه و یا محدوده‌ای از اعداد باشد. (سلول‌های خالی و کاراکترها محاسبه نمی‌شود). در مثال فوق محدوده A1:A6 است.

criteria: همان شرط ما می‌باشد. این شرط می‌تواند شامل عدد، عبارت، یک سلول، متن و تابع باشد. در مثال فوق شرط اعداد کوچکتر از ۲۸ است.

نکته ۱: کلیه عبارات و شرط‌های ریاضی باید داخل گیومه ” ” باشد مگر اینکه شرط شما عدد باشد.

نکته ۲: در شرایط مشابه مثال فوق چنانچه بخواهید بجای نوشتن عدد ۲۸ در تابع آدرس آن یعنی A6 را در تابع درج کرد باید شرط را بصورت زیر بنویسید:

“<“&A6

sum_range: این گزینه اختیاری می‌باشد. زمانی از این آرگومان استفاده می‌کنیم که Range  ما شامل اعداد نباشد و یا اینکه اعدادی که قصد داریم مجموع آن‌ها را محاسبه کنیم در محدوده range قرار نداشته باشند که در مثال بالا هیچ یک از دو حالت فوق برقرار نیست. چون هم Range ما شامل اعداد است و هم اینکه اعدادی که قصد داریم مجموع آن‌ها را محاسبه کنیم در محدوده range قرار دارند. بنابراین در مثال ۱ از آرگومان sum_range استفاده نمی‌کنیم.

بنابراین مطابق توضیحات بالا برای مثال ۱ تابع Sumif به صورت زیر نوشته می‌شود.

=Sumif(A1:A6,"<28")

خروجی فرمول بالا برابر است با ۶۰.

در مثال بالا فقط از دو آرگومان range و criteria استفاده شده‌است. اگر بخواهیم از هر ۳ آرگومان Range و Criteria و Sum_range استفاده کنیم مثال ۲ را مشاهده کنید.

مثال ۲) جدول زیر را در نظر بگیرید که مربوط به پخش اقلام در یک منطقه زلزله زده می‌باشد. مجموع کل “آب معدنی”های توزیع شده در کلیه مناطق را محاسبه کنید.

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

برای محاسبه جمع کل “آب معدنی”ها باید از فرمول زیر استفاده کرد:

=Sumif(B2:B10,"آب معدنی",C2:C10)

مطابق با ساختار تابع ،Sumif محدوده یا Range مورد نظر سلول‌های B2:B10 هستند که عدد نمی‌باشد. اکسل باید این محدوده را با شرط مطابقت دهد.

 Criteriaیا شرط ما در اینجا “آب معدنی” می‌باشد.

اعدادی که قصد داریم جمع کنیم در محدوده range قرار ندارند بنابراین باید از آرگومان اختیاری sum_range استفاده کرد. Sum_range محدوده اعداد متناظر با Range می‌باشد یعنی C2:C10 که در صورت مطابقت شرط، اکسل آن‌ها را با هم جمع می‌کند.

جواب مثال فوق برابر است با ۴۸۰۰.

مثال ۳) در مثال ۲ جمع کل “آب معدنی” را در منطقه “جنوب” محاسبه کنید.

در این مثال ما با دو شرط روبرو هستیم که به این علت نمی‌توان از تابع Sumif استفاده کرد و بجای آن باید از تابع Sumifs استفاده نمود. بوسیله این تابع می‌توان جمع‌های چند شرطی را انجام داد.

• ساختار تابع Sumifs به شکل زیر است:

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

sum_range: محدوده‌ای که می‌خواهیم آن را جمع کنیم. (در مثال فوق محدوده C2:C10 می‌باشد)

criteria_range1: محدوده شرط اول (محدوده شرط اول ما در این مثال، نوع کالا است یعنی محدوده B2:B10)

criteria1: شرط اول (آب معدنی)

criteria_range2: محدوده شرط دوم (محدوده شرط دوم ما در این مثال، منطقه است یعنی محدوده A2:A10)

criteria2:شرط دوم (جنوب)

=Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"جنوب")

خروجی فرمول فوق برابر است با ۲۰۰۰.

مثال ۴) در مثال ۲ جمع کل “آب معدنی” را در مناطق “جنوب” و “شمال” محاسبه کنید.

مثال فوق، مشابه مثال ۳ است، اما یک تفاوت کوچک وجود دارد و آن این است که در شرط دوم علاوه بر منطقه “جنوب”، منطقه “شمال” هم باید در نظر گرفته شود.

در اینحالت باید مشابه مثال ۳ از تابع Sumifs استفاده نمود اما با کمی تفاوت. شاید بخواهید برای حل مثال فوق، در فرمول قبل شرط جدید یعنی منطقه “شمال” را اضافه کنید اما نمی‌توان اینکار را انجام داد. چون تابع Sumifs پس از بررسی شرط منطقه “جنوب”، شرط منطقه “شمال” را بررسی می‌کند یعنی باید هر دو شرط برقرار باشد که چنین چیزی ممکن نیست که در یک سلول هم جنوب باشد و هم شمال. از طرفی درون تابع Sumifs نمی‌توان از OR استفاده کرد. برای حل این مشکل دو راهکار وجود دارد:

یا باید نام مناطق را درون کوشه بگذارید و همراه با تابع Sumifs از تابع Sum هم استفاده کنید:

=Sum(Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,{"جنوب","شمال"})

یا اینکه از مجموع دو Sumifs استفاده کنید:

=Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"جنوب")+Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"شمال")

خروجی فرمول فوق برابر است با ۴۰۰۰.

مثال ۵) محاسبه مجموع اعداد بین دو تاریخ مشخص:

مطابق جدول زیر مجموع مقادیر ستون B در صورتیکه بین دو تاریخ ۳/۴/۲۰۱۴ و ۵/۱۰/۲۰۱۴ باشند را محاسبه کنید.

در واقع در این مثال هم دو شرط وجود دارد که بصورت زیر در تابع Sumifs قرار داده می‌شوند.

=SUMIFS(B2:B8,A2:A8,">=2014-03-04",A2:A8,"<=2014-05-10")

خروجی فرمول بالا برابر است با ۹۱.