تابع SUMIFS را چگونه در حسابداری استفاده کنیم؟
وقتی لازم است جمع هزینهها یا درآمدها را همزمان بر اساس چند شرط محاسبه کنید، جمعزدن دستی ردیفها زمانبر و مستعد خطاست. تابع SUMIFS در حسابداری این کار را در Excel انجام میدهد؛ برای مثال میتوانید هزینه اجاره یک شعبه را در یک بازه زمانی مشخص محاسبه کنید. در این مقاله، ساختار فرمول را با یک جدول مالی و مثالهای قابل استفاده در گزارشگیری یاد میگیرید. این آموزش برای دانشجویان و حسابدارانی مناسب است که میخواهند گزارشهای دقیقتری بسازند؛ در وبسایت علیرضا میرزازاده نیز موضوعات آموزشی حسابداری و Excel دنبال میشوند.
SUMIFS جمع یک محدوده عددی را فقط برای ردیفهایی محاسبه میکند که چند شرط را همزمان داشته باشند. ابتدا محدوده جمع را مشخص کنید و سپس برای هر شرط، محدوده بررسی و معیار آن را وارد کنید. در گزارش حسابداری میتوان از این تابع برای جمع هزینه بر اساس حساب، شعبه و بازه تاریخ استفاده کرد.
تابع SUMIFS چیست و چه کاربردی در حسابداری دارد؟
SUMIFS یکی از توابع جمع شرطی Excel است. برخلاف تابع SUM که همه اعداد یک محدوده را جمع میکند، SUMIFS فقط مقادیری را جمع میزند که با معیارهای تعیینشده مطابقت داشته باشند. شرطها با هم رابطه «و» دارند؛ یعنی یک ردیف باید تمام شرطها را برآورده کند تا مبلغ آن در نتیجه لحاظ شود.
این ویژگی برای گزارشهای مالی کاربردی است؛ مثلاً میتوانید جمع «هزینه حمل» را برای «شعبه مرکزی» در یک ماه محاسبه کنید. همچنین میتوان جمع فروش را بر اساس نام مشتری و نوع کالا یا مانده یک حساب را بر اساس کد حساب و دوره گزارش استخراج کرد. نتیجه به درستبودن و یکدستبودن دادههای مبنا وابسته است؛ SUMIFS جایگزین ثبت صحیح اسناد یا کنترل حسابها نیست.
ساختار فرمول SUMIFS در Excel
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range محدوده اعدادی است که باید جمع شوند. پس از آن، محدوده شرط و معیار همان شرط وارد میشود. برای افزودن شرطهای بیشتر، جفتهای «محدوده شرط، معیار» را ادامه دهید. هر محدوده شرط باید با ردیفهای محدوده جمع همتراز باشد؛ در غیر این صورت ممکن است فرمول نتیجه نادرست بدهد یا خطا ایجاد کند.
در بعضی تنظیمات Excel، جداکننده آرگومانها بهجای ویرگول انگلیسی (,) نقطهویرگول (;) است. اگر فرمول با ویرگول پذیرفته نشد، تنظیمات منطقهای Excel را در نظر بگیرید و جداکننده مناسب را جایگزین کنید.
مثال جدول مالی برای جمع چند شرطی
فرض کنید اطلاعات ثبتشده در جدول زیر قرار دارد. ستونها بهترتیب شامل تاریخ، عنوان حساب، شعبه و مبلغ هستند. ارقام جدول صرفاً آموزشیاند و اطلاعات واقعی یک شرکت نیستند.
| تاریخ | عنوان حساب | شعبه | مبلغ (ریال) |
|---|---|---|---|
| 1403/01/05 | هزینه اجاره | تهران | 80,000,000 |
| 1403/01/12 | هزینه برق | تهران | 12,000,000 |
| 1403/01/20 | هزینه اجاره | شیراز | 60,000,000 |
| 1403/02/05 | هزینه اجاره | تهران | 80,000,000 |
| 1403/02/14 | هزینه برق | تهران | 10,000,000 |
| 1403/02/18 | هزینه اجاره | شیراز | 60,000,000 |
در فایل واقعی فرض میکنیم تاریخ در ستون A، عنوان حساب در ستون B، شعبه در ستون C و مبلغ در ستون E قرار دارد. برای جمع هزینه اجاره شعبه تهران در تمام ردیفها بنویسید:
=SUMIFS($E$2:$E$100,$B$2:$B$100,"هزینه اجاره",$C$2:$C$100,"تهران")
فرمول فقط ردیفهایی را جمع میکند که عنوان حساب آنها «هزینه اجاره» و شعبه آنها «تهران» باشد. بر اساس ارقام آموزشی جدول، نتیجه 160,000,000 ریال است. علامت دلار در آدرس محدودهها، مرجع را هنگام کپیکردن فرمول ثابت نگه میدارد.
گزارشگیری بر اساس حساب، شعبه و بازه زمانی
برای گزارش ماهانه، باید تاریخ ابتدا و انتهای دوره را هم به شرطها اضافه کنید. فرض کنید سلول H2 تاریخ شروع و سلول H3 تاریخ شروع ماه بعد را نگه میدارند. استفاده از بازهای که از تاریخ شروع آغاز میشود و پیش از تاریخ شروع ماه بعد پایان مییابد، زمان را نیز بهتر پوشش میدهد؛ بهویژه اگر سلولهای تاریخ ساعت داشته باشند.
=SUMIFS($E$2:$E$100,$B$2:$B$100,"هزینه اجاره",$C$2:$C$100,"تهران",$A$2:$A$100,">="&H2,$A$2:$A$100,"<"&H3)
در این فرمول، علامت >= تاریخ شروع را شامل میکند و < تاریخ شروع دوره بعد را کنار میگذارد. در نتیجه همه تاریخهای ماه انتخابشده وارد گزارش میشوند، بدون اینکه لازم باشد آخرین روز ماه را دستی محاسبه کنید. مطمئن شوید H2 و H3 مقدار تاریخ واقعی Excel دارند، نه متنهایی که فقط شبیه تاریخ نمایش داده میشوند.
اگر محدوده تاریخ را مستقیماً در فرمول میسازید، میتوانید از تابع DATE استفاده کنید؛ برای نمونه، شرط تاریخ از ابتدای ژانویه 2025 تا پیش از ابتدای فوریه همان سال به این صورت نوشته میشود: ">="&DATE(2025,1,1) و "<"&DATE(2025,2,1). این تاریخ صرفاً نمونه آموزشی است. تنظیم ترتیب آرگومانهای DATE و جداکنندهها را با نسخه و تنظیمات Excel خود هماهنگ کنید.
معیارهای متنی و شرطهای مقایسهای
معیار میتواند متن، عدد یا یک شرط مقایسهای باشد. برای مثال، معیار "تهران" ردیفهای شعبه تهران را انتخاب میکند. برای جمع مبلغهای بزرگتر از عددی که در سلول H4 قرار دارد، از شرطی مانند ">"&H4 استفاده کنید. اتصال عملگر به مرجع سلول با & انجام میشود.
برای تطبیق بخشی از یک متن نیز میتوان از نویسههای جایگزین استفاده کرد. ستاره (*) به معنی هر تعداد نویسه و علامت سؤال (?) به معنی یک نویسه است. مثلاً معیار "هزینه*" میتواند عنوانهایی را پیدا کند که با «هزینه» شروع میشوند. اگر در دادهها فاصله اضافی یا شکل نوشتاری متفاوت وجود داشته باشد، نتیجه ممکن است با انتظار شما فرق کند.
تفاوت شرط AND و OR در فرمول SUMIFS
شرطهای متعدد داخل یک SUMIFS بهصورت AND بررسی میشوند. برای نمونه، ترکیب «هزینه اجاره» و «تهران» فقط ردیفهایی را برمیگرداند که هر دو ویژگی را دارند. اما اگر بخواهید مبلغ شعبه تهران یا شیراز را جمع کنید، معمولاً دو SUMIFS را با هم جمع میکنید:
=SUMIFS($E$2:$E$100,$C$2:$C$100,"تهران")+SUMIFS($E$2:$E$100,$C$2:$C$100,"شیراز")
برای اینکه یک مبلغ دوبار شمرده نشود، شرطهای OR را طوری تعریف کنید که همپوشانی ناخواسته نداشته باشند. اگر گزارش شامل چند شرط متنی پیچیده یا گروهبندیهای زیاد است، PivotTable نیز میتواند گزینه مناسبی برای تحلیل و مرور دادهها باشد.
خطاهای رایج در استفاده از SUMIFS
قبل از اتکا به نتیجه، محدودهها و نوع دادهها را بررسی کنید. یک فرمول درست نیز در صورت وجود مبلغ متنی، تاریخ متنی یا عنوان حساب ناهماهنگ، گزارش قابل اتکایی تولید نمیکند.
- ناهماندازهبودن محدودهها: محدوده جمع و همه محدودههای شرط را از ردیفهای متناظر انتخاب کنید.
- مبلغ ذخیرهشده بهصورت متن: عددهایی که به شکل متن وارد شدهاند ممکن است در جمع لحاظ نشوند. قالب و نوع داده ستون مبلغ را بررسی کنید.
- تاریخ واقعی نبودن: نمایش ظاهری تاریخ کافی نیست؛ مقدار سلول باید توسط Excel بهعنوان تاریخ شناخته شود.
- تفاوت در عنوانها: فاصلههای ابتدا یا انتهای متن و تفاوت در شیوه نوشتن نام حساب یا شعبه میتواند بعضی ردیفها را از گزارش حذف کند.
- اشتباه گرفتن واحد مبلغ: بررسی کنید همه مبالغ در یک واحد، مانند ریال یا تومان، ثبت شده باشند و گزارش همان واحد را نشان دهد.
- مراجع جابهجا هنگام کپی: برای محدودههای ثابت از مرجع مطلق، مانند
$E$2:$E$100، استفاده کنید.
چکلیست کنترل گزارش قبل از استفاده
- محدوده جمع به ستون درست و فقط به ردیفهای داده اشاره میکند.
- محدودههای معیار همردیف و هماندازه با محدوده جمع هستند.
- عنوان حساب، شعبه و سایر معیارها با مقادیر جدول تطبیق دارند.
- تاریخهای شروع و پایان بهعنوان تاریخ واقعی Excel ثبت شدهاند.
- واحد مبلغ و شیوه ثبت بدهکار و بستانکار در گزارش روشن است.
- نتیجه فرمول با نمونهگیری از چند ردیف یا یک گزارش کنترلی بررسی شده است.
سؤالات متداول
تفاوت SUMIFS و SUMIF چیست؟
SUMIF برای جمع بر اساس یک شرط به کار میرود؛ SUMIFS امکان تعریف چند شرط همزمان را میدهد. در گزارش حسابداری، وقتی باید مبلغ را براساس چند ویژگی مانند حساب و شعبه محدود کنید، SUMIFS مناسبتر است.
آیا SUMIFS میتواند بر اساس چند ماه گزارش تهیه کند؟
بله. برای هر دوره، تاریخ شروع و شروع دوره بعد را بهعنوان مرزهای بازه تعریف کنید و فرمول را برای همان بازه محاسبه کنید. میتوانید تاریخها را از سلولهای گزارش بگیرید تا تغییر دوره آسانتر شود.
چرا نتیجه SUMIFS صفر است؟
معیارها را با دادههای جدول مقایسه کنید و فاصلههای اضافی، نوع داده مبلغ، واقعیبودن تاریخها و محدودههای انتخابشده را بررسی کنید. همچنین مطمئن شوید جداکننده آرگومانها در فرمول با تنظیمات Excel سازگار است.
جمعبندی
تابع SUMIFS در حسابداری راهی انعطافپذیر برای جمع مبلغها بر اساس چند معیار است. با انتخاب درست محدوده جمع، تعریف جفتهای محدوده و معیار، و استفاده از شرطهای تاریخ میتوانید گزارشهای ماهانه و شعبهای بسازید. برای قابل اعتماد بودن گزارش، یکدستی دادهها، واحد مبالغ و کنترل نتیجه را جدی بگیرید؛ فرمول بهتنهایی صحت ثبتهای حسابداری را تضمین نمیکند.
یک نسخه آزمایشی از جدول مالی خود بسازید و فرمول را ابتدا روی چند ردیف کنترل کنید. سپس با تمرین توابع شرطی و ابزارهای گزارشگیری Excel، گزارشهای حسابداری را دقیقتر و سریعتر آماده کنید.