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

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

فیلم آموزشی تابع and و or در اکسل

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

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

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

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

تابع توصیف مثال فرمولی توصیف فرمول
AND در صورتی که تمام پارامترها صحیح باشد، TRUE را برمیگرداند.
=AND(A2>=10,B2<5)
این فرمول در صورتی که مقدار سلول A2 بزرگتر یا مساوی 10 باشد، و مقدار B2 کوچکتر از 5 باشد، TRUE را برمیگرداند. در غیر این صورت FALSE برگردانده میشود.
OR در صورتی که هرکدام از پارامترها صحیح باشد، TRUE را برمیگرداند.
=OR(A2>=10,B2<5)
این فرمول در صورتی که مقدار سلول A2 بزرگتر یا مساوی 10 باشد، یا مقدار B2 کوچکتر از 5 باشد، یا هر دو شرط برقرار باشد TRUE را برمیگرداند. اگر هیچ کدام از شروط برقرار نباشد، FALSE برگردانده میشود.
XOR یک or منحصر به فرد را برای تمام آرگومان ها برمیگرداند.
=XOR(A2>=10,B2<5)
این تابع در صورتی که یکی از شروط برقرار باشد TRUE را برمیگرداند در صورتی که هر دو شرط برقرار باشد یا هر دو شرط برقرار نباشد FALSE برگردانده میشود.
NOT عکس مقدار منطقی آرگومان خود را برمیگرداند. برای مثال در صورتی که آرگومان FALSE باشد،TRUE برگردانده میشود و برعکس.
=NOT(A2>=10)
این فرمول در صورتی که مقدار سلول A2 بزرگتر یا مساوی 10 باشد FALSE را برمیگرداند در غیر این صورت TRUE برگردانده میشود.

علاوه بر 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(A2="BANANAS",B2>C2)
در صورتی که A2 شامل موز باشد و B2>C2 باشد آنگاه true را برمیگرداند و در غیر این صورت false برگردانده میشود.
=AND(B2>20,B2=C2)
در صورتی که B2 بزرگتر از 20 باشد، و همچنین برابر C2 باشد آنگاه true را برمیگرداند در غیر اینصورت False برگردانده میشود.
=AND(A2="موز",B2>=30,B2>C2)
در صورتی که A2 شامل موز،  B2 بزرگتر یا مساوی 30 و B2 بزرگتر از C2 باشد آنگاه TRUE را برمیگرداند در غیر اینصورت FALSE برگردانده میشود.

تابع 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(AND(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(A2="موز",A2="پرتقال")
اگر A2 شامل موز یا پرتقال باشد TRUE و در غیر این صورت FALSE را برمیگرداند.
=OR(B2>=40,C2>=20)
اگر B2 بزرگتر یا مساوی 40 یا C2 بزرگتر یا مساوی 20 باشد TRUE و در غیر این صورت FALSE را برمیگرداند.
=OR(B2=" ",C2="")
در صورتی که یکی از B2 یا C2 یا هر دو خالی باشد TRUE و در غیر این صورت FALSE را برمیگرداند.

این تابع نیز میتواند تاحد زیادی بر قابلیت های دیگر توابعی که آزمون منطقی انجام می دهند( مثل 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(1>0,2<1)

TRUE

به این دلیل که آرگومان اول صحیح و دوم غلط است TRUE را برمیگرداند.
=XOR(1<0,2<1)

FALSE

به این دلیل که هر دو آرگومان غلط است، FALSE را برمیگرداند.
=XOR(1>0,2>1)

FALSE

به این دلیل که هر دو آرگومان صحیح است، 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 را نشان دادند. با فهم این مسائل پایه ای، میتوانید برای کارهای واقعی خود فرمول های پیچیده و هوشمندانه ای را بنویسید.

فیلم آموزشی استفاده از توابع منطقی در برنامه نویسی vba

اگر دوست دارید به صورت پیشرفته تر از این توابع در برنامه نویسی vba استفاده کنید و با منطق استفاده از آنها آشنا بشید فیلم آموزشی زیر رو ببینید.

مشترک شدن
Notify of
guest

13 نظرات
نظردهی درون متنی
مشاهده همه نظرات
زهرا

با سلام و احترام
چگونه باید در یک فاکتور خرید محصولات، جمع نهایی خرید را به صورت دستور شرطی زیر بنویسیم :
. دستور شرطی بنویسیم که اگر جمع نهایی خریدن بین 100000تا 200000 باشد 7 درصد و اگر بیش از 200000باشد 10 درصد تخفیف دهد و در غیر این صورت قیمت فاکتور را برگرداند و مبلغ پس از تخفیف نوشته شود و درصد تخفیف با نمایش پیغام متنی “شامل 7%تخفیف ” یا “شامل 10%تخفیف ” یا “عدم تخفیف ” نمایش داده شود .چگونه باید از تابع AND استفاده کنیم

امیر دایی

سلام برای اینکه با تابع if در اکسل به صورت کامل اشنا بشید حتما فیلم آموزشی و مطالب زیر مطالعه کنید. تابع if در اکسل به همراه مثال و فیلم آموزشی تابع if تو در تو به همراه مثال تابع ifs در اکسل 2019 با فرض اینکه جمع نهایی خرید در سلول A1 هست فرمول محاسبه مبلغ به صورت زیر میشه: =if(a1<100000,a1,if(a1<200000,(1-7%)*a1,(1-10%)*A1) برای اینکه درصد تخفیف و متن مشخص کنه کافی هست فرمول بالا رو در یک سلول دیگه بنویسید و به جای محاسبه متن مورد نظر بنویسید. در نهایت دوتا سلول با استفاده از & به هم بچسبونید که… مطالعه بیشتر »

Ghazal

سلام اگر بخوایم بگیم متغیر برابر ۰ یا ۱ باشه از چه تابعی میشه استفاده کرد؟

امیر دایی

کاری که میخواید بکنید باید بیشتر توضیح بدید. منظورتون متوجه نشدم.

ایمان همتی

عالی مثل همیشه

ali

سلام
من یک فرمول می خوام که ساخص های زیر را در نظر بگیره
اگر عدد ما کوچکتر از 0.5 باشه در سلول دیگری غیر قابل قبول بزنه ، اگر بین 0.5تا0.8 باشه حاشیه ای در نظر بگیره ، اگر بین 0.8 تا 1.2 باشه قابل قبول ، اگر بین 1.2 تا 1.5 باشه مجدد حاشیه ای و اگر از 1.5 بزرگتر باشه غیر قابل قبول بزنه
ممنون

امیر دایی

سلام مطلب و مثال های تابع if تو در تو رو مطالعه کنید.
if تو در تو در اکسل

محمد

سلام وقت به خیر یک سوال
در استفاده از تابع AND وقتی تعداد داده ها زیاد هست مثلا 1000 ردیف هست ولی انتخاب ما یک ردیف هست چ طوری میشه که علاوه بر اینکه اون ردیف یکی رو پیدا میکنه و رنگی میشه صفحه هم خودش خودکار بره روی اون سلول یعنی با ماشوراه ماوس نریم پیداش کنیم خودش خوکار بره روش مثل find که سلول رو پیدا میکنه.

امیر دایی

سلام
سوالتون خیلی گنگ هست. با مثال توضیح بدید.

محمد

مثلا یک ستون با 1000 عدد ردیف داریم ما وقتی از طریق سرچ کردن دنبال یک کلمه میگردیم علاوه براینکه کلمه رو پیدا میکنه هر جای صفحه باشیم هم سریع میره یا میپره روی اون سلول
حالا با تابع And هم همین اتفاق میفته یعنی پیدا میکنه اما مثلا الان که روی سلول 1 ماوس کلیک کرده و قرارداره دیگه برای پیدا کردن اون سلول true نمیپره صفحه روش
چ طوری برای تابع and این قابلیت رو بزاریم

امیر دایی

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

محمد

سلام دو تا فیلمی که بارگزاری شده یک هست. اشتباه شده اموزش and و or نیست

امیر دایی

سلام
ممنون از اینکه اطلاع دادید. اصلاح شد.