تابع IF با سه شرط در اکسل – توضیح پیاده سازی به زبان ساده + کد

IF یکی از پرکاربردترین توابع برای بررسی شرط‌های مختلف در اکسل است. با استفاده از این دستور می‌توانید مسائل مختلف را تجزیه‌ و تحلیل کنید و برای پاسخ‌های درست (True) مقادیر مختلفی را نمایش دهید. شما باید بتوانید به‌طور هم‌زمان چندین شرط را بررسی کرده و در صورت «درست» (True) بودن شرط‌ها، نتیجه ارزیابی‌های منطقی بازگردانده شوند یا عمل خاصی انجام شود. بنابراین دیر یا زود باید نوشتن فرمول‌های پیچیده شامل چندین تابع IF را یاد بگیرید. فرمول‌های اکسلی که در آن‌ها بیش از یک شرط IF وجود دارد به توابع IF تو در تو شهرت دارند. اگر با تابع IF در اکسل آشنا نیستید، توصیه می‌کنیم ابتدا مقاله «تابع IF در اکسل» را مطالعه و سپس نحوه نوشتن تابع IF با سه شرط در اکسل را در این مطلب دنبال کنید.

مثال‌های این مطلب در تمامی نسخه‌های اکسل اعم از نسخه‌های ۲۰۰۷ تا ۲۰۲۱ و مایکروسافت ۳۶۵ (Microsoft 365) قابل اجرا است. در ادامه می‌خواهیم آموزش تابع IF را با سه شرط در اکسل به‌ همراه مثال‌های عملی و شرط‌های مختلف ارائه دهیم.

تابع IF در اکسل چیست؟

بیشتر اوقات با فرمول نویسی در اکسل می‌توان محاسبات مختلفی را انجام داد. با تابع IF نیز می‌توانید مقایسه منطقی بین یک یا چند شرط را انجام داده و بر اساس پاسخ درست یا غلط مقداری را نمایش دهید.

سینتکس (Syntax) تابع IF به‌ شکل زیر است:

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

تابع IF در اکسل پارامترهای مختلفی دارد که در ادامه کاربرد هر کدام را به ترتیب بررسی می‌کنیم.

• Logical_test: منظور از این پارامتر هر مقدار یا عبارت منطقی است که با ارزیابی آن می‌توان به پاسخ صحیح (True) یا غلط (False) رسید. این پارامتر می‌تواند از عملگرهای مقایسه‌ای مختلفی برای بررسی نتیجه عبارت‌های منطقی استفاده کند. مثلاً 100 =[Quarter1] عبارت منطقی است. درصورتی‌که یک سطر از ستون [Quarter1] برابر ۱۰۰ باشد، تابع پاسخ True و در غیر اینصورت پاسخ False را برمی‌گرداند.
• Value_if_true: این پارامتر مقداری است که درصورت پاسخ True آرگومان Logical_test برگردانده می‌شود. مثلاً اگر این پارامتر برابر رشته متن Within Budget باشد و پارامتر Logical_test پاسخ True را برگرداند، تابع IF رشته متن Within Budget را در خروجی نمایش می‌دهد. همچنین اگر Logical_test برابر True و Value_if_true خالی باشد، این پارامتر برابر پاسخ صفر (0) می‌شود.
• Value_if_false: درصورت False بودن Logical_test این پارامتر بازگردانی می‌شود. مثلاً اگر این آرگومان شامل رشته متن Over budget و Logical_test برابر False باشد، تابع IF در اکسل رشته متن Over budget را در خروجی نمایش می‌دهد.

برای بررسی چند شرط مثل ۲ یا ۳ شرط در تابع IF در اکسل باید شرط‌ها را با عملگرهای منطقی زیر به یکدیگر مرتبط کنیم: ‌

• AND: استفاده از این عملگر منطقی به این معنا است که همه شرط‌ها، مثلاً هر ۳ شرط در گزاره IF باید True یا همگی با هم باید False باشند.
• OR: استفاده از گزاره منطقی OR در شرط IF یعنی هر یک از شرط‌ها می‌توانند True یا False باشند.
• NOT: نقیض شرط نیز برای زمانی است که هیچ‌کدام از شرط‌ها با معیارهای شما مطابقت ندارند.

آموزش استفاده از تابع IF با سه شرط در اکسل

در مثال اول دستور IF با سه شرط در اکسل را با استفاده از عملگر منطقی OR بررسی می‌کنیم. به‌طوری‌که در دستور IF با استفاده از عملگر OR اگر مقدار هر خانه درستون Department برابر با Financial Reporting ،Financial Reporting یا Planning & Budgeting باشد، در ستون Division مقدار Finance قرار داده می‌شود و در غیر اینصورت، مقدار «Other» در سلول مربوطه قرار داده می‌شود. بنابراین فرمول مربوط به اجرای این عملکرد برای خانه D31 به صورت زیر است:

1=IF(OR(D31="Accounting", D31="Financial Reporting", D31="Planning & Budgeting"), "Finance", "Other")

در تصویر زیر مشاهده می‌شود که با استفاده از فرمول بالا مقدار Finance در خانه E31 قرار داده شده است، زیرا مقدار خانه D31 برابر با «Planning & Budgeting» بوده و شرط IF برقرار است.

در این فرمول اکسل ۳ شرط با استفاده از عملگر OR به کار رفته و در گزاره IF ملاک قرار داده می‌شوند. در عملگر OR اگر یکی از شرط‌ها درست باشد، ارزش گزاره و شرط IF برابر با True خواهد بود و بنابراین عملیات تعیین شده در فرمول برای آن مورد خاص اجرا خواهد شد.

• شرط اول: در این شرط بررسی می‌شود که آیا مقدار خانه D31 در جدول برابر با «Accounting» هست یا خیر. در این مورد، پاسخ منفی است و بنابراین شرط دوم باید بررسی شود.
• شرط دوم: حال در شرط دوم بررسی می‌شود که آیا مقدار سلول D31 برابر با «Financial Reporting» هست یا خیر. این بار هم پاسخ منفی است و بنابراین در ادامه باید شرط سوم بررسی شود.
• شرط سوم: اینجا در آخرین شرط یعنی شرط سوم تابع IF بررسی می‌شود که آیا مقدار D31 برابر با «Planning & Budgeting» هست یا خیر که این بار پاسخ مثبت است و بنابراین کل عبارت شرطی گزاره IF برابر با True می‌شود. یعنی شرط گزاره IF برقرار است و بنابراین باید دستور تعیین شده برای حالت True در تابع IF اجرا شود. در نتیجه، مقدار «Finance» در سلول E31 قرار داده می‌شود.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

در مثال بعدی می‌خواهیم مسئله‌ای برای دستور IF با سه شرط در اکسل را با استفاده از عملگر منطقی AND بررسی کنیم. در این مثال نمرات امتحانی ۳ دانش‌آموز در جدول نوشته شده است. اگر دانش‌آموزان بتوانند در هر ۳ امتحان نمره بالای ۷۰ دریافت کنند، شرط IF مقدار «قبول» (Pass) و در غیر اینصورت، مقدار «مردود» (Fail) را بازمی‌گرداند. وقتی از عملگر AND در گزاره شرطی IF در اکسل استفاده می‌شود، برای برقرار بودن کل شرط IF، باید تک تک شرط‌ها برقرار و برابر با True باشند و حتی اگر یکی از آن‌ها False باشد، کل گزاره شرطی دستور IF برابر با False خواهد بود و در نتیجه عملیات تعیین شده برای False بودن شرط در تابع IF اجرا خواهد شد. فرمول مربوطه برای این مثال به منظور بررسی ردیف ۵۳ در جدول به صورت زیر است که در سلول H53 نوشته می‌شود.

1=IF(AND(E53>=70, F53>=70, G53>=70), "Pass", "Fail")

در تصویر زیر مشاهده می‌شود که با استفاده از فرمول بالا مقدار «قبول» (Pass) و «مردود» (Fail) بررسی شده و نتیجه در خانه H53 مقدار «قبول» (Pass) قرار داده شده است، زیرا مقدار خانه‌های F53 ،E53 و G53 برابر با بالاتر یا مساوی ۷۰ بوده و شرط IF برقرار است.

در این فرمول سه شرط زیر ارزیابی می‌شوند:

• شرط اول: در این شرط بررسی می‌شود که آیا نمره اولین امتحان برای دانش‌آموز مربوطه بالاتر یا مساوی ۷۰ بوده است یا خیر؟ در این مثال پاسخ بله است، به همین دلیل فرمول ادامه پیدا کرده و شرط بعدی را بررسی می‌کند.
• شرط دوم: در اینجا نیز نمره امتحان دوم برای بالاتر یا مساوی ۷۰ بودن بررسی می‌شود. با توجه به جدول پاسخ بله است و فرمول برای بررسی شرط سوم ادامه پیدا می‌کند.
• شرط سوم: در آخرین شرط نیز شرط‌های امتحان سوم برای نمره بالاتر یا مساوی ۷۰ بررسی می‌شود و با برقرار بودن شرط، گزاره شرطی تابع IF برقرار و برابر با True است و لذا عملیات مربوط به حالت True در تابع IF اجرا می‌شود.

آموزش تابع IF با چندین شرط مختلف

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

• چند دستور IF تو در تو (که با عنوان تابع IFS در اکسل هم شناخته می‌شود).
• فرمولی با ترکیب چند شرط به وسیله عملگرهای OR ،AND و NOT

آیا می‌دانید تا چند تابع IF را می‌توان در اکسل نوشت؟ در واقع تا ۶۴ شرط را با دستور IF در اکسل می‌توان مورد بررسی قرار دارد. با این حال استفاده از این تعداد شرط کمی دور از ذهن به نظر می‌رسد. علاوه بر این، قطعاً جایگزین‌های بسیار بهتری نسبت به استفاده از این نوع فرمول‌نویسی پیچیده وجود دارد.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

در مثالی که می‌خواهیم تابع IF تو در تو را توضیح دهیم، نمرات دانش‌آموزان بر اساس نمره‌های کسب شده با فرمولی شامل ۴ دستور IF تو در تو بررسی می‌شود.

1=IF(E107<60, "F", IF(E107<70, "D", IF(E107<80, "C", IF(E107<90, "B" ,"A"))))

نکته: توجه کنید هنگام استفاده از فرمول چند شرطی در اکسل نتیجه شرط‌ها می‌توانند روی نتیجه نهایی تاثیرگذار باشند. هنگام تداخل چند شرط، اگر فقط یکی از IFهای داخلی برابر با True ارزیابی شود، کل IF خارجی True در نظر گرفته می‌شود و بنابراین مقداری بازگردانده خواهد شد که در جای آرگومان [value_if_true] قرار داده شده است و بقیه مقادیر را نادیده می‌گیرد. هنگام استفاده از IFهای تو در تو اگر می‌خواهید فرمول به درستی کار کند، به ترتیب توابع منطقی حتماً توجه کنید.

مثلاً اگر بخواهید از دستور IF با ۳ خروجی استفاده کنید، استفاده از یک تابع IF تو در تو کافی خواهد بود. بنابراین اولین عبارت IF مقدار خروجی اول را بررسی می‌کند و عبارت دوم برای بررسی احتمال‌های خروجی دوم و سوم است.

نکته: در صورت استفاده از آفیس ۳۶۵ و نسخه‌های ۲۰۱۶ به بالای اکسل می‌توانید از تابع IFS در اکسل به جای IF تو در تو استفاده کنید. این تابع نوشتن دستورات را به طور چشم‌گیری کوتاه‌تر و خواناتر می‌کند.

در این مثال، حالت پیشرفته استفاده از تابع IF با ۳ شرط منطقی OR ،AND و NOT بررسی می‌شود. در جدول اکسل مربوط به این مثال، فهرستی از سفارش‌ها وجود دارد که برای هر سفارش، ستون‌هایی شامل «Date» (تاریخ سفارش)، «Order Value» (قیمت سفارش)، «Category» (دسته‌بندی محصول) و «Free Shipping» (ارسال رایگان) در نظر گرفته شده است. درصورت درست بودن تست‌های منطقی زیر، محصولات به‌ عنوان واجد شرایط (Eligible) علامت‌گذاری خواهند شد. شرط‌ها باید به صورت زیر باشند:

• سفارش‌ها باید در سال ۲۰۲۰ انجام شوند.
• سفارش محصولات تنها شامل دو دسته لپ‌تاپ و کامپیوتر است.
• امکان ارسال رایگان برای سفارش‌ها وجود ندارد.

نتیجه بررسی فرمول در سلول H80 نمایش داده می‌شود:

1=IF(AND(D80>=DATE(2020,1,1),D80<=DATE(2020,12,31),OR(F80="PC", F80="Laptop"), NOT(G80="Yes")), "Eligible", "Not eligible")

در تصویر زیر مشاهده می‌شود که با استفاده از فرمول بالا مقدار Eligible در خانه H80 قرار داده شده است، زیرا مقدار خانه‌های F80 ،E80 و G80 برابر با شرط‌های سال سفارش ۲۰۲۰، دسته محصول لپ‌تاپ و ارسال غیر رایگان بوده و شرط IF برقرار است.

تحلیل فرمول به شرح زیر است:

1. با AND اطمینان حاصل می‌شود تمام شرط‌های منطقی برای علامت‌گذاری سفارش محصول به عنوان واجد شرایط (Eligible) درست باشند. در صورت نادرست بودن (False) هر یک از شرط‌ها، تابع IF آرگومان [value_if_false] را برمی‌گرداند.
2. عبارت D80>=DATE(2020,1,1) و D80<=DATE(2020,12,31)  بررسی می‌کند آیا سفارش بین تاریخ‌های ۱ ژانویه و ۳۱ دسامبر انجام شده یا خیر.
3.  OR برای بررسی دسته محصول (لپ‌تاپ یا کامپیوتر) استفاده می‌شود.
4. در نهایت شرط NOT برای بررسی ارسال رایگان و غیر رایگان است. این شرط پاسخ بله و خیر را برای ارسال رایگان نمایش می‌دهد.

فیلم آموزش محاسبات آماری در اکسل Excel در فرادرس

کلیک کنید

جمع‌بندی

توابع شرطی تجزیه و تحلیل مسائل پیچیده را ساده‌تر می‌کنند. به همین دلیل با استفاده از آن‌ها می‌توانید در اکسل مسائل را به ساده‌ترین شکل ممکن حل کنید. در این مطلب تابع IF با سه عملگر منطقی OR ،AND و NOT را بررسی کردیم تا دید بهتری نسبت به حل مسائل با توابع چند شرطی پیدا کنید.

سؤالات متداول

در ادامه به سوالات متداول پیرامون تابع IF با سه شرط در اکسل پاسخ می‌دهیم.

در صورت وجود چند شرط چگونه از تابع IF استفاده کنیم؟

برای حل مسائل چند شرطی می‌توان از عبارات شرطی IF با سه عملگر منطقی OR ،AND و NOT استفاده کرد. استفاده از عملگر منطقی AND به این معنا است که همه شرط‌ها، مثلاً هر ۳ شرط در گزاره IF باید True یا همگی با هم باید False باشند. در عملگر منطقی OR یک شرط درست یا نادرست برای برگشت مقدار آرگومان‌ها کافی است. مثلاً اگر ۳ شرط وجود داشته باشد برای پاسخ درست تابع IF با درست‌بودن یکی از شرط‌ها مقدار تابع بازگردانی می‌شود. NOT نیز مقدار را با عبارت درست و غلط بررسی می‌کند و نتیجه هرچه باشد، خروجی برعکس برگردانده می‌شود.

آیا می‌توان از چندین دستور IF در اکسل استفاده کرد؟

بله، تا ۶۴ تابع مختلف IF تو در تو در اکسل را می‌توان استفاده کرد. با این حال استفاده از این تعداد اصلاً توصیه نمی‌شود.

استفاده از چند دستور IF بهتر است یا else IF؟

اگر شرط‌های مختلف با چندین تابع IF دارید که پاسخ هر کدام می‌تواند درست باشد، باید از چندین دستور IF استفاده کنید. در غیر اینصورت تنها برای یک شرط درست در تابع IF استفاده از else IF توصیه می‌شود.