- 发帖
- 16132
- 学分
- 16242
- 经验
- 2562
- 精华
- 49
- 金币
- 0
|
疑难72如何将公式返回的错误值转为其他值
9QO!vx mk$Yoz KH
iYV rGP?
E3 如图 72‑1所示,在D5单元格使用公式根据D3单元格输入的ID号查询客户名称,当ID号不在A列的数据中时,会出现#N/A错误,能否返回“未找到”? +X4ttv
G
Z-n!
^ Q|zE@nLS
→ 解决方案1: l93Q"*_ 使用IS类函数设置判断条件。 |O #w dnYW → 操作方法 kY0g
}o'< 在D6:D8单元格分别输入以下公式: Bil;@,Z# =IF(ISNA(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE))
yS(=eB_ =IF(ISERROR(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE)) H>Iet}/c → 原理分析 :5n"N5Go 针对VLOOKUP函数发生错误返回错误值#N/A的特性,使用ISNA函数进行判断。如果公式可能返回其他错误值,则使用对错误值均通用的ISERROR函数进行判断,一般表示为: _j|n}7a =IF(ISERROR(原公式),出错需返回的值,原公式) ?.|wfBI IS类函数判断屏蔽错误值 U{.+*e18 以ISERROR函数为例,其语法为: 9^=t@ ISERROR(value) ahz@HX 判断参数是否为错误值,并返回TRUE或FALSE,与ISBLANK、ISERR、ISLOGICAL、ISNA、ISNONTEXT、ISNUMBER、ISREF、ISTEXT等函数并称为IS类函数,具体参数值判断情况见表格 72‑1。 8O}A/*1FJ 表格72‑1 IS类函数参数特征 +;4AG::GN 关于VLOOKUP函数语法,请参阅:第4章第1节疑难122 Dk.9&9mz _|Kv~\G! T(fR/~:z? → 解决方案2 |4 d{X@`& 使用IFERROR函数简化公式。 *<h → 操作方法 =G3J.S*Riy IFERROR函数屏蔽错误值 ]!S)O|_D[ 在D8单元格输入以下公式: k%4A::= =IFERROR(VLOOKUP(D3,A3:B10,2,FALSE),"未找到") }Rf:DmPE 其中,IFERROR函数的语法为: D;#Yn M3 IFERROR(value,value_if_error) $dWl A<u 其中,第2参数为当第1参数为任意错误值时返回的指定值,如果第1参数不是错误值,则返回第1参数自身的值。使用该函数,可以有效减少公式长度,但在Excel 2003等早期版本中不被支持。 sou$qKoG01 =IFERROR(原公式,出错需返回的值) 7l|> → 知识扩展 xF :poi
常见的7种错误产生原因及解决方案 vN6]6nUOiT 1.#VALUE!错误 t+TbCe 原因1 公式使用文本(文本形式的数字除外)进行算术运算。
![."xHVeL 解决方法:首先确认公式运算具有实际意义,比如不能建立类似“="姓名"+50”没有意义的公式;其次,可以使用适当的函数以忽略错误。例如使用N或SUM函数让加法运算忽略文本。 =B 4g EWR e7j]BzGvl ?p/kuv{\o# 原因2 公式使用文本(含文本形式的数字)代替逻辑值。 B}xo|:f!zj 例如,A1是文本型数字1,A2输入公式: gQcr'[[a =IF(A1,"启动","关闭") \~1zAiSd># 解决方法:使用完整的判断语句,如输入公式: J\8l%4q3 =IF(A1="1","启动","关闭") '<E8<bi KzH}5:qI T`2fPxM:cZ 原因3 在单一单元格中使用多个单元格的数据区域,且不存在绝对交集。例如在A1单元格输入=C3:D4 zR/p}Wu|! 解决方法:将数值区域改为单一数值,或者根据需要使用数组公式或多单元格数组公式。 NB yN}e cB"F1~z jU9zCMyNF 原因4 函数计算时参数超出范围导致的错误,例如: laRKt"A =FIND("a","BCD") V{^fH6;[ =FIND("a","abcd",5) J?=Ob?+
_ 在字符串"BCD"中找不到小写字母"a",或者在字符串"abcd"的第5个字符开始查找,超出字符串长度范围。 jKi*3-& 解决方法:根据数据情况正确建立公式,或者使用IFERROR、ISERR、ISNUMBER等函数进行判断后屏蔽错误。 Pc-8L]2oaF NS/L! "g 'C?f"P:X{ 2.#DIV/0!“被零除”错误 }4ghT(C}$ 公式使用除法运算(包括使用MOD求余数)时,分母为0产生的错误。多数原因是分母引用空单元格而被视为0参与运算。 D;8V{Hs
解决方法:使用IF函数判断分母不为0再进行原公式计算。例如公式: n|`):sP =A1/B1 "5DAGMU 改为: _"t.1+-K =IF(B1=0,"",A1/B1) d4eC Bqx dfkTDG+ ~q%9zO' 3.#NAME?“无效名称”错误 h.+&=s!Nsy 原因1 在公式中输入了不可识别为函数或名称的文本,或者定义的名称被删除。 RxeRO2 解决方法:检查函数名称拼写是否正确,建议使用“公式记忆式键入”功能辅助输入正确函数;检查名称管理器中是否包含公式中使用的名称,或者公式中的文本未使用半角双引号包含。例如下面公式中函数名称拼写错误: X}QmeY[0I =SUBSTUTITE("方 法"," ",) c(=O`%B{ 应改为: 7G=P|T\ =SUBSTITUTE("方 法"," ",)
O,,n
[/,6O =}o>_+" 原因2 公式中的文本未用引号包含 >^ 1S26 =IF(B3=男,55,50) TF3q?0 “男”将被误识别为未定义的“名称”,公式应改为: S^"e5n2 =IF(B3="男",55,50) J?ZVzKTb>} =gv/9ce)3 FG36,6N%2j 原因3 使用了Excel 4.0宏表函数或者其他工作簿自定义函数而未复制VBA代码而产生的错误。 cz~FWk 解决方法:检查文件格式是否为“启用宏的工作簿”即.xlsm格式,是否已将自定义函数代码复制到本工作簿。 M&T/vByTn_ ok_{8z\# l@F
e(^5E 4.#N/A“值不可用”错误 |g^W @.P 一般为查找函数HLOOKUP、VLOOKUP、MATCH、LOOKUP查不到正确结果返回的错误。 v})Ti190 原因1 使用精确匹配查找时,查找区域不包含所查数据。 +rw3.d
bQQ/7KM )Ua2x@j'C@ 原因2 查找区域或查找的数据可能使用了文本形式数字,与数值不相匹配。 |.8=gS5 D&d:>.~u a^/20UFq 原因3 使用模糊匹配查找时,查找区域的数据未按函数要求升序或降序排列。 @"7dk.| 解决方法:确认查找区域包含查找的值,检查数据类型、排序方式是否与查找函数使用的参数相匹配,并使用IS类函数或IFERROR函数屏蔽错误。 zLK
~i>aW ;
xH'%W9z pXlBKJmW 5.#REF!“无效的单元格引用”错误
n#'',4f 原因1 引用的单元格被删除。 3Qr!?=nf 解决方法:检查包含此错误的单元格,并根据需要重写建立公式。 tf79Gb> C$;s+ALy[ T.dO0$,Q@$ 原因2 引用函数参数的值没有对应的单元格区域。 0A4| 例如下面公式中,A1单元格上方已无单元格,因此返回#REF!错误,需避免此类错误: 9)hC,)5
=OFFSET(A1,-1,0) DL!s)5!M 下面公式中,因工作表标签“1-1”包含特殊字符,INDIRECT函数返回#REF!错误: Elk$9 << =INDIRECT("1-1!A1") VQx-gm8}! 因此在引用时需要用一对单引号包含工作表标签,将公式改为: htSk2N/ =INDIRECT("'1-1'!A1") Q&a<9e& iDxgAV f* OHvzK8 6.#NUM!“数字”错误 SO<K#HfE$? 原因 超出数据个数。例如公式: Ri0+nJ6 =SMALL(A1:A10,11) ZKTBjOa]* 总数据10个,求第11小的值。在数组公式中SMALL(IF(条件,ROW(数据)),ROW(1:1))向下复制的行数超过满足条件的记录个数时,会出现此错误。 =gyK*F(RK e|9Bzli{ }X
:r:{r 7.#NULL!“空值”错误 sb5kexGxkc 原因 使用交叉引用运算符引用两个没有共有单元格区域。例如公式: sgsMlZ
3/ =B3 C5:F5 bxHk0w 其中,B3单元格与C5:F5单元格区域不存在共有单元格区域。 l7um9@[4 '
Em($A(
|