تاریخ: 24 دسامبر 2015 دسته بندی:

خطاها در اکسل همراهی ضروری برای همه کسانی است که... هنگامی که یک عبارت در یک سلول قابل ارزیابی نباشد، برنامه پیام خطا را در سلول نمایش می دهد. با یک "#" به دنبال نام خطا شروع می شود. اگر با توابع اکسل آشنا هستید و می دانید چگونه باید از این موضوع بترسید، نیازی به ترس نیست ساده ترین منطقعملیات ریاضی - شما به راحتی می توانید خطا را پیدا و اصلاح کنید.

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

انواع خطاها

اگر خطایی رخ دهد، رمزگشایی به اصلاح آن کمک می کند:

خطا	شرح
#DIV/0!	هنگام تلاش برای تقسیم بر صفر خطایی رخ می دهد
#نام؟	برنامه نمی تواند نام وارد شده را تشخیص دهد. به عنوان مثال، نام تابع را اشتباه نوشتید یا رشته متن را در گیومه قرار ندادید
#عدم	داده در دسترس نیست. به عنوان مثال، من هیچ ارزشی پیدا نکردم
#خالی!	شما تقاطع محدوده هایی را درخواست کردید که تلاقی نمی کنند
#عدد!	مشکل یکی از مقادیر عددی، که در فرمول استفاده می شوند. به عنوان مثال، شما سعی می کنید جذر یک عدد منفی را بگیرید. در ریاضیات کلاسیک این عمل معنی ندارد
#ارتباط دادن!	فرمول حاوی پیوندی است که وجود ندارد. به عنوان مثال، شما سلولی که به آن اشاره دارد را حذف کرده اید
#ارزش!	فرمول حاوی اجزای نامعتبر است. اغلب این خطا زمانی رخ می دهد که نحو فرمول ها نقض می شود.

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

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

ردیابی خطا از طریق مراحل محاسبه

لینک های دایره ای در اکسل

نوع دیگر خطا، مرجع دایره ای است. زمانی اتفاق می‌افتد که به سلولی ارجاع می‌دهید که مقدار آن به سلولی که فرمول را در آن می‌نویسید بستگی دارد. مثلا در قفس A1فرمول =A2+1 نوشته شده است و در A2 Writ =A1، یک مرجع چرخه ای ظاهر می شود که بی انتها دوباره محاسبه می شود. در این مورد، برنامه در مورد ظاهر یک مرجع چرخه ای هشدار می دهد و محاسبه "فرمول های چرخه ای" را متوقف می کند. یک فلش دو سر در سمت چپ سلول ها ظاهر می شود. شما باید خطای رخ داده را تصحیح کرده و محاسبه را تکرار کنید.

گاهی اوقات "حلقه" پیچیده زمانی رخ می دهد که یک پیوند چرخه ای با چندین فرمول میانی تشکیل شود.

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

ردیابی پیوندهای دایره ای

در اکسل می توانید نتیجه حلقه زدن فرمول ها را محاسبه کنید. برای انجام این کار، کادر را علامت بزنید فایل – گزینه‌ها – فرمول‌ها – محاسبات تکراری را فعال کنید. در همان بلوک می توانید حداکثر تعداد تکرار (محاسبات) را برای یافتن تعادل و خطای مجاز تنظیم کنید. در بیشتر موارد این کار ضروری نیست، بنابراین توصیه می کنم این کادر را علامت نزنید. با این حال، وقتی می دانید که فرمول های حلقه درست هستند و محاسبه آنها به یک نتیجه پایدار منجر می شود - چرا این کار را انجام ندهید؟

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

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

من یک فرمول وارد می کنم، اما به جای نتیجه، متن #N/A یا ##### را می بینم. چه مفهومی داره؟

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

خطای #####

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

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

خطای #N/A

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

• تابع یک یا چند آرگومان مورد نیاز را ندارد.
• توابع VLOOKUP، GLOOKUP، LOOKUP یا MATCH مقدار نادرستی را برای آرگومان lookup_value مشخص کردند.
• توابع VLOOKUP، HLOOKUP یا MATCH برای جستجوی جدول مرتب نشده استفاده می شود.
• در جدول اصلی، #N/A یا ND() به جای داده های از دست رفته وارد شده است.

خطای #DIV/0!

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

• این فرمول به صراحت تقسیم بر صفر را مشخص می کند (مثلاً 10/0 =).
• در یک فرمول یا تابع، مقسوم‌کننده اشاره‌ای به یک سلول خالی یا سلولی است که حاوی صفر است.

خطای #EMPTY!

این خطا زمانی رخ می دهد که دو محدوده متقاطع در شرایط یک تابع مشخص شده باشند، اما در واقع آنها را قطع نمی کنند.

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

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

خطای #NUMBER!

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

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

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

خطای #VALUE!

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

• متن به جای عدد یا مقدار منطقی (TRUE یا FALSE) در فرمول وارد می شود.
• پس از وارد کردن یا ویرایش فرمول آرایه، به جای CTRL+SHIFT+ENTER، ENTER را فشار دهید.
• مرجع، فرمول یا تابع به عنوان یک آرایه مشخص می شود.
• یک محدوده به عنوان یک شرایط تابعی مشخص می شود که به یک مقدار واحد نیاز دارد.

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

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

اگر فرمول شامل چندین تابع باشد، آیتم Show Calculation Steps در منوی زمینه ظاهر می شود. در پنجره ای که باز می شود، می توانید ببینید که کدام محاسبه باعث خطا می شود.

من یک خطای #NAME دارم؟ و من نمی توانم دلیل آن را پیدا کنم.

• املای نام(های) تابع را بررسی کنید. برای انجام این کار، می توانید Function Wizard (گروه Function Library از تب Formulas) را فراخوانی کنید و انتخاب کنید. تابع مورد نظراز لیست پیشنهادی
• متنی که در فرمول وجود نداشت در فرمول وارد شد. نقل قول های دوگانه. فرض کنید که نتیجه فرمول قرار بود یک متن با یک عدد باشد - به عنوان مثال، مجموع 200 روبل. هنگامی که متن Total را در یک فرمول وارد می کنید، اگر متن در دو علامت نقل قول قرار نگیرد، اکسل داده ها را به عنوان یک نام تفسیر می کند که باعث ایجاد خطا می شود.

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

• فرمول یا تابع از نامی استفاده می کند که تعریف نشده است. آن را بررسی کنید نام داده شدهوجود دارد. در تب Formulas، در گروه Named Cells، Name Manager را انتخاب کنید و ببینید آیا نام در لیست است یا خیر. اگر این نام وجود ندارد، می توانید با انتخاب ایجاد نام، آن را اضافه کنید.
• در املای نام اشتباه بود. برای تأیید اینکه نام‌های صحیح را در نوار فرمول وارد کرده‌اید، F3 را فشار دهید، روی نامی که می‌خواهید کلیک کنید و سپس روی OK کلیک کنید.
• فرمول یا تابع از ارجاع به کاربرگ دیگری استفاده می کند که در یک علامت نقل قول قرار نمی گیرد. اگر نام برگه یا کتاب کار دیگری حاوی کاراکترهای غیرحرفی یا فاصله باشد، نام باید در حروف (") محصور شود.

من یک پایگاه داده عظیم از مشتریان - خریداران دارم. هر روز یک ستون به پایگاه داده اضافه می شود که میزان درآمد هر مشتری را نشان می دهد. اگر خریدار چیزی خریداری نکرده باشد، #N/A در سلول قرار می گیرد. چگونه می توانم یک ستون را جمع کنم اگر چنین داده هایی در آنجا وجود دارد؟

ساده ترین راه برای حل این مشکل تغییر اصل پر کردن پایگاه داده و پر نکردن سلول برای مشتریانی است که خریدی انجام نداده اند.

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

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

با استفاده از تابع BDSUMM، می‌توانیم سلول‌هایی را که حاوی مقدار #N/A نیستند را برای جمع‌بندی انتخاب کنیم. تابع BDSUMM دارای نحو زیر است:

BDSUMM (پایگاه داده، فیلد، شرایط)، که در آن

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

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

BDSUMM(C2:C18;1;E2:E3) که در آن

• C2: C18 جدول ما است،
• E2: E3 شرایط ما است.

لطفاً توجه داشته باشید که محدوده پایگاه داده و شرایط باید شامل سرصفحه ستون باشد! در این صورت باید عنوان شرط با عنوان ستونی که محاسبات روی آن انجام می شود مطابقت داشته باشد!

نتیجه ما در زیر جدول نشان داده شده است:

اگر پایگاه داده ما دارای خطا باشد انواع متفاوت، کافی است آنها را به شرایط اضافه کنید (و بر این اساس دامنه شرایط را در فرمول گسترش دهید) تا به نتیجه مطلوب برسید.

از این روش می توان برای توابع COUNT، BCOUNT، DMAX، DMIN، BDPRODUCT و موارد مشابه نیز استفاده کرد.

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

در زیر توضیحی از فرمول های نشان داده شده در تصویر با ارائه خواهیم داد اطلاعات دقیقبرای هر اشتباهی

1. #مورد!- "تقسیم بر 0" اغلب هنگام تلاش برای تقسیم بر صفر اتفاق می افتد. یعنی فرمول تعبیه شده در سلول، که عملکرد تقسیم را انجام می دهد، با سلولی با مقدار صفر یا "خالی" روبرو می شود. برای حل این مشکل، تمام سلول های درگیر در محاسبه را بررسی کنید و مقادیر نامعتبر را تصحیح کنید. دومین اقدام منجر به #DIV/O! - این ورود مقادیر نادرست به برخی از توابع است، مانند =AVERAGE()، در صورتی که در طول محاسبه، محدوده مقدار حاوی 0 باشد. همان نتیجه توسط سلول های خالی قابل دسترسی توسط فرمولی ایجاد می شود که به داده های خاصی برای محاسبه نیاز دارد. .
2. #عدم- "اطلاعاتی وجود ندارد". اینگونه است که اکسل مقادیری را که با فرمول (تابع) واضح نیستند علامت گذاری می کند. با وارد کردن اعداد نامناسب در یک تابع، مطمئناً این خطا را راه اندازی می کنید. هنگامی که ظاهر می شود، مطمئن شوید که تمام سلول های ورودی به درستی پر شده اند، به خصوص در مواردی که همان نوشته روشن است. اغلب هنگام استفاده یافت می شود
3. #نام? - "نام نامعتبر"، نشانگر نام نادرست فرمول یا بخشی از آن. اگر تمام عناوین و نام‌های همراه با الگوریتم محاسبه را بررسی و تصحیح کنید، مشکل از بین می‌رود.
4. #خالی!- "یک مقدار خالی در محدوده وجود دارد"، سیگنالی مبنی بر اینکه در جایی در محاسبه مناطق غیر همپوشانی وجود دارد یا یک فاصله بین محدوده های مشخص شده درج شده است. یک اشتباه کاملا نادر یک ورودی اشتباه ممکن است به شکل زیر باشد:

SUM(G10:G12 I8:J8)

اکسل چنین دستوراتی را نمی شناسد.
5. #عدد!- یک خطا توسط فرمولی حاوی عددی که با محدوده مشخص شده مطابقت ندارد ایجاد می شود.
6. #ارتباط دادن!- هشدار می دهد که سلول های مرتبط با این فرمول ناپدید شده اند. بررسی کنید که آیا سلول های مشخص شده در فرمول حذف شده اند یا خیر.
7. #ارزش!- نوع آرگومان برای عملکرد تابع اشتباه انتخاب شده است.

8. پاداش، خطا ##### - عرض سلول برای نمایش کل عدد کافی نیست

علاوه بر این، اکسل هشدار می دهد که فرمول نادرست است. برنامه سعی خواهد کرد به شما بگوید دقیقاً چگونه علائم نگارشی را قرار دهید (مثلاً پرانتز). اگر گزینه پیشنهادی با شرایط شما مطابقت دارد، روی "بله" کلیک کنید. اگر راهنمایی نیاز به تنظیم دستی دارد. سپس «نه» را انتخاب کنید و خودتان پرانتزها را مرتب کنید.

خطاها در اکسل با استفاده از تابع ERROR() برای Excel 2003

این تابع به خوبی به حذف خطاها در اکسل کمک می کند. با یافتن خطا در سلول‌ها کار می‌کند؛ اگر خطایی در فرمول پیدا کرد، TRUE را برمی‌گرداند و بالعکس. در ترکیب با =IF() به شما این امکان را می دهد که در صورت یافتن خطا، مقدار را جایگزین کنید.

فرمول کاری: =IF(ERROR(expression)،error,expression).

IF(ISERROR (A1/A2);””;A1/A2)

توضیح: اگر هنگام اجرای A1/A2 خطایی پیدا شود، خالی ("") برگردانده می شود. اگر همه چیز درست پیش رفت (یعنی ERROR (A1/A2) = FALSE)، A1/A2 محاسبه می شود.

خطاها در اکسلاستفاده از IFERROR() برای Excel 2007 و بالاتر

یکی از دلایلی که به سرعت به Excel 2007 تغییر دادم IFERROR() بود (دلیل اصلی این است)

تابع iferror دارای قابلیت های هر دو تابع ERROR() و IF() است، اما در موارد جدیدتر موجود است. نسخه های اکسل، که بسیار راحت است

ابزار به صورت زیر فعال می شود: =IFERROR(value; value on error). به جای "مقدار" یک عبارت / پیوند محاسبه شده به سلول وجود دارد، و به جای "مقدار در صورت خطا" - اگر اشتباهی رخ داد، چه چیزی باید برگردانده شود، به عنوان مثال، اگر هنگام محاسبه A1/A2 #مورد!سپس فرمول به صورت زیر خواهد بود:

IFERROR(A1/A2;””)

» از اینجا دانلود کنید

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

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

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

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

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

این پیام ها در مورد استفاده از متن به جای عدد یا مقدار بولی (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

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

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

اگر یک سلول به جای نتیجه حاوی فرمول باشد

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

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

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

اگر داده‌های منبع را تغییر دهید، اما فرمول‌های موجود در برگه نمی‌خواهند دوباره محاسبه شوند، محاسبه مجدد خودکار فرمول‌ها را غیرفعال کرده‌اید.

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

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

اگر سلول با علامت هش پر شده باشد

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

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

نتیجه محاسبه در قالب اشتباه است

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

وقتی پیوندهای خارجی در دسترس نیستند

اگر فرمول اکسلنتیجه نادرست را برمی گرداند

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

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

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

برای آسان‌تر کردن فرآیند، می‌توانید فلش‌های پیوند را روشن کنید. شما به راحتی می توانید با کلیک کردن تعیین کنید که فرمول به کدام سلول ها اشاره دارد فرمول ها - وابستگی های فرمول - سلول های تاثیرگذار. روی یک برگه فلش های آبینشان می دهد که به کدام داده ها اشاره می کنید.

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

لطفاً توجه داشته باشید که در جداول پیچیده، ترسیم فلش ها زمان و منابع ماشین زیادی را می گیرد. برای حذف فلش ها، کلیک کنید فرمول ها – وابستگی های فرمول – حذف فلش ها.

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

اگر یک مرجع دایره ای در اکسل رخ دهد

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

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

برای پیدا کردن چنین فرمول های "نادرستی"، به روبان نگاه کنید: فرمول ها – وابستگی های فرمول – بررسی خطا – خطاهای چرخه ای. این منو لیستی از سلول ها را با فرمول های حلقه باز می کند. روی هر یک کلیک کنید تا مکان نما را در آن قرار دهید و فرمول را بررسی کنید.

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

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

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

خطاهای داخلی اکسل

گاهی اوقات در حین محاسبات خطاهایی با علامت "#" شروع می شود. به عنوان مثال، "#N/A"، "#NUMBER!"، و غیره. این خطاها من هستم، این پست را بخوانید و سعی کنید دلیل خطای خود را بفهمید. وقتی این اتفاق می افتد، می توانید به راحتی همه چیز را اصلاح کنید.

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

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

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

و منتظر سوالات و نظرات شما در مورد این پست هستم!