正在阅读：Excel如何避免公式错误 Excel避免公式错误技巧Excel如何避免公式错误 Excel避免公式错误技巧

 下图中带#字符的奇怪语句，相信你一定没少遇到过。其实，这些都是Excel中常见的函数公式返回的错误值！

每次看到它们的时候，很多人内心恐怕都是下面这样的场景——

今天，将为大家全面介绍这些错误值产生的原因，并且还有应对的办法，帮助大家日后可以轻松地处理函数公式返回的错误值！

-1-错误值生成的原因

生成「#DIV/0!」错误值的原因

当销售人员计算「同比」时，出现了#DIV/0!错误值。

在Excel中出现「#DIV/0!」错误值原因是在于在公式中使用了除法运算，同时除数为0。

比中，当计算「叔玉」的同比值时，由于他上期的完成数为0，当用公式

=(C5-B5)/B5

计算同比时，B5单元格的值为0，所以出现了「#DIV/0!」错误值。

其中DIV是除数的英文（divisor）缩写，而/0表示除数为0。

生成「#NAME」错误值的原因

当Excel无法识别公式中的文本时，将出现「#NAME」错误值。

比中，本来是要对B2:B9单元格区域进行求和，但是把SUM函数写成了sume函数，Excel无法识别这个函数，所以生成了「#NAME」错误值。

在D列输入了公式，也生成了错误值「#NAME」。

=本月同比&(C2-B2)/B2

在Excel函数公式中，如果要输入文本值，需要用英文双引号括起来。如果没有括起来，Excel会认为这个文本是自定义的公式名称，或是函数名称，结果没找到，就会生成「#NAME」错误值。

生成「#REF!」错误值的原因

#REF!错误值也是一种常见的函数公式返回的错误值，当函数公式中的单元格引用被删除时，将会生成「#REF!」错误值。

其中「#REF!」错误值中的REF是引用的英文缩写（reference），除了删除原有公式中的单元格引用，凡是函数公式中要返回一个无效的单元格引用时，都会生成「#REF!」错误值。

单元格A1:C9只有9行数据，而E2此处的公式是

=INDEX(A1:C9,10,1)

含义是要返回A1:C9单元格区域中的第10行第1列的数据，显然这个是不存在的引用，所以也生成了「#REF!」错误值。

生成「#NULL!」错误值的原因

为了求两个黄色填充单元格区域的交叉重叠部分（即蓝色填充单元格）的和，可以使用下面的公式

=SUM(B2:D7C6:F11)

其中公式中B2:D7和C6:F11之间的空格符是一个单元格区域运算符，用于求出两个单元格区域的交叉部分。

当两个单元格区域没有交叉部分时，函数就将生成「#NULL!」错误值。下面的公式就将生成「#NULL!」错误值。

=SUM(B2:C5D8:F11)

因为B2:C5单元格区域和D8:F11单元格区域之间没有相交重叠的单元格区域。

生成「#NUM!」错误值的原因

若在Excel中输入了函数中不支持的数值参数时，会生成「#NUM!」错误值。

当用DEC2BIN函数将十进制值转化为二进制值时，由于DEC2BIN函数的第2个参数使用了超出可以显示范围的参数，所以生成了「#NUM!」错误值。

生成「#N/A」错误值的原因

#N/A错误值也是一种常见的错误值，如果经常使用VLOOKUP函数，就一定不会陌生！用VLOOKUP函数查找不到要查找的值的对应信息时，就会返回#N/A错误值。

在G2单元格中输入公式

=VLOOKUP(F2,$A$1:$C$8,3,0)

在A列的销售人员中并没有「叔玉」这个人，所以G2单元格生成了「#N/A」错误值。不仅仅是VLOOKUP函数，在查找引用函数中如果某个被查找值无法找到，都将生成「#N/A」错误值。

生成「#VALUE!」错误值的原因

#VALUE!错误值的生成原因有多种，其中最常见的有以下两种↓

a.文本参与了数值运算

C5单元格中计算折后价时，由于工作人员疏忽，在A5单元格价格70中添加了文本「元」，变成了「70元」，Excel将该A5单元格的数据视为文本，文本参与乘法运算，导致生成了「#VALUE!」错误值。

b.输入了一个数组公式，没有按【Shitf+Ctrl+Enter】组合键结束

下面是一个经典的求单列不重复值的公式。

由于开始的公式没有以【Shitf+Ctrl+Enter】组合快捷键结束，所以公式生成了「#VALUE!」错误值。

当换成以【Shitf+Ctrl+Enter】组合快捷键结束公式的输入时，公式才返回正确的值，这个就是数组公式的要点。

生成「#####」错误值的原因

准地说，「#####」错误值并不是函数公式产生的错误值，它是Excel中的一种显示预警。

当单元格中出现「#####」时，一般有以下两个原因：

a.当我们在单元格输入负数，然后将单元格格式显示为日期或者时间格式时，单元格内容会显示「#####」。

这种况的解决办法就是把单元格格式改成常规。

b.当单元格的列宽不足以显示所有单元格内容时，也会出现「#####」错误值。

由于D列的列宽不足以显示D5的公式生成的值，所以显示「####」，当双击调整列宽后即可显示正确的值。

-2-纠正错误值的方法

上面详细地介绍了Excel中8种错误值的生成原因，接下来要告诉大家如何纠正这些错误值。

总原则

所有的错误值都有具体的生成原因，要想避免生成错误值，首要原则就是保证输入的函数名称和函数参数要正确。

比如#NAME错误值的生成原因，主要就是由于输入了Excel无法识别的函数名称或者参数，这时候只需修改函数名称即可。

再比如#REF!错误值需要注意，不要误删除有引用的单元格，另外要引用的单元格必须存在。不能只有10行的单元格区域，想要返回第11行的。

遇到错误值，替换显示原则

有些错误值是不可避免的会生成。

比如VLOOKUP找不到值时，返回的#N/A错误值，再比如算同比时的除数确实为0。

遇到这些不可避免的错误值，我们可以用一个万能函数IFERROR，将结果显示为其它更有意义的值。

IFERROR函数的语法如下

=IFERROR(value,value_if_error)

其中第一个参数value为返回错误值的公式，value_if_error参数为当公式返回错误值时要设置的返回值。

比如上文中的「#DIV/0!」错误值，可以使用如下的公式替代

=IFERROR((C5-B5)/B5,"上期完成数为0")

再比如上文中的#N/A错误值，可以使用如下的公式替代：

=IFERROR(VLOOKUP(F2,$A$1:$C$8,3,0),"没有该成员的销售信息")

即使在输入公式时没有注意，造成了这样的错误也不要慌，相信你看完文章已经对这8个难缠错误的原因和解决办法都了然于心了！

下次，我们也就能从容应对这些错误了~