سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

استفاده آرایه ای از تابع 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 ثبت میشه.

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

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

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

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

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

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

نکته:
فرمول های آرایه ای حتما با Ctrl+Shift+Enter باید ثبت بشن. جهت آشنایی بیشتر با منطق فرمول نویسی آرایه ای مقاله فرمول نویسی آرایه ای در اکسل رو مطالعه کنید.

 

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

Countif

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

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

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

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

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

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

روش اول دو شرط 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

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

133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • sara ۱۸ تیر ۱۳۹۸ / ۱:۴۰ ب٫ظ

    با سلام
    خسته نباشید
    شمارش یک ستون که دارای متنهای متفاوت می باشد و بزرگتر از یک عدد باشد و به شرطی که مربوط به یکی از ستون های دیگر باشد با چه دستوری نوشته می شود.
    از دستور Countifs نمی شود به این دلیل که این دستور اگر چند مورد خاص از متن های این ستون را بخواهیم شمارش نماییم استفاده می شود.
    از چه دستوری شرطی استفاده نمایم؟
    بطور مثال ستون مربوطه دارای کالاهای متفاوت مصرفی بوده است و ستون دیگر نیز شهرهای متفاوت را شامل شود
    به چه صورت تشخیص دهم که تعداد کالاهای مصرفی بیشتر از ده بار بطور مثل در شهر تهران چقدر بوده است؟ به همین صورت در کالاهای مصرفی در شهر های دیگر
    با تشکر

    • آواتار
      حسنا خاکزاد ۶ مرداد ۱۳۹۸ / ۱۲:۳۸ ب٫ظ

      درود بر شما
      با توجه به توضیحات countifs جواب نیاز شما رو میده!
      یک شرط میشه محصول مصرفی و یک شرط هم شهر. تعداد حساب میشه و بعد روی بیشتر از ۱۰ ها ی کار دیگه ای میکنید. فمرول نویسی یا فیلتر یا …

  • sasa1349 ۱۶ آبان ۱۳۹۷ / ۲:۲۹ ب٫ظ

    با سلام
    بسیار مفید و آموزنده بود
    ممنون از تلاش و پشتکارتان

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان