آخرین مطالب ارسالی
Last Posts
آهنگ های ویژه
Best Posts
کاشت مو
بهترین سایت کاشت مو با کمترین قیمت
کلینیک زیبایی
شمارش شرطی در اکسل با استفاده فرمول آرایه ای تابع Countif

استفاده آرایه ای از تابع Countifs

۲ رأی، میانگین: ۵

همونطور که در مقالات قبلی و در توضیح توابع countif/s گفته شد، این توابع بصورت شرطی و با منطق “و” شمارش شرطی انجام میدن. یعنی اگر چند شرط داشته باشیم، اشتراک آنها بصورت خروجی نمایش داده خواهد شد. (در مثال های مقالات قبل کاملا توضیح داده شده).

در مقاله قبلی، نحوه استفاده از تابع countif رو با منطق “یا” توضیح دادیم و مثال های متنوعی ارائه کردیم. حالا در این آموزش میخواهیم تابع countifs رو با منطق “یا” ترکیب کنیم. بعبارتی ترکیب منطق “و” و “یا” رو با هم داشته باشیم و مثال هایی رو در این زمینه تشریح کنیم.

شمارش سلول ها، با یک شرط OR و چند شرط AND

مثلا فرض کنید داده هایی مشابه شکل ۱ داریم. میخواهیم تعداد محموله ۱ و محموله ۲ که تحویل شده اند را بشماریم. در واقع محموله ۱ که تحویل شده، یا محموله ۲ که تحویل شده. برای حل این مسئله، از روش های زیر استفاده میکنیم.

روش اول: مجموع دو تابع Countifs

COUNTIFs (rangeشرط ۱, rangeشرط۳) + COUNTIFs(rangeشرط ۲, rangeشرط۳)

در این روش هم منطق “و” داریم هم منطق “یا”. منطق “و” از این جهت که میگیم محموله ۱ که تحویل شده باشه. منطق “یا” از این جهت که محموله ۱ یا محموله ۲ باشه.

=COUNTIFS (A2:A11 ; “محموله ۱” ; C2:C11 ; “تحویل شد”) + COUNTIFS (A2:A11 ; “محموله ۲” ; C2:C11 ; “تحویل شد”)

شمارش شرطی در اکسل- با منطق "و" و "یا"

شکل ۱- شمارش شرطی در اکسل– با منطق “و” و “یا”

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

روش دوم: استفاده از Countifs بصورت ثابت آرایه ای

روش راحت تری که برای محاسبه این نوع مسائل در نظر گرفته میشه، استفاده از فرمول نویسی آرایه ای و در نظر گرفتن این شرط ها بصورت آرایه ای هست. در این نوع فرمول نویسی، شرط ها داخل آکولاد { } قرار گرفته و فرمول با Ctrl+Shift+Enter ثبت میشه.

حتما بخوانید:  جستجوی بازه ای با فرمول Vlookup در اکسل

به نمونه زیر دقت کنید:

=SUM ( COUNTIFS (A2:A11 ; {“محموله ۱″,”محموله ۲”} ; C2:C11 ; {“تحویل شد”} ))

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

=SUM ( COUNTIFS (A2:A11 ; F1:G1 ; C2:C11 ; F2))

شمارش شرطی در اکسل- با منطق "و" و "یا" بصورت آرایه ای

شکل ۲- شمارش شرطی در اکسل– با منطق “و” و “یا” بصورت آرایه ای

 

این فرمول میاد شرط ها رو یکی یکی در نظر میگیره. یعنی یکبار محموله ۱ رو با تحویل شد شمارش میکنه و بار دوم محموله دوم رو با تحویل شد، شمارش میکنه. نتیجه این دو حالت رو ذخیره میکنه و در نهایت روی نتایج ذخیره شده، جمع انجام میده. به تصویر زیر دقت کنید:

Countif

حالا با همین منطق میتونیم شرط های بیشتری رو هم اعمال کنیم. مثلا محموله ۱ و ۲ که تحویل شده و ارزش آن بیش از ۵۰۰۰ هست. به فرمول زیر دقت کنید:

شمارش شرطی چندگانه در اکسل- با منطق "و" و "یا" بصورت آرایه ای

شکل ۳- شمارش شرطی چندگانه در اکسل- با منطق “و” و “یا” بصورت آرایه ای

منطق این فرمول هم مشابه بالاست. یعنی یک بار محموله ۱، تحویل شده که ارزش بیش از ۵۰۰۰ داره شمرده میشه. یکبار هم محموله ۲ که تحویل شده و ارزش بیش از ۵۰۰۰ داره. در نهایت هم نتیجه این دو شمارش رو با هم جمع میکنه.

شمارش سلول ها، با چند شرط OR و چند شرط AND

در مثال های قبلی اینکه چطور یک شرط Or رو با چند شرط AND ترکیب کنیم و شمارش انجام بدیم رو تشریح کردیم. حالا میخوایم بیش از یک شرط   OR رو بررسی کنیم. برای این کار از دو روش میتونیم استفاده کنیم. حالت آرایه ای تابع Countifs که مشابه مثال قبلی هست (ولی با این محدودیت که دو جموعه شرط OR بیشتر قابل قبول نخواهد بود). یک روش  هم ترکیب تابع sumproduct, Match  و Isnumber . که این روش هیچ محدودیتی به لحاظ تعدا دشرط Or نداره. شرط AND هم که کلا برای هیچکدوم محدودیت نداره (به جز محدودیت ۲۵۵ آرگومان)، چرا که اصل منطق تابع countifs منطق AND “و” هست.

حتما بخوانید:  محاسبه فراوانی داده ها با تابع Frequency

روش اول دو شرط OR

فرض کنید میخواهیم تعداد محموله های ۱ و ۲ که یا ارسال شدن و یا تحویل شدن رو بشمریم. در واقع دو سری شرط داریم، یکی “محموله ۱” یا “محموله ۲” بودن. یکی هم “ارسال شد” یا “تحویل شد” بودن.

برای این کار باید مشابه مثال های بالا، شرط ها رو داخل تابع Countifs تخصیص بدیم. با یک تفاوت کوچک ولی خیلی مهم. اون هم این که مجموعه دوم شرط ها رو باید با آرایه عمودی وارد فرمول کنیم. آرایه عمودی در مفهوم آرایه ها با ; نمایش داده میشه. (این نکته فارغ از بحث جدا کننده فرمول هست). در واقع , آرایه افقی و ; آرایه عمودی رو میسازه.

به شکل ۴ دقت کنید. آرایه نوشته شده برای مجموعه دوم شرط ها، با ; از هم جدا شدن.

نحوه نمایش آرایه افقی و عمودی در آرایه ها

شکل ۴- نحوه نمایش آرایه افقی و عمودی در آرایه ها

در واقع همین فرمول رو بخوایم طور دیگه بنویسیم و شرط ها رو از سلول بگیریم. باید مجموعه دوم شرط (با منطق Or) رو عمودی وارد سلول کنیم. مطابق شکل ۵٫

شمارش شرطی چندگانه در اکسل- دو سری شرط با منطق OR

شکل ۵- شمارش شرطی چندگانه در اکسل- دو سری شرط با منطق OR

منطق محاسبه رو تا الان حتما حدس زدید. مشابه مثال های بالا، ۴ بار محاسبه انجام میشه:

محموله ۱ که تحویل شده (۱)

محموله ۱ که ارسال شده (۱)

محموله ۲ که تحویل شده (۱)

محموله ۲  که ارسال شده (۰)

نتیجه این ۴ محاسبه در نهایت با هم جمع میشه و به عنوان خروجی فرمول نمایش داده میشه. به تصویر زیر دقت کنید.

Countif Vertical array

چون اکسل یک نرم افزار دو بعدی است نمیتونیم فرمول های سه بعدی و چهار بعدی داشته باشیم و همونطور که در فرمول بالا مشاهده کردید این فرمول قادر به بررسی دو سری شرط در قالب یک ماتریس دو بعدی هست.

حالا برای محاسبه بیش از ۲ سری شرط با منطق OR روش دوم رو ببینید.

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

روش دوم بیش از دو شرط OR

حالا میخوایم بیش از دو سری شرط با منطق OR رو محاسبه کنیم. فرض کنید میخواهیم تعداد محموله های ۱ و ۲ از گروه A و C که در وضعیت ارسال شد و تحویل شد هستند رو بشمریم.

=SUMPRODUCT( ISNUMBER(MATCH(A2:A11;F1:G1;0))* ISNUMBER(MATCH(C2:C11;F2:G2;0))* ISNUMBER(MATCH(B2:B11;F3:G3;0)))

شمارش شرطی چندگانه در اکسل- بیش از دو سری شرط با منطق ORشکل ۶- شمارش شرطی چندگانه در اکسل- بیش از دو سری شرط با منطق OR

تشریح فرمول:

یکی از شرط ها رو تشریح میکنیم، مابقی منطق مشابه دارند. بررسی شرط محموله ۱ و ۲:

ابتدا فرمول Match بصورت آرایه ای بررسی میکنه که هر کدوم از داده های موجود در جدول، بین شرط های ما هست یا خیر. اگر باشه، خروجی عدد و اگر نباشه خروجی #N/A خواهد بود.

{۱;۲;#N/A;#N/A;2;#N/A;1;2;#N/A;#N/A}

بعد با استفاده از تابع IsNumber این داده ها به True و False تبدیل میشن.

{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}

به همین ترتیب برای دو سری شرط بعدی هم محاسبه انجام میشه و در نهایت سه آرایه از True و False داریم.

=SUMPRODUCT( {TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}* {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}* {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE})

نتیجه حاصلضرب این سه آرایه بصورت زیر خواهد بود که جمع آنها نتیجه مورد نظر ما خواهد بود.

=SUMPRODUCT{1;0;0;0;0;0;1;0;0;0}

بعبارت دیگر ابتدا محموله ۱ که تحویل شده و گروه A یا C هست شمرده میشه. بعد محموله ۱ که ارسال شده و گروه A یا C هست و همینطور الی آخر.

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

حالا با همین منطق میتونید مسائل مربوط به Sumif/S رو حل کنید؟

 

سوال: تعداد محموله های ۱و ۲ که تحویل و ارسال شده و ارزش آن بین ۳۰۰۰ تا ۷۰۰۰ هست رو محاسبه کنید؟

جواب رو در ادامه در قالب کامنت ثبت کنید

دانلود فایل آموزش شمارش شرطی با تابع Countifs

برای دانلود این فایل روی دکمه زیر کلیک کنید:

شمارش شرطی در اکسل با استفاده فرمول آرایه ای تابع Countif

با عضویت در سایت به صورت مستقیم دانلود کنید

شمارش شرطی در اکسل با استفاده فرمول آرایه ای تابع Countif