جشنواره تابستانی اکسل پدیا (یک روز تا پایان)
سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

انواع خطا
۴.۲/۵ - (۵ امتیاز)

انواع خطا در اکسل (Excel Errors)

اگر در تابع یا فرمولی که در اکسل می نویسیم اشکالی وجود داشته باشه، با پیام های مختلفی خطا (مثل Value!, #N/A# و …) روبرو میشیم. نوع پیام خطایی که با اون مواجه میشیم به ما کمک میکنه تا علت خطا رو متوجه بشیم. پس باید با انواع خطا در فرمول نویسی و علت بروز اونها آشنا باشیم. انواع خطا در اکسل رو در جدول زیر مشاهده میکنید:

#NULL! زمانی که دو محدوده مورد نظر ما تقطعی نداشته باشند
#DIV/0! زمانی این خطا ظاهر میشه که عددی رو تقسیم بر ۰ کنیم
#VALUE! این خطا زمانی است که متغیرهای تابع از جنس مورد نظر نباشن (مثلا آرگومانی باید عدد بگیره، اما متن اختصاص بدیم)
#REF! زمانی که سل مرجع مورد استفاده در تابع، وجود نداشته باشه یا حذف شده باشه
#NAME? زمانی که نام فرمول به درستی تشخیص داده نشه (دیکته نادرست تابع) یا متن بدون “” داخل فرمول داشته باشیم
#NUM! زمانی که اکسل با یک عدد نا معتبر مواجه بشه
#N/A زمانی که داده ای برای یک تابع موجود نیست. (Not Available)

حالا برای هرکدام از این خطاها مثالی میزنیم:

انواع خطا: !NULL# (عدم وجود اشتراک)

خطاهای اکسل - بروز خطای #NULL! هنگام عدم وجود اشتراک در محدوده ها

شکل ۱- انواع خطا در اکسل – بروز خطای !NULL# هنگام عدم وجود اشتراک در محدوده ها

نکته:
برای پیدا کردن اشتراک محدوده ها، بین آنها فاصله (Space) میگذاریم.

 

انواع خطا: !DIV/0 # تقسیم بر صفر)

خطاهای اکسل - نمایش خطای #DIV/0! هنکام تقسیم عدد بر صفر

شکل ۲- انواع خطا در اکسل – نمایش خطای !DIV/0 # هنکام تقسیم عدد بر صفر

انواع خطا: !VALUE# (نوع متغیر نادرست)

خطاهای اکسل - خطای #VALUE! موقع تخصیص متغیر نادرست

شکل ۳- انواع خطا در اکسل – خطای !VALUE# موقع تخصیص متغیر نادرست

انواع خطا: !REF# (حذف یا عدم وجود مرجع فرمول)

این خطا در دو صورت اتفاق می افتد:

  • حذف مرجع سل. همونطور که در تصویر زیر مشاهده می کنید. با حذف مرجع فرمول، با خطای !REF# مواجه می شویم.

خطاهای اکسل - خطای Ref

  • ارجاع به محدوده نا موجود. همونطور که در شکل ۴ می بینید، فرمول A1+B1 که در سل B3 نوشته شده است بعد از درگ کردن به سمت چپ، با خطا مواجه می شه. چرا که قبل از ستون A محدوده ای در اکسل وجود نداره. پس B1 به A1 تبدیل شده و A1 با خطا مواجه می شه.

انواع خطا - ارجاع محدوده ناموجود به فرمول و خطای #REF!

شکل ۴- انواع خطا در اکسل – ارجاع محدوده ناموجود به فرمول و خطای !REF#

نکته:
خطای #REF! اصولا بخاطر آدرس دهی نادرست اتفاق می افته. تسلط به انواع آدرس دهی از اصول فرمول نویسی حرفه ای بشمار میره.

 

خطای !NAME#: تایپ نادرست فرمول یا متن در فرمول

انواع خطا - تایپ اشتباه تابع SUM و بروز خطای #NAME!

شکل ۵- خطاهای اکسل – تایپ اشتباه تابع SUM و بروز خطای ?NAME#

اگر در تابع متنی می نویسیم باید داخل “” قرار گرفته باشه. در غیراینصورت طبق شکل ۶ با خطای ?NAME# مواجه میشیم.

خطاهای اکسل - تایپ متن در فرمول بدون "" و بروز خطای #NAME!

شکل ۶- خطاهای اکسل – تایپ متن در فرمول بدون “” و بروز خطای ?NAME#

خطای !NUM#: برخورد با عدد نامعتبر

همونطور که میدونید اعداد منفی ریشه دوم ندارند. پس وقتی عدد منفی در تابع جذر وارد میکنیم، با خطای !NUM# مواجه میشیم.

 خطاهای اکسل - ورود عدد منفی در تابع جذر و بروز خطای #NUM!

شکل ۷- خطاهای اکسل – ورود عدد منفی در تابع جذر و بروز خطای !NUM#

 خطای N/A#: عدم وجود داده مورد نظر در تابع

خطاهای اکسل - عدم وجود کلمه یزد در جدول مورد نظر و بروز خطای #N/A

شکل ۸- خطاهای اکسل – عدم وجود کلمه یزد در جدول مورد نظر و بروز خطای N/A#

گاهی اوقات مطمئن هستیم که داده مورد نظر در جدول موجود هست اما تابع VLOOKUP اونو پیدا نمیکنه و با خطای N/A# مواجه میشیم. این موضوع نیاز به بررسی داره که در آینده به آن خواهیم پرداخت.

نکته:
پیدا کردن علت خطا و ریشه اون در فرمول های طولانی و پیچیده کار مشکلی هست. همونطور که میدونید اگر کوچک ترین جزء یک تابع با خطا مواجه باشه، خروجی کل فرمول نوشته شده خطا خواهد بود. پس اینکه بتونیم بفهمیم خطای خروجی ناشی از کدوم قسمت فرمول نوشته شده است اهمیت خیلی زیادی داره. بهترین روش برای این موضوع تسلط به روش های عیب یابی (Debug) فرمول هست.

 

علاوه بر خطاهای معرفی شده در بالا یک خطای دیگه وجود داره به نام خطای Circular Reference که در آموزش های بعدی در موردش صحبت میکنیم.

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

کلیدواژه : متوسط
آواتار
127

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

دیدگاه کاربران
  • ایرج ۲ بهمن ۱۳۹۸ / ۹:۳۰ ق٫ظ

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

    • سامان چراغی ۲ بهمن ۱۳۹۸ / ۱۱:۲۸ ق٫ظ

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

  • Ali ۸ دی ۱۳۹۸ / ۲:۰۸ ب٫ظ

    علت اینکه توی یک ستون تایپ میکنم ۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱۱ و بعد اکسل بصورت۱.۱۱۱۱۱E+24
    نشان میدهد چیست؟

    • آواتار
      حسنا خاکزاد ۸ دی ۱۳۹۸ / ۹:۲۴ ب٫ظ

      فرمت سلول عوض میشه. نماد علمی برای نمایش اعداد خیلی بزرگ یا اعداد خیلی کوچیک بکار میره
      میتونید بذارید در حالت general تا همونو نمایش بده فقط دقت داشته باشید اعداد بیش از ۱۵ رقم رو نمیتونید با فرمت Number وارد سلول کنید

  • MAHDI ۶ آذر ۱۳۹۸ / ۱۱:۴۸ ق٫ظ

    سلام لطفا راهنمایی کنید با این پیام چکار کنم
    verify that the file has not been corrupted and that the file extension matches the format of the file

    • آواتار
      حسنا خاکزاد ۸ آذر ۱۳۹۸ / ۸:۰۲ ب٫ظ

      درود بر شما
      این خطا عموما موقعی رخ میده که فایل خراب شده باشه
      مثلا در حین کار، برق قطع شده باشه، یا نرم افزار هنگ کرده باشه و …
      راهی که میشه تست کرد که شاید فایل برگرده، اینه که از قسمت open گزینه open and repair رو بزنید

  • مهدی ارجمند ۱۰ مرداد ۱۳۹۸ / ۱۲:۳۶ ب٫ظ

    سلام
    لطفا در مورد علامت تعجب که در داخل مربع و کنار یه مثلث سبز رنگ در کنار سلول میاد (خطا در مورد فرمول) توضیح دهید . ممنون

    • آواتار
      حسنا خاکزاد ۱۲ مرداد ۱۳۹۸ / ۴:۱۳ ب٫ظ

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان