پیام های خطا و روش حل آنها در اکسل

پیام های خطا در اکسل

پیام های خطا هنگام نوشتن فرمول در اکسل روی می دهند و این خطاها در سلول حاوی فرمول نشان داده می شوند.

سلولی که دارای مشکل باشد با یک علامت مثلث سبزرنگ مشخص می شود.

پیام های خطا در اکسل

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

پیام های خطا در اکسل

در ادامه انواع پیام های خطا و مفهوم آنها توضیح داده شده است.

  # # # # #

این نوع خطا زمانی نمایش داده می شود که عرض ستون برای نمایش محتویات عددی مناسب نیست و برای رفع این خطا باید عرض ستون را بیشتر کنیم.

تذکر: این خطا فقط برای مقادیر عددی رخ می دهد و برای سایر اطلاعات با این نوع خطا روبرو نمی شویم.

#DIV/0

این خطا بیانگر تقسیم بر صفر است. اگر در قسمتی از فرمول عملیات تقسیم بر صفر اتفاق بیفتد مانند A1/B2 اما در خانه مخرج یعنی B2 هنوز عددی وارد نشده و از آنجا که سلول خالی برابر صفر در نظر گرفته می شود این پیغام نمایش داده می شود.

 # N/A

یعنی مقدار موردنظر در دسترس نیست (Not Available) اگر از توابع جستجو مانند vlookup و match و … استفاده کنید و این توابع نتوانند مقداری را پیدا کنند این پیام خطا نمایش داده می شود.

# NAME

وقتی که اکسل بخشی از فرمول نوشته شده را نفهمد این پیام ظاهر می شود.

برای مثال فرمول را به این شکل می نویسیم (sum(range= و اگر قبلاً محدوده range را تعریف نکرده باشیم با این خطا مواجه خواهیم شد.

# NULL

اگر برای جدا کردن ورودی های یک تابع به جای کاما و نقطه کاما از کلید فاصله استفاده کنیم خطای NULL رخ می دهد.

 # REF

خطای مربوط به نامشخص بودن مرجع فرمول می باشد. فرض کنید در شیت 1 فرمولی نوشته ایم که مجموع اعداد واقع در محدوده A1:A10 شیت 2 را جمع می کند و بعداً شیت 2 را حذف کرده ایم. در این صورت فرمول خطای REF #را نمایش می دهد.

 # NUM

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

# VALUE

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

برای مثال در توابعی که ورودی عددی دارند یک ورودی غیر عددی وارد شود این خطا نمایش داده می شود.یا زمانی که در توابع آرایه ای به جای کلید ترکیبی ctrl+shift+Enter فقط کلید Enter را فشار دهیم این خطا رخ می دهد.

برطرف کردن خطا

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

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

همانطور که قبلاً ذکر شد در سلول حاوی خطا یک مثلث کوچک سبزرنگ ظار می شود و با کلیک بر روی این سلول در کنار آن علامت اخطار به نمایش در می آید و با کلیک بر روی این علامت اخطار لیستی از گزینه های مختلف به نمایش در می آید.

پیام های خطا در اکسل

در این پنجره بر روی عبارت show calculation steps کلیک می کنیم. تصویر زیر نمایش داده می شود.

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

پیام های خطا در اکسل

نظارت اکسل هنگام ثبت فرمول

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

پیام های خطا در اکسل

با کلیک بر روی دکمه OK به فرمول باز می گردیم و تا زمانی که اشتباه اصلاح نشود نمی توانیم بقیه فرمول را ادامه دهیم.

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

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

پیام های خطا در اکسل

در صورتی که فرمول نشان داده شده مطابق نظر ما باشد با کلیک بر روی OK فرمول را ثبت می کنیم و در غیر این صورت بر روی دکمه No کیک می کنیم تا به فرمول باز گردیم و آن را اصلاح نمائیم.

پیغام های هشدار

برخی از پیام ها هستند که فقط حالت هشدار دارند و لزوماً به معنای وقوع خطا نیستند.

برای مثال اگر در سلولی که نوع داده در آن Text تعریف شده، یک عدد را وارد کنیم، علامت مثلث خطا در آن ظاهر می شود و با کلیک بر روی علامت اخطار زرد رنگ پنجره ای ظاهر می شود که اگر بر روی convert to number کلیک کنیم نوع محتویات سلول به عدد تبدیل می شود

در صورتی که بر روی ignore کلیک شود اکسل از این خطا چشم پوشی می کند.

به طور کلی لازم نیست در این موارد کار خاصی انجام دهیم.

یکی دیگر از هشدارها مربوط به انتخاب محدوده صحیح برای یک فرمول می شود. برای مثال محدوده A1:A6 با تعدادی عدد پر شده است و ما فرمول را در سلول E1 به صورت زیر نوشته ایم

=sum(A1:A5)

به نظر می رسد که خطایی روی داده است. با توجه به اینکه در انتهای لیست محدوده ای که فرمول برای آن نوشته شده است عدد وجود دارد و این عدد در فرمول لحاظ نشده، اکسل حدس زده شاید خطایی اتفاق افتاده . این پیغام به ما می گوید که سلول های مجاور (adjacent cells) در فرمول لحاظ نشده اند.  اگر روی update کلیک کنیم فرمول اصلاح شده و سلول A6 به محدوده جمع اضافه می شود.

پیام های خطا در اکسل

دور باطل در فرمول چرخه ای

اگر در فرمولی که در یک سلول نوشته شده و از آدرس همان سلول در فرمول استفاده شود یک مرجع چرخشی و یا Circular reference ایجاد می شود.

برای مثال در سلول A1 فرمول را به صورتA1+B1 = وارد می کنیم. پس از فشردن دکمه Enter پنجره زیر ظاهر می شود.

پیام های خطا در اکسل

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

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

به همین منظور اکسل مقدار این فرمول را محاسبه نمی کند.

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

در شکل 5 هدف محاسبه مجموع سلول های A1:A5 در خانه A6 می باشد. اما از آنجا که هنگام انتخاب محدوده، به اشتباه خود خانه A6 هم در فرمول قرار گرفته است، فرمول به شکل چرخه ای درآمده است.

پیام های خطا در اکسل

مرجع: کتاب کلید توابع و فرمول ها در اکسل