(Microsoft Excel)当您输入值或尝试执行它无法理解的操作时，Microsoft Excel可能会返回错误。有几种类型的错误，每个错误都与您可能犯的特定类型的错误相关联。
#N/A 错误是标准Excel错误。当您错误地引用数据时，它就会出现。例如，不存在或存在于查找表之外的引用数据、查找值中的拼写错误或在查找值中添加了额外字符（逗号、撇号甚至空格字符）。
由于错误是在您错误地引用了查找值时发生的，因此它最常与查找函数相关联，例如LOOKUP、VLOOKUP、HLOOKUP和MATCH函数。让我们看看#N/A 错误的原因、示例和一些修复。
#N/A 错误的原因
以下是可能导致工作表出现 #N/A 错误的原因：
- 您拼错了查找值（或插入了额外的空格字符）
- 您在查找表中拼错了一个值（或插入了一个额外的空格字符）
- 查找范围已错误地输入到公式中
- 您为查找值使用了与查找表中存在的数据类型不同的数据类型（即，使用text 而不是 numbers(text instead of numbers)）
- 在查找表中找不到您输入的查找值
#N/A 错误示例
让我们以 VLOOKUP 函数(use the VLOOKUP function)为例来了解在使用LOOKUP、HLOOKUP或MATCH等(MATCH)Excel函数后，由于它们共享相似的语法结构，最终可能会出现 #N/A 错误。
例如，假设您在Excel(Excel)工作簿 中列出了一长串员工及其奖金。
您使用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 错误，但修复主要可以分为两种方法：
- 更正输入(Correcting the inputs)
- 捕获错误(Trapping the error)
更正输入(Correcting the inputs)
理想情况下，您应该使用本教程前面列出的原因来确定错误的原因。修复原因将确保您不仅摆脱错误，而且获得正确的输出。
您应该首先使用本指南中列出的原因作为清单。这样做将帮助您找到需要修复以消除错误的错误输入。例如，它可能是拼写错误的值、多余的空格字符或查找表中数据类型不正确的值。
捕获错误(Trapping the error)
或者，如果您只想(just )消除工作表中的错误，而无需单独检查错误，您可以使用多个Excel公式。一些函数是专门为捕获错误而创建的，而另一些函数可以帮助您使用多个函数构建逻辑语法以消除错误。
您可以使用以下函数之一捕获 #N/A 错误：
- IFERROR 函数(IFERROR Function)
- 干扰素功能(IFNA Function)
- ISERROR函数和IF函数的组合(A Combination of ISERROR Function and IF Function)
- 修剪功能(TRIM Function)
1. IFERROR 函数(1. IFERROR Function)
创建IFERROR(IFERROR)函数的唯一目的是更改返回错误的单元格的输出。
使用IFERROR函数允许您输入希望单元格显示而不是错误的特定值。例如，如果在使用VLOOKUP时单元格 E2 中出现 #N/A 错误，则可以将整个公式嵌套到IFERROR函数中，如下所示：
IFERROR(VLOOKUP(E4,B2:C7,2,1),“找不到员工”(IFERROR(VLOOKUP(E4,B2:C7,2,1),“Employee not found”)
如果VLOOKUP函数导致错误，它将自动显示文本字符串“未找到员工(Employees)”而不是错误。
如果要在公式返回错误时显示空白单元格，也可以通过简单地插入两个引号 (“”) 来使用空字符串。
请注意，IFERROR函数适用于所有错误。因此，例如，如果您嵌套在IFERROR函数中的公式返回 # DIV错误，则IFERROR仍将捕获错误并返回最后一个参数中的值。
2.干扰素功能(IFNA Function)
IFNA函数是IFERROR函数的更具体版本，但工作方式完全相同(exactly)。这两个函数之间的唯一区别是IFERROR函数捕获所有(all)错误，而IFNA函数仅捕获 #N/A 错误。
例如，如果您有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函数一样工作，因为它依赖于ISERROR函数来检测错误，并且 IF 函数根据逻辑测试呈现输出。
该组合适用于所有(all)错误，例如IFERROR函数，而不仅仅是 #N/A 函数。以下是使用 IF 和ISERROR函数捕获(ISERROR)Excel VLOOKUP #N/A 错误时的语法示例：
=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),”Employee not found”)
4.修剪功能(TRIM Function)
我们之前讨论过，无意中在查找值中插入的空格字符会导致 #N/A 错误。但是，如果您已经将一长串查找值填充到工作表中，则可以使用TRIM函数而不是单独从每个查找值中删除空格字符。
首先，使用TRIM(TRIM)函数创建另一列以修剪名称中的前导和尾随空格：
然后，使用新的名称列作为VLOOKUP(VLOOKUP)函数中的查找值。
修复宏中的 #N/A 错误
没有可用于修复宏中的#N/A 错误的特定公式或快捷方式。由于您可能在创建宏时(macro when you created it)将多个函数添加到宏中，因此您需要检查用于每个函数的参数并验证它们是否正确以修复 maco 中的 #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 pеrform an action that it faіls to understand. There are severаl types of errоrs, аnd eaсh error is associаted with specific typeѕ 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:
- Correcting the inputs
- 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.
