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

یکی از کارهایی که در اکسل زیاد انجام می‌دهیم، جمع کردن اعداد است. برای یک جمع ساده معمولاً از تابع SUM استفاده می‌کنیم؛ اما همیشه قرار نیست همه اعداد را با هم جمع کنیم.

گاهی می‌خواهیم فقط اعدادی را جمع کنیم که یک شرط مشخص دارند.

مثلاً:

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

در این شرایط تابع SUMIF یکی از معروف‌ترین و کاربردی‌ترین توابع اکسل است.

تابع SUMIF در اکسل چیست؟

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

این تابع مشهور و پرکاربرد ترکیب دو تابع IF و Sum می باشد. به طور خلاصه می توان گفت: تابع SUMIF محدوده ای از داده های عددی را به صورت مشروط جمع می کند.

این تابع سه پارامتر به شکل زیر دارد:

=SUMIF(range,criteria,[sum_range])

Range محدوده ای که باید شرط اول روی آن اعمال شود.

Criteria شرطی که باید اعمال شود.

Sum_Range محدوده ای است که باید جمع شود. قسمت sum_range اختیاری است.

مثال: می خواهیم در لیست زیر جمع فروش کالای مداد رنگی را داشته باشیم.

یک مثال ساده دیگر از SUMIF

فرض کنید جدول فروش زیر را داریم:

فروشنده	مبلغ فروش
علی	12,000,000
رضا	18,000,000
علی	15,000,000
مریم	22,000,000
علی	20,000,000

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

=SUMIF(A2:A6,"علی",B2:B6)

اکسل در محدوده A2:A6 به دنبال «علی» می‌گردد و مبالغ متناظر در ستون B را جمع می‌کند.

بنابراین:

12,000,000 + 15,000,000 + 20,000,000

برابر با:

47,000,000

خواهد بود.

تفاوت SUM و SUMIF چیست؟

تابع SUM همه اعداد یک محدوده را جمع می‌کند:

=SUM(B2:B6)

اما SUMIF فقط اعدادی را جمع می‌کند که شرط مشخصی داشته باشند:

=SUMIF(A2:A6,"علی",B2:B6)

بنابراین:

SUM => جمع ساده

SUMIF => جمع شرطی

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

SUMIF با شرط عددی

شرط SUMIF فقط متن نیست. می‌توانیم از عملگرهای ریاضی نیز استفاده کنیم.

فرض کنید در ستون B مبالغ فروش قرار دارند و می‌خواهیم فقط فروش‌های بیشتر از 10 میلیون را جمع کنیم:

=SUMIF(B2:B20,">10000000")

در این حالت خود محدوده B2:B20 هم محدوده بررسی شرط و هم محدوده جمع است.

می‌توانیم از عملگرهای مختلف استفاده کنیم:

">10000000"     بزرگ‌تر از 10 میلیون
"<10000000"     کوچک‌تر از 10 میلیون
">=10000000"    بزرگ‌تر یا مساوی
"<=10000000"    کوچک‌تر یا مساوی
"=10000000"     مساوی
"<>10000000"    مخالف

استفاده از یک سلول به‌عنوان شرط

بهتر است در فایل‌های حرفه‌ای، شرط را همیشه مستقیماً داخل فرمول ننویسیم.

مثلاً فرض کنید مقدار موردنظر در سلول E2 قرار دارد و می‌خواهیم فروش‌های بیشتر از این مقدار را جمع کنیم:

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

اگر مقدار E2 برابر 10,000,000 باشد، فرمول فروش‌های بیشتر از 10 میلیون را جمع می‌کند.

اگر مقدار E2 را تغییر دهیم، نتیجه نیز به‌صورت خودکار تغییر خواهد کرد.

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

جمع فروش یک محصول خاص

فرض کنید جدول زیر را داریم:

محصول	فروش
لپ‌تاپ	45,000,000
مانیتور	15,000,000
لپ‌تاپ	38,000,000
کیبورد	5,000,000
لپ‌تاپ	52,000,000

برای محاسبه مجموع فروش لپ‌تاپ:

=SUMIF(A2:A6,"لپ‌تاپ",B2:B6)

نتیجه:

135,000,000

خواهد بود.

این فرمول در گزارش‌های فروش بسیار پرکاربرد است.

SUMIF با شرط متنی

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

مثلاً:

محصول	دسته	مبلغ
لپ‌تاپ	دیجیتال	45,000,000
میز	اداری	12,000,000
مانیتور	دیجیتال	15,000,000
صندلی	اداری	8,000,000
چاپگر	دیجیتال	20,000,000

اگر بخواهیم مجموع فروش گروه «دیجیتال» را محاسبه کنیم:

=SUMIF(B2:B6,"دیجیتال",C2:C6)

نتیجه:

80,000,000

خواهد بود.

استفاده از Wildcard در SUMIF

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

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

* برای هر تعداد کاراکتر
? برای یک کاراکتر

مثلاً اگر محصولات مختلفی با کلمه «سامسونگ» شروع شوند:

=SUMIF(A2:A100,"سامسونگ*",B2:B100)

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

مثلاً موارد زیر می‌توانند در شرط قرار بگیرند:

سامسونگ A55
سامسونگ S24
سامسونگ S25 Ultra

جمع مقادیر مخالف یک شرط

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

می‌توانیم از <> استفاده کنیم:

=SUMIF(A2:A100,"<>لپ‌تاپ",B2:B100)

در این حالت تمام مقادیری که نام محصول آن‌ها «لپ‌تاپ» نیست، در جمع لحاظ می‌شوند.

SUMIF برای تاریخ‌ها

SUMIF فقط برای متن و عدد کاربرد ندارد و می‌توانیم از آن برای کار با تاریخ‌ها نیز استفاده کنیم.

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

اگر تاریخ موردنظر در سلول E2 باشد:

=SUMIF(A2:A100,E2,B2:B100)

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

مثلاً:

=SUMIF(A2:A100,">="&E2,B2:B100)

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

یک کاربرد مهم؛ محاسبه فروش یک فروشنده

فرض کنید یک فایل فروش داریم:

تاریخ	فروشنده	محصول	مبلغ
1 فروردین	علی	لپ‌تاپ	40,000,000
2 فروردین	رضا	مانیتور	15,000,000
3 فروردین	علی	چاپگر	10,000,000
4 فروردین	مریم	لپ‌تاپ	35,000,000
5 فروردین	علی	مانیتور	18,000,000

اگر بخواهیم مجموع فروش علی را به دست بیاوریم:

=SUMIF(B2:B6,"علی",D2:D6)

نتیجه:

68,000,000

است.

حالا اگر نام فروشنده را در سلول F2 قرار دهیم:

=SUMIF(B2:B6,F2,D2:D6)

با تغییر نام فروشنده در F2، گزارش نیز به‌صورت خودکار تغییر می‌کند.

این روش برای ساخت گزارش‌های مدیریتی بسیار بهتر از نوشتن نام فروشنده داخل فرمول است.

تفاوت SUMIF و SUMIFS

یکی از مهم‌ترین نکاتی که هنگام یادگیری SUMIF باید بدانیم، تفاوت آن با SUMIFS است.

اگر فقط یک شرط داریم، از SUMIF استفاده می‌کنیم:

=SUMIF(B2:B100,"علی",D2:D100)

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

مثلاً:

مجموع فروش علی در فروردین

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

فروشنده = علی
تاریخ = فروردین

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

به‌صورت خلاصه:

یک شرط → SUMIF

چند شرط → SUMIFS

تفاوت SUMIF و AVERAGEIF

در مجموعه توابع شرطی اکسل، ممکن است با AVERAGEIF نیز مواجه شوید.

SUMIF برای جمع استفاده می‌شود:

=SUMIF(A2:A20,"علی",B2:B20)

اما AVERAGEIF برای میانگین:

=AVERAGEIF(A2:A20,"علی",B2:B20)

بنابراین اگر سؤال ما این باشد که:

«مجموع فروش علی چقدر است؟»

از SUMIF استفاده می‌کنیم.

اگر سؤال این باشد که:

«میانگین فروش علی چقدر است؟»

از AVERAGEIF استفاده می‌کنیم.

استفاده از SUMIF برای هزینه‌ها

این تابع فقط برای فروش نیست.

فرض کنید هزینه‌های یک شرکت به شکل زیر ثبت شده‌اند:

واحد	هزینه
اداری	12,000,000
تولید	25,000,000
اداری	8,000,000
فروش	15,000,000
تولید	30,000,000

برای محاسبه مجموع هزینه‌های واحد تولید:

=SUMIF(A2:A6,"تولید",B2:B6)

نتیجه:

55,000,000

خواهد بود.

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

مدیریت خطا و داده‌های ناموجود

SUMIF برخلاف AVERAGEIF، زمانی که هیچ رکوردی با شرط موردنظر پیدا نکند، معمولاً نتیجه 0 را برمی‌گرداند.

مثلاً:

=SUMIF(A2:A100,"محصول ناموجود",B2:B100)

اگر چنین محصولی وجود نداشته باشد، نتیجه صفر خواهد بود.

در صورت نیاز می‌توانیم با IF یا IFERROR نتیجه را به شکل مناسب‌تری نمایش دهیم.

مثلاً:

=IF(SUMIF(A2:A100,E2,B2:B100)=0,"فروشی ثبت نشده",SUMIF(A2:A100,E2,B2:B100))

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

چند مثال سریع از SUMIF

جمع فروش یک فروشنده

=SUMIF(A2:A100,"علی",B2:B100)

جمع فروش بیشتر از 10 میلیون

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

جمع فروش کمتر از 5 میلیون

=SUMIF(B2:B100,"<5000000")

جمع فروش یک محصول که نام آن در E2 قرار دارد

=SUMIF(A2:A100,E2,B2:B100)

جمع محصولاتی که با «سامسونگ» شروع می‌شوند

=SUMIF(A2:A100,"سامسونگ*",B2:B100)

جمع همه موارد به‌جز یک محصول خاص

=SUMIF(A2:A100,"<>لپ‌تاپ",B2:B100)

یک نکته مهم در استفاده از SUMIF

یکی از اشتباهات رایج این است که محدوده شرط و محدوده جمع را اشتباه انتخاب کنیم.

برای مثال:

=SUMIF(A2:A100,"علی",B2:B100)

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

بنابراین بهتر است ساختار جدول مرتب و محدوده‌ها هم‌راستا باشند.

جمع‌بندی

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

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

ساختار اصلی آن را به خاطر بسپارید:

=SUMIF(range,criteria,[sum_range])

به زبان ساده:

محدوده را بررسی کن → شرط را پیدا کن → مقادیر متناظر را جمع کن.

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

در واقع SUMIF یکی از همان توابعی است که یادگیری آن ساده است، اما در فایل‌های واقعی اکسل کاربرد بسیار زیادی دارد و می‌تواند بسیاری از جمع‌های دستی و زمان‌بر را به یک فرمول ساده تبدیل کند.