لیست ها و محدوده ها (5)
ماکروها (روش های VBA) (63)
متفرقه (39)
اشکالات و اشکالات اکسل (3)

نحوه نشان دادن 0 به جای خطا در یک سلول با فرمول

شرایطی وجود دارد که فرمول های زیادی بر روی برگه های یک کتاب کار ایجاد شده است که وظایف مختلفی را انجام می دهد. علاوه بر این، فرمول ها مدت ها پیش، شاید حتی توسط شما ایجاد شده اند. و فرمول ها خطاها را برمی گرداند. به عنوان مثال #DIV/0! (#DIV/0!) . اگر تقسیم بر صفر در فرمول = A1 / B1 رخ دهد که B1 صفر یا خالی است. اما ممکن است خطاهای دیگری نیز وجود داشته باشد (#N/A، #VALUE!، و غیره). شما می توانید فرمول را با اضافه کردن یک بررسی خطا تغییر دهید:

=IF(ISERR(A1 / B1)، 0، A1 / B1)
استدلال ها:
=IF(EOSH(1 آرگومان)، 2 آرگومان، 1 آرگومان)
این فرمول ها در هر نسخه ای از اکسل کار می کنند. درست است، تابع EOS خطای #N/A (#N/A) را پردازش نخواهد کرد. برای پردازش #N/A به همین روش، باید از تابع ERROR استفاده کنید:
=IF(ISERROR(A1 / B1)، 0، A1 / B1)
=IF(ISERROR(A1 / B1)، 0، A1 / B1)
با این حال، در ادامه متن از EOSH استفاده خواهم کرد (از آنجایی که کوتاهتر است) و علاوه بر این، همیشه لازم نیست که خطای #N/A "دیدن" نباشد.
اما برای نسخه های اکسل 2007 و بالاتر، می توانید از یک تابع کمی بهینه تر استفاده کنید IFERROR:
=IFERROR(A1 / B1 ;0)
=IFERROR(A1 / B1,0)
استدلال ها:
=IFERROR(1 آرگومان؛ 2 آرگومان)

1 آرگومان: عبارت برای محاسبه
آرگومان دوم: مقدار یا عبارتی که در صورت وجود خطایی در آرگومان اول باید به سلول برگردانده شود.

چرا IFERROR بهتر است و چرا آن را بهینه تر می نامم؟ بیایید به فرمول اول با جزئیات بیشتری نگاه کنیم:
=IF(EOSH(A1 / B1)، 0، A1 / B1)
اگر مرحله به مرحله محاسبه کنیم، خواهیم دید که ابتدا عبارت A1 / B1 محاسبه می شود (یعنی تقسیم). و اگر نتیجه آن خطا باشد، EOSH TRUE را برمی گرداند که به IF ارسال می شود. و سپس تابع IF مقدار آرگومان دوم 0 را برمی گرداند.
اما اگر نتیجه اشتباه نباشد و ISERR FALSE را برگرداند، تابع عبارت محاسبه شده قبلی را دوباره محاسبه می کند: A1 / B1
با فرمول داده شده این نقش خاصی ندارد. اما اگر فرمولی مانند VLOOKUP با جستجوی چندین هزار ردیف استفاده شود، محاسبه دوبار آن می تواند زمان محاسبه مجدد فرمول ها را به میزان قابل توجهی افزایش دهد.
تابع IFERROR عبارت را یک بار ارزیابی می کند، نتیجه آن را به خاطر می آورد و اگر نادرست باشد، آنچه را که به عنوان آرگومان دوم نوشته شده است برمی گرداند. اگر خطایی وجود نداشته باشد، نتیجه ذخیره شده محاسبه عبارت را از آرگومان اول برمی گرداند. آن ها محاسبه واقعی یک بار انجام می شود که عملاً هیچ تأثیری بر سرعت محاسبه مجدد کلی فرمول ها نخواهد داشت.
بنابراین، اگر اکسل 2007 و بالاتر دارید و فایل در نسخه های قبلی استفاده نمی شود، منطقی است که از IFERROR استفاده کنید.

اصلا چرا باید فرمول های دارای خطا اصلاح شوند؟ این معمولاً برای نمایش داده‌ها در گزارش‌ها با زیبایی بیشتر انجام می‌شود، به خصوص اگر گزارش‌ها سپس به مدیریت ارسال شوند.

بنابراین، فرمول هایی در برگه وجود دارد که خطاهای آنها باید پردازش شود. اگر یک یا دو فرمول مشابه برای اصلاح وجود داشته باشد (یا حتی 10-15)، پس تقریباً هیچ مشکلی برای جایگزینی دستی آنها وجود ندارد. اما اگر چندین ده یا حتی صدها فرمول از این دست وجود داشته باشد، مشکل تقریباً ابعاد جهانی پیدا می کند :-). با این حال، فرآیند را می توان با نوشتن نسبتاً ساده کرد کد ساده ویژوال بیسیکبرای برنامه
برای تمام نسخه های اکسل:

Sub IfIsErrNull() Const sToReturnVal As String = "0" , vbInformation, "www.site" Exit Sub End If For Her rc در rr If rc.HasFormula سپس s = rc.Formula s = Mid(s, 2) ss = " =" & "IF(ISERR(" & s & ")،" & sToReturnVal & "," & s & ")" اگر سمت چپ(ها، 9)<>"IF(ISERR(" Then If rc.HasArray then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number then ss = rc.Address rc. Exit For End If End If End If Next rc If Err را انتخاب کنید .Number سپس MsgBox "فرمول پردازش شده"

Sub IfIsErrNull() Const sToReturnVal As String = "0" "اگر لازم باشد به جای صفر خالی برگردانده شود "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) اگر rr چیزی نیست پس MsgBox "محدوده انتخاب شده حاوی داده ای نیست"، vbInformation، "www..HasFormula سپس s = rc.Formula s = Mid(s, 2) ss = "=" & "IF(ISERR(" & s & ")،" & sToReturnVal & "," & s & ")" اگر سمت چپ(های 9)<>"IF(ISERR(" Then If rc.HasArray then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number then ss = rc.Address rc. Exit For End If End If End If Next rc If Err را انتخاب کنید .Number سپس MsgBox "نمی توان فرمول را در سلول تبدیل کرد: " & ss & vbNewLine & _ Err.Description، vbInformation، "www..site" End If End Sub

برای نسخه های 2007 و بالاتر

Sub IfErrorNull() Const sToReturnVal As String = "0" "در صورت لزوم، به جای صفر، خالی برگردید "Const sToReturnVal As String = """""" Dim rr به عنوان محدوده، rc به عنوان محدوده Dim s به عنوان رشته، ss به عنوان رشته در خطا از سرگیری مجموعه بعدی rr = Intersect(Selection, ActiveSheet.UsedRange) اگر rr چیزی نیست پس MsgBox "محدوده انتخاب شده حاوی داده ای نیست", vbInformation, "www.site" خروج Sub End If برای هر rc در rr اگر rc.HasFormula سپس s = rc.Formula s = Mid(s, 2) ss = "=" & "IFERROR(" & s & ", " & sToReturnVal & ")" اگر سمت چپ (های 8)<>"IFERROR(" سپس اگر rc.HasArray سپس rc.FormulaArray = ss در غیر این صورت rc.Formula = ss پایان اگر اگر Err.Number سپس ss = rc.آدرس rc.انتخاب خروج برای End If End If End اگر بعد rc اگر Err.Number سپس MsgBox "نمی توان فرمول را در سلول تبدیل کرد:"& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Else MsgBox "فرمول پردازش شده"، vbInformation، "www.site" End If End Sub

Sub IfErrorNull() Const sToReturnVal As String = "0" "اگر لازم است به جای صفر خالی برگردانده شود "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) اگر rr چیزی نیست پس MsgBox "محدوده انتخاب شده حاوی داده ای نیست"، vbInformation، "www..HasFormula سپس s = rc.Formula s = Mid(s, 2) ss = " =" & "IFERROR(" & s & "," & sToReturnVal & ")" اگر سمت چپ(های 8)<>"IFERROR(" سپس اگر rc.HasArray سپس rc.FormulaArray = ss در غیر این صورت rc.Formula = ss پایان اگر اگر Err.Number سپس ss = rc.آدرس rc.انتخاب خروج برای End If End If End اگر بعد rc اگر Err.Number سپس MsgBox "فرمول موجود در سلول را نمی توان تبدیل کرد: " & ss & vbNewLine & _ Err.Description، vbInformation، "www..site" End If End Sub

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

کد بالا را کپی کنید و به ویرایشگر VBA بروید( جایگزین+F11، یک ماژول استاندارد ایجاد کنید ( درج کنید -مدول) و فقط این کد را در آن قرار دهید. به کتاب کار اکسل مورد نظر رفته و تمام سلول هایی که فرمول آنها باید تبدیل شود را انتخاب کنید تا در صورت بروز خطا صفر شوند. مطبوعات جایگزین+F8، کد را انتخاب کنید IfIsErrNull(یا IfErrorNullبسته به اینکه کدام یک را کپی کرده اید) و فشار دهید اجرا کردن.
یک تابع رسیدگی به خطا به تمام فرمول های سلول های انتخاب شده اضافه می شود. کدهای داده شده همچنین موارد زیر را در نظر می گیرند:
-اگر فرمول قبلاً از تابع IFERROR یا IF(EOSH) استفاده کرده باشد، چنین فرمولی پردازش نمی شود.
-کد همچنین توابع آرایه را به درستی پردازش می کند.
-شما می توانید سلول های غیر مجاور (از طریق Ctrl) را انتخاب کنید.
چه ضرری دارد:فرمول های آرایه پیچیده و طولانی به دلیل ماهیت این فرمول ها و پردازش آنها از VBA می توانند باعث ایجاد خطای کد شوند. در این صورت کد در مورد عدم امکان ادامه کار می نویسد و سلول مشکل دار را برجسته می کند. بنابراین، من قویاً توصیه می‌کنم کپی‌های فایل‌ها را جایگزین کنید.
اگر مقدار خطا باید با خالی و نه صفر جایگزین شود، پس به رشته نیاز دارید

"Const sToReturnVal As String = """"""

حذف آپستروف ( " )

شما همچنین می توانید این کدبا فشار دادن یک دکمه (چگونه یک دکمه برای فراخوانی یک ماکرو در یک کاربرگ ایجاد کنیم) یا در یک افزونه قرار داده شده است (چگونه افزونه خود را ایجاد کنیم؟) تا بتوانید آن را از هر فایلی فراخوانی کنید.

و یک اضافه کوچک: سعی کنید از کد به طور متفکرانه استفاده کنید. بازگرداندن یک خطا همیشه مشکل ساز نیست. به عنوان مثال، هنگام استفاده از VLOOKUP، گاهی اوقات مفید است که ببینید کدام مقادیر یافت نشدند.
من همچنین می خواهم توجه داشته باشم که باید در فرمول های واقعی اعمال شود. زیرا اگر یک فرمول #NAME!(#NAME!) را برگرداند، این بدان معناست که برخی از آرگومان ها به اشتباه در فرمول نوشته شده است و این یک خطا در نوشتن فرمول است، نه خطا در نتیجه محاسبه. بهتر است برای جلوگیری از خطاهای منطقی در محاسبات در کاربرگ، چنین فرمول هایی را تجزیه و تحلیل کنید و خطا را پیدا کنید.

آیا مقاله کمکی کرد؟ لینک را با دوستان خود به اشتراک بگذارید دروس تصویری

("نوار پایین":("textstyle":"ایستا"،"textpositionstatic":"پایین"،"textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"پایین سمت چپ"،"textpositionmarginleft":24," textpositionmarginright":24"textpositionmargintop":24"textpositionmarginbottom":24"texteffect":"slide"، "texteffecteasing":"easeOutCubic"، "texteffectduration":600، "texteffectslidedirection": "leftlid"، "texteffects" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"اسلاید"،"texteffectslidedirection1":"راست"،"texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic:601"ext "texteffectdelay1":1000,"texteffect2":"slide", "texteffectslidedirection2":"راست"،"texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic"،"texteffectduration2":600""ext textcss":"display:block; padding:12px; text-align:left;""textbgcss":"display:block; position:absolute; top:0px; left:0px; عرض:100%؛ ارتفاع:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);""titlecss":"display:block; موقعیت:نسبی; فونت:پررنگ 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; موقعیت:نسبی; فونت: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; موقعیت:نسبی; margin-top:8px;""texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;,""descriptioncssresponsive":"display:none !important;""buttoncssresponsive" """"addgooglefonts":false,""googlefonts":""، "textleftrightpercentforstatic":40))

روز بخیر، دوستان!

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

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

خوب، در واقع این چیزی است که چگونه است خطا در فرمول هابرتری داشتن:

1. خطای #####.این یکی از رایج ترین و ساده ترین است خطاها در فرمول های اکسل . این فقط یک چیز دارد: عرض ستون به اندازه کافی گسترده نیست تا اطلاعات شما را به طور کامل نمایش دهد. راه حل این مشکل بسیار ساده است، نشانگر ماوس را به سمت حاشیه ستون ببرید و در حالی که دکمه سمت چپ را نگه داشته اید، سلول را بزرگ کنید تا داده ها شروع به نمایش کنند یا با دوبار کلیک کردن بر روی حاشیه ستون می توانید کلیک کنید. در عریض ترین سلول ستون
2. خطای #NAME؟. این خطا (#NAME؟)در فرمول های اکسل تنها زمانی رخ می دهد که ویرایشگر نتواند متن موجود در فرمول را تشخیص دهد (به عنوان مثال، یک خطا در نام تابع به دلیل اشتباه تایپی =SUM(A1:A4). خطا در فرمول هابرتری داشتن، باید آن را با دقت بخوانید و خطا را اصلاح کنید (A1:A4).
3. خطای #VALUE!. این خطا (#VALUE!)وقتی فرمول حاوی آرگومانی باشد که نوع آن برای محاسبات شما مناسب نیست ممکن است با این مشکل مواجه شوید. به عنوان مثال، یک مقدار متن =A1+B1+C1 در فرمول یا فرمول ریاضی شما درج شده است، جایی که C1 متن است. راه حل مشکل ساده است، از فرمولی استفاده کنید که سلول های حاوی متن را نادیده می گیرد یا فقط حذف می کند ارزش داده شدهاز سلول C1
4. خطای #BUSINESS/0. همانطور که از خطای ظاهر شده در فرمول می بینید، شما به سادگی آرگومان خود را در عدد 0 ضرب کردید و این کار بر اساس قوانین ریاضی قابل انجام نیست. برای تصحیح این خطا باید عدد را طوری تغییر دهید که با 0 برابر نشود یا فرمول مثلا منطقی را تغییر دهید که از خطا جلوگیری می کند. =IF(A2=0;””;A1/A2)
5. خطای #LINK!. این یکی از رایج ترین و گیج کننده ترین اشتباهات است توابع اکسل. وقتی این خطا را می بینید به این معنی است که فرمول به سلولی اشاره می کند که دیگر وجود ندارد. این امر به ویژه هنگامی که با حجم زیادی از داده در و کار می کنید مشکل ساز است مقدار زیاد. وقتی جداول خود را به این شکل ویرایش می کنید خطا در فرمول هابرتری داشتنآنها نباید شما را بترسانند، تعمیر آنها بسیار آسان است، فقط باید همه چیز را به جای خود بازگردانید، یا در صورت لزوم، فرمول را به صورت دستی بازنویسی کنید و استدلال اشتباه را از آن حذف کنید.

امیدوارم این مقاله در مورد آنها باشد خطا در فرمول هابرتری داشتنو تصحیح آنها برای شما مفید شد و چیز جدید و جالبی برای خود یاد گرفتید.

شما را در مقالات جدید می بینیم!

"چرا دنیا آنقدر مرتب شده است که افرادی که می دانند چگونه برای لذت زندگی کنند هرگز پول ندارند و آنها که پول دارند نمی دانند "هدر دادن زندگی خود" به چه معناست؟
D.B. نمایش دهید

اگر اکسل نتواند فرمول یا عملکرد کاربرگ را به درستی ارزیابی کند. مقدار خطا را نمایش می دهد - به عنوان مثال، #NAME؟، #NUMBER!، #VALUE!، #N/A، #EMPTY!، #LINK! - در سلولی که فرمول در آن قرار دارد. بیایید به انواع آن نگاه کنیم خطاها در اکسل، آنها دلایل ممکن، و نحوه از بین بردن آنها.

خطای #NAME؟

خطای #NAMEزمانی ظاهر می شود که نامی که در فرمول استفاده می شود حذف شده باشد یا قبلاً تعریف نشده باشد.

علل خطاهای #NAME؟:

1. اگر فرمول از نامی استفاده می کند که حذف شده یا تعریف نشده است.

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

عیب یابی: یک نام تعریف کنید. نحوه انجام این کار در این توضیح داده شده است.

1. خطا در نوشتن نام تابع:

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

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

1. ارجاع به طیف وسیعی از سلول‌ها یک کولون (:) وجود ندارد.

خطاها در اکسل - خطا در نوشتن محدوده ای از سلول ها

عیب یابی: فرمول را تصحیح کنید. در مثال بالا =SUM(A1:A3) است.

1. این فرمول از متنی استفاده می کند که در داخل آن قرار ندارد نقل قول های دوگانه. اکسل خطا می دهد، زیرا چنین متنی را به عنوان یک نام در نظر می گیرد.

خطاهای اکسل - خطا در ترکیب متن با یک عدد

عیب یابی: متن فرمول را در دو گیومه قرار دهید.

خطاهای اکسل - ادغام متن به درستی

خطای #NUMBER!

خطای #NUMBER! در اکسل اگر فرمول حاوی عدد نادرستی باشد نمایش داده می شود. مثلا:

1. وقتی مقدار مثبت مورد نیاز است از یک عدد منفی استفاده کنید.

خطاها در اکسل - خطا در فرمول، مقدار آرگومان منفی در تابع SQRT

عیب یابی: بررسی کنید که آرگومان های وارد شده به تابع درست باشند.

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

خطاهای اکسل - خطای فرمول به دلیل مقدار بسیار بزرگ

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

خطای #VALUE!

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

دلایل خطای #VALUE!:

1. فرمول حاوی فاصله، علامت یا متن است، اما باید شامل یک عدد باشد. مثلا:

خطاهای اکسل - جمع اعداد و مقادیر متن

عیب یابی: بررسی کنید که آیا انواع آرگومان در فرمول به درستی تنظیم شده است.

1. یک محدوده به عنوان آرگومان تابع وارد می شود و تابع انتظار دارد که یک مقدار واحد وارد شود.

خطاها در اکسل - تابع VLOOKUP از یک محدوده به عنوان آرگومان به جای یک مقدار استفاده می کند

عیب یابی: آرگومان های معتبری برای تابع ارائه دهید.

1. وقتی از یک فرمول آرایه استفاده می کنید، Enter را فشار می دهید و اکسل یک خطا نشان می دهد زیرا آن را به عنوان یک فرمول معمولی در نظر می گیرد.

عیب یابی: برای تکمیل وارد کردن فرمول از کلیدهای ترکیبی Ctrl+Shift+Enter استفاده کنید.

خطاهای اکسل - استفاده از فرمول آرایه

خطای #LINK

خطاها در اکسل - خطا در فرمول به دلیل حذف ستون A

عیب یابی: فرمول را تغییر دهید.

خطای #DIV/0!

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

خطاها در اکسل - خطای #DIV/0!

عیب یابی: فرمول را تصحیح کنید.

خطای #N/A

خطای #N/A در اکسلبه این معنی است که فرمول از یک مقدار غیر قابل دسترس استفاده می کند.

دلایل خطای #N/A:

1. هنگام استفاده از توابع VLOOKUP، GLOOKUP، VIEW، MATCH، آرگومان search_value نادرست استفاده می شود:

خطاها در اکسل - مقداری که به دنبال آن هستید در آرایه در حال مشاهده نیست

عیب یابی: آرگومان صحیح را روی مقداری که به دنبال آن هستید تنظیم کنید.

1. خطا در استفاده از توابع VLOOKUP یا GLOOKUP.

عیب یابی: بخش اختصاصی را ببینید

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

خطاهای اکسل - خطاهای فرمول آرایه

عیب یابی: محدوده ارجاعات فرمول را برای مطابقت با سطرها و ستون ها تنظیم کنید یا یک فرمول آرایه را در سلول های از دست رفته وارد کنید.

1. تابع یک یا چند آرگومان مورد نیاز را ندارد.

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

عیب یابی: همه آرگومان های تابع مورد نیاز را وارد کنید.

خطای #EMPTY!

خطای #EMPTY! در اکسلزمانی اتفاق می افتد که یک فرمول از محدوده های غیر همپوشانی استفاده می کند.

خطاها در اکسل - استفاده از محدوده های غیر همپوشانی در فرمول SUM

عیب یابی: املای فرمول را بررسی کنید.

خطای ####

دلایل خطا

1. عرض ستون برای نمایش محتویات سلول کافی نیست.

خطاهای اکسل - افزایش عرض ستون برای نمایش مقدار در سلول

عیب یابی: عرض ستون/ستون ها را افزایش دهید.

1. سلول حاوی فرمولی است که هنگام محاسبه تاریخ یا زمان مقدار منفی را برمی گرداند. تاریخ و زمان در اکسل باید مقادیر مثبت باشد.

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

عیب یابی: املای فرمول را بررسی کنید، تعداد روز یا ساعت یک عدد مثبت بود.

» پیغام خطای خاصی را نمایش می دهد. علاوه بر این، هر نوع خطا با پیام خاص خود نشان داده می شود، به دلایل مختلف ایجاد می شود و بر این اساس نیاز به به طرق مختلفمجوزها

##### - به چه معناست و چگونه آن را برطرف کنیم؟

این نمادها نشان می دهد که ستون حاوی اعداد برای آنها به اندازه کافی پهن نیست یا اینکه تاریخ و ساعت وارد شده به خانه های آن ستون حاوی اعداد منفی است.
در حالت اول، کافی است به سادگی عرض ستون را افزایش دهید یا تغییر دهید قالب شمارهداده ها (به عنوان مثال، تعداد ارقام اعشار را کاهش دهید).
در مورد دوم، شما باید:

• اگر تعداد روزهای بین دو تاریخ محاسبه شده است، فرمول را بررسی کنید.
• اگر فرمول حاوی خطا نیست، باید قالب سلول را تغییر دهید و به عنوان مثال از قالب "تاریخ و زمان" به قالب "عمومی" یا "تعداد" تغییر دهید.

#ارزش! - به چه معناست و چگونه آن را برطرف کنیم؟

این پیام ها در مورد استفاده از متن به جای عدد یا مقدار بولی (TRUE یا FALSE) هستند. یعنی اکسل چنین پلی بویی است و نمی تواند متن داده شده در یک سلول را به نوع داده صحیح تبدیل کند.
باید اطمینان حاصل کنید که فرمول یا تابع به سلول هایی اشاره می کند که حاوی مقادیر معتبر هستند.
به عنوان مثال، اگر سلول A2 دارای یک عدد و سلول A3 حاوی متن باشد، سلول A1 با فرمول =A2+A3 #VALUE را نمایش می دهد! .

#DIV/0! - به چه معناست و چگونه آن را برطرف کنیم؟

این پیام ها نشان می دهد که یک سلول یک عدد را بر 0 (صفر) تقسیم می کند یا از ارجاع به یک سلول خالی استفاده می شود.

• در پنجره کاربرگ باز، سلول دارای این خطا را انتخاب کرده و F2 را فشار دهید.
• هنگامی که خود فرمول یا تابع در یک سلول نمایش داده می‌شود و تمام سلول‌های مرتبط با پیوند به این فرمول یا سلول انتخاب می‌شوند، مقادیر موجود در سلول‌های انتخابی را به دقت بررسی کنید و در صورت لزوم، تنظیمات فرمول یا تغییر را انجام دهید. پیوند به سلول های خالی
• Enter یا دکمه Enter را در نوار فرمول فشار دهید.

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

#نام؟ - به چه معناست و چگونه آن را برطرف کنیم؟

این نمادها نشان می دهد که فرمول از یک نام موجود یا یک عملگر نادرست استفاده می کند.

1 گزینه

اگر از نامی استفاده می شود که تعریف نشده است، باید موارد زیر انجام شود:

• در پنجره کاربرگ باز، به تب Formulas رفته و در گروه نام‌های تعریف شده، روی دکمه Name Manager کلیک کنید.
• در پنجره Name Manager، ببینید که آیا نام داده شدهدر لیست.

اگر این نام وجود ندارد، باید آن را طبق دستورالعمل "" اضافه کنید.

گزینه 2

اگر در املای نام یک اشتباه وجود دارد، باید املای آن را بررسی کنید.

• در پنجره کاربرگ باز شده، F3 را فشار دهید.
• در پنجره "درج نام" نام مورد نظر را از لیست نام ها انتخاب کرده و روی دکمه "OK" کلیک کنید.
• اصلاحات (در صورت لزوم) را در فرمولی که در سلول مناسب ظاهر می شود انجام دهید.
• برای پین کردن، Enter را فشار دهید.

گزینه 3

اگر یک فرمول از یک تابع غلط املایی استفاده کند.
برای مثال، SUM(A1:A10) به جای SUM(A1:A10).

• در پنجره کاربرگ باز، سلولی را با تابع غلط املایی انتخاب کنید.
• منوی دکمه «منبع خطا» را در کنار این سلول باز کنید.
• از لیست دستورات، Edit in Formula Bar را انتخاب کنید.
• در نوار فرمول در کادر نام، املای صحیح فرمول مورد نیاز نمایش داده می شود که با توجه به آن می توانید املای اشتباه را تغییر دهید.
• نتیجه را با کلیک روی کلید Enter ذخیره کنید.

گزینه 4

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

گزینه 5

اگر یک کولون در ارجاع به محدوده ای از سلول ها وجود ندارد، برای تصحیح آن، باید علامت کولون در فرمول را در همه این مراجع بررسی کنید و در صورت لزوم آن را اصلاح کنید.
به عنوان مثال، SUM(A1 A10) به جای SUM(A1:A10).

گزینه 6

#N/A - به چه معناست و چگونه آن را برطرف کنیم؟

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

1 گزینه

اگر داده‌های گمشده و همچنین #N/A یا ND() در فرمول وارد شده‌اند، باید #N/A با داده‌های جدید جایگزین شود.

نام #N/A در سلول‌هایی وارد می‌شود که داده‌های آن هنوز در دسترس نیست.

گزینه 2

اگر توابع LOOKUP، LOOKUP، MATCH یا VLOOKUP مقدار نادرستی را برای آرگومان «lookup_value» مشخص می کنند (به عنوان مثال، ارجاع به محدوده ای از سلول ها، که مجاز نیست)، باید بر این اساس یک مرجع را فقط به مورد دلخواه تعیین کنید. سلول.

گزینه 3

اگر آرگومان های مورد نیاز برای یک تابع کاربرگ استاندارد ارائه نشده باشد، باید همه آرگومان های تابع متناظر مورد نیاز را وارد کنید.

گزینه 4

اگر فرمولی از فرمولی استفاده کند که در آن موجود نیست این لحظهتابع، باید بررسی کنید که کتاب کار با استفاده از تابع کاربرگ باز است و عملکرد به درستی کار می کند.

گزینه 5

اگر از توابع VLOOKUP، GLOOKUP یا MATCH برای مشاهده مقادیر در یک جدول مرتب نشده استفاده می کنید، اطلاعات نمای جدول پیش فرض باید به ترتیب صعودی باشد.
توابع VLOOKUP و GLOOKUP حاوی آرگومان "interval_lookup" هستند که به شما امکان می دهد یک مقدار خاص را در یک جدول مرتب نشده جستجو کنید. با این حال، برای یافتن یک مقدار خاص، آرگومان "interval_lookup" باید FALSE باشد.
تابع MATCH حاوی آرگومان match_type است که به شما امکان می دهد داده ها را برای جستجو مرتب کنید. اگر مقدار مربوطه یافت نشد، توصیه می شود آرگومان "matching_type" را روی 0 تنظیم کنید.

گزینه 6

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

گزینه 7

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

#ارتباط دادن! - به چه معناست و چگونه آن را برطرف کنیم؟

1 گزینه

اگر سلول ارجاع شده توسط فرمول حذف شده باشد یا این سلولمقدار سلول های کپی شده قرار می گیرد، باید فرمول را تغییر دهید تا مراجع جدید را در نظر بگیرید.

گزینه 2

اگر از یک تابع OLE استفاده می کنید که با برنامه ای مرتبط است که در حال اجرا نیست، باید برنامه مورد نیاز را اجرا کنید.

رابط OLE (Object Linking and Embedding) توسط بسیاری پشتیبانی می شود برنامه های مختلفو برای قرار دادن یک سند ایجاد شده در یک برنامه در برنامه دیگر استفاده می شود. به عنوان مثال، می توانید درج کنید سند وردبه یک کتاب کار اکسل و بالعکس.

گزینه 3

گزینه 4

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

#عدد! - به چه معناست و چگونه آن را برطرف کنیم؟

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

1 گزینه

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

گزینه 2

اگر یافتن نتیجه در یک تابع با تکرار (انتخاب پارامترها)، به عنوان مثال "VSD" یا "BET" غیرممکن است، باید یک تقریب اولیه متفاوت را امتحان کنید یا تعداد تکرارها را تغییر دهید.

گزینه 3

اگر نتیجه محاسبه فرمول عددی است که برای نمایش در اکسل خیلی بزرگ یا برعکس خیلی کوچک است، باید فرمول را تغییر دهید و اطمینان حاصل کنید که نتیجه در محدوده 1*10307 تا 1 است. *10307.

#خالی! - به چه معناست و چگونه آن را برطرف کنیم؟

این پیام‌ها نشان می‌دهند که وقتی تغییر مسیر مشخص شده است، هیچ سلول مشترکی وجود ندارد.
مقطع دو منطقه

1 گزینه

اگر از یک عملگر محدوده نادرست استفاده شود، باید اصلاحاتی انجام شود، یعنی:

• برای نشان دادن ارجاع به محدوده‌ای از سلول‌ها، از کولون (:) به عنوان جداکننده بین سلول‌های شروع و پایان محدوده استفاده کنید. برای مثال، SUM(C1:C20).
• برای نشان دادن ارجاع به دو محدوده مجزا، از عملگر اتحاد استفاده می شود - نقطه ویرگول (;). برای مثال، SUM(C1:C20;D1:D20).

گزینه 2

اگر محدوده های مشخص شده دارای سلول های مشترک نیستند، باید پیوندها را تغییر دهید تا به تقاطع مورد نظر برسید.

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

خطاهای فرمول ها به چند دسته تقسیم می شوند:

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

خطاهای منطقی:در این حالت فرمول خطایی را برنمی‌گرداند، بلکه یک نقص منطقی دارد که باعث می‌شود محاسبه نادرست باشد.

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

خطاهای معنایی:به عنوان مثال نام تابع اشتباه نوشته شده است، در این صورت اکسل خطای #NAME را برمی گرداند؟

خطا در فرمول های آرایه:وقتی فرمول آرایه ای را وارد می کنید، باید Ctrl + Sift + Enter را بعد از اتمام تایپ فشار دهید. اگر این کار را انجام ندهید، اکسل متوجه نمی شود که یک فرمول آرایه ای است و یک خطا یا نتیجه نادرست را برمی گرداند.

خطا در محاسبات ناقص:در این مورد، فرمول ها به طور کامل محاسبه نمی شوند. برای اطمینان از اینکه همه فرمول ها دوباره محاسبه شده اند، Ctrl + Alt + Shift + F9 را تایپ کنید.

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

خطای #DIV/0!

اگر فرمولی ایجاد کنید که بر صفر تقسیم شود، اکسل خطای #DIV/0 را برمی گرداند!

از آنجایی که اکسل یک سلول خالی را به عنوان صفر در نظر می گیرد، تقسیم بر یک سلول خالی نیز با خطا مواجه می شود. این مشکل اغلب هنگام ایجاد فرمولی برای داده هایی که هنوز وارد نشده اند رخ می دهد. فرمول در سلول D4 در کل محدوده (=C4/B4) کشیده شده است.

این فرمول نسبت مقادیر ستون‌های C به B را برمی‌گرداند. از آنجایی که تمام داده‌های روز وارد نشده است، فرمول خطای #DIV/0 را برمی‌گرداند!

برای جلوگیری از خطا، می توانید از , برای بررسی خالی بودن یا نبودن سلول های ستون B استفاده کنید:

IF(B4=0;"";C4/B4)

اگر سلول B4 خالی یا حاوی 0 باشد، این فرمول یک مقدار خالی برمی گرداند، در غیر این صورت مقدار شمارش شده را خواهید دید.

روش دیگر استفاده از تابع ISERROR است که خطا را بررسی می کند. اگر عبارت C4/B4 خطایی را برگرداند، فرمول زیر یک رشته خالی برمی گرداند:

IFERROR(C4/B4;"")

خطای #N/A

خطای #N/A زمانی رخ می دهد که سلول ارجاع شده توسط فرمول حاوی #N/A باشد.

به طور معمول، خطای #N/A در نتیجه اجرا برگردانده می شود. در موردی که مطابقت پیدا نشد.

برای دریافت خطا و نمایش یک سلول خالی، از تابع =ESND() استفاده کنید.

ESND(VLOOKUP(A1,B1:D30,3,0)؛"")

لطفا توجه داشته باشید که تابع ESND است خصوصیت جدیددر اکسل 2013. برای سازگاری با نسخه های قبلیاز آنالوگ این تابع استفاده کنید:

IF(END(VLOOKUP(A1,B1:D30,3,0));"";VLOOKUP(A1,B1:D30,3,0))

خطای #NAME؟

ممکن است اکسل خطای #NAME را برگرداند؟ در موارد زیر:

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

خطای #EMPTY!

خطای #EMPTY! زمانی اتفاق می‌افتد که یک فرمول سعی می‌کند از تقاطع دو محدوده استفاده کند که واقعاً قطع نمی‌شوند. عملگر تقاطع در اکسل فضا است. فرمول زیر #EMPTY را برمی‌گرداند زیرا محدوده‌ها همپوشانی ندارند.

خطای #NUMBER!

خطای #NUMBER! در موارد زیر مسترد خواهد شد:

• یک مقدار غیر عددی در آرگومان عددی فرمول وارد شد (به عنوان مثال، 1000 دلار به جای 1000)
• یک آرگومان نامعتبر در فرمول وارد شده است (به عنوان مثال =ROOT(-12))
• تابعی که از تکرار استفاده می کند نمی تواند نتیجه را محاسبه کند. نمونه هایی از توابع با استفاده از تکرار: VSD()، BET()
• فرمول مقدار خیلی بزرگ یا خیلی کوچک را برمی گرداند. اکسل از مقادیر بین -1E-307 و 1E-307 پشتیبانی می کند.

خطای #LINK!

• شما یک ستون یا ردیفی را که توسط یک سلول فرمول ارجاع داده شده بود حذف کردید. به عنوان مثال، اگر اولین سطر یا ستون های A یا B حذف شوند، فرمول زیر با خطا مواجه می شود:

• شما کاربرگ را که توسط یک سلول فرمول ارجاع داده شده بود حذف کردید. به عنوان مثال، فرمول زیر یک خطای if را برمی گرداند ورق 1حذف شد:

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

• شما سلول را برش می دهید و سپس آن را در سلولی که فرمول به آن ارجاع می دهد بچسبانید. در این صورت خطای #LINK! برگردانده می شود.

خطای #VALUE!

خطای #VALUE! رایج ترین خطا است و در شرایط زیر رخ می دهد:

• آرگومان تابع دارای یک نوع داده نادرست است یا فرمول در تلاش است تا عملیاتی را با استفاده از داده های نادرست انجام دهد. به عنوان مثال، هنگام تلاش برای اضافه کردن یک مقدار عددی به یک مقدار متن، فرمول یک خطا را برمی‌گرداند
• آرگومان تابع یک محدوده زمانی است که باید یک مقدار واحد باشد
• توابع ورق سفارشی محاسبه نمی شود. برای اجبار به محاسبه مجدد، Ctrl + Alt + F9 را فشار دهید
• یک تابع کاربرگ سفارشی تلاش می کند تا عملیاتی را انجام دهد که معتبر نیست. به عنوان مثال، یک تابع سفارشی نمی تواند محیط اکسل را تغییر دهد یا در سلول های دیگر تغییراتی ایجاد کند
• هنگام وارد کردن فرمول آرایه فراموش کرده اید که Ctrl + Shift + Enter را فشار دهید