راهنمای کامل تابع COUNTIF در اکسل

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

سینتکس و مفهوم پایه

سینتکس تابع:

=COUNTIF(range, criteria)

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

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

• شمارش تعداد سلول‌هایی که دقیقا “apple” هستند:

=COUNTIF(A2:A100, "apple")

این فرمول تعداد دفعاتی که مقدار “apple” دقیقا در بازه A2:A100 ظاهر شده را برمی‌گرداند. تابع به صورت پیش‌فرض غیر حساس به حروف بزرگ/کوچک است (case-insensitive).

• شمارش مقادیر بزرگ‌تر یا مساوی 100:

=COUNTIF(B:B, ">=100")

با استفاده از عملگرهای مقایسه‌ای (<, >, <=, >=,  ) می‌توانید شرط‌های عددی تعریف کنید. دقت کنید که عملگر همراه با عدد در داخل نقل‌قول قرار می‌گیرد.

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

دو wildcard اصلی: * برای هر تعداد کاراکتر و ? برای یک کاراکتر. اگر متن حاوی پرسش‌نشان یا ستاره باشد، باید با ~ فرار (escape) شود.

=COUNTIF(A:A, "*report*")

این فرمول تمام سلول‌هایی را می‌شمارد که در متنشان کلمه “report” در هر جایی ظاهر شده باشد.

=COUNTIF(A:A, "data?")

این فرمول سلول‌هایی را که شامل عبارت پنج‌حرفی و حرف آخر متغیر هستند (مثال: data1، dataA) می‌شمارد.

=COUNTIF(A:A, "~*")

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

شمارش سلول‌های خالی و غیرخالی

• خالی‌ها:

=COUNTIF(C:C, "")

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

• غیرخالی‌ها:

=COUNTIF(C:C, "")

تعداد سلول‌هایی که حاوی مقدار هستند (نه خالی) را می‌شمارد.

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

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

=COUNTIF(D:D, ">" & DATE(2023,1,1))

این فرمول تعداد تاریخ‌هایی را می‌شمارد که بعد از اول ژانویه 2023 هستند. اتصال رشته با & ضروری است زیرا شرط تاریخ باید در قالب متن ساخته شود.

وقتی چند شرط دارید — COUNTIFS و روش‌های OR

COUNTIF فقط یک شرط را پشتیبانی می‌کند. برای چند شرط از COUNTIFS استفاده کنید (توجه: COUNTIFS از ترکیب شروط با «و» پشتیبانی می‌کند):

=COUNTIFS(A:A, "apple", B:B, ">50")

این فرمول سلول‌هایی را می‌شمارد که هم در ستون A مقدار “apple” و هم مقدار متناظر در ستون B بزرگ‌تر از 50 باشد.

برای منطق «یا» (OR) چند راه وجود دارد:

=SUM(COUNTIF(A:A, {"apple","orange"}))

این فرمول مجموع تعداد “apple” و “orange” را در ستون A برمی‌گرداند. در اکسل‌های قدیمی ممکن است لازم باشد با Ctrl+Shift+Enter اجرا شود، ولی نسخه‌های جدید dynamic array آن را بدون نیاز به این کار اجرا می‌کنند.

=SUMPRODUCT((A2:A100="apple")+(A2:A100="orange"))

SUMPRODUCT روشی انعطاف‌پذیر برای محاسبات OR و کار با شرط‌های پیچیده است. توجه کنید که SUMPRODUCT معمولاً برای دامنه‌های بزرگ کندتر از COUNTIF است.

شمارش مقادیر یکتا (Unique) با کمک COUNTIF

برای شمارش مقدارهای یکتای ظاهرشده در یک ستون می‌توان از ترکیب COUNTIF و SUMPRODUCT استفاده کرد:

=SUM(1/COUNTIF(A2:A100, A2:A100))

این فرمول در اکسل‌های قدیمی باید به‌صورت آرایه‌ای وارد شود (Ctrl+Shift+Enter). در نسخه‌های جدید اکسل با پشتیبانی از توابع آرایه دینامیک معمولا بدون وارد کردن آرایه کار می‌کند. ایده اصلی این است که برای هر مقدار، تعداد تکرارش را محاسبه کنیم و معکوس آن را جمع بزنیم تا هر مقدار یکتا معادل 1 به مجموع اضافه شود.

خطاها، محدودیت‌ها و نکات حرفه‌ای

• COUNTIF حساس به نوع داده‌ها نیست: گاهی عددی که به صورت متن ذخیره شده شمارش نمی‌شود مگر اینکه نوعش تبدیل شود.
• COUNTIF تنها یک شرط را می‌پذیرد — برای شروط چندگانه از COUNTIFS یا ترکیب توابع استفاده کنید.
• عملکرد: روی محدوده‌های بسیار بزرگ (مانند ستون کامل در ورک‌بوک‌های بزرگ) سرعت کاهش می‌یابد؛ برای سرعت بهتر بازه‌ها را محدود کنید یا از جداول ساختاری استفاده کنید.
• استفاده از نام دامنه‌های پویا یا جدول اکسل (Structured Table) باعث خوانایی و پایداری فرمول می‌شود.

نمونه جدول خلاصه

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

تابع COUNTIF برای تحلیل سریع و گزارش‌های یک شرطی عالی است. با یادگیری wildcardها، کار با تاریخ‌ها و ترکیب با COUNTIFS یا SUMPRODUCT می‌توانید سناریوهای پیچیده‌تری را نیز پوشش دهید. همواره محدوده‌ها را بهینه کنید و اگر نیاز به شروط چندگانه یا OR پیچیده دارید از روش‌های جایگزین بهره ببرید.

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

ارسال