تابع فرمول COUNTIF در اکسل چیست؟ (به زبان خودمانی)
تا حالا براتون پیش اومده که یه لیست بلندبالا از اسامی، اعداد یا محصولات توی اکسل داشته باشید و بخواید خیلی سریع بدونید که مثلاً اسم “زهرا” چند بار تکرار شده یا چند تا از حقوقها بیشتر از ۱۰ میلیون تومانه؟
راه سختش اینه که اکسل رو سطر به سطر با چشم بگردید و با انگشت بشمارید (که هم وقتگیره هم پر از خطا).
راه آسونش استفاده از یه ابزار جادویی به اسم فرمول COUNTIF در اکسل هست. تابع COUNTIF در اکسل مثل یه منشی باهوش عمل میکنه که بهش میگید: “داداش برو تو این ستون (محدوده) بگرد ببین چند تا سلول هست که شرط من رو داشته باشه و تعدادشون رو بهم بگو!”
این تابع یکی از پرکاربردترین توابع اکسل است که از دو بخش COUNT (شمارش) و IF (اگر) تشکیل شده. یعنی اگر شرط من برقرار بود، بشمار.
یک مثال خیلی ساده:
فرض کنید در ستون A لیست نمرات دانشجویان را دارید. میخواهید بدانید چند نفر نمره ۲۰ گرفتهاند. به جای شمردن دستی، به اکسل میگویید:
=COUNTIF(A:A, 20)
بلافاصله عدد مربوطه به شما نشان داده میشود. این کاربرد در حسابداری، دفترداری، انبارداری و حتی کارهای شخصی خانه به شدت نجاتدهنده است.
ساختار تابع و آرگومانها (موتور جستجوی اکسل)
برای اینکه بتونید از این منشی باهوش استفاده کنید، باید دستوراتش رو بلد باشید. ساختار تابع COUNTIF خیلی ساده است و فقط دو تا بخش داره که بهشون میگیم آرگومان:
=COUNTIF(Range, Criteria)
| آرگومان | معنی | مثال |
|---|---|---|
Range |
محدودهای که میخواهید جستجو کنید. | A2:A100 یا B:B (کل ستون B) |
Criteria |
شرطی که باید بررسی شود. | "تهران" یا ۱۰۰ یا ">5000" |
یک نکته طلایی:
حتماً دقت کنید که شرط متنی باید داخل علامت نقل قول (" ") قرار بگیرد.
-
درست:
=COUNTIF(A1:A10, "سیب") -
نادرست:
=COUNTIF(A1:A10, سیب)
نکته طلایی دوم:
برای جلوگیری از جابجایی محدوده هنگام کپی کردن فرمول، میتوانید از آدرسدهی مطلق با علامت دلار ($) استفاده کنید.
-
مثال:
=COUNTIF($A$1:$A$10, "تهران")
۴ مثال کاربردی برای افراد مبتدی و حرفهای
تا اینجا تئوری بود. بریم سراغ کار عملی. این ۴ مثال طوری طراحی شده که ۹۰٪ نیازهای روزمره شما رو پوشش بده.
مثال ۱: شمارش متن دقیق (مناسب برای دفترداران و فروشندگان)
فرض کنید لیست فروش روزانه دارید و میخواهید بدانید دقیقاً چند عدد “شیر” فروخته شده است.
| A | |
|---|---|
| ۱ | محصول |
| ۲ | شیر |
| ۳ | ماست |
| ۴ | شیر |
| ۵ | پنیر |
فرمول: =COUNTIF(A2:A5, "شیر")
نتیجه: ۲
مثال ۲: شمارش اعداد بزرگتر یا مساوی (مناسب برای حسابداران)
میخواهید بدانید چند فاکتور بالای ۵ میلیون تومان صادر شده است.
| A | B | |
|---|---|---|
| ۱ | نام مشتری | مبلغ فاکتور |
| ۲ | علی | ۴,۵۰۰,۰۰۰ |
| ۳ | رضا | ۷,۲۰۰,۰۰۰ |
| ۴ | مریم | ۵,۰۰۰,۰۰۰ |
فرمول: =COUNTIF(B2:B4, ">=5000000")
نتیجه: ۲ (چون هم ۷.۲ میلیون و هم ۵ میلیون شامل شرط میشوند)
مثال ۳: شمارش موارد نابرابر (مناسب برای مدیران کنترل کیفیت)
فرض کنید میخواهید بدانید چه تعداد از کالاها غیر از رنگ “قرمز” هستند.
| A | |
|---|---|
| ۱ | رنگ کالا |
| ۲ | قرمز |
| ۳ | آبی |
| ۴ | قرمز |
فرمول: =COUNTIF(A2:A4, "<>قرمز")
نتیجه: ۱ (فقط رنگ آبی)
مثال ۴: استفاده از Wildcard برای جستجوی ناقص (جستجوی هوشمند)
بعضی وقتا اسم کامل رو نمیدونیم. مثلاً میخواهیم هر سلولی که کلمه “آقای” توش هست رو بشماریم، چه “آقای رضایی” چه “آقای محمدی” (این برای خانمهای خانهدار در لیست مهمانی یا کارمندان در لیست پرسنل کاربرد داره).
| A | |
|---|---|
| ۱ | نام و نام خانوادگی |
| ۲ | آقای احمدی |
| ۳ | خانم حسینی |
| ۴ | آقای محمدی |
فرمول: =COUNTIF(A2:A4, "*آقای*")
نتیجه: ۲
در بخش بعدی Wildcard ها رو کاملتر توضیح میدم.
عملگرهای منطقی (رمز پیشرفته کار کردن با COUNTIF)
این بخش خیلی مهمه و معمولاً کاربرا توش گیر میکنن. چطور به اکسل بفهمونیم مثلاً “بزرگتر از” یا “کوچکتر مساوی”؟
| عملگر | معنی | مثال در COUNTIF |
|---|---|---|
> |
بزرگتر از | =COUNTIF(A:A, ">100") |
< |
کوچکتر از | =COUNTIF(B:B, "<50") |
>= |
بزرگتر یا مساوی | =COUNTIF(C:C, ">=10") |
<= |
کوچکتر یا مساوی | =COUNTIF(D:D, "<=0") |
<> |
مخالف (نابرابر) | =COUNTIF(E:E, "<>انصرافی") |
ترفند حرفهای: شرط پویا با ارجاع به سلول
به جای اینکه عدد ۱۰۰ رو داخل فرمول بنویسید، میتونید به یه سلول دیگه ارجاع بدید. اینطوری دیگه لازم نیست هر بار فرمول رو عوض کنید.
مثلاً در سلول F1 عدد ۱۰۰ رو داریم.
فرمول اشتباه: =COUNTIF(A:A, ">F1") ← اکسل فکر میکنه دنبال متن ">F1" میگردی!
فرمول درست: =COUNTIF(A:A, ">"&F1)
نکته: حتماً از علامت & برای چسباندن عملگر به محتوای سلول استفاده کنید.
کار با Wildcardها (جادوی ستاره و سوال)
این بخش رو رقبا یا نگفتن یا خیلی سرسری ازش گذشتن. Wildcard یعنی کاراکترهای جایگزین. سه تا کاراکتر جادویی داریم:
-
علامت ستاره
*: یعنی هر تعداد کاراکتر (حتی صفر تا). -
علامت سوال
?: یعنی دقیقاً یک کاراکتر. -
علامت مد
~: برای خنثیسازی جادوی ستاره و سوال.
کاربردها:
-
شمارش مواردی که با حرف “ش” شروع میشوند:
=COUNTIF(A:A, "ش*") -
شمارش مواردی که به “ی” ختم میشوند:
=COUNTIF(A:A, "*ی") -
شمارش کدهایی با فرمت خاص: مثلاً شمارش کدهای
۱۲-۳۴-۵۶(اگر فرمت ثابت باشه):=COUNTIF(A:A, "??-??-??") -
شمارش خود علامت ستاره: فرض کنید دنبال عبارت
*توجه*میگردید:=COUNTIF(A:A, "~*توجه~*")
شمارش سلولهای خالی و ناخالی (سوال تکراری همه کاربران)
این یکی از رایجترین سوالات تازهکارهاست.
-
برای شمارش سلولهای خالی (Blank):
=COUNTIF(A1:A10, "") -
برای شمارش سلولهای ناخالی (Non-Blank):
=COUNTIF(A1:A10, "<>")
تفاوت COUNTIF و COUNTIFS (چه زمانی از کدام استفاده کنیم؟)
خیلی از کاربرا این دو رو قاطی میکنن. بیایید یک بار برای همیشه قضیه رو روشن کنیم.
-
COUNTIF: برای یک شرط به کار میره. (مثال: چند نفر تهرانی هستند؟) -
COUNTIFS: برای چند شرط همزمان به کار میره. (مثال: چند نفر تهرانی هستند و حقوقشان بالای ۱۰ میلیون است؟)
ساختار COUNTIFS:
=COUNTIFS(محدوده۱, شرط۱, محدوده۲, شرط۲, ...)
نکته بسیار مهم (مزیت رقابتی):
در نسخههای جدید اکسل، همیشه بهتر است از COUNTIFS استفاده کنید حتی اگر فقط یک شرط دارید. چرا؟ چون اگر بعداً خواستید شرط دوم اضافه کنید، نیازی به تغییر فرمول نیست. همچنین COUNTIFS در کار با کل ستون (A:A) در فایلهای حجیم سریعتر و بهینهتر از COUNTIF عمل میکند.
ترکیب قدرتمند COUNTIF با دیگر توابع (اینجا حرفهای میشوید!)
این بخش رو هیچ رقیبی پوشش نداده! قدرت واقعی اکسل در ترکیب توابع هست.
۱. ترکیب با IF برای نمایش پیام:
آیا میخواهید اگر عددی بیش از ۳ بار تکرار شد، هشدار “تکراری” ببینید؟
=IF(COUNTIF(A:A, A1)>1, "تکراری", "مقدار یکتا")
۲. ترکیب با SUM برای شمارش شرط “یا” (OR):
COUNTIF فقط شرط “و” (AND) رو پشتیبانی میکنه. اگر بخوایم تعداد فروشندگان “تهران” یا “اصفهان” رو بشماریم چطور؟
=SUM(COUNTIF(B:B, {"تهران", "اصفهان"}))
۳. جلوگیری از خطا با IFERROR:
اگه فرمولت به هر دلیلی خطا داد (مثلاً اسم شیت اشتباه بود)، چطور ظاهر کاربرگ رو زشت نکنیم؟
=IFERROR(COUNTIF(A:A, "تهران"), "خطا در محاسبه")
عیبیابی و رفع خطاهای رایج (مشکلاتی که همه را کلافه میکند)
خطای ۱: #VALUE!
-
دلیل: معمولاً وقتی رخ میدهد که شرط متنی بیشتر از ۲۵۵ کاراکتر باشد، یا فایلی که فرمول از آن داده میخواند بسته باشد، یا
COUNTIFSمحدودههایی با اندازه نابرابر داشته باشد. -
راه حل: شرط رو خلاصهتر کنید. فایل منبع رو باز کنید. مطمئن شید که تعداد سطر و ستون محدودهها یکسان است.
خطای ۲: خروجی فرمول ۰ (صفر) است ولی میدانیم باید عددی وجود داشته باشد.
-
دلیل: این یکی از مرموزترین خطاهاست. معمولاً به خاطر وجود فضای خالی (Space) مخفی در انتهای متن است.
-
راه حل: از
Wildcardاستفاده کنید! به جای=COUNTIF(A:A, "تهران")بنویسید:=COUNTIF(A:A, "*تهران*"). یا اینکه از تابعTRIMبرای پاکسازی دادهها استفاده کنید.
خطای ۳: ارور #NAME?
-
دلیل: اسم تابع را اشتباه تایپ کردهاید (مثلاً
COUNTEF). -
راه حل: دیکته کلمه را چک کنید.
خطای ۴: خطای Circular Reference (ارجاع حلقوی)
-
دلیل: فرمول
COUNTIFرا در سلولی مینویسید که خود آن سلول هم جزو محدوده جستجو (Range) است. -
راه حل: فرمول را در یک سلول خارج از محدوده جستجو بنویسید.
سوالات متداول (FAQ)
سوال ۱: آیا COUNTIF میتواند بر اساس رنگ سلول بشمارد؟
خیر. تابع COUNTIF فقط محتوای عددی یا متنی سلول را بررسی میکند. برای شمارش بر اساس رنگ باید از کدنویسی VBA یا ابزار Filter استفاده کنید.
سوال ۲: چرا COUNTIF تاریخ شمسی من را نمیشناسد؟
اکسل تاریخ را به عنوان یک عدد سریال ذخیره میکند. برای جستجوی تاریخ شمسی، بهتر است تاریخ را در یک سلول جداگانه (مثلاً D1) بنویسید و از آن در فرمول استفاده کنید: =COUNTIF(B:B, D1). اگر میخواهید بازه تاریخی جستجو کنید، از COUNTIFS استفاده کنید: =COUNTIFS(B:B, ">="&D1, B:B, "<="&E1).
سوال ۳: آیا میتوانم با COUNTIF موارد تکراری را پیدا کنم؟
بله! فرمول زیر در سلول B1 مشخص میکند که مقدار A1 چند بار تکرار شده است. اگر نتیجه بزرگتر از ۱ بود، یعنی تکراری است:
=COUNTIF(A:A, A1)
جمعبندی و مسیر حرفهای شدن در اکسل
تبریک میگویم! شما اکنون بر یکی از قدرتمندترین ابزارهای اکسل مسلط شدهاید. فرمول COUNTIF به شما این قدرت را میدهد که به جای اتلاف وقت برای شمارش دستی، در چند ثانیه گزارشهای حرفهای و دقیق تهیه کنید. چه یک دانشجو باشید که نمرات را تحلیل میکند، چه یک خانم خانهدار که هزینههای ماهانه را مدیریت میکند و چه یک کارمند یا دفتردار که با انبوهی از داده سروکار دارد، یادگیری این تابع گامی بلند به سوی افزایش سرعت و دقت شماست.
اما نکته اینجاست که COUNTIF فقط یک قطره از دریای بیکران قابلیتهای اکسل است. تسلط واقعی زمانی اتفاق میافتد که بتوانید این تابع را در کنار دهها تابع دیگر به کار بگیرید، گزارشهای پویا طراحی کنید و داشبوردهای مدیریتی خلق کنید.
اگر میخواهید این مسیر را سریعتر و اصولیتر طی کنید و از سردرگمی در میان انبوه اطلاعات پراکنده اینترنت نجات پیدا کنید، دورههای آموزشی تخصصی ما دقیقاً برای همین هدف طراحی شدهاند.
در دورههای ما، فراتر از فرمولنویسی مقدماتی را یاد میگیرید:
✅ صفر تا صد اکسل با تمرکز بر بازار کار.
✅ یادگیری پروژهمحور و کاربردی (نه صرفاً تئوری).
✅ رفع اشکال و پشتیبانی اختصاصی مدرس.
✅ رازهای تهیه گزارشهای لحظهای و حرفهای.
همین حالا قدم اول را بردارید!
برای مشاهده سرفصلهای کامل دورهها و دریافت مشاوره رایگان، روی لینک زیر کلیک کنید و اولین مهارت طلایی خود را به رزومهتان اضافه کنید.
