سبد خرید 0

وبلاگ

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

خطای فرمول نویسی در اکسل چیست؟

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

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

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

نام خطانام کاملتوضیحات خطا
#N/ANot Available Errorمواجه با این خطا در اکسل که معمولترین ارور فرمول نویسی در اکسل می باشد، اگر تابع نتونه مقداری رو پیدا کنه این ارور در اکسل ظاهر میشه
#NAME?Naming Errorزمانی که قسمتی از تابع یا آرگومان رو اشتباهی تایپ کنین این ارور در اکسل ظاهر میشه.یعنی اشتباه در تایپ کردن تابع یا آرگومان اون داشته باشین.
#NULL!Null Errorزمانی که شما از طریق تعریف محدوده داخل تابع، حد اشتراک دو محدوده رو تعریف کنید ولی اون حد اشتراک وجود نداشته باشد این خطا ظاهر میشود
#DIV/0!Division Errorزمانی که یک عدد بر صفر تقسیم شده باشد، این خطا ظاهر میشود.
#VALUE!Value Errorزمانی که متغیر(های) تابع از نوع تعیین شده در آرگومان تابع نباشد این خطا ظاهر میشود.برای مثال ورودی آرگومان تابع از نوع عدد باشد ولی شما متن یا یک چیز دیگر وارد کنید.
#NUM!Number Errorزمانی که به تابع یک عددی بدهید که تابع آن عدد را نامعتبر بشناسد این خطا ظاهر میشود.
مثلا تابع: SQRT(-10)             0
#REF!Reference Errorزمانی که سلول مرجع مورد استفاده شده در تابع، وجود نداشته باشد یا حذف شده باشد این خطا ظاهر می شود.

بررسی خطا در اکسل و روش رفع ارور:

خطای #N/A :

در مثال زیر چون کد مشتری 1009  در لیست وجود ندارد و توسط تابع VLOOKUP یافت نمی شود این خطا ایجاد می شود.

حتما بخوانید:   آخرین سلول پر یا سطر پر در یک جدول اکسل را چگونه پیدا کنیم؟

راه حل: این ارور در اکسل زیاد نگران کننده نیست.اگر شما مقدار جست و جویی را که به تابع می دهید در لیست پیدا شود این خطا رفع می شود.برای عدم نمایش آن هم می توان از تابع ()IFERROR کمک گرفت.

خطای فرمول نویسی n/a# در اکسل
شکل شماره 1 – خطای n/a# در اکسل

خطای #NAME?:

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

راه حل: نام تابع را درست تایپ کنید.

ارور ?NAME# در اکسل
شکل شماره 2 – خطای ?NAME# در اکسل

نکته: اگر بخواهیم در آدرس دهی های فرمول حد اشتراک چند محدوده را تعریف کنیم کافیه بین محدوده ها یک فاصله(space) بندازیم.به فرمول SUM در شکل زیر توجه کنید.

خطای #NULL!:

در فرمول شکل زیر تعریف کردیم که جمع حداشتراک محدوده J3:J4 و J6:J7 را بدست بیاورد.اما همانطور که می بینید هیچ حداشتراکی بین این دو محدوده وجود ندارد که درنهایت این خطا رخ می دهد.

راه حل: محدوده را طوری انتخاب کنید که دو محدوده دارای محدوده مشترک باشند.در این مثال اگر فرمول مثلا به شکل SUM(J3:J6 J4:J7) نوشته شود مشکل حل می شود.که محدوده مشترک آنها J4:J6 خواهد بود.

خطای فرمول نویسی !NULL# در اکسل
شکل شماره 3 – خطای !NULL# در اکسل

خطای #DIV/0!:

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

حتما بخوانید:   تابع SUMIFS و کاربرد آن برای جمع های چند شرطی

راه حل: فرمولی که قبل از عبارت تقسیم “/” قرار دارد عدد صفر تولید می کند.اگر این نتیجه در محاسبات شما محتمل هست جای نگرانی نیست.درغیر اینصورت فرمول هایی که قبل از عبارت تقسیم “/” قرار دارند را دوباره بررسی کنید تا مقدار 0 را تولید نکند.

خطای DIV/0 در اکسل
شکل شماره 4 – خطای DIV/0 در اکسل

خطای #VALUE!:

به فرمول جمع در شکل زیر توجه کنید.ما قصد داریم حاصل جمع دو محدوده J4 و J3 را بدست بیاوریم.قاعدتا برای جمع داده ها باید از نوع عددی باشند که در مثال زیر نوع داده سلول J3 از نوع متنی می باشد و  این خطا ایجاد می شود.

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

خطای !VALUE# در اکسل
شکل شماره 5 – خطای !VALUE# در اکسل

خطای #NUM!:

همانطور که می دانید برای گرفتن جذر یک عدد باید یک عدد مثبت باشد.اما در مثال زیر عدد 10- وارد شده که این عدد ازنظر تابع SQRT اعتبار محاسباتی ندارد.

حتما بخوانید:   تابع LEN در اکسل با مثال پیشرفته ترکیب توابع

راه حل: با توجه به راهنمای تابع یک مقدار قابل قبول برای تابع معرفی کنید.که در این مثال باید عدد 10 را وارد کنیم.

خطای !NUM# در اکسل
شکل شماره 6 – خطای !NUM# در اکسل

خطای #REF!:

اگر شما برای یک فرمول محدوده ای مثلا a1:a5 رو تعریف کنید بعد بیایید این محدوده رو Delete کنید که در نتیجه این ارور در اکسل رخ می دهد.

راه حل: عبارت #REF! را پاک کرده و دوباره آدرس دهی کنید.

خطای !REF# در اکسل
شکل شماره 7 – خطای !REF# در اکسل

شبه خطا در اکسل:

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

Circular#Circular Errorزمانی که یک تابع یا فرمول را به یک سلول ارجاع بدهید و دوباره همان سلول به سلول اولی ارجاع دهید یک پنجره حاوی این ارور در اکسل ظاهر میشود.
#####Column Width Errorزمانی که عرض مقدار وارد شده داخل سلول بیشتر از عرض ستون باشد این حالت پیش میاد.که با بیشتر کردن عرض ستون مشکل حل میشه

منابع:

blog.hubspot.com

exceljet.net

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

6 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

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

    • با سلام.

      ابتدا ببینید کاراکتر جداکننده توابع در تنظیمات کنترل پنل سیستم شما کدام هست.

      از آموزش ویدئویی زیر میتونید استفاده کنید:
      تنظیم کاراکتر جداکننده توابع در اکسل

  • با سلام وقتتون بخیر
    مشکلی داشتم در رابطه با نمره ای که صفر وارد شده و سلولی که محتوای آن delete شده ولی در هنگام جستجوی محتوا، مقدار آن صفر در نظر گرفته میشود
    سلولی را که قبلا در آن delete زدیم و هیچ مقداری ندارد، موقع جستجوی محتوای سلول با استفاده از دستور index و match ، مقدار آن سلول را صفر نمایش می دهد ولی می خواهم هیچ عددی را نمایش ندهد چکار کنم؟

    مثلا این دستور:
    =IFERROR(INDEX($J$4:$J$43؛MATCH($E$50؛$E$4:$E$43؛0)؛0)؛””)

  • سلام لطف میکنین جواب این سوال و بدین
    در صورت استفاده از تابع vlook up در نرم افزار اکسل اگر مقدار مورد جستجو کوچکتر از کوچکترین مقدار اولین ستون جدول باشد کدام خطا رخ می دهد

  • سلام خسته نباشید من فاکتور رو میزنم بعد د آخر با تابع sum جمع میکنم با ماشین حساب تقریبا هر بار ۲۰۰ الی ۳۰۰ هزار تومان اختلاف داره میشه راهنمایی کنید ؟

    • سلام. وقت بخیر.

      بررسی کنید فرمت سلولی Text نباشد.

      اعداد رو بصورت دسته ای جمع بزنید ببینید کدوم اعداد در جمع لحاظ نمی شود.

دیدگاهتان را بنویسید