آموزش تابع COUNTIF و COUNTIFS در اکسل | تفاوت، مثال و نکات کاربردی تابع شمارش شرطی اکسل

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

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

تابع COUNTIF در اکسل چیست؟

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

نحوه استفاده از تابع COUNTIF

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

=Counta(B2:B10)

=Counta(B2:B10)

• range: محدوده ای از سلول‌ها که شرط روی آن اعمال می شود.

• criteria: شرطی که باید بررسی شود (مثل “>100” یا “=تهران”).

مثال کاربردی از استفاده تابع COUNTIF

همان طور که در مثال زیر مشاهده می کنید، در ستون A لیست تعدادی از شهرها به همراه تکرار آن ها را آورده ایم. حال فرض کنید می خواهیم تعداد تکرار هر شهر را محاسبه کنیم.

برای این کار کافی است از فرمول COUNTIF استفاده کنیم. به این صورت که در بخش اول فرمول، محدوده ای که می خواهیم شمارش در آن انجام بگیرد را وارد می کنیم (در مثال ما A2:A15) و در بخش دوم فرمول، شرط را وارد می کنیم. نحوه وارد کردن شرط برای شمارش شهر تهران به این صورت هست که هم می توان عبارت “تهران” را به عنوان شرط قرار داد هم همان طور که ما در مثال انجام داده ایم می توان این شرط را به صورت متغیر یا داینامیک روی سلول D5 قرار داد که محتوای این سلول همان “تهران” است.

محدودیت های تابع COUNTIF

• فقط از یک شرط پشتیبانی می کند.

• شرط های پیچیده یا ترکیبی (AND/OR) قابل پیاده سازی مستقیم نیستند.

• در شرایطی که نیاز به بررسی چند ستون وجود دارد، جوابگو نخواهد بود.

تابع COUNTIFS در اکسل چیست؟

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

نحوه استفاده از تابع COUNTIFS

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

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• criteria_range1: محدوده ای که شرط اول در آن بررسی می شود.

• criteria1: شرط اول.

• و همین طور می توان به ترتیب چندین جفت شرط دیگر هم اضافه کرد.

مثال عملی از استفاده تابع COUNTIFS

همان طور که در مثال زیر مشاهده می کنید، در ستون A لیست ماه ها، در ستون B لیست تعدادی از شهرها و در ستون C مبلغ فروش هر شهر در آن ماه را آورده ایم. حال فرض کنید می خواهیم بررسی کنیم که هر شهر در چند ماه به تارگت فروش خود (حداقل فروش) دست پیدا کرده است. یعنی باید بررسی کنیم که مبلغ فروش هر شهر در چند ماه بیشتر از حداقل فروش تعیین شده برای آن شهر است.

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

• بخش اول تابع: محدوده “شهر” یا همان B2:B16
• بخش دوم تابع: شرط مربوط به بخش اول یعنی نام شهر مربوط یا همان F5 و F6 و F7
• بخش سوم تابع: محدوده “مبلغ فروش” یا همان C2:C16
• بخش سوم تابع: شرط مربوط به بخش سوم یعنی مثلاً برای شهر تهران “>=” & G5

محدودیت های تابع COUNTIFS

• نسخه های قدیمی تر اکسل (قبل از ۲۰۰۷) از این تابع پشتیبانی نمی کنند.

• همه محدوده های شرط ها باید دقیقاً هم اندازه باشند.

• شرط های OR به صورت مستقیم قابل پیاده سازی نیستند و نیاز به فرمول ترکیبی دارند.

مقایسه تابع COUNTIF و COUNTIFS

نکات کاربردی برای استفاده از COUNTIF و COUNTIFS:

1) می‌ توانید از علائم مقایسه ای مثل > و < و <> و =* و => و =< در criteria استفاده کنید. نحوه استفاده از این علائم به صورت زیر است:

=COUNTIF(A:A,">=" & B1)

=COUNTIF(A:A,">=" & B1)

2) برای مطابقت متنی جزئی می‌توانید از نماد * یا ? استفاده کنید:

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

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

در این حالت در ستون A هر سلولی که شامل عبارت “Test” هست شمارش می شود. استفاده از علامت * به این معنی است که به اکسل می گویید هر سلولی که داخل آن عبارت “Test” وجود دارد را شمارش کن؛ یعنی مثلاً Testa، aTest، aTestb، abTest c همگی شمارش می شوند.

=COUNTIF(A2:A10, "Te??")

=COUNTIF(A2:A10, "Te??")

در این حالت در ستون A فقط سلول هایی که دارای 4 کاراکتر باشند و با عبارت “Te” شروع شود شمارش می شوند.

=COUNTIF(A2:A10, "T?e?")

=COUNTIF(A2:A10, "T?e?")

در این حالت در ستون A فقط سلول هایی که دارای 4 کاراکتر باشند و حرف اول آن ها “T” و حرف سوم آن ها “e” باشد، شمارش می شوند.

3) برای پیاده سازی شرط OR می‌ توانید چند COUNTIF بنویسید و با هم جمع بزنید:

=COUNTIF(A2:A10, "تهران") + COUNTIF(A2:A10, "شیراز")

=COUNTIF(A2:A10, "تهران") + COUNTIF(A2:A10, "شیراز")

4) استفاده از آدرس سلول به جای شرط ثابت باعث انعطاف پذیری بیشتر فرمول خواهد شد:

=COUNTIF(A2:A100, E1)

=COUNTIF(A2:A100, E1)

5) حتماً دقت کنید که در COUNTIFS محدوده های شرطی هم اندازه با محدوده های دیگر باشند تا فرمول درست کار کند.

جمع بندی

توابع COUNTIF و COUNTIFS دو ابزار بسیار مفید در اکسل هستند که برای شمارش شرطی داده ها به کار می روند.

• تابع COUNTIF برای شرایط ساده تر و تک شرطی مناسب است.

• تابع COUNTIFS برای شرایط پیچیده تر و چند شرطی کاربرد دارد.

استفاده صحیح از این توابع به شما کمک می کند گزارش های دقیق تر و داشبوردهای هوشمندتری طراحی کنید.

امیدواریم این آموزش از سایت اهداف سبز برای شما مفید بوده باشد.

اگر سوال یا نظری درباره آموزش تابع COUNTIF و COUNTIFS در اکسل دارید، خوشحال می شویم در بخش دیدگاه ها با ما به اشتراک بگذارید.