معرفی تابع تعداد شرطی countif اکسل

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

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

این تابع به اکسل می‌گوید:

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

ساختار تابع COUNTIF در اکسل

ساختار تابع به صورت زیر است:

=COUNTIF(range, criteria)

در این فرمول:

range محدوده‌ای است که می‌خواهیم در آن جست‌وجو کنیم.
criteria شرطی است که باید برای شمارش سلول‌ها مشخص کنیم.

این تابع مشهور و پرکاربرد ترکیب دو تابع IF و Count می باشد. به طور خلاصه می توان گفت: تابع CountIF تعداد در یک محـــدوده که دارای یک شـــرط خاص می باشد را می شمارد.

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

شمارش اعداد با COUNTIF

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

فرض کنید نمرات دانشجویان در محدوده B2:B20 قرار دارند و می‌خواهیم بدانیم چند نفر نمره بیشتر از 15 گرفته‌اند.

فرمول:

=COUNTIF(B2:B20,">15")

اکسل فقط سلول‌هایی را می‌شمارد که مقدار آنها بیشتر از 15 باشد.

شمارش نمرات کمتر از یک مقدار مشخص

برای مثال، برای شمارش نمرات کمتر از 10:

=COUNTIF(B2:B20,"<10")

و برای شمارش نمرات بزرگ‌تر یا مساوی 10:

=COUNTIF(B2:B20,">=10")

عملگرهای مقایسه‌ای مانند >, <, >=, <= و <> در COUNTIF کاربرد زیادی دارند.

شمارش سلول‌هایی که دقیقاً برابر یک عدد هستند

اگر بخواهیم تعداد افرادی را که نمره آنها دقیقاً 20 است محاسبه کنیم:

=COUNTIF(B2:B20,20)

در این حالت نیازی به قرار دادن عدد 20 داخل علامت نقل‌قول نیست.

البته این فرمول نیز قابل استفاده است:

=COUNTIF(B2:B20,"=20")

هر دو روش نتیجه یکسانی دارند.

شمارش سلول‌های حاوی یک متن مشخص

یکی دیگر از کاربردهای رایج COUNTIF، شمارش یک عبارت متنی است.

فرض کنید ستون A شامل وضعیت سفارش‌ها باشد:

وضعیت
ارسال شد
در انتظار
ارسال شد
لغو شد
ارسال شد

برای شمارش سفارش‌هایی که ارسال شده‌اند:

=COUNTIF(A2:A6,"ارسال شد")

نتیجه:

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

استفاده از COUNTIF برای سلول‌های خالی

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

برای مثال:

=COUNTIF(A2:A100,"")

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

در مقابل، برای شمارش سلول‌های غیرخالی می‌توان از شرط زیر استفاده کرد:

=COUNTIF(A2:A100,"<>")

البته در فایل‌های پیچیده، مخصوصاً زمانی که سلول‌ها دارای فرمول باشند، بهتر است نوع داده و وضعیت واقعی سلول‌ها را نیز در نظر بگیرید.

استفاده از علامت ستاره (*) در COUNTIF

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

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

برای مثال:

=COUNTIF(A2:A100,"Excel*")

می‌تواند سلول‌هایی را پیدا کند که متن آنها با Excel شروع می‌شود.

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

Excel
Excel Advanced
Excel 2021
Excel Training

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

استفاده از علامت سؤال (?) در COUNTIF

علامت ? نیز یک کاراکتر جایگزین است؛ با این تفاوت که فقط یک کاراکتر را جایگزین می‌کند.

برای مثال:

=COUNTIF(A2:A100,"م?مد")

می‌تواند متن‌هایی را پیدا کند که ساختار آنها مطابق الگوی موردنظر باشد و یک کاراکتر در محل ? قرار گرفته باشد.

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

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

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

فرض کنید در سلول D1 عبارت «تهران» نوشته شده است.

می‌توانیم بنویسیم:

=COUNTIF(A2:A100,D1)

در این حالت هر زمان مقدار D1 تغییر کند، نتیجه فرمول نیز تغییر خواهد کرد.

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

استفاده از COUNTIF با شرط عددی و سلول

فرض کنید مقدار موردنظر برای مقایسه در سلول D1 قرار دارد و می‌خواهیم تعداد اعداد بزرگ‌تر از آن را محاسبه کنیم.

فرمول:

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

در اینجا & برای اتصال عملگر > به مقدار موجود در سلول D1 استفاده شده است.

برای مثال اگر D1 برابر 15 باشد، فرمول عملاً تعداد مقادیر بزرگ‌تر از 15 را شمارش می‌کند.

COUNTIF در یک مثال واقعی فروش

فرض کنید جدول فروش یک فروشگاه به شکل زیر باشد:

محصول	تعداد فروش
لپ‌تاپ	5
مانیتور	3
لپ‌تاپ	7
موس	10
لپ‌تاپ	4

اگر بخواهیم بدانیم محصول «لپ‌تاپ» چند بار در جدول ثبت شده است:

=COUNTIF(A2:A6,"لپ‌تاپ")

نتیجه:

اما توجه کنید که این فرمول تعداد دفعات ثبت محصول را می‌شمارد، نه مجموع تعداد فروش آن را.

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

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

در این مثال نتیجه مجموع فروش لپ‌تاپ برابر با:

خواهد بود.

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

تفاوت COUNTIF و COUNTIFS

تابع COUNTIF برای شمارش بر اساس یک شرط طراحی شده است.

برای مثال:

=COUNTIF(B2:B100,">15")

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

برای مثال، فرض کنید می‌خواهیم تعداد دانشجویانی را پیدا کنیم که:

نمره آنها بیشتر از 15 است.
وضعیت آنها «فعال» است.

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

=COUNTIFS(B2:B100,">15",C2:C100,"فعال")

بنابراین به صورت ساده:

COUNTIF → شمارش با یک شرط

COUNTIFS → شمارش با چند شرط

جمع‌بندی

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

ساختار اصلی آن:

=COUNTIF(range,criteria)

است.

از این تابع می‌توان برای موارد مختلفی مانند:

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

استفاده کرد.

اگر فقط یک شرط برای شمارش دارید، COUNTIF معمولاً انتخاب مناسبی است. اما اگر لازم است چند شرط را هم‌زمان بررسی کنید، بهتر است سراغ COUNTIFS بروید.

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