در این مقاله با چگونگی استفاده از توابع COUNTIF و COUNTIFS در اکسل بر اساس منطق توابع OR و AND آشنا خواهید شد. همچنین تعدادی مثال برای انواع مختلف داده از قبیل اعداد، تاریخ، متن، کاراکترهای WILDCARD و سلول های غیر خالی ارائه خواهد شد.

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

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

فیلم آموزشی تابع countifs در اکسل

استفاده از فرمول countifs رو به دلیل طولانی بودن در دو قسمت میگیم، توی قسمت اول کاربرد کلی این تابع رو بررسی میکنیم و در قسمت دوم  ترکیب counifs و conditional ormating رو توضیح میدیم.

در آموزش قبلی در مورد تابع countif یاد گرفتیم که چطور یک بازه رو مشخص کنیم و یک شرط بهش بدیم، توی countifs هر چندتا شرط که بخوایم میتونیم تعیین کنیم.

توی این قسمت راجع به name_definition هم صحبت میکنیم.

توی قسمت قبل یاد گرفتیم چطور از فرمول countifs به تنهایی استفاده کنیم. توی این قسمت با ترکیب countifs و conditional formating افرادی که شرط ما برای اونها برقرار است رو به رنگ دلخواه در میاریم.

تابع COUNTIFS در اکسل – ترکیب و کاربرد

همانطور که تاکنون ذکر شده است، تابع COUNTIFS در اکسل به منظور شمارش سلولها در محدوده های مختلف و بر اساس یک یا چند شرط و معیار متفاوت طراحی شده است.تابع COUNTIFS در ورژن های 2007 به بعد اکسل قابل دسترس است و شما میتوانید مثال های زیر را در هر ورژنی از اکسل مورد استفاده قرار دهید.

ترکیب تابع countifs

ترکیب تابع countifs به شکل زیر است:

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

criteria_range1: محدوده ای را که شرط اول می بایست در مورد آن به کار گرفته شود تعریف میکند. این پارامتر ضروری است.

criteria1: شرط را به صورت عدد، متن، تاریخ، آدرس سلول، عبارت، یا دیگر توابع اکسل بیان میکند. این مورد نیز ضروری است. این شرط نشان میدهد که کدام سلول ها می بایست شمرده شوند و به صورتهای  10, “<=32”, A6, و “شیرینی ها” بیان میشود.

[criteria_range2, criteria2]…: اینها محدوده های اضافی و شرط های مربوط به آنها هستند. این پارامترها اختیاری هستند. شما میتوانید تا 127 محدوده-شرط در فرمول خود مورد استفاده قرار دهید.

در حقیقت، لازم نیست که ترکیب تابع COUNTIFS را حفظ کنید، به محض اینکه شروع به تایپ کردن کنید، اکسل پارامترهای این تابع را به شما نشان خواهد داد و پارامتری که در حال وارد کردن آن هستید به صورت هایلایت نشان داده میشود.

تابع countifs – نکاتی که می بایست به یاد داشته باشید

1. شما میتوانید از تابع COUNTIFS برای یک محدوده و یک شرط و همچنین در چند محدوده همراه با چند شرط استفاده کنید. در صورتی که از این تابع برای چند محدوده و شرط های متعدد استفاده میکنید فقط سلول هایی شمارش میشود که دارای تمام شرطهای مشخص شده باشند.
2. هر محدوده اضافی که در فرمول وارد میکنید می بایست به اندازه محدوده اول ردیف و ستون داشته باشد .
3. در فرمول هم میتوان از محدوده های متوالی و هم محدوده های غیر متوالی استفاده کرد
4. در صورتی که شرط، آدرس یک سلول خالی باشد، تابع آن را یک مقدار صفر در نظر خواهد گرفت.
5. شما میتوانید از کارکترهای wildcard – ستاره(*) و علامت سوال(؟) بعنوان شرط در فرمول خود استفاده کنید.

نحوه استفاده از countifs و countif با شرط های چندگانه در اکسل

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

نحوه شمارش سلول ها با شرط های متعدد (منطق AND)

این سناریو بسیار آسان است زیرا تابع COUNTIFS برای شمارش سلول ها در صورت TRUE بودن چندین شرط طراحی شده است. ما به این مسئله منطق AND میگوئیم زیرا تابع AND نیز به همین صورت کار میکند.

فرمول1: فرمول COUNTIFS همراه با شرط های متعدد

فرض کنید که لیستی از محصولات مختلف شبیه به چیزی که در تصویر زیر آمده دارید، و شما میخواهید آماری از کالاهایی داشته باشید که در انبار ذخیره شده است (مقادیر ستون B که بزرگتر از 0 هستند) ولی هنوز به فروش (مقدار درون ستون C که مساوی صفر) نرسیده است.

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

=COUNTIFS(B2:B7,">0", C2:C7,"=0")

و  نتیجه شمارش شده 2 است: (“گیلاس” و “لیمو”)

فرمول 2. فرمول COUNTIFS با شرط مشابه

زمانی که میخواهید آیتم ها را بر اساس یک شرط شمارش کنید نیز می بایست محدوده ها و شرط را به صورت جداگانه وارد کنید.

برای مثال، فرمول زیر کالاهایی که در هر دو ستون B و C مقدار صفر دارند را شمارش میکند:

=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")

این فرمول 1 را برمیگرداند زیرا فقط “کیوی” در هر دو ستون مقدار صفر دارد.

استفاده از یک فرمول ساده تر همراه با یک criteria_range مثل

=COUNTIFS(B2:C7,"=0")

نتیجه متفاوتی ارائه میکند –  تعداد کل آیتم هایی که در محدوده B2:C7 مقدار صفر دارند(که 4 آیتم است)

نحوه شمارش سلول ها با شرط های متعدد(منطق or)

همانطور که در مثال بالا مشاهده کردید، شمارش سلول هایی که تمام شرط های مشخص شده را دارا باشند بسیار ساده است زیرا تابع  COUNTIFS برای همین کار طراحی شده است.

ولی اگر بخواهید سلول هایی را شمارش کنید که حداقل یکی از شرط های مشخص شده را دارا باشند چه باید کرد؟ (بر اساس منطق تابع or). به طور کلی دو راه برای انجام این کار وجود دارد. استفاده از چندین فرمول COUNTIF یا استفاده از یک فرمول SUM COUNTIF با یک آرایه ثابت .

فرمول 1: ترکیب 2 یا چند فرمول COUNTIFS و COUNTIF

فرض کنید که میخواهید مقادیر “در انتظار” یا “کنسل شده” را در جدول زیر شمارش کنید. برای انجام این کار، میتوانید به سادگی دو فرمول COUNTIF معمولی بنویسید و نتیجه را جمع کنید:

=COUNTIF($C$2:$C$11,"کنسل شده") + COUNTIF($C$2:$C$11,"در انتظار")

در شرایطی که هر فرمول می بایست بیش از یک شرط را بررسی کند، از COUNTIFS به جای COUNTIF استفاده کنید. برای مثال، برای شمارش سفارش های “در انتظار” و  “کنسل شده”  برای آُیتم سیب، از فرمول زیر استفاده کنید:

=COUNTIFS($A$2:$A$11,"سیب",$C$2:$C$11,کنسل شده"")+COUNTIFS($A$2:$A$11,"سیب", $C$2:$C$11,"در انتظار")

فرمول 2: بدست آوردن مجموع COUNTIFS ها با استفاده از یک آرایه ثابت

در شرایطی که می بایست تعداد زیادی از شرط ها را بررسی کنید، استفاده از رویکرد بالا خوب نیست؛ زیرا باعث میشود فرمول شما بسیار بزرگ شود. برای انجام این محاسبات با استفاده از یک فرمول خلاصه تر، تمام شرط های خود را در یک آرایه ثابت قرار دهید، و سپس آن آرایه را در قسمت criteria  فرمول COUNTIFS خود قرار دهید. برای بدست آوردن مجموع کل، COUNTIFS را در درون تابع sum قرار دهید:

=SUM(COUNTIFS(range,{"criteria1","criteria2","criteria3",…}))

در جدول داده های ما، برای شمارش سفارش هایی که در وضعیت” کنسل شده” یا  “در انتظار”  یا “در حمل و نقل ” قرار دارند، میتوان از فرمول زیر استفاده کرد:

=SUM(COUNTIFS($C$2:$C$11, {"در حمل و نقل", "در انتظار", "کنسل شده"}))

با همین شیوه، میتوانید محدوده های خود را بر اساس دو یا چند جفت criteria_range / criteria شمارش کنید. برای مثال، برای بدست آرودن تعداد سفارشات “سیب” که در وضعیت ” کنسل شده” یا  “در انتظار”  یا “در انتظار” قرار دارند، میتوان از فرمول زیر استفاده کرد:

=SUM(COUNTIFS($A$2:$A$11,"سیب",$C$2:$C$11,{"در حمل و نقل","در انتظار","کنسل شده"}))

نحوه شمارش اعداد بین دو عدد خاص

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

فرمول1: COUNTIFS برای شمارش سلولهای بین دو عدد

برای پیدا کردن تعداد اعداد بین 5 و 10(شامل 5 و 10 نمی شود) که در سلول های C2 تاC10 قرار دارند، از این فرمول استفاده کنید:

=COUNTIFS(B2:B10,">5", B2:B10,"<10")

برای اینکه خود 5 و 10 نیز شامل محدوده شود،  از علامت های بزرگتر یا مساوی و کوچکتر یا مساوی استفاده کنید.

=COUNTIFS(B2:B10,">=5", B2:B10,"<=10")

فرمول 2: فرمول های COUNTIFS برای شمارش اعداد بین x و y

همین نتایج را میتوان از طریق کم کردن یک تابع COUNTIF  از دیگری بدست آورد. تابع اول تمام مقدارهای بزرگتر از حد پایین را بدست می آورد (عدد 5 در این مثال) و تابع دوم اعداد بزرگتر از حد بالا را برمیگرداند. گرفتن اختلاف این دو مقدار عدد دلخواه را به ما نشان خواهد داد:

=COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10")

تعداد اعداد بزرگتر از 5 و کوچکتر از 10 را در محدوده C2:C10 نشان میدهد. این فرمول همان نتیجه ای را برمیگرداند که در تصویر بالا نشان داده شد.

=COUNTIF(C2:C10, ">=5")-COUNTIF(C2:C10, ">10")

این فرمول تعداد اعداد بین 5 تا 10 را در محدوده C2:C10 را نشان میدهد با این تفاوت که شامل خود اعداد 5 و 10 نیز میشود.

نحوه استفاده از آدرس سلول در فرمول های COUNTIFS

زمانی که از عملگرهای منطقی مثل، “>”, “<“, “<=” یا  “>=” همراه با آدرس سلول در فرمول COUNTIFS استفاده میکنید به یاد داشته باشید که عملگر را در درون “” قرار دهید و قبل از آدرس سلول یک & قرار دهید تا یک رشته متنی ساخته شود.

در داده های نمونه زیر، سفارشات بالای 200هزار ریال را برای سیب بدست آورید. اگر criteria_range1 در سلول های A2:A11 و criteria_range2 در سلول های B2:B11 قرار داشته باشد، میتوانید از فرمول زیر برای انجام این کار استفاده کنید:

=COUNTIFS($A$2:$A$11, "Apples", $B$2:$B$11, ">200")

یا میتوانید مقدار شرط خود را در سلول های مشخص قرار دهید. برای مثال f1 و f2، و این آدرس ها را در درون سلول خود قرار دهید:

=COUNTIFS($A$2:$A$11, $F$1, $B$2:$B$11, ">"&$F$2)

لطفاً به یاد داشته باشید که از آدرس مطلق سلول، هم در پارامتر criteria و هم در پارامتر criteria_range استفاده کنید. این کار باعث خواهد شد اگر فرمول به سلول دیگری کپی شد آسیب نبیند.

نحوه استفاده از COUNTIFS همراه با کاراکتر های wildcard

در فرمول های COUNTIFS، میتوانید از کاراکتر های wildcard زیر استفاده کنید.

• علامت سؤال(؟): برای شمارش سلول هایی که با یک کاراکتر خاص شروع میشود یا پایان می یابد.
• ستاره(*): با استفاده از این علامت سلول هایی را شمارش میکنند که شامل یک کلمه یا کاراکتر(ها) به عنوان محتوای سلول میشود

نکته: در صورتی که میخواهید سلول هایی را شمارش کنید که شامل یک علامت سؤال یا ستاره واقعی هستند، یک علامت~ قبل از علامت سؤال یا ستاره قرار دهید.

حال ببینیم که چگونه میتوان از یک کاراکتر  wildcard در فرمول های COUNTIFS در زندگی واقعی استفاده کرد. فرض کنید لیستی از پروژه ها را در ستون A دارید و میخواهید بدانید که چه تعداد پروژه به افراد تخصیص یافته است به این معنی یک نام در ستون B دارید و از آنجایی که با نحوه استفاده از تابع COUNTIFS با شرط های چندگانه آشنا شده ایم، یک شرط دیگر به تابع اضافه کنید و آن اینکه، تاریخ پایان در ستونD را نیز در نظر بگیرید.

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

=COUNTIFS(B2:B10,"*",D2:D10,"<>"&""))

توجه داشته باشید که، در شرط دوم نمیتوانید از علامت & استفاده کنید زیرا ستون D حاوی تاریخ است و نه متن و به همین دلیل از شرطی استفاده میکنیم که سلول های غیر خالی را پیدا کند: “<>”&””

توابع COUNTIFS و COUNTIf با شرط های چندگانه برای تاریخ

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

مثال1: شمارش تاریخ در یک محدوده تاریخ مشخص

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

برای مثال، فرمول های  زیر تعداد تاریخ های موجود در سلول های B2 تا B10 که در محدوده 1-Jun-2017 تا 7-Jun-2017 قرار میگیرد را شمارش میکند:

=COUNTIFS(C2:C9, ">=6/1/2017", C2:C9, "<=6/7/2017")

=COUNTIF(C2:C9, ">=6/1/2017") - COUNTIF(C2:C9, ">6/7/2017")

مثال2: شمارش تاریخ با شرط های چندگانه

با همین شیوه، میتوانید با استفاده از یک فرمول COUNTIFS تعداد تاریخ هایی را که دارای 2 یا چند شرط هستند شمارش کنید. برای مثال، فرمول زیر تعداد کالاهایی را که  بعد از  تاریخ 20 می خریداری شده است و بعد از 1 جون تحویل داده شده است را شمارش میکند:

=COUNTIFS(B2:B9,">5/1/2017",C2:C9,">6/7/2017")

مثال3: شمارش تاریخ با شرط های چندگانه بر اساس تاریخ فعلی

شما میتوانید با استفاده از ترکیب تابع TODAY() و تابع COUNTIF  تاریخ ها را بر اساس تاریخ فعلی شمارش کنید:

برای مثال فرمول COUNTIFS زیر با دو محدوده و دو شرط ، به شما میگوید که چه تعداد کالا خریداری شده است ولی هنوز تحویل داده نشده است:

=COUNTIFS(B2:B9, "<"&TODAY(), C2:C9, ">"&TODAY())

این فرمول امکان استفاده در شرایط بسیار متنوعی را دارد. برای مثال، شما میتوانید با استفاده از این فرمول مشخص کنید که چه تعداد کالا در مدت زمانی بیش از دو هفته پیش به فروش رفته است ولی هنوز تحویل داده نشده است:

=COUNTIFS(B2:B9, "<="&TODAY()-7, C2:C9, ">"&TODAY())

امیدواریم که مثال های ارائه شده در درک این توابع به شما کمک کرده باشد. و از اینکه از سایت ما بازدید کردید تشکر میکنیم.