توابع COUNTIF،COUNTIFS،COUNT،COUNTA و COUNTBLANK

18 دی 1400

دقیقه

توابع COUNTIF،COUNTIFS،COUNT،COUNTA و COUNTBLANK - اغلب پیش می‌آید بخواهید تعداد سلول‌های محدوده‌ای که با یک شرط و معیار خاص مطابقت دارند را شمارش کنید. مثلاً اگر کاربرگی حاوی اطلاعاتی درباره فروش لوازم آرایشی باشد، شاید بخواهید تعداد مبادلات فروشی را که توسط فروشنده‌ای بنام جنیفر و یا تعداد مبادلات فروشی را که پس از تاریخ دهم ژوئن انجام شده را بشمارید. تابع COUNTIF به شما اجازه می‌دهد تعداد سلول‌های یک محدوده که با معیارها یا شروط موجود در ردیف یا ستون‌های کاربرگ مطابقت دارند را شمارش کنید.

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل که در فصل گذشته به بررسی استفاده از مدیریت سناریو (Scenario Manager) در تحلیل حساسیت پرداختیم، در این فصل به توابع COUNTIF،COUNTIFS،COUNT،COUNTA و COUNTBLANK می پردازیم.

سؤال‌هایی که در این فصل به آنها پاسخ داده می‌شود:

  • فرض کنید لیستی از ترانه‌ها داریم که در رادیو پخش شده‌اند. مشخصه هایی از هر ترانه چون نام خواننده، تاریخ پخش ترانه و مدت‌زمان آن در اختیار مان قرارداده شده است. چگونه می‌توان به سؤالاتی مثل سؤالات زیر در مورد ترانه‌های این لیست پاسخ دهم؟
    • چندتا از ترانه های هر خواننده پخش شده است؟
    • چند ترانه توسط امینم خوانده نشده است؟
    • چند ترانه دارای طول حداقل 4 دقیقه‌ای هستند؟
    • چند ترانه نسبت به میانگین طول کل ترانه‌های لیست دارای طول بلندتری می‌باشند؟
    • چند ترانه از خواننده‌هایی هستند که نام خانوادگی آنها با حرف S آغاز می‌شود؟
    • چند ترانه از خواننده‌هایی هستند که نام خانوادگی آنها دقیقاً شش حرف دارد؟
    • چند تا از ترانه‌ها بعد از تاریخ 15 ژوئن سال 2005 پخش شده‌اند؟
    • چند تا از ترانه‌ها پیش از سال 2009 پخش شده‌اند؟
    • طول چند تا از ترانه‌ها دقیقاً 4 دقیقه می‌باشد؟
    • چند تا از ترانه‌های پخش شده توسط بروس اسپرینگستین خوانده شده‌اند و دقیقاً چهار دقیقه طول می‌کشند؟
    • چند تا از ترانه‌ها توسط مادونا خوانده شده و سه یا چهار دقیقه طول می‌کشند؟

 

  • در موضوعی کلی تر، چگونه عملیاتی شبیه عملیات زیر را انجام می‌دهیم؟
    • شمارش تعداد سلول‌های حاوی ارقام در یک محدوده.
    • شمارش تعداد سلول‌های خالی در یک محدوده.
    • شمارش تعداد سلول‌های غیر خالی در یک محدوده.

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

دستور زبان تابع COUNTIF‌ عبارت است از: COUNTIF(range,criterion) با استفاده از آرگومان‌های زیر:

Range(محدوده) محدوده سلول‌هایی است که می‌خواهید در آن سلول‌هایی با معیار مشخص را شمارش کنید.

Criterion (معیار) یک عدد، تاریخ و یا نوشته است که مشخص می‌کند آیا یک سلول خاص در محدوده  شمرده ‌شود یا خیر.

دستور زبان تابع COUNTIFS عبارت است از:

COUNTIFS(range1,criterion1,range2,criterion2,…, range_n,criterion_n)

تابع COUNTIFS تعداد ردیف‌هایی را که آرگومان range1 با criterion1، range2 با criterion2 و range_n با criterion_n و غیره مطابقت می‌کند را شمارش می‌نماید؛ بنابراین تابع COUNTIFS اجازه می‌دهد که معیار موردنظر بیش از یک ستون را درگیر و یا چند شرط را بر یک ستون اعمال نمایید. سایر توابعی که به چند معیار اجازه اعمال می‌دهند در فصل 21  بنام توابعSUMIF،AVERAGEIF،SUMIFS،AVERAGEIFS،MAXIFS و MINIFS و فصل 50 بنام” تلخیص داده‌ها با توابع آماری دیتابیس” مورد بحث قرار گرفته‌اند.

کلید استفاده موفقیت‌آمیز از تابعCOUNTIF (و یا هر تابع مشابهی) در درک محدوده وسیعی از معیارها و شرایطی که اکسل می‌پذیرد ، می‌باشد. انواع معیارهایی که می‌توانید استفاده کنید در کار با تمرین‌ها و مثال‌های ارائه شده به‌خوبی توضیح داده شده‌اند. در اینجا علاوه بر مثال‌هایی از تابع COUNTIF مثال‌هایی از توابع COUNT، COUNTA و COUNTBLANK‌ نیز آورده شده است.

  • تابعCOUNT تعداد سلول‌های حاوی ارقام در محدوده را می‌شمارد.
  • تابع COUNTA تعداد سلول‌های غیر خالی درون محدوده را می‌شمارد.
  • تابعCOUNTBLANK تعداد سلول‌های خالی موجود در محدوده مورد نظر را می‌شمارد.

به‌عنوان تصویری از نحوه استفاده از این توابع، بانک داده‌ای را در نظر بگیرید (فایلRock.xlsx) که به شما اطلاعات زیر را در مورد ترانه‌های پخش شده در ایستگاه رادیویی WKRP ارائه می‌دهد:

  • نام خواننده
  • تاریخ پخش ترانه
  • مدت‌زمان هر ترانه

تصویر 1-20 به شما مجموعه‌ای از این داده‌ها را نشان می‌دهد:

توابع COUNTIF،COUNTIFS،COUNT،COUNTA و COUNTBLANK

تصویر 1-20 پایگاه‌داده ترانه‌های استفاده شده در مثال تابعCOUNTIF

 

پاسخ به سؤالات این فصل:

چندتا از ترانه های هرخواننده پخش شده است؟

برای شروع کار اولین ردیف پایگاه‌داده یعنی محدوده D6:G6 را انتخاب می‌کنیم. آنگاه با فشار دادن کلیدهای Ctrl+ Shift به‌اضافه کلید جهت پایین (Down Arrow) تمامی پایگاه‌داده را انتخاب می‌کنیم. آنگاه در گروه گزینه‌هایDefined Names در سربرگ Formulas روی گزینه Create From Selection کلیک کرده و گزینه Top Rowرا انتخاب کرده و روی دکمه Ok  کلیک می‌کنیم. با این کار محدوده D7:D957 را به نام Song Number، محدوده E7:E957 را به نام Singer، محدوده F7:F957 را به نام Date و محدوده G7:G957 را به نام Minutes نام‌گذاری می‌کنیم.

برای اینکه بدانیم هر خواننده چند ترانه خوانده است می‌بایست فرمول =COUNTIF(Singer,B5) را از سلول G5 به محدوده C6:C12 کپی کنید. این فرمول اکنون در سلول C5 تعداد سلول‌هایی موجود در محدوده خواننده را نشان می‌دهد که مقدار آنها با مقدار سلولB5   یعنی(EMINEM) مطابقت داشته باشند (114 عدد). مشابه همین را می‌توان در تصویر 2-20 در مورد نام خواننده (Cher) با 112 عدد و سایر نام خواننده‌ها مشاهده نمود. می‌توان تعداد ترانه‌های خوانده شده توسط EMINEM را با فرمول =COUNTIF(Singer,”Eminem”) با ذکر نام نیز پیدا کرد. توجه کنید که می‌بایست متونی چون Eminem را در میان دو علامت نقل‌قول قرارداد وهمچنین بهتر است بدانید این معیار نسبت به کوچک یا بزرگ نوشته شدن حروف حساس نیست.

تصویر 2-20 استفاده از تابعCOUNTIF برای مشخص نمودن تعداد ترانه‌های خوانده شده توسط هر خواننده

چند ترانه توسط امینم خوانده نشده است؟

برای حل این مسئله می‌بایست بدانید که اکسل این ترکیب” <>” را به‌عنوان” نامساوی با” تفسیر می‌کند. همان‌طور که در تصویر 3-20 می‌بینید، فرمول =COUNTIF(Singer,”<>Eminem”) که در سلول C15‌وارد شده به شما نشان می‌دهد که 837 ترانه در این پایگاه‌داده توسط امینم خوانده نشده‌اند. می‌بایست ترکیب این دو کاراکتر (<>) را به‌عنوان متن به همراه Eminem که البته آن هم متن است درون دو علامت نقل‌قول قرار دهیم. می‌توان همین نتیجه را با استفاده از فرمول =COUNTIF(Singer,”<>”&B5) نیز به دست آورد که در آن از نماد & (آمپرساند) برای ایجاد ارجاع به سلول B5 و عملگرهای <> استفاده شده است.

تصویر 3-20 می‌توانید تابعCOUNTIF را با عملگر نامساوی (<>) ترکیب کنید.

 

چند ترانه دارای طول حداقل 4 دقیقه‌ای هستند؟

در سلول C16 تعداد ترانه‌هایی که با طول زمان حداقل 4 دقیقه پخش شده‌اند را با استفاده از فرمول =COUNTIF(Minutes,”>=4″) محاسبه کرده‌ایم. می‌بایست >=4 را درون علامت نقل‌قول قرارداد چراکه ترکیب کاراکترهای بزرگ‌تر یا مساوی (>=) مثل ترکیب<> به‌عنوان یک متن در نظر گرفته می‌شود. می‌بینید که 477 ترانه دارای مدت‌زمان اجرای حداقل 4 دقیقه می‌باشند.

چند ترانه نسبت به میانگین طول کل ترانه‌های لیست دارای طول بلندتری می‌باشند؟

برای پاسخ به این سؤال ابتدا در سلول G5  میانگین طول یک ترانه را با فرمول AVERAGE(Minutes) محاسبه می‌کنیم. آنگاه در سلول C17 تعداد ترانه‌هایی را که بیشتر از میانگین محاسبه شده طول می‌کشند را با فرمول =COUNTIF(Minutes,”>”&G5) محاسبه می‌کنیم. می‌توان با استفاده از کاراکتر& به سلول دیگری از معیارهای موجود (در اینجا G5) ارجاع ایجاد کرد. می‌بینید که 477 ترانه طولی بیشتر از میانگین طول محاسبه دارند که با تعداد ترانه‌های که حداقل 4 دقیقه طول می‌کشند برابر است. علت این برابری ارقام آن است که طول هریک از ترانه‌ها را به‌عنوان یک عدد صحیح فرض کرده‌ایم. مثلاً ترانه‌ای که حداقل 3.48 دقیقه طول کشیده شده به‌عنوان ترانه‌ای که چهار دقیقه طول کشیده شده در نظر گرفته شده است.

چند ترانه از خواننده‌هایی هستند که نام خانوادگی آنها با حرف S آغاز می‌شود؟

برای پاسخ به این سؤال کاراکترهای فرانویسه (wildcard) یعنی کاراکتر ستاره (*) را در معیارها بکار می‌گیریم. کاراکتر ستاره نمایانگر هر دنباله ای از کاراکترها است بنابراین فرمول =COUNTIF(Singer,”S*”) هر ترانه‌ای که حرف اول نام خانوادگی خواننده آن با S شروع می‌شود را انتخاب می‌کند (این معیار به بزرگی یا کوچکی حروف حساس نیست). می‌بینیم که 232 ترانه توسط خوانندگانی خوانده شده که حرف اول نام خانوادگی آنها با S شروع شده. این عدد به سادگی تعداد تمام ترانه‌هایی است که توسط بروس اسپرینگستن (Bruce Springsteen) و بریتنی اسپیرز (Britney Spears) خوانده شده. (103+129=232)

چند ترانه از خواننده‌هایی هستند که نام خانوادگی آنها دقیقاً شش حرف دارد؟

در این مثال از فرانویسه علامت سؤال استفاده کرده‌ام. علامت سؤال با هر کاراکتر دیگری برابر است، بنابراین واردکردن فرمول =COUNTIF(Singer,”??????”) در سلول C19 تعداد ترانه‌های خوانده شده توسط خواننده‌هایی که نام خانوادگی آنها شش حرف دارد را محاسبه می‌کند. نتیجه رقم 243 است. دو خواننده دارای نام خانوادگی شش حرفه هستند بریتنی اسپیرز (Britney Spears) و امینم (Eminem)که هر دو باهم 243 عدد ترانه خوانده‌اند. (129+114=243)

چند تا از ترانه‌ها بعد از تاریخ 15 ژوئن سال 2005 پخش شده‌اند؟

معیاری که با تابع COUNTIF استفاده می‌کنید تاریخ را بر اساس شماره سریال تاریخ محاسبه می‌کند (رقم تاریخ بعدی بزرگ‌تر از تاریخ قبلی در نظر گرفته می‌شود) فرمول =COUNTIF(Date,”>6/15/2005″) به شما نشان می‌دهد که 98 ترانه پس از تاریخ 15 ژوئن سال 2005 پخش شده‌اند.

چند تا از ترانه‌ها پیش از سال 2009 پخش شده‌اند؟

در اینجا می‌خواهیم معیار موردنظرمان تمامی تاریخ‌های پیش از 31 دسامبر سال 2008 را انتخاب کند. در سلول C21 فرمول =COUNTIF(Date,”<=12/31/2008″) را وارد می‌کنیم. می‌توان دید که 951 ترانه (که معلوم می‌شود تمامی ترانه‌ها هستند) پیش از شروع سال 2009 پخش شده‌اند.

 طول چند تا از ترانه‌ها دقیقاً 4 دقیقه می‌باشد؟

در سلول  C22 تعداد ترانه‌هایی را که دقیقاً 4 دقیقه طول می‌کشند را با فرمول =COUNTIF(Minutes,4) محاسبه کرده‌ایم. این فرمول تعداد سلول‌های موجود در محدوده G7:G957 را که شامل عدد 4 هستند را شمارش می‌کند. همان‌طور که می‌بینید 247 ترانه دقیقاً 4 دقیقه طول می‌کشند. در سلول C23 با استفاده از همین روش می بینیم که 230 ترانه دقیقاً 5 دقیقه طول می‌کشند.

چند تا از ترانه‌های پخش شده توسط بروس اسپرینگستین خوانده شده‌اند و دقیقاً چهار دقیقه طول می‌کشند؟

می‌خواهیم تمام ردیف‌هایی که در ستون خواننده دارای ورودی Springsteen و همچنین در ستون دقیقه‌ها دارای عدد ورودی 4 است را جمع بزنیم. چراکه این سؤال دارای بیش از یک معیار است، این کاری است که تابع فوق‌العاده COUNTIFS انجام می‌دهد. می‌توان خیلی ساده در سلول C24  فرمول  =COUNTIFS(Singer,”Springsteen”,Minutes,4) را وارد می‌کنیم.

این فرمول همه ردیف‌های دارای عبارت Springsteen و دقیقه 4 را جمع می‌زند. مشخص می‌شود بروس اسپرینگستن 24 ترانه خوانده است که دقیقاً چهار دقیقه طول می‌کشند. ترانه موردعلاقه من از بروس اسپرینگستین ترانه”Thunder Road” است اما این ترانه بیش از 4 دقیقه طول می‌کشد.  توصیه می کنم  برای کار با فرمولهای حاوی تابع COUNTIFS از ابزار Function Wizard استفاده کنید . فراموش نکنید که می‌توانید نام محدوده‌ها را در فرمول خود با کلید F3 جای‌گذاری نمایید.

چند تا از ترانه‌ها توسط مادونا پخش شده که سه یا چهار دقیقه طول می‌کشند؟

از آنجا که در اینجا با معیارها یا شروط چندگانه سروکار داریم، این کار تنها از تابع COUNTIFS برمی‌آید.واردکردن‌فرمول=COUNTIFS(Singer,”Madonna”,Minutes,”<=4″,Minutes,”>=3″) در سلول C25 باعث می‌شود تمام ردیف‌هایی که در آنها نام ترانه‌ای از مادونا است که سه تا چهار دقیقه طول می‌کشند شمرده شود. اینها دقیقاً همان ردیف‌هایی است که ما می خواهیم شمرده شوند. معلوم می‌شود که مادونا 70 ترانه خوانده است که زمان آنها بین 3 تا چهار دقیقه است (ترانه موردعلاقه من ” Crazy for You! ”  است.)

چگونه تعداد سلول‌های موجود در یک محدوده که حاوی ارقام است را بشماریم؟

تابعCOUNT تعداد اعداد سلول‌های درون یک محدوده که حاوی مقادیری عددی هستند را می‌شمارد. مثلاً فرمول =COUNT(B5:C14)  در سلول C2 عدد 9 را نشان می‌دهد چراکه 9 سلول (سلول‌های C5 :C13) در محدوده B5:C14 حاوی اعداد می‌باشند. (تصویر 2-20 را ببینید)

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

تابع COUNTBLANK تعداد سلول‌های خالی در یک محدوده را می‌شمارد. مثلاً فرمول =COUNTBLANK(B5:C14) وارد شده عدد 2 را به ما می‌دهد چراکه دو سلول (B14‌و C14) در محدوده B5:C14 خالی هستند.

چگونه تعداد سلول‌های غیرخالی را در یک محدوده شمارش کنیم؟

تابع COUNTA تعداد سلول‌های غیر خالی را در یک محدوده برای ما شمارش می‌کند. مثلاً فرمول =COUNTA(B5:C14) در سلول C3 عدد 18 را به ما می‌دهد چراکه هجده سلول در محدوده B5:C14 خالی نیستند.

یادآوری‌:

در ادامه این کتاب روش‌های جایگزین برای پاسخگویی به سؤالاتی که شامل یک یا چند معیار یا شرط هستند را موردبحث و گفتگو قرار می‌دهیم (مثلاً اینکه چند تا از ترانه‌های بریتنی اسپیرز پیش از دهم ژوئن سال 2005 پخش شده‌اند)

  • توابع آماری پایگاه‌داده در فصل 50
  • فرمول‌های آرایه در فصل 91″ بنام فرمول‌ها و توابع آرایه‌ای”

 

مسئله‌های این فصل:

مسئله‌های زیر مربوط به فایل Rock.xlsx هستند.

چند ترانه پخش شده توسط افراد دیگری غیر از بریتنی اسپیرز خوانده شده‌اند؟

چند ترانه پیش‌ازتاریخ 15 ژوئن سال 2004 پخش شده‌اند؟

چند ترانه پیش از 1 ژوئن سال 2004 و 4 جولای 2006 پخش شده‌اند؟ راهنمایی: تفاضل بین دو تابع COUNTIF‌ را در نظر بگیرید.

چند ترانه توسط خواننده‌هایی که نام خانوادگی‌شان با حرف M  شروع می‌شود خوانده شده‌اند؟

چند ترانه توسط خواننده‌هایی که نامشان دارای حرف e است خوانده شده‌اند؟

فرمولی  بنویسید که همواره تعداد ترانه‌های پخش شده در روز جاری را به دست آورد. راهنمایی: از تابع TODAY() استفاده کنید.

در محدوده D4:G15 سلول‌هایی را که حاوی مقادیر عددی هستند ، را محاسبه کنید. تعداد سلول‌های خالی را محاسبه کنید. تعداد سلول‌هایی که غیر خالی را محاسبه کنید.

چند ترانه خوانده شده توسط باری مانیلو در سال 2004 پخش شده است؟

چند ترانه از مندی مور که حداقل چهار دقیقه طول می‌کشد در سال 2007 و یا پیش از آن پخش شده است؟

چند ترانه از بریتنی اسپیرز که زمان آنها دقیقاً سه دقیقه است بعد از سال 2004 پخش شده است؟

فایلی بنام NBC.xlsx حاوی اطلاعات زیر می‌باشد:

  1. ستونA و B حاوی نام هریک از تیم‌های بسکتبال NBA و شماره کد هریک از تیم‌ها می‌باشند. مثلاً شماره 1 تیم آتلانتا است.
  2. ستون C حاوی نام تیم میزبان هر بازی است.
  3. ستون D حاوی تیم مهمان هر بازی است.
  4. ستون E حاوی امتیازات کسب شده تیم میزبان است.
  5. ستون F حاوی امتیازات کسب شده تیم مهمان است.

از این داده‌ها  تعداد بازی‌های انجام شده توسط هر تیم را محاسبه کنید.

 

فایلی بنام Matchthesecond.xlsx لیستی از نام‌ها را در اختیار شما قرار می‌دهد. توجه کنید که برخی از نام‌ها بیش از یک‌بار تکرار شده‌اند. شما می‌بایست ردیفی را مشخص کنید که دومین تکرار یک نام مثلاً نام Dave در آن رخ‌داده است. کاربرگی تنظیم کنید که به شما اجازه می‌دهد نام شخصی را در آن و عدد صحیح مثبتی (مثلاً عدد n) را وارد کرده تا ردیفی را که آن نام به تعداد n بار در آن تکرار شده را به شما نشان دهد.

فایل Numbers.xlsx حاوی مجموعه‌ای از اعداد است. مشخص کنید جمعاً چند تا از این اعداد بین یک و دوازده هستند.

فایلی بنام Problem14data.xlsx حاوی اطلاعاتی درباره 2000 شرکت بزرگ در دنیا است. مشخص کنید چند عدد از این شرکت‌ها بانک و چند عدد بانک آمریکایی هستند؟

فایلی بنام Prombelm14data.xlsx حاوی اطلاعاتی درباره 200 شرکت بزرگ در سراسر دنیا است. مشخص کنید چند تا از بانک‌ها دارای درآمد فروش>=$100 Billion، >=90billion، >=$10Billion (بزرگ‌تر یا مساوی 100 میلیارد دلار، بزرگ‌تر یا مساوی 90 میلیارد، بزرگ‌تر یا مساوی 10 میلیارد دلار) و به همین ترتیب می‌باشند.

فایل ها جانبی:
دانلود فایل نمونه
اشتراک گذاری در شبکه های اجتماعی

مایکروسافت اکسل (Excel)

loader

لطفا شکبیا باشید...