تابع COUNTIFS در اکسل — راهنمای جامع و کاربردی

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

ساختار و پارامترها

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

• criteria_range1: بازه‌ای که شرط اول روی آن اعمال می‌شود.
• criteria1: شرطی که باید بر روی criteria_range1 بررسی شود (مثلاً “Apple” یا “>100”).
• تابع می‌تواند تا 127 جفت بازه-شرط داشته باشد.

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

=COUNTIFS(A2:A100, "Apple", B2:B100, "Sold")

این فرمول تعداد ردیف‌هایی را می‌شمارد که در ستون A مقدار “Apple” و در ستون B مقدار “Sold” باشند. فرمول به حروف بزرگ/کوچک حساس نیست (case-insensitive).

=COUNTIFS(C2:C200, ">50", D2:D200, "<=100")

تعداد سلول‌هایی در بازه C2:C200 که مقدار بزرگتر از 50 و در بازه D2:D200 مقدار کوچکتر مساوی 100 دارند را می‌شمارد (دقت کنید هر جفت شرط روی بازه‌های مجزا اعمال می‌شود).

استفاده از عملگرها و wildcard

• برای مقایسه‌های بزرگتر/کوچکتر از نمادهای متنی مانند “>100” یا “<=2025-01-01” یا بهتر از تابع DATE استفاده کنید.
• برای جستجوی ترکیبی از متن از wildcardها استفاده کنید: “*” به معنی هر تعدادی از کاراکترها، “?” به معنی یک کاراکتر.

=COUNTIFS(A2:A100, "*apple*", B2:B100, "")

این مثال ردیف‌هایی را می‌شمارد که در ستون A شامل کلمه ‘apple’ در هر جایی از متن باشند و ستون B خالی نباشد. علامت “” به معنی «مخالف خالی» است.

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

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

=COUNTIFS(DateRange, ">=" & DATE(2025,1,1), DateRange, "<=" & DATE(2025,12,31))

این فرمول تعداد سطرهایی را می‌شمارد که تاریخ آن‌ها در بازه سال 2025 قرار دارد. توجه کنید که برای عملگرها باید از عمل concatenation (&) استفاده شود.

حالت‌های OR (یا) با COUNTIFS

COUNTIFS به‌طور پیش‌فرض منطق AND دارد (همه شرط‌ها باید برقرار باشند). برای پیاده‌سازی منطق OR می‌توان از ترکیب توابع SUM و COUNTIFS استفاده کرد:

=SUM( COUNTIFS(A2:A100, {"Apple","Banana"}, B2:B100, "Sold") )

در این مثال شمارش ردیف‌هایی که در ستون A یا “Apple” یا “Banana” باشند و در ستون B “Sold” دارند را برمی‌گرداند. خروجی COUNTIFS با آرایه ورودی یک آرایه از نتایج تولید می‌کند که با SUM جمع زده می‌شود. این روش در اکسل‌های جدید (که فرمول‌های آرایه‌ای پویایی دارند) ساده و قدرتمند است.

مثال پیشرفته — ترکیب AND و OR

=SUM( COUNTIFS(A2:A100, {"Apple","Banana"}, B2:B100, {"Sold","Returned"}) )

این فرمول تعداد تمام ترکیبات ممکن از دو لیست شرط را می‌شمارد (جمع 4 شمارش ترکیبی). اگر می‌خواهید از ترکیب‌های مشخص استفاده کنید باید از SUMPRODUCT یا تنظیمات پیچیده‌تر کمک بگیرید.

جایگزین SUMPRODUCT برای شرایط پیچیده

=SUMPRODUCT( (A2:A100="Apple") * (B2:B100="Sold") * (C2:C100>100) )

SUMPRODUCT انعطاف بیشتری برای شرط‌های غیرمعمول مثل شرط‌های مبتنی بر تابع، مقایسه طول متن، یا شروطی که COUNTIFS از آن‌ها پشتیبانی نمی‌کند، ارائه می‌دهد. هر عبارت بولی به 1 یا 0 تبدیل شده و حاصل ضرب این‌ها جمع می‌شود.

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

• برای افزایش خوانایی از نامگذاری بازه‌ها (Named Ranges) یا جدول (Excel Table) استفاده کنید. مثال: COUNTIFS(Sales[Product], “Apple”, Sales[Status], “Sold”).
• در برخی نسخه‌های محلی اکسل جداکننده آرگومان‌ها ممکن است نقطه‌ویرگول (;) باشد—در صورت خطا آن را بررسی کنید.
• COUNTIFS حس حروف (case sensitivity) را رعایت نمی‌کند؛ اگر نیاز به حساسیت به حروف دارید باید از فرمول‌های پیچیده‌تر مثل SUMPRODUCT با EXACT استفاده کنید.
• عملکرد: استفاده از کل ستون‌ها (مثلاً A:A) در محدوده‌های بسیار بزرگ روی نسخه‌های قدیمی می‌تواند سرعت را کاهش دهد؛ بهتر است بازه‌ها را محدود کنید یا از جدول استفاده کنید.
• برای شمارش خالی‌ها از criteria =”” و برای غیرخالی از “” استفاده کنید.
• COUNTIFS تا 127 جفت شرط را پشتیبانی می‌کند—اگر بیش از این نیاز دارید باید از روش‌های دیگر مثل پایگاه داده یا Power Query استفاده کنید.

خطاهای رایج

• طول بازه‌ها یکسان نیست: همه criteria_rangeها باید طول یکسان داشته باشند؛ در غیر این صورت خطا خواهید گرفت.
• استفاده نادرست از تاریخ‌ها: تاریخ‌ها باید تاریخ واقعی در اکسل باشند، نه رشته‌های متنی.
• انتظار رفتار OR به‌جای AND: COUNTIFS همیشه AND است مگر اینکه از تکنیک‌های آرایه‌ای یا SUM/ADD استفاده کنید.

جمع‌بندی

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

در صورت نیاز می‌توان مثال‌های داده‌محور (با جدول نمونه) یا نسخه‌هایی برای اکسل فارسی/انگلیسی را آماده کرد تا فرمول‌ها مستقیم در کاربرگ شما اجرا شوند.

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

ارسال