ویدیو آموزش

 

تابع COUNTIF در Excel — آموزش کامل با مثال‌های کاربردی

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

تابع COUNTIF بخشی از مجموعه توابع شرطی (Conditional Functions) اکسل است و ساختار ساده‌ای دارد. این تابع تنها با دو آرگومان کار می‌کند:
۱. محدوده (Range)
۲. شرط (Criteria)

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

کاربرد COUNTIF در معیارهای عددی (Numeric Criteria)

یکی از رایج‌ترین استفاده‌های COUNTIF، شمارش سلول‌ها بر اساس مقدارهای عددی است. با این تابع، می‌توان مشخص کرد که چه تعداد از داده‌ها:

• دقیقاً برابر یک عدد خاص هستند

• از یک عدد بزرگ‌تر هستند

• از یک مقدار کوچک‌ترند

• بین دو عدد قرار می‌گیرند

• یا حتی برابر یا بزرگ‌تر/کوچک‌تر از یک مقدار تعیین‌شده‌اند

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

برای مثال، فرمول زیر تعداد سلول‌هایی را شمارش می‌کند که مقدار آن‌ها دقیقاً برابر با عدد ۲۰ است:

=COUNTIF(A1:A50,”=20″)

در این مثال، اکسل محدوده A1 تا A50 را بررسی می‌کند و تعداد سلول‌هایی را که مقدارشان ۲۰ است، نمایش می‌دهد. این روش به‌خصوص زمانی کاربرد دارد که داده‌های تکراری در یک ستون دارید و می‌خواهید بدانید یک مقدار خاص چند بار تکرار شده است.

مثال‌های پیشرفته‌تر از COUNTIF:

تابع COUNTIF تنها به مقایسه عددی ساده محدود نمی‌شود. شما می‌توانید از آن برای انجام تحلیل‌های پیچیده‌تر نیز استفاده کنید. برای مثال:

شمارش مقادیر بزرگ‌تر از یک عدد:

=COUNTIF(B2:B100,”>50″)

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

شمارش مقادیر کوچکتر یا برابر یک مقدار مشخص:

=COUNTIF(C1:C200,”<=100″)

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

=COUNTIF(D1:D300,”<>”)

شمارش تکرار یک کلمه یا متن:

=COUNTIF(E1:E150,”Excel”)

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

چرا استفاده از COUNTIF در سئو، گزارش‌گیری و تحلیل داده مهم است؟

در پروژه‌های کاری، تحلیل داده‌ها بخش حیاتی تصمیم‌گیری است. تابع COUNTIF کمک می‌کند تا:

• الگوها و تغییرات موجود در داده‌ها را سریع پیدا کنید

• گزارش‌های دقیق‌تری برای مدیریت ایجاد کنید

• سریعا نتایج آماری قابل اعتماد تولید کنید

• فرایندهای تحلیلی در اکسل را اتوماتیک کنید

همین ویژگی‌ها باعث شده COUNTIF به یکی از بهترین ابزارهای تحلیل داده در اکسل تبدیل شود:

۲. فرمول زیر با استفاده از COUNTIF، دقیقاً همان خروجی را تولید می‌کند:

۳. فرمول COUNTIF زیر تعداد سلول‌هایی را محاسبه می‌کند که مقدارشان بیش‌تر یا برابر با عدد 21 باشد:

۴. فرمول COUNTIF زیر نتیجه‌ای کاملاً مشابه را ارائه می‌دهد:

توضیح: استفاده از عملگر & باعث می‌شود علامت “بزرگ‌تر یا مساوی” با مقدار موجود در سلول C1 ترکیب گردد.
۵. فرمول COUNTIF زیر، تعداد سلول‌هایی را شمارش می‌کند که مقدارشان با عدد 20 برابر نباشد:

۶. فرمول‌های COUNTIF زیر، تعداد سلول‌هایی را محاسبه می‌کنند که مقدار آن‌ها برابر با عدد 20 یا 30 باشد:

 

ترفندهای مرتبط با متن

با بهره‌گیری از تابع COUNTIF و چند روش ساده در اکسل، می‌توانید تعداد سلول‌هایی را که حاوی یک متن مشخص هستند بشمارید. توجه داشته باشید که متن موردنظر باید درون علامت نقل‌قول دوتایی (” “) نوشته شود.

1.فرمول COUNTIF زیر، تعداد سلول‌هایی را محاسبه می‌کند که دقیقاً شامل واژه “سیب” هستند:

 

۲. فرمول COUNTIF زیر، تعداد سلول‌هایی را شمارش می‌کند که شامل “سیب” به‌همراه دقیقاً یک کاراکتر اضافه باشند. علامت سؤال (?) نمایانگر یک کاراکتر واحد است:

 

۳. فرمول COUNTIF زیر تعداد سلول‌هایی را شمارش می‌کند که دقیقاً شامل کلمه «سیب» همراه با رشته‌ای متشکل از صفر یا چند کاراکتر دیگر باشند. علامت ستاره (*) نمایانگر رشته‌ای است که می‌تواند هیچ یا چند کاراکتر را شامل شود:

۴. فرمول COUNTIF زیر تعداد سلول‌هایی را محاسبه می‌کند که به هر صورت شامل کلمه (سیب) باشند:

۵. فرمول COUNTIF زیر تعداد سلول‌هایی را شمارش می‌کند که حاوی متن باشند:

شمارش مقادیر منطقی (Boolean) در اکسل

در اکسل، داده‌ها فقط شامل عدد و متن نمی‌شوند؛ بلکه در بسیاری از پروژه‌های تحلیلی، گزارش‌گیری‌ها، فرمول‌نویسی‌های پیشرفته و حتی داشبوردهای مدیریتی، از مقادیر منطقی یا Boolean نیز استفاده می‌شود. مقادیر منطقی که شامل TRUE و FALSE هستند، نقش مهمی در فیلتر کردن داده‌ها، کنترل شرایط، تست فرمول‌ها و ایجاد ساختارهای منطقی دارند. برای شمارش این مقادیر، یکی از بهترین ابزارهای موجود در Excel تابع COUNTIF است.

تابع COUNTIF امکان شمارش سلول‌ها بر اساس یک شرط خاص را فراهم می‌کند و به‌راحتی می‌تواند تشخیص دهد که در یک محدوده مشخص چند مقدار TRUE یا FALSE وجود دارد. این قابلیت در زمان‌هایی کاربرد دارد که داده‌های شما حاصل فرمول‌های منطقی مانند IF، OR، AND، NOT یا فرمول‌های مقایسه‌ای باشند و بخواهید بدانید چند مورد از آن‌ها نتیجه مثبت یا منفی داشته‌اند.

به‌عنوان مثال، فرمول زیر تعداد سلول‌هایی را که مقدار TRUE دارند، محاسبه می‌کند:

۲. با استفاده از تابع COUNTIF زیر، می‌توان تعداد سلول‌هایی را که مقدار منطقی FALSE دارند، مشخص کرد:

 

شمارش خطاها در اکسل

برای شمارش انواع خاصی از خطاها، می‌توانید از تابع COUNTIF استفاده نمایید.
۱. در مثال زیر، این تابع تعداد سلول‌هایی را که حاوی خطای #NAME? هستند، محاسبه می‌کند:

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

نکته: برای وارد کردن یک فرمول آرایه‌ای، لازم است هم‌زمان کلیدهای CTRL + SHIFT + ENTER را فشار دهید تا اکسل به‌صورت خودکار آن را داخل آکولادهای {} قرار دهد. اما در نسخه‌های جدیدتر مانند Excel 365 یا Excel 2021، تنها زدن کلید Enter کافی است و نیازی به وارد کردن آکولادها نیست.
برای آموزش کامل نحوه ساخت این نوع فرمول‌ها، می‌توانید به بخش «شمارش خطاها» در سایت ما مراجعه نمایید.

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

شمارش با استفاده از چند شرط (AND) در اکسل

استفاده از شرط‌های ترکیبی AND در اکسل بسیار ساده است. تابع COUNTIFS (که در انتها حرف S دارد) به شما این امکان را می‌دهد که بر اساس چندین شرط، سلول‌ها را بشمارید.

۱. برای نمونه، اگر بخواهید تعداد ردیف‌هایی را که هم شامل کلمه “سیب” و هم “سبز” هستند بشمارید، می‌توانید از COUNTIFS بهره بگیرید:

 

استفاده از شرط‌های OR در اکسل

شمارش بر پایه شرط‌های OR در اکسل ممکن است در برخی موارد کمی دشوار به نظر برسد.
۱. در مثال زیر از توابع COUNTIF استفاده شده تا تعداد سلول‌هایی که شامل “Google” یا “Facebook” هستند (در یک ستون مشخص) محاسبه شود. این نوع کاربرد همچنان ساده بوده و پیچیدگی خاصی ندارد:

۲. با این‌ حال، اگر قصد دارید تعداد ردیف‌هایی را محاسبه کنید که در یکی از دو ستون شامل “سیب” یا “قرمز” باشند، دیگر نمی‌توان تنها با استفاده‌ی ساده و مستقیم از تابع COUNTIF (دو بار) به نتیجه رسید. (به تصویر زیر توجه کنید):

نکته: در این حالت، ردیف‌هایی که شامل هر دو عبارت “سبز” و “سیب” باشند، دوبار در شمارش لحاظ می‌شوند، در حالی که هدف ما این است که هر ردیف فقط یک‌بار محاسبه شود. مقدار صحیح برای این مثال عدد ۴ است.
۳. فرمول آرایه‌ای زیر برای برطرف کردن این مسئله طراحی شده است:

نکته: برای وارد کردن فرمول‌های آرایه‌ای، باید هم‌زمان کلیدهای CTRL + SHIFT + ENTER را فشار دهید تا اکسل به طور خودکار آن را درون آکولادهای {} قرار دهد. در نسخه‌های جدید مانند Excel 365 یا Excel 2021 کافی است فقط کلید Enter را بزنید و نیازی به نمایش کروشه‌ها نیست.
برای راهنمایی کامل‌تر در مورد نحوه نوشتن این فرمول‌ها، می‌توانید به صفحه «شمارش با شرط OR» در سایت ما مراجعه کنید.

برای مشاهده آموزش تفاوت و کاربردهای SUBSTITUTE و REPLACE در اکسل؛ کدام را استفاده کنیم؟ کلیک کنید.

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

تابع COUNTIF یکی از ابزارهای بسیار مفید و پرکاربرد اکسل است. بیایید چند نمونه جالب دیگر را با هم مرور کنیم:
۱. فرمول COUNTIF زیر از یک محدوده نام‌گذاری‌شده استفاده می‌کند. محدوده‌ای با نام سن  که به سلول‌های A1 تا A6 اشاره دارد:

۲. فرمول COUNTIF زیر تعداد سلول‌هایی را محاسبه می‌کند که مقدار آن‌ها کمتر از میانگین سن (۳۲.۲) باشد:

۳. برای شمردن سلول‌هایی که مقدارشان در بازه بین دو عدد مشخص قرار دارد، می‌توانید از تابع  COUNTIFS  با حرف S در انتها بهره ببرید:

۴. برای محاسبه تعداد دفعات تکرار هر مقدار در محدوده‌ای به نام سن , می‌توان از تابع COUNTIF استفاده کرد:

نکته: در سلول B2 فرمول:

 =COUNTIF(“سن”,A2)

وارد شده، سلول B3 شامل =COUNTIF(Ages,A3) است و این روند برای سایر سلول‌ها نیز ادامه دارد.

۵. برای شناسایی مقادیر تکراری در داده‌ها می‌توانید از تابع IF کمک بگیرید:

نکته: با استفاده از ترکیب تابع COUNTIF و ابزار قالب‌بندی شرطی (Conditional Formatting) می‌توانید مقادیر تکراری را در اکسل شناسایی کرده و آن‌ها را به‌صورت برجسته نمایش دهید.

جادوی شمارش در اکسل
تابع COUNTIF به‌تنهایی قادر نیست تعداد تکرار یک واژه خاص را درون یک سلول یا مجموعه‌ای از سلول‌ها بشمارد. برای رسیدن به این هدف، نیاز به کمی ترفند خاص داریم!
۱. فرمول زیر تعداد دفعاتی را که واژه “dog” در سلول A1 ظاهر شده، محاسبه می‌کند: