آموزش COUNTIF و AND و OR در اکسل

در بیشتر مثال هایی که در ابتدای این بخش ارایه شده اند از توابع یا فرمول هایی که شمارش های شرطی انجام می دهند استفاده شده است. فرمول های پیشرفته ی شمارشی که در این بخش بررسی می شوند، مثال های پیچیده تری برای شمارش سلول های کاربرگ بر اساس ملاک های گوناگون هستند.

شمارش سلول با استفاده از تابع COUNTIF

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

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

Criteria: ملاک منطقی ای که مشخص می کند آیا یک سلول خاص در عملیات شمارش به حساب بیاید یا خیر.

جدول زیر چندین مثال از فرمول هایی که از تابع COUNTIF استفاده می کنند را نشان می دهد. این فرمول ها همگی روی دامنه ای به نام Data عمل می کنند. همانطور که مشاهده می کنید آرگومان Criteria کاملا انعطاف پذیر است و می توانید برای آن از مقادیر ثابت، عبارات، تابع، ارجاع های سلولی و حتی کارکتر ها نیز استفاده کنید.

تعداد سلول هایی که حاوی عدد 12 هستند را بر می گرداند.

= COUNTIF(Data,12)

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

= COUNTIF(Data”<0”)

تعداد سلول هایی که مساوی صفر نیستند را بر می گرداند.

= COUNTIF(Data,”<>0”)

تعداد سلول هایی که حاوی مقدار عددی بزرگ تر از 5 هستند را بر می گرداند.

= COUNTIF(Data,”>5”)

تعداد سلول هایی که محتویات آنها مساوی با محتویات سلول A1 هستند را بر می گرداند.

= COUNTIF(Data,A1)

تعداد سلول هایی که محتویات آنها بزرگ تر از مقدار سلول A1 هستند را بر می گرداند.

= COUNTIF(Data,”>”&A1)

تعداد سلول هایی که حاوی مقادیر متنی هستند را بر می گرداند.

= COUNTIF(Data,”*”)

تعداد سلول هایی که دقیقا حاوی 3 کارکتر هستند را بر می گرداند.

= COUNTIF(Data,”???”)

تعداد سلول هایی که فقط حاوی کلمه budget  هستند بر می گرداند

= COUNTIF(Data,” budget”)

تعداد سلول هایی کهدر جایی از متن آنها، کلمه budget قرار دارد را بر می گرداند.

= COUNTIF(Data,”*budget*”)

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

= COUNTIF(Data,”A*”)

تعداد سلول هایی که حاوی تاریخ فعلی هستند را بر می گرداند.

= COUNTIF(Date,TODAY())

تعداد سلول هایی که حاوی مقدار عددی بزرگتر از مقدار میانگین هستند را بر می گرداند.

= COUNTIF(Data,”>”&AVERAGE

(Date))

تعداد سلول هایی که 3 واحد بیش از انحراف استاندارد نسبت به مقدار میانگین هستند را بر می گرداند.

= COUNTIF(Data,”>”&AVERAGE

(Date)+STDEV(Data)*3)

تعداد سلول هایی که حاوی مقادیر عددی 3 یا 3- هستند را بر می گرداند.

= COUNTIF(Data,3)+ COUNTIF(Data,-3)

تعداد سلول هایی که حاوی عبارت منطقی TRUE هستند را بر می گرداند.

= COUNTIF(Data,TRUE)

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

= COUNTIF(Data,TRUE)+

COUNTIF(Data,FALSE)

تعداد سلول هایی که حاوی مقدار خطای #N/A هستند را بر می گرداند.

= COUNTIF(Data,”#N/A”)

 

شمارش سلول بر مبنای چند ملاک

در بسیاری از موارد لازم است تا تنها در صورتی که سلول با دو یا پند ملاک تطبیق دارند در عملیات شمارش به حساب در آیند. این ملاک می توانند بر مبنای سلول های شمارش شده یا بر مبنای دامنه ی سلول های مرتبط باشند.

ملاک And

ملاک And  در صورتی که تمام شروط مشخص شده تحقق پیدا کند سلول ها در عملیات شمارش به حساب می آورد. یک مثال متداول از این قضیه، فرمولی است که تعداد مقادیری که در یک دامنه‌ی عددی مشخص قرار دارند را می شمارد. مثلا ممکن است بخواهید سلول هایی که حاوی مقادیر بزرگ تر از 100 و کمتر از 200 هستند را شمارش کنید. تابع COUNTIFS می توانید این کار را انجام دهد:

=COUNTIFS(Amount,”>100”,Amount,”<=200”)

دقت کنید اگر داده ها در جدول قرار داشته باشند، می توانید از ارجاع به جدول در فرمول استفاده کنید. برای مثال اگر جدولی به نام Table داشته باشید می توانید فرمول بالا را به صورت زیر بازنویسی کنید:

=COUNTIFS(Table1[Amount],”>100”, Table1[Amount],”<=200”)

تابع COUNTIFS، هر تعداد آرگومان دوتایی را قبول می کند. بخش اول جفت، دامنه ای است که قرار است شمارش شود( در این مثال، دامنه ای به نام Amount) و بخش دوم، ملاک مورد نظر است. مثال پیش شامل دو مجموعه آرگومان دوتایی بوده و تعدد سلول هایی که در آنها Amount بزرگ تر از 100 و کوچیک تر یا مساوی 200 است را بر می گرداند.

پیش از انتشار نسخه اکسل 2007 باید فرمولی مثل فرمول زیر برای انجام عملیات فوق استفاده می کردید:

=COUNTIFS(Amount,”>100”)- COUNTIFS(Amount,”>200”)

این فرمول، تعداد مقادیری که بزرگ تر از 100 بودند  را شمارش و سپس تعداد مقادیری که بزرگتر از مساوی 200 بودند را از آن مقدار کسر می کرد. نتیجه ی حاصل، تعداد سلول هایی بودند که شامل مقادیر بزرگتر از 100 و کوچیکتر یا مساوی 200 بودند. با توجه به اینکه هدف این عملیات، شمارش مقادیری است که کمتر یا مساوی 200 هستند، اما به دلیل حضور شرط کوچیکتر از 200 در آن، این فرمول کمی گیج کننده است. تکنیک دیگر برای این عملیات، استفاده از فرمول های آرایه ای مانند فرمول زیر بوده که ساخت آنها به نسبت دیگر فرمول ها ساده تر است:

{=SUM((Amount>100)*(Amount<=200))}

در هنگام درج فرمول های آرایه ای باید کلید ها ترکیبی Ctrl+Shift+Enter استفاده کنید اما نیازی به درج کروه ها به وسیله ی کاربر نیست چون اکسل آنها را در محا های مناسب قرار می دهد.

گاهی اوقات، ملاک های شمارش بر مبنای سلول هایی که شمارش می شوند هستند. برای مثال ممکن است بخواهید تعداد فروش هایی که ملاک های  زیر تطبیق دارند را شمارش کنید:

ماه آنها ژانویه باشد.

نماینده فروش کتاب باشد

مقدار فروش نیز بیش تر از 1000 باشد

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

=COUNTIFS(Month,”January”,SalesRep,”Brooks”,Amount,”>1000”)

فرمول دیگری که تمام نسخه های اکسل پاسخ می دهد، از تابع SUMPRODUCT استفاده می کند. فرمول زیر نتیجه ای مشابه با فرمول پیش را بر می گرداند:

=SUMPRODUCT((Month=”January”)*(SalesRep=”Brooks”)*(Amount>1000))

روش دیگر انجام این شماره، استفاده از فرمول آرایه ای است:

{=SUM ((Month=”January”)*(SalesRep=”Brooks”)*(Amount>1000))}

ملاک OR

برای شمارش سلول ها با استفاده از ملاک Or گاهی اوقات می توانید از چندین تابع COUNTIF استفاده کنید. بای مثال، فرمول زیر تعداد فروش انجام شده در ماه ژانویه یا فوریه را مشخص می کند:

=COUNTIFS(Month,”January”)+ COUNTIFS(Month,”February”)

همچنین می توانید از تابع COUNTIFدر یک فرمول آرایه ای استفاده کنید. برای مثال، فرمول آرایه ای زیر نتیجه ای مشابه فرمول پیش را بر می گراند:

{=SUM(COUNTIFS(Month,{”January,”February”}))}

اما اگر مبنای ملاکOr بر مبنای سلول هایی که قرار است شمارش شوند، باشد تابع COUNTIFS پاسخ نخواهد داد. برای مثال ممکن است بخواهید تعداد فروش هایی که ملاک های زیر تطبیق دارند را شمارش کنید:

ماه آنها ژانویه باشد.

نماینده فروش کتاب باشد

مقدار فروش نیز بیش تر از 1000 باشد

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

{=SUM (IF(Month=”January”)+(SalesRep=”Brooks”)+(Amount>1000),1))}

ترکیب ملا های And وOr

در بعضی موارد ممکن است در حین عملیات شمارش، مجبور شوید ملاک های را با یکدیگر ترکیب کنید. برای مثال ممکن است بخواهید فروش هایی را شمارش کنید که ملاک هایی زیر تطبیق داشته باشند:

ماه فروش ژانویه باشد.

نماینده فروش، Books یا Cook باشند.

فرمول آرایه ای زیر، فروش هایی که با ملاک بالا تطبیق دارند را شمارش می کند:

{=SUM ((Month=”January”)*IF(SalesRep=”Brooks+( SalesRep=”Cook”),1))}


نویسنده : --

تگ ها : آموزش اکسل
ثبت نظر