تابع IFS

تابع IFS در اکسل، به عنوان یک تابع منطقی، قادر است چندین شرط را به صورت همزمان بررسی کند.

گاهی استفاده از دستور IF ساده کافی نیست و برای حل مسائل پیچیده‌تر نیاز به ترکیب چندین IF تودرتو (Nested) داریم.

در این مواقع، تابع IFS می‌تواند جایگزین مناسبی باشد و کمک می‌کند تا فرمول‌های منطقی خود را کوتاه‌تر و خواناتر کنید.

✅ به طور دقیق‌تر، عملکرد تابع IFS بر این اساس است که:

1. ابتدا چندین عبارت منطقی را به همراه مقادیر متناظر با آن‌ها دریافت می‌کند و
2. سپس عبارات را یکی یکی و به ترتیب بررسی می‌کند.
3. و در اولین جایی که عبارت منطقی درست بود، مقدار متناظر با آن شرط صحیح را برمی‌گرداند.

از آنجا که تابع IFS در واقع ترکیب چند تابع IF است، پس بهتر است قبل از ادامه‌ی این مقاله، با تابع IF آشنایی داشته باشید.

نحوه نوشتن تابع IFS

ساختار تابع IFS به این شکل است:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

در این تابع، ورودی‌ها به شرح زیر است:

logical_test1: عبارت منطقی اول.

value_if_true1: مقداری که باید در صورت برقراری عبارت منطقی اول بازگردانده شود.

logical_test2: (اختیاری) عبارت منطقی دوم.

value_if_true2: (اختیاری) مقداری که باید در صورت برقراری عبارت منطقی دوم بازگردانده شود.

… سایر ورود‌ی‌ها: (اختیاری) سایر عبارات منطقی و مقادیر متناظر بعدی (تا حداکثر 127 جفت ورودی)

در تابع IFS، قرار است صحت عبارت های منطقی وارد شده تست شود.
برای همین است که در مراجع آموزشی (از جمله مایکروسافت) به این عبارت‌های منطقی می‌گویند: "تست منطقی" یا logical_test.
همچنین به ورودی بعد از عبارت منطقی می‌گویند: value_if_true که ترجمه کلمه به کلمه آن می‌شود: "مقدار_اگر_درست" یا "مقدار خروجی تابع اگر عبارت منطقی درست باشد").

البته برای راحتی می‌توان به جای این دو می‌توان گفت: test و value.

اگر ورودی‌ها و خروجی تابع IFS را به شکل یک تابع ریاضی به تصویر بکشیم، چنین خواهد بود:

در نوشتن تابع IFS، ترتیب ورودی‌ها اهمیت دارد زیرا این تابع به دنبال اولین جایی است که مقدار عبارت منطقی TRUE شود. پس ما باید منطق مسئله خودمان را به طور کامل بدانیم و بتوانیم ترتیب عبارت‌های منطقی را طراحی کنیم، سپس اقدام به نوشتن تابع IFS کنیم.

پس توجه داشته باشید که:

• ورودی‌های تابع IFS به صورت جفت وارد می‌شود (test و value)
• دو ورودی (جفت) اول اجباری هستند.
• تابع IFS حداکثر تا تعداد 127 جفت ورودی‌هایی (یک تست منطقی و یک مقدار) که تابع IFS پشتیبانی می‌کند.
• اگر در بین جفت ورودی‌های تابع، چندین تست (عبارت منطقی) به طور همزمان درست باشد، برای IFS مهم ترتیب وارد کردن ورودی‌هاست. یعنی IFS به دنبال اولین عبارت منطقی است که TRUE باشد.
• ووردی‌های تابع می‌تواند مشابه تابع IF است یعنی می‌توان از مقادیر ثابت، ارجاع به سلول یا محدوده و یا نتیجه یک فرمول دیگر استفاده کرد.

در ادامه از طریق مثال‌، بیشتر با این تابع آشنا می‌شوید. در ابتدا بیایید ببینیم هنگام کار با IFS چطور فکر می‌کنیم.

مسیر ذهنی ما هنگام فرمول‌نویسی تابع فرضی IFS

? در اینجا می‌خواهیم کمی نحوه فکر کردن هنگام نوشتن تابع IFS را با هم مرور کنیم.

اولاً همانطور که گفتیم، در اکسل به جای نوشتن IFهای تودرتو، می‌توان از IFS استفاده کرد. یعنی می‌توانیم فرمولی که که شامل چندین تابع IF (چندین سطح، لایه یا شرط) و به شکل تودرتو نوشته شده است را با یک IFS جایگزین کنیم. بنابراین ساده‌تر شدن و خوانایی فرمول، ? انگیزه نوشتن تابع IFS است.

✅✅ حالا فرض کنید یک تابع IFS می‌خواهیم بنویسیم که سه لایه شرطی دارد (یعنی معادل سه سطح از شکل تودرتو است).

پس 3 تا آزمون عبارت منطقی نیاز داریم و برای نوشتن تابع، 6 تا ورودی خواهیم داشت:
1️⃣ عبارت (یا تست) منطقی اول و 2️⃣ مقدار تابع در صورت درستی عبارت منطقی اول
3️⃣ عبارت (یا تست) منطقی دوم و 4️⃣ مقدار تابع در صورت درستی عبارت منطقی دوم
5️⃣ عبارت (یا تست) منطقی سوم و 6️⃣ مقدار تابع در صورت درستی عبارت منطقی سوم

ما می‌توانیم ورودی‌های تابع IFS خود را به شکل زیر در ذهن خودمان نام‌گذاری کنیم (آن طور که منابع رسمی نام‌گذاری می‌کنند):

logical_test1 و value_if_true1
logical_test2 و value_if_true2
logical_test3 و value_if_true3

? البته در اینجا برای سادگی به جای logical_test و value_if_true بگوییم: test و value.

در نتیجه ترکیب این ورودی‌ها، تابع مورد نظر ما چنین خواهد بود:

=IFS( test1, value1, test2, value2, test3, value3)

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

=IFS(
test1, value1,
test2, value2,
test3, value3)

✅ شکستن خطوط در هنگام نوشتن تابع را با فشردن همزمان دو کلید Alt و Enter انجام می‌دهیم.

تا اینجا با ?مسیر ذهنی نوشتن این تابع آشنا شدید. در ادامه مثال‌هایی بیشتر از کاربردهای تابع IFS می‌بینید.

تعیین علامت اعداد با توابع IF و IFS

ابتدا یادآوری می‌کنیم که عبارت‌های شرطی را می‌توان با عملگرهای شرطی (مانند <>=) یا ترکیب با سایر توابع منطقی (مانند AND) نوشت.

حالا فرض کنید در ستون A تعدادی عددی داریم و می‌خواهیم برای آن برچسب مناسبی را تعیین کنیم.

1. اگر عدد مثبت باشد، برچسب "مثبت" یا "Positive"،
2. اگر منفی باشد برچسب "منفی" یا "Negative"،
3. و اگر صفر باشد برچسب "صفر" یا "Zero" نمایش دهیم.

? ما در اینجا فرمول را برای اولین سلول A1 می‌نویسیم و شما می‌توانید آن را برای بقیه سلول‌های همان ستون کپی کنید.

پیاده‌سازی با IF تودرتو (2 سطحی)

اگر بخواهیم این موضوع را با IF پیاده‌سازی کنیم، چنین خواهیم داشت:

=IF(   A1 > 0, "Positive",
    IF(A1 < 0, "Negative","Zero")
   )

بیایید فرمول فوق را خط به خط بررسی کنیم:

✅خط اول: این خط بررسی می‌کند که آیا مقدار سلول A1 مثبت است یا خیر. اگر اینطور باشد، فرمول "Positive" را بازمی‌گرداند و اگرنه به خط بعدی می‌رود و وارد IF سطح بعدی می‌شود.
✅خط دوم: ما وارد IF سطح بعدی شده‌ایم و پس از پرانتز ")" ما ورودی‌های این سطح از IF را داریم. کل این خط بررسی می‌کند که آیا مقدار موجود در سلول A1 منفی است یا خیر. اگر اینطور باشد، فرمول "Negative" را بازمی‌گرداند و اگرنه مقدار پیش‌فرض "Zero" را بازمی‌گرداند. در اینجا با بسته شدن پرانتز "(" این سطح از IF بسته می‌شود.
✅خط سوم: در اینجا ما با قرار دادن علامت "(" در واقع داریم IF سطح اول را می‌بندیم.

البته کل آنچه گفته شد، می‌تواند در یک خط هم آورده شود.

=IF( A1 > 0, "Positive", IF(A1 < 0, "Negative","Zero"))

پیاده‌سازی با IFS

در اینجا می‌خواهیم به جای IF (تودرتو)، از تابع IFS استفاده کنیم. در نظر داشته باشید که ما باید منطق مسئله خودمان را بدانیم و ترتیب ورودی‌ها (test و value) را خودمان طراحی کرده باشیم.

در سلولی دیگر از کاربرگ فرمول زیر را می‌نویسیم:

=IFS(A1>0,"Positive",A1<0,"Negative",A1=0,"Zero")

حالا فرض کنید که در سلول A1 عدد صفر قرار گرفته است. بیایید فرمول فوق را مرحله به مرحله بررسی کنیم:

وارد زیر مراحلی است که در پشت صحنه تا رسیدن به جواب طی می‌شود:

1. در ابتدا، تابع IFS عبارت منطقی A1>0 را بررسی می‌کند. از آنجایی که مقدار سلول A1 صفر است، عبارت منطقی A1>0 نادرست است. بنابراین، تابع IFS به عبارت منطقی بعدی می‌رود.
2. سپس تابع IFS سپس عبارت منطقی A1<0 را بررسی می‌کند. مجدداً چون A1 صفر است، عبارت منطقی A1<0 نیز نادرست است. بنابراین، تابع IFS به عبارت منطقی بعدی می‌رود.
3. حالا تابع IFS عبارت منطقی A1=0 را بررسی می‌کند و چون عبارت منطقی A1=0 درست است، مقدار "Zero" را برمی‌گرداند.

همان طور که می‌بینید با استفاده از تابع IFS در فرمول‌نویسی، دیگر نگران پرانتزها نیستیم؛ برخلاف IF.

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

? حالا پس از نوشتن فرمول برای یک سلول، می‌توانیم آن را برای سایر سلول‌ها (اعدادی که در همان ستون هستند) کپی کنیم.

کاربرد مقدار پیش‌فرض در تابع IFS

◀️ بسته به موضوع مسئله، گاهی مواردی پیش می‌آید که می‌خواهیم در آخرین مرحله از تابع IFS، عبارت منطقی را ننویسیم. این کار به ساده شدن فرمول و کاهش حجم محاسبات کمک می‌کند.

? برای مثال، در بخش قبل با فرض صفر بودن سلول A1، مراحل محاسبات تابع IFS را بررسی کردیم و دیدید که در تابع IFS ورودی‌ها به ترتیبی که وارد شده‌اند بررسی می‌شوند.
اما اگر دقت کنید می‌بینید که لازم نیست آخرین تست (test) عبارت منطقی انجام شود!

?چون در اعداد حقیقی، وقتی عددی مثبت یا منفی نباشد، طبیعتاً صفر است. به عبارت دیگر، وقتی اکسل به انتهای ورودی‌های تابع IFS رسیده است، عملاً معنای آن این است که عدد مورد نظر صفر بوده است (مثبت و منفی نبوده است). پس لازم نیست اکسل این موضوع را در قالب عبارت منطقی A1=0 مجدداً تست کند.

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

مثلاً تابع IF (که در همین مثال آوردیم) را در نظر بگیرید:

=IF( A1 > 0, "Positive", IF(A1 < 0, "Negative","Zero"))

در مثال بالا، مقدار "Zero" یک مقدار پیش‌فرض است که در صورت درست نبودن عبارت منطقی A1<0، در خروجی تابع IF می‌آید.

⚡⚡⚡اما تابع IFS آرگومانی برای مقدار پیش‌فرض ارائه نمی‌کند. پس باید راهی دیگر پیدا کنیم.

پیاده‌سازی مقدار پیش‌فرض در تابع IFS

? مسیر ذهنی زیر را در نظر بگیرید:

• ما می‌خواهیم که اکسل آن عبارت منطقی را تست نکند. ممکن است فکر کنیم که می‌شود آن عبارت منطقی را حذف کرد (تا test انجام نشود). اما این امکان‌پذیر نیست چون در این صورت قواعد نوشتاری ساختار IFS را رعایت نکرده‌ایم.
• از طرفی نمی‌توانیم کلاً جفت ورودی آخر را ننویسیم. چون اگر A1 برابر صفر باشد، آنگاه هیچ‌کدام از عبارت منطقی دیگر درست نخواهند بود (چون A1 مثبت یا منفی نیست). اما این باعث خطا می‌شود چون تابع IFS همیشه در عبارت‌های منطقی ورودی به دنبال مقدار TRUE می‌گردد، و اگر آن را پیدا نکند، خطای #N/A را برمی‌گرداند.

در موضوع این مسئله، ما می‌توانیم برای ننوشتن آخرین عبارت منطقی، به جای A1=0 بنویسیم: TRUE.
به شکل زیر:

=IFS(A1>0,"Positive",A1<0,"Negative",TRUE,"Zero")

با این کار عملاً عبارت منطقی A1=0 توسط اکسل بررسی نمی‌شود و این باعث کاهش محاسبات (خصوصاً در مدلهای پیچیده‌تر) می‌شود. دقت کنید که در این حالت شما از خطای #N/A هم جلوگیری کرده‌اید چون در نهایت عبارت TRUE توسط تابع IFS دیده خواهد شد.

✏️ تکمیلی: البته توجه داشته باشید که طبق این روش، ما در واقع یک عبارت ثابت (مقدار TRUE) را مستقیماً در فرمول خود وارد کرده‌ایم و این موضوع مقداری از پویایی فرمول‌های ما کم می‌کند. با این حال این روش به ساده‌تر کردن فرمول کمک می‌کند.

ارزیابی کیفی نمرات با تابع IFS

فرض کنید نمرات پایان‌نامه‌های ارشد یک دانشگاه را داریم و می‌خواهیم آن را به ارزیابی کیفی تبدیل کنیم.

توضیح مسئله

✅ مدیران آموزشی طی جلسه‌ای، جدول زیر برای این کار تهیه کرده‌اند:

19-20.00    عالی
18-18.99    خیلی خوب
16-17.99    خوب
12-15.99    متوسط
00-11.99    مردود

در لیست بالا اعداد تا 2 رقم اعشار در نظر گرفته شده‌اند ولی ما می‌توانیم به کمک عملگرهای منطقی تمام اعداد حقیقی مثلاً کوچکتر از 12 را در نظر بگیریم.

✅ اگر مقدار عددی پایان‌نامه را در سلول A1 نوشته باشیم، به طور معادل می‌توانیم عبارت‌های منطقی زیر را بنویسیم:

A1 <= 19 "Excellent"
A1 <= 18 "Very good"
A1 <= 16 "Good"
A1 <= 12 "Average"
A1 < 12 "Fail"

نوشتن تابع IFS

حالا برای نوشتن فرمول مورد نظر، عبارت‌های جفتی فوق را به ترتیب در فرمول زیر به کار می‌بریم:

=IFS(A1 >= 19, "Excellent", A1 >= 18, "Very good", A1 >= 16, "Good", A1 >= 12, "Average",A1<12, "Fail")

چون خواندن این عبارت‌ها کمی مشکل است، می‌توان خطوط فرمول‌ها به صورت زیر شکسته نوشت. البته در فرمول زیر از تکنیک "مقدار پیش فرض" هم استفاده شده و در آخرین جفت ورودی، از عبارت TRUE به جای A1<12 استفاده شده است:

=IFS(
A1 >= 19, "Excellent",
A1 >= 18, "Very good",
A1 >= 16, "Good",
A1 >= 12, "Average",
TRUE, "Fail")

این فرمول ابتدا شرطی را بررسی می‌کند که آیا نمره 19 یا بالاتر است یا خیر. اگر اینطور باشد، فرمول "Excellent" را برمی‌گرداند. در غیر این صورت، فرمول شرط بعدی را بررسی می‌کند. فرمول به همین ترتیب ادامه می‌یابد تا زمانی که یک شرط TRUE را پیدا کند. اگر هیچ شرطی TRUE را برنگرداند، فرمول "Fail" را برمی‌گرداند.

نوشتن صعودی یا نزولی؟

همان‌طور که گفتیم، ترتیب ورودی‌ها در تابع IFS مهم است. این ما هستیم که باید موضوع مسئله خود را بشناسیم و بر اساس آن انتخاب کنیم که بازه‌های اعدادی ما از بزرگ به کوچک باشند یا برعکس.

فرمول قبلی به شکل نزولی طراحی شده بود. اما می‌توانید فرمول را صعودی بنویسید و همان نتیجه را دریافت کنید:

=IFS(
A1 < 12, "Fail",
A1 < 16, "Average",
A1 < 18, "Good",
A1 < 19, "Very good",
A1>=19, "Excellent")

توجه کنید که صرفاً ترتیب ورودی‌ها برعکس شده است. همان طور که می‌دانید، می‌توانید به جای A1>=19 بنویسید: TRUE.

خطاهای تابع IFS

• توجه کنید که عبارت منطقی logical_test وارد شده باید طوری باشند که تابع IFS بتواند آنها را به TRUE یا FALSE تبدیل کند، در غیر اینصورت با خطای #VALUE! مواجه خواهید شد.
• اگر تابع IFS هنگام تست عبارات منطقی نتواند یک عبارت منطقی صحیح (TRUE) پیدا کند، نتیجه تابع با خطای #N/A همراه خواهد بود.
• اگر ورودی‌ها خودشان دارای خطا باشند، به تناسب تابع IFS نیز با خطا مواجه خواهد شد.

جمع‌بندی

• قبل از استفاده از تابع IFS، بهتر است با تابع IF آشنا باشید.
• در ورود اطلاعات آرگومان‌های IFS، ترتیب مهم است چون تابع به اولین عبارت منطقی نگاه می‌کند که TRUE باشد.
• ماهیت ورودی‌های تابع IFS مشابه تابع IF است که در این مقاله آن را مجدداً تکرار نکردیم. یعنی می‌توانید از مقادیر ثابت، ارجاع به سلول‌ها یا محدوده‌ها استفاده کنید.

مهدی ملک