标签：错误

Excel 判断公式的错误类型：ERROR.TYPE函数

如果一个单元格中的公式发生了错误，可通过ERROR.TYPE函数判断其错误类型。

ERROR.TYPE函数的语法为：=ERROR.TYPE(error_val)，其中Error_val参数为需要得到其标号的一个错误值，主要有以下几种情况。

※　参数值为“#NULL!”，函数返回“1”。

※　参数值为“#DIV/0!”，函数返回“2”。

※　参数值为“#VALUE!”，函数返回“3”。

※　参数值为“#REF!”，函数返回“4”。

※　参数值为“#NAME?”，函数返回“5”。

※　参数值为“#NUM!”，函数返回“6”。

※　参数值为“#N/A”，函数返回“7”。

※　参数值为其他值，函数返回错误值“#N/A”。

下面举例说明函数的使用方法：判断A2单元格是否包含#NULL!或#DIV/0!错误值，若包含错误值，则显示相应的提示信息；若不包含错误值，则返回#N/A错误值。

1．单元格包含错误值

01　在“A2:A3”单元格中输入需要计算的数据，本例输入1和0。

02　在“B2”单元格中输入公式：=A2/A3，然后按下“Enter”键确认。

03　在需要显示判断结果的单元格中输入公式：=IF(ERROR. TYPE(B2)<3,CHOOSE(ERROR.TYPE(B2),”区域没有交叉”,”除数为零”))，按下“Enter”键确认即可。

alt

2．单元格不包含错误值

01　在“A2:A3”单元格中输入需要计算的数据，本例输入6和2。

02　在“B2”单元格中输入公式：=A2/A3，然后按下“Enter”键确认。

03　在需要显示判断结果的单元格中输入公式：=IF(ERROR.TYPE(B2)<3,CHOOSE(ERROR.TYPE(B2),”区域没有交叉”,”除数为零”))，按下“Enter”键确认即可。

alt

Excel 解决“#N/A”错误

当数值对函数或公式不可用时，将出现“#N/A”错误。此时可根据具体情况进行解决。

1．缺少数据，在其位置输入了#N/A或NA()

遇到这种情况造成“#N/A”错误时，解决方法是用新的数据代替“#N/A”即可。

2．为工作表函数的lookup_value参数赋予了不正确的值

当为MATCH、HLOOKUP、LOOKUP或VLOOKUP工作表函数的lookup_value参数赋予了不正确的值时，将出现“#N/A”错误。此时的解决方式是确保“lookup_value”参数值的类型正确即可。

3．在未排序的工作表中使用了工作表函数查找值

在未排序的工作表中使用了VLOOKUP、HLOOKUP或MATCH工作表函数来查找值时，也会出现此类错误，解决方法如下。

默认情况下，在工作表中查找信息的函数必须按升序排序。但VLOOKUP函数和HLOOKUP函数包含一个“range_lookup”参数，该参数允许函数在未进行排序的表中查找完全匹配的值。若需要查找完全匹配值，可将“range_lookup”参数设置为“FALSE”。

此外，MATCH函数包含一个“match_type”参数，该参数用于指定列表查找匹配结果时必须遵循的排序次序。若函数找不到匹配结果，可更改“match_type”参数；若要查找完全匹配的结果，需将“match_type”参数设置为“0”。

4．使用函数时省略了必需的参数

当使用内置或自定义工作表函数时，如果省略了一个或多个必需的函数，也会出现此类错误。此时的解决方法是将函数中的所有参数完整输入即可。

5．自定义工作表函数不可用

在工作表中自定义函数后，在下次使用时，若发现该自定义函数不可用，则可能是包含该自定义函数的工作簿未打开的缘故。此时必须确保包含该自定义函数的工作簿已打开，而且函数工作无异常。

6．运行的宏程序输入的函数返回#N/A

当运行的宏程序输入的函数返回#N/A错误时，请确保函数中的参数输入正确且位于正确的位置。

7．数组公式引用区域的行列数不一致

当数组公式中使用的参数的行数或列数与包含数组公式的区域的行数或列数不一致时，也会出现此类错误，解决方法如下。

如果已在多个单元格中输入了数组公式，则必须确保公式引用的区域具有相同的行数和列数，或者将数组公式输入到更少的单元格中。

例如在高为10行的区域(A1:A10)中输入数组公式，但公式引用的区域(C1:C8)高为8行，区域C9:C10中将显示“#N/A”。要更正此错误，可在较小的区域中输入公式如“A1:A8”，或者将公式引用的区域更改为相同的行数，如“C1:C10”。

Excel 解决“#REF!”错误

当单元格引用无效时，会出现#REF!错误，例如，函数引用的单元格（区域）被删除、链接的数据不可用等。可通过下面的方法解决。

※　更改公式，或者在删除或粘贴单元格后立即单击“撤销”以恢复工作表中的单元格。

※　启动使用的对象链接和嵌入（OLE）链接所指向的程序。

※　确保使用的是正确的不可用的动态数据交换（DDE）主题。

※　检查函数以确定参数是否引用了无效的单元格或单元格区域。

Excel 解决“#NAME?”错误

当Excel无法识别公式中的文本时，将出现#NAME?错误，遇到这类错误时，首先针对具体的公式，逐一检查错误的对象，然后加以更正。下面根据具体情况介绍解决方法。

1．使用了不存在的名称

如果是因为使用了不存在的名称导致的错误，查看所使用的名称是否存在的方法为：在“公式”选项卡中的“定义的名称”选项组中单击“名称管理器”，查看名称是否列出，若名称在对话框中未列出，可以单击“新建”按钮添加名称。

2．在公式中输入文本时没有使用双引号

如果需要在公式中输入文本或将输入的内容作为文本使用，则必须使用双引号，此时Excel会将其解释为名称。

3．区域引用中漏掉了冒号“:”

在引用单元格区域时，必须使用冒号“:”，例如“A1:B5”，若未使用冒号，也会出现该错误。用户只需确保公式中的所有区域引用都使用了冒号“:”即可避免此错误。

小提示　如果公式中引用了其他工作表或者其他工作簿中的值或单元格，且这些工作簿或工作表的名字中包含非字母字符或空格，那么必须用单引号“‘”将名称引起。

4．使用了加载宏的函数，而没有加载相应的宏

若是有加载宏的函数，则需要先加载相应的宏，加载宏的具体操作如下。

01　切换到“文件”选项卡，单击“选项”命令。

02　弹出“Excel选项”对话框，切换到“加载项”选项卡，在右侧窗口的“管理”下拉列表中选择“Excel加载项”选项，单击“转到”按钮。

03　弹出“加载宏”对话框，勾选需要加载的宏前面的复选框，单击“确定”按钮即可。

alt

Excel 解决“#NULL!”错误

当函数表达式中使用了不正确的区域运算符、不正确的单元格引用或指定两个并不相交的区域的交点时，则会出现#NULL!错误。如果使用了不正确的区域运算符，则需要将其进行更正，才能正确返回函数值，具体方法如下。

※　若要引用连续的单元格区域，可使用冒号分隔对区域中第一个单元格的引用和对最后一个单元格的引用。如SUM(A1:E1)引用的区域为从单元格A1到单元格E1。

※　若要引用不相交的两个区域，可使用联合运算符，即逗号“,”。如对两个区域求和，可确保用逗号分隔这两个区域，函数表达式为：SUM(A1:C5,D1:F5)。

Excel 解决“#NUM!”错误

当在公式或函数中使用了无效的数值时，则会出现#NUM!错误。下面分别介绍遇到不同情况时的解决方法。

1．在需要数字参数的函数中使用了无法接受的参数

解决方法为：确保函数中使用的参数是数字，而不是文本、货币以及时间等其他格式。例如，即使要输入的值是￥1000，也应在公式中输1000。

2．输入的公式所得出的数字太大或太小，无法在Excel中表示

更改单元格中的公式，使运算的结果介于“-1*10307”到“1*10307”之间即可。

3．使用了进行迭代的工作表函数，且函数无法得到结果

为工作表函数使用不同的起始值，或者更改Excel迭代公式的次数即可。更改Excel迭代公式次数的操作如下。

01　切换到“文件”选项卡，单击“选项”命令。

02　弹出“Excel选项”对话框，切换到“公式”选项卡，在右侧勾选“启用迭代计算”复选框，在下方设置最多迭代次数和最大误差，完成后单击“确定”按钮即可。

alt

Excel 解决“#VALUE!”错误

在单元格中输入函数表达式后确认，如果出现#VALUE！错误，则可能是以下原因造成的。

※　为需要单个值（而不是区域）的运算符或函数提供了区域引用。

※　当函数式需要数字或逻辑值时，输入了文本。

※　输入和编辑的是数组函数式，但却用“Enter”键进行确认等。

遇到这类错误，解决方法是更正相关的数据类型，如果输入的是数组函数式，则在输入过完成后，使用“Ctrl+Shift+Enter”组合键进行确认。

例如：在某个单元格中输入函数式：=A1+B2，而A1或B2中有一个单元格内容是文本，确认后函数将会返回上述错误。

Excel 解决“#DIV/0!”错误

当数字除以零（0）时，会出现#DIV/0!错误。例如用户在某个单元格中输入函数式：=A1/B1，当B1单元格为“0”或为空时，确认后函数式将返回上述错误。

遇到这类错误，解决办法是修改引用的空白单元格，或在作为除数的单元格中输入不为零的值即可。

注意　如果引用了空单元格，即运算对象是空单元格时，Excel会将其作为零（0）值处理。

Excel 计算错误与互补错误函数：ERF函数

ERF函数用于计算误差函数在上下限之间的积分。ERFC函数用于返回从x到∞（无穷）积分的ERF函数的补余误差函数。ERF、ERFC函数的语法如下：

ERF(lower_limit,upper_limit)

其中，lower_limit参数为ERF函数的积分下限，upper_limit参数为ERF函数的积分上限。如果省略，ERF将在零到下限之间进行积分。

ERFC(x)

其中，x参数为ERF函数的积分下限。

计算公式如下：

下面通过实例详细讲解该函数的使用方法与技巧。

打开“ERF函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的数据说明如图20-84所示。要求根据给定的数据条件计算误差函数在上下限之间的积分、从x到∞（无穷）积分的ERF函数的补余误差函数。具体的操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=ERF(0.6598)”，然后按“Enter”键返回，即可计算出误差函数在0与0.6598之间的积分值，如图20-85所示。

图20-84　原始数据

图20-85　计算0与0.6598之间的积分值

STEP02：选中A3单元格，在编辑栏中输入公式“=ERF(1)”，然后按“Enter”键返回，即可计算出误差函数在0与1之间的积分值，如图20-86所示。

STEP03：选中A4单元格，在编辑栏中输入公式“=ERFC(1)”，然后按“Enter”键返回，即可返回1的ERF函数的补余误差函数，如图20-87所示。

图20-86　计算0与1之间的积分值

图20-87　计算补余误差函数

如果下限是非数值型，函数ERF返回错误值“#VALUE!”。如果下限是负值，函数ERF返回错误值“#NUM!”。如果上限是非数值型，函数ERF返回错误值“#VALUE!”。如果上限是负值，函数ERF返回错误值“#NUM!”。如果x是非数值型，则函数ERFC返回错误值“#VALUE!”。如果x是负值，则函数ERFC返回错误值“#NUM!”。

Excel 应用IFERROR函数自定义公式错误

IFERROR函数是一个自定义公式错误时的提示函数。如果公式计算出错则返回指定的值，否则返回公式结果。其语法如下：

IFERROR(value,value_if_error)

参数value为需要检查是否存在错误的参数；参数value_if_error为公式计算错误时要返回的值，计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。

打开“IFERROR函数.xlsx”工作簿，切换至“Sheet1”工作表，该工作表中进行了几个除法运算，如图11-20所示。下面通过使用IFERROR函数来查找和处理公式中的错误，具体操作步骤如下。

STEP01：在C2单元格中输入公式“=IFERROR(A2/B2,”计算中有错误”)”，按“Enter”键返回，即可得到计算结果“8”，如图11-21所示。

图11-20　目标数据

图11-21　返回结果为“8”

STEP02：在C3单元格中输入公式“=IFERROR(A3/B3,”计算中有错误”)”，按“Enter”键返回，即可得到计算结果“计算中有错误”，如图11-22所示。这是因为被除数为“0”。

STEP03：在C4单元格中输入公式“=IFERROR(A4/B4,”计算中有错误”)”，按“Enter”键返回，即可得到计算结果“0”，如图11-23所示。A4单元格中的值为0，所以结果为“0”。

返回结果为“计算中有错误”

图11-22　返回结果为“计算中有错误”

图11-23　返回结果为“0”

IFERROR函数可以用来查找和处理公式中的错误。对IFERROR函数来说，如果参数value或参数value_if_error是空单元格，则IFERROR函数将其视为空字符串值（””）。如果参数value是数组公式，则IFERROR函数为参数value中指定区域的每个单元格返回一个结果数组。