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

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

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

شمارش شرطی در اکسل

همونطور که میدونیم تابع Countif در اکسل فقط میتونه یک شرط رو بررسی کنه و تعداد رو حساب کنه. تابع Countifs هم میتونه چند شرط رو بررسی کنه که منطق این بررسی “و” هست. اما اگر ما بخوایم با منطق “یا” شمارش شرطی در اکسل انجام بدیم باید چکار کنیم. یعنی زمانی که چند شرط داریم، هر کدومش که برقرار باشه در محاسباتمون باید حساب بشه. این منطق “یا” هست. در واقع میگیم، تعداد داده ها رو طوری بشمار که یا شرط اول، یا شرط دوم. یا شرط سوم برقرار باشه.

در این مقاله چند روش برای محاسبات مربوط به شرط های با منطق “یا” می پردازیم:

COUNTIF(rangeشرط ۱) + COUNTIF(rangeشرط ۲)

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

در واقع اگر با Countifs بنویسیم، داریم بهش میگیم در ستون محموله، سلولی که هم محموله ۱ هست و هم محموله ۲ رو بشمر! که خب این اصلا امکان پذیر نیست. پس برای حل این مسئله میتونیم دوتا Countif رو با هم جمع کنیم.

= COUNTIF(A:A, “محموله ۱”) + COUNTIF(A:A, “محموله ۳”)

حالا که داریم تمرین میکنیم اصول فرمول نویسی رو رعایت کنیم، بهتره که بجای انتخاب کل ستون، محدوده مشخص رو برای فرمول انتخاب کنیم تا فایل دچار کندی نشه. همچنین برای پویایی بیشتر و تغییر راحت شرط ها، بجای اینکه مستقیم داخل فرمول شرط ها رو بنویسیم، بهتره که این شرط ها رو از سلول بگیریم.(مطابق شکل ۱)

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

= COUNTIF (A2:A11 ; E1) + COUNTIF (A2:A11 ; F1)

محاسبه شرط های چندگانه با منطق OR "یا"

شکل ۱- شمارش شرطی در اکسل – محاسبه شرط های چندگانه با منطق OR “یا”

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

SUM(COUNTIF(range, {شرط ۱شرط ۲شرط ۳, …}))

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

برای استفاده از این روش باید با منطق فرمول نویسی آرایه ای آشنا باشیم. ثابت آرایه ای در اکسل به اینصورت نمایش داده میشه که آیتم ها داخل دابل کوتیشن ” ” و با کاما , از هم تفکیک میشن و همه در یک آکولاد نگه داشته میشن.

{“محموله ۱″,”محموله ۲″,”محموله ۳”}

حالا باید ثابت نوشته شده رو به عنوان شرط در تابع Countif وارد کنیم و Ctrl+Shift+Enter بزنیم. بصورت زیر:

=COUNTIF( A2:A14 ; {“محموله ۱″,”محموله ۲″,”محموله ۳”} )

در مرحله آخر حتما باید این فرمول رو داخل یک تابع Sum بذاریم. چرا که خروجی Countif سه تا عدد جدا گانه برای محموله ۱، محموله ۲ و محموله ۳ هست. این اعداد باید در انتها با هم جمع زده بشن.

=SUM ( COUNTIF ( A2:A14 ; {“محموله ۱″,”محموله ۲″,”محموله ۳”} ))

محاسبه تعداد با منطق "یا" با استفاده از تابع Countif (آرایه ای)

شکل ۲- شمارش شرطی در اکسل  – محاسبه تعداد با منطق “یا” با استفاده از تابع Countif (آرایه ای)

 به تصویر زیر دقت کنید. عدد ۵، ۳ و ۳ تعداد محموله ۱، محموله ۲ و محموله ۳ هست که وقتی داخل تابع Sum قرار میگیره، خروجی نهایی یعنی تعداد کل محموله ۱، محموله ۲ و محموله ۳ خواهد بود.

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

نحوه دیباگ فرمول

جهت مشاهده نحوه دیباگ فرمول، لینک مربوط به مقاله اصول فرمول نویسی (دیباگ کردن فرمول) رو مطالعه کنید.

 

حالا اگر بخوایم فرمول رو طوری بنویسیم که شروط رو از سلول بگیریم (داخل فرمول مستقیم ننویسیم) باید مطابق شکل ۳ عمل کنیم. در واقع شرط ها رو در سلول می نویسیم و در قسمت Criteria، محدوده شروط رو انتخاب کرده و در نهایت هم Ctrl+Shift+Enter رو میزنیم.

=SUM ( COUNTIF ( A2:A14 ; E1:G1 ) )

محاسبه تعداد با منطق "یا" با استفاده از تابع Countif (آرایه ای)

شکل ۳- شمارش شرطی در اکسل – محاسبه تعداد با منطق “یا” با استفاده از تابع Countif (آرایه ای)

روش سوم:  استفاده از Sumproduct

SUMPRODUCT(1*(range ={شرط ۱, شرط۲شرط۳, …}))

یک راه دیگه برای محاسبه تعداد با استفاده از منطق OR “یا”، استفاده از تابع Sumproduct هست.

SUMPRODUCT((range=شرط ۱) + (range=شرط ۲) + …)

برای اینکه این ساختار رو بهتر درک کنیم، ساختار بالا رو بصورت زیر نمایش میدیم:

در این روش، هر سلول از محدوده با شرط مورد نظر مقایسه میشه. اگر با شرط برابر بود، True و اگر با شرط برابر نبود، False نمایش داده میشه. در مرحله اول، نتیجه مقایسه محدوده با شروط مورد نظر، عبارات منطقی True/False خواهد بود. در مرحله بعد، عدد ۱ در این عبارات ضرب شده و به مقادیر صفر و یک تبدیل میشه. در واقع ۱*false=0 و ۱*True=1 خواهد شد. در مرحله آخر مقادیر ۱ با هم جمع زده میشه و به عنوان خروجی فرمول مورد نظر نمایش داده میشه.در واقع تعداد اعداد ۱ نشون دهنده تعداد سلولهایی هست که شروط مورد نظر در اونها برقرار بوده. مثال بالا رو با این روش حل میکنیم:

حتما بخوانید:  Skip Blank | تکنیکی کاربردی در Paste special

=SUMPRODUCT ( 1 * ( A2:A14 = {“محموله ۱″,”محموله ۲″,”محموله ۳”} ) )

یا

=SUMPRODUCT ( ( A2:A14=”محموله ۱″ ) + ( A2:A14=”محموله ۲″ ) + ( A2:A14=”محموله ۳″) )

محاسبه تعداد، با منطق "یا" با استفاده از Sumproduct

شکل ۴- شمارش شرطی در اکسل – محاسبه تعداد، با منطق “یا” با استفاده از Sumproduct

نحوه عملکرد این فرمول رو در تصویر زیر مشاهده می کنید:

با استفاده از Sumproduct

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

محاسبه تعداد، با منطق "یا" با استفاده از Sumproduct

شکل ۵- محاسبه تعداد، با منطق “یا” با استفاده از Sumproduct

توجه داشته باشید که تابع Sumproduct از تابع Countif کندتر هست و در مقیاس بزرگ بهتره که کمتر از این تابع استفاده بشه.

در این مقاله نحوه شمارش با منطق “یا” رو دیدیم. چون منطق پیش فرض تابع Countif “و” هست، خیلی مهمه که به این روش ها مسلط باشیم تا بتونیم محاسبات دلخواه خودمون رو انجام بدیم. همچنین از همین منطق میتونیم برای تابع sumif هم استفاده کنیم و براحتی این منطق رو روی داده ها اعمال کنیم. حالا اگر بخوایم مطابق شکل ۶، بخوایم تعداد محموله ۱ و محموله ۲ رو که تحویل داده شده است رو بشمریم، چطور باید این منطق ترکیبی “و” و “یا” رو اعمال کنیم؟ در مقاله بعدی به تشریح این روش خواهیم پرداخت.

بررسی شرط "و" و "یا" بصورت همزمان

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

 ارسال در حدود 2 ماه قبل  ادامه مطلب »