انواع خطاها در اکسل و راه چاره برطرف کردن آن ها

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

انواع خطاها در اکسل و راه چاره برطرف کردن آن ها

دلیل نمایان شدن انواع خطاها در اکسل چیست؟

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

معرفی انواع خطاها در اکسل

1. !VALUE#

این خطا از متداول ترین و رایج ترین نوع خطاها محسوب می شود. خطای #VALUE! زمانی ظاهر می شود که نوع داده مطابق با انتظار اکسل نباشد. مثلاً هنگامی که مانند زیر یک مقدار متنی به یک عدد اضافه شود، این خطا نمایان خواهد شد:

A+1″=

2. !REF#

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

A1+B1=

در صورت حذف سلول B1، تابع عوض شده و به شکل زیر می شود:

!A1+#REF=

لازم به ذکر است منظور از حذف سلول، حذف محتویاتی که درون سلول قرار دارند نیست بلکه حذف کلی خود سلول است که از طریق کلیک راست بر روی سلول مورد نظر و انتخاب گزینه Delete اتفاق می افتد و یا اینکه از طریق مراجعه به Home قسمت Cell و قسمت Delete صورت می گیرد.

3. !DIV/0 #

اغلب این خطا هنگامی ظاهر می شود که تقسیم بر صفر در یک عملیات ریاضی قرار داشته باشد. این عمل از نظر ریاضی هم غیرممکن است.

مثلاً مانند زیر عددی تقسیم به صفر شود:

1/0=

4. ?NAME#

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

به عنوان مثال:

Excel+1=

5. !NULL#

این خطا زمانی رخ می دهد که اکسل محدوده مشخص شده در یک سلول را پیدا نکند.به عنوان مثال وقتی که بین دو محدوده هیچ تقاطعی نباشد:

(SUM(A1:A10 C1:C10=

6. N/A#

این خطا وقتی که یک تابع مانند MATCH یا VLOOKUP مقدار خواسته شده را پیدا نکند، رخ می دهد. به عنوان مثال:

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

7. !NUM#

این خطا در دو صورت ظاهر می شود که در ادامه بیان شده اند:

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

معرفی شبه خطاها در اکسل

ممکن است در اکسل با یک سری خطا روبرو شوید که خطاهای واقعی نیستند و اغلب در اثر یک فرمت دهی نادرست یا یک برگه در حال محاسبه، ظاهر می شوند.

1. ########

یک رشته از نماد پوند (#) در دو صورت در یک سلول ظاهر می شود که در ادامه بیان شده اند:

  • در صورتی که برای نشان دادن مقدار، ستون سلول مورد نظر خیلی باریک باشد رخ می دهد؛ که راهکار اصلاح آن هم بسیار راحت است. تنها کاری که باید انجام دهید این است که عرض ستون خود را به طور مناسب زیادتر کنید.
  • اگر در سلولی که دارای فرمت تاریخ یا زمان است، مقداری منفی قرار گرفته باشد، ######## نمایان می شود که اغلب وقتی رخ می دهد که تفاضل دو زمان یا تاریخ را حساب کنیم.

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

2. GETTING-DATA#

GETTING-DATA# نوعی پیغام است و خطا نیست. این پیغام هنگامی در اکسل نمایان می شود که یک صفحه پیچیده یا گسترده با اطلاعات فراوان محاسبه می شود. در اکسل 2007 و بالاتر، گروه بندی عملیات رخ می دهد، بدین ترتیب امکان دارد که سلول های پیچیده تر بعد از انجام کارهای قبلی تمام بشوند. در حالی که پردازش محاسبات هنوز انجام می شود، امکان دارد که سلول های ناتمام پیغام #GETTING-DATA را نشان دهند. این پیغام یک خطای واقعی محسوب نمی شود چون این پیغام دائمی نیست و وقتی که محاسبات کامل شود، از بین می رود.

3. Circular References

این خطا زمانی ایجاد می شود که در محاسبات فرمول یک سلول به خود آن سلول ارجاع دهیم.مثلاً درصورتی که فرمول زیر در سلول B1 نوشته شود، خطا Circular References ظاهر می شود:

A1+B1=

علائم بروز خطا Circular در قسمت پایین بیان شده اند:

1- در قسمت نوار شرایط موجود در پایین صفحه اکسل، آدرس مربوط به سلول خطا نمایش داده می شود.

2- جواب آن صفر می شود.

معرفی توابع مدیریت انواع خطاها در اکسل

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

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

این تابع عدد مربوط به هر خطا را برمی گرداند.

روش های پردازش خطاهای معمول

1. دریافت خطاهای Lookup از طریق IFERROR

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

به عنوان مثال در فرمول زیر VLOOKUP به منظور یافتن محتویات سلول A1 در ستون C تلاش می کند:

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

چنانچه قادر نباشد یک داده را بیابد، معمولاً باید یک خطا را نمایش دهد، اما به جای آن No Match را نشان می دهد.

2. 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 ظاهر می شود.

3. آموزش کاربران با استفاده از ISERROR

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

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

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

کلام آخر

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

منبع: مجله انگیزه
انتشار: 29 مهر 1399 بروزرسانی: 29 مهر 1399 گردآورنده: namasho.com شناسه مطلب: 2053

به "انواع خطاها در اکسل و راه چاره برطرف کردن آن ها" امتیاز دهید

امتیاز دهید:

دیدگاه های مرتبط با "انواع خطاها در اکسل و راه چاره برطرف کردن آن ها"

* نظرتان را در مورد این مقاله با ما درمیان بگذارید