تابع IF و AND در اکسل

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

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

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

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

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

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

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

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

شرط:

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

فرمول  IF/AND:

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

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

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

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

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

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

در این فرمول شرط گذاشته ایم که اگر سلول A2 مساوی صفر نبود؛ حال بررسی کن اگر 1 تقسیم بر A2 بزرگتر از 0.5 بود «خوب» و در غیر اینصورت «بد» را برگردان و اگر A2 مساوی صفر بود، «بد» را برگردان.

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

برای ترکیب تابع IF و OR نیز شبیه به نمونه قبل عمل میکنیم. تنها تفاوت موجود با ترکیب تابع IF/AND در این است که در اینجا اگر حداقل یکی از شرطها صحیح باشد؛ مقدار 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 نیز میتواند در فرمول های آرایه به کار گرفته شود. شما ممکن است زمانی که لازم است تمام عناصر یک آرایه مورد ارزیابی قرار گیرد به این ترکیب نیاز داشته باشید.

برای مثال، فرمول 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 طراحی شده است.

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

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

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

در زیر تابع IF تو در تو آمده است که اگر سلول 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(C2=0, "متأسفیم، خطایی رخ داده است.", B2/C2)

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