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

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

1. #یک کسب و کار!- این خطا تقریباً همیشه به این معنی است که فرمول موجود در سلول سعی می کند مقداری را بر صفر تقسیم کند. اغلب این اتفاق می افتد به این دلیل که در سلول دیگری که به این اشاره دارد، مقدار صفر وجود دارد یا مقداری وجود ندارد. شما باید تمام سلول های مرتبط را برای چنین مقادیری بررسی کنید. همچنین، این خطا زمانی رخ می دهد که مقادیر نادرستی را در برخی از توابع وارد کنید، به عنوان مثال، در OSTAT ()، زمانی که آرگومان دوم 0 است. همچنین، اگر سلول های خالی را برای ورود داده رها کنید، خطای تقسیم بر صفر می تواند رخ دهد. و برخی از فرمول ها به مقداری داده نیاز دارند. با این کار یک خطا نمایش داده می شود. # DIV / 0!که می تواند کاربر نهایی را گیج کند. برای این موارد، می توانید از تابع IF () برای آزمایش استفاده کنید، به عنوان مثال = IF (A1 = 0؛ 0؛ B1 / A1). در این مثال، اگر سلول A1 حاوی مقدار خالی یا تهی باشد، تابع به جای خطا، 0 را برمی گرداند.
2. # N / A- این خطا مخفف not available است، به این معنی که مقدار برای یک تابع یا فرمول در دسترس نیست. اگر مقدار نامناسبی را در تابع وارد کنید، می توانید این خطا را مشاهده کنید. برای رفع آن، ابتدا سلول های ورودی را از نظر خطا بررسی کنید، به خصوص اگر این خطا در آنها نیز ظاهر شود.
3. #نام؟- این خطا زمانی رخ می دهد که نام خود را به اشتباه در فرمول مشخص کنید یا به اشتباه نام خود فرمول را مشخص کنید. برای تصحیح این، همه نام ها و عناوین موجود در فرمول را دوبار بررسی کنید.
4. # خالی!- این خطا مربوط به محدوده های موجود در فرمول است. اغلب زمانی اتفاق می افتد که دو محدوده غیر همپوشانی در فرمول نشان داده می شود، به عنوان مثال = SUM (C4: C6؛ A1: C1).
5. #عدد!- خطا زمانی رخ می دهد که فرمول حاوی مقادیر عددی نادرست است که خارج از محدوده هستند.
6. #ارتباط دادن!- این خطا زمانی رخ می دهد که سلول های اشاره شده توسط این فرمول حذف شده باشند.
7. #ارزش!- در این مورد ما در مورد استفاده از نوع اشتباه آرگومان برای تابع صحبت می کنیم.

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

رسیدگی به خطاها با تابع ISERROR ().

با استفاده از تابع ISERROR () می‌توانید خطاها را پیدا کنید و آنها را مدیریت کنید. این تابع بسته به اینکه هنگام ارزیابی آرگومان آن خطایی رخ دهد، true یا false را برمی گرداند. فرمول کلی برای رهگیری این است: = IF (ISERROR (عبارت)؛ خطا؛ بیان).

اگر در حین محاسبه خطایی ظاهر شود، تابع if یک خطا (مثلاً یک پیام) برمی گرداند. به عنوان مثال، فرمول زیر را در نظر بگیرید: = IF (ISERROR (A1 / A2)؛ ""؛ A1 / A2). در صورت خطا (تقسیم بر 0)، فرمول یک رشته خالی برمی گرداند. اگر خطایی رخ نداد، خود عبارت A1 / A2 برگردانده می شود.

یک تابع راحت‌تر دیگر، IFERROR () وجود دارد که دو تابع قبلی IF () و ISERROR () را ترکیب می‌کند: IFERROR (مقدار؛ مقدار روی خطا)، که در آن: معنی- بیان برای محاسبه، ارزش روی خطا- نتیجه بازگشت در صورت بروز خطا. برای مثال ما به این صورت خواهد بود: = IFERROR (A1 / A2؛ "").

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

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

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

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

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

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

مواقعی وجود دارد که یک سلول در اکسل به طور کامل با علائم پوند پر می شود. این به معنای یکی از دو گزینه است:

در این صورت افزایش عرض ستون دیگر کمکی نخواهد کرد.

# خطای DIV / 0!

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

خطای # N / A

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

خطای #NAME؟

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

در این مثال، نام محدوده تعریف نشده است.

خطای # خالی!

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

خطای #NUM!

خطا #عدد!زمانی رخ می دهد که مشکل در فرمول مربوط به مقدار باشد.

به یاد داشته باشید که اکسل از مقادیر عددی -1E-307 تا 1E + 307 پشتیبانی می کند.

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

خطای # پیوند!

خطای #VALUE!

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

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

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

این درس مقادیر خطاهای فرمول را که سلول ها می توانند داشته باشند شرح می دهد. با دانستن معنی هر کد (به عنوان مثال: #VALUE !، # DIV / 0 !، #NUMBER !، # N / A !، #NAME !، #NOT !، #REF!)، به راحتی می توانید بفهمید که چگونه یک خطا در فرمول پیدا کنید و او را از بین ببرید.

نحوه حذف # DIV / 0 در اکسل

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

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

نتیجه یک محاسبه اشتباه #NUM است!

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

چند مثال کاربردی:

خطا: #NUM! زمانی اتفاق می افتد که یک مقدار عددی خیلی بزرگ یا خیلی کوچک باشد. این خطا همچنین می تواند هنگام تلاش برای گرفتن ریشه از یک عدد منفی رخ دهد. به عنوان مثال، = ROOT (-25).

سلول A1 دارای اعداد بسیار زیادی است (10 ^ 1000). اکسل نمی تواند چنین اعداد زیادی را مدیریت کند.

سلول A2 با اعداد زیاد نیز همین مشکل را دارد. به نظر می رسد که 1000 عدد کمی است، اما وقتی فاکتوریل آن را برگردانید، مقدار عددی بسیار بزرگی است که اکسل نمی تواند با آن مقابله کند.

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

نحوه حذف ND در اکسل

مقدار موجود نیست: # N / A! - به این معنی است که مقدار برای فرمول موجود نیست:

فرمول نوشته شده در B1: = SEARCH ("Maxim"؛ A1: A4) محتوای متن "Maxim" را در محدوده سلول های A1: A4 جستجو می کند. محتوا در سلول دوم A2 یافت شد. بنابراین، تابع نتیجه 2 را برمی گرداند. فرمول دوم محتوای متن "Andrey" را جستجو می کند، سپس محدوده A1: A4 حاوی چنین مقادیری نیست. بنابراین، تابع خطای # N / A (بدون داده) را برمی گرداند.

خطای #NAME! در اکسل

در دسته خطاهای نوشتن توابع قرار می گیرند. نام نامعتبر: #NAME! - به این معنی است که اکسل متن نوشته شده در فرمول را تشخیص نمی دهد (نام تابع = SUM () برای او ناشناخته است، با خطا نوشته شده است). این نتیجه یک خطای نحوی هنگام نوشتن نام تابع است. مثلا:

خطای # خالی! در اکسل

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

در این حالت محل تقاطع محدوده ها سلول C3 است و تابع مقدار آن را نمایش می دهد.

آرگومان های داده شده در تابع: = SUM (B4: D4 B2: B3) - یک تقاطع تشکیل نمی دهند. بنابراین، تابع یک مقدار با یک خطا می دهد - # NULL!

#ارتباط دادن! - خطای مراجع سلول اکسل

در این مثال، خطا زمانی رخ داد که فرمول به درستی کپی نشده بود. ما 3 محدوده سلول داریم: A1: A3، B1: B4، C1: C2.

زیر اولین محدوده در سلول A4، فرمول جمع را وارد کنید: = SUM (A1: A3). و سپس همان فرمول را در محدوده دوم، در سلول B5 کپی کنید. فرمول، مانند قبل، تنها 3 سلول B2: B4 را جمع می کند و مقدار B1 اول را دور می زند.

هنگامی که همان فرمول در محدوده سوم کپی شد، تابع خطای #REFERENCE! را به سلول C3 برگرداند. از آنجایی که فقط 2 سلول در بالای سلول C3 وجود دارد و نه 3 (طبق فرمول اصلی).

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

نحوه رفع VALUE در اکسل

#ارزش! - خطا در ارزش اگر بخواهیم یک عدد و یک کلمه در اکسل اضافه کنیم، نتیجه یک #VALUE خواهد بود! یک واقعیت جالب این است که اگر بخواهیم دو سلول اضافه کنیم که مقدار اولی یک عدد است و دومی یک متن با استفاده از تابع = SUM () باشد، خطایی رخ نمی‌دهد و متن گرفته می‌شود. مقدار 0 در حین محاسبه مثلا:

شبکه ها در یک سلول اکسل

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

بنابراین هش (######) به جای مقادیر سلول زمانی که تاریخ منفی است قابل مشاهده است. به عنوان مثال، ما سعی می کنیم تاریخ جدید را از تاریخ قدیمی کم کنیم. و در نتیجه محاسبه، قالب سلول ها "تاریخ" است (نه "عمومی").

یک قالب سلول نادرست همچنین می تواند یک سری کاراکتر هش (######) را به جای مقادیر نمایش دهد.

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

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

خطای #####

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

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

خطای # N / A

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

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

# خطای DIV / 0!

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

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

خطای # خالی!

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

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

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

خطای #NUM!

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

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

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

خطای #VALUE!

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

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

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

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

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

• املای نام تابع (عنوان) را بررسی کنید. برای انجام این کار، می توانید Function Wizard (گروه Function Library در تب Formulas) را فراخوانی کرده و تابع مورد نیاز را از لیست پیشنهادی انتخاب کنید.
• شما متنی را در فرمولی وارد کردید که در دو گیومه قرار نگرفته بود. بیایید بگوییم که نتیجه فرمول این بود که یک متن با یک عدد باشد - به عنوان مثال، Total 200 r. اگر وقتی متن 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، BDPROIZVED و موارد مشابه نیز استفاده کرد.

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

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

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

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

خطاهای پیوند نادرست:منطق فرمول درست است، اما فرمول از یک مرجع سلول نامعتبر استفاده می کند. برای یک مثال ساده، محدوده داده برای جمع کردن در فرمول 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 باشد، این فرمول خالی خواهد شد، در غیر این صورت مقدار محاسبه شده را خواهید دید.

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

IFERROR (C4 / B4؛ "")

خطای # N / A

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

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

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

ESND (VLOOKUP (A1; B1: D30; 3; 0); "")

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

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

خطای #NAME؟

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

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

خطای # خالی!

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

خطای #NUM!

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

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

خطای # پیوند!

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

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

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

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

خطای #VALUE!

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

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