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

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

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

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

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

در ورژن های جدید اکسل (۲۰۰۷ به بعد)، میتوانید تا ۶۴ تابع IF را در یک فرمول قرار دهید و در ورژن ۲۰۰۳ اکسل این تعداد به ۷ تابع کاهش می یابد.

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

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

  • عالی: بیش از ۲۴۹
  • خوب: بین ۲۰۰ تا ۲۴۹
  • رضایتبخش: بین ۱۵۰ تا ۱۹۹
  • ضعیف: کمتر از ۱۵۰

حال، بر اساس معیارهای بالا یک فرمول 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,’عالی’)))

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

آموزش شرط تو رد تو

آموزش شرط تو رد تو

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

مثال۲: تابع 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, ”)))))

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

شرط تو در تو

شرط تو در تو

 

این مثال صرفاً جهت آشنایی شما با این تابع بود. شما میتوانید از این تابع بر اساس آنچه مورد نیازتان است؛ استفاده کنید. برای مثال، به جای وارد کردن مستقیم قیمت در فرمول، میتوانید آدرس سلول هایی که این مقدارها درآن قرار گرفته است را در فرمول وارد کنید (سلول های 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, ”))))))

شرط تو در تو

شرط تو در تو

 

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

خواندن این نوشته  کپی فرمت و شکل متن در ورد

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

نکات مهم

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

  1. در اکسل ۲۰۱۶-۲۰۰۷، میتوانید تا ۶۴ شرط را در تابع خود به کار گیرید. در دیگر ورژن های ۲۰۰۳ و ماقبل، میتوانید از ۷ تابع استفاده کنید.
  2. حواستان به ترتیب شرط ها در تابع باشد. در صورتی که شرط اول صحیح باشد، شرط های بعدی مورد بررسی قرار نمیگیرد.
  3. در صورتی که فرمول شما بیش از ۵ تابع IF چندگانه در خود دارد، میتوانید آن را با یکی از جایگزین هایی که در زیر معرفی شده است جایگزین کنید.

 

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

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

  1. برای بررسی چند شرط، از LOOKUP, VLOOKUP, INDEX/MATCH یا CHOOSE استفاده کنید.
  2. از ترکیب تابع IF با توابع منطقی OR / AND استفاده کنید. (تابع IF اکسل همراه با شرط های And/or چندگانه در اکسل، if های تو در تو و غیره)
  3. از فرمول های آرایه، به همان شکلی که در مثال توضیح داده شد استفاده کنید.
  4. از تابع CONCATENATE یا علامت & استفاده کنید.

مثال دیگر از توابع اکسل تابع  CONCATENATE است. این تابع میتواند تا ۳۰ پارامتر را در ورژن های قدیمی و تا ۲۵۵ پارامتر در ورژن های ۲۰۰۷ به بعد اکسل قبول کند. و این به معنای بررسی کردن ۲۵۵ شرط است.

برای مثال، برای برگرداندن نتایج مختلف بر اساس مقادیری که در سلول 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’, ‘ضعیف ‘, ”)

شرط تو در تو

شرط تو در تو

قبل از آموزش این بخش، نوشته IF را بخوانید.

با تشکر امید دیده گاه

 

این نوشته را به اشتراک بگذارید

نوشتهٔ پیشین
تابع SUM
نوشتهٔ بعدی
پنهان و آشکار ساختن نوار ریبون در Microsoft word 2016

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

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

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

امید دیده گاه
فهرست