مایکروسافت اکسل^{(Microsoft Excel)} ممکن است زمانی که مقداری را وارد می‌کنید یا سعی می‌کنید عملی را انجام دهید که متوجه نمی‌شود، خطایی را برگرداند. انواع مختلفی از خطاها وجود دارد و هر خطا با انواع خاصی از اشتباهات مرتبط است که ممکن است مرتکب شده باشید.

خطای #N/A یک خطای استاندارد اکسل^(Excel) است. زمانی ظاهر می شود که به داده ها به اشتباه ارجاع داده اید. به عنوان مثال، داده‌های ارجاعی که وجود ندارند یا خارج از جدول جستجو وجود دارند، یک خطای املایی در مقدار جستجو ایجاد کردند، یا یک کاراکتر اضافی به مقدار جستجو اضافه کردند (کاما، آپوستروف یا حتی یک کاراکتر فاصله).

از آنجایی که خطا زمانی رخ می دهد که شما به اشتباه یک مقدار جستجو را ارجاع داده اید، معمولاً با توابع جستجو مانند LOOKUP ، VLOOKUP ، HLOOKUP و تابع MATCH مرتبط است. بیایید به دلایل، یک مثال و برخی از رفع خطای #N/A نگاه کنیم.

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

در زیر دلایلی وجود دارد که می تواند باعث ایجاد خطای #N/A در کاربرگ شما شود:

• شما یک مقدار جستجو را اشتباه نوشته اید (یا یک کاراکتر فاصله اضافی درج کرده اید)
• شما یک مقدار را در جدول جستجو اشتباه نوشته اید (یا یک کاراکتر فاصله اضافی درج کرده اید)
• محدوده جستجو به اشتباه در فرمول وارد شده است
• شما از نوع داده متفاوتی برای مقدار جستجو نسبت به آنچه در جدول جستجو وجود دارد استفاده کرده اید (یعنی از متن به جای اعداد استفاده شده است^{(text instead of numbers)} )
• مقدار جستجویی که وارد کردید در جدول جستجو پیدا نشد

مثالی از خطای #N/A

بیایید از تابع VLOOKUP^{(use the VLOOKUP function)} به عنوان مثال استفاده کنیم تا بفهمیم چگونه ممکن است پس از استفاده از توابع اکسل^(Excel) مانند LOOKUP ، HLOOKUP یا MATCH با یک خطای #N/A مواجه شوید زیرا ساختار نحوی مشابهی دارند.

به عنوان مثال، فرض کنید که فهرست بلندبالایی از کارمندان و پاداش های آنها در کتاب کار  اکسل فهرست شده است.^(Excel)

شما از فرمول VLOOKUP استفاده می‌کنید، ^(VLOOKUP)[lookup_value] مربوطه را وارد می‌کنید که برای آن یک مرجع سلولی (سلول D4) وارد می‌کنید، [table_array] (A2:B7) را تعریف می‌کنید و [col_index_num] (2) را تعریف می‌کنید. 

برای آرگومان نهایی به نام [range_lookup] ، باید از 1 (یا TRUE ) برای دستور دادن به Excel برای به دست آوردن تطابق دقیق استفاده کنید. تنظیم آن بر روی 2 (یا FALSE ) به اکسل^(Excel) دستور می دهد تا به دنبال یک مطابقت تقریبی باشد که می تواند خروجی نادرستی به شما بدهد.

فرض کنید^(Suppose) فرمولی برای دریافت پاداش برای چند کارمند منتخب تنظیم کرده اید، اما مقدار جستجو را اشتباه املایی کرده اید. در نهایت با یک خطای #N/A مواجه خواهید شد زیرا اکسل^(Excel) نمی تواند مطابقت دقیقی برای مقدار موجود در جدول جستجو پیدا کند.

بنابراین، برای رفع این خطا چه کاری باید انجام دهید؟ 

نحوه رفع خطای #N/A

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

1. تصحیح ورودی ها^{(Correcting the inputs)}
2. به دام انداختن خطا^{(Trapping the error)}

تصحیح ورودی ها^{(Correcting the inputs)}

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

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

به دام انداختن خطا^{(Trapping the error)}

از طرف دیگر، اگر می‌خواهید فقط^{(just )} خطاها را از کاربرگ خود حذف کنید، بدون اینکه زحمت بررسی اشتباهات را به خود اختصاص دهید، می‌توانید از چندین فرمول اکسل استفاده کنید. ^(Excel)برخی از توابع به طور خاص برای به دام انداختن خطاها ایجاد شده اند، در حالی که برخی دیگر می توانند به شما در ایجاد یک نحو منطقی با استفاده از چندین توابع برای حذف خطاها کمک کنند.

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

• تابع IFERROR^{(IFERROR Function)}
• عملکرد IFNA^{(IFNA Function)}
• ترکیبی از تابع ISERROR و تابع IF^{(A Combination of ISERROR Function and IF Function)}
• عملکرد TRIM^{(TRIM Function)}

1. تابع IFERROR^{(1. IFERROR Function)}

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

استفاده از تابع IFERROR^(IFERROR) به شما امکان می دهد مقدار خاصی را وارد کنید که می خواهید سلول به جای خطا نشان دهد. به عنوان مثال، اگر هنگام استفاده از VLOOKUP در سلول E2 خطای #N/A داشته باشید، می‌توانید کل فرمول را در یک تابع IFERROR^(IFERROR) قرار دهید، مانند:

IFERROR(VLOOKUP(E4,B2:C7,2,1)،"کارمند پیدا نشد"^{(IFERROR(VLOOKUP(E4,B2:C7,2,1),“Employee not found”)}

اگر تابع VLOOKUP^(VLOOKUP) منجر به خطا شود، به‌طور خودکار به جای خطا، رشته متنی « کارمندان^(Employees) یافت نشد» را نمایش می‌دهد.

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

توجه داشته باشید که تابع IFERROR^(IFERROR) برای همه خطاها کار می کند. بنابراین، برای مثال، اگر فرمولی که در داخل تابع IFERROR^(IFERROR) قرار داده‌اید ، یک خطای # DIV را برمی‌گرداند ، IFERROR همچنان خطا را به دام می‌اندازد و مقدار را در آخرین آرگومان برمی‌گرداند.

2. عملکرد IFNA^{(IFNA Function)}

تابع IFNA نسخه خاص تری از تابع IFERROR^(IFERROR) است اما دقیقاً^(exactly) به همان روش کار می کند. تنها تفاوت بین این دو تابع این است که تابع IFERROR ^(IFERROR)همه^(all) خطاها را به دام می اندازد ، در حالی که تابع IFNA فقط خطاهای #N/A را به دام می اندازد.

به عنوان مثال، اگر خطای VLOOKUP^(VLOOKUP) #N/A داشته باشید، اما برای خطای # VALUE ، فرمول زیر کار خواهد کرد :

IFNA(VLOOKUP(E4,B2:C7,2,1)،"کارمند پیدا نشد"^{(IFNA(VLOOKUP(E4,B2:C7,2,1),“Employee not found”)}

3. ترکیبی از تابع ISERROR و تابع IF^{(A Combination of the ISERROR Function and the IF Function)}

راه دیگر برای به دام انداختن خطا استفاده از تابع ISERROR همراه با تابع IF است. اساساً مانند تابع IFERROR^(IFERROR) عمل می کند، به این ترتیب که برای تشخیص خطا به تابع ISERROR و برای ارائه خروجی بر اساس یک تست منطقی به تابع IF متکی است.^(ISERROR)

این ترکیب با تمام^(all) خطاها مانند تابع IFERROR^(IFERROR) کار می کند، نه فقط تابع #N/A. در اینجا نمونه‌ای از نحوه شکل‌بندی نحو هنگام به دام انداختن یک خطای Excel VLOOKUP #N/A با توابع IF و ISERROR آمده^(ISERROR) است:

=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),”Employee not found”)

4. عملکرد TRIM^{(TRIM Function)}

قبلاً بحث کردیم که یک کاراکتر فاصله که به طور ناخواسته در مقدار جستجو درج می شود می تواند منجر به خطای #N/A شود. با این حال، اگر یک لیست طولانی از مقادیر جستجو در کاربرگ خود دارید، می توانید به جای حذف کاراکتر فاصله از هر مقدار جستجو به صورت جداگانه، از تابع TRIM استفاده کنید.^(TRIM)

ابتدا، ستون دیگری ایجاد کنید تا با استفاده از تابع TRIM^(TRIM) ، فضاهای اصلی و انتهایی نام‌ها را برش دهید :

سپس، از ستون جدید نام ها به عنوان مقادیر جستجو در تابع VLOOKUP^(VLOOKUP) استفاده کنید.

رفع خطای #N/A در ماکروها

هیچ فرمول یا میانبری خاصی وجود ندارد که بتوانید از آن برای رفع خطاهای #N/A در یک ماکرو استفاده کنید. از آنجایی که احتمالاً هنگام ایجاد ماکرو چندین تابع را به ماکرو اضافه کرده‌اید^{(macro when you created it)} ، باید آرگومان‌های استفاده شده برای هر تابع را بررسی کنید و بررسی کنید که آیا برای رفع خطای #N/A در ماکو درست هستند یا خیر.

#N/A خطاها رفع شد

رفع خطاهای #N/A زمانی که بفهمید علت آنها چیست کار چندان دشواری نیست. اگر خیلی نگران خروجی نیستید و نمی‌خواهید یک فرمول منجر به خطا شود، می‌توانید از توابعی مانند IFERROR و IFNA برای مقابله با خطای #N/A استفاده کنید.

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

Microѕoft Excel may return an error when you input a value or try to perform an action that it failѕ to understand. There аre several types of еrrors, and each error is associated with specific types of mistakes you might have made.

The #N/A error is a standard Excel error. It appears when you’ve referenced data incorrectly. For example, referenced data that doesn’t exist or exists outside of the lookup table, made a spelling error in the lookup value, or added an extra character in the lookup value (a comma, apostrophe, or even a space character).

Since the error occurs when you’ve incorrectly referenced a lookup value, it’s most commonly associated with lookup functions like LOOKUP, VLOOKUP, HLOOKUP, and the MATCH function. Let’s look at the reasons, an example, and some fixes for the #N/A error.

Reasons for #N/A Error

Following are the reasons that can cause a #N/A error on your worksheet:

• You’ve misspelled a lookup value (or inserted an extra space character)
• You’ve misspelled a value in the lookup table (or inserted an extra space character)
• The lookup range has been entered incorrectly into the formula
• You’ve used a different data type for the lookup value than the one that exists in the lookup table (i.e., used text instead of numbers)
• The lookup value you entered was not found in the lookup table

Example of #N/A Error

Let’s use the VLOOKUP function as an example to understand how you might end up with a #N/A error after using Excel functions like LOOKUP, HLOOKUP, or MATCH since they share a similar syntax structure.

For instance, say you have a long list of employees and their bonuses listed in an Excel workbook. 

You use the VLOOKUP formula, enter a relevant [lookup_value] for which you insert a cell reference (cell D4), define the [table_array] (A2:B7), and define [col_index_num] (2). 

For the final argument called the [range_lookup], you should use 1 (or TRUE) to instruct Excel to obtain an exact match. Setting it to 2 (or FALSE) will instruct Excel to look for an approximate match, which can give you an incorrect output.

Suppose you’ve set up a formula for obtaining bonuses for a select few employees, but you misspell the lookup value. You’ll end up with a #N/A error because Excel won’t be able to find an exact match for the value in the lookup table.

So, what must you do to fix this error? 

How to Fix #N/A Error

There are several ways for troubleshooting the #N/A error, but the fixes can primarily be categorized into two approaches:

1. Correcting the inputs
2. Trapping the error

Correcting the inputs

Ideally, you should identify the cause of the error using the reasons listed previously in this tutorial. Fixing the cause will ensure that you’re not just getting rid of the error but also getting the correct output.

You should start by using the reasons listed in this guide as a checklist. Doing this will help you find the incorrect input that you need to fix to eliminate the error. For example, it could be a misspelled value, an extra space character, or values with an incorrect data type in the lookup table.

Trapping the error

Alternatively, if you want to just eliminate errors from your worksheet without bothering with individually checking for mistakes, you can use several Excel formulas. Some functions have been created specifically to trap errors, while others can help you construct a logical syntax using multiple functions to eliminate errors.

You can trap the #N/A error using one of the following functions:

• IFERROR Function
• IFNA Function
• A Combination of ISERROR Function and IF Function
• TRIM Function

1. IFERROR Function

The IFERROR function was created with the sole purpose of changing the output for a cell that returns an error.

Using the IFERROR function allows you to enter a specific value that you want a cell to show instead of an error. For instance, if you have a #N/A error in cell E2 when using VLOOKUP, you can nest the entire formula into an IFERROR function, like so:

IFERROR(VLOOKUP(E4,B2:C7,2,1),“Employee not found”

If the VLOOKUP function results in an error, it will automatically display the text string “Employees not found” instead of the error.

You can also use an empty string by simply inserting two quotation marks (“”) if you want to display a blank cell when the formula returns an error.

Note that the IFERROR function works for all errors. So, for instance, if the formula you’ve nested inside the IFERROR function returns a #DIV error, IFERROR will still trap the error and return the value in the last argument.

2. IFNA Function

The IFNA function is a more specific version of the IFERROR function but works exactly the same way. The only difference between the two functions is that the IFERROR function traps all errors, while the IFNA function only traps #N/A errors.

For instance, the following formula will work if you have a VLOOKUP #N/A error, but not for a #VALUE error:

IFNA(VLOOKUP(E4,B2:C7,2,1),“Employee not found”

3. A Combination of the ISERROR Function and the IF Function

Another way to trap an error is to use the ISERROR function along with the IF function. It essentially works like the IFERROR function, in that it relies on the ISERROR function to detect an error, and the IF function to render output based on a logical test.

The combination works with all errors like the IFERROR function, not just the #N/A function. Here’s an example of what the syntax will look like when trapping an Excel VLOOKUP #N/A error with the IF and ISERROR functions:

=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),”Employee not found”)

4. TRIM Function

We discussed earlier that a space character inserted inadvertently in the lookup value can result in a #N/A error. However, if you have a long list of lookup values already populated into your worksheet, you can use the TRIM function instead of removing the space character from each lookup value individually.

First, create another column to trim leading and trailing spaces in the names using the TRIM function:

Then, use the new column of names as the lookup values in the VLOOKUP function.

Fix #N/A Error in Macros

There’s no specific formula or shortcut you can use to fix #N/A errors in a macro. Since you likely added several functions into your macro when you created it, you’ll need to check the arguments used for each function and verify if they’re correct to fix an #N/A error in a maco.

#N/A Errors Fixed

Fixing #N/A errors isn’t that difficult once you understand what causes them. If you’re not too concerned about the output and just don’t want a formula to result in an error, you can use functions like IFERROR and IFNA to easily tackle the #N/A error.

Related posts

• زمان استفاده از Index-Match به جای VLOOKUP در اکسل

• نحوه رفع خطای Bookmark Not Defined در Word

• نحوه حذف خطوط خالی در اکسل

• چگونه Outlook Stack On Loading Profile را رفع کنیم

• پیغام خطای «نمی توان مجوز تأیید» را در MS Office برطرف کنید

• با استفاده از ابزار جستجوی هدف تجزیه و تحلیل What-If اکسل

• مرتب سازی داده های تک ستونی و چند ستونی در صفحات گسترده اکسل

• 4 روش برای تبدیل Excel به Google Sheets

• نحوه رفع مشکل جستجوی Outlook

• نحوه ایجاد یک ماکرو یا اسکریپت VBA در اکسل

• از نام های محدوده پویا در اکسل برای کشویی های انعطاف پذیر استفاده کنید

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• رفع خطای عدم تطابق تگ شروع پایان تگ هنگام باز کردن فایل‌های DOCX

• چگونه یک فایل اکسل را برای همکاری آسان به اشتراک بگذاریم

• نحوه رفع خطای مایکروسافت آفیس 25090

• از صفحه کلید برای تغییر ارتفاع سطر و عرض ستون در اکسل استفاده کنید

• نحوه ردیابی وابستگان در اکسل

• نحوه درج سریع چند ردیف در اکسل

• نحوه استفاده از دستورات If و Nested If در اکسل

• 3 روش برای تقسیم سلول در اکسل