Excel技巧应用篇:错误不可怕,就看你如何使用ISNA函数 当Excel无法找到所需内容时,单元格中会出现“N/A”错误。要拦截和处理此类错误,可以使用ISNA函数,它可以使公式更加友好,使工作表更加美观。 ISNA函数 Excel ISNA函数用于检查单元格或公式是否存在N/A错误。结果是一个逻辑值:如果检测到#N/A错误,则为TRUE,否则为FALSE。 ISNA函数的语法为: ISNA(value) 其中,value是要检查#N/A错误的单元格值或公式。 使用ISNA函数的基本公式示例: =ISNA(A2) 如果单元格A2包含#N/A错误,则返回TRUE,如果出现任何其他错误、值或空白单元格,则返回FALSE。如下图1所示。 图1 用以上形式使用ISNA函数的几乎很少。通常,它与其他函数一起用于评估某个公式的结果。为此,只需将ISNA的value参数设置另一个公式: ISNA(公式) 在下面的数据集中,假设要比较两个列表(列A和列D),并确定两个列表中都存在的名称以及仅出现在列表1中的名称。 要将A2中的值与列D中的每个值进行比较,公式为: =MATCH(A2,D2:D9,0) 如果找到查找值,MATCH函数将返回其在查找数组中的相对位置,否则将发生#N/A错误。为了测试MATCH的结果,将其嵌套在ISNA函数中: =ISNA(MATCH(A2,D2:D9,0)) 如下图2所示。 图2 现在,可以清楚地看到哪些学生通过了所有测试,哪些学生至少有一次测试失败。 IF/ISNA组合的Excel公式 ISNA函数只能返回两个布尔值,因此可将其与IF函数结合使用,显示自定义消息: IF(ISNA(…),有错误时的文本, 没有错误时的文本) 进一步完善上面的示例,找出组A的哪些学生都通过了任何测试,并返回“没有失败的测试”。对于剩下的学生,将返回“失败”。为此,在IF的逻辑测试中嵌入ISNA/MATCH公式: =IF(ISNA(MATCH(A2,D2:D9,0)),"没有失败的测试","失败") 结果看起来更好且更直观,如下图3所示。 图3 VLOOKUP/ISNA组合的Excel公式 IF/ISNA组合是一个通用的解决方案,可以与任何函数一起使用,该函数在一组数据中搜索某些内容,并且在找不到查找值时返回#N/A错误。 带有VLOOKUP函数的ISNA函数的语法如下: IF(ISNA(VLOOKUP(…),“自定义文本”,VLOOKUP(…)) 也就是说:如果VLOOKUP导致一个#N/A错误,则返回自定义文本,否则返回VLOOKUP的结果。 在我们的示例表中,假设希望返回学生考试不及格的科目。对于成功通过所有测试的人,将显示“无失败测试”。 为了查找科目,构造了经典的VLOOKUP公式: =VLOOKUP(A2,D3:E9,2,FALSE) 然后将其嵌套在上面讨论的通用IF/ISNA公式中: =IF(ISNA(VLOOKUP(A2,D3:E9,2,FALSE)),"没有失败的测试",VLOOKUP(A2,D3:E9,2,FALSE)) 结果如下图4所示。 图4 在Excel 2013及更高版本中,可以利用IFNA函数捕获和处理N/A错误。这使你的公式更短,更容易阅读。 例如,将#N/A错误替换为破折号(“-”),并得到这个优雅的解决方案: =IFNA(VLOOKUP(A2,D3:E9,2,FALSE),"-") 结果如下图5所示。 图5 Excel 365和2021版的用户根本不需要任何包装函数,因为其XLOOKUP函数本身可以处理#N/A错误: =XLOOKUP(A3,D3:D9,E3:E9,“-”) 结果与上图5所示完全相同。 SUMPRODUCT/ISNA组合统计#N/A错误数 要统计特定单元格区域内的#N/A错误,可将ISNA函数与SUMPRODUCT函数一起使用,方法如下: SUMPRODUCT(--(ISNA(range)) 在这里,ISNA返回一个TRUE值和FALSE值数组,双否定(--)将逻辑值强制转换为数值1和0,然后SUMPRODUCT将结果相加。 例如,要找出有多少学生在所有测试中都通过,修改单元格区域(A2:A13)查找值的MATCH公式,并将其嵌套在ISNA函数中: =SUMPRODUCT(--ISNA(MATCH(A2:A13,D2:D9,0))) 公式确定9名学生没有失败的测试,即MATCH函数返回9个N/A错误,如下图6所示。 图6 这就是如何在Excel中创建和使用ISNA公式,希望对你有所帮助。 |
|