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

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

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

• تابع IF همراه با شرط های AND/OR چندگانه
• استفاده از IF های تو در تو در اکسل
• نحوه استفاده از تابع IF در فرمول های آرایه
• استفاده از تابع IF همراه با دیگر توابع در اکسل
• تابع IF در مقابل توابع IFERROR و IFNA

نحوه استفاده از تابع IF همراه با شرط های چندگانه

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

تابع AND – در صورتی که آزمون منطقی تابع شما شامل یک تابع AND باشد، اگر تمام شرط ها صحیح باشد، اکسل TRUE را برمیگرداند؛ در غیر این صورت FALSE برگردانده میشود.

تابع OR – زمانی که از تابع OR در LOGICAL TEST خود استفاده کنید، اگر یکی از شرط ها صحیح باشد اکسل TRUE را برمیگرداند در غیر این صورت FALSE برگردانده میشود.

برای درک بهتر این موضوع، به بررسی چند مثال میپردازیم.

مثال1: استفاده از توابع IF و AND در اکسل

فرض کنید که جدولی از نمره های دو آزمون در اختیار دارید. نمره اول که در ستون C قرار دارد می بایست بیشتر یا مساوی 20 باشد. نمرات آزمون دوم که در ستون D قرار دارد می بایست بزرگتر یا مساوی 30 باشد. فقط در صورتی که دو شرط بالا برقرار باشد، یک دانش آموز قبول میشود.

آسانترین راه برای نوشتن فرمول این است که ابتدا شرط ها را بنویسیم و سپس آن را در قسمت logical test فرمول خود جاگذاری کنیم.

شرط:

=AND(B2>=20, C2>=30)

فرمول  IF/AND:

=IF((AND(C2>=20, D2>=30)), "قبول", "رد")

آسان بود. اینطور نیست؟ فرمول به اکسل میگوید که در صورتی که مقدار درون ستون C بزرگتر یا مساوی20 باشد و مقدار درون ستون D بزرگتر یا مساوی 30 باشد کلمه “قبول” را برگرداند و در غیر اینصورت کلمه “رد” را برگرداند. تصویر زیر صحت فرمول ما را نشان میدهد:

نکته: اکسل تمام شرطها را در تابع AND بررسی میکند حتی اگر یکی از شرطهای بررسی شده FALSE باشد. برای مثال، فرمول

 =IF(AND(A2<>0,(1/A2)>0.5),"خوب" "بد")

در صورتی که سلول A2 برابر صفر باشد پیام (#DIV/0!) “خطای تقسیم بر صفر” را برمیگرداند. برای جلوگیری از این مشکل، می بایست از یک IF تو در تو استفاده کرد:

=IF(A2<>0, IF((1/A2)>0.5, "خوب", "بد"), "بد")

مثال2: استفاده از تابع IF همراه با تابع OR در اکسل

برای ترکیب تابع IF و OR نیز شبیه به نمونه قبل عمل میکنیم. تنها تفاوت موجود با ترکیب تابع IF/AND در این است که در اینجا اگر حداقل یکی از شرطها نیز صحیح باشد تابع OR مقدار TRUE را برمیگرداند.

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

=IF((OR(C2>=20, D2>=30)), "قبول", "رد")

در اینجا اگر نمره اول بزرگتر یا مساوی 20 باشد یا نمره دوم بزرگتر یا مساوی 30 باشد تابع مقدار “قبول” را برمیگرداند.

همانطور که در تصویر زیر می بینید، دانش آموز ما شانس بیشتری برای قبول شدن در آزمون پایانی دارد (امین به دلیل بدشانسی و کم آوردن فقط 1 نمره رد شد)

طبیعتاً شما حتماً نباید از یکی از این دو تابع در LOGICAL TEST خود استفاده کنید. مجموعه زیادی از فرمول ها وجود دارد که میتوانید بر اساس نیاز کسب و کار خود آنها را در فرمول IF خود جاگذاری نمائید، به شرطی که:

• در ورژن های 2007 به بعد اکسل فرمول شما بیش از 255 آرگومان نداشته باشد و طول فرمول بیش از 8192 کاراکتر نداشته باشد.
• در اکسل 2003 و پائینتر فقط میتوانید 30 پارامتر در فرمول خود داشته باشید و طول فرمول شما نباید از 1024 کاراکتر بیشتر باشد.

مثال3: استفاده از تابع IF به همراه توابع AND , OR

در شرایطی که می بایست داده ها خود را بر اساس چند مجموعه از شرط ها بررسی کنید، می بایست از تابع AND و OR به صورت همزمان استفاده کنید.

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

شرط ا: ستون C>=20 و ستون D>=25

شرط2: ستون C>=15 و ستون  D>=20

در صورتی که هرکدام از شرطهای بالا صحیح باشد، آزمون نهایی قبول شده در نظر گرفته میشود و در غیر اینصورت رد میشود.

ممکن است فرمول کمی پیچیده به نظر برسد ولی اگر به دقت به آن نگاه کنید اینطور نیست. فقط کافیست دو شرط را به صورت فرمول AND بیان کنید، و آنها را از طریق فرمول OR ارائه کنید زیرا شما به صحیح بودن هر دو شرط نیاز ندارید:

=OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)

در آخر، تابع OR بالا را در logical test جاگذاری کنید و مقدار value if true و value if false را مشخص کنید. در نتیجه، به فرمول زیر می رسید که شرط های And و OR چندگانه دارد:

=IF(OR(AND(C2>=20,D2>=25),AND(C2>=15,D2>=20)), "قبول", "رد")

تصویر زیر درستی فرمول ما را نشان میدهد:

استفاده از توابع چندگانه 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 را بعنوان “عالی” در نظر میگیرد.

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

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

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

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

=SUM(IF(A1:A5<=1,1,2))

فرمول برای هر مقدار در ستون A یک امتیاز در نظر می گیرد؛ اگر مقدار کوچکتر یا مساوی 1 باشد،امتیاز 1، و در صورتی که بزرگتر از 1 باشد امتیاز 2 را به آن تخصیص میدهد. و سپس فرمول SUM مقادیر 1 و 2 را با هم جمع میکند. این مسئله در تصویر زیر توضیح داده  شده است:

 

نکته: از آنجایی که این فرمول یک فرمول آرایه ای است، به یاد داشته باشید که کلیدهای Ctrl + Shift + Enter را فشار دهید.

استفاده از تابع if به همراه دیگر توابع در اکسل

در این مطلب در مورد نمونه های تابع if در ترکیب با توابع AND  و OR  بحث کردیم. حال، به دیگر توابع اکسل که میتوانند با تابع IF ترکیب شوند میپردازیم و مزیت هرکدام را توضیح میدهیم.

مثال1: استفاده از IF همراه با SUM، AVERAGE، MIN و MAX

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

ولی اگر جدول شما ساختار تعریف شده ای داشته باشد که امکان تغییر در آن وجود ندارد، چه باید کرد؟ در این مورد، به جای اضافه کردن یک ستون کمکی، میتوانید مقادیر را مستقیماً در فرمول خود وارد کنید:

=IF((C2+D2)>=60,"خوب", IF((C2+D2)=>40, "ضعیف","رضایتبخش "))

خوب است، ولی اگر جدول شما تعداد زیادی نمرات داشته باشد چه اتفاقی می افتد، برای مثال، 5 ستون مختلف یا بیشتر؟ اضافه کردن این مقادیر به صورت مستقیم در فرمول آن را به شدت بزرگ میکند. راه حل مناسب تر جایگذاری کردن تابع SUM در قسمت logical test تابع if  است:

=IF(SUM(C2:F2)>=120,"خوب",IF(SUM(C2:F2)>=90,"ضعیف", "رضایتبخش"))

به همین شیوه، میتوانید دیگر فرمول ها را نیز در قسمت logical test فرمول خود قرار دهید:

تابع If و average:

=IF(AVERAGE(C2:F2)>=30,"خوب",IF(AVERAGE(C2:F2)>=25,"ضعیف","رضایتبخش "))

این فرمول در صورتی که میانگین نمرات موجود در ستون C تا F بزرگتر یا مساوی 30 باشد”خوب” را برمیگرداند. در صورتی که میانگین بین 25 تا 29 باشد “رضایتبخش” برگردانده میشود و در صورت اینکه نمرات کمترا از 25 باشد “ضعیف” برگردانده میشود.

تابع if به همراه max,min

برای پیدا کردن بزرگترین و کوچکترین نمرات، میتوانید به ترتیب از توابع MAX و MIN استفاده کنید. با توجه به اینکه ستون F معادل مجموع نمرات است، فرمول های زیر این کار را انجام میدهند:

MAX: =IF(F2=MAX($F$2:$F$10), "بهترین نتیجه", "")

MIN: =IF(F2=MIN($F$2:$F$10), " بدترین نتیجه", "")

در صورتی که نمرات بزرگتر و کوچکتر شما در یک ستون قرار داشته باشد، میتوانید یکی از توابع بالا را در دیگری جایگذاری کنید. برای مثال،

=IF(F2=MAX($F$2:$F$10),"بهترین نتیجه",IF(F2=MIN($F$2:$F$10), "بدترین نتیجه ", ""))

به همین صورت، میتوانید تابع IF را همراه با دیگر توابع در کاربرگ خود مورد استفاده قرار دهید. برای مثال، میتوانید این تابع را همراه با توابع  GetCellColor / GetCellFontColor مورد استفاده قرار دهید تا بر اساس رنگ سلول نتایج متفاوتی بدست آورید.

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

برای مثال، به منظور شمارش تعداد یک مقدار بر اساس شرط یا شرایط چندگانه، میتوانید از توابع COUNTIF و COUNTIFS استفاده کنید. برای یافتن مجموع چند مقدار بر اساس شرط یا شرط های مختلف می توانید به ترتیب از SUMIF و SUMIFS استفاده کنید و در نهایت برای بدست آوردن میانگین بر اساس یک یا چند شرط توابع AVERAGEIF و AVERAGEIFS طراحی شده است.

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

• نحوه استفاده از countif در اکسل
• استفاده از توابع countif و countifs با شرط های چندگانه
• تابع sumif در اکسل – مثالهای فرمولی برای بدست آوردن مجموع سلولها به صورت مشروط
• نحوه استفاده از توابع sumif و sumifs با شرط های چندگانه

مثال2: تابع IF همراه با ISNUMBER و ISTEXT

تاکنون شیوه مشخص کردن سلول های خالی و غیر خالی را با استفاده از تابع ISBLANK فراگرفته ایم.

اکسل توابع مشابه ای را فراهم کرده است که مقادیر متن و عدد را شناسایی میکند – به تریتب تابع های ISTEXT و ISNUMBER

در زیر تابع IF تو در تو آمده است که اگر سلول B1 شامل مقدار متنی باشد”متن” را برمیگرداند و در صورتی که این سلول شامل عدد باشد”عدد” برگردانده می شود.

=IF(ISTEXT(B1),"متن",IF(ISNUMBER(B1),"عدد",IF(ISBLANK(B1), "خالی", "")))

نکته:توجه داشته باشید که فرمول بالا مقدار “عدد” را هم برای عدد و هم برای تاریخ برمیگرداند. دلیل این اتفاق این است که اکسل تاریخ را به صورت عدد ذخیره میکند. به این صورت که 1ژانویه سال 1900 را بعنوان 1 ذخیره میکند.

مثال3: استفاده از نتایج برگردانده شده توسط تابع IF بوسیله یک تابع دیگر

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

مثال زیر نحوه استفاده از تابع CONCATENATE را همراه با تابع IF نشان داده است:

=CONCATENATE("کاری که انجام داده اید",IF(C1>5,"خوب است ", "عالی است"))

برای درک بهتر فرمول بالا به تصویر زیر دقت کنید:

تابع if در مقابل iferror و ifna

توابع IFERROR و IFNA برای به دام انداختن خطا در فرمول و جایگزین کردن آن با دیگر محاسبات، مقادیر از پیش تعیین شده یا پیام متنی به کار گرفته میشود. در ورژهای قدیمی اکسل، میتوانید از ترکیب های  IF ISERROR و  IF ISNA به جای این دو تابع استفاده کنید.

تفاوت این دو در این است که توابع IF ISERROR و  IF ISNA هر گونه خطایی را شناسایی میکنند ولی توابع IFERROR و IFNA فقط خطای  #N/A را شناسایی میکنند.

نمونه فرمول IFERROR در زیر آمده است:

=IFERROR(A2/B2, "متأسفیم، یک خطا رخ داده است.")

همانطور که در تصویر بالا قابل مشاهده است، ستون C نتیجه تقسیم ستون A را بر B نشان میدهد. همچنین میتوانید مشاهده کنید که در سلول های C2 تا C5 خطا رخ داده است. دلیل این خطا این است که هیچ عددی را نمیتوان بر صفر تقسیم کرد.

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

=IF(B2=0, "متأسفیم، یک خطا رخ داده است.", A2/B2)