آموزش کار با تابع Countif در اکسل همراه با مثال

0 نظر
شنبه 13 مرداد
مطالعه: 40 دقیقه
آموزش های عمومی
بی شک یکی از کاربردی ترین نرم افزارها در زمینه دسته بندی و استخراج داده های کوچک و بزرگ Excel است. از این رو تمرکز ما در این راهنما، از سری آموزش های اکسل بر روی تابع Excel COUNTIF خواهد بود که می تواند تمام روش های عملی را برای شمارش انواع سلولها، مانند سلولهای خالی یا غیرخالی، با مقادیر عدد، تاریخ یا متن، حاوی کلمات یا کاراکترهای خاص و ... در اطلاعات شما را ارائه کند. پس جهت ارتقا مهارت های خود در زمینه کار با تابع Countif در اکسل با ما همراه شوید.
نحوه کار با تابع Countif در اکسل همراه با مثال
نکته قابل توجه در کار با تابع های Countif این است که فرمول های Countif در تمام نسخه های Excel یکسان هستند و به همین دلیل می توانید از نمونه های این آموزش در اکسل 365، 2021، 2019، 2016، 2013، 2010 و 2007 بدون محدودیت استفاده کنید. همچنین برای ملموس تر شدن نکات، آموزش را با یک مثال کاربردی و آموزشی پیش خواهیم برد:1- فرمول متن و اعداد
تصور کنید لیستی از میوه ها همراه با تعداد آن ها را به شکل زیر وارد اکسل کرده اید، فرمولی که باید برای شمارش میوه هایی که به تعداد 5 موجودی دارند به شکل =COUNTIF(C2:C15,"Roger Federer") خواهد بود که پارامترهای آن به صورت زیر قابل ترجمه است:- نوشتن اولین پارامتر (ستون انتخابی) در محدوده داخل ()
- استفاده از علامت , به عنوان جداکننده برای ادامه نوشتن دستور
- نوشتن عنوان معیار در محدوده بعد از علامت کاما
- استفاده از کلمات به جای لیست اعداد در ستون 2 مانند =COUNTIF(C1:C9,C7) در صورت درخواست
نکته مهم! به یاد داشته باشید که در تمام مراحل پیش رو برای نوشتن فرمول در اکسل، نحوه وارد کردن فرمول به صورت تصویر زیر است و تفاوتی ندارد.

اکنون مانند تصویر زیر تمام اطلاعات موجود با مقدار 5 در ستون C برای شما لیست خواهند شد:

2- فرمول کاراکترهای Wildcard (عام)
روشی که برای شمارش اطلاعات حاوی شمارش تمام سلولهای حاوی یک کلمه، عبارت یا حروف خاص که در این آموزش کلمه "حسین" با فرمول =(COUNTIF(B2:B10,"*حسین*")) در نظر گرفته شده است را شروع به شمارش می کند:
همانند مثالی که در بالا نشان داده شد، یک ستاره (*) در اطراف کلمات "حسین" بکار برده شده است که در صورت عدم نتیجه گیری از این روش می توانید از علامت سؤال (؟) نیز کمک بگیرید. همچنین ایده های فرمول نویسی زیر را نیز می توانید استفاده کنید:
- فرمول (B2:B10,"*علی")COUNTIF= مناسب زمانی که می خواهید داده های شروع شده با کلمه "علی" را پیدا کنید.
- فرمول (C2:C10,"زاده*")COUNTIF= مناسب زمانی که می خواهید داده های تمام شده با کلمه "زاده" را پیدا کنید.
3- فرمول Blank Or Not Blank
چنانچه می خواهید فرمولی بنویسید که تعداد خانه های خالی و یا پر شده از اطلاعات در اکسل را به شما نشان دهد، موارد زیر را مطالعه کنید:- نمایش تعداد خانه های پر در اکسل
- COUNTIF(range,"<>")
- COUNTIF(range,"<>"&"")

نکته مهم! نوشتن مشخصات ستون را در فرمول بالا فراموش نکنید.
- نمایش تعداد خانه های خالی در اکسل
- فرمول COUNTIF(range,"<>"&"*")= برای دستیابی به خانه های خالی بدن متن
- فرمول COUNTIF(C2:C11,"")= و یا COUNTBLANK(C2:C11)= برای خانه های خالی در محدوده C2:C11
4- فرمول شمارش داده های بزرگتر، کمتر یا مساوی
جدول زیر راهنمایی کلی برای انواع فرمول هایی است که می خواهید مقادیر بزرگتر، کمتر و یا مساوی با عدد خاصی را در اطلاعات اکسل بدست آورید:فرمول | مفهوم فرمول | اطلاعات خروجی فرمول |
COUNTIF(B2:B10,">5")= | شمارش داده های بزرگتر | تعداد خانه هایی که عدد بزرگتر از 5 هستند را می شمارد |
COUNTIF(B2:B10,"<5")= | شمارش داده های کمتر | تعداد خانه هایی که عدد کمتر از 5 هستند را می شمارد |
COUNTIF(B2:B10,"=5")= | شمارش داده های مساوی | تعداد خانه هایی که مساوی با 5 هستند را می شمارد |
COUNTIF(B2:B10,"<>5")= | شمارش داده های نامساوی | تعداد خانه هایی که نامساوی با 5 هستند را می شمارد |
COUNTIF(B2:B8,">=5")= | شمارش داده های بزرگتر یا مساوی | تعداد خانه هایی که بزرگتر و یا مساوی با 5 هستند را می شمارد |
COUNTIF(B2:B8,"<=5")= | شمارش داده های کوچکتر یا مساوی | تعداد خانه هایی که کوچکتر و یا مساوی با 5 هستند را می شمارد |
نکته مهم! فراموش نکنید که قبل از نوشتن مشخصات مرجع سلولی که می خواهید مقادیر بیشتر و یا کمتر از آن را بدست آورید، باید در فرمول یک علامت (&) را مانند مثال COUNTIF(A2:A9,">"&A3) بنویسید.
5- فرمول تاریخ ها
ممکن است حالتی برای شما رخ دهد که بخواهید داده های خود را بر اساس تاریخ خاصی دسته بندی کنید. در این صورت باید مراحل و فرمول های زیر را برای بدست آوردن دقیق ترین اطلاعات بکار برد:فرمول | مفهوم فرمول | اطلاعات خروجی فرمول |
COUNTIF(A2:A10,"6/1/2014")= | تاریخ های برابر | داده های محدوده A2:A10 با تاریخ مشخص را می شمارد. |
COUNTIF(A2:A10,">=6/1/2014")= | تاریخ های بزرگتر یا مساوی | داده های محدوده A2:A10 با تاریخ بزرگتر یا مساوی را می شمارد. |
COUNTIF(A2:A10,">="&A2-"7")= | تاریخ های بزرگتر یا مساوی با یک تاریخ منهای x | داده های A2:A10 با تاریخ بزرگتر یا مساوی A2 منهای 7 روز را می شمارد. |
اما اگر به دنبال اطلاعاتی می گردید که معیار مقایسه شما تاریخ امروز است، می بایست یکی از فرمول های زیر را با توجه به اطلاعات خروجی فرمول و بر اساس تاریخ فعلی وارد کنید:
فرمول | اطلاعات خروجی فرمول |
COUNTIF(B2:B10،TODAY())= | داده های برابر با تاریخ روز را می شمارد. |
COUNTIF(B2:B10,"<"TODAY())= | داده های قبل از تاریخ روز را می شمارد. |
COUNTIF(B2:B10,">"TODAY())= | داده های بعد از تاریخ روز را می شمارد |
COUNTIF(B2:B10,"="&TODAY()+7)= | داده های امروز و 7 روز بعد را می شمارد. |
COUNTIF(A2:A10, ">=6/1/2014")-=COUNTIF(A2:A10, ">6/7/2014") | داده های موجود در یک محدوده خاص زمانی را می شمارد. |
برای مثال در ادامه یک سری از داده های انتخابی را با فرمول تاریخ دسته بندی کرده ایم:

6- فرمول معیارهای متعدد
برای آشنایی با این نوع از دسته فرمول های Countif در Excel باید گفت که این دستورات برای یافتن تعداد یا سلولهایی که با یک یا چند معیار مطابقت دارند استفاده می شود که می توان آن ها را به دسته بندی های زیر تقسیم کرد:- شمردن اطلاعات عددی بین دو عدد
- شمردن اطلاعات متنی
7- فرمول شمارش در مقادیر تکراری و منحصر به فرد
به جرات می توان گفت یکی از کاربردی ترین فرمول های موجود در این راهنما همین گزینه خواهد بود، زیرا همواره ما نیاز داریم تا از شر اطلاعات اضافه و تکراری در اکسل خلاص شویم و یا برعکس برخی اوقات ممکن است از دیدن تکرار موفقیت خود لذت ببریم. پس به همین دلیل برای سهولت در کاربرد می توانید از دسته بندی فرمول های زیر استفاده کنید:- شمردن اطلاعات تکراری در ستون
- شمردن اطلاعات تکراری در دو ستون
- شمردن اطلاعات تکراری در ردیف
- فرمول SUMPRODUCT((COUNTIF(A1:N2,A2:N2)>1)*(A1:N2<>""))= برای داده های تکراری
- فرمول SUMPRODUCT((COUNTIF(A1:N2,A1:N2)=1)*(A1:N2<>""))= برای داده های منحصر بفرد
