ترفندهای کامپیوتری

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

روش‌های زیر به طور نمونه برای انجام عملیات جمع توضیح داده شده است. هدف محاسبه مجموع اعداد محدوده B2:B6 در شکل زیر است که در سلول B3 خطا وجود دارد. همانطور که مشاهده می‌کنید خروجی تابع Sum بصورت خطا ظاهر شده است.

🔵 روش اول) استفاده از یک ستون کمکی

با استفاده فرمول زیر خطای موجود در سلول B3 را در یک ستون کمکی به صفر تبدیل می‌کنیم و سپس اعداد ستون جدید را با هم جمع می‌کنیم.

=IF(ISNUMER(B2),B2,0)

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

🔵 روش دوم) با استفاده از تابع Sumif

با استفاده از تابع 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)

🔵 روش پنجم) ماکرونویسی

با استفاده از کدهای ماکرو که در این سایت ارائه شده است هم می‌توانید مشکل فوق را حل کنید.

منبع: wmfexcel.com