تابع SUMIF در اکسل — آموزش با مثال‌های کاربردی

توابع در اکسل نقش بسیار مهمی ایفا می‌کنند. در حقیقت، توابع، فرمول‌های آماده‌ای هستند که به کمک آن‌ها، کاربران اکسل می‌توانند محاسبات پیچیده را به سادگی انجام دهند. یکی از گروه توابعی که بیشترین کاربرد را در میان اکسل کاران دارد، توابع جمع شرطی یا تابع sumif در اکسل است. البته با توجه به دامنه وسیع چنین تابعی، شرکت مایکروسافت، در نسخه ۲۰۱6 اکسل، تابع دیگری که کارایی تابع SUMIF را توسعه داده، معرفی کرد. تابع SUMIFS قابلیت جمع‌بندی براساس چندین شرط را داشته و بنابراین نسبت به تابع SUMIF مزیت بیشتری دارد.

برای آشنایی بیشتر با اکسل و نحوه فرمول نویسی در آن مطالب فرمول نویسی در اکسل — به زبان ساده و تابع IF در اکسل — به زبان ساده را مطالعه کنید. همچنین خواندن نوشتارهای فرمول چند شرطی در اکسل — به زبان ساده و توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی نیز خالی از لطف نیست.

تابع SUMIF در اکسل

یکی از کارهای معمول که در صفحه گسترده‌ها صورت می‌گیرد، دسته‌بندی و جمع مقداری برای دسته‌ها است. خوشبختانه تابع SUMIF در اکسل برای جمع کردن سطرهایی از کاربرگ که براساس یک شرط، دسته‌بندی شده‌اند مناسب است. البته محدودیت یا ضعف‌هایی برای تابع SUMIF وجود دارد که شرکت مایکروسافت با معرفی تابع SUMIFS سعی در برطرف کردن آن‌ها در نسخه‌های ۲۰۱6 به بعد اکسل کرده است.

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

کلیک کنید

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

این تابع معمولا به صورت زیر به کار می‌رود. توجه داشته باشید که در زیر، پارامتری که در داخل براکت ([ ]) نشان داده می‌شوند، یک پارامتر اختیاری است، به این معنی که تعیین آن، شکل محاسبه برای تابع را تغییر داده یا امکانات دیگری به محاسبه می‌افزاید.

=SUMIF (range, criteria, [sum_range])

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

• پارامتر range: اولین آرگومان یا پارامتر برای این تابع، ناحیه‌ای است که باید شرط و جمع‌بندی برای آن اعمال شود. پس در حقیقت ساختاری برداری دارد.
• پارامتر criteria: پارامتر دوم خود شرط است که روی ناحیه مربوط به پارامتر اول اعمال می‌شود. تابع SUMIF در اکسل از عملگرهای منطقی مانند (> ، <، <> ، =) و علائم جایگزین مانند (* ،?) که گاهی به آن‌ها «فرا‌نویسه» نیز می‌گویند، پشتیبانی می‌کند. همانطور که در ادامه خواهید خواند، این شرط‌ها ممکن است برحسب مقادیر سلول‌های دیگر نیز تعیین شوند.
• پارامتر سوم (اختیاری) sum_range: پارامتر سوم این تابع، ناحیه‌ای است که باید جمع‌بندی شود. از آنجایی که این پارامتر اختیاری است، با تعیین آن، ناحیه معرفی شده، با توجه به شرط، جمع شده و پارامتر اول، فقط ناحیه شرط را تعیین خواهد کرد.

نکته: تابع SUMIF از نسخه ۲۰۰۳ اکسل، همراه کاربران اکسل بوده است.

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

البته استفاده از علامت‌های جایگزین (فرانویسه) می‌تواند دامنه کاربرد شرط‌ها را گسترش دهد. در ادامه با مثال‌هایی آشنا خواهیم شد که در آن‌ها از این ویژگی تابع SUMIF در اکسل استفاده شده است. ولی توجه داشته باشید که در پارامتر اول و دوم، فقط یک شرط یا محدودیت را برای جمع‌بندی، اعمال می‌شود.

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

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

همانطور که مشاهده می‌کنید، ستون‌های «نام بانک»، «تاریخ» و «نوع واریز/دریافت» دارای مقادیر تکراری هستند. بنابراین برای دسته‌بندی از این ستون‌ها می‌توان استفاده کرد. از طرفی ستون «مبلغ»، شامل مقادیر عددی است که در نتیجه جمع‌بندی توسط آن صورت خواهد گرفت.

• برای آشنایی با نحوه نوشتن تاریخ شمسی در اکسل به مطلب تاریخ شمسی در اکسل | راهنمای کاربردی در مجله فرادرس، مراجعه کنید.

ابتدا شکل ساده تابع SUMIF در اکسل را که با دو پارامتر بیان می‌شود به کار خواهیم برد، سپس به نحوه نوشتن تابع با سه پارامتر خواهیم پرداخت.

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

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

• سوال ۱: مجموع مبلغ‌های بیشتر از ۳۰۰ چقدر است؟
• سوال ۲: مجموع مبلغ‌های کمتر یا مساوی با 200 چقدر است؟
• سوال ۳: مجموع مبلغ‌هایی که مخالف ۲۰۰ هستند برابر با چه مقداری است؟
• سوال ۴: مجموع مبلغ‌هایی که بیشتر یا مساوی با 2۰۰ و کمتر یا مساوی با 400 هستند چقدر است؟ (بین ۲۰۰ تا ۴۰۰)

همانطور که خواندید، همه شرط‌های مربوط به سوال‌های ذکر شده، براساس ستون مبلغ بوده، در نتیجه ناحیه شرط و ناحیه جمع در یک جا قرار دارند. بنابراین کافی است فقط دو پارامتر اول تابع SUMIF در اکسل را به کار ببریم. از طرفی توجه داشته باشید که تابع SUMIF قادر است فقط یک شرط را مورد بررسی قرار دهد. ولی سوال ۴ روی ستون مبلغ، دو شرط را در نظر گرفته است. پس پاسخ سوال ۴، توسط تابع SUMIF در اکسل قابل اجرا نخواهد بود. این سوال را در زمانی که با تابع SUMIFS آشنا شدیم، پاسخ خواهیم داد.

مطابق با سوال‌های ۱ تا ۳، فرمول‌های مربوطه را در ادامه مشاهده می‌کنید.

پاسخ سوال ۱ برابر با 850 است که ناشی از جمع سلول‌های E6 و E8 است. توجه داشته باشید که پارامتر دوم باید، به صورت یک عبارت متنی (عبارتی داخل گیومه "") نوشته شود.

1=SUMIF(E3:E9,">300")

پاسخ سوال ۲ برابر با 400 است که حاصل جمع سلول‌های E3, E4 و E7 است. به عبارت "=>" که به معنی کوچکتر یا مساوی است، توجه داشته باشید.

1=SUMIF(E3:E9,"<=200")

نتیجه به کارگیری تابع SUMIF در اکسل برای سوال ۳ به شکل مجموع کل سلول‌ها به جز سلول E4 است. بنابراین حاصل برابر با 1600 است. فرمول و پارامترهای تابع SUMIF را در زیر مشاهده می‌کنید.

1=SUMIF(E3:E9,"<>200")

واضح است که پارامتر دوم با مقدار «200<>» نشانگر عبارت «مخالف ۲۰۰» است.

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

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

• سوال 5: مجموع مبالغ مربوط به ستون نوع واریز با مقدار «دریافت» چقدر است؟
• سوال 6: مجموع تراکنش‌های بانک‌ها غیر از بانک تجارت چقدر است؟
• سوال 7: مجموع تراکنش‌های مربوط به تاریخ ‎22/01/1399 چقدر است؟
• سوال 8: مجموع تراکنش‌های مربوط به تاریخ 21/12/1398 تا 22/01/1399 چقدر است؟

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

باز هم مشخص است که سوال‌های 5 تا 7، برحسب یک شرط قابل بیان هستند، ولی سوال 8 باید با دو شرط تعیین شود. یعنی تاریخ‌هایی که بیشتر از 21/12/1398 و تاریخ‌هایی کمتر از 22/01/1399. بنابراین نمی‌توان پاسخ سوال 8 را به کمک تابع SUMIF در اکسل بدست آورد. در قسمت بعدی بر اساس تابع SUMIFS این محاسبه را انجام خواهیم داد.

مقدار محاسبه شده برای سوال 5 برابر است با 950 که براساس حاصل جمع سلول‌های E3, E6 و E8 بدست آمده است. زیرا همگی آن‌ها در ستون «نوع واریز/دریافت»، دارای مقدار «دریافت» هستند. پارامترهایی که برای تابع SUMIF در این حالت تنظیم شده را در زیر مشاهده می‌کنید.

1=SUMIF(D3:D9,"دریافت",E3:E9)

سوال 6 را هم با استفاده از فرمول زیر پاسخ داده‌ایم. واضح است که مقدار 1200 براساس جمع همه سلول‌هایی ستون E به غیر از E5, E8 و E9 حاصل شده است.

1=SUMIF(B3:B9,"<> تجارت",E3:E9)

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

1=SUMIF(C3:C9,"="&D17,E3:E9)

البته فرض کرده‌ایم که تاریخ مورد نظر را (برحسب تاریخ شمسی) در سلول D17 وارد کرده‌ایم.

استفاده از علامت‌های جایگزین در تابع SUMIF اکسل

احتمالا هنگامی جستجوی فایل‌ها در سیستم عامل ویندوز از علامت‌های جایگزین یا فرانویسه آشنا شده‌اید. دو علامت پر کاربرد که به عنوان جایگزین بخشی از یک عبارت متنی به کار می‌روند، علامت‌های * و ? هستند. در عبارت متنی، در هر مکانی که علامت * ظاهر شود، نشانگر جایگزینی آن با هر رشته دیگر متنی است. برای مثال اگر عبارتی به صورت *abc ظاهر شود، تمامی رشته‌های متنی زیر می‌تواند نتیجه حاصل از جستجو این عبارت باشد.

abcd, abcde, abcfxsdaaaaaaa, abcabcd, ...

در نتیجه می‌توان گفت که نتیجه جستجو، شامل همه عبارت‌هایی است با حروف abc شروع شده باشند.

از طرفی هر بار استفاده از علامت ? در یک عبارت، نشانگر تعداد حروفی است که می‌توانند در آن محل جایگزین شوند. برای مثال اگر می‌خواهید به دنبال عبارت‌هایی بگردید که دارای ۳ حرف باشند، از رشته متنی ??? باید کمک بگیرد. البته می‌توانید با ذکر حروف، نتیجه را کمی تغییر دهید. برای مثال برای جستجو عبارت‌هایی که ۳ حرف داشته و با حرف a شروع شده‌اند، به صورت ??a عبارت جستجو را مشخص می‌کنیم.

از همین ویژگی‌ها برای جستجو و دسته‌‌بندی در تابع SUMIF استفاده می‌کنیم. حال به ذکر سه سوال و پاسخ آن‌ها توسط این تابع در اکسل می‌پردازیم.

• سوال 9: مبلغ تراکنش‌ها مربوط به بانک ملت و ملی چقدر است؟

به نظر می‌رسد که این سوال به صورت دو شرطی است. ولی به کمک علامت‌های جایگزین (فرانویسه‌ها) می‌توانیم آن را با توجه به اسامی دیگر بانک‌ها به صورتی در بیاوریم که با تابع SUMIF در اکسل پاسخ داده شود. همانطور که می‌بینید، هر دو بانک با حروف «مل» شروع می‌شوند و بقیه بانک‌ها چنین وضعیتی ندارند. بنابراین عبارت مورد جستجو را با علامت‌های جایگزین به صورت «*مل» می‌نویسیم تا پاسخ مناسب را دریافت کنیم. به فرمول زیر توجه کنید.

1=SUMIF(B3:B9,"مل*",E3:E9)

مشخص است که مقدار پاسخ برابر با ۳۰۰ خواهد بود که نتیجه جمع سلول‌های E3 تا E4 است.

نکته: تابع SUMIF می‌تواند جمع ستون مبلغ را براساس شرط روی سلول‌های خالی نیز محاسبه کند. در مثال زیر، SUMIF بسته به اینکه ستون B حاوی مقدار خالی باشد، برای جمع آوری مقادیر در ستون E استفاده شده است.

1=SUMIF(B3:B9,"",E3:E9)

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

1=SUMIF(B3:B9,"<>",E3:E9)

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

هنگام استفاده از تابع SUMIF باید به شکلی عمل کنید که شرایط موجود برای پارامترهای آن فراهم باشد. به این منظور به نکات زیر توجه داشته باشید.

• تابع SUMIF فقط از یک شرط پشتیبانی می‌کند. برای در نظر گرفتن چندین شرط برای یک یا چند محدوده، از تابع SUMIFS استفاده کنید.
• وقتی پارامتر sum_range را به کار نبرید، سلولهای موجود در محدوده شرط (Range) برای مقایسه و محدودیت‌ها به کار می‌روند.
• رشته‌ها یا عبارت‌های متنی در پارامتر دوم (شرط) باید به همراه علامت نقل قول ("") همراه باشد.
• آدرس سلول‌ها در عبارت شرطی نباید به کار روند. برای استفاده از آدرس سلول‌ها باید آن‌ها را به کمک عملگر & به قسمت شرط ضمیمه کنید.
• علامت‌های جایگزین ? و * می‌تواند در پارامتر دوم (شرط) استفاده شود. یک علامت سوال با هر یک از حروف و یک ستاره با هر دنباله از حروف مطابقت دارد.
• اگر می‌خواهید در یک عبارت شرطی، مقدار * یا ? را جستجو و برای آن گروه‌بندی کنید، از علامت مد (~) در علامت سوال یا ستاره استفاده کنید (به عنوان مثال ?~ ، *~).
• تابع SUMIF احتیاج به پارامتر اول و سوم به صورت یک ناحیه پیوسته دارد. بنابراین نمی‌توان نواحی گسسته را برای این پارامتر انتخاب کرد.
• ابعاد ناحیه انتخاب شده برای پارامتر اول (ناحیه شرط) و پارامتر سوم (ناحیه جمع) باید یکسان باشد. در غیر اینصورت ممکن است (بدون اینکه اکسل خطای ظاهر کند) پاسخ‌های غلط دریافت کنید.

تابع SUMIFS در اکسل

از آنجایی که تابع SUMIF براساس یک شرط عمل می‌کند، اکسل تابع SUMIFS را زمانی که با چندین شرط برای گروه‌بندی مواجه هستیم، پیشنهاد می‌کند. به این ترتیب ضعف عمده‌ای که تابع SUMIF دارد برطرف می‌شود. ولی توجه داشته باشید که تابع SUMIFS در اکسل ۲۰۱6 قابل استفاده است.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

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

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

این بار سه پارامتر اجباری را برای تابع SUMIFS مشاهده می‌کنید که در ادامه آن‌ها را معرفی و مورد بررسی قرار می‌دهیم.

• پارامتر sum_range: این پارامتر، ناحیه‌ای که برای جمع‌بندی به کار می‌رود را مشخص می‌کند. همانطور که قبلا گفته شد، مانند تابع SUMIF، این ناحیه باید به صورت یک ناحیه پیوسته باشد.
• پارامتر criterian_range1: در این قسمت، ناحیه‌ای که مرتبط با شرط اول است، مشخص می‌شود. ابعاد این ناحیه باید با sum_range مطابقت داشته باشد.
• پارامتر criterian1: شرطی که باید روی پارامتر دوم تابع SUMIFS اعمال شود، در این پارامتر تعیین می‌شود. درست به مانند تابع SUMIF، علامت گیومه "" برای بیان شرط در این پارامتر الزامی است.
• پارامتر criterian_range2: نواحی بعدی برای مشخص کردن شرط در این قسمت و پارامترهای بعدی ظاهر می‌شود.
• پارامتر criterian2: مقدار شرط برای تعیین شرط در پارامترهای critetria2 و ...critetria3 تعیین می‌شود.
• ...

در ادامه به پاسخ سوالات باقی‌مانده در قسمت قبل خواهیم پرداخت.

پاسخ سوال ۴ (مجموع مبلغ‌هایی که بیشتر یا مساوی با 2۰۰ و کمتر یا مساوی با 400 هستند) به صورت زیر خواهد بود. به تعیین شرط بین دو مقدار که در این تابع با دو شرط مشخص شده است، توجه کنید.

1=SUMIFS(E3:E9,E3:E9,"<=400",E3:E9,">=200")

از آنجایی که سلول‌های E4, E5 و E8 ,E9 برای جمع‌بندی به کار می‌روند، مقدار جمع مقادیر بین ۲۰۰ تا ۴۰۰ توسط تابع SUMIFS برابر با ۱۱۰۰ خواهد بود.

همچنین برای پاسخ دادن به سوال 8 (مجموع تراکنش‌های مربوط به تاریخ 21/12/1398 تا 22/01/1399) از فرمولی به صورت زیر کمک خواهیم گرفت. باز هم مشخص است که از دو شرط برای ستون تاریخ استفاده کرده‌ایم.

1=SUMIFS(E3:E9,C3:C9,">="&D18,C3:C9,"<="&E18)

توجه داشته باشید که فرض بر این است که تاریخ ابتدای جستجو را با قالب فارسی، در سلول D18 و تاریخ پایان جستجو را در E18 نوشته‌ایم.

این بار یک سوال دیگر هم مطرح می‌کنیم و با استفاده از تابع SUMIFS آن را پاسخ خواهیم داد. فرض کنید تمامی پرداخت‌های مربوط به بانک صادرات را در نظر گرفته و مجموع ستون مبلغ را لازم داریم. شکل فرمولی که برای پاسخ به این سوال لازم داریم به صورت زیر است.

1=SUMIFS(E3:E9,B3:B9,"صادرات",D3:D9,"پرداخت")

نتیجه همانطور که می‌توان حدس زد، این حاصل جمع، برابر با 550 خواهد بود که ناشی از مجموع سلول‌های E5 و E9 است. همانطور که مشاهده کردید، در این مثال از دو ناحیه و دو شرط برای دسته‌بندی استفاده کرده و ناحیه مربوط سطرهای متناظر با آن‌ها را در ستون مبلغ، جمع کردیم.

نکته: اگر لازم باشد که بیش از دو شرط روی جمع‌بندی به کار رود، باید از Criteria_range3 و Criteria3 به بعد استفاده کنید.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

فایل مربوط به این کاربرگ را می‌توانید از اینجا دریافت کنید. البته فایل دریافتی با قالب فشرده در اختیارتان قرار می‌گیرد. کافی است پس از خارج کردن آن از حالت فشرده، در اکسل آن را بارگذاری نمایید تا به محاسبات و فرمول‌های نوشته شده در این مطلب، دسترسی پیدا کنید.

در تصویر ۲، نتایج محاسبات و کاربرگ مربوط به سوالات طرح شده در این متن با تابع SUMIF و SUMIFS، دیده می‌شود.

به این موضوع نیز توجه داشته باشید که تابع SUMIFS می‌تواند ۱۲۷ ناحیه و شرط را کنترل کرده و برای جمع به کار برد. از طرفی تعیین شرط و ناحیه شرط برای این تابع درست به مانند تابع SUMIF است. موضوع دیگر هنگام استفاده از تابع SUMIFS مربوط به ستون‌هایی با مقادیر TRUE و FALSE است که این تابع نسبت به آن‌ها نقطه ضعف داشته و قادر به شناسایی آن‌ها نیست.

خلاصه و جمع‌بندی

تابع SUMIF برای جمع‌بندی روی یک و تابع SUMIFS برای چند گروه که توسط شرط تعیین می‌شوند، به کار می‌روند. یکی از مزایای استفاده از این توابع، سادگی در پارامترها و تعریف تابع است. هر چند به کمک ابزارهای دسته و جمع‌بندی اکسل، مانند جدول محوری، نیز می‌توان محاسباتی نظیر این دو تابع را انجام داد ولی انعطاف و گستردگی شرط‌هایی به کار رفته در تابع SUMIFS در هیچ ابزار یا تابع دیگر اکسل وجود ندارد. وجود تابع SUMIFS از مزایای مطلوب برای استفاده از اکسل ۲۰16 به بعد است. توجه داشته باشید که جنس و نحوه عملکرد تابع SUMIFS با تابع SUMIF شباهت‌های زیادی دارد.