آشنایی با تابع SUMIFS در اکسل

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

ساختار و سینتکس تابع

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

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

توضیح پارامترها:

• sum_range: بازه‌ای که مقادیر جمع‌پذیر در آن قرار دارد.
• criteria_range1: بازه‌ای که شرط اول روی آن اعمال می‌شود.
• criteria1: شرط مربوط به بازه اول (می‌تواند عدد، متن یا عبارت منطقی مانند “>100” باشد).
• می‌توانید چندین زوج criteria_range و criteria اضافه کنید تا چند شرط (AND منطقی) اعمال شود.

نمونه داده (مثال پایه)

مثال‌های کاربردی

1) جمع فروش برای Product A در منطقه Tehran:

=SUMIFS(E2:E5, D2:D5, "Product A", B2:B5, "Tehran")

در این فرمول E2:E5 بازه مقادیر (Amount)، D2:D5 ستون محصول و B2:B5 ستون منطقه است. نتیجه جمع مقادیری است که هم محصول آنها Product A و هم منطقه Tehran باشد.

2) جمع مقادیر بزرگ‌تر از 1000 در کل داده‌ها:

=SUMIFS(E2:E5, E2:E5, ">1000")

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

3) استفاده از تاریخ‌ها (مثلاً جمع فروش‌های ماه ژانویه 2025):

=SUMIFS(E2:E5, A2:A5, ">=2025-01-01", A2:A5, "<=2025-01-31")

در این مثال A2:A5 ستون تاریخ است. با دو شرط محدوده تاریخی، فروش‌های مربوط به ژانویه فیلتر می‌شوند. در اکسل واقعی ممکن است نیاز به تابع DATE یا فرمت تاریخ محلی داشته باشید، مثل: <> =SUMIFS(E2:E5, A2:A5, “>=”&DATE(2025,1,1), A2:A5, “<=”&DATE(2025,1,31)).

استفاده از کاراکترهای جایگزین (Wildcards)

SUMIFS از کاراکترهای * و ? پشتیبانی می‌کند که برای تطبیق متن مفیدند.

=SUMIFS(E2:E5, C2:C5, "A*")

این فرمول همه فروش‌هایی که نام فروشنده با حرف A شروع می‌شود جمع می‌کند. “*” نمایانگر هر تعداد کاراکتر و “?” نمایانگر یک کاراکتر است.

عملگرهای منطقی و ارجاع به سلول

برای استفاده از عملگرها به همراه مقدار در یک سلول، باید عملگر را با & به مقدار سلول متصل کنید:

=SUMIFS(E2:E5, E2:E5, ">" & G1)

اگر در سلول G1 عدد 1000 باشد، فرمول بالا معادل “>1000” خواهد بود و جمع مقادیر بزرگ‌تر از مقدار در G1 را محاسبه می‌کند.

OR در برابر AND — چگونه جمع‌گیری با چند شرط “یا” انجام دهیم

SUMIFS به‌صورت پیش‌فرض شرط‌ها را به شکل AND اعمال می‌کند. برای اعمال OR می‌توان یکی از روش‌های زیر را به کار برد:

• جمع چندین SUMIFS که هر کدام یکی از شرایط OR را دارند: =SUMIFS(…, conditionA) + SUMIFS(…, conditionB)
• استفاده از تابع SUMPRODUCT برای شرایط پیچیده (بدون نیاز به جمع دستی): مثال در بخش بعد.

مثال OR با SUMPRODUCT

=SUMPRODUCT((D2:D5={"Product A","Product B"})*(E2:E5))

این فرمول جمع فروش برای Product A یا Product B را محاسبه می‌کند. SUMPRODUCT یک رویکرد مؤثر برای OR و شرایط پیچیده است اما مصرف حافظه بیشتری دارد.

نکات حرفه‌ای و بهترین شیوه‌ها

• هماهنگی طول بازه‌ها: همه criteria_rangeها باید طول و شکل یکسان با sum_range داشته باشند، وگرنه خطا می‌دهد.
• در نسخه‌های قدیمی اکسل از اشاره به کل ستون‌ها (مثلاً A:A) با احتیاط استفاده کنید چون ممکن است کارایی را کاهش دهد.
• برای خوانایی و نگهداری بهتر از Named Ranges (نام‌گذاری بازه‌ها) استفاده کنید.
• در اکسل فارسی ممکن است جداکننده آرگومان‌ها نیم‌فاصله یا نقطه ویرگول باشد؛ فرمول‌های مثال بالا با کاما نوشته شده‌اند (English locale).
• برای افزایش سرعت در جداول بزرگ، از ستون‌های کمکی (Helper Columns) استفاده کنید تا محاسبات پیچیده تکرار نشوند.

اشتباهات رایج و راه‌حل‌ها

• خطا به دلیل اندازه‌های متفاوت بازه‌ها: مطمئن شوید sum_range و criteria_rangeها هم‌اندازه‌اند.
• فراموش کردن قرار دادن عملگر همراه مقدار سلول: “> ” & A1 لازم است، نه فقط “>A1”.
• استفاده از * و ? برای تطبیق متن بدون توجه به فاصله‌ها یا فرمت‌ها: از TRIM و CLEAN برای پاک‌سازی داده‌ها قبل استفاده کنید.

مقایسه با توابع مشابه

SUMIF فقط یک شرط دارد؛ اگر به بیش از یک شرط نیاز دارید از SUMIFS استفاده کنید. SUMPRODUCT انعطاف بیشتری برای شرایط OR و محاسبات برداری دارد اما پیچیدگی و هزینه پردازشی آن بالاتر است.

چند مثال پیشرفته‌تر

جمع فروش‌های منطقه Tehran توسط Amin در بازه زمانی مشخص:

=SUMIFS(E2:E5, B2:B5, "Tehran", C2:C5, "Amin", A2:A5, ">=2025-01-01", A2:A5, "<=2025-01-31")

این فرمول نشان می‌دهد چگونه چند شرط متن و تاریخی را ترکیب کنید تا یک گزارش دقیق به‌دست آورید.

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

تابع SUMIFS ابزار اصلی برای گزارش‌گیری مبتنی بر چند شرط در اکسل است. با یادگیری نحوه استفاده از عملگرها، Wildcardها، و ترکیب با توابعی مانند DATE، SUMPRODUCT و Named Ranges می‌توانید گزارش‌های پویا و قابل اعتماد بسازید. همواره به کارایی و اندازه بازه‌ها دقت کنید و در جداول بزرگ از ستون‌های کمکی برای افزایش سرعت استفاده کنید.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال