جمع چک ها در اکسل

تابع SUMIF در اکسل

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

تابع 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 است. توجه داشته باشید که پارامتر دوم باید، به صورت یک عبارت متنی (عبارتی داخل گیومه “”) نوشته شود.

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

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

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

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

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

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

اگر علاقمند هستید تا مهارت های ICDL را یاد بگیرید میتوانید در دوره 7 مهارت ICDL شرکت کنید