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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

روش سوم:  استفاده از Sumproduct برای شمارش شرطی در اکسل

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

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

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

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

یا

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

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

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

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

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

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

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

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

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

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

ثبت ایمیل جهت دانلود فایل

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

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...