استفاده از توابع منطقی در اکسل – AND,OR,XOR,NOT

در این مطلب توابع AND,OR,XOR,NOT توضیح داده خواهد شد و مثال هایی از هرکدام ارائه می شود تا کاربرد های آن را برای شما روشن سازد.

هفته گذشته به بررسی عامل های منطقی در اکسل پرداختیم که با استفاده از آنها به مقایسه داده های مختلف در سلول های مختلف میپرداختیم. امروز خواهید دید که چگونه میتوان استفاده از عامل های منطقی را گسترش داد و چگونگی ساخت آزمون های پیچیده تر منطقی برای انجام و محاسبات و تحلیل های پیشرفته را خواهید آموخت. توابع منطقی اکسل از قبیل AND,OR,XOR,NOT در این راه به شما کمک خواهند کرد.

• توابع منطقی اکسل – بازنگری
• تابع AND اکسل
• تابع OR در اکسل
• تابع XOR اکسل
• تابع NOT در اکسل

توابع منطقی اکسل – بازنگری

اکسل چهار نوع تابع منطقی ارائه داده است . این توابع AND،  OR، XOR، NOT هستند. زمانی از این فرمول های استفاده میشود که میخواهید بیش از یک مقایسه را در فرمول خود انجام دهید و شرط های متعددی را بررسی کنید. توابع منطقی نیزمثل عامل های منطقی، پس از بررسی آرگومان ها مقادیر TRUE و FALSE را برمیگردانند.

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

علاوه بر 4 تابع منطقی بالا، اکسل سه تابع شرطی را نیز ارائه کرده است، IF، IFERROR و IFNA

توابع منطقی اکسل، آمار و ارقام

• در قسمت آرگومان های این توابع میتوانید از آدرس سلول، اعداد، مقادیر متنی، مقادیر BOOLIAN، عملگرهای مقایسه ای یا دیگر توابع TEXT استفاده کنید. به هر حال، هر کدام از آرگومان ها می بایست به صورت مقادیر TRUE و FALSE ارزیابی شود.
• در صورتی که آرگومان یک تابع منطقی شامل سلول خالی باشد، در نظر گرفته نمیشود، در صورتی که تمام آرگومان ها سلول های خالی باشند، فرمول خطای #VALUE را برمیگرداند.
• در صورتی که آرگومانی شامل اعداد باشد، آنگاه 0 مقدار FALSE را برمیگرداند و دیگر اعداد از جمله اعداد منفی مقدار TRUE را برمی گردانند. برای مثال، در صورتی که سلول های A1:A5 شامل اعداد باشد، آنگاه فرمول =AND(A1:A5) در صورتی که هیچ کدام از سلول های شامل 0 نباشد TRUE را برمیگرداند در غیر این صورت FALSE برگردانده میشود.
• در صورتی که یکی از آرگومان ها بعنوان مقادیر منطقی ارزیابی شود آنگاه فرمول، خطای #VALUE را برمیگرداند.
• در صورتی که نام تابع را غلط تایپ کرده باشید یا در ورژن های قدیمی اکسل از این توابع استفاده کنید، اکسل خطای #NAME! را برمیگرداند. برای مثال تابع XOR فقط در ورژن های 2013 و 2016 اکسل موجود است.
• در ورژن های 2007 به بعد اکسل میتوانید 255 آرگومان را در فرمول خود وارد کنید ولی طول فرمول نباید از 8192 کاراکتر بیشتر شود. در اکسل 2003 و پایینتر، میتوانید از 30 آرگومان استفاده کنید و طول فرمول شما 1024 کاراکتر نباید بیشتر شود.

استفاده از تابع And در اکسل

محبوب ترین تابع خانواده توابع منطقی and است. این تابع زمانی استفاده میشود که بخواهید چند شرط را به صورت همزمان استفاده کنید و مطمئن شویدکه تمام شروط صحیح هستند. این تابع تمام شرط ها را بررسی میکند و در صورت صحت تمام آنها true را برمیگرداند در غیر اینصورت false برگردانده میشود.

ترکیب تابع And به شکل زیر است:

=and(logical1[logocal2],…)

که در آن logical شرطی است که میخواهید بررسی کنید. شرط اول ضروری است و شروط بعدی اختیاری هستند. حال به بررسی چند مثال فرمولی میپردازیم که نحوه استفاده از تابع And را به شما نشان میدهد.

تابع andدر اکسل – کاربرد های معمول

این تابع به تنهایی کاربرد زیادی ندارد ولی در کنار دیگر توابع اکسل، تابع and میتواند تا حد زیادی قابلیت های کاربرگ شما را ارتقاء دهد. یکی از بزرگترین کاربرد های تابع And امکان استفاده از آن در قسمت آزمون منطقی تابع IF است. با این کار میتوان شروط متعددی را مورد بررسی قرار داد. برای مثال میتوانید هرکدام از تابع های and بالا را دررون تابع IF به کار گیرید و نتیجه ای شبیه به زیر بدست آورید:

=IF(AND(A2=”موز”,B2>C2),”بد”,”خوب”)

فرمول اکسل برای شرط میانی

در صورتی که به دنبال فرمولی هستید که تمام مقدار ها بین دو مقدار را درنظر بگیرد، میتوانید از ترکیب تابع IF با AND استفاده کنید.

برای مثال در ستون A و B و C سه مقدار دارید و میخواهید مشخص کنید که آیا مقدار ستون A در بین مقادیر ستون های B و C قرار می گیرد یا خیر. برای نوشتن چنین فرمولی، فقط کافیست از ترکیب دو تابع IF و  AND و تعدادی از عملگرهای مقایسه ای استفاده کنید.

فرمولی که نشان میدهد آیا X بین Y و Z می باشد ، به صورت فراگیر(شامل خود Y و Z نیز میشود)

=IF(AND(A2>=B2,A2<=C2),”خیر”,”بله”)

فرمولی که نشان میدهد آیا X بین Y و Z می باشد ، به صورت غیرفراگیر(شامل خود Y و Z نمیشود)

=IF(AND(A2>B2,A2<C2),”خیر”,”بله”)

همانطور که در تصویر بالا مشخص است، این فرمول برای تمام انواع داده، ارقام، تاریخ، و مقادیر متنی مناسب می باشد. در مقایسه متن، این فرمول متن ها را به صورت کاراکتر به کاراکتر و به ترتیب حروف الفبا ارزیابی میکند. برای مثال این فرمول نشان میدهد که سیب بین پرتقال و کیوی قرارمی گیرد زیرا،”س” بین “پ” و “ک” قرار دارد.

همانطور که مشاهده میکنید، ترکیب IF و AND بسیار ساده و سریع  است. فرمول بالا نشان میدهد که مقدار موجود در ستون B کوچکتر از مقدار ستون C است . به این معنی که ستون C همیشه حد بالا و ستون B همیشه حد پایین را میگیرد و دلیل اینکه فرمول برای ردیف   6 (A6 برابر 12، B6، 15 و C6 برابر 3 است)  خیر را برمیگرداند نیز همین است.

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

حال اگر به جای and در قسمت آزمون منطقی از MEDIAN استفاده کنید به فرمول زیر می رسید:

=IF(MEDIAN(A2=”موز”,B2>C2),”خوب”,”بد”)

با این کار نتایج زیر بدست خواهد آمد:

همانطور که می بینید تابع MEDIAN به خوبی با اعداد و تاریخ کار میکند ولی برای مقادیر TEXT خطای #NUM را برمیگیرداند. در صورتی که یک فرمول میانی عالی میخواهید که به خوبی با اعداد و تاریخ کار کند، می بایست ترکیب پیچیده ای از AND و OR ایجاد کنید:

=IF(OR(A2>B2,A2>C2),AND(A2>B2,A2>C2)),”خیر”,”بله”)

استفاده از تابع or در اکسل

علاوه بر And، or نیز یکی دیگر از توابع منطقی در اکسل است که برای دو عبارت منطقی با یکدیگر به کار گرفته می شود. تفاوت این دو این است که در تابع or اگر یکی از شروط هم صحیح باشد ، تابع true را برمیگرداند و اگر همه آرگومان ها صحیح نباشد، تابع false را برمیگرداند.تابع or در تمام ورژن های 2000 به بعد در دسترس است.

ترکیب تابع or بسیار شبیه به تابع and است:

Or(logical1,[logical2],…)

Logical عبارتی است که می بایست تست شود و میتواند true یا false باشد. Logicall اول ضروری است و مابقی (در ورژن های جدید تا 255) اختیاری هستند.

حال به بررسی چند مثال فرمولی میپردازیم تا درک روشنی از نحوه کار این تابع بدست آورید:

این تابع نیز میتواند تاحد زیادی بر قابلیت های دیگر توابعی که آزمون منطقی انجام می دهند( مثل or)در اکسل بیافزاید.

تابع if در ترکیب با or

=IF(OR(B2>30,C2>20),”خوب”,”بد”)

 این فرمول در صورتی که مقدار سلول B2 بزرگتر از 30 باشد یا  مقدار سلول C2 بزرگتر از 20  باشد خوب را برمیگرداند و در غیر این صورت بد را برمیگرداند.

توابع and/or در فرمول

به صورت طبیعی هیچ مانعی در استفاده از 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)

برای مثال، به منظور مشخص کردن اینکه کدام محموله از سیب یا پرتقال به فروش رسیده است، به این معنی که مقدار موجودی انبار (ستون (B برابر با مقدار فروش رفته (ستونC) باشد، فرمول زیر میتواند سریعا این موضوع را به شما نشان دهد:

=OR(AND(A2=”موز”,B2=C2),AND(A2=”پرتقال”,B2=C2))

تابع OR در CONDITIONAL FORAMATING اکسل

=OR($B2=””,$C2=””)

 فرمول بالا سلول های خالی موجود در ستون های B و C یا هر دو را هایلایت میکند .

عکس

استفاده از XOR در اکسل

اکسل در ورژن 2013  تابع XOR را معرفی کرد این تابع همان OR منحصر به فرد است . این واژه برای افرادی که با دیگر زبان های برنامه نویسی یا دانش عمومی کامپیوتر آشنایی دارند، آشنا است. برای کسانی که این آشنایی را ندارند واژه ” OR منحصر به فرد” ممکن است به خوبی هضم نشود ولی توجه به فرمول های زیر قطعا کمک کننده خواهد بود:

تابع XOR شباهت بسیار زیادی به OR دارد:

=XOR(LOGICAL1,[LOGICAL2],…)

Logical اول ضروری است و مابقی (در ورژن های جدید تا 254) اختیاری هستند. این Logical ها میتوانند مقادیر منطقی، آرایه ها یا آدرسها باشد که میتواند TRUE یا FALSE باشد.

ساده ترین شکل این تابع دو عبارت منطقی دارد که اگر یکی صحیح باشد TRUE برگردانده میشود و اگر هر دو صحیح باشد یا هر دو شتباه باشد، FALSE برگردانده میشود.

فرمول های زیر به سادگی این موضوع را نشان میدهد:

زمانی که عبارت های منطقی بیشتری به فرمول اضافه شود تابع XOR به این شکل عمل خواهد کرد:

• در صورتی که تعداد فردی از آرگومان ها صحیح باشد TRUE را برمیگرداند.
• در صورتی که تعداد زوجی از آرگومان ها صحیح باشد یا تمام آرگومان ها اشتباه باشد FALSE را برمیگرداند.

تصویر زیر این موضوع را نشان میدهد:

در صورتی که در مورد استفاده از تابع XOR در زندگی واقعی خود اطمینان ندارید ، به مثال زیر توجه کنید:

فرض کنید جدولی از شرکت کنندگان در یک مسابقه و نتایج آنان برای دو بازی اول در اختیار دارید و میخواهید بدانید که کدام یک از بازیکنان بر اساس شرایط زیر بازی سوم را انجام خواهد داد:

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

این کار را میتوان به وسیله یک فرمول ساده XOR انجام داد:

=XOR(B2=”برد”,C2=”برد”)

و اگر این فرمول را در قسمت آزمون منطقی تابع IF قرار دهید، نتایج ملموس تری را بدست خواهید آورد:

=IF(XOR(B2=”برد”, C2=”خیر”, “بله”, (“برد”)

استفاده از تابع NOT در اکسل

یکی از ساده ترین توابع اکسل از لحاظ ترکیب تابع NOT است:

=NOT(LOGICAL)

برای بر عکس نشان دادن مقدار یک آرگومان از آن تابع استفاده میشود. به عبارت دیگر، در صورتی که عبارت منطقی غلط ارزیابی شود TRUE برگردانده میشود و برعکس. برای مثال، هر دو فرمول FALSE را برمی گرداند:

=NOT(TRUE)

=NOT(2*2=4)

 اما چرا باید از چنین تابعی استفاده کرد و چنین نتیجه خنده داری را بدست آورد؟ در بعضی موارد، شما نیاز خواهید داشت که بدانید چه زمانی یک شرط صحیح نمی باشد . برای مثال ممکن است بخواهید برای لباس پوشیدن، بعضی از رنگ ها را که مناسب شما نیست استفاده نکنید. برای مثال اگر نخواهید از رنگ مشکی استفاده کنید، از فرمول زیر استفاده کنید:

=NOT(C2=”مشکی”)

مثل همیشه در اینجا نیز بیش از یک راه برای انجام این کار وجود دارد و میتوان به جای استفاده از تابع NOT  از عملگر غیر مساوی استفاده کرد:

=C2<>”مشکی”

در صورت که میخواهید شروط چندین گانه ای را در تابع NOT استفاده کنید می بایست این تابع را با توابع OR یا And ترکیب کنید. برای مثال، در صورتی که میخواهید رنگ های سیاه و سفید را مستثنی کنید، فرمول به صورت زیر خواهد بود:

=NOT(OR(“مشکی”,C2=”سفید”)

 و اگر به جای کت مشکی ژاکت مشکی یا کت چرمی مد نظرتان است میتوانید از ترکیب AND و NOT استفاده کنید:

=NOT(AND(C2=”مشکی”,C2=”کت”)

یکی دیگر از کاربرد های تابع Not در اکسل برعکس نشان دادن عملکرد دیگر توابع در اکسل است. برای مثال، میتوانید از ترکیب Not و ISBLANK تابعی ایجاد کنید که سلول های غیر خالی را نشان میدهد و در اکسل نیز موجود نیست.

همانطور که میدانید فرمول =ISBLANK(A2) در صورتی که سلول A2 خالی باشد TRUE را برمیگرداند. تابع NOT میتواند این نتیجه را به FALSE تغییر دهد.

=NOT(ISBLANK(A2))

همچنین میتوانید پا را فراتر بگذارید و با ترکیب IF/NOT/ISBLANK تابعی ایجاد کنید که در دنیای واقع نیز قابل استفاده باشد:

=IF(NOT(ISBLANK(C2)),C2*0.15,”NO BONUS: (“)

 این فرمول به اکسل میگوید که این کار را انجام دهد: در صورتی که سلول C2 خالی نباشد، مقدار موجود در آن را در 0.15 ضرب کند که ان کار 15 درصد جایزه به فروشندگانی که فروش اضافی داشته باشند ارائه میدهد و در صورتی که C2 خالی باشد عبارت “بدون جایزه:( ” نشان داده میشود.

نحوه کار توابع منطقی در اکسل را مرور کردیم. البته این مثال ها، فقط ویژگی های سطحی تابع های AND,OR,NOT,XOR را نشان دادند. با فهم این مسائل پایه ای، میتوانید برای کارهای واقعی خود فرمول های پیچیده و هوشمندانه ای را بنویسید.