تمرین ۱: ترکیب توابع SUMIF و COUNTIF + سناریو و تمرین در محیط اکسل ۳۶۰

مقدمه

در تحلیل داده‌های اکسل، گاهی صرفاً شمارش تعداد رخدادها (تعداد دفعاتی که یک شرط برقرار شده) کافی نیست. بلکه می‌خواهیم جمع مقادیر مرتبط با آن شرط را هم محاسبه کنیم. ترکیب توابع SUMIF و COUNTIF این نیاز را به‌خوبی رفع می‌کند. در این صفحه شما یاد می‌گیرید چگونه با استفاده همزمان از این دو تابع، گزارش‌های دقیق‌تری از داده‌های خود تهیه کنید و تحلیل ارزشمندتری ارائه دهید.

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

کلمات کلیدی

SUMIF، COUNTIF، ترکیب SUMIF و COUNTIF، جمع شرطی در اکسل، شمارش شرطی در اکسل، تحلیل داده در Excel، آموزش توابع اکسل، گزارش‌گیری با اکسل

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

=SUMIF(range, criteria, [sum_range])

در اینجا range محدوده‌ای است که شرط روی آن اعمال می‌شود، criteria شرطی است که مشخص می‌کنید (مثل عدد، متن، یا عبارت منطقی)، و sum_range (اختیاری) محدوده‌ای است که مقادیر آن جمع می‌شوند اگر شرط برقرار باشد. اگر sum_range مشخص نشود، اکسل روی همان range جمع انجام می‌دهد.

تابع COUNTIF نیز برای شمارش تعداد سلول‌هایی کاربرد دارد که شرط خاصی را دارند. ساختار آن ساده است:

=COUNTIF(range, criteria)

در این تابع نیز range بازه‌ای است که قرار است سلول‌ها در آن بررسی شوند و criteria شرطی است که باید برآورده شود. این تابع از عملگرهای منطقی و حتی واژگان جایگزین (wildcard) مثل * و ? هم پشتیبانی می‌کند.

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

مثال ترکیبی:

فرض کنید ستونی دارید که نام محصولات را نشان می‌دهد (ستون A) و ستونی دیگر مقدار فروش هر محصول را (ستون B).

• اگر بخواهید تعداد دفعات فروش یک محصول خاص (مثلاً «سیب») را محاسبه کنید، از COUNTIF به صورت زیر استفاده می‌کنید:
• =COUNTIF(A:A, “سیب”)
• اگر بخوای جمع کل فروش آن محصول خاص را حساب کنی، از SUMIF به این شکل استفاده کن:
• =SUMIF(A:A, “سیب”, B:B)

با این دو فرمول کنار هم، می‌توانید بفهمی «سیب» چند بار فروخته شده و مجموع مبلغ فروش آن چقدر بوده است.

این ترکیب در تحلیل‌های پیشرفته‌تر هم قابل گسترش است، مخصوصاً وقتی بخوای چند شرط همزمان (مثلاً شهر فروش + محصول) را بررسی کنی، می‌تونی از توابع پیشرفته‌تری مثل SUMIFS و COUNTIFS استفاده کنی. 
همچنین توجه داشته باشید که در نوشتن شرط‌ها باید دقت کنید؛ مثلاً شرط‌های متنی باید در گیومه (” “) قرار بگیرند و می‌توانی از wildcardها هم استفاده کنی.

سناریوی کاربردی 1

فرض کنید شما مدیر مالی یک شرکت کوچک هستی و در اکسل لیستی از فروش ماهانه محصولات داری، به شکل زیر:

هدف شما:

1. ‌تعداد دفعات فروش هر محصول را بدانید.
2. ‌جمع کل مبلغ فروش هر محصول را محاسبه کنید.

مراحل:

1. جدولی با نام محصولات یکتا (مثل «سیب»، «موز»، «پرتقال») ایجاد کن.
2. در ستون کنار هر محصول، فرمول COUNTIF بنویس:
3. =COUNTIF(A:A, C2)

(فرض کن C2 سلول «سیب» است)

1. در ستون بعدی، فرمول SUMIF بنویس:
2. =SUMIF(A:A, C2, C:B־amount)

(باز هم فرض کن بازه مقادیر فروش در ستون B است.)

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

ترکیب توابع SUMIF و COUNTIF در Excel یکی از روش‌های قدرتمند برای تحلیل داده‌هاست، مخصوصاً زمانی که می‌خواهی هم تعداد و هم مجموع مقادیر مرتبط با یک شرط خاص را بررسی کنی. در ادامه چند حالت کاربردی و مثال برایت آوردم:

✅ تعریف کوتاه توابع

• SUMIF(range, criteria, sum_range)
  مجموع مقادیر در محدوده‌ای خاص که با شرط مشخص مطابقت دارند.
• COUNTIF(range, criteria)
  تعداد سلول‌هایی که با شرط مشخص مطابقت دارند.

🧮 مثال‌های ترکیبی

1. محاسبه میانگین شرطی با SUMIF و COUNTIF

اگر بخواهی میانگین فروش برای یک محصول خاص را محاسبه کنی:

=SUMIF(A2:A100, "محصول A", B2:B100) / COUNTIF(A2:A100, "محصول A")

• ستون A شامل نام محصولات است.
• ستون B شامل مبلغ فروش است.
• این فرمول مجموع فروش محصول A را تقسیم بر تعداد دفعات فروش آن می‌کند.

2. استفاده در یک سلول با توابع تو در تو

اگر بخواهی بررسی کنی که آیا میانگین فروش محصول A از عدد خاصی بیشتر است:

=IF(SUMIF(A2:A100, "محصول A", B2:B100)/COUNTIF(A2:A100, "محصول A") > 500000, "بیشتر", "کمتر")

3. ترکیب با شرط‌های پویا

اگر بخواهی شرط را از یک سلول دیگر بگیری:

=SUMIF(A2:A100, C1, B2:B100) / COUNTIF(A2:A100, C1)

• سلول C1 شامل نام محصول مورد نظر است.

سناریوی کاربردی 2

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

برای حل مسئله در محیط واقعی اکسل از ترکیب توابع SUMIF و COUNTIF استفاده کنید.

نتیجه‌گیری

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

سؤالات متداول

+ - آیا SUMIF و COUNTIF به حروف بزرگ و کوچک حساس هستند؟

خیر، این توابع در اکسل معمولاً تفاوتی بین حروف بزرگ و کوچک قائل نمی‌شوند.

+ - آیا می‌توانم چند شرط را در SUMIF یا COUNTIF اعمال کنم؟

خیر، خودِ SUMIF و COUNTIF فقط یک شرط قبول می‌کنند. اگر بخوای چند شرط داشته باشی

(مثلاً محصول = «سیب» و شهر = «تهران»)،

باید از SUMIFS و COUNTIFS استفاده کنی

+ - آیا شرط می‌تواند شامل متن یا wildcard باشد؟

بله، در تابع SUMIF می‌توانی شرط متنی تعریف کنی مثل "سیب" یا از wildcard مثل "سیب*" استفاده کنی. همچنین در COUNTIF هم می‌توانی از * و ? استفاده کنی

+ - اگر sum_range را در SUMIF تعیین نکنم چه اتفاقی می‌افتد؟

اگر آرگومان sum_range را وارد نکنی، اکسل به طور پیش‌فرض از همان بازه‌ای استفاده می‌کند که شرط در آن بررسی شده است (یعنی همان range به عنوان محدوده جمع در نظر گرفته می‌شود