تابع COUNTIF در اکسل

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

فرمول COUNTIF در تمام ورژن های اکسل مشابه هستند و شما میتوانید مثال هایی را که در این مقاله ارائه میشود در تمام ورژن های 2007 به بعد اکسل مورد استفاده قرار دهید.

• تابع COUNTIF در اکسل – ترکیب و کاربرد
• مثال هایی در مورد نحوه استفاده از COUNTIF در اکسل
• فرمول های COUNTIF در اکسل برای متن و عدد (تطابق دقیق)
• فرمول های COUNTIF در اکسل برای متن و عدد (تطابق جزیی)
• شمارش در صورت خالی بودن یا غیر خالی بودن
• شمارش در صورت بزرگتر بودن، کوچکتر بودن یا مساوی بودن
• فرمول های COUNTIF در اکسل برای تاریخ
• COUNTIF در اکسل با شرط های چندگانه
• شمارش مقادیر چندگانه و منحصر به فرد

اگر با تابع if اکسل آشنا نیستید مطالعه این مطالب را به شما پیشنهاد می کنیم:

تابع if در اکسل | مثال ها و کاربردها

تابع if با شرط های and و  or

if تو در تو در اکسل به همراه مثال های کاربرد

فرمول های آرایه ای در اکسل به همراه مثال های کاربردی

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

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

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

ترکب تابع COUNTIF بسیار ساده است:

=COUNTIF(range,criteria)

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

range(محدوده)  – یک یا چندین سلول را برای شمارش تعریف میکند. در اینجا هم مثل سایر فرمول ها در اکسل محدوده مورد نظر خود را در فرمول قرار میدهیم برای مثال A1:A10

criteria(شرط) – شرطی را که به سلول میگوید کدام سلول های شمرده شود را مشخص میکند. این شرط میتواند عدد، رشته متن، آدرس سلول یا عبارت باشد. برای مثال شما میتوانید از شروطی مثل “10”، A2،”>=10″ یا “متن” استفاده کنید.

چیزی که در تصویر زیر میبینید لیست بهترین تنیس بازان در 14 سال گذشته است. فرمول زیر

 =COUNTIF(C2:C15,"Roger federer ")

تعداد دفعه ای را که نام Roger Federer در لیست آمده است را شمارش میکند.

نکته: شرط نسبت به حروف بزرگ و کوچک حساس نیست. به این معنی که اگر “ROGER FEDERER” را در فرمول تایپ کنید هیچ تغییری در نتیجه رخ نخواهد داد.

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

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

فرمول COUNTIF در اکسل برای متن و عدد (مطابقت جزئی)

در حقیقت، چند لحظه پیش در مورد تابع COUNTIF که سلول هایی را که دقیقاً حاوی یک کلمه خاص بودند شمارش میکرد بحث کردیم. اجازه دهید یادآوری کنم که فرمول =COUNTIF(C2:C15,”Roger Federer”) سلول هایی را شمارش میکرد که دقیقاً شامل کلمه Roger Federer می شدند. بنابراین شما پارامترهای زیر را وارد فرمول کردید:

• یک محدوده بعنوان پارامتر اول
• یک کاما به عنوان جداکننده
• یک یا چند کلمه بین “” بعنوان شرط

میتوانید به جای وارد کردن دستی اطلاعات در فرمول از آدرس سلول استفاده کنید و نتایج قطعی تری را بدست آورید.

=COUNTIF(C1:C9,C7)

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

=COUNTIF(D2:D9,5)

فرمول های تابع COUNTIF در اکسل همراه با کاراکتر های WILDCARD

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

فرض کنید، لیستی از وظایف مربوط به هر شخص را در اختیار دارید و میخواهید وظایف اختصاص داده شده به DANNY BROWN را شمارش کنید. از آنجایی که DANNY را میتوان به اشکال مختلف نوشت ما از BROWN بعنوان معیار جستجو استفاده کردیم.

=COUNTIF(D2:D10,"*BROWN*)

از علامت ستاره برای پیدا کردن سلول هایی که با هر ترتیبی شامل این واژه بشوند استفاده شد. همانطور که در مثال بالا نشان داده شد، در صورتی که می بایست کلمات مطابق یک کاراکتر را پیدا کنید از علامت سؤال (?) به شکلی که در زیر توضیح داده شده است استفاده کنید.

نکته: همچنین استفاده از کاراکترهای WILDCARD به کمک اپراتور & امکان پذیر است. برای مثال به جای وارد کردن “*Brown*” به طور مستقیم در فرمول، میتوانید آن را در هر سلولی تایپ کنید و بگویید F1؛ و از فرمول زیر برای شمارش سلول هایی که شامل “Brown” است استفاده کنید.

=COUNTIF(B2:B10, "*"&F1&"*")

شمارش سلول های شروع شونده و پایان یابنده با کاراکترهای خاص

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

=COUNTIF(C2:C10,"امی*")

سلول هایی را شمارش میکند که با MR شروع میشود.

=COUNTIF(C2:C10," *یی")

سلول هایی را شمارش میکند که با ED پایان می یابد.

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

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

=COUNTIF(D2:D9,"??یی")

تعداد سلول هایی را شمارش میکند که با « یی» پایان می یابد و فقط دارای 4 کاراکتر در محدوده مشخص شده هستند. ( شامل فاصله نیز می شود)

=COUNTIF(D2:D9,"امی?????? ")

تعداد سلول هایی را شمارش میکند که با « امی» شروع میشود و فقط دارای 8 کاراکتر در محدوده مشخص شده هستند. (شامل فاصله نیز می شود)

نکته: در صورتی که به دنبال تعداد سلول هایی هستید که حاوی علامت ؟ یا * باشند قبل از ان علائم از علامت~ در فرمول خود استفاده کنید. برای مثال

=COUNTIF(D2:D9,"*~?*")

سلول هایی را که در محدوده D2 تا D9 دارای علامت سؤال باشند را شمارش خواهد کرد.

تابع COUNTIF در اکسل برای شمارش سلول های خالی و غیر خالی

مثال های زیر نحوه استفاده از فرمول های COUNTIF برای شمارش سلول های خالی و غیر خالی در یک محدوده را مشخص میکند:

تابع COUNTIF در اکسل برای سلول های غیر خالی

ممکن است در دیگر مقالات اکسل با فرمول هایی به سبک فرمول زیر برای شمارش سلول های غیر خالی مواجه شده باشید:

=COUNTIF(RANGE,"*")

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

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

=COUNTIF(RANGE,"<>"&"")

این فرمول به درستی با تمام انواع داده کار میکند –  متن، تاریخ، اعداد – این موضوع در تصویر زیر قابل مشاهده است:

تابع COUNTIF در اکسل برای سلول های خالی

در صورتی که به دنبال شمارش سلول های خالی هستید، می بایست از شیوه ای مشابه شیوه قبل استفاده کنید – از فرمولی با کاراکترهای WILDCARD برای مقادیر متنی یا دیگر مقادیر استفاده کنید تا تمام سلول های خالی را شمارش کند.

فرمول برای شمارش سلول هایی که دارای هیچ متنی نیستند:

=COUNTIF(RANGE,"<>"&"*")

که به فارسی روان، شرط موجود در این فرمول، سلول هایی را که شامل * نمیشود را شمارش میکند. به این معنی که این فرمول تمام سلول هایی را که شامل هیچ متنی نمی شود را شمارش میکند.

اما فرمول زیر سلول هایی را شمارش میکند که هیچ مقداری ندارد، یعنی مطلقا خالی هستند و نه تنها متن بلکه هیچ نوع داده ای درون آن وجود ندارد:

=COUNTIF(RANGE,"")

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

=COUNTIF(B2:B11,"")

تمام سلول های خالی در محدوده B2 تا B11 را مشخص میکند.

نکته: در نظر داشته باشید که اکسل فرمولی را برای شمارش سلول های خالی ارائه کرده است – =COUNTBLANK – برای مثال فرمول زیر دقیقا همان نتایجی را بدست خواهد داد که فرمول موجود در تصویر بالا ارائه کرده بود.

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

=COUNTBLANK(B2:B11)

فرمول زیر سلول های غیر خالی را شمارش میکند.

=ROWS(B2:B11)*COLUMNS(B2:B11)-COUNTBLANK(B2:B11)

همچنین به یاد داشته باشید که

=COUNTIF(RANGE,"")

و

=COUNTBLANK(RANGE)

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

=ROWS(C2:С11)*COLUMNS(C2:С11)-COUNTIF(C2:С11,"<>"&"").

شمارش در صورت بزرگتر بودن، کوچکتر بودن یا مساوی بودن

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

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

نکته: در صورتی که از آدرس سلول استفاده میکنید می بایست عملگر را در درون “” قرار دهید. برای مثال به منظور شمارش کردن سلول های موجود در محدوده D2:D9 که دارای مقدار بیش از مقدار موجود در سلول D3 باشد از فرمول

=COUNTIF(D2:D9,">"&D3)

استفاده کنید:

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

=COUNTIF(D2:D9,"*>5*")

تعداد سلول های موجود در محدوده D2:D9 را که حاوی

“تحویل داده شده>5روز”

یا

“>5 مورد موجود”

هستند را شمارش میکند.

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

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

تمام فرمول های در بالا توضیح داده شده هم برای اعداد و هم برای تاریخ قابل استفاده است . در جدول زیر چند مثال در این زمینه ارائه شده است:

در کنار این کاربرد های عمومی، میتوانید از ترکیب تابع COUNTIF با دیگر توابع مربوط به زمان و تاریخ مثل TODAY() استفاده کنید تا سلول های یک محدوده را بر اساس تاریخ امروز شمارش کنید.

در زیر نمونه ای از استفاده از این فرمول در مسائل واقعی آمده است( تاریخ امروز 25-JUN-2014 است)

عکس

توصیه میکنیم این مطالب را هم مطالعه کنید:

تابع COUNTIF در اکسل همراه با شرط های چندگانه

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

مثال 1: تابع COUNTIF با دو شرط برای اعداد

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

=COUNTIF(B2:B9,">5″)-COUNTIF(B2:B9,">=15″)

مثال2: تابع COUNTIF با دو شرط برای متن

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

=COUNTIF(B2:B13,"آب پرتقال")+COUNTIF(B2:B13,"*آبمیوه ")

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

=COUNTIF(B2:B13,"آب پرتقال") + COUNTIF(B2:B13,"*آب میوه") + COUNTIF(B2:B13,"بستنی")

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

یکی دیگر از کاربردهای تابع COUNTIF پیدا کردن مقادیر تکراری در یک ستون، یک ردیف یا بین دو ستون است.

مثال1: پیدا کردن و شمردن مقادیر تکراری در یک ستون

برای مثال، این فرمول

 =COUNTIF(A2:A10,A2)>1

تمام مقادیر تکراری را در محدوده A2:A10 شناسایی میکند در حالی که

=COUNTIF(B2:B10,TRUE)

به شما میگوید چند مقدار تکراری در این محدوده وجود دارد.

مثال 2: شمارش مقادیر تکراری بین دو ستون

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

=SUMPRODUCT((COUNTIF(A2:A1000,B2:B1000)>0)*(B2:B1000<>""))

همچنین میتوان پا را فراتر گذاشت و تعداد اسم های منحصر به فرد موجود در ستون C را شمارش کرد (تعداد اسم هایی که در ستون B وجود ندارد)

=SUMPRODUCT((COUNTIF(A2:A1000,B2:B1000)=0)*(B2:B1000<>""))

مثال 3: شمارش مقادیر تکراری و غیر تکراری در یک ردیف

در صورتی که میخواهید مقادیر تکراری یا غیر تکراری را در یک ردیف شمارش کنید، از یکی از فرمول های زیر استفاده کنید:

شمارش مقادیر تکراری در یک ردیف:

=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)>1)*(A2:I2<>""))

شمارش مقادیر غیر تکراری در یک ردیف:

=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)=1)*(A2:I2<>""))

سوالات و مشکلات رایج در مورد تابع COUNTIF در اکسل

امیدواریم که مثال های ارائه شده درک روشنی از تابع COUNTIF برای شما فراهم کرده باشد. در صورتی که در کار با تابع  COUNTIF یا پیاده سازی یکی از مثال های بالا با مشکلی مواجه شده اید، به 5 مشکل رایج در استفاده از تابع COUNTIF که در زیر آمده است مراجعه کنید. فرصت خوبی است که با استفاده از این 5 مورد مشکل خود را رفع کنید و نکته های جدید به آموخته های خود اضافه کنید.

1. تابع COUNTIF برای محدوده های غیر متوالی

سؤال: چگونه میتوانم از تابع COUNTIF اکسل برای محدوده های غیر متوالی استفاده کنم؟

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

اشتباه:

=COUNTIF(A2,B3,C4,">0″)

صحیح:

=COUNTIF(A2,">0″) + COUNTIF(B3,">0″) + COUNTIF(C4,">0″)

یک راه حل دیگر استفاده از تابع INDIRECT به منظور ساخت یک آرایه است. برای مثال، هر دو فرمول های زیر نتیجه یکسانی را که در تصویر مشاهده میکنید را برمیگرداند:

=SUM(COUNTIF(INDIRECT({"B2:B8″,"D2:C8″}),"=0"))

=COUNTIF($B2:$B8,0) + COUNTIF($D2:$D8,0)

2. استفاده از علامت های & و نقل قول در تابع countif در اکسل

سؤال: چه زمانی نیاز به استفاده از علامت & در تابع COUNTIF می باشد؟

پاسخ: این قسمت احتمالاً محرمانه ترین قسمت است. به شخصه فکر میکنم که ترکیب تابع COUNTIF بسیار گیج کننده است ولی اگر به خوبی به آن توجه کنید منطق پشت آن را درخواهید یافت. علامت های & و “” برای ساختن رشته های متنی به کار گرفته میشود. بنابراین شما می بایست بر اساس قوانین زیر رفتار کنید:

• در صورتی که از عدد یا آدرس در شرط های مطابقت دقیق استفاده میکنید، شما به هیچ کدام از این دو علامت نیاز نخواهید داشت. برای مثال:
• =COUNTIF(A1:A10,10)
• یا
• =COUNTIF(A1:A10,C1)
• در صورتی که شرط شما شامل کاراکتر های WILDCARD، متن، یا عملگرهای مقایسه ای به همراه عدد میشود، می بایست آنها را در درون “” قرار دهید. برای مثال

 =COUNTIF(A2:A10,"لیمو")

 یا

=COUNTIF(A2:A10,"*")

یا

=COUNTIF(A2:A10,">5″)

• در صورتی که از عبارتی به همراه آدرس سلول یا دیگر توابع اکسل استفاده می کنید، می بایست از “” برای شروع یک رشته متنی استفاده کنید و برای متصل کردن یا پایان دادن آن رشته نیز می بایست از & استفاده کنید. برای مثال،
• =COUNTIF(A2:A10,”>”&D2)

یا

=COUNTIF(A2:A10,"<="&TODAY())

اگر شک دارید که آیا یک & نیاز است یا خیر، از هر دو شیوه استفاده کنید.در بسیاری از موارد استفاده از & مفید است. برای مثال فرمول

=COUNTIF(C2:C8,"<=5″)

و

=COUNTIF(C2:C8,"<="&5)

هر دو به خوبی کار میکنند.

3. فرمول COUNTIF برای سلول های فرمت شده (کد های رنگ)

سؤال: چگونه میتوانم سلول ها را بر اساس فونت یا فضای رنگ شده شمارش کنم؟

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

4. خطای #NAME! در تابع COUNTIF

سؤال: تابع COUNTIF من خطای #NAME را برمیگرداند. چگونه میتوانم آن را برطرف کنم؟

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

5. کار نکردن تابع COUNTIF

سؤال: تابع COUNTIF من کار نمیکند. کجای کار اشتباه است؟

پاسخ: در صورتی که فرمولی نوشته اید که به نظر صحیح است ولی به خوبی کار نمیکند یا نتایج غلطی را ارائه میکند. با بررسی مسائل ساده نظیر محدوده، شرط ها، آدرس سلول، استفاده از علامت & و “” شروع کنید.

مراقب استفاده از فاصله در فرمول COUNTIF باشید. موقع نوشتن یکی از مثال های این مقاله بسیار عصبانی شده بودم زیرا فرمول به نظر کاملاً صحیح بود ولی به درستی کار نمیکرد. بعد از مدتی دریافتم که مشکل وجود یک فاصله در فرمول است. برا مثال به این فرمول توجه کنید. =COUNTIF(B2:B13,” LEMONADE”) درنگاه اول هیچ چیز اشتباهی وجود ندارد. ولی درواقع یک فاصله اضافی بعد از علامت ” وجود دارد. مایکروسافت اکسل تصور میکند شما به دنبال یافتن کلمه LEMONADE هستید که با یک فاصله نوشته شده باشد.

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