توابع IF تو در تو و نکات مهمی که باید بدانید

حتما تا حالا به این موضوع برخورد کردید که موقع انتخاب و تصمیم گیری، بیش از ۲ خروجی داشته باشید. در آموزش های قبلی، بررسی شرط های چندگانه و گرفتن ۲ خروجی (به ازای برقرار بودن و برقرار نبودن شرط یا شروط) رو بررسی کردیم. حالا میخوایم بررسی شرط های مختلف (یک یا بیش از یک شرط) و گرفتن چند خروجی رو بررسی کنیم. برای این موضوع از IF های تو در تو (متداخل) یا Nested_If استفاده میکنیم. در واقع تابع خاصی نیست، صرفا نوع بکارگیری تابع IF اکسل در اینجا اهمیت داره که آرگومان های IF رو چطور تعیین کنیم. با این روش، میتونیم چندین حالت رو مقایسه کنیم و به ازای هر حالت یک خروجی داشته باشیم.

در این مقاله قصد داریم چند مثال از بکارگیری IFهای تو در تو رو بررسی کنیم و در هر مثال جزئیات فرمول رو به طور کامل توضیح میدیم. البته در ورژن ۲۰۱۹ تابع IFS اضافه شده که میتونید بجای IF تو در تو از این تابع استفاده کنید.

مثال ۱: تعیین وضعیت درسی دانش آموزان براساس نمره کسب شده

فرض کنید مطابق شکل ۱ لیستی از اسامی دانش آموزان به همراه نمرات آن ها داریم. می خواهیم دانش آموزان را براساس نمره کسب شده و معیارهای نمایش داده شده در شکل دسته بندی کنیم. همان طور که مشخص هست تعداد شرط ها بیش از یکی هست و باید برای تعیین وضعیت هر دانش آموز بر اساس نمره، از فرمولی با IFهای تو در تو استفاده کنیم. با توجه به شکل زیر که اسامی دانش آموزان در ستون A و نمرات در ستون B درج شده، در سلول C2 فرمول رو به شکل زیر می نویسیم:

=IF(B2>۱۸, “خیلی خوب”, IF(B2>=۱۷, “خوب”, IF(B2>۱۵, “قابل قبول”, “ضعیف”)))

شکل ۱- تعیین وضعیت دانش آموزان براساس نمره کسب شده – تابع IF اکسل

نحوه عملکرد فرمول:

قبل از توضیح فرمول، یکبار آرگومان های تابع IF رو با هم مرور می کنیم:

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

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

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

=IF(B2>18, "خیلی خوب",
     IF(B2>=17, "خوب",
         IF(B2>15, "قابل قبول", "ضعیف")))

در فرمول بالا logical_test مربوط به IF اول یعنی عبارت (B2>۱۸) چک میشه و در صورتی که خروجی logical_test عبارت TRUE باشه مقدار value_if_true یعنی عبارت “خیلی خوب” نمایش داده میشه و در صورتی که خروجی logical_test عبارت FALSE باشه IF دوم فعال می شه. در واقع IF دوم به جای value_if_false در IF اول تعریف میشه. در IF دوم هم در صورتی که logical_test یعنی عبارت (B2>=۱۷) درست باشه خروجی فرمول عبارت “خوب” خواهد بود و در غیر این صورت IF سوم فعال میشه. IF سوم به جای value_if_false در IF دوم تعریف شده.

مثال ۲: محاسبه قیمت نهایی براساس میزان کالای درخواستی

فرض کنید جدولی مطابق شکل زیر داریم که حاوی اطلاعاتی مربوط به قیمت کالا براساس تعداد هست. همان طور که در شکل زیر مشخص هست قیمت هر واحد کالا براساس تعداد تقاضا متفاوت هست. حالا میخواهیم با وارد کردن تعداد کالا مورد نظرمون، جمع مبلغی که باید بپردازیم رو محاسبه کنیم. با توجه به اینکه قیمت هر واحد کالا براساس تعداد کالای درخواستی متفاوت هست باید برای محاسبه قیمت هر واحد کالا از IF های تو در تو استفاده کنیم که فرمول به شکل زیر خواهد بود:

=B9*IF(B9>=101, 12, IF(B9>=50, 13, IF(B9>=20, 16, IF(B9>=11, 18, IF(B9>=1, 20, “”)))))

شکل ۲- محاسبه مبلغ کل با استفاده از IF های تو در تو

در فرمول بالا می تونیم به جای قرار دادن قیمت هر واحد کالا (اعداد ۲۰، ۱۸، ۱۶ و….) در فرمول، از آدرس سلول های حاوی این اعداد مطابق شکل ۳ استفاده کنیم. با این کار در صورتی که قیمت هر واحد کالا تغییر بکنه فرمول نیازی به ویرایش نداره.

= B9*IF(B9>=101, B6, IF(B9>=50, B5, IF(B9>=20, B4, IF(B9>=11, B3, IF(B9>=1, B2, “”)))))

شکل ۳- استفاده از آدرس سلول ها به جای مقادیر سلول ها در فرمول – تابع IF اکسل

در همین مثال اگر بخواهیم وقتی مقادیر کمتر از یک و یا بیشتر از ۲۰۰ وارد شد، خروجی فرمول عبارت “خارج از محدوده است” را نمایش بده و در غیر این صورت براساس محدوده های تعریف شده مبلغ نهایی را محاسبه بکنه، فرمول به شکل زیر تغییر می کنه:

=IF)OR(B9>۲۰۰,B9<۱), “خارج از محدوده است”, B9*IF(B9>=۱۰۱,۱۲, IF(B9>=۵۰, ۱۳, IF(B9>=۲۰, ۱۶, IF( B9>=۱۱, ۱۸, IF(B9>=۱, ۲۰, “”)))))(

شکل ۴- اضافه کرده شرط داده های خارج از محدوده – تابع IF اکسل

همان طور که در فرمول بالا مشخص هست در قسمت logical_test مربوط به IF اول از تابع OR استفاده کردیم و در قسمت value_if_false مربوط به IF اول از IF های تو در تو استفاده کردیم. (برا یآشنایی با منطق توابع OR/AND مقاله مربوط به شروط چندگانه ور مطالعه کنید.

نکات و ترفندهای IF های تو در تو

1. محدودیت های IF های تو در تو

تعداد توابع IF که می تونیم در یک فرمول با هم ترکیب کنیم در نسخه ای مختلف اکسل با هم متفاوته به طوری که در نسخه ۲۰۰۳ اکسل تنها امکان ترکیب ۷ تابع IF وجود داره در صورتی که این تعداد در نسخه های ۲۰۰۷، ۲۰۱۰، ۲۰۱۳ و ۲۰۱۶ به حداکثر ۶۴ تابع IF می رسه. در ویرایش ۲۰۱۹ هم محدودیتی برای ترکیب IF ها وجود نداره.

درسته که می تونیم برای مثال در نسخه ۲۰۱۶ اکسل ۶۴ تا IF رو با هم ترکیب کنیم ولی باید این موضوع رو هم در نظر داشته باشیم که ترکیب این تعداد تابع IF در یک فرمول هم باعث پیچیدگی پیش از حد فرمول میشه و فهم فرمول رو هم مشکل تر می کنه و هم اینکه عملکرد فرمول به خاطر چک کردن تعداد زیاد شرط ها کاهش پبدا می کنه.

2. ترتیب قرار گرفتن شرط ها در IF های تو در تو

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

=IF(B2>۱۸, “خیلی خوب”, IF(B2>=۱۷, “خوب”, IF(B2>۱۵, “قابل قبول”, “ضعیف”)))

در صورتی که نمره دانش آموزی ۱۹ باشه، با توجه به اینکه عبارت شرطی اول یعنی B2>۱۸ خروجی TRUE خواهد داشت در نتیجه دیگه باقی شرط ها چک نمیشه و خروجی فرمول عبارت “خیلی خوب” رو نمایش میده که کاملا درسته.

حالا فرض کنید ترتیب نوشتن شرط ها رو تغییر بدیم و فرمول رو به شکل زیر بنویسیم:

=IF(B2>۱۵, “قابل قبول”, IF(B2>=۱۷, “خوب”, IF(B2>۱۸, “خیلی خوب”, “ضعیف”)))

حالا اگر نمره دانش آموزی ۱۹ باشه ، فرمول در همون گام اول متوقف میشه و عبارت “قابل قبول” رو نمایش میده و دیگه باقی شرط ها کنترل نمی شن که این نتیجه درست نیست. پس متوجه شدیم که ترتیب نوشتن شرط ها چقدر می تونه در نتیجه نهایی تاثیرگذار باشه.

3. ارزیابی گام به گام عملکرد فرمول

برای اینکه عملکرد مرحله به مرحله یک فرمول IF تو در تو رو بررسی کنیم می تونیم از ویژگی Evaluate Formula که در تب Formula و زیرمجموعه Formula Auditing قرار گرفته استفاده کنیم. برای این کار کافیه روی سلول مورد نظر که فرمول در اون نوشته شده کلیک کنیم و بعد از مسیر گفته شده روی گزینه Evaluate Formula کلیک کنیم. پنجره Evaluate Formula مطابق شکل زیر نمایش داده میشه که فرمول موجود در سلول انتخابی در اون نمایش داده شده.

شکل ۵- Evaluate Formula

برای بررسی گام به گام فرمول روی دکمه Evaluate کلیک می کنیم. هر بار که روی دکمه Evaluate کلیک می کنیم یک بخش از فرمول بررسی و نتیجه نمایش داده میشه تا اینکه خروجی نهایی فرمول بدست میاد. در این حالت دکمه Evaluate به Restart تغییر پیدا می کنه و می تونیم از ابتدا تمام مراحل فرمول رو دوباره بررسی کنیم. برای روشن شدن موضوع به ویدئو زیر توجه کنید:

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

4. پرانتز ها در IF های تو در تو

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

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

در شکل زیر تفکیک پرانتزها با استفاده از رنگ ها نمایش داده شده. همان طور که در شکل ۶ مشخص هست به ازای هر پرانتزی که باز شده یک پرانتز هم با همون رنگ بسته شده.

شکل ۶- تفکیک پرانتز ها در فرمول با استفاده از رنگ

5. نمایش متفاوت متن و عدد در فرمول

وقتی داریم در قسمت logical_test تابع IF، شرط رو وارد می کنیم، باید دقت کنیم که نحوه نوشتن عدد و متن متفاوت هست. در logical_test متن باید بین دو تا کوتیشن (” “) نوشته بشه در صورتی که برای اعداد نیازی به کوتیشن نیست. برای مثال به دو فرمول زیر دقت کنید:

=IF(B2>۱۵, “قابل قبول”  ,…)     ——> طرز صحیح نوشتن

=IF(B2>”۱۵”, “قابل قبول”,…..) ——> طرز اشتباه نوشتن

در دو فرمول بالا در صورتی که مقدار سلول B2 عدد ۲۰ باشه، خروجی فرمول اول TRUE و خروجی فرمول دوم FALSE خواهد بود. در فرمول دوم چون عدد ۱۵ بین دو تا کوتیشن قرار گرفته، در نتیجه فرمول اون رو یک رشته عددی در نظر می گیره. برای همین هم خروجی FALSE رو نمایش میده.

6. استفاده از فاصله در IF های تو در تو برای خوانایی بهتر فرمول

وقتی که از IF های تو در تو در فرمول نویسی استفاده می کنیم بهتر هست برای گویاتر شدن فرمول بین توابع IF فاصله بذاریم. اینکار فهم فرمول رو خیلی راحت تر می کنه. اکسل نسبت به گذاشتن فاصله در فرمول حساس نیست پس می تونیم بدون هیچ نگرانی با گذاشتن فاصله در فرمول ها اون ها رو خوانا تر کنیم. برای اینکه در یک فرمول فاصله بذاریم، نشان گر موس رو در هر جایی که می خواهیم فرمول به خط بعد منتقل بشه نگه میداریم و کلید های ترکیبی Alt + Enter رو می زنیم و بعد نوار فرمول رو بزرگتر می کنیم تا فرمول در چند خط نمایش داده بشه. برای روشن شدن موضوع ویدئو زیر رو ببینید:

جایگزین های تابع IF اکسل به صورت تو در تو

با توجه به محدودیت هایی که در تعریف تعداد IF ها در نسخه های مختلف اکسل وجود داره و برای خلاصه تر کردن فرمول و افزایش سرعت عمل فرمول، می تونیم از موارد زیر به جای IF های تو در تو (بسته به منطق سوال) استفاده کنیم:

1. استفاده از تابع CHOOSE برای چک کردن شرط های مختلف و برگردوندن مقادیر مختلف؛
2. استفاده از یک جدول مرجع و تابع VLOOKUP که در اون از حالت approximate match استفاده شده؛
3. استفاده از توابع AND و OR در قسمت logical_test تابع IF؛
4. استفاده از فرمول نویسی آرایه ای،
5. ترکیب چند تابع IF با استفاده از تابع CONCATENATE؛
6. برای کاربران حرفه ای اکسل، بهترین جایگزین برای IF های تو در تو، تعریف یک تابع سفارشی با استفاده از VBA هست.

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

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

جهت دانلود فایل نمونه این آموزش، روی لینک زیر کلیک کنید: