توابع COUNTIF و COUNTIFS و تفاوت آنها

روش اول :

 عملگر = را تایپ و تابع Countif را می‌نویسیم .

پرانتز را باز می‌کنیم و با فشردن همزمان دو دکمه  CTRL+A   پنجره آرگومان های تابع Countif باز می‌شود

Range:

مقادیر عددی ستون نمره از خانه C2 تا C9 را انتخاب می کنیم.

Criteria:

شرط تابع نمرات مساوی 12 می باشد پس “12=”  را در قسمت شرط وارد می‌کنیم .

بنابراین فرمول بصورت زیر تکمیل می شود .

COUNTIF(C2:C9;”=12″)=

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

COUNTIF(C2:C9;”=”&12)=

COUNTIF(C2:C9;12)=

در فرمول اول فقط عملگر مقایسه‌ای را دربین ” ” قرار می‌دهیم وعدد 12 را با & به رشته “=” وصل می‌کنیم .

در فرمول دوم، می‌توانیم وقتیکه از عملگر = استفاده می‌کنیم می‌توانیم “=” ننوشته و  فقط عدد 12 را بنویسیم

و وقتی از اعداد استفاده می‌کنیم نیازی به ” ” نمی‌باشد .

نکته: وقتی از عملگر مقایسه‌ای مساوی (=) استفاده می‌کنیم می توانیم “=” را حذف و فقط مقدار جلوی مساوی را در شرط وارد کنیم .

روش دوم :

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

از تب Formulas گزینه Insert Function را انتخاب می‌کنیم .

در قسمت or select  a category  اگر گزینه Most Recently used را انتخاب کنیم.

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

می توانیم تابع countif را از این لیست انتخاب کنیم .

و لیست تمام توابع اکسل  به ترتیب حروف الفبا بصورت صعودی نمایش داده می شوند .

تابع countif را جستجو می کنیم تا آن را بیابیم .

یا اینکه راحتر است که  در پنجره Insert Function در قسمت بالای پنجره Search For a Function نام تابع Countif را وارد کنیم و روی go  کلیک کنیم

تابع Countif در لیست توابع اکسل نشان داده می شود آن را انتخاب و ok  می کنیم .

وقتی پنجره مربوط به آرگومان ها باز شد در قسمت Range و Criteria مقادیر را وارد و ok  می کنیم .

مثال 3:

می خواهیم مقادیر مخالف 16 را تعدادشان را محاسبه کنیم.

 عملگر = را تایپ و عبارت Countif را می‌نویسیم .

پرانتز را باز می‌کنیم و با فشردن همزمان دو دکمه  CTRL+A پنجره آرگومان‌های تابع Countif باز می‌شود.

Range:

مقادیر عددی ستون نمره از خانه C2 تا C9 را انتخاب می‌کنیم.

Criteria:

شرط تابع نمرات مخالف 16 می‌باشد پس “16<>”  را در قسمت شرط وارد می‌کنیم .

بنابراین فرمول بصورت زیر تکمیل می‌شود .

COUNTIF(C2:C9;”<>16″)=

فرمول فوق بشکل زیر با کمک & هم صحیح می‌باشد.

COUNTIF(C2:C9;”<>”&16)=

مثال 4:

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

محاسبه تعداد با تابع countif

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

ازتب Formulas  گزینه Insert function را انتخاب می‌کنیم.

و  تابع Countif  را انتخاب می‌کنیم.

تابع به  لیست اضافه می شود آن را انتخاب می کنیم و  OK  می‌کنیم.

Range:

مقادیر متنی ستون نام از B2  تا B9  را انتخاب می‌کنیم.

Criteria:

شرط تابع عبارت متنی است پس بین “محمد”قرار می‌گیرد.

فرمول بصورت زیر تکمیل می‌شود.

(“محمد”;B2:B9)countif=

نکته: برای نوشتن عبارات متنی در قسمت شرط حتما باید عبارت در بین ” ” قرار گیرد.

نکته: برای نوشتن مقادیر عددی مثل 16 نیازی به استفاده از ” ” نمی‌باشد.

مثال 5:

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

سلول مناسب را انتخاب می‌کنیم عملگر مساوی و تابع Countif را می‌نویسیم.

پرانتز را باز می‌کنیم با فشردن CTRL+A پنجره آرگومان‌ها باز می‌شود.

Range:

مقادیر متنی ستون نام از B2  تا B9  را انتخاب می‌کنیم.

Criteria:

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

یعنی قبل و بعد از علی ممکنه حروف دیگر باشد.

پس از عملگر * قبل و بعد از نام علی استفاده می‌کنیم و می‌نویسیم  “*علی*”

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

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

تابع Countifs چیست و در کجا استفاده می‌شود

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

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

حل مثال‌های مختلف و کاربردی از تابع Countifs

برای مثال می‌خواهیم تعداد دانش آموزانی که شماره کلاس آنها 101 است و نام آنها چهار حرفی می‌باشد و نمره بزرگتر مساوی 10 کسب کرده اند رابدست آوریم.

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

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

فرمول تابع بصورت زیر می‌باشد:

COUNTIFS(Criteria_range1;Criteria1;[Criteria_range2;Criteria2];[…..])=

( ….;شرط دوم ;محدوده شرط دوم ;شرط اول;محدوده شرط اول)countifs=

• بازه شرط اول و شرط اول اجباری می باشد و بقیه بازه‌های شرط و شرط‌ها اختیاری است.
• در این تابع می‌توانیم 127 محدوده شرط و شرط داشته باشیم که در فرمول بالا فقط دو محدوده شرط و شرط را نوشتیم .

روش کار با تابع:

در خانه D11 کلیک می‌کنیم عملگر مساوی را تایپ می‌کنیم.

تابع Countifsرا نوشته و پرانتز را باز می‌کنیم .

و همزمان دو دکمه CTRL+A را می‌فشاریم تا پنجره آرگومان‌های تابع باز گردد.

Criteria_range1:

در این مثال محدوده شرط اول، کل ستون نام کلاس از خانه B2 تا B9 می‌باشد. که با درگ انتخاب می‌کنیم .

Criteria1:

شرط محدوده اول کلاس 101 می‌باشد که چون متنی است بین “کلاس 101 ” می‌نویسیم .

Criteria_range2:

محدوده شرط دوم کل ستون نام دانش آموزان از خانه C2 تا C9 می‌باشد.

Criteria2:

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

قبلا توضیح داده شد که هر یک  کاراکتر ؟ جایگزین یک حرف خواهد شد پس وقتی بدنبال نام های چهار حرفی هستیم در قسمت شرط می نویسیم “????”

Criteria_range3:

محدوده شرط سوم کل ستون نمرات از خانه D2 تا D9  است .که با درگ انتخاب می‌کنیم.

Criteria3:

شرط سوم نمرات بزرگتر مساوی 10 است پس در این قسمت “10=<” را می‌نویسیم.

در پایان فرمول مثال بالا بصورت زیر تکمیل می‌شود.

(“10=<“;C2:C9;”????”;D2:D9;”کلاس101″;B2:B9)COUNTIFS