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

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

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

نحوه کار با تابع Countif در اکسل همراه با مثال

نکته قابل توجه در کار با تابع های Countif این است که فرمول های Countif در تمام نسخه های Excel یکسان هستند و به همین دلیل می توانید از نمونه های این آموزش در اکسل 365، 2021، 2019، 2016، 2013، 2010 و 2007 بدون محدودیت استفاده کنید. همچنین برای ملموس تر شدن نکات، آموزش را با یک مثال کاربردی و آموزشی پیش خواهیم برد:

1- فرمول متن و اعداد

تصور کنید لیستی از میوه ها همراه با تعداد آن ها را به شکل زیر وارد اکسل کرده اید، فرمولی که باید برای شمارش میوه هایی که به تعداد 5 موجودی دارند به شکل =COUNTIF(C2:C15,"Roger Federer") خواهد بود که پارامترهای آن به صورت زیر قابل ترجمه است:
  • نوشتن اولین پارامتر (ستون انتخابی) در محدوده داخل ()
  • استفاده از علامت , به عنوان جداکننده برای ادامه نوشتن دستور
  • نوشتن عنوان معیار در محدوده بعد از علامت کاما
  • استفاده از کلمات به جای لیست اعداد در ستون 2 مانند =COUNTIF(C1:C9,C7) در صورت درخواست
کادر اول در ستون C را انتخاب کرده و Fx را بزنید Count را بزنید. سپس فرمول COUNTIF(B2:B9,5)= را وارد کنید و گزینه OK را کلیک نمایید.
نکته مهم! به یاد داشته باشید که در تمام مراحل پیش رو برای نوشتن فرمول در اکسل، نحوه وارد کردن فرمول به صورت تصویر زیر است و تفاوتی ندارد.
مراحل استفاده از فرمول های خاص تابع Countif در Excel
اکنون مانند تصویر زیر تمام اطلاعات موجود با مقدار 5 در ستون C برای شما لیست خواهند شد:
مراحل استفاده از فرمول های خاص تابع Countif در Excel

2- فرمول کاراکترهای Wildcard (عام)

روشی که برای شمارش اطلاعات حاوی شمارش تمام سلول‌های حاوی یک کلمه، عبارت یا حروف خاص که در این آموزش کلمه "حسین" با فرمول =(COUNTIF(B2:B10,"*حسین*")) در نظر گرفته شده است را شروع به شمارش می کند:
مراحل استفاده از فرمول های خاص تابع Countif در Excel
همانند مثالی که در بالا نشان داده شد، یک ستاره (*) در اطراف کلمات "حسین" بکار برده شده است که در صورت عدم نتیجه گیری از این روش می توانید از علامت سؤال (؟) نیز کمک بگیرید. همچنین ایده های فرمول نویسی زیر را نیز می توانید استفاده کنید:
  • فرمول (B2:B10,"*علی")COUNTIF= مناسب زمانی که می خواهید داده های شروع شده با کلمه "علی" را پیدا کنید.
  • فرمول (C2:C10,"زاده*")COUNTIF= مناسب زمانی که می خواهید داده های تمام شده با کلمه "زاده" را پیدا کنید.
نکته مهم! می توانید به جای علامت (*) از (?) نیز استفاده کنید. همچنین برای پیدا کردن تعداد علامت هایی مانند (*) و (?) در جدول فرمول شما باید چیزی شبیه به COUNTIF(B2:B9,"*~?*")= باشد.

3- فرمول Blank Or Not Blank

چنانچه می خواهید فرمولی بنویسید که تعداد خانه های خالی و یا پر شده از اطلاعات در اکسل را به شما نشان دهد، موارد زیر را مطالعه کنید:
  • نمایش تعداد خانه های پر در اکسل
برخی از منابع علمی تنها فرمولی که برای این کار معرفی می کنند فرمول COUNTIF(A1:A10"*") می باشد، اما این یک واقعیت است و باگ آن در این است که تاریخ و اعداد به عنوان سلول های خالی در نظر گرفته شده و در شمارش درستی صورت نمی پذیرد. پس باید یکی از دو فرمول زیر را برای دسترسی به بهترین جواب وارد کنید:
  1. COUNTIF(range,"<>")
  2. COUNTIF(range,"<>"&"")
مراحل استفاده از فرمول های خاص تابع Countif در Excel
نکته مهم! نوشتن مشخصات ستون را در فرمول بالا فراموش نکنید.
  • نمایش تعداد خانه های خالی در اکسل
برای دستیابی به اطلاعات مقابل روش بالا یعنی بدست آوردن تعداد خانه های خالی در اکسل، فرمول های زیر را همانند روش های پیشین همراه با مشخصات ستون تایپ کنید:
  1. فرمول COUNTIF(range,"<>"&"*")= برای دستیابی به خانه های خالی بدن متن
  2. فرمول 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") داده های موجود در یک محدوده خاص زمانی را می شمارد.

برای مثال در ادامه یک سری از داده های انتخابی را با فرمول تاریخ دسته بندی کرده ایم:
مراحل استفاده از فرمول های خاص تابع Countif در Excel

6- فرمول معیارهای متعدد

برای آشنایی با این نوع از دسته فرمول های Countif در Excel باید گفت که این دستورات برای یافتن تعداد یا سلول‌هایی که با یک یا چند معیار مطابقت دارند استفاده می شود که می توان آن ها را به دسته بندی های زیر تقسیم کرد:
  • شمردن اطلاعات عددی بین دو عدد
نحوه شمارش اعداد در یک محدوده خاص یعنی کمتر از X اما بزرگتر از Y که فرمول های مخصوص به خود دارد و می تواند اطلاعات زیادی را در اختیار شما قرار دهد. برای مثال برای شمردن اطلاعات محدوده B2:B9 که بزرگتر از 5 و کمتر از 15 باشد از فرمول COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15")= کمک می گیریم.
  • شمردن اطلاعات متنی 
همان طور که مشاهده کردید فرمول بالا برای شمردن اطلاعات عددی است، اما برای لیست کردن بر اساس دو نام مختلف می توانید از جمع دو فرمول مانند فرمول (B2:B13,"لیمو")+COUNTIF(B2:B13,"گریپ فروت")COUNTIF= استفاده کنید.

7- فرمول شمارش در مقادیر تکراری و منحصر به فرد

به جرات می توان گفت یکی از کاربردی ترین فرمول های موجود در این راهنما همین گزینه خواهد بود، زیرا همواره ما نیاز داریم تا از شر اطلاعات اضافه و تکراری در اکسل خلاص شویم و یا برعکس برخی اوقات ممکن است از دیدن تکرار موفقیت خود لذت ببریم. پس به همین دلیل برای سهولت در کاربرد می توانید از دسته بندی فرمول های زیر استفاده کنید:
  • شمردن اطلاعات تکراری در ستون
نمونه ای از فرمولی که می توانید برای مشاهده و تصمیم گیری در خصوص همه ورودی های تکراری B2:B10 بکار گرفت به شکل COUNTIF(B2:B10,B2)>1= است.
  • شمردن اطلاعات تکراری در دو ستون
ممکن است نیاز داشته باشید تا دو ستون متنی را برای بررسی نام های و اطلاعات تکراری بررسی کنید که در این حالت لازم است تا از ترکیب تابع های Excel COUNTIF و SUMPRODUCT همانند فرمول زیر که به راحتی داده های تکراری موجود در دو ستون را بررسی می کند SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)>0)*(C2:C1000<>""))= کمک بگیرید.
  • شمردن اطلاعات تکراری در ردیف
چنانچه می خواهید یک ردیف از داده های خود را برای بررسی اطلاعات تکراری زیر ذره بین قرار دهید، باید یکی از فرمول های زیر را امتحان کنید:
  1. فرمول SUMPRODUCT((COUNTIF(A1:N2,A2:N2)>1)*(A1:N2<>""))= برای داده های تکراری
  2. فرمول SUMPRODUCT((COUNTIF(A1:N2,A1:N2)=1)*(A1:N2<>""))= برای داده های منحصر بفرد
مراحل استفاده از فرمول های خاص تابع Countif در Excel

سخن آخر

از توضیحات ارائه شده مشخص است که تابع Countif در Excel برای شمارش سلول‌ها در محدوده مشخصی که معیار یا شرایط خاصی مورد استفاده کاربران مبتدی و حرفه ای قرار می گیرد و می تواند در برای شمارش سلول ها با یک کلمه خاص یا شروع با یک حرف روش پیشرفته و سریعی برای شما باشد. این تنوع کاربرد بسیار قدرتمند است و توصیه می کنیم از روش های بالا در دسته بندی اطلاعات خود بهره ببرید.
چقدر این پست مفید بود؟ روی یک ستاره کلیک کنید تا به آن امتیاز دهید!
پست های پیشنهادی
گفتن شب بخیر به انگلیسی با 20 روش
مبتدی

گفتن شب بخیر به انگلیسی با 20 روش

نحوه گفتن شب بخیر در زبان انگلیسی!
تیم محتوا
1403/04/16
نصب اینستاگرام روی کامپیوتر
مبتدی

2+1 راهکار برای نصب اینستاگرام روی کامپیوتر

چگونه می توان Instagram را روی کامپیوتر نصب کرد؟
تیم محتوا
1403/05/21
ورود به اینستاگرام با ایمیل به همراه عیب یابی مشکلات
متوسط

ورود به اینستاگرام با ایمیل به همراه عیب یابی مشکلات

روش ورود به اینستاگرام با ایمیل به صورت تصویری + حل مشکلات و...
تیم محتوا
1403/05/23