原文标题:《Excel 公式的 8 种错误类型,最后一个 90% 的人都不知道!》

常用 Excel 的小伙伴们,应该多多少少用过函数公式吧?

一些复杂难搞的统计、查找问题,有时写个公式就能效率翻番~

但是,公式也不总是一次性就能写对的,这个过程中可能会出现诸如「#REF!」、「#VALUE!」等的错误!

所以今天咱就来给大家分析一下,Excel 公式的几种常见错误类型,以及排查错误的方法~

#VALUE!

含义:这个错误通常表示键入公式的方式错误,或者引用的单元格错误

这种错误非常普遍,并且很难找到具体原因。

比如,公式中存在类型不匹配的问题:将文本与数字放在一起进行数学运算。

例:

盘点 Excel 公式的 8 种错误类型-编程知识网

排查方法:

❶ 检查公式中的运算是否涉及不兼容的数据类型。

❷ 确保所有函数的参数都是正确的数据类型。

❸ 如果使用了文本函数,确保文本字符串是正确的格式。

#REF!

含义:这个错误表示引用了不存在的单元格,通常是因为引用的单元格或整个工作表被删除或粘贴覆盖。

例:

盘点 Excel 公式的 8 种错误类型-编程知识网

排查方法:

❶ 检查公式中的单元格引用是否指向了已经删除或移动的单元格。

❷ 如果使用了 3D 引用(引用多个工作表上的同一单元格或范围,如「Sheet1!A1」),确保所有工作表都存在。

❸ 使用 Excel 的「查找和替换」功能查找所有的#REF! 错误,并检查它们的引用。

#DIV/0!

含义:这个错误表示公式尝试将一个数字除以零

例:

盘点 Excel 公式的 8 种错误类型-编程知识网

排查方法:

❶ 检查公式中的除数是否为零。

❷ 使用 Iferror 函数或 If 语句来避免除以零的错误,比如「=IF (A1=0,””,B1 / A1)」。

#NAME?

含义:这个错误通常表示公式中存在 Excel 无法识别的文本,可能是因为函数名拼写错误,或者自定义的名称未定义。

例:

盘点 Excel 公式的 8 种错误类型-编程知识网

▲ 函数名称 Sum 拼写错误

盘点 Excel 公式的 8 种错误类型-编程知识网

▲ 想要自定义的函数名称未定义

排查方法:

❶ 检查函数名和变量名是否正确拼写。

❷ 确保所有自定义名称都已经在工作簿中定义。

❸ 关于自定义名称的用法可以参考:这个新函数,居然能把英文公式变中文?羡慕哭了!

#NUM!

含义:这个错误表示公式或函数中包含无效数值,比如使用了错误的数据类型或数字格式,以及公式产生的数字过大或过小。

例: 

盘点 Excel 公式的 8 种错误类型-编程知识网

▲ 函数的参数 2 让公式结果超出可显示范围

排查方法:

❶ 检查公式中是否有不合理的数值。

❷ 确保所有数值参数都在函数的有效范围内,使函数结果介于 -1*10307 和 1*10307 之间。

#N/A

含义:这个错误表示公式中的某些值不可用,通常与 Vlookup 或 Hlookup 函数有关,当公式找不到引用值时会出现。

例:

盘点 Excel 公式的 8 种错误类型-编程知识网

排查方法:

❶ 检查 Vlookup 或 Hlookup 函数的查找范围是否正确。

❷ 确保查找值确实存在。

#SPILL!

含义:这个错误表示在数组公式中,打算放置结果的溢出范围过小,使结果无法展开。

例:

盘点 Excel 公式的 8 种错误类型-编程知识网

▲ 预留给数组展开的单元格中有别的内容

排查方法:

❶ 检查数组公式是否超出了可用单元格的边界。

❷ 尝试将数组公式的结果分配到没被占用的单元格中。

❸ 数组公式无法溢出到合并单元格中,可将单元格取消合并。

#NULL!

含义:这个错误表示公式使用了不正确的区域运算符,或在区域引用之间使用了交叉运算符(空格字符)来指定不相交的两个区域的交集。

例:

盘点 Excel 公式的 8 种错误类型-编程知识网

▲ 运算区域相交,可以使用空格作为区域运算符

盘点 Excel 公式的 8 种错误类型-编程知识网

▲ 运算区域不相交,使用空格作为区域运算符会出现错误

排查方法:

❶ 改用「:」或「,」这种正确的区域运算符(比如「A1:A11」,「A1:A10,C1:C10」)。

❷ 检查公式中的区域引用是否正确,确保它们是相交的。

其他错误

表格中含有不可见字符或其他「脏」数据而导致公式出错:

参见:整理了 4 个小时,Excel 数据清洗的 7 个函数,都在这里了!

不知道公式哪里出错时

可以用【公式】选项卡下的【公式求值】可视化公式的每个运算步骤,找出在哪一步出错。

盘点 Excel 公式的 8 种错误类型-编程知识网

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:卫星酱