- 发帖
- 16132
- 学分
- 16242
- 经验
- 2562
- 精华
- 49
- 金币
- 0
|
疑难72如何将公式返回的错误值转为其他值 Qkt
j 80i-)a\n Y)X
'hk)5|
iX 3Y:
如图 72‑1所示，在D5单元格使用公式根据D3单元格输入的ID号查询客户名称，当ID号不在A列的数据中时，会出现#N/A错误，能否返回“未找到”？ 5"x=k
p>!d
+,"O#`sy< → 解决方案2 K\ bA[5+N 使用IFERROR函数简化公式。 yvQRr75 → 操作方法 vz\^Aa
#fv IFERROR函数屏蔽错误值 Ys+2/>! 在D8单元格输入以下公式： @ X$~{Vp__ =IFERROR(VLOOKUP(D3,A3:B10,2,FALSE),"未找到") !foiGZ3g 其中，IFERROR函数的语法为： a#U2y " IFERROR(value,value_if_error) g>@T5&1q
* 其中，第2参数为当第1参数为任意错误值时返回的指定值，如果第1参数不是错误值，则返回第1参数自身的值。使用该函数，可以有效减少公式长度，但在Excel 2003等早期版本中不被支持。 ZQY]c
=IFERROR(原公式,出错需返回的值) " R=,W{= → 知识扩展 vr:5+wew 常见的7种错误产生原因及解决方案 d*M:P jG@ 1．#VALUE!错误 T>$S&U 原因1 公式使用文本（文本形式的数字除外）进行算术运算。 q6A"+w,N 解决方法：首先确认公式运算具有实际意义，比如不能建立类似“="姓名"+50”没有意义的公式；其次，可以使用适当的函数以忽略错误。例如使用N或SUM函数让加法运算忽略文本。 C0 RnBu <-Hw@g
;,OZ8g)LH 原因2 公式使用文本（含文本形式的数字）代替逻辑值。 =>y%Aj&4 例如，A1是文本型数字1，A2输入公式： dKG 2f =IF(A1,"启动","关闭") ,V>7eQt?
解决方法：使用完整的判断语句，如输入公式： vXq2="+ =IF(A1="1","启动","关闭") j9voeV|7 v
v F:
i]8 +JG6 原因3 在单一单元格中使用多个单元格的数据区域，且不存在绝对交集。例如在A1单元格输入=C3:D4 "8BZj;yS 解决方法：将数值区域改为单一数值，或者根据需要使用数组公式或多单元格数组公式。 "DpgX8lG_ km 0LLYG
w jRv =[ 原因4 函数计算时参数超出范围导致的错误，例如： :iKk"r,2P[ =FIND("a","BCD") K6..N\7 =FIND("a","abcd",5) F\)?Ntj)>@ 在字符串"BCD"中找不到小写字母"a"，或者在字符串"abcd"的第5个字符开始查找，超出字符串长度范围。 $G,#nh2 oD 解决方法：根据数据情况正确建立公式，或者使用IFERROR、ISERR、ISNUMBER等函数进行判断后屏蔽错误。 nQHQVcDs8 eHIsTL@Fp
`~+1i5-} 2．#DIV/0!“被零除”错误 hFLLg|@ 公式使用除法运算（包括使用MOD求余数）时，分母为0产生的错误。多数原因是分母引用空单元格而被视为0参与运算。 A;ti$jy 解决方法：使用IF函数判断分母不为0再进行原公式计算。例如公式： )
<>1Q{j@ =A1/B1 :S_3(/} \ 改为： UHaY|I${U =IF(B1=0,"",A1/B1) mO?yrM * 5N5Deb#V
#mFY?Zp) 3．#NAME?“无效名称”错误 y%wjQC 0~ 原因1 在公式中输入了不可识别为函数或名称的文本，或者定义的名称被删除。 d i; Fj 解决方法：检查函数名称拼写是否正确，建议使用“公式记忆式键入”功能辅助输入正确函数；检查名称管理器中是否包含公式中使用的名称，或者公式中的文本未使用半角双引号包含。例如下面公式中函数名称拼写错误： !! \O B6 =SUBSTUTITE("方 法"," ",) O{Y_j&1 应改为： HOY@< ' =SUBSTITUTE("方 法"," ",) vgyv~Px]AW 5 `Mos
)W0zu\fL = 原因2 公式中的文本未用引号包含 ;_ TP Jy =IF(B3=男,55,50) S0zk< S
“男”将被误识别为未定义的“名称”，公式应改为： o&PPW~D+h@ =IF(B3="男",55,50) FL8?<bU 8b!-2d:*
=Felo8+ 原因3 使用了Excel 4.0宏表函数或者其他工作簿自定义函数而未复制VBA代码而产生的错误。 .! ><qV g 解决方法：检查文件格式是否为“启用宏的工作簿”即.xlsm格式，是否已将自定义函数代码复制到本工作簿。 :Qekv(z ?Z {:[.
:#pfv)W6t 4．#N/A“值不可用”错误 6M)
4v{F 一般为查找函数HLOOKUP、VLOOKUP、MATCH、LOOKUP查不到正确结果返回的错误。 k O8W> 原因1 使用精确匹配查找时，查找区域不包含所查数据。 F Tk`Mq IMF9eS{L
=eB^( !M 原因2 查找区域或查找的数据可能使用了文本形式数字，与数值不相匹配。 \-F
F[:|J ^5,B6
q>^x ,:L 原因3 使用模糊匹配查找时，查找区域的数据未按函数要求升序或降序排列。 /z(;1$Ld6{ 解决方法：确认查找区域包含查找的值，检查数据类型、排序方式是否与查找函数使用的参数相匹配，并使用IS类函数或IFERROR函数屏蔽错误。 ndB [f aEJds}eE6)
cCWk^ lF], 5．#REF!“无效的单元格引用”错误 >oOZ Duj 原因1 引用的单元格被删除。 V]fsjpvlmr 解决方法：检查包含此错误的单元格，并根据需要重写建立公式。 ~ V:@4P ^~65M/
'U %L\v, 原因2 引用函数参数的值没有对应的单元格区域。 mY& HK) 例如下面公式中，A1单元格上方已无单元格，因此返回#REF!错误，需避免此类错误： X1J' =OFFSET(A1,-1,0) l^WPv/}? 下面公式中，因工作表标签“1-1”包含特殊字符，INDIRECT函数返回#REF!错误： t
(sQw '> =INDIRECT("1-1!A1") LNU#NJ^Axt 因此在引用时需要用一对单引号包含工作表标签，将公式改为：
Z'ZN^j{ =INDIRECT("'1-1'!A1") YYzl"<)c {B d 0
PRpW*#"EI 6．#NUM!“数字”错误 3R|C$+Sc 原因 超出数据个数。例如公式： A@HCd&h =SMALL(A1:A10,11) + NpH k 总数据10个，求第11小的值。在数组公式中SMALL(IF(条件,ROW(数据)),ROW(1:1))向下复制的行数超过满足条件的记录个数时，会出现此错误。 q n2X. _` = w#sCy
dh.vZ0v=7 7．#NULL!“空值”错误 ^@a|s
Sb 原因 使用交叉引用运算符引用两个没有共有单元格区域。例如公式： g5,Bj =B3 C5:F5 5kju{2`GF 其中，B3单元格与C5:F5单元格区域不存在共有单元格区域。 s+jL BY +:#x!i;W8[
|
