第二十九天:EXCEL函数公式常见的计算错误值和处理方法

朋友们，大家好！

在日常工作中，我们经常用EXCEL函数处理表册，函数的运用，大幅度提升了我们的工作效率。笔者集合工作实际，把最常用的EXCEL函数进行逐一详细讲解，课程中有大量案例，为便于朋友们更加深入了解各个函数的用法，将于每天上午7:00同步发布视频教程和图文教程（包含公式）。只要不懈努力和不断实践，通过30天的系统学习，你也能成为EXCEL函数高手，从此告别加班，让同事和朋友刮目相看。

今天，我将和大家一起盘点在使用EXCEL公式时常见的错误值和处理技巧，有效防止错误的再次发生和连续使用。

一、常见的公式计算错误值

（一）“#####”错误和解决方法

出现错误的原因：有时对表格的格式进行调整，并没有对表格中的数据进行编辑，调整格式后却发现有些单元格的数据不见了，取而代之的是“#####”形式的数据。

解决办法：

1.如果整个单元格都是#号填充，通常表示该单元格中的数字、日期和时间超过了第一个的宽度，无法完整显示数据，增加列宽即可解决。

2.当单元格的数据类型和单元格格式不符时，也可能显示“#####”错误，此时可以改变单元格格式，直到正常显示数据。

3.当单元格包含的公式返回无效的日期和时间时，比如产生负值，也会显示“#####”错误，因此，需要确保日期和时间公式的准确性。

（二）“#VALUE!”错误和解决方法

出现错误的原因：当使用的参数或数值类型错误，原因是数值型和非数值型数据进行了四则运算，或者当公式自动更正功能不能更正公式时，也或者是数组公式没有按【Ctrl+Shift+Enter】三键组合结束，都会产生“#VALUE!”错误。

解决办法：确认公式和函数使用的参数、计算对象、类型是否正确，公式引用的单元格中是否包含有效的数值。

（三）“#N/A”错误和解决方法

出现错误的原因：在手动输入公式时，常常会出现“#N/A”错误值，主要是因为公式中没有可用数值，常见的有以下几种情况：

1.源数据缺失：小数组在复制到大区域中时，尺寸不匹配的部分会返回“#N/A”错误值。

2.目标数据缺失：在引用或涉及目标数据的运算时，如果目标数据缺失，就会产生“#N/A”错误值。

3.参数数据缺失：如公式“INDEX(,,)”，由于没有参数，就会返回“#N/A”错误值。

4.数组运算不匹配：当两个对应的数组进行运算时，由于矩阵数目不匹配，也会产生“#N/A”错误值。

解决办法：检查目标数据、源数据、参数等是否填写完整，相互运算的数组尺寸是否相同。

（四）“#NULL!”错误和解决方法

出现错误的原因：公式使用了不相交的两个区域交集，需要注意的是不相交，而不是相交为空。例如：公式“=A1:D1 A3:D3”，两行不相交。

解决办法：预先检查计算区域，避免空值的产生。若要在引用不相交的两个区域，一定要使用联合运算符，即半角逗号“,”。

（五）“#REF!”错误和解决方法

出现错误的原因：

1.引用地址失效：当删除了其他公式所引用的单元格、将已移动的单元格粘贴到其他公式所引用的单元格中或使用了拖动填充控制柄的方法复制公式，但公式中的相对引用成分变成了无效引用。

2.返回无效的单元格：如公式“=OFFSET(B2,-ROW(A2),)”，返回的是单元格B2向上移动2行的单元格的值，指定的是B0单元格，但是该单元格地址不存在。

解决办法：检查被引用的单元格或区域返回参数的值是否存在或有效，对公式进行修改，在删除或粘贴单元格之后恢复工作表中的单元格。

（六）“#NUM!”错误和解决方法

出现错误的原因：在需要数字参数的函数中使用了无法接受的参数。

解决办法：确保函数中使用的参数为正确的数值范围和数值类型。如“=10^400”超出了EXCEL数值大小的限制，属于范围出错，因此，我们在进行计算时，要保持数值的规范，及时需要输入的值是“$800”，也应在公式中输入“800”。

（七）“#DIV/0!”错误和解决方法

出现错误的原因：在EXCEL中，当公式的除数是0时，将会产生错误值“#DIV/0!”。如果参数是一个空白单元格，EXCEL会认定为0，也会产生错误值“#DIV/0!”。

解决办法：将除数更改为非零值，修改单元格引用，或在用作除数的单元格中输入不是零的数值，确保公式中的除数不为零或空。

（八）“#NAME?”错误和解决方法

出现错误的原因：在公式中使用了EXCEL不能识别的文本，产生该错误值的原因较多，具体如下：

1.函数名称拼写错误；

2.某些函数未加载宏，如DATEDIF函数；

3.名称拼写错误；

4.公式中输入文本时没有使用双引号，或者在中文输入法状态下输入了引号“”；

5.单元格地址书写错误，如输入了错误公式“=AVERAGE(A:B6)”；

6.在低版本EXCEL中使用了高版本的公式。

解决办法：确保公式拼写正确，要加载宏，定义正确的名称，删除不受支持的函数，或者替换为受支持的函数。

要确认公式中使用的名称是否存在，可以在“名称管理器”中查看所需的名称有没有被定义。如果公式中引用了其他工作表或工作簿中的值或单元格，且工作簿或工作表的名称中包含非字母字符或空格时，需要将该字符放在单引号“'”中。

二、利用IFERROR和ISERROR函数彻底解决公式错误

（一）IFERROR函数定义

IFERROR函数可以捕获和处理公式中的错误值，如果公式的计算结果为错误，则返回指定的值，否则将返回公式结果。

语法：IFERROR(被检查参数,错误时返回的值)

被检查参数：必需参数，检查是否存在错误的参数，主要检查以下错误类型：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?和#NULL!。

错误时返回的值：必需参数，是指公式计算结果为错误时返回的值。

注意事项：

如果参数为空单元格，则IFERROR会视为空值。

如果值是数组公式，则IFERROR返回值中指定区域内每个单元格的结果数组。

（二）ISERROR函数定义

ISERROR函数用于测试公式返回的数值是否有错误。如果有错误，该函数返回TRUE，反之返回FALSE，通常与IF函数配套使用。

语法：ISERROR(表达式)

表达式：可以是任何有效的表达式。

ISERROR和IFERROR的区别：

两个函数都可以起到容错的作用，同样的效果，使用IFERROR函数可以使公式更简短。

=IF(ISERROR(A1),0,A1)和=IFERROR(A1,)两个公式的结果完全相同，因此，实际中建议多用IFERROR函数解决同类问题。

以上就是我们在EXCEL公式计算时常见的一些错误和解决办法。明天的课程是“EXCEL函数高手成长记”30天系列课程的最后一节课，我将和大家一起总结学习EXCEL函数遇到的一些困惑及技巧，希望大家学有所成，大幅度提高工作效率。

感谢朋友们的支持，如果你有好的意见建议和问题，欢迎在评论区留言交流，期待你的精彩！