IF تو در تو

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

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

برای دیدن لینک دانلود فایل تمرین های این نوشته در سایت ثبت نام کنید و وارد شوید.



	این محتوا فقط برای کاربران وارد شده محدود شده است.برای مشاهده این مطالب لطفاً  وارد شوید .

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

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

مثال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(B2>150,’رضایتبخش’,IF(B2>=200,’خوب’,IF(B2>249,’عالی’)))

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

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

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

فرض کنید بر اساس تعداد کالایی که میخرید؛ قیمت متفاوت خواهد بود. به عنوان مثال از خرید کالا از یک عدد تا 10 عدد 20000 ریال باشد. و برای 11 کالا تا 19 کالا 18000ریال باشد و … . و هدف شما این است که فرمول برای هر محدوده قیمت کل را بررسی کند. به عبارت دیگر، فرمول شما می بایست شرط های چندگانه را بررسی کند و بر اساس اینکه تعداد کالا در کدام محدوده قیمت قرار میگیرد؛ محاسبات انجام شود. تصویر زیر قیمت را برای هر محدوده خرید از کالا نشان میدهد.

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

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

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

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

این مثال صرفاً جهت آشنایی شما با این تابع بود. شما میتوانید از این تابع بر اساس آنچه مورد نیازتان است؛ استفاده کنید. برای مثال، به جای وارد کردن مستقیم قیمت در فرمول، میتوانید آدرس سلول هایی که این مقدارها درآن قرار گرفته است را در فرمول وارد کنید (سلول های 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 جدید زمانی که تعداد کالا بیشتر یا کمتر از حد بالا و پایینی که شما تعیین کرده اید؛ بود؛ پیام «خارج از محدوده» را نمایش دهد. برای این منظور فرمول زیر را مینویسیم:

=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 چندگانه خود و مصون ماندن از خطاهای احتمالی، سه نکته ساده زیر را در ذهن داشته باشید:

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

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

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

برای بررسی چند شرط، از LOOKUP, VLOOKUP, INDEX/MATCH یا CHOOSE استفاده کنید.
از ترکیب تابع IF با توابع منطقی OR / AND استفاده کنید. (تابع IF اکسل همراه با شرط های And/or چندگانه در اکسل، if های تو در تو و غیره)
از فرمول های آرایه، به همان شکلی که در مثال توضیح داده شد استفاده کنید.
از تابع 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’, ‘ضعیف ‘, ”))

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