if تو در تو در اکسل

استفاده از توابع چندگانه if در اکسل

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

مثال: فرض کنید که نمیخواهید فقط قبول شدن یا رد شدن دانش آموزان را بررسی کنید بلکه نمرات آنان را بعنوان “خوب”، “رضایتبخش” و “ضعیف” ارزیابی کنید. برای مثال،

خوب: 60 یا بیشتر (>=60)

رضایتبخش: بین 40 تا 60 (>40 and <60)

ضعیف: کمتر از 40 (<=40)

برای شروع میتوانید ستون جدیدی (D) ایجاد کنید که مقدار ستون های C,B را با هم جمع میکند: D: =C2+D2

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

=IF(E2>=60, 'خوب', IF(E2>40, 'ضعیف', 'رضایتبخش'))

همانطور که مشاهده میکنید، تنها یک تابع IF تو در تو کافی است. طبیعتاً، اگر لازم باشد میتوانید IF های بیشتری را در آن جایگذاری کنید. برای مثال،

=IF(E2>=70,'عالی',IF(E2>=60,'خوب',IF(E2>40,'ضعیف','رضایتبخش')))

فرمول بالا، یک شرط دیگر را نیز اضافه میکند، نمرات بزرگتر و مساوی 70 را بعنوان “عالی” در نظر میگیرد.

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

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

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

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

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

جایگزین هایی برای If چندگانه در اکسل

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

1. برای بررسی چند شرط، از LOOKUP, VLOOKUP, INDEX/MATCH یا CHOOSE استفاده کنید.
2. از ترکیب تابع IF با توابع منطقی OR / AND استفاده کنید.
3. از فرمول های آرایه، به همان شکلی که در مثال توضیح داده شد استفاده کنید.
4. از تابع 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 چندگانه آسان تر است.

علامت&:

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

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

از اینکه این مطلب را مطالعه کردید متشکریم.