یکی از مشکلات نرم‌افزار اکسل در هنگام کار با اکثر توابع برای انجام محاسبات این است که اگر ورودی تابع شامل خطا باشد، خروجی آن نیز به صورت خطا نمایش داده خواهد شد. برای رفع این مشکل چندین روش وجود دارد که در ادامه به آن‌ها اشاره می‌شود،
روش‌های زیر به طور نمونه برای انجام عملیات جمع توضیح داده شده است. هدف محاسبه مجموع اعداد محدوده B2:B6 در شکل زیر است که در سلول B3 خطا وجود دارد. همانطور که مشاهده می‌کنید خروجی تابع Sum بصورت خطا ظاهر شده است.
? روش اول) استفاده از یک ستون کمکی
با استفاده فرمول زیر خطای موجود در سلول B3 را در یک ستون کمکی به صفر تبدیل می‌کنیم و سپس اعداد ستون جدید را با هم جمع می‌کنیم.
=IF(ISNUMER(B2),B2,0)
در واقع در فرمول فوق اگر سلول دارای خطا باشد آن را به صفر تبدیل می‌کند، اما اگر عدد باشد بدون هیچ تغییری آنرا دوباره درج می‌کند.
? روش دوم) با استفاده از تابع Sumif
می‌توان کاری کرد که در حین محاسبه جمع از خطا صرف نظر شود. به طور مثال در مثال فوق خطای !VALUE# در سلول B3 وجود دارد. باید در تابع Sumif از این خطا صرف نظر شود مانند فرمول زیر:
=SUMIF(B2:B6,”<>#VALUE!”)
خروجی این فرمول برابر با ۱۳ می‌شود.
فرمول فوق زمانی کاربرد دارد که فقط خطای !VALUE# وجود داشته باشد اما چون ممکن است چندین نوع خطا وجود داشته باشد باید از معیاری استفاده کرد که همه خطاها را صرف نظر کند مانند فرمول زیر که برای همه خطاها قابل استفاده است:
=SUMIF(B2:B6,”<=”&9.99E+307)
? روش سوم) استفاده از فرمول‌نویسی آرایه‌ای
در این نوع فرمول‌نویسی پس از نوشتن فرمول باید به جای اینتر، کلیدهای Ctrl + Shift+ Enter را همزمان بفشارید. در فرمول زیر در حین جمع سلول‌ها تابع IFERROR خطاها را به صفر تبدیل می‌کند.
=SUM(IFERROR(B2:B6,0))
تابع IFFERROR در اکسل ۲۰۰۷ و بالاتر موجود است، اما اگر از اکسل ۲۰۰۳ استفاده می‌کنید باید از فرمول‌های زیر استفاده نمایید. (حتما در انتها کلیدهای Ctrl+Shift+Enter را فشار دهید)
=SUM(IF(ISERROR(B2:B6),0,B2:B6))
=SUM(IF(ISNUMBER(B2:B6),B2:B6,0))
? روش جهارم) تابع AGGREGATE (اکسل ۲۰۱۰ به بعد)
با استفاده از تابع AGGREGATE که نسخه پیشرفته تابع SUBTOTAL است می‌توانید خیلی ساده مشکل فوق را حل کنید. فرمول زیر مجموع داده‌ها را با صرف نظر کردن از خطاها محاسبه می‌کند:
=AGGREGATE(9,6,B2:B6)
? روش پنجم) ماکرونویسی
با استفاده از کدهای ماکرو که در این سایت ارائه شده است هم می‌توانید مشکل فوق را حل کنید.
