تابع IFERROR

تابع IFERROR به نوعی خطاهای اکسل را فیلتر می‌کند یا به دام می‌اندازد. ?

✅ کاری که IFERROR انجام می‌دهد این است:

1. ورودی را دریافت می‌کند: مثلا یک فرمول یا آدرس سلول.
2. اگر ورودی خطا نداشت، مقدار همان ورودی را در خروجی قرار می‌دهد. مثلاً نتیجه آن فرمول و یا مقدار آن سلول را برمی‌گرداند.
3. اما اگر خطایی در ورودی وجود داشت، مقداری که شما تعیین کرده‌اید (متن، عدد یا فرمول دیگر) را برمی‌گرداند.

✅به کمک این تابع می‌توانید:

• از بروز خطا در ادامه محاسبات یا تحلیل‌های خود جلوگیری کنید.
• پیغام‌هایی مناسب را برای اطلاع کاربر و یا تهیه گزارش چاپی تعیین کنید.
• برای محاسبات خود یک فرمول پشتیبان طراحی کنید: مثلاً دو فرمول را به تابع IFERROR بدهید: یکی اصلی باشد و دیگری فرعی. اگر فرمول اصلی با خطا مواجه شد، تابع IFERROR فرمول فرعی را در محاسبات وارد کند.

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

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

نحوه نوشتن تابع IFERROR به شرح زیر است:

=IFERROR(value, value_if_error)

این تابع دو آرگومان دارد:

• value: آنچه که وجود خطاها در آن بررسی می‌شود. این می‌تواند یک فرمول، مقدار یا ارجاع به سلول یا ناحیه‌ای از کاربرگ باشد.
• value_if_error: چیزی که باید در صورت مشاهده خطا بازگرداند. این می‌تواند یک رشته خالی (سلول خالی)، پیام متنی، مقدار عددی، فرمول یا محاسبه دیگری باشد.

می‌توانیم ورودی‌ها و خروجی تابع IFERROR را در قالب یک تابع ریاضی ببینیم:

✅✅ تابع‌ IFERROR خطاهای زیر را می‌تواند تشخیص دهد:

#N/A
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!

در نظر داشته باشید که:

• ما باید موضوع مسئله را بشناسیم و اکسل صرفاً آنچه ما می‌خواهیم را اجرا می‌کند.
• در واقع اکسل خطاهای ما را پیش‌بینی نمی‌کند و ما باید خطاهای ممکن مسئله را تشخیص داده باشیم تا بتوانیم شکل مناسب ترکیب توابع را تعیین کنیم.
• حتی اگر مسئله را به درستی درک کرده باشیم، باز ممکن است در هنگام پیاده‌سازی در اکسل خطاهایی رخ دهد که به ذات مسئله ما وابسته نیست و به محیط اکسل برمی‌گردد. پیش‌بینی چنین خطاهایی با تسلط بیشتر بر فرمول‌نویسی اکسل قابل انجام است.
• تابع IFERROR قرار است وجود خطا در ورودی اول را چک کند و نه ورودی دوم. پس اگر ورودی دوم (value_if_error) خودش دارای خطا بود، آن وقت دیگر تابع IFERROR نمی‌تواند کاری درباره خطا انجام دهد و صرفاً همان خطا به خروجی منتقل می‌شود.

ورود اطلاعات در IFERROR

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

مقدار ثابت

✅ می‌توان مقدار ثابتی (متن یا عدد) را مستقیم در ورودی‌های این تابع نوشت.

اگر چه فرمول زیر کاربرد خاصی ندارد ولی صرفاً جهت یادگیری فرمول زیر را در نظر بگیرید:

=IFERROR(1,0)

در فرمول بالا، ? مسیر ذهنی زیر را طی کنید:

1. تابع IFERROR به دنبال خطا در ورودی اول می‌گردد.
2. اما این ورودی هیچ خطایی ندارد،
3. پس این تابع به ورودی دوم هیچ کاری ندارد و همان ورودی اول یعنی عدد 1 را در خروجی می‌نویسد.

به عنوان نمونه دیگر، تابع زیر را در نظر بگیرید:

=IFERROR(FALSE,"Text")

در فرمول بالا، ? مسیر ذهنی زیر را طی کنید:

1. تابع به دنبال خطا در ورودی اول است.
2. محتوای ورودی اول خطا ندارد بلکه صرفاً یک عبارت منطقی است.
3. پس IFERROR به ورودی دوم اصلاً کاری ندارد و همان ورودی اول یعنی عبارت منطقی FALSE را مستقیماً در خروجی می‌نویسد.

اشاره به سلول

✅ ورودی‌های تابع می‌توانند مستقیماً آدرس یک سلول باشند. مثلاً فرمول زیر را در نظر بگیرید:

=IFERROR(A1,"Error in A1")

در فرمول بالا، ? مسیر ذهنی زیر را در نظر بگیرید:

1. اگر سلول A1 ار نظر اکسل بدون خطا باشد، همان مقدار در خروجی IFERROR قرار می‌گیرد.
2. اما اگر سلول A1 به هر دلیلی خطایی داشته باشد، عبارت "Error in A1" در خروجی تابع قرار می‌گیرد.

با این روش می‌توانیم به کاربر پیغامی مناسب بدهیم تا کاربر متوجه وجود خطا در A1 بشود.

فرمول‌ها

✅ ورودی‌های تابع IFERROR میتواند فرمول باشد.

مثلاً در فرمول زیر، ورودی اول تابع IFERROR خودش یک فرمول است (تقسیم دو عدد). در اولی از اعداد ثابت استفاده شده و در دومی از ارجاع به سلول‌ها:

=IFERROR(10/0,"Err")
=IFERROR(A1/B1,"Err")

همان طور که می‌بیند در فرمول اول، مقدار 10/0 دارای خطای تقسیم بر صفر است. بنابراین خروجی تابع اول مقدار متنی "Err" است. با فرمول‌نویسی مشابه تابع دوم می‌توانیم از خطای تقسیم بر صفر جلوگیری کنیم.

اشاره به محدوده به عنوان آرایه

✅ تابع IFERROR از محدوده‌ها و آرایه‌ها پشتیبانی می‌کند.

برای نمونه اگر در ورودی اول تابع IFERROR یک محدوده مثل A1:A10 را وارد کنید، آنگاه اکسل این ورودی را به شکل یک آرایه می‌بیند که از 10 عنصر تشکیل شده است و وجود خطا در همه آن 10 سلول (به طور جداگانه) بررسی می‌شود.

⬅️ این تقریباً مشابه نوشتن 10 تا تابع IFERROR است، البته در حالت آرایه‌ای، برای همه سلول‌هایی که خطا دارند، از ورودی دوم استفاده خواهد شد.

در همین رابطه، تابع زیر را در نظر بگیرید:

=IFERROR(A1:A10,"Error")

در فرمول بالا، ?مسیر ذهنی زیر را طی کنید:

1. تابع IFERROR ناحیه A1:A10 را همزمان دریافت می‌کند و آن را به شکل یک آرایه با 10 عضو می‌بیند.
2. و وجود خطا را در هر کدام از این 10 مورد، به طور جداگانه بررسی می‌کند.
3. و همزمان 10 تا خروجی می‌دهد که هر کدام معادل یک IFERROR جداگانه است.

در فرمول قبلی، برای نمایش کامل نتیجه در محیط کاربرگ، نیاز به 10 سلول هم داریم! چون 10 ورودی داشتیم و قرار است IFERROR برای هر کدام یک جواب به ما بدهد.

✅✅ در ورژن‌های جدید (نسخه 2021 به بعد)، اکسل از Dynamic Arrays یا آرایه‌های پویا پشتیبانی می‌کند، یعنی:

1. شما با ارجاع به ناحیه یا آرایه، فرمول خود را که دارای IFERROR است را می‌نویسید.
2. سپس اکسل خودش ناحیه مناسب برای نمایش این سلول‌ها به شکل خودکار در نظر گرفته و نتیجه را نشان می‌دهد.

?? در ورژن‌های قدیمی، شما باید به اکسل می‌فهماندید که نیاز به محاسبات آرایه‌ای دارید:

1. باید ابتدا اندازه ناحیه لازم برای خروجی را آگاهانه در نظر می‌گرفتید و ناحیه انتخاب می‌کردید.
2. سپس برای یک سلول فرمول را می‌نوشتید.
3. آنگاه همزمان دکمه‌های Ctrl و Alt و Enter را فشار می‌دادید.

⬅️ همان‌طور که می‌بینید، روش قبلی نوشتن فرمول‌های آرایه‌ای، سخت‌تر و با انعطاف کمتر است.

آرایه‌های ثابت

همچنین در ورودی تابع IFERROR می‌توانید از آرایه‌های ثابت هم استفاده کنید:

=IFERROR({1,2,3},"Error")

که مانند اشاره به محدوده‌ها، در اینجا هم اگر از ورژن‌های جدید اکسل استفاده می‌کنید (که dynamic array را پشتیبانی می‌کنند)، خود اکسل خروجی آرایه‌ای را در کاربرگ ارائه می‌کند. ولی در ورژن‌های قبلی اکسل باید همان مسیری که مثل قبلی گفتیم را طی کنید یعنی ناحیه را انتخاب کرده، فرمول را نوشته و همزمان Ctrl+Alt+Enter را فشار دهید.

ترکیب IFERROR با سایر توابع اکسل

کاربرد اصلی تابع IFERROR هنگام ترکیب با سایر توابع است که در ادامه می‌بینید:

ترکیب با SUM برای جمع بدون خطا

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

به کمک فرمول زیر می‌توانیم سلول‌هایی که دارای خطا هستند را به صفر تبدیل کنیم که اثری بر تابع SUM نداشته باشند:

=SUM(IFERROR(A1:A10,0))

فرمول بالا از یک IFERROR داخل تابع SUM تشکیل شده است.

برای درک این فرمول، ?مسیر ذهنی زیر را طی کنید:

1. چون به تابع IFERROR یک محدوده داده شده است، پس اکسل سلول‌های A1تا A10 را مشابه یک آرایه در نظر می‌گیرد.
2. پس اکسل تابع IFERROR را برای تک تک این سلول‌ها به شکل جداگانه اجرا می‌کند و در نهایت یک آرایه را به SUM می‌دهد.
3. اما این آرایه اینطور از سلول‌های A1 تا A10 ساخته می‌شود:
   • هر کدام از سلول‌ها که ✅بدون خطا   باشند، متناظر با آنها ⬅️ مقدار خودشان به آرایه خروجی IFERROR می‌رود.
   • هر کدام از سلول‌ها که ⚠️دارای خطا  باشند، متناظر با آنها ⬅️ مقدار صفر  (0) به آرایه خروجی IFERROR می‌رود.
   • در نهایت تابع IFERROR آرایه‌ای بدون خطا را به تابع SUM می‌دهد.
4. تابع SUM آرایه را دریافت کرده و اعداد آن را جمع می‌کند.

◀️ توجه کنید که سلول‌های A1 تا A10 ممکن است متنی هم باشند که این تابع آنها را در نظر نمی‌گیرد.

ترکیب با COUNT برای جلوگیری از خطا

✅ تابع COUNT برای شمارش اعداد استفاده می‌شود. اما اگر این تابع در ورودی‌های خود نتواند عددی را پیدا کند، خطا خواهد داد.

با فرمول زیر می‌توانیم از خطا جلوگیری می‌کنیم:

=IFERROR(COUNT(A1:A10), "No Number Found")

برای درک این فرمول، ?مسیر ذهنی زیر را طی کنید:

1. در این فرمول، ناحیه A1 تا A10 به تابع COUNT فرستاده می‌شود.
2. تابع COUNT به شمارش سلول‌هایی می‌پردازند که در محتوای آنها عدد یافت می‌شود (چه عدد ثابت، چه تاریخ و چه نتیجه فرمول…).
3. اگر تابع COUNT
   • بتواند عددی در آن ناحیه پیدا کند، تعداد آن را به IFERROR می‌فرستد.
   • ولی اگر عددی پیدا نکند، خطای #N/A را خواهد فرستاد.
4. سپس تابع IFERROR خروجی تابع COUNT را دریافت می‌کند، حالا تابع IFERROR:
   • اگر از تابع COUNT عدد دریافت کند که تعداد در خروجی می‌نویسد.
   • اگر خطای #N/A را دریافت کند، پیام "No Number Found" را در خروجی خواهد نوشت.

◀️ به این ترتیب ما از وجود خطا در شمارش اعداد ناحیه A1:A10 جلوگیری کرده‌ایم.

محدودیت IFERROR - توابع مرتبط و جایگزین

گفتیم که تابع IFERROR انواعی از خطاها را به دام می‌اندازد.

ولی خوب: برای همه آنها یک خروجی دارد! یعنی با همه خطاها یک طور برخورد می‌کند.

?? این موضوع باعث می‌شود که ⚠️ شما متوجه نوع خطا نشوید.

✅✅ برای مقابله بهتر با این موضوع پیشنهاد می‌کنیم که:

1️⃣ اولاً قبل از مواجه با خطاها در اکسل این نکته‌ها را در نظر بگیرید:

1. مسئله خودتان را بهتر بشناسید تا بدانید چه خطاهایی در پیاده‌سازی ممکن است رخ دهد.
2. با تسلط بر فرمول‌نویسی در محیط اکسل، از خطاهای ممکن در اکسل اطلاع پیدا کنید.
3. با بررسی نوع اطلاعات ورودی مطمئن شوید که ریشه خطاها از ذات داده‌ها نباشد.
4. مسیر فرمول‌ها را با ابزارهای کمکی اکسل یا به شکل دستی چک کنید تا پردازش داده‌ها مطمئن شوید.

2️⃣ ثانیاً برای مقابله اختصاصی با خطاها این موارد را در نظر بگیرید:

1. می‌توانید به کمک تابع ERROR.TYPE در اکسل نوع خطای موجود در ورودی را تشخیص دهید و با ترکیب این ERROR.TYPE با تابع IF حالت‌های مختلف خطا را مدیریت و کنترل کنید.
2. در مواردی مانند مثال قبلی که خطا از نوع #NA بود، می‌توانید از تابع IFNA استفاده کنید. تابع IFNA مشابه IFERROR است اما خطای نوع #NA را فیلتر می‌کند.
3. اگر می‌خواهید چک کنید که یک ورودی اساساً دارای خطا هست یا نه، از تابع ISERROR استفاده کنید. البته تابع ISERR هم همین عملکرد را دارد، اما فقط خطای #N/A را نمی‌‌گیرد. در مقابل اگر می‌خواهید انحصاراً وجود خطای #N/A را بررسی کنید، می‌توانید از تابع ISNA استفاده کنید.
4. می‌توان از مدیریت خطا در ?برنامه‌نویسی VBA استفاده کرد. این کمک می‌کند که مدیریت خطاها را به صورت دقیق‌تر و با تفکیک نوع خطاها انجام دهید. این موضوع به تخصص بیشتری دارد.

جمع بندی

• تابع IFERROR در اکسل برای به دام انداختن و مدیریت خطا در فرمول‌ها است.
• این تابع کمک می‌کند تا مدل‌ها و گزارش‌های ما در مقابل خطاها منعطف باشد.
• تابع IFERROR این محدودیت را دارد که برای همه خطاهای گفته شده فقط یک خروجی جایگزین ارائه می‌کند که تا حدی می‌توان این محدودیت را با شناخت بهتر خطاها و مقابله اختصاصی با آنها رفع کرد.

مهدی ملک