论坛风格切换切换到宽版
  • 2530阅读
  • 1回复

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

上一主题 下一主题
离线阿文哥
 

发帖
16132
学分
16242
经验
2562
精华
49
金币
0
只看楼主 倒序阅读 使用道具 0楼 发表于: 2014-08-25
疑难72如何将公式返回的错误值转为其他值 CM6! 1 7  
;U3K@_  
=sso )/3  
P$]K  
如图 72‑1所示,在D5单元格使用公式根据D3单元格输入的ID号查询客户名称,当ID号不在A列的数据中时,会出现#N/A错误,能否返回“未找到”?
I%b5a` 7  
x=-(p}0o;<  
72‑1   公式返回错误值
96S$Y~G# &  
2PSkLS&IM  
→ 解决方案1 O`I}Lg]~q  
使用IS类函数设置判断条件。 ~pHuh#>  
→ 操作方法 :9e4(7~ona  
D6:D8单元格分别输入以下公式: l](!2a=[  
=IF(ISNA(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE)) JmeE}:5lpj  
=IF(ISERROR(VLOOKUP(D3,A3:B10,2,FALSE)),"未找到",VLOOKUP(D3,A3:B10,2,FALSE)) a|(|!=  
→ 原理分析 <9\Lv]ng  
针对VLOOKUP函数发生错误返回错误值#N/A的特性,使用ISNA函数进行判断。如果公式可能返回其他错误值,则使用对错误值均通用的ISERROR函数进行判断,一般表示为: Vvp{y  
=IF(ISERROR(原公式),出错需返回的值,原公式) 4 9HP2E  
IS类函数判断屏蔽错误值 qO/3:-  
ISERROR函数为例,其语法为: bc NyB$S  
ISERROR(value) `sA xk  
判断参数是否为错误值,并返回TRUEFALSE,与ISBLANKISERRISLOGICALISNAISNONTEXTISNUMBERISREFISTEXT等函数并称为IS类函数,具体参数值判断情况见表格 72‑1 %&0/ Ypp=  
表格72‑1        IS类函数参数特征 {c~w Ms#  
-0TI7 @  
函数
hi4-Z=pl  
如果为下面的内容,则返回TRUE
.Yvy37n((  
ISBLANK
q#1G4l.  
值为空白单元格。
r;{ggwY&J  
ISERR
A'Z!l20_  
值为任意错误值(除去#N/A)。
CpK:u! Dn  
ISERROR
b~B'FD  
值为任意错误值(#N/A#VALUE!#REF!#DIV/0!#NUM!#NAME?#NULL!)。
s>1\bio*I  
ISLOGICAL
FS!9 j8  
值为逻辑值。
6pt|Crvu  
ISNA
J1 w3g,  
值为错误值#N/A(值不存在)。
K7C!ZXw~  
ISNONTEXT
45x4JG  
值为不是文本的任意项(请注意,此函数在值为空单元格时返回TRUE)。
CEBu[TT/9  
ISNUMBER
Thk CKM  
值为数字。
_yF@k~ h  
ISREF
/sn }Q-Zy2  
值为引用。
^6Zx-Mf\  
ISTEXT
bSn={O"M  
值为文本。
关于VLOOKUP函数语法,请参阅:4章第1节疑难122 hsJ^Au=})w  
HR?bnkv|id  
f,|;eF-Z  
→ 解决方案2 )T?BO  
使用IFERROR函数简化公式。 (D6ks5Uui  
→ 操作方法 Q&lb]U+\u  
IFERROR函数屏蔽错误值 > 5 ?c93?  
D8单元格输入以下公式: 6`%|-o :  
=IFERROR(VLOOKUP(D3,A3:B10,2,FALSE),"未找到") CIsX$W  
其中,IFERROR函数的语法为: :'xZF2  
IFERROR(value,value_if_error) T!Tp:&O-  
其中,第2参数为当第1参数为任意错误值时返回的指定值,如果第1参数不是错误值,则返回第1参数自身的值。使用该函数,可以有效减少公式长度,但在Excel 2003等早期版本中不被支持。 >;F}>_i  
=IFERROR(原公式,出错需返回的值) &y7 =tEV  
→ 知识扩展 !I\eIV>0b  
常见的7种错误产生原因及解决方案 q(WGvl^r  
1#VALUE!错误 #xq3 )B  
原因1     公式使用文本(文本形式的数字除外)进行算术运算。 U#- 5",X|  
解决方法:首先确认公式运算具有实际意义,比如不能建立类似“="姓名"+50”没有意义的公式;其次,可以使用适当的函数以忽略错误。例如使用NSUM函数让加法运算忽略文本。 F>^KXq:Z  
[O}D^qp  
u~ VXe  
原因2     公式使用文本(含文本形式的数字)代替逻辑值。 *3OlWnZ?  
例如,A1是文本型数字1A2输入公式: OoRg:"9{#  
=IF(A1,"启动","关闭") mKyF<1,m  
解决方法:使用完整的判断语句,如输入公式: S,ud pQ7  
=IF(A1="1","启动","关闭") `sy_'`i>X  
iQ1[60?)T  
|`kk mq  
原因3     在单一单元格中使用多个单元格的数据区域,且不存在绝对交集。例如在A1单元格输入=C3:D4 MRZN4<}9  
解决方法:将数值区域改为单一数值,或者根据需要使用数组公式或多单元格数组公式。 O2yD{i#l*#  
XiV K4sD8  
]&B/rSC  
原因4     函数计算时参数超出范围导致的错误,例如: Vq/hk  
=FIND("a","BCD") 8e ?9:VM]  
=FIND("a","abcd",5) @HnahD  
在字符串"BCD"中找不到小写字母"a",或者在字符串"abcd"的第5个字符开始查找,超出字符串长度范围。 x^i97dZS^"  
解决方法:根据数据情况正确建立公式,或者使用IFERRORISERRISNUMBER等函数进行判断后屏蔽错误。 __1Hx?f  
T+t7/PwC;  
 _VM}]A  
2#DIV/0!“被零除”错误 ^|vP").aQm  
公式使用除法运算(包括使用MOD求余数)时,分母为0产生的错误。多数原因是分母引用空单元格而被视为0参与运算。 ydRS\l  
解决方法:使用IF函数判断分母不为0再进行原公式计算。例如公式: 1}SON4U  
=A1/B1 }6 u)wF5  
改为: 1{RA\CF  
=IF(B1=0,"",A1/B1) I>w|80%%  
Xr4k]'Mg  
: ;hm^m]Y  
3#NAME?“无效名称”错误 ,1-idpnX  
原因1     在公式中输入了不可识别为函数或名称的文本,或者定义的名称被删除。 PI9aKNt  
解决方法:检查函数名称拼写是否正确,建议使用“公式记忆式键入”功能辅助输入正确函数;检查名称管理器中是否包含公式中使用的名称,或者公式中的文本未使用半角双引号包含。例如下面公式中函数名称拼写错误: Uq x@9z(  
=SUBSTUTITE("  "," ",) jq_4x[  
应改为: *lu*h&Y  
=SUBSTITUTE("  "," ",) UF\k0oLz  
SO MAs'=  
Sj*W|n\gj  
原因2     公式中的文本未用引号包含 foY]RkW9  
=IF(B3=,55,50) YACx9K H  
“男”将被误识别为未定义的“名称”,公式应改为: (KfQ'B+  
=IF(B3="",55,50) ^ 5>W`vwp  
/,j'V r\"  
nE0I[T(  
原因3     使用了Excel 4.0宏表函数或者其他工作簿自定义函数而未复制VBA代码而产生的错误。 m]'+Eye ]r  
解决方法:检查文件格式是否为“启用宏的工作簿”即.xlsm格式,是否已将自定义函数代码复制到本工作簿。 Lm.N {NV'  
EG_P^ <z  
teLZplC=f  
4#N/A“值不可用”错误 E0aFHC[  
一般为查找函数HLOOKUPVLOOKUPMATCHLOOKUP查不到正确结果返回的错误。 o4j!:CI  
原因1     使用精确匹配查找时,查找区域不包含所查数据。 \l# H#~  
/}%C'  
jaVx9FR +  
原因2     查找区域或查找的数据可能使用了文本形式数字,与数值不相匹配。 {A%&D^o)  
_j{)%%?r  
}dqOE-"I"n  
原因3     使用模糊匹配查找时,查找区域的数据未按函数要求升序或降序排列。 U+( qfa5(  
解决方法:确认查找区域包含查找的值,检查数据类型、排序方式是否与查找函数使用的参数相匹配,并使用IS类函数或IFERROR函数屏蔽错误。 $RHw6*COG  
W'E3_dj+  
.lI.I  
5#REF!“无效的单元格引用”错误 EpCNp FQT<  
原因1     引用的单元格被删除。 p]toDy-}  
解决方法:检查包含此错误的单元格,并根据需要重写建立公式。 2$2@?]|?  
LJ9#!r@H  
yM(ezb  
原因2     引用函数参数的值没有对应的单元格区域。 }XRfHQk  
例如下面公式中,A1单元格上方已无单元格,因此返回#REF!错误,需避免此类错误: > >;He7  
=OFFSET(A1,-1,0) UIZ9" Da  
下面公式中,因工作表标签“1-1”包含特殊字符,INDIRECT函数返回#REF!错误: O`PQ4Q*F  
=INDIRECT("1-1!A1") N[DKA1Ei  
因此在引用时需要用一对单引号包含工作表标签,将公式改为: N>g6KgX{K  
=INDIRECT("'1-1'!A1") j.V7`x  
#k!;=\FV  
O -p^S  
6#NUM!“数字”错误 .\)ek[?  
原因              超出数据个数。例如公式: :Fh_Ya0  
=SMALL(A1:A10,11) {n<1uh9~$8  
总数据10个,求第11小的值。在数组公式中SMALL(IF(条件,ROW(数据)),ROW(1:1))向下复制的行数超过满足条件的记录个数时,会出现此错误。  3L4v@  
z~1S/,Ca  
|uRYejj#j  
7#NULL!“空值”错误 q%1B4 mF'  
原因              使用交叉引用运算符引用两个没有共有单元格区域。例如公式: 8)b*q\ O'  
=B3 C5:F5 k^s7s{  
其中,B3单元格与C5:F5单元格区域不存在共有单元格区域。 }Py Z{yS  
\TYH7wXDP  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水
关注我们的新浪微博:http://e.weibo.com/cpahome

发帖
1267
学分
15
经验
1267
精华
0
金币
0
只看该作者 1楼 发表于: 2016-10-20
曾经拥有的不要忘记;不能得到的更要珍惜;属于自己的不要放弃;已经失去的留作回忆。我刚来~~~嘿嘿~~
快速回复
限100 字节
温馨提示:欢迎交流讨论,请勿纯表情!
 
上一个 下一个