تابع COUNTIF در Excel

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

معیارهای عددی (Numeric Criteria)

با استفاده از COUNTIF می‌توان تعداد سلول‌هایی را که دارای مقدار مشخصی هستند یا مقادیری بزرگ‌تر، کوچک‌تر یا مساوی یک عدد خاص دارند، محاسبه کرد.
به عنوان مثال، فرمول زیر تعداد سلول‌هایی را شمارش می‌کند که مقدار آن‌ها دقیقاً برابر با عدد ۲۰ است:

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

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

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

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

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

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

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

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

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

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

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

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

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

برای شمارش مقادیر منطقی مانند TRUE یا FALSE در اکسل، می‌توانید از تابع COUNTIF بهره ببرید.
به‌عنوان مثال، فرمول زیر تعداد سلول‌هایی را که مقدار 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» در سایت ما مراجعه کنید.

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

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

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

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

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

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

 =COUNTIF(“سن”,A2)

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

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

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

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

مقالات پیشنهادی:

1. آموزش کامل فرمول SUM در اکسل به زبان ساده
2. آموزش مرحله به مرحله ترکیب توابع match و index در اکسل
3. آموزش توابع Sumif و Sumifs در اکسل و بررسی تفاوت های آن‌ها