شاید برای شما هم پیش آمده باشد که هنگام کار با اکسل با یک سری خطا روبرو شده‌اید که روش برطرف کردن آن‌ها را نمی‌دانستید. لازم به ذکر است که اگر این خطاها به درستی مدیریت و برطرف نشوند، حتی می‌توانند فاجعه آور باشند؛ اما نگران نباشید در اکسل راهی برای مدیریت پیام‌های خطا وجود دارد. با ما همراه باشید تا با انواع خطاها در اکسل و نحوه برطرف کردن آن‌ها آشنا شویم.
دلیل نمایان شدن انواع خطاها در اکسل چیست؟
اکسل به گونه‌ای طراحی و برنامه‌ریزی شده که هنگام استفاده از توابع، انتظار می‌رود ورودی‌های آن‌ها ویژگی و مشخصات خاصی داشته باشند. به عنوان مثال در هنگام استفاده از تابع SUM برای اضافه کردن سلول‌ها، اکسل به طور پیش فرض در نظر می‌گیرد که ارجاعات شما اعداد می‌باشند یا مثلاً در هنگام استفاده از VLOOKUP برای پیدا کردن یک مقدار در محدوده سلولی، اکسل فرض می‌کند که دارای ارزش و مقادیر هستند. به طور کلی وقتی که چیزی بر خلاف انتظار اکسل باشد یا به عبارت دیگر اکسل قادر نباشد چیزی را که انتظار دارد پیدا کند، پیام خطایی ظاهر می‌شود.
معرفی انواع خطاها در اکسل
۱. !VALUE#
این خطا از متداول‌ترین و رایج‌ترین نوع خطاها محسوب می‌شود. خطای #VALUE! زمانی ظاهر می‌شود که نوع داده مطابق با انتظار اکسل نباشد. مثلاً هنگامی که مانند زیر یک مقدار متنی به یک عدد اضافه شود، این خطا نمایان خواهد شد:
A”+1″=
۲. !REF#
این خطا در صورت حذف یا انتقال مرجع یک سلول ظاهر می‌شود. همانطور که می‌دانید اکسل همواره سعی می‌کند همه مراجع را به شکل اتوماتیک به‌روزرسانی کند، اما هنگامی که موفق به انجام این کار نشود، جایگزینی مرجع واقعی سلول با خطا صورت می‌گیرد. مثلاً درصورتی که محتویات سلول‌های A1 و B1 را جمع کنید، تابع مربوطه به شکل زیر است:
A1+B1=
در صورت حذف سلول B1، تابع عوض شده و به شکل زیر می‌شود:
!A1+#REF=
لازم به ذکر است منظور از حذف سلول، حذف محتویاتی که درون سلول قرار دارند نیست بلکه حذف کلی خود سلول است که از طریق کلیک راست بر روی سلول مورد نظر و انتخاب گزینه Delete اتفاق می‌افتد و یا اینکه از طریق مراجعه به Home قسمت Cell و قسمت Delete صورت می‌گیرد.
۳. !DIV/0 #
اغلب این خطا هنگامی ظاهر می‌شود که تقسیم بر صفر در یک عملیات ریاضی قرار داشته باشد. این عمل از نظر ریاضی هم غیرممکن است.
مثلاً مانند زیر عددی تقسیم به صفر شود:
۱/۰=
۴. ?NAME#
این خطا زمانی رخ می‌دهد که اکسل نتواند محدوده نامی را پیدا کند. بر اساس تصور اکسل هر رشته معرفی نشده که نام یک تابع نیست، یک محدوده نام‌گذاری شده است. در واقع وقتی این خطا برایتان اتفاق می‌افتد که یک رشته را به طور صحیح معرفی نکرده و یا به طور صحیح مرجع سلول را عنوان نکرده‌اید.
به عنوان مثال:
Excel+1=
۵. !NULL#
این خطا زمانی رخ می‌دهد که اکسل محدوده مشخص شده در یک سلول را پیدا نکند.به عنوان مثال وقتی که بین دو محدوده هیچ تقاطعی نباشد:
(SUM(A1:A10 C1:C10=
۶. N/A#
این خطا وقتی که یک تابع مانند MATCH یا VLOOKUP مقدار خواسته شده را پیدا نکند، رخ می‌دهد. به عنوان مثال:
(MATCH(“A”,{“B”,”C”},0=
۷. !NUM#
این خطا در دو صورت ظاهر می‌شود که در ادامه بیان شده‌اند:
- هنگامی که نتیجه یک فرمول یا تابع در اکسل در محدوده اعداد تعریف شده قرار نگیرد و دارای اعتبار نباشد. مثلاً مقدار نتیجه به دست آمده بسیار کوچک باشد یا بسیار بزرگ باشد و یا جذر عدد منفی باشد. (همانطور که خودتان هم می‌دانید اعداد منفی جذر ندارند بدین ترتیب وقتی که ورودی تابع SQRT یک عدد منفی باشد، نتیجه آن خطای #NUM! است.)
- در صورتی که یک فرمول از توابع تکرارشونده استفاده کند که بدین ترتیب قادر نیست حاصل فرمول را بیابد و با خطا مواجه می‌شود، نظیر برخی از توابع مالی مثل IRR و RATE. (برای مدیریت و رفع این خطا کافی است که تعداد دفعات تکرار محاسبه فرمول را در قسمت تنظیمات اکسل تعیین کنید.)
معرفی شبه خطاها در اکسل
ممکن است در اکسل با یک سری خطا مواجه شوید که خطاهای واقعی نیستند و اغلب در اثر یک فرمت دهی نادرست یا یک برگه در حال محاسبه، ظاهر می‌شوند.
۱. ########
یک رشته از نماد پوند (#) در دو صورت در یک سلول ظاهر می‌شود که در ادامه بیان شده‌اند:
- در صورتی که برای نشان دادن مقدار، ستون سلول مورد نظر خیلی باریک باشد رخ می‌دهد؛ که راه حل اصلاح آن هم بسیار راحت است. تنها کاری که باید انجام دهید این است که عرض ستون خود را به طور مناسب زیادتر کنید.
- اگر در سلولی که دارای فرمت تاریخ یا زمان است، مقداری منفی قرار گرفته باشد، ######## نمایان می‌شود که اغلب وقتی رخ می‌دهد که تفاضل دو زمان یا تاریخ را حساب کنیم.
با هر دو مورد ذکر شده در بالا، اطلاعات در سلول باقی می‌مانند و تنها مشکل موجود نوع فرمت سلول است. سایر فرمول‌هایی که دارای ارجاع به سلول هستند، باز هم قادرند که اطلاعات را مشاهده کنند و از آن‌ها استفاده کنند، بنابراین جزء خطاهای واقعی محسوب نمی‌شوند.
۲. GETTING_DATA#
GETTING_DATA# نوعی پیام است و خطا نیست. این پیام هنگامی در اکسل نمایان می‌شود که یک صفحه پیچیده یا گسترده با اطلاعات فراوان محاسبه می‌شود. در اکسل ۲۰۰۷ و بالاتر، گروه‌بندی عملیات رخ می‌دهد، بدین ترتیب امکان دارد که سلول‌های پیچیده‌تر بعد از انجام کارهای قبلی تمام بشوند. در حالی که پردازش محاسبات هنوز انجام می‌شود، امکان دارد که سلول‌های ناتمام پیام #GETTING_DATA را نشان دهند. این پیام یک خطای واقعی محسوب نمی‌شود چون این پیام دائمی نیست و وقتی که محاسبات کامل شود، از بین می‌رود.
۳. Circular References
این خطا زمانی ایجاد می‌شود که در محاسبات فرمول یک سلول به خود آن سلول ارجاع دهیم.مثلاً درصورتی که فرمول زیر در سلول B1 نوشته شود، خطا Circular References ظاهر می‌شود:
A1+B1=
علائم بروز خطا Circular در قسمت پایین بیان شده‌اند:
۱- در قسمت نوار وضعیت موجود در پایین صفحه اکسل، آدرس مربوط به سلول خطا نمایش داده می‌شود.
۲- جواب آن صفر می‌شود.
دیگران چه می‌خوانند؟
معرفی توابع مدیریت انواع خطاها در اکسل
در اکسل مجموعه‌ای از توابع وجود دارد که قادر هستند پیغام‌های خطا را قبل از نمایان شدن آن‌ها دریافت کنند. این توابع علاوه بر اینکه قادر به آرایش داشبورد یا گزارش‌ها هستند، می‌توانند ایجاد توابع جستجوی پیچیده و صفحات حرفه‌ای اکسل را امکان پذیر کنند. این توابع در قسمت پایین معرفی شده‌اند:
ISREF .1
تابعی است که مقدار منطقی TRUE را وقتی که محتوی سلول یک مرجع باشد، برمی‌گرداند.
ISNA .2
چنانچه محتوی سلول خطای #N/A باشد، این تابع مقدار منطقی TRUE را برمی‌گرداند.
ISERROR .3
این تابع مقدار TRUE را برای هر گونه خطایی برمی‌گرداند.
ISERR .4
این تابع مقدار TRUE را برای هر گونه خطایی به استثنا #N/A برمی‌گرداند.
IFNA .5
این تابع به منظور پیدا کردن و مدیریت خطای #N/A استفاده می‌شود.
IFERROR .6
این تابع به منظور پیدا کردن و مدیریت خطاها استفاده می‌شود.
ERROR.TYPE .7
این تابع عدد مربوط به هر خطا را برمی‌گرداند.
روش‌های پردازش خطاهای معمول
۱. گرفتن خطاهای Lookup از طریق IFERROR
وقتی که به منظور پر کردن فیلدها از جدول جستجو از VLOOKUP یا HLOOKUP استفاده می‌شود، این توابع در صورت مطابق نشدن داده‌ها خطایی را پیدا می‌کنند. IFERROR می‌تواند به جای اینکه !VALUE# را نشان دهد، خطا را گرفته و پیغام مناسب را نشان دهد.
به عنوان مثال در فرمول زیر VLOOKUP به منظور یافتن محتویات سلول A1 در ستون C تلاش می‌کند:
=IFERROR(VLOOKUP(A1,C:C,1,FALSE),”No Match”)
چنانچه قادر نباشد یک داده را بیابد، معمولاً باید یک خطا را نمایش دهد، اما به جای آن “No Match” را نشان می‌دهد.
۲. IFERROR های متداخل و پیچیده
در صورتی که در یک تابع جستجو مانند MATCH در نخستین تلاش خطا ظاهر شود، تابع IFERROR متداخل قادر است که جستجوی دوم یا حتی سوم را در فیلد value_if_error خود انجام دهد.
به عنوان مثال در فرمول زیر اولین MATCH به منظور یافتن محتویات سلول A1 در سلول F2 تا F11 سعی می‌کند.
=IFERROR(MATCH(A1,F2:F11,0),IFERROR(MATCH(A1,G:G,0),”No Match”))
چنانچه قادر نباشد یک داده را بیابد، معمولاً یک خطا را برمی‌گرداند، اما به جای آن، جستجوی دیگری برای A1 در ستون G اجرا می‌شود. در پایان، در صورت انجام نشدن هر دو تابع MATCH، در IFERROR خروجی “No Match” ظاهر می‌شود.
۳. آموزش کاربران با استفاده از ISERROR
همچنین می‌توان از گرفتن خطاها برای آموزش کاربر به منظور پر کردن فرم‌ها یا استفاده از یک داشبورد در یک صفحه گسترده استفاده کرد. فرض کنید یک سلول محتوی عملیات تقسیم دارید که ورودی آن توسط کاربر وارد می‌شود. لازم است که ورودی صفر نباشد وگرنه عملیات انجام نشده و خطای !DIV/0 # نمایان می‌شود. در مجاور سلول ورودی کاربر، می‌توان یک سلول با دستورالعمل‌های زیر قرار داد:
=IF(ISERROR(B5),”Value must be larger than zero”,”Input accepted”)
در مثال ذکر شده در بالا B5 سلولی است که عمل تقسیم در آن انجام می‌شود. درصورتی که عمل تقسیم خطای !DIV/0 # را نمایان کند، فرمول جمله “مقدار باید بزرگ‌تر از صفر باشد” را نشان می‌دهد وگرنه جمله “ورودی پذیرفته شده است” را نمایش می‌دهد.
کلام آخر
همانطور که مطالعه کردید، خطاها و ارورهای زیادی در حین کار با اکسل ممکن است پیش بیاید که بعضا این خطاها دست و پا گیر بوده و باعث عصبانیت کاربران می شود؛ ولی اگر با ساختار اکسل آشنایی اندکی داشته باشید و همچنین مطلب را با دقت مطالعه کرده باشید، به راحتی می توانید خطاهای اکسل را برطرف کنید. یکی از مهمترین راه‌های جلوگیری از ایجاد خطا و ارور، اشراف کامل نسبت به فرمول نویسی در اکسل است، بنابراین یادگیری کامل آن را فراموش نکنید. در پایان لازم به ذکر است که کارشناسان تکنولوژی انگیزه آماده دریافت سوالات و تجربیات شما در این خصوص هستند.