توابع پایه اکسل - تابع COUNTIF 

تابع COUNTIF تعداد سلول‌هایی که با معیار و شرط واحدی مطابقت دارند را بر می‌گرداند. COUNTIF می‌تواند برای شمارش سلول‌هایی که حاوی تاریخ، اعداد و متن هستند استفاده شود. شرط‌ها می‌تواند شامل عملگرهای منطقی (>،<،<>،=) و نویسه‌های جایگزین (*،?) باشد.

     از کجا شروع کنم        

هدف تابع

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

خروجی تابع

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

ترکیب تابع

= COUNTIF ( range , criteria ) 

ورودی تابع (مولفه‌ها‌)

range – محدوده سلول‌هایی که باید شمارش شوند.
criteria – معیارهایی که کنترل می‌کنند کدام سلول‌ها باید شمارش شوند.

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

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

ویژگی‌های کلیدی

• تعداد سلول‌هایی که یک شرط واحد را برآورده می‌کنند، می‌شمارد (برای چندین شرط از COUNTIFS استفاده کنید) 
• با تاریخ، متن و اعداد کار می‌کند.
• از عملگرهای مقایسه‌ای (>، <، <>، =) و نویسه‌های جایگزین (*، ?) پشتیبانی می‌کند.
• در همه نسخه‌های اکسل موجود است 

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

مثال پایه

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

= COUNTIF ( range , criteria )

معیار ارائه شده در مولفه criteria به سلول‌های محدوده مشخص شده در مولفه range اعمال می‌شود. وقتی مقادیر سلول‌ها با معیار مشخص شده مطابقت داشته باشد ، به شمارش اضافه می‌شوند. 

در برگه زیر، مقدار فروش هر استان را داریم. از COUNTIF برای انجام سه محاسبه زیر استفاده می‌کنیم:

(1) شمارش تمام فروش‌های ابراهیم محمدی ،(2) شمارش تمام فروش‌ها در آذربایجان شرقی و (3) شمارش کل فروش‌های بالای 83 واحد.

فرمول‌های درج شده در سلول‌های محدوده G3:G5 به این شکل هستند:

= COUNTIF ( B3:B13 , "ابراهیم محمدی")   //   تعداد فروش‌های ابراهیم محمدی
= COUNTIF ( C3:C13 , "آذربایجان شرقی)   //   تعداد فروش‌ها در آذربایجان شرقی
= COUNTIF ( D3:D13 , ">83" )   //   تعداد فروش‌های بالای 83 واحد

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

• علامت تساوی (=) برای معیارهای "مساوی است با" لازم نیست (یعنی از "ابراهیم محمدی" استفاده کنید نه "=ابراهیم محمدی").
• COUNTIF به حروف کوچک و بزرگ لاتین حساس نیست، بنابراین "jim" و "Jim" نتایج یکسانی را بر می‌گردانند.
• اعداد هنگام استفاده با عملگرها باید داخل گیومه ("") قرار گیرند (مثلاً ">83").

توجه داشته باشید که شکل مولفه دوم تابع COUNTIF تا حدودی غیرمعمول است. به جای اینکه به سادگی مقدار >83 را به عنوان معیار وارد کنید، باید آن را داخل علامت نقل قول وارد کنید( ">83" ). اگر مقادیر مورد نیاز را در نقل قول قرار ندهید، اکسل اجازه ورود فرمول را نمی‌دهد. 

اعمال معیارها

تابع COUNTIF از عملگرهای منطقی (>،<،<>،=) و نویسه‌های جایگزین (*،?) برای ارزیابی پشتیبانی می‌کند. بخش چالش‌ برانگیز استفاده از تابع COUNTIF شکل ترکیب مورد نیاز برای اعمال معیارها است. دلیل این امر این است که COUNTIF جز هشت تابعی است که بررسی شروط را با دو مولفه range و criteria انجام می‌دهند. به دلیل این طراحی ، عملگرها باید داخل نقل قول‌های دوتایی ("") قرار گیرند. جدول زیر نمونه‌هایی از شکل ترکیب معیارهای رایج را نشان می‌دهد:

توجه داشته باشید که دو مثال آخر شامل نویسه الحاق (&) هستند. هر زمان که از مقدار سلول دیگر یا نتیجه فرمولی با عملگرهای منطقی مانند "<" استفاده می‌کنید، باید آن را پیوند دهید. دلیل این امر آن است که اکسل قبل از اینکه بتواند از مقدار یک عملگر استفاده کند، ابتدا باید آدرس‌های سلول و فرمول‌ها را ارزیابی کند.

معیارهای موجود در سلول دیگر

روش مناسب برای استفاده از COUNTIF این است که معیارها را در سلول دیگری قرار دهید و سپس در فرمول خود به آن سلول ارجاع دهید. این کار تغییر معیارها را در صورت نیاز بدون ویرایش فرمول اصلی آسان می‌کند. به عنوان مثال ، می‌توانید سلول‌های محدوده‌ای را که مقادیر آنها برابر با مقدار سلول A1 هستند، شمارش کنید:

= COUNTIF ( range , A1 )

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

= COUNTIF ( range , ">" & A1 )

توجه داشته باشید که عملگر "<" را با نویسه الحاق (&) به سلول A1 متصل می‌کنیم. در برگه زیر ، تابع COUNTIF طوری پیکربندی شده است که شمارش تمام فروش‌های بالاتر مقدار موجود در G2 را برگرداند. توجه داشته باشید که عملگر (<) که متن است، باید داخل علامت نقل قول قرار گیرد. فرمول سلول G3 به صورت زیر وارد شده است:

= COUNTIF ( D3:D13 ,  ">" & G2 )   //  است  G2شمارش سلول‌هایی که مقدارشان بزرگتر از مقدار سلول

آدرس‌های سلول را داخل علامت نقل قول به صورت "A1" قرار ندهید. انجام این کار آنها را به متن تبدیل می‌کند.

مساوی نیست با

برای بیان معیارهای «مساوی نیست با»، از عملگر « <> » که داخل نماد نقل قول ("") قرار گرفته است، استفاده کنید. برای مثال، همانطور که در برگه زیر مشاهده می‌کنید، از « قرمز <> » برای شمارش رنگ‌هایی که «قرمز نیست » و از « آبی <> » برای شمارش رنگ‌هایی که «آبی نیست» استفاده کنید:

= COUNTIF ( B3:B3 , "<>قرمز)   //   رنگ‌هایی که "قرمز" نیست
= COUNTIF ( B3:B7 , "<>آبی")   //   رنگ‌هایی که "آبی" نیست
= COUNTIF ( B3:B7 , "<>" & E5)   //  برابر نیست E5مقادیری که با مقدار سلول   

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

• در فرمول آخر، مستقیما از E5 استفاده می‌کنیم، بنابراین باید معیار را به صورت E5 &"<>" وارد کنیم.
• از نماد نقل قول در اطراف آدرس سلول استفاده نکنید.

سلول‌های خالی

تابع COUNTIF می‌تواند سلول‌هایی که خالی هستند یا حاوی مقداری باشند را بشمارد. از یک رشته خالی ("") برای هدف قرار دادن سلول‌های خالی و از عملگر "نامساوی با" ("<>") برای هدف قرار دادن سلول‌هایی که خالی نیستند، استفاده کنید. در برگه زیر COUNTIF برای شمارش سلول‌های خالی و غیر خالی بسته به اینکه ستون D حاوی "x" باشد یا نباشد، استفاده می‌شود:

= COUNTIF ( D3:D7 , "")   // شمارش سلول‌های خالی
= COUNTIF ( D3:D7 , "<>")   // شمارش سلول‌هایی که خالی نیستند

در فرمول دوم، تابع COUNTIF شمارش سلول‌هایی که خالی نیستند را بدون توجه به مقادیر آنها بر می‌گرداندد. نتیجه این است که ۲ فاکتور «پرداخت نشده» (خالی) و ۳ فاکتور پرداخت شده وجود دارد. برای دقیق‌تر کردن محاسبه ، می‌توانید از فرمولی به صورت زیر استفاده کنید که تنها سلول‌هایی را شمارش کند که حاوی "x" باشد:

= COUNTIF ( D3:D7 , "x")  //  است"x"شمارش سلول‌هایی که شامل

تاریخ‌ها

در اکسل، تاریخ‌ها اعداد سریالی هستند، بنابراین می‌توانید از عملگرهایی مانند <,>، <=، >= مانند هر عدد دیگری در ارزیابی‌های مربوط به تاریخ‌ها استفاده کنید. بخش چالش ‌برانگیز استفاده از تاریخ‌ها در شرط‌های COUNTIF وارد کردن تاریخ‌ به روشی است که اکسل آن را بفهمد. قابل اعتمادترین روش برای انجام این کار، ارجاع به یک تاریخ معتبر در سلول دیگر یا استفاده از تابع DATE است. برگه زیر هر دو روش را نشان می‌دهد:

= COUNTIF ( B3:B7, " < " & DATE ( 2025,3,1 ) )
= COUNTIF ( B3:B7, " >= " & DATE ( 2025,4,1 ) )
= COUNTIF ( B3:B7 ,">" & E7)

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

• هنگام استفاده از یک تابع با یک عملگر، باید مانند (2024,3,1)  DATE &"<" عمل الصاق کردن را انجام دهید.
• هنگام استفاده از یک آدرس سلول با یک عملگر، باید مانند E7 &">" عمل الصاق کردن را انجام دهید.

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

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

نویسه‌های جایگزین

تابع COUNTIF از سه نویسه جایگزین پشتیبانی می‌کند که می‌توانید در معرفی معیارها برای مطابقت بیشتر از آنها استفاده کنید:

• نماد ستاره (*) - با صفر یا چند نویسه مطابقت دارد
• نماد سوال (?) - با هر نویسه‌ای مطابقت دارد
• نماد مد (~) – برای ارزیابی خود نویسه‌های جایگزین استفاده می‌شود

برگه زیر نحوه استفاده از نویسه‌های جایگزین را با تابع COUNTIF نشان می‌دهد. فرمول‌های F3:F6 معیارهای شرح داده شده در ستون E را اعمال می‌کنند.

= COUNTIF ( B3:B13 , " *ان")   //     شمارش استان‌هایی که به “ ان” ختم می‌شود
= COUNTIF ( B3:B13 , "*ی")   //  شمارش استان‌هایی که به “ ی”  ختم می‌شود
= COUNTIF ( B3:B13 , "?????")   //   استان‌هایی که “5 حرفی” هستند 
= COUNTIF ( B3:B13 , "*~*")   //  شمارش استان‌هایی که به “ * ”  ختم می‌شود

توجه داشته باشید که فرمول سلول F6 از "*~*" برای مطابقت با نماد ستاره (*) که در انتهای "تهران*" در سلول C7 قرار دارد، استفاده می‌کند. نماد مد (~) امکان می‌دهد خود نویسه جایگزین را پیدا کنید. برای مطابقت با نماد سوال (?) از "?~" برای تطبیق  نماد ستاره (*) از "*~" و برای تطبیق نماد مد (~) از "~~" استفاده کنید. جدول زیر مثال‌های بیشتری از نحوه استفاده از نویسه‌های جایگزین را نشان می‌دهد:

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

منطق تابع OR

تابع COUNTIF برای اعمال تنها یک شرط طراحی شده است، بنابراین راهی برای شمارش سلول‌ها با منطق تابع OR "این یا آن" وجود ندارد. یک راه حل این است که معیارها را به عنوان یک آرایه ثابت مانند {"قرمز","آبی"} ارائه داده و سپس فرمول COUNTIF را درون تابع SUM به صورت تو در تو قرار دهید:

= SUM ( COUNTIF ( range , {"قرمز","آبی"} ))   //   قرمز یا آبی

فرمول فوق  سلول‌های موجود در مولفه اول (range) را زمانی شمارش می‌کند که شامل "قرمز" یا "آبی" باشند. اساسا COUNTIF دو تعداد را در یک آرایه بر می‌گرداند (یکی برای "قرمز" و دیگری برای "آبی") و تابع SUM مجموع  این دو تعداد ‌را بر می‌گرداند. 

جدول خلاصه

می‌توانید از COUNTIF برای ایجاد یک جدول خلاصه ساده استفاده کنید. در برگه زیر، لیستی از رنگ‌های غیرتکراری در محدوده F3:F7 داریم. هدف شمارش مقادیر ستون C بر اساس رنگ است. فرمول سلول G3، که در سلول‌های پایینی هم کپی شده به صورت زیر است:

= COUNTIF ( $C$3:$C$14 , F3 )

توجه داشته باشید که مولفه‌ اول به صورت آدرس مطلق قفل شده‌ است تا از تغییر آدرس‌‌ سلول‌های فرمول هنگام کپی فرمول در سلول‌های دیگر جلوگیری شود. اگر از اکسل نسخه ۲۰۲۱ یا بالاتر استفاده می‌کنید، می‌توانید مقادیر ستون جدول خلاصه را به طور همزمان با یک فرمول آرایه پویا به صورت زیر در سلول G3 محاسبه کنید:

= COUNTIF ( C3:C14 , F3:F7)

در این حالت به آدرس‌دهی مطلق نیازی نداریم زیرا یک فرمول واحد همه نتایج را ایجاد می‌کند. می‌توانید با استفاده از تابع UNIQUE در سلول F3 یک قدم جلوتر بروید تا لیستی از رنگ‌های غیر تکراری به صورت محدوده آبشاری  ایجاد کنید، سپس مستقیما به این محدوده آبشاری به صورت زیر ارجاع دهید: 

= COUNTIF ( C3:C14 , F3 # )

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

در اکسل ۳۶۵، می‌توانید از تابع جدید GROUPBY نیز برای ایجاد جدول خلاصه استفاده کنید.

مشکل کار با آرایه‌ها

یکی از محدودیت‌های مهم ‌COUNTIF این است که اجازه‌ استفاده از آرایه در مولفه اول را نمی‌دهد. یعنی نمی‌توانید آرایه‌ای را از تابع دیگر به COUNTIF به عنوان محدوده معرفی کنید. برای درک مشکل، به برگه‌ زیر توجه کنید که در آن 10 تاریخ در ستون B و 10 مقدار در ستون C داریم. هدف ایجاد فرمولی برای  شمارش ورودی‌ها بر اساس سال است. اگر با فرمول‌های اکسل کمی آشنایی دارید، ممکن است فکر کنید که می‌توانید از ترکیب COUNTIF و YEAR در یک فرمول به صورت زیر استفاده کنید:

= COUNTIF ( YEAR ( B3:B12 )  , E3 )

ایده این است که سال را از تاریخ‌های ستون B با استفاده از تابع YEAR استخراج کنید و سپس از عدد ۲۰۲۴ در سلول E5 به عنوان معیار استفاده کنید. اگر این روش جواب می‌داد، خیلی خوب می‌شد. اما ! اکسل حتی اجازه نمی‌دهد این فرمول را وارد کنید. اگر امتحان کنید، با خطای عمومی «مشکلی در فرمول وجود دارد» مواجه خواهید شد.

مشکل این است که COUNTIF برای مولفه range به یک محدوده‌ی مناسب نیاز دارد، اما YEAR (B3:B12) آرایه‌ای مانند آرایه زیر را بر می‌گرداند:

{2025;2025;2025;2025;2025;2024;2024;2024;2024;2024}

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

 چگونه می‌توانیم این مشکل را حل کنیم؟

یک راه حل خوب این است که از تابع SUMPRODUCT و فرمولی مانند این استفاده کنید:

= SUMPRODUCT ( - - (range=criteria) )

اگر این الگو را با مثال ارائه شده تطبیق دهیم، به نتیجه زیر می‌رسیم:

= SUMPRODUCT ( - - ( YEAR ( B3:B12 ) = E3 ) )

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

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

= COUNTIFS ( B3:B12 , ">=" & DATE ( E3 , 1 , 1 ) , B5:B12 , "<=" & DATE (E3 , 12 , 31 ) )

با این حال، این فرمول پیچیده‌تر است ، من گزینه SUMPRODUCT را ترجیح می‌دهم. در واقع، این مثال خوبی از این است که چگونه SUMPRODUCT  می‌تواند یک مسئله پیچیده را در یک فرمول حل کند.

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

محدودیت‌ها

تابع COUNTIF چند محدودیت دارد که باید از آنها آگاه باشید:

• COUNTIF فقط از یک شرط واحد پشتیبانی می‌کند. برای محاسبه شمارش سلول‌هایی که با چند شرط مطابقت دارند، از تابع COUNTIFS استفاده کنید.
• COUNTIF به حروف کوچک و بزرگ لاتین حساس نیست. از تابع EXACT به همراه SUMPRODUCT برای شمارش‌های حساس به حروف بزرگ و کوچک لاتین استفاده کنید.
• COUNTIF به یک محدوده واقعی برای مولفه اول خود نیاز دارد. نمی‌توانید یک آرایه ارائه دهید. این بدان معناست که نمی‌توانید مقادیر محدوده داخل فرمول را قبل از اعمال معیارها تغییر دهید. 
• COUNTIF اعداد طولانی بزرگتر از ۱۵ رقم را به درستی نمی‌شمارد. 
•  اگر با استفاده از تابع COUNTIF به محدوده‌ای در یک فایل اکسل خارجی ارجاع دهید، برای محاسبه نتیجه نیاز است که فایل اکسل باز باشد، در غیر این صورت COUNTIF خطای !VALUE# را بر می‌گرداند. برای حل این مشکل، می‌توانید از تابع SUMPRODUCT استفاده کنید که این محدودیت را ندارد. 

رایج‌ترین روش برای دور زدن اکثر این محدودیت‌ها، استفاده از تابع SUMPRODUCT است. با این حال، آخرین نسخه‌ اکسل جایگزین‌های قدرتمندی از جمله BYROW، BYCOL و GROUPBY ارائه می‌دهد.

نکات

• COUNTIF فقط از یک شرط پشتیبانی می‌کند. برای اعمال چند شرط از تابع COUNTIFS استفاده کنید.
• رشته‌های متنی (غیر عددی) در معیارها باید داخل علامت نقل قول قرار گیرند (مثلاً "<100"، ">32"، "TX")
• آدرس‌های سلول استفاده شده در مولفه دوم داخل علامت نقل قول قرار نمی‌گیرند، مثلاً "<"&A1
• نویسه‌های جایگزین را می‌توان در معیارها استفاده کرد.
• COUNTIF به یک محدوده معمولی نیاز دارد و نمی‌توانید از محدوده به صورت آرایه استفاده کنید.
• COUNTIF هنگام ارزیابی برای مطابقت با رشته‌های طولانی‌تر از 255 نویسه، نتایج نادرستی را بر می‌گرداند.
• COUNTIF هنگام ارجاع به یک فایل اکسل دیگر که باز نیست، خطای !VALUE# را بر می‌گرداند.

لطفا دیدگاه خود را در کادر پایین با ما به اشتراک بگذارید.

آیا می‌خواهید:

• برنامه‌های قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
• فرصت های شغلی جدید کسب کنید.

کتاب‌های معرفی شده ، راهنمای گام به گام جامع و عملی برای افرادی است که (مانند شما) می‌خواهند به این اهداف برسند.

مهراج محمدی

مدیر

...