- 发帖
- 16132
- 学分
- 16242
- 经验
- 2562
- 精华
- 49
- 金币
- 0
|
疑难72如何将公式返回的错误值转为其他值 5{/Pn%5 kCjI`=7$[ >upUY(3& d?y\~< 如图 72‑1所示,在D5单元格使用公式根据D3单元格输入的ID号查询客户名称,当ID号不在A列的数据中时,会出现#N/A错误,能否返回“未找到”? =LY^3TlDj
Afhx`J1KO L\b_,'I → 解决方案1: uWtS83i 使用IS类函数设置判断条件。 V+~{a:8[pq → 操作方法 8PjhvU 在D6:D8单元格分别输入以下公式: +pViHOJu&V =IF(ISNA(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE)) /D]r"- =IF(ISERROR(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE)) ?F!J@Xn5 → 原理分析 '"\Mjz)/ 针对VLOOKUP函数发生错误返回错误值#N/A的特性,使用ISNA函数进行判断。如果公式可能返回其他错误值,则使用对错误值均通用的ISERROR函数进行判断,一般表示为: /~huTKA} =IF(ISERROR(原公式),出错需返回的值,原公式) ]%8;c
IS类函数判断屏蔽错误值 >P"/nS"nn 以ISERROR函数为例,其语法为: 78~V/L;@S2 ISERROR(value) Wc;D{p?Lb 判断参数是否为错误值,并返回TRUE或FALSE,与ISBLANK、ISERR、ISLOGICAL、ISNA、ISNONTEXT、ISNUMBER、ISREF、ISTEXT等函数并称为IS类函数,具体参数值判断情况见表格 72‑1。 #&KE_n 表格72‑1 IS类函数参数特征 6U9Fa=%>} 关于VLOOKUP函数语法,请参阅:第4章第1节疑难122 [7w_.(f# z( [ $,e\ l<_mag/j9o → 解决方案2 _?LI0iIFx 使用IFERROR函数简化公式。 I19F\
L`4 → 操作方法 }U1shG[ IFERROR函数屏蔽错误值 F4(U~n< 在D8单元格输入以下公式: _!1c.[\T =IFERROR(VLOOKUP(D3,A3:B10,2,FALSE),"未找到") irj}:f;!eF 其中,IFERROR函数的语法为: t1s@Ub5);I IFERROR(value,value_if_error) cty#@?"e 其中,第2参数为当第1参数为任意错误值时返回的指定值,如果第1参数不是错误值,则返回第1参数自身的值。使用该函数,可以有效减少公式长度,但在Excel 2003等早期版本中不被支持。 jF85bb$ =IFERROR(原公式,出错需返回的值) S9055`v5 → 知识扩展 ^qr[?ky]& 常见的7种错误产生原因及解决方案 d}J#wT 1.#VALUE!错误 |q)Q<%VS' 原因1 公式使用文本(文本形式的数字除外)进行算术运算。 |]b/5s;> 解决方法:首先确认公式运算具有实际意义,比如不能建立类似“="姓名"+50”没有意义的公式;其次,可以使用适当的函数以忽略错误。例如使用N或SUM函数让加法运算忽略文本。 io_64K+K &`W,'qD$ aKr4E3` 原因2 公式使用文本(含文本形式的数字)代替逻辑值。 Gfch|Q^INy 例如,A1是文本型数字1,A2输入公式: w*@Z-'(j =IF(A1,"启动","关闭") o%Q2.
解决方法:使用完整的判断语句,如输入公式: RM$S|y{L =IF(A1="1","启动","关闭") u)>*U'bM ?IO/zkeXg (:]iHg3 原因3 在单一单元格中使用多个单元格的数据区域,且不存在绝对交集。例如在A1单元格输入=C3:D4 824%]i3 解决方法:将数值区域改为单一数值,或者根据需要使用数组公式或多单元格数组公式。 :\We =oX <
rxem(PPu NO1]JpR 原因4 函数计算时参数超出范围导致的错误,例如: aKcV39brr =FIND("a","BCD") KJZY.7 =FIND("a","abcd",5) Ia"
Mi+{ 在字符串"BCD"中找不到小写字母"a",或者在字符串"abcd"的第5个字符开始查找,超出字符串长度范围。 ueo3i1 解决方法:根据数据情况正确建立公式,或者使用IFERROR、ISERR、ISNUMBER等函数进行判断后屏蔽错误。 #R|4(Hl
L ]2
N';(R PmTd
+Gj$ 2.#DIV/0!“被零除”错误 $"1&! 公式使用除法运算(包括使用MOD求余数)时,分母为0产生的错误。多数原因是分母引用空单元格而被视为0参与运算。 mz'8
解决方法:使用IF函数判断分母不为0再进行原公式计算。例如公式: mt7:`- =A1/B1 \LXNdE2B 改为: +O6@)?pI =IF(B1=0,"",A1/B1) uS.a9
Q(
rMloj8O* "
E#%x{d 3.#NAME?“无效名称”错误 5@5="lNjS 原因1 在公式中输入了不可识别为函数或名称的文本,或者定义的名称被删除。 0C_Qp% Z 解决方法:检查函数名称拼写是否正确,建议使用“公式记忆式键入”功能辅助输入正确函数;检查名称管理器中是否包含公式中使用的名称,或者公式中的文本未使用半角双引号包含。例如下面公式中函数名称拼写错误: I
A^DfdZY =SUBSTUTITE("方 法"," ",) yB[LO(i 应改为: cH ?]uu( =SUBSTITUTE("方 法"," ",) 2N#L'v@g=+
A(5?
ci dq&d>f1 原因2 公式中的文本未用引号包含 2Ta F7Jn =IF(B3=男,55,50) B9\o:eY “男”将被误识别为未定义的“名称”,公式应改为: {'[1I_3 =IF(B3="男",55,50) IC~D?c0H: GV/FK{v5 H&+s& |