怎样使公式中不出现#N/A等错误值
经常出现公式结果返回#N/A等错误值,怎样不显示错误值呢,有2个方法,一是IS类函数,一是iferror函数。
用来检验数值或引用类型的12个工作表函数,如表1,概括为 IS 类函数。可以检验数值的类型并根据参数取值返回 TRUE或FALSE。例如,如果数值为对空白单元格的引用,函数 ISBLANK 返回逻辑值 TRUE,否则返回 FALSE。
函数 |
功能 |
语法 |
其中包括用来检验数值或引用类型的12个工作表函数。 |
||
isblank |
空白单元格 |
isblank(value) |
iserr |
任意错误值(除去 #N/A) |
iserr(value) |
iserror |
任意错误值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!) |
iserror(value) |
iseven |
如果参数value为偶数,返回 TRUE,否则返回 FALSE |
iseven(value) |
islogical |
逻辑值 |
islogical(value) |
isna |
错误值 #N/A(值不存在) |
isna(value) |
isnontext |
任意不是文本的项(注意此函数在值为空白单元格时返回 TRUE) |
isnontext(value) |
isnumber |
数字 |
isnumber(value) |
isodd |
如果参数value 为奇数,返回 TRUE,否则返回 FALSE |
isodd(value) |
ispmt |
计算特定投资期内要支付的利息。 |
ISPMT(rate, per, nper, pv) |
isref |
引用 |
isref(value) |
istext |
文本 |
istext(value) |
表1
Value 为需要进行检验的数值。函数如果为下面的内容,则返回 TRUE。
说明:IS 类函数的参数value 是不可转换的。例如,在其他大多数需要数字的函数中,文本值 "19" 会被转换成数字 19。然而在公式 Isnumber ("19") 中,"19" 并不由文本值转换成别的类型的值,函数 Isnumber 返回 FALSE。IS 类函数在用公式检验计算结果时十分有用。当它与函数 IF 结合在一起使用时,可以提供一种方法用来在公式中查出错误值(请参阅下面的示例)。
示例:
1、在一个工作表中,假设需要计算 A1:A4 区域的平均值,但不能确定单元格内是否包含数字。如果 A1:A4 不包含任何数字,公式 AVERAGE(A1:A4) 返回错误值 #DIV/0!。为了应付这种情况,可以使用下面的公式来查出潜在的错误值:
IF(ISERROR(AVERAGE(A1:A4)),"NoNumbers",AVERAGE(A1:A4))
2、在下面的表格中,当分母为0,除法运算结算显示#DIV/0!,如图1:
图1
怎么让结果不显示#DIV/0!,而显示“无数据”?输入公式
=IF(ISERR(B11/C11),"无数据",B11/C11),就可以显示想要的结果。如图2。
图2
3、用Vlookup查找如果没有找不到会显示#N/A,如下表中F列和G列是用Vlookup函数在A1:C19区域内查找,结果有#N/A出现。如图3。
图3
F列和G列用isna函数,如G2公式=IF(ISNA(VLOOKUP(E2,$A$2:$C$19,3,0)),"无",VLOOKUP(E2,$A$2:$C$19,3,0)),显示结果如图4所示。
图4
也可以用Iferror函数,如果能查找到第1个参数为vlookup函数的结果,如果找不到则返回第2个参数的内容。G2公式=IFERROR(VLOOKUP(E2,$A$2:$C$19,3,0),"无")。