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

توابع منطقی از پرکاربردترین و مفیدترین دستورات در اکسل است که میتوانند به بررسی سلولها بپردازند و نتیجه ای را به عنوان خروجی بازگردانند.
ابن توابع به ما کمک میکنند تا وظایف خود در صفحات گسترده را ساده کنیم.

چگونگی استفاده از دستور شرطی IF در اکسل

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

=IF(logical_test, [value_if_true], [value_if_false])

• logical_test : در این قسمت باید شرط مورد نظر خود را وارد کنید.
• value_if_true : در صورتی که نتیجه شرط صحیح یا True بود باید این قسمت انجام شود.
• value_if_false : در صورتی که نتیجه شرط صحیح نباشد یا False باشد این قسمت باید انجام شود.

عملگرهای مقایسه در اکسل

هنگامی که شما نیاز پیدا میکنید تا دو یا چند مقدار را با هم مقایسه و بررسی کنید پای این عملگرها به میان می آید ، توضحات هر دستور را در جدول زیر مشاهده خواهید کرد :

هم اکنون اجازه دهید تا مثال هایی را برای شرط IF و عملگرهای مقایسه ای بیان کنیم.

مثال 1 برای IF : مقدار متنی

در این مثال ما میخواهیم بررسی کنیم که یک سلول برابر با یک مقدار خاصی است یا خیر؟
تابع IF به حروف کوچک و بزرگ حساس نیست ، بنابر این حروف کوچک و بزرگ برای ما اهمیتی ندارند.
در فرمول زیر بررسی میکنید که اگر در ستون B و سطر 2 مقدار Completed وجود داشته باشد نتیجه را در سلول No قرار بدهد و اگر Completed نبود Yes را در ستون C قرار دهد.

=IF(B2="Completed","No","Yes"))

اگر چه IF به حروف کوچک و بزرگ حساس نیست ولی متن مورد بررسی به کوچک و بزرگ بودن حروف حساس است.

مثال 2 برای IF : مقدار عددی

عملکرد IF در مواجهه با مقادیر عددی بسیار خوب عمل میکند.
در فرمول زیر بررسی میکنیم که آیا مقدار سلول B2 که 78 است بزرگتر یا مساوی 75 است یا خیر ، اگر مقدار B2 بزرگتر یا مساوری 75 بود باید در سلول عبارت Pass را نوشته در غیر اینصورت Fail را بنویسد.

=IF(B2>=75,"Pass","Fail")

از آنجای که در سلول B2 مقدار 78 است و 78 بزرگتر از 75 است باید در سلول مقدار Pass نوشته شود.
به کمک این دستور میتوانید محاسبات مختلفی را بر روی سلولهای خود انجام دهید.
هم اکنون میخواهیم 10% تخفیف را به مشتری بدهیم در صورتی که مشتری مقدار مشخصی از پول خود را خرج کرده باشد ، در این مثال 3000 پوند را استفاده میکنیم.

=IF(B2>=3000,B2*90%,B2)

در اینجا میگوییم که اگر مقدار درون سلول B2 بزرگتر یا مساوی 3000 بود باید مقدار B2 را در 90 درصد ضرب کند در غیر اینصورت خود مقدار B2 را برگرداند.

مثال 3 برای IF : سایر مقادیر

در مثال سوم ما قصد داریم تا از مقادیر دیگری به غیر از رشته و عدد استفاده کنیم در اینجا میخواهیم تا بررسی خود را بر روی تاریخ انجام دهیم.
فرمول زیر را در نظر بگیرید :

=IF(B2<TODAY(),"Overdue",B2-TODAY())

در اینجا برسی میکنیم که آیا مقدار تاریخ درون سلول B2 کوچکتر از تاریخ امروز است (تاریخ امروز را با تابع ()TODAY واکشی میکنیم) اگر تاریخ درون سلول کوچکتر از تاریخ امروز باشد باید مقدار متنی Overdue را بازگرداند و اگر غیراینصورت بود باید تاریخ امروز را از B2 کم کند.
با این دستور میتوانیم متوجه شویم که چند روز دیگر تا رسیدن به تاریخ سلول باقی مانده است.

Nested IF یا شرط های تو در تو در اکسل چیست؟

شرط های تو در تو به این معناست که شما میتوانید IF های خود را درون IF های دیگر بنویسید ، چون در فرمول نویسیهای پیشرفته شما ممکن است به IF های تو در تو احتیاج پیدا کنید.
هامنطور که گفیم یک تابع IF قادر به خروجی دو مقدار value_if_true  و value_if_false است ، اگر یک تابع دیگر IF درون آنها قرار دهیم میتوانیم یک تابع شرطی دیگر را بررسی کنیم.
به فرمول زیر دقت کنید :

=IF(B2>=90,"Excellent",IF(B2>=75,"Good","Poor"))

در اینجا بررسی میشود که آیا مقدار B2 بزرگتر یا مساوری 90 است ، اگر جواب مثبت باشد عبارت Excellent باز خواهد گشت در غیر اینصورت ما درون یک شرط دیگر میرویم ، در شرط بعدی بررسی میشود که اگر B2 بزرگتر یا مساوی 75 بود عبارت Good بازگردانده شود در غیر اینصورت Poor بازمیگردد که در این مثال نتیجه فرمول ما Good خواهد بود.
در صورتی که شرط های زیادی را میخواهید بر روی مقادیر انجام دهید میتاونید از تابع SWITCH استفاده کنید.

توابع AND و OR

توابع AND و OR هنگامی که شما میخواهید بیش از یک مقایسه در فرمول خود داشته باشید استفاده میشود. عملکرد IF تنها میتواند یک شرط را درون خود بررسی کند.
نتایج AND و OR ارزش های True یا False را به خود میگیرند.
تابع AND به این صورت است که باید در عملگر مقایسه ای حتما خروجی هر دو True یا False باشد تا مقدار True را بازگرداند در غیر اینصورت False را باز میگرداند.
تابع OR به اینگونه است که هر یک از شرط ها اگر مقدار True را باز گردانند نتیجه کلی True خواهد بود و تنها در حالی نتیجه Fasle خواهد بود که نتیجه هر دو Fasle باشد.
شما میتوانید در شرط های خود تا 255 دستور AND یا OR را استفاده کنید.
در زیر ساختار دستور AND را مشاهده میکنید :

=AND(logical1, [logical2] ...)

در ادامه مثالهایی را برای AND و OR خواهیم زد.

مثال های برای تابع AND

در فرمول زیر AND وظیف دارد که برسی کند مشتری حداق 3000 پوند خرج کرده و حداقل سه سال است که مشتری ما است.

=AND(B2>=3000,C2>=3)

شما میتوانید نتیجه را به صورت True یا False مشاهده کنید.
همانطور که برای سلول B2 ملاحظه میکنید مقدار False برگشته است زیرا مقدار B2 بزگتر از 3000 نیست ، این در حالی است که در سمت دیگر مقدار C2 مساوی 3 است ،برای اینکه نتیجه کلی And باشد باید هر دو مقدار true داشته باشند که در اینجا فقط یک مقدار True است.

مثالهای برای OR

=OR(B2>=3000,C2>=3)

در این مثال همان دستور بالا را استفاده خواهیم کرد فقط به جای AND از OR استفاده خواهیم کرد. در اینجا چون مقدار ارزیابی ما یعنی C2 مساوی 3 است نتیجه کلی فرمول True خواهد بود.

استفاده از And و OR در IF

از آنجایی که AND و OR خروجیشان True و False است میتاونید از اینها در شرط های IF هم استفاده کنید.
در فرمول زیر بررسی میکنیم که آیا B2 بزرگتر از 3000 است که جواب آن False است پس تا اینجا در تابع AND یک طرف False  داریم ، در قسمت دوم شرط AND مقدار C2 را بررسی خواهیم کرد که مقدار True باز میگرداند بنابر این در دستور AND ما یک True و یک False داریم چون تابع And داریم در اینجا مقدار False برخواهد گشت.
بنابر این در شرط به قسمت False خواهیم رفت که مقدار B2 را برمیگرداند.

=IF(AND(B2>=3000,C2>=3),B2*90%,B2)

در جدول زیر سایر نتایج را در ستون D مشاهده خواهید کرد.

تابع XOR

علاوه بر تابع OR یک تابع منحصر به فرد دیگر نیز وجود دارد که XOR نامیده میشود. تابع XOR با نسخه اکل 2013 معرفی شد و ساختار XOR همانند OR است.

=XOR(logical1, [logical2] ...)

در هنگام ارزیابی تسوط XOR دو حالت خواهید داشت :

• اگر دو مقدار یکی باشند نتیجه False باز گردانده خواهد شد.
• اگر یکی True و دیگری False باشد مقدار True باز گردانده خواهد شد.

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

=IF(XOR(B2>=3000,C2>=3000),"Yes","No")

در فرمول بالا وارد XOR میشویم که در قسمت اول شرط آن بررسی میشود که آیا B2 بزرگتر یا مساوی 3000 است که نتیجه آن میشود False و در قسمت دوم بررسی میشود که C2 بزرگتر یا مساوی 3000 است یا خیر که نتیجه میشود False همانطور که گفتیم از دو مقدار در XOR یکی باشند خروجی False خواهد شد.
پس به قسمت False شرط IF وارد میشویم که مقدار No را باز میگرداند.

تابع NOT

تابع NOT از نامش مشخص است که چه عملکردی دارد ، اگر مقدار بررسی True باشد خروجی False را برمیگرداند و اگر False  باشد مقدار True باز میگردد.
با مثالی ساده با عملکرد این تایع آشنا خواهید شد.
قبل از آن به ساختار این تابع نگاهی بیندازید :

=NOT(logical)

* این تابع فقط یک ورودی میگیرد.

مثال اول برای تابع NOT

در این مثال ما تصور میکنیم که یک دفتر مرکزی در لندن و جاهای مختلف دیگر داریم ، ما میخوامی اگر مقدار سلول به غیر از لندن باشد True را باز گرداند.

=IF(NOT(B2="London"),"Yes","No")

این فرمول بررسی میکند که آیا B2 مساوی London است که نتیجه True است ولی چون از تابع NOT استفاده میکنیم False پاس داده خواهد شد که مقدار No در خروجی قرار میگیرد.
در زیر دستور NOT را با علامت های کوچک و بزرگتر نوشته ایم که دقیقا <> عملکردی هماند NOT دارد.

=IF(B2<>"London","Yes","No")

مثال دوم برای تابع NOT

در این مثال میخواهیم دستور ISTEXT را معرفی کنیم ، این دستور بررسی میکند که مقدار ورودی از نوع متن است یا خیر؟ اگر متن باشد مقدار TRUE را برمیگرداند و درغیر اینصورت False را برمیگرداند.
به مثال زیر دقت کنید :

=IF(NOT(ISTEXT(B2)),B2*5%,0)

در فرمول بالا بررسی میکنیم که آیا مقدار B2 رشته است خیر؟ اگر رشته ای بود True را پاس میدهد اگر دقت کنید ISTEXT درون تابع Not قرار دارد یعنی اینکه اگر رشته ای نبود باید مقدار 0 را پاس دهد.
این کار برای این است که اگر ما محاسبه عددی داشتیم و کاربر اشتباها و یا فراموش کند مقدار عددی وارد کند برنامه در تبدیل نوع رشته ای به عددی خطا نگیرد.