شرح خطاهای اکسل، رفع و پیشگیری از آنها - ویدئوی آموزشی

دلایل نمایش ###‌ در سلول اکسل

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

نکته) برای آنکه عرض یک ستون به اندازه بزرگترین مقدار آن ستون تنظیم شود کافی است که بر روی مرز بین ۲ ستون D-Click (دبل کلیک) کنید.

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

دلایل نمایش #Name در سلول اکسل

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

شایع‌ترین این خطا به دلیل آن است که شما در فرمول متنی را نوشته‌اید و فراموش کرده‌اید که آن را در داخل علامت " "  (بخوانید کوتیشن یا دقیق تر دبل کوت) قرار دهید. به مثال‌های زیر دقت کنید که می‌خواهیم عبارت سلام را به سلول A1 بچسبانیم:

= salam & A1

که خطای#Name را خواهید گرفت و صحیح آن به شکل زیر است:

= "salam" & A1

نکته) بدیهی است که اگر نام تابع sum به اشتباه sun و یا vlookup را اشتباه vlokup بنویسید هم همین خطا را خواهید گرفت.

خطای #REF به دلیل حذف شیت، سلول و یا سطر و ستون

در فرمول زیر آدرس سلول A1‌ و همچنین شیتی به نام salam به کار رفته شده است:

= salam!A1 + 2

 حال اگر سطر 1 و یا ستون A‌ و یا شیت salam را حذف (Delete کنید)، آدرس‌ها به #REF! تبدیل خواهند شد:

= #REF!A1 + 2
or
= salam!#REF! + 2 

خطای #REF به دلیل Copy - Paste فرمول نسبی

این حالت، دومین دلیل شایع خطای REF اکسل است. فرض کنید که فرمول زیر در سلول C1 نوشته شده است:

= A1 + 2

می‌دانیم که آدرس A1 در این فرمول نسبی است و در واقع این فرمول مقدار ۳ سلول قبل‌ترش را با عدد ۲ جمع می‌زند و اگر همین فرمول را در ستون B و یا A اکسل کپی کنید، حتما خطای #REF! را خواهید گرفت، چون ۳ ستون قبل از آن‌ها واقعا وجود ندارد.

خطای #REF به آدرس خارج از منطقه در VLOOKUP

فرمول زیر را در نظر بگیرید:

= VLOOKUP( 100, A:C, 5, 0 )

شما از تابع VLOOKUP اکسل خواسته‌اید که در محدوده A:C جستجو کنید و سپس مقداری که در ۵ امین ستون این منطقه است را برای شما بیاورید. این کار متناقض است زیرا A:C کلا ۳ ستون است و غیر ممکن است که بتواند این تابع برای شما ۵ امین ستونش را بیاورد.

خطای #REF به دلیل آدرس اشتباه شیت

اگر در فرمولی به نام شیتی که وجود ندارد اشاره کنید (مثلا نام شیت را اشتباه تایپ کنید)، اکسل ابتدا یک پنجره باز می‌کند و از شما می‌خواهد که فایلی که این شیت در آن است را انتخاب کنید، و اگر شما فایلی را انتخاب نکنید، خطای #REF! را خواهید دید.

رفع خطای !REF# در اکسل

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

۱) با زدن کلید CTRL + Z سعی کنید که سطر و ستون‌های حذف شده را بازگردانید.

۲) با بستن و ذخیره نکردن فایل و سپس باز کردن مجدد فایل، همه چیز را به حالت قبل از حذف برگردانید.

۳) اگر فقط ۱ شیت حذف شده است و خطای  #REF! را دارید، می‌توانید با زدن کلید CTRL + H به پنجره Relpace اکسل بروید و همه !REF# را با نام شیت مناسب، جایگزین کنید.

۴) شاید بخواهید که همه خطاهای #REF! را حذف کنید، برای اینکار از ابزار Go To Special اکسل می‌توانید این خطاها را بیابید و پاک کنید. (در قسمت مقالات مرتبط همین صفحه، لینک آموزش آن گذاشته شده است)

پیشگیری از خطای #REF!

کار صحیح در اکسل این است که قبل از حذف سطر و یا ستون و یا یک شیت در اکسل، مطمئن شوید که هیچ فرمولی به آن وابسته نیست.

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

پیشگیری از خطای #Div/0 در اکسل

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

= IFERROR( A1 / A2, "!!" )

سایر نکته‌ها و خطاهای اکسل

خلاصه این پیام:‌ فرمول شما خطای جدی و غیر قابل تصحیحی دارد. 

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

خلاصه این پیغام: فرمول شما خطایی داشت و آن را به این شکل اصلاح می‌کنم:

تنظیمات خطایابی از Excel Options

شناخت انواع خطاها دراکسل

چگونه از خطا شدن خروجی یک فرمول جلوگیری کنیم.