وبلاگ

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

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

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

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

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

نام خطانام کاملتوضیحات خطا
#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 می شود که نهایتا دچار این خطا می شود.

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

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

خطای #VALUE!:

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

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

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

خطای #NUM!:

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

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

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

خطای #REF!:

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

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

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

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

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

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

منابع:

blog.hubspot.com

exceljet.net

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

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

18 دیدگاه

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

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

    • با سلام.

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

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

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

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

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

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

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

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

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

  • سلام روزتون بخیر. من ی فایل اکسل میخواستم آپلود کنم خطای زیر میده
    arithmetic overflow error converting expression to data type
    باید چیکا کنم؟ ممنونم از کمکتون

  • سلام
    من توی یکی از سل ها یه عدد رو وارد کردم. توی سل بعدی مساوی رو میذارم و این فرمول رو مینویسم:
    P=82*T/V-42.82-3604115/V^2
    اما موقع کلیک ارور هیچ اسم خاصی نداره و فقط میگه که اگه میخوای فرمول بنویسی مساوی بذار اگه میخوای خودشو ببینی یه آپاستروف بذار
    حتی فرمول رو هم به دو قسمت تقسیم کردم ولی باز هم به قسمت دوم ارور میده:
    D6-3604115/C6^2
    به C6 ارور میده

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

      برای فرمول نویسی در اکسل حتما باید ابتدا علامت مساوی (=) را درج کنید.

      مثلا:

      42+11=

  • سلام
    مشکلی با کاراکتر جداسازی در فرمول دارم، راهنمایی کنید. مانند زیر
    =INDEX(Customers!D:D؛MATCH(I1963؛Customers!A:A؛0))

  • سلام من در زمان باز کردن فایل اکسل با خطا مواجه میشم
    excel found unreadable content in(…) xlxs.do you want to recover the contents of this workbook ?if you trust the source of this work book .click yes
    بعد دو تا گزینه yes , no داره زمانی که روی yes کلیک میکنم
    میاد
    the workbook can not be opened or repaired buy microsoft excel because it is corrupt چطور میتونم این مشکل رو حل کنم چون یک نرم افزار repairxlx,xlxs هم دانلود کردم فایل رو repair میکنه ولی چون آفیس در ایران کرک شده هست برای save از من active key میخواهد که ندارم میشه زاهنمایی کنید چطور مشکل رو حل کنم

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

      اول از همه آفیستون رو کرک کنید.

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

  • سلام جناب بنده با اعداد غیر صفر وقتی میانگین میگیرم ارورdiv/0 میده و همینطور برای sum و max نتیجه ی0 میده دلیلش چی میتونه باشه؟

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

      نتایج فرمول شما تقسیم عدد بر صفز میشه که در عمل ریاضی تعریف نشده هستش.

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