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

پیام‌های خطا به‌خصوص زمانی که در صفحات کاربر نهایی مانند گزارش‌ها و داشبوردها نشان داده شوند، اگر به‌درستی مدیریت نشوند می‌توانند خیلی بد باشند. خوشبختانه، اکسل راهی برای از بین بردن خطاهایی مانند #VALUE! ، #NUM! ، #REF! ، #DIV/0! ، #NAME? ، #NULL! ، #N/A و #GETTING_DATA قبل از اینکه نشان داده شوند، دارد. در اینجا می‌آموزیم که چگونه پیام‌های خطا در اکسل را مدیریت کنیم.

چرا پیام‌های خطا ظاهر می‌شوند

هنگامی‌که شما از توابع در اکسل استفاده می‌کنید، انتظار می‌رود که ورودی‌های آنها دارای مشخصه‌های خاصی باشند. هنگامی‌که از تابع SUM برای اضافه کردن سلول‌ها استفاده می‌کنید، اکسل فرض می‌کند که ارجاعات اعداد هستند. هنگامی‌که از VLOOKUP برای پیدا کردن یک مقدار در محدوده سلولی استفاده می‌کنید، اکسل تصور می‌کند که ارزش و مقادیر آنها وجود دارد.

هر زمان که اکسل چیزی را که انتظار داشته باشد، پیدا نکند، پیام خطایی را نشان می‌دهد.

انواع خطاها در Excel

#VALUE!

خطای #VALUE! از شایع‌ترین نوع خطاهاست. این خطا زمانی اتفاق می‌افتد که نوع داده مطابق با آنچه انتظار می‌رود نباشد.

به‌عنوان مثال، زمانی که یک مقدار متنی را به یک عدد اضافه کنید:

="A"+1

#REF!

خطای #REF! زمانی اتفاق می‌افتد که مرجع یک سلول حذف شده و یا انتقال یافته است. اکسل تلاش می‌کند تا تمام مراجع را به‌صورت خودکار به‌روزرسانی کند، اما زمانی که نمی‌تواند این کار را انجام دهد، مرجع واقعی سلول با خطا جایگزین می‌شود. به‌عنوان مثال، اگر ما محتویات سلول‌های A1 و B1 را جمع کنیم، تابع به‌صورت زیر خواهد بود:

=A1+B1

پس از حذف سلول B1، تابع به این صورت تغییر می‌کند:

=A1+#REF!

دقت داشته باشید منظور از حذف سلول، حذف محتویات داخل سلول نیست بلکه حذف کامل سلول با استفاده از کلیک راست بر روی سلول مد نظر و زدن کلید Delete (و یا از آدرس Home – Cell – Delete) می‌باشد.

#DIV/0!

خطای#DIV/0! معمولاً زمانی اتفاق می‌افتد که در یک عملیات ریاضی، تقسیم بر صفر وجود داشته باشد که این عمل از نقطه نظر ریاضی ممکن نیست. یک مثال ساده، تقسیم هر عدد به صفر است:

=1/0

#NAME?

خطای#NAME? هنگامی‌که اکسل محدوده نامی را پیدا نمی‌کند، ظاهر می‌شود. اکسل فرض می‌کند که هر رشته معرفی نشده که نام یک تابع نیست، یک محدوده نام‌گذاری شده است. زمانی با این خطا مواجه می‌شوید که فراموش کرده‌اید به درستی یک رشته را معرفی کنید یا مرجع سلول را عنوان کنید. مثلاً:

=Excel+1

#NULL!

خطای #NULL! هنگامی‌که اکسل نمی‌تواند محدوده مشخص شده در یک سلول را پیدا کند نشان داده می‌شود. مثلاً زمانی که بین دو محدوده هیچ تقاطعی وجود نداشته باشد:

=SUM(A1:A10 C1:C10)

#N/A

خطای #N/A زمانی اتفاق می‌افتد که یک تابع مانند MATCH یا VLOOKUP نمی‌تواند مقداری را که خواسته شده جستجو کند. مثلاً:

=MATCH("A",{"B","C"},0)

#NUM!

این خطا در مواقع زیر ظاهر می‌شود:
۱- زمانی که حاصل یک فرمول یا تابع در محدوده اعداد تعریف شده نگنجد و معتبر نباشد. به عنوان مثال مقدار حاصل شده یا خیلی کوچک یا خیلی بزرگ باشد و یا جذر عدد منفی. (همانطور که می‌دانید اعداد منفی جذر ندارند پس حاصل تابع SQRT در صورتی که ورودی آن عدد منفی باشد خطای #NUM! می‌باشد.)
۲- زمانی که یک فرمول از توابعی استفاده می‌کند که تکرار شونده هستند و در نتیجه نمی‌تواند نتیجه فرمول را پیدا کند، مانند برخی از توابع مالی مثل IRR و RATE (جهت برطرف کردن این خطا می‌بایست تعداد دفعات تکرار محاسبه فرمول را در تنظیمات اکسل مشخص کرد)

شبه خطاها در اکسل

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

########

دو دلیل وجود دارد که باعث می‌شود یک رشته از نماد پوند (#) را در یک سلول ببینید.

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

دومین دلیل که ممکن است ######## را ببینید، مواقعی است که سلولی که فرمت تاریخ یا زمان دارد، مقداری منفی داشته باشد که این مورد معمولاً زمانی اتفاق می‌افتد که تفاضل دو زمان یا تاریخ را محاسبه کنیم.

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

#GETTING_DATA

#GETTING_DATA یک پیغام است که زمانی در اکسل ظاهر می‌شود که یک شیت پیچیده یا گسترده همراه با اطلاعات زیاد در حال محاسبه باشد. در اکسل ۲۰۰۷ و جدیدتر، عملیات گروه‌بندی می‌شوند بنابراین سلول‌های پیچیده‌تر ممکن است پس از انجام کارهای قبلی به پایان برسند. در حالی که محاسبات هنوز پردازش می‌شوند، سلول‌های ناتمام ممکن است #GETTING_DATA را نمایش دهند. از آنجایی که این پیام موقتی است و زمانی که محاسبات تکمیل می‌شود، ناپدید می‌شود می‌توان گفت این یک خطای واقعی نیست.

Circular References

هنگامی‌که فرمول یک سلول را در محاسبات به خود آن سلول ارجاع دهیم، این خطا تشکیل می‌گردد، به عنوان مثال در سلول B1 بنویسید:

=A1+B1

نشانه‌های ایجاد خطای Circular موارد زیر می‌باشد:
۱- آدرس سلول خطا در نوار وضعیت در پایین صفحه اکسل نشان داده می‌شود.
۲- حاصل آن صفر می‌شود.

تکنیک‌های پردازش خطاهای معمول

گرفتن خطاهای Lookup با IFERROR

هنگام استفاده از VLOOKUP یا HLOOKUP برای پر کردن فیلدها از جدول جستجو، این توابع خطایی را در صورت مطابق نشدن داده‌ها پیدا می‌کنند. IFERROR می‌تواند به‌جای نشان دادن #VALUE! ، خطا را بگیرد و پیام مناسب را نمایش دهد. به مثال زیر نگاه کنید:

=IFERROR(VLOOKUP(A1,C:C,1,FALSE),"No Match")

در فرمول بالا، VLOOKUP در حال تلاش برای پیدا کردن محتویات سلول A1 در ستون C است. اگر نمی‌تواند یک داده را پیدا کند، به‌طور معمول باید یک خطا را نشان دهد، اما در عوض "No Match" را نمایش می‌دهد.

IFERROR های متداخل و پیچیده

اگر در یک تابع جستجو مثل MATCH در اولین تلاش خطا نشان داده شود، تابع IFERROR متداخل می‌تواند یک جستجوی دوم یا حتی سوم را در فیلد value_if_error خود اجرا کنند. به مثال زیر نگاه کنید:

=IFERROR(MATCH(A1,F2:F11,0),IFERROR(MATCH(A1,G:G,0),"No Match"))

در مثال بالا، اولین MATCH در حال تلاش برای پیدا کردن محتویات سلول A1 در سلول F2 تا F11 می‌باشد. اگر نتواند یک داده را پیدا کند، به طور معمول یک خطا را برمی‌گرداند، اما در عوض، جستجوی دیگری برای A1 در ستون G انجام می‌شود. در نهایت، اگر هر دو تابع MATCH انجام نشود، در IFERROR خروجی "No Match" نمایش داده می‌شود.

آموزش کاربران از طریق ISERROR

گرفتن خطاها نیز می‌تواند به منظور آموزش کاربر برای پر کردن فرم‌ها یا استفاده از یک داشبورد در یک صفحه گسترده استفاده شود. این‌گونه در نظر بگیرید که سلولی دارید که دارای یک عملیات تقسیم است که کاربر ورودی آن را وارد می‌کند. ورودی باید غیر صفر باشد، در غیر این صورت عملیات محاسبه نمی‌شود و خطای #DIV/0! نشان داده می‌شود. در کنار سلول ورودی کاربر، می‌توانید سلولی با دستورالعمل‌های زیر قرار دهید:

=IF(ISERROR(B5),"Value must be larger than zero","Input accepted")

در این مثال، B5 سلولی است که در آن تقسیم اتفاق می‌افتد. اگر عملیات تقسیم خطای #DIV/0! را نمایش دهد، فرمول عبارت "مقدار باید بزرگ‌تر از صفر باشد" را نمایش می‌دهد در غیر این صورت، "ورودی پذیرفته شده است" را نشان می‌دهد.