تابع If در دستورات تو در تو در اکسل

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

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

در ورژن های جدید اکسل (2007 به بعد)، میتوانید تا 64 تابع IF را در یک فرمول قرار دهید و در ورژن 2003 اکسل این تعداد به 7 تابع کاهش می یابد.

اگر میخواهید به صورت عمیق با تابع if در اکسل آشنا شوید مطالعه مطالب زیر که شامل مثال های کابردی تابع if هست توصیه می کنیم:

استفاده از تابع IF: فرمول برای اعداد، متن، تاریخ، و سلول های خالی

تابعIF همراه با شرط های And/or چندگانه در اکسل، if های تو در تو و غیره [کلیک کنید]

تابع if در اکسل – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

مالیات حقوق و دستمزد-if-vlookup در اکسل (فیلم آموزشی)

ادغام فرمول هایvkooup,choose,iferror,if,and (فیلم آموزشی)

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

نمونه تابع IF تو در تو کلاسیک در اکسل
فرمول IF چندگانه همراه با محاسبات ریاضی
تابع IF تو در تو – نکات مهم
جایگزین های IF چندگانه در اکسل

مثال1: فرمول IF تو در تو چند شرطی کلاسیک

فرض کنید، لیستی از دانش آموزان در ستون A و نمرات آنان در ستون B دارید و میخواهید نمرات آنان را با شرایط زیر طبقه بندی کنید:

عالی: بیش از 249
خوب: بین 200 تا 249
رضایتبخش: بین 150 تا 199
ضعیف: کمتر از 150

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

=IF(B2>249, "عالی",IF(B2>=200,"خوب", IF(B2>150,"رضایتبخش","ضعیف ")))

و تصویر زیر نشان میدهد که به درستی کار میکند:

فهم منطق تابع if تو در تو اکسل با چند شرط

از بعضی افراد می شنویم که میگویند تابعif چندگانه باعث گیج شدن آنها میشود. سعی کنید به این تابع از زاویه ای دیگر نگاه کنید:

=if(b2>249,"عالی"),If(b2>=200,"خوب",If(b2>150,"رضایتبخش")))

این تابع به اکسل میگوید که logical test تابع اول را بررسی کند، در صورتی که شرط صحیح باشد، مقدار موجود در value if true را برگرداند. در صورتی که شرط تابع اول صحیح نباشد، تابع دوم را بررسی کند و الی آخر.

IF(check if B2>=249, if true – return “عالی”, or else

IF(check if B2>=200, if true – return “خوب”, or else

IF(check if B2>150, if true – return “رضایتبخش”, if false –

Return”ضعیف”)))

ترتیب شرط ها در if تو در تو مهم است

چند پاراگراف بالاتر، اشاره کردیم که مهترین شرط می بایست در ابتدا آورده شود، ممکن است از خود بپرسید که چرا؟ دلیل این کار این است که تابع if شرط های را بر اساس ترتیبی که در فرمول آورده میشود بررسی میکند و زمانی که یکی از شرط های صحیح باشد، شرط بعدی بررسی نمیشود.

حال، این مطالب را به صورت عملی توضیح میدهیم. در صورتی که تابع IF چندگانه بالا را به صورت برعکس بنویسید، عمل نخواهد کرد. اکسل شروع به بررسی فرمول و کوچکترین شرط که در ابتدا آورده شده است میکند و برای هر عدد بالاتر از 150 واژه “رضایتبخش” را نمایش میدهد و به این ترتیب این تابع شرط های “خوب” و “عالی” را بررسی نمیکند. بنابراین در زمان نوشتن تابعIF تو در تو خود به یاد داشته باشید که ترتیب بسیار اهمیت دارد.

مثال2: تابع IF چندگانه یا تو در تو همراه با محاسبات ریاضی

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

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

با فرض اینکه کاربر تعداد کالا را در سلول B8 وارد میکند فرمول به شکل زیر خواهد بود:

=B8*IF(B8>=101, 12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, "")))))

و نتیجه چیزی شبیه به تصویر زیر خواهد شد:

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

برای مثال، به جای وارد کردن مستقیم قیمت در فرمول ، میتوانید آدرس سلول هایی که این مقدارها درآن قرار گرفته است را در فرمول وارد کنید (سلول های B2 تا B6). این کار به کاربران شما اجازه میدهد که منشاء داده ها را اصلاح کنند بدون اینکه لازم باشد فرمول را تغییر دهند:

=B8*IF(B8>=101,B6, IF(B8>=50, B5, IF(B8>=20, B4, IF( B8>=11, B3, IF(B8>=1, B2, "")))))

یا ممکن است بخواهید تابع if را به فرمول خود اضافه کنید که حد بالایی، پایینی یا هردو حد محدوده مقداری را اصلاح کند. زمانی که مقدار خارج از محدوده است، فرمول یک پیام “خارج از محدوده” را نشان میدهد:

=IF(OR(B8>200,B8<1), مقدار. خارج از محدوده"", B8*IF(B8>=101,12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, ""))))))

فرمول هایی که در بالا توضیح داده شده در تمام ورژن های 2000 تا 2016 اکسل کار میکند. در اکسل 2016 که قسمتی از office 365 است میتوانید برای انجام این کارها از تابع IFS استفاده کنید.

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

تابع if تو در تو اکسل با چند شرط – نکات مهم

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

در اکسل 2016-2007، میتوانید تا 64 شرط را در تابع خود به کار گیرید. در دیگر ورژن های 2003 و ماقبل، میتوانید از 7 تابع استفاده کنید.
حواستان به ترتیب شرط ها در تابع باشد. در صورتی که شرط اول صحیح باشد، شرط های بعدی مورد بررسی قرار نمیگیرد.
در صورتی که فرمول شما بیش از 5 تابع IF تو در تو در خود دارد، میتوانید آن را با یکی از جایگزین هایی که در زیر معرفی شده است جایگزین کنید.

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

برای کنار گذاشتن محدودیت 7 تابع if تو در تو در ورژن های 2007 و ماقبل اکسل و همچنین برای فشرده تر کردن و خلاصه کردن فرمول خود، میتوانید از یکی از توابع در زیر معرفی شده به جای IF با شرط های چندگانه استفاده کنید.

برای بررسی چند شرط، از LOOKUP, VLOOKUP, INDEX/MATCH یا CHOOSE استفاده کنید.
از ترکیب تابع IF با توابع منطقی OR / AND استفاده کنید.
از فرمول های آرایه، به همان شکلی که در مثال توضیح داده شد استفاده کنید.
از تابع CONCATENATE یا علامت & استفاده کنید.

مثال دیگر از توابع اکسل تابع CONCATENATE میتواند تا 30 پارامتر را در ورژن های قدیمی و تا 255 پارامتر در ورژن های 2007 به بعد اکسل قبول کند که این به معنی بررسی کردن 255 شرط است.

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

IF تو در تو:

=IF(B2>249,"عالی",IF(B2>=200,"خوب",IF(B2>150,"رضایتبخش","ضعیف ")))

تابع CONCATENATE:

=CONCATENATE(IF(C1="a", "عالی", ""), IF(C1="b", "خوب", ""), IF(C1="c", "ضعیف ", ""))

علامت&

=IF(B2="a", "عالی", "") & IF(B2="b", "خوب", "") & IF(B2="c", "ضعیف ", "") & IF(B2="d", "ضعیف ", "")

همانطور که در تصویر زیر می بینید، استفاده از تابع concatenate فرمول را خلاصه تر نمیکند، ولی در مقایسه با IF تو در تو، فهم آن ساده تر است.

برای کاربران قوی اکسل، بهترین جایگزین برای تابع IF تو در تو، ساخت یک کاربرگ با استفاده از VBA است.

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

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

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33, IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44, IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))

به عنوان یک "قاعده سرانگشتی" کلی، اگر فرمولی با بیش از 7 دستور تو در تو دارید، باید به جای آن از یک تابع VBA استفاده کنید. با این حال، اگر نمی خواهید از VBA استفاده کنید، می توانید با ایجاد یک نام تعریف شده که به بخشی از فرمول اشاره دارد، این محدودیت را برطرف کنید. از آنجایی که فرمول های تعریف شده به طور جداگانه ارزیابی می شوند، می توانید یک یا چند فرمول تعریف شده داشته باشید که به فرمول های بزرگ اشاره دارد و آنها را در یک فرمول "مستر" ترکیب کنید.

IF A4 = 1 Then 11
Else If   A4 = 2 Then 22
Else If   A4 = 3 Then 33
Else If   A4 = 4 Then 44
...
Else If   A4 = 13 Then 130  Else "Not Found"

و غیره البته، به عنوان یک موضوع عملی، بهتر است از VLOOKUP برای انجام این کار استفاده کنیم، اما این برای مثال کار خواهد کرد. ابتدا یک فرمول به نام OneToSix با اشاره به فرمول ایجاد می کنیم:

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33, IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44, IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))

سپس فرمول دیگری به نام SevenToThirteen ایجاد کنید و به فرمول مراجعه کنید:

=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99, IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120, IF(Sheet1!$A$4=13,130,"NotFound")))))))

در نهایت، فرمول "master" را در سلول کاربرگ وارد کنید:

=IF(OneToSix,OneToSix,SevenToThirteen)

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

هر زمان که از حد مجاز توابع تو در تو فراتر رفتید، می توانید از این تکنیک استفاده کنید