توابع منطقی در اکسل | تابع XOR
توابع شرطی در اکسل (تابع And ، تابع Or و تابع XOR)
مسائل شرطی و مقایسه ای در اکسل، یکی از پر تکرارترین و مهم ترین مسائلی هست که به شیوه های مختلف و در سطوح مختلف، مورد استفاده قرار میگیره. از فرمول نویسی های ساده و روزمره تا پیچیده ترین حالت ها و در نرم افزارهای حرفه ای، مسائل منطقی و مقایسه ای جایگاه ویژه ای دارند و یکی از اساسی ترین پایه های اکسل حرفه ای، توابع منطقی مثل تابع And به شمار میرن.
در مقالات قبلی راجع به عملگرهای منطقی در اکسل صحبت کردیم و نحوه مقایسه داده ها با استفاده از عملگرها رو شرح دادیم. در این مقاله میخواهیم نحوه استفاده از این عملگرها رو با استفاده از توابع منطقی، توسعه بدیم و محاسبات پیشرفته تری رو انجام بدیم.
مروری بر توابع منطقی
مایکروسافت اکسل چهار تابع منطقی رو معرفی کرده که بتونیم روی داده ها کار کنیم. از این توابع وقتی استفاده میکنیم که بخوایم بیش از یک شرط رو بررسی کنیم. مشابه عملگرهای منطقی خروجی این توابع و بسته به منطق هر یک (بعد از بررسی چند شرط)، True/False خواهد بود. در جدول زیر بصورت خلاصه منطق هر یک از این توابع چهارگانه رو توضیح میدهیم و در ادامه با مثال های متنوع به تشریح بیشتر این توابع می پردازیم.
| تابع | توضیح تابع | مثال | شرح مثال |
| AND | خروجی تابع True خواهد بود اگر همه اجزای تابع (شرطها) برقرار (True) باشند | =and (a2>=10,b2<5) | اگر محتوای سلول a2 بزرگتر مساوی 10 و محتوای سلول b2 کوچکتر از 5 باشه، خروجی فرمول true خواهد بود |
| OR | خروجی تابع True خواهد بود اگر حداقل یکی از اجزای تابع (شرطها) برقرار (True) باشد | =or (a2>=10,b2<5) | اگر یا محتوای سلول a2 بزرگتر مساوی 10 و یا محتوای سلول b2 کوچکتر از 5 باشه، خروجی فرمول true خواهد بود |
| XOR | خروجی تابع True خواهد بود اگر خروجی یکی از شرط ها با سایر شرط ها متفاوت باشه. یعنی یکیش true باشه، بقیه باید false باشن. | =xor (a2>=10,b2<5) | اگر محتوای سلول a2 بزرگتر مساوی 10 باشه و محتوای سلول b2 کوچکتر از 5 نباشه و یا برعکس، خروجی فرمول true خواهد بود. اگر هر دو برقرار باشند و یا هر دو برقرار نباشند، خروجی false خواهد بود |
| NOT | خروجی این تابع برعکس خروجی شرط داخل فرمول است. یعنی اگر خروجی شرط، true باشه، خروجی فرمول False هست و برعکس | =not (a2>=10) | نتیجه بر عکس خروجی شرط خواهد بود. یعنی اگر محتوای سلول a2 بزرگتر از 10 باشه، خروجی فرمول false خواهد بود و برعکس |
این توابع چهارگانه، با ترکیب با سایر توابع منطقی مثل if و iferror و … در حل مسائل بسیار پرکاربرد هستند.
توابع منطقی اکسل-اصول و قواعد
- در اجزای توابع منطقی، میشه آدرس سلول، داده های متن یو عددی، داده های صفر و یک، عملگرهای مقایسه ای و سایر توابع رو استفاده کرد. در هر صورت، این آرگومان ها باید خروجی true/false داشته باشند. بعبارتی در آرگومانهای شرطی توابع منطقی، چیزهایی باید قرار بگیرن که خروجی true/false داشته باشند.
- اگر یکی از اجزای توابع منطقی سلول خالی داشته باشند، اتفاقی نمیفته و این سلول های خالی در نظر گرفته نمیشن. ولی اگر همه آرگومان ها سلول خالی باشن، خروجی فرمول خطای #Value! خواهد بود.
- اگر اجزای یک تابع منطقی عدد باشن، صفر، false خوهد بود و اعداد غیرصفر True. مثلا اگر محدوده a1:a5 محتوی عدد باشن، خروجی این فرمول =AND(A1:A5) برابر خواهد بود با true ، اگر هیچ کدوم از سلول های این محدوده صفر نباشند و اگر یک صفر داشته باشن falseخواهد بود.
- اگر خروجی هیچ یک از اجزای توابع منطقی، true/false نباشند، این فرمول با خطای #Value! مواجه خواهد شد.
- در ورژن های 2007 به بالا، میشه تا 255 آرگومان داخل توابع منطقی نوشت. البته با رعایت شرط حداکثر کاراکتر در یک سلول یعنی 8192 کاراکتر. در ورژن 2003 این تعداد کمتر هست، یعنی 30 آرگومان و با سقف 1024 کاراکتر در یک سلول.
استفاده از تابع AND در اکسل برای مقایسه بازه ای
همونطور که قبلا هم توضیح داده شده، تابع AND از توابع بسیار پرکاربرد منطقی هست و زمانی مورد استفاده قرار می گیره که بخوایم چندین شرط رو با هم بررسی کنیم که به ازای برقرار بودن همه شرط ها، TRUE خروجی تابع خواهد بود. ساختار تابع AND رو در مقاله توابع منطقی توضیح دادیم. در این مقاله میخوایم نمونه ها و مثال های بیشتری رو در ترکیب با سایر توابع بررسی کنیم.
خود تابع AND به تنهایی ممکنه خیلی جذاب نباشه ولی وقتی با سایر توابع، مخصوصا با if ترکیب میشه نتایج خیلی کاربردی در بر خواهد داشت. مثلا به مثال زیر توجه کنید:
میخواهیم بررسی کنیم و ببینیم که اگر اعداد مورد نظر در یک ستون در یک بازه خاص قرار میگیرند، خروجی “بله” و در غیر اینصورت خروجی “خیر” نمایش داده بشه.
مثلا داده مورد نظر در ستون A و ابتدای بازه در ستون B و انتهای بازه در ستون C قرار داره. میخوایم فرمولی بنویسیم که هر عدد از ستون A در ستون B تا C مربوط به خودش قرار گرفت، خروجی “بله” نمایش داده بشه. همونطور که میدونیم تابعی به نام between وجود نداره و باید از دو شرط بصورت همزمان استفاده کنیم. یعنی عدد مورد نظر هم بزرگتر مساوی ابتدای بازه و هم کوچکتر مساوی انتهای بازه باشه.
=IF(AND(A2>=B2,A2<=C2),”بله”, “خیر”)
شکل 1- بررسی عدد بین بازه
همونطور که میبینید، تاریخ هم به عنوان عدد بررسی شده که در بازه مورد نظر هست یا نه. دقت داشته باشید که جنس تاریخ میلادی هست و ظاهرش شمسی. برای همین قابلیت مقایسه و محاسبه داره. اگر با این مبحث آشنا نیستید، حتما مقاله مربوط به تاریخ شمسی در اکسل و تاریخ در اکسل رو مطالعه کنید.
توابع منطقی تو در تو
توابع منطقی ( تابع AND و تابع OR ) رو میتونیم بصورت ترکیبی هم بکار ببریم و شروط بیشتری رو با منطق های مختلف استفاده کنیم. به الگوهای زیر دقت کنید. الگوهای زیر نمونه ای از ترکیب این تواع هستن که میتونه حالت های متنوع تری هم داشته باشه.
=AND(OR(Cond1, Cond2,…), Cond3)
=AND(OR(Cond1, Cond2,…), OR(Cond3, Cond4,…)
=OR(AND(Cond1, Cond2,…), Cond3)
=OR(AND(Cond1,Cond2,…), AND(Cond3,Cond4,…))
مثلا فرض کنید در جدول زیر، میخوایم میوه های موز و پرتقال که فروش رفتن رو رنگی کنیم. میدونیم که اگر موجودی انبار با فروش رفته ها برابر باشه، یعنی کل محصول فروش رفته. به فرمول زیر دقت کنید:
=OR(AND($A2=”موز”,$B2=$C2),AND($A2=”پرتقال”,$B2=$C2))
این فرمول دو قسمت داره که هر دو منطق تابع AND دارند. هر کدوم جداگانه بررسی میشن و خروجی آنها میشه آرگومانهای تابع OR. در هایت هم منطق تابع OR روی داده ها اعمال میشه و یک خروجی True/False نمایش داده میشه. اگر True باشه فرمت مورد نظر اعمال میشه و اگر نباشه، نه.
شکل 2- فرمت دهی شرطی و ترکیب توابع منطقی
نتیجه بررسی فرمول رو روی اولین محصول (سیب) در ویدئو زیر می بینید:
استفاده از XOR در اکسل
از ورژن 2013 به بعد اکسل تابع XOR رو اضافه کرد. درک مفهوم این تابع برای کسانی که با زبان های برنامه نویسی آشنا هستند راحت تره چون معادل هست با اصطلاح Exclusive Or. منطق این تابع به این صورت هست که برای true بودن فرمول، خروجی آرگومانها نباید یکسان باشه، یعنی اگه همه false یا همه true باشن، خروجی تابع False خواهد بود. باید خروجی یکی از آرگومان ها با بقیه متفاوت باشه.
در مثال زیر که اطلاعات مربوط به یک مسابقه است، میخوایم تعیین کنیم که بر اساس شروط زیر، کدوم ورزشکار باید بازی سوم رو هم انجام بده؟
- ورزشکاری که بازی اول و دوم رو برده، بدون نیاز به مسابقه، میره فینال و نیازی به انجام مسابقه سوم نداره.
- ورزشکاری که هر دو بازی رو باخته، کلا حذف شده و اصلا نیازی به بازی سوم نیست
- ورزشکاری که یکی از بازی های اول و دوم رو برده، باید بازی سوم رو هم انجام بده.
شکل 3- کاربرد تابع XOR
استفاده از NOT در اکسل
این تابع، هر خروجی منطقی رو برعکس میکنه. یعنی اگر خروجی آرگومان، True باشه، خروجی فرمول False خواهد بود و برعکس.
در مثال زیر، میخواهیم همه رنگ لباس رو به جز مشکی مشخص کنیم.
شکل 4- مشخص کردن همه رنگ ها به جز مشکی
تابع NOT هم در ترکیب بشه با تابع AND و تابع OR و… میتونه خیلی کاربردی باشه و مسائل مختلفی رو حل کنه.
در این مقاله با مثال های بیشتری از توابع منطقی OR/AND/XOR/NOT آشنا شدیم و ساختار و اصول ترکیب این توابع رو یاد گرفتیم. خروجی این توابع همگی False/True هستن که کاربرد خیلی زیادی در ترکیب با سایر توابع مثل if و ابزارهای مهمی مثل conditional formatting و data validation دارند.
ممکن است همچنین دوست داشته باشید
عملگرهای منطقی در اکسل
تابع MOD در اکسل یک تابع کاربردی
