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

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

نحو (Syntax) و پارامترها

نحو تابع به شکل زیر است:

=SUMIF(range, criteria, [sum_range])

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

• range: محدوده‌ای که شرط روی آن اعمال می‌شود (مثلاً ستون A).
• criteria: شرطی که باید برآورده شود (مثل “>100” یا “apples”).
• sum_range (اختیاری): محدوده‌ای که مقادیر آن جمع می‌شود. اگر این پارامتر حذف شود، خودِ range جمع می‌شود.

مثال‌های پایه‌ای

فرض کنید در ستون A نام محصولات و در ستون B مبلغ فروش قرار دارد. می‌خواهیم جمع فروش مربوط به محصول “سیب” را محاسبه کنیم.

=SUMIF(A2:A100, "سیب", B2:B100)

این فرمول تمام سلول‌های ستون B را جمع می‌کند که سطر متناظر آن در ستون A برابر “سیب” باشد.

استفاده از عملگرهای مقایسه

برای شرط‌هایی مثل بزرگتر یا کوچکتر باید عملگر را به صورت رشته‌ای بنویسید یا با استفاده از عمل اتصال (&) مقدار را از سلول دیگر وارد کنید:

=SUMIF(B2:B100, ">100")

این فرمول تمام مقادیر بیشتر از 100 را در محدوده B2:B100 جمع می‌کند. اگر مقدار شرط در سلولی مثل D1 باشد:

=SUMIF(B2:B100, ">" & D1)

با استفاده از ” & ” مقدار D1 به شرط اضافه می‌شود.

کار با متن و کاراکترهای جایگزین (Wildcard)

تابع SUMIF از کاراکترهای جایگزین پشتیبانی می‌کند:

• * (ستاره): نشان‌دهنده هر توالی کاراکتر.
• ? (سوال): نشان‌دهنده دقیقاً یک کاراکتر.
• ~ (تیلده): برای فرار دادن کاراکترهای * یا ? زمانی که می‌خواهید دقیقاً آن‌ها را جستجو کنید.

=SUMIF(A2:A100, "مشتری*", B2:B100)

این فرمول فروش همه مشتریانی را جمع می‌کند که نام آن‌ها با “مشتری” شروع می‌شود.

نکات مهم و محدودیت‌ها

• SUMIF غیرقابل‌حساس به حروف کوچک و بزرگ است؛ یعنی “apple” و “Apple” برابر حساب می‌شوند.
• اگر sum_range اندازه‌ای متفاوت از range داشته باشد، اکسل از گوشه بالا-چپ هر دو محدوده به‌صورت متناظر استفاده می‌کند؛ بنابراین باید طول یکسانی داشته باشند.
• برای چند شرط همزمان باید از SUMIFS استفاده کنید (توجه: ترتیب پارامترها در SUMIFS متفاوت است).
• وقتی شرط شامل تاریخ است، بهتر است از توابع تاریخ یا مرجع سلول (مثل “>” & DATE(2025,1,1) یا “>” & A1) استفاده کنید تا اشتباهات ناشی از فرمت سلول کاهش یابد.

مثال با داده و جدول نمونه

فرمول زیر جمع فروش مربوط به “سیب” را برمی‌گرداند:

=SUMIF(B2:B4, "سیب", C2:C4)

در مثال بالا نتیجه برابر 270 خواهد بود.

جایگزین‌ها و ترکیب‌های پیشرفته

اگر نیاز به چند شرط داشته باشید، از SUMIFS استفاده کنید:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

برای مثال جمع فروش سیب‌ها در سال 2024:

=SUMIFS(C2:C100, B2:B100, "سیب", D2:D100, 2024)

در اکسل 365 و نسخه‌های جدید می‌توانید از توابع پویا مانند FILTER و SUM ترکیبی استفاده کنید که گاهی خواناتر و منعطف‌تر است:

=SUM(FILTER(C2:C100, (B2:B100="سیب")*(D2:D100=2024)))

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

بهینه‌سازی و نکات عملکردی

• اگر داده‌ها بسیار بزرگ هستند، SUMIFS معمولاً سریع‌تر از ترکیب‌های آرایه‌ای است.
• برای محاسبات مکرر و گزارش‌های پویا بهتر است محدوده‌ها را به جدول (Excel Table) تبدیل کنید؛ با این کار فرمول‌ها پویا شده و خوانایی افزایش می‌یابد.
• از فرمول‌های volatile مانند INDIRECT یا OFFSET دوری کنید چون باعث کندی محاسبات می‌شوند.

عیب‌یابی و سؤالات متداول

• اگر فرمول نتیجه صفر یا اشتباه می‌دهد، از همسان بودن فرمت‌ها (متن vs عدد، تاریخ) مطمئن شوید.
• برای شرایط متنی که حاوی کاراکترهای خاص (* یا ?) هستند از “~” قبل از آن‌ها استفاده کنید.
• اگر می‌خواهید شرط باشد که سلولی خالی نباشد از “” به تنهایی استفاده کنید: =SUMIF(A2:A100,"",B2:B100)

مثال پیشرفته: ترکیب SUMIF و VLOOKUP/SUMPRODUCT

گاهی لازم است شرط را بر اساس نتایج جستجو تعیین کنید؛ به‌عنوان مثال جمع مقادیری که متعلق به گروهی خاص هستند که نام گروه در جدول دیگری تعریف شده است. در این مواقع می‌توان از VLOOKUP یا INDEX/MATCH برای دریافت شرط استفاده کرد و سپس SUMIF را صدا زد.

=SUMIF(A2:A100, VLOOKUP(F1, Groups!A:B, 2, FALSE), C2:C100)

فرمول بالا شرط را از یک جدول گروه‌ها می‌خواند و سپس جمع‌گیری را انجام می‌دهد.

خلاصه و توصیه‌های نهایی

تابع SUMIF مناسب مسائل جمع‌شرطی ساده است و با ترکیب‌های صحیح می‌تواند بسیار قدرتمند باشد. برای شرایط چندگانه SUMIFS و برای نیازهای پیچیده‌تر SUMPRODUCT یا توابع پویا مانند FILTER پیشنهاد می‌شود. همواره فرمت داده‌ها را کنترل کنید و از جداول اکسل برای نگهداری محدوده‌های پویا استفاده نمایید.

در صورت نیاز به مثال اختصاصی با داده‌های شما، می‌توانم فرمول‌های دقیق‌تر و بهینه‌سازی شده را بر اساس ساختار فایل اکسل شما پیشنهاد دهم.

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

ارسال