خطاهای فرمول نویسی در اکسل
انواع خطا در اکسل (Excel Errors)
اگر در تابع یا فرمولی که در اکسل می نویسیم اشکالی وجود داشته باشه، با پیام های مختلفی خطا (مثل Value!, #N/A# و …) روبرو میشیم. نوع پیام خطایی که با اون مواجه میشیم به ما کمک میکنه تا علت خطا رو متوجه بشیم. پس باید با انواع خطا در فرمول نویسی و علت بروز اونها آشنا باشیم. انواع خطا در اکسل رو در جدول زیر مشاهده میکنید:
| #NULL! | زمانی که دو محدوده مورد نظر ما تقطعی نداشته باشند |
| #DIV/0! | زمانی این خطا ظاهر میشه که عددی رو تقسیم بر ۰ کنیم |
| #VALUE! | این خطا زمانی است که متغیرهای تابع از جنس مورد نظر نباشن (مثلا آرگومانی باید عدد بگیره، اما متن اختصاص بدیم) |
| #REF! | زمانی که سل مرجع مورد استفاده در تابع، وجود نداشته باشه یا حذف شده باشه |
| #NAME? | زمانی که نام فرمول به درستی تشخیص داده نشه (دیکته نادرست تابع) یا متن بدون “” داخل فرمول داشته باشیم |
| #NUM! | زمانی که اکسل با یک عدد نا معتبر مواجه بشه |
| #N/A | زمانی که داده ای برای یک تابع موجود نیست. (Not Available) |
حالا برای هرکدام از این خطاها مثالی میزنیم:
انواع خطا: !NULL# (عدم وجود اشتراک)
شکل ۱- انواع خطا در اکسل – بروز خطای !NULL# هنگام عدم وجود اشتراک در محدوده ها
برای پیدا کردن اشتراک محدوده ها، بین آنها فاصله (Space) میگذاریم.
انواع خطا: !DIV/0 # تقسیم بر صفر)
شکل ۲- انواع خطا در اکسل – نمایش خطای !DIV/0 # هنکام تقسیم عدد بر صفر
انواع خطا: !VALUE# (نوع متغیر نادرست)
شکل ۳- انواع خطا در اکسل – خطای !VALUE# موقع تخصیص متغیر نادرست
انواع خطا: !REF# (حذف یا عدم وجود مرجع فرمول)
این خطا در دو صورت اتفاق می افتد:
- حذف مرجع سل. همونطور که در تصویر زیر مشاهده می کنید. با حذف مرجع فرمول، با خطای !REF# مواجه می شویم.
- ارجاع به محدوده نا موجود. همونطور که در شکل ۴ می بینید، فرمول A1+B1 که در سل B3 نوشته شده است بعد از درگ کردن به سمت چپ، با خطا مواجه می شه. چرا که قبل از ستون A محدوده ای در اکسل وجود نداره. پس B1 به A1 تبدیل شده و A1 با خطا مواجه می شه.
شکل ۴- انواع خطا در اکسل – ارجاع محدوده ناموجود به فرمول و خطای !REF#
خطای #REF! اصولا بخاطر آدرس دهی نادرست اتفاق می افته. تسلط به انواع آدرس دهی از اصول فرمول نویسی حرفه ای بشمار میره.
خطای !NAME#: تایپ نادرست فرمول یا متن در فرمول
شکل ۵- خطاهای اکسل – تایپ اشتباه تابع SUM و بروز خطای ?NAME#
اگر در تابع متنی می نویسیم باید داخل “” قرار گرفته باشه. در غیراینصورت طبق شکل ۶ با خطای ?NAME# مواجه میشیم.
شکل ۶- خطاهای اکسل – تایپ متن در فرمول بدون “” و بروز خطای ?NAME#
خطای !NUM#: برخورد با عدد نامعتبر
همونطور که میدونید اعداد منفی ریشه دوم ندارند. پس وقتی عدد منفی در تابع جذر وارد میکنیم، با خطای !NUM# مواجه میشیم.
شکل ۷- خطاهای اکسل – ورود عدد منفی در تابع جذر و بروز خطای !NUM#
خطای N/A#: عدم وجود داده مورد نظر در تابع
شکل ۸- خطاهای اکسل – عدم وجود کلمه یزد در جدول مورد نظر و بروز خطای N/A#
گاهی اوقات مطمئن هستیم که داده مورد نظر در جدول موجود هست اما تابع VLOOKUP اونو پیدا نمیکنه و با خطای N/A# مواجه میشیم. این موضوع نیاز به بررسی داره که در آینده به آن خواهیم پرداخت.
پیدا کردن علت خطا و ریشه اون در فرمول های طولانی و پیچیده کار مشکلی هست. همونطور که میدونید اگر کوچک ترین جزء یک تابع با خطا مواجه باشه، خروجی کل فرمول نوشته شده خطا خواهد بود. پس اینکه بتونیم بفهمیم خطای خروجی ناشی از کدوم قسمت فرمول نوشته شده است اهمیت خیلی زیادی داره. بهترین روش برای این موضوع تسلط به روش های عیب یابی (Debug) فرمول هست.
علاوه بر خطاهای معرفی شده در بالا یک خطای دیگه وجود داره به نام خطای Circular Reference که در آموزش های بعدی در موردش صحبت میکنیم.
بعد از تسلط به علل بروز خطا و راه های رفع اشکال و خطایابی فرمول، نوبت به بحث مدیریت خطا میرسه که حتما باید با فرمول های این بخش نیز آشنا باشید.
ممنون از مطالب مفیدتون
با سلام خدمت شما یه مشکلی واسم پیش اومده وقتی جمع یک ستون رو انتخاب می کنم یک عدد در گوشه نوار ابزار پایین داریم که با عدد سام تفاوت دارد خواستم بدونم چه دلیلی داره؟
سلام
اگر ممکنه برخی از سلول های به صورت متن وارد شده باشند. در اینصورت گوشه سمت چپ بالای آنها یک علامت سبز رنگ هستند.
باید تبدیل به عدد بشن
علت اینکه توی یک ستون تایپ میکنم ۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱ و بعد اکسل بصورت۱.11111E+24
نشان میدهد چیست؟
فرمت سلول عوض میشه. نماد علمی برای نمایش اعداد خیلی بزرگ یا اعداد خیلی کوچیک بکار میره
میتونید بذارید در حالت general تا همونو نمایش بده فقط دقت داشته باشید اعداد بیش از ۱۵ رقم رو نمیتونید با فرمت Number وارد سلول کنید
سلام لطفا راهنمایی کنید با این پیام چکار کنم
verify that the file has not been corrupted and that the file extension matches the format of the file
درود بر شما
این خطا عموما موقعی رخ میده که فایل خراب شده باشه
مثلا در حین کار، برق قطع شده باشه، یا نرم افزار هنگ کرده باشه و …
راهی که میشه تست کرد که شاید فایل برگرده، اینه که از قسمت open گزینه open and repair رو بزنید
سلام
لطفا در مورد علامت تعجب که در داخل مربع و کنار یه مثلث سبز رنگ در کنار سلول میاد (خطا در مورد فرمول) توضیح دهید . ممنون
درود بر شما
اون هشار، تا حدی میتونه خطا رو بگیره. ولی خیلی کلی.
مثلا وقتی میانگین یک محدوده میگیرید و درگ ممیکنید و $ درست نذاشته باشید، حدس میزنه که $ ها غلطه. یا با توجه به نوع درگ گکردن و چینش داده ها این حدس و میزنه.
یا اینکه روی اعدادی که بصورت متنی ذخیره شده باشن این هشدار نمایش داده میشه که میگه اعداد و بصورت متن ذخیره کردی و …