تابع IF اکسل - نوشتن فرمول شرطی در اکسل

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

امیدوارم که روزی در دبیرستان‌ها برای همه رشته‌ها کمی اصول این درس زیبا، آموزش داده شود.

جملات شرطی چه در اکسل باشد و چه در زبان روزمره دارای سه قسمت است که با حوصله می خواهم تک به تک این قسمت ها را برای شما شرح دهم:

قسمت اول تابع IF اکسل - مقایسه

در هر جمله شرطی ساده ما معمولا در ابتدا یک مقایسه می کنیم به عنوان مثال:

* می گوییم که اگر معدل تو بالای 18 شود.  (معدل با عدد 18 مقایسه می شود.)

* می گوییم که اگر هوا بارانی بود . (وضعیت هوا با بارانی بودن مقایسه می شود)

* اگر بعد از ساعت 8 کارت بزنی  (ساعت ورود با عدد 8 صبح مقایسه می شود)

* اگر حقوق تو بالای 1500000 تومان باشد ( مبلغ حقوق شما با مبنای 15000000 مقایسه می شود)

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

قسمت دوم تابع IF اکسل - یک اتفاق

قسمت دوم جملات شرطی به ما می گوید که اگر پاسخ قسمت اول "درست از آب درآمد" یا به زبان دقیق‌تر True شد، باید چه اتفاقی باید بیافتد. و معمولا در فارسی از واژه "آنگاه" ، "اونوقت" و سپس بیان آن "اتفاق" استفاده می کنیم:

*  که اگر معدل تو بالای 18 شود آونوقت "برایت پلی استیشن می خرم"

* می گوییم که اگر هوا بارانی بود آنگاه "چترت را ببر"

* اگر بعد از ساعت 8 کارت بزنی  آنگاه "کسر کار خواهی خورد"

* اگر حقوق تو بالای 1500000 تومان باشد اونوقت "باید مالیات بدهی"

قسمت سوم تابع IF اکسل - یک اتفاق

قسمت سوم تابع IF در اکسل یا هر جمله شرطی به ما می گوید که اگر پاسخ مقایسه "درست از آب در نیامد" یا به زبان دقیق‌تر False شد،  چه اتفاقی باید بیافتد. معمولا در فارسی این قسمت بعد از واژه "وگرنه" و "در غیر اینصورت" بیان می شود:

*  که اگر معدل تو بالای 18 شود آونوقت "برایت پلی استیشن می خرم" وگرنه "دوچرخه ات رو هم می گیرم"

* می گوییم که اگر هوا بارانی بود آنگاه "چترت را ببر" در غیراینصورت "کلاه تابستانیت را ببر"

* اگر بعد از ساعت 8 کارت بزنی  آنگاه "کسر کار خواهی خورد" وگرنه "مشکلی نیست"

* اگر حقوق تو بالای 1500000 تومان باشد اونوقت "باید مالیات بدهی" و در  غیر اینصورت "مشمول مالیات نیستی"

خلاصه و جمع بندی

پس یک جمله شرطی ساده در اکسل و هر زبان برنامه نویسی دیگری دارای سه قسمت است :
1) مقایسه
2) اتفاقی (یا کار یا محاسبه ای) که اگر پاسخ مقایسه درست از آب درآمد، باید رخ دهد.
3) اتفاقی (یا کار یا محاسبه ای) که اگر پاسخ مقایسه درست از آب در نیامد، باید رخ دهد.

عکس زیر اینها را به صورت خلاصه مثال هوای بارانی به شما نشان می دهد. یعنی

 اگر «هوا بارانی بود» آنگاه «چتر ببر» وگرنه «کلاهت را بردار»

شکل کلی تابع IF در اکسل

تابع IF در اکسل سه ورودی دارد و ورودی ها با علامت کاما از هم جدا می شوند:

IF( ... , ... , ....)

حال می‌دانیم که باید در قسمت اول IF باید "یک مقایسه" بنویسیم و به جای نوشتن «آنگاه» و «وگرنه» در فرمول  IF کافی است که یک کاما بگذارید و خود اکسل منظور شما را می‌فهمد.

بنابراین شکل کلی تابع IF اکسل اینگونه خواهد شد:

IF( Yek moghayese , har formuli  , har formuli)

مثال ۱)

فرمولی می‌نویسیم که عبارت  اگر «هوا بارانی بود» آنگاه «چتر ببر» وگرنه «کلاهت را بردار» را در اکسل شبیه سازی کند:

=IF( hava = 🌧 , ☂ , 👒 )

مثال ۲) 

مثلا می خواهیم بدانیم که آیا حقوق شما مشمول مالیات هستید یا خیر. برای اینکار باید حقوق شما را در سلول A1 اکسل می نویسیم و با حداقل حقوق تصویبی برای مالیات مقایسه کنیم  بنابراین قسمت اول IF به شکل زیر خواهد شد:

IF( A1 > 15000000 , ...  , ...)

در قسمت دوم IF هر فرمولی و یا هر محاسبه ای و یا هر چیزی که اکسل می فهمد 😉 را  آن بنویسید. و این مقدار وقتی محاسبه می شود که پاسخ شرط شما "TRUE" شود و اگر پاسخ شرط شما FALSE شود، این محاسبه هرگز انجام نمی شود.

در این مثال ما 10% مبلغ حقوق را به عنوان مالیات محاسبه می کنیم و اگر حقوق بیش از 15000000 باشد، این محاسبه انجام می شود و به عنوان پاسخ فرمول IF نمایش داده می شود.

IF( A1 > 15000000 , A1*10%  , ...)

قسمت سوم IF ، می تواند دقیقا مانند قسمت دوم هر فرمول اکسلی باشد، و اگر پاسخ شرط شما FALSE شود، این محاسبه انجام می شود و به خروجی  IF نمایش داده می شود .

در این مثال می خواهیم که مالیات حقوق زیر 15000000 عدد صفر باشد.

IF( A1 > 15000000 , A1*10%  ,  0 )

به همین سادگی در اکسل یک IF نوشته می شود.

و می توانیم این فرمول را اینگونه بخوانیم:

"اگر مبلغ حقوق (یا سلول A1) بیشتر از 15000000  بود آنگاه باید 10% مبلغ حقوق را مالیات بدهی و گرنه مالیاتت صفر خواهد شد".

مثال ۳) 

در جلوی نام پروژه‌هایی که درصد پیشرفت آنها ٪ ۱۰۰ شده است، علامت تیک بگذارید.

=IF( A1 = 100%, "✔", "")

مشاهده می‌کنید که می‌توانیم از هر ایموجی در فرمول IF استفاده کنیم و اگر بخواهیم که خروجی تابع هیچ باشد، باید از "" استفاده کنیم.

مثال ۴)

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

می‌دانیم که عیدی دو برابر حقوق است به شرط آنکه از سه برابر حداقل حقوق سال مربوطه بیشتر نباشد.اگر سه برابر حقوق پایه را مبلغ ۱۲ میلیون تومان در نظر بگیرید و بنابراین هیچ کسی نباید بیش از ۱۲ میلیون تومان عیدی بگیرد. اگر در سلول A1 حقوق پایه کاوه وارد شده باشد، فرمول عیدی کاوه در اکسل اینگونه خواهد شد:

=IF( A1 * 2 > 12000000, 12000000, A1 * 2)

مثال ۵) 

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

فرض کنیم که می‌خواهیم اگر کسی بعد از ساعت ۸ صبح ورودش ثبت شد، برای او تاخیر در ورود (Late) بزنیم و گرنه بزنیم On Time. برای نوشتن ساعت 8:00 در هر فرمولی اکسلی باید از تابع TIMEVALUE استفاده کرد:

=IF( A1 > TIMEVALUE("8:00"), "Late", "On Time")

بررسی چندین شرط در تابع IF اکسل

در تمامی مثال‌های بالا، در قسمت اول تابع IF فقط ۱ شرط را بررسی می‌کردیم، مثلا «هوا = بارانی» است یا خیر. حال می‌خواهیم فرمول‌هایی را بنویسیم که در قسمت شرط آن‌ها باید چندین شرط بررسی شوند.

برای پیاده سازی چندین شرط در فرمول نویسی اکسل از ۲ تابع AND / OR استفاده می‌شود. که در ادامه با مثال‌هایی ساده آن‌ها را ذکر می‌کنم.

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

معرفی تابع AND اکسل 

پرده اول) برگشت از کوه 

غروب است و آزاده و کاوه خسته و گرسنه از کوهنوری به خانه رسیده‌اند و کاوه به سارا می‌گوید اگر در در خانه گوجه و تخم مرغ داریم، یک املت درست کنیم و گرنه چای شیرین و پنیری می‌خوریم. 

در این مثال اگر ۲ چیز فراهم باشد یعنی تخم مرغ داشته باشند و همچنین گوجه هم داشته باشند، آنگاه املت درست می‌کنند وگرنه چای شرین و پنیر. 

در این جلمه به واژه «همچنین» دقت کنید که با آن برقرار بودن ۲ شرط را بررسی می‌کنیم و در دنیای فرمول نویسی اکسل «همچنین» را با تابع AND می‌نویسیم. در تابع AND چندین عبارت را بررسی می‌کنیم و اگر حاصل همه عبارت‌ها TRUE بود، آنگاه نتیجه تابع AND ، مقدار TRUE می‌شود وگرنه (حتی اگر حاصل یک عبارت FALSE باشد) ، نتیجه تابع AND، مقدار FALSE خواهد شد. معمولا تابع AND را به شکل زیر در کتاب‌های برنامه نویسی نمایش می‌دهند. 

L1    | L2    | AND
-------------------
TRUE  | TRUE  | TRUE
TURE  | FALSE | FALSE
FALSE | TRUE  | FALSE
FALSE | FALSE | FALSE

در جدول فرضی بالا وقتی L1 مقدارش TRUE باشد و هچنین L2 هم مقدارش TRUE باشد، نتیجه AND مقدار TRUE می‌شود.

البته در اکسل AND را به صورت یک تابع و به شکل زیر داریم که می‌تواند تا ۱۲۷ ورودی از نوع TRUE/FALSE را بگیرد:

AND(    ,     ,    )

و در قسمت اول تابع IF اکسل، برای بررسی برقرار بودن چندین شرط از AND به شکل زیر استفاده می‌کنیم:

=IF( AND(    ,     ,    )  , ... , ... )

و فرمول عصرانه آزاده  و کاوه داستان ما اینگونه می‌شود:

=IF( AND( "gojeh" = "darim", "tokhm morg" = "darim ) , "omlet", "chay va panin" )

پرده دوم) دوره آموزشی خارج از کشور

من در شرکتی کار می‌کنم که قرار است کسانی که سابقه آن‌ها بیشتر از ۱۰ سال و همچنین سن آنها بین ۳۰ تا ۴۰ سال باشد را برای دوره‌‌ای ۶ ماهه آموزشی به خارج از کشور (فرض کنید فرانسه یا سوئیس) بفرستند و حالا می‌خواهم فرمولی را بنویسیم که مشخص کند در بین همه کارکنان چه کسانی هستند که این شرایط را دارند. (خوشبختانه آزاده و کاوه داستان ما این شرایط را دارند و مثل همیشه حسن هیچ کدامشان را ندارد):

قبل از شروع فرمول یک نکته مهم را باید به شما در مورد سن بین ۳۰ تا ۴۰ سال و نوشتن فرمول آن بگویم. معمولا در ریاضی برای بررسی آنکه مقداری مثل X که بین ۳۰ تا ۴۰ باشد، اینگونه می‌نویسیم:

30 < X < 40

اما در دنیای کامپیوتر این فرمول آنگونه که ما می‌خواهیم تفسیر نمی‌شود در تقریبا در همه زبان‌های برنامه نویسی که من می‌شناسم باید این فرمول را با ۲ شرط پیاده کنیم یعنی بنویسیم:

مقدار X بیشتر از ۳۰ باشد و «همچنین» مقدار X کمتر از ۴۰ باشد. 

بنابراین در فرمول نویسی اکسل از AND برای اینکار به شکل زیر باید استفاده کنیم:

= AND( X > 30 , X < 40)

حالا فرض کنید که اسامی افراد در ستون A سابقه افراد در ستون B اکسل و سن آن‌ها در ستون C اکسل تایپ شده است و نتیجه فرمول ما در ستون D مشاهده می‌شود:

  |   A     |  B   |  C   |  D
--+---------+------+------+-----
1 | KAVEH   |  13  | 35   | Yes
2 | AZADEH  |  10  | 31   | Yes
3 | HASSAN  |   5  | 60   | No
4 | BAHAR   |  13  | 44   | No
--+---------+------+------+-----

 فرمول ما برای بررسی اعزام دوره آموزشی خارج از کشور به شکل زیر خواهد شد:

=IF( AND( B1 > 10, C1 > 30 , C1 < 40) , "Yes", "No" )

این فرمول را اینگونه بخوانید:
اگر «سابقه فرد بیش از ۱۰ بود و همچنین سنش بیشتر از ۳۰ بود و همچنین سنش کمتر از ۴۰ بود» آنگاه «واجد شرایط هست/Yes» و گرنه «نیست/No»