2883阅读
1回复

[EXCEL使用技巧]【Excel技巧】如何将公式返回的错误值转为其他值 [复制链接]

上一主题下一主题查看指定楼层

离线阿文哥

管理员

发帖: 16132

学分: 16242

经验: 2562

精华: 49

金币: 0

只看楼主倒序阅读使用道具 0楼发表于: 2014-08-25

疑难72如何将公式返回的错误值转为其他值

如图 72‑1所示，在D5单元格使用公式根据D3单元格输入的ID号查询客户名称，当ID号不在A列的数据中时，会出现#N/A错误，能否返回“未找到”？

图72‑1 公式返回错误值

→ 解决方案1：
使用IS类函数设置判断条件。
→ 操作方法
在D6:D8单元格分别输入以下公式：
=IF(ISNA(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE))
=IF(ISERROR(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE))
→ 原理分析
针对VLOOKUP函数发生错误返回错误值#N/A的特性，使用ISNA函数进行判断。如果公式可能返回其他错误值，则使用对错误值均通用的ISERROR函数进行判断，一般表示为：
=IF(ISERROR(原公式),出错需返回的值,原公式)
IS类函数判断屏蔽错误值
以ISERROR函数为例，其语法为：
ISERROR(value)
判断参数是否为错误值，并返回TRUE或FALSE，与ISBLANK、ISERR、ISLOGICAL、ISNA、ISNONTEXT、ISNUMBER、ISREF、ISTEXT等函数并称为IS类函数，具体参数值判断情况见表格 72‑1。
表格72‑1 IS类函数参数特征

\|?<^4U8 函数	aU?HIIA 如果为下面的内容，则返回TRUE
cllnYvr3 ISBLANK	Bhg,P.7 值为空白单元格。
'j'G4P_G ISERR	Jm[_X 值为任意错误值（除去#N/A）。
#j4jZBOTM ISERROR	YlJ_$Q[ 值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）。
6S&OE k ISLOGICAL	)JXy>q# 值为逻辑值。
?5<x$YI ISNA	Yx':~ 值为错误值#N/A（值不存在）。
J?_-Dg(= ISNONTEXT	5j>olz=n} 值为不是文本的任意项（请注意，此函数在值为空单元格时返回TRUE）。
V\|j{#; ISNUMBER	5#BM 值为数字。
/j"aOLL\| ISREF	O\q-Ai 值为引用。
,&iZ*6=X?0 ISTEXT	$VnPs!a 值为文本。

关于VLOOKUP函数语法，请参阅：第4章第1节疑难122

→ 解决方案2
使用IFERROR函数简化公式。
→ 操作方法
IFERROR函数屏蔽错误值
在D8单元格输入以下公式：
=IFERROR(VLOOKUP(D3,A3:B10,2,FALSE),"未找到")
其中，IFERROR函数的语法为：
IFERROR(value,value_if_error)
其中，第2参数为当第1参数为任意错误值时返回的指定值，如果第1参数不是错误值，则返回第1参数自身的值。使用该函数，可以有效减少公式长度，但在Excel 2003等早期版本中不被支持。
=IFERROR(原公式,出错需返回的值)
→ 知识扩展
常见的7种错误产生原因及解决方案
1．#VALUE!错误
原因1     公式使用文本（文本形式的数字除外）进行算术运算。
解决方法：首先确认公式运算具有实际意义，比如不能建立类似“="姓名"+50”没有意义的公式；其次，可以使用适当的函数以忽略错误。例如使用N或SUM函数让加法运算忽略文本。

原因2     公式使用文本（含文本形式的数字）代替逻辑值。
例如，A1是文本型数字1，A2输入公式：
=IF(A1,"启动","关闭")
解决方法：使用完整的判断语句，如输入公式：
=IF(A1="1","启动","关闭")

原因3     在单一单元格中使用多个单元格的数据区域，且不存在绝对交集。例如在A1单元格输入=C3:D4
解决方法：将数值区域改为单一数值，或者根据需要使用数组公式或多单元格数组公式。

原因4     函数计算时参数超出范围导致的错误，例如：
=FIND("a","BCD")
=FIND("a","abcd",5)
在字符串"BCD"中找不到小写字母"a"，或者在字符串"abcd"的第5个字符开始查找，超出字符串长度范围。
解决方法：根据数据情况正确建立公式，或者使用IFERROR、ISERR、ISNUMBER等函数进行判断后屏蔽错误。

2．#DIV/0!“被零除”错误
公式使用除法运算（包括使用MOD求余数）时，分母为0产生的错误。多数原因是分母引用空单元格而被视为0参与运算。
解决方法：使用IF函数判断分母不为0再进行原公式计算。例如公式：
=A1/B1
改为：
=IF(B1=0,"",A1/B1)

3．#NAME?“无效名称”错误
原因1     在公式中输入了不可识别为函数或名称的文本，或者定义的名称被删除。
解决方法：检查函数名称拼写是否正确，建议使用“公式记忆式键入”功能辅助输入正确函数；检查名称管理器中是否包含公式中使用的名称，或者公式中的文本未使用半角双引号包含。例如下面公式中函数名称拼写错误：
=SUBSTUTITE("方  法"," ",)
应改为：
=SUBSTITUTE("方  法"," ",)

原因2     公式中的文本未用引号包含
=IF(B3=男,55,50)
“男”将被误识别为未定义的“名称”，公式应改为：
=IF(B3="男",55,50)

原因3     使用了Excel 4.0宏表函数或者其他工作簿自定义函数而未复制VBA代码而产生的错误。
解决方法：检查文件格式是否为“启用宏的工作簿”即.xlsm格式，是否已将自定义函数代码复制到本工作簿。

4．#N/A“值不可用”错误
一般为查找函数HLOOKUP、VLOOKUP、MATCH、LOOKUP查不到正确结果返回的错误。
原因1     使用精确匹配查找时，查找区域不包含所查数据。

原因2     查找区域或查找的数据可能使用了文本形式数字，与数值不相匹配。

原因3     使用模糊匹配查找时，查找区域的数据未按函数要求升序或降序排列。
解决方法：确认查找区域包含查找的值，检查数据类型、排序方式是否与查找函数使用的参数相匹配，并使用IS类函数或IFERROR函数屏蔽错误。

5．#REF!“无效的单元格引用”错误
原因1     引用的单元格被删除。
解决方法：检查包含此错误的单元格，并根据需要重写建立公式。

原因2     引用函数参数的值没有对应的单元格区域。
例如下面公式中，A1单元格上方已无单元格，因此返回#REF!错误，需避免此类错误：
=OFFSET(A1,-1,0)
下面公式中，因工作表标签“1-1”包含特殊字符，INDIRECT函数返回#REF!错误：
=INDIRECT("1-1!A1")
因此在引用时需要用一对单引号包含工作表标签，将公式改为：
=INDIRECT("'1-1'!A1")

6．#NUM!“数字”错误
原因              超出数据个数。例如公式：
=SMALL(A1:A10,11)
总数据10个，求第11小的值。在数组公式中SMALL(IF(条件,ROW(数据)),ROW(1:1))向下复制的行数超过满足条件的记录个数时，会出现此错误。

7．#NULL!“空值”错误
原因              使用交叉引用运算符引用两个没有共有单元格区域。例如公式：
=B3 C5:F5
其中，B3单元格与C5:F5单元格区域不存在共有单元格区域。