[已解决]请问各位老师，是否有公式错误及解决方法的资料，谢谢！

wengrobert

请问各位老师，是否有公式错误及解决方法的资料，谢谢！

最佳答案

月排行榜 / 总排行榜

wjh619993018

2010-5-28 11:02

目前没发现有人专门整理这方面的资料，有一个和错误值相关的，帖上来看一下吧

一、数据运算结果错误
（1）显性错误
出现显性错误时，存在公式的单元格会出现错误的信息。下面列举错误信息的错误原因和解决方法：
1.＃＃＃＃＃
错误原因：输入到单元格中的数值太长或公式产生的结果太长，单元格容纳不下。
解决方法：适当增加列宽度。
2.＃DIV/0!
错误原因：公式被0(零)除。
解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。
3.＃N/A
错误原因：当在函数或公式中没有可用的数值时，将产生错误值#N/A。
解决方法：如果工作表中某些单元格暂时没有数值，在这些单元格中输入#N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。
4.＃NAME？
错误原因：在公式中使用了MicrosoftExcel不能识别的文本。
解决方法：确认使用的名称确实存在。如果所需的名称没有被列出，添加相应的名称。如果名称存在拼写错误，修改拼写错误。
5.＃NULL！
错误原因：试图为两个并不相交的区域指定交叉点。
解决方法：如果要引用两个不相交的区域，使用联合运算符(逗号)。
6.＃NUM！
错误原因：公式或函数中某些数字有问题。
解决方法：检查数字是否超出限定区域，确认函数中使用的参数类型是否正确。
7.＃REF！
错误原因：单元格引用无效。
解决方法：更改公式。在删除或粘贴单元格之后，立即单击[撤消]按钮以恢复工作表中的单元格。
8.＃VALUE！
错误原因：使用错误的参数或运算对象类型，或自动更改公式功能不能更正公式。
解决方法：确认公式或函数所需的参数或运算符是否正确，并且确认公式引用的单元格所包含均为有效的数值。
注意：2～8的错误处理可以使用If和信息函数（ERROR.TYPE、ISERR、ISERROR和ISNA等）处理，例如=IF(ERROR.TYPE(A1)=2,"A1除数为0",A1)。
（2）隐性错误
数据的运算结果产生错误，而在单元格不显示错误信息。
1公式运用错误
公式运用错误的原因是对功能相近或函数名称相似的公式的运用错误。
① 带A的函数与不带A的函数
例如COUNT和COUNTA：
COUNT函数
返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。函数COUNT在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。
COUNTA函数
返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。参数值可以是任何类型，它们可以包括空字符("")，但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。
可见COUNT与COUNTA的区别在于错误值或其他无法转换成数字的文字是否需要忽略，当应该忽略的时候用到COUNTA就产生无效性了。
示例：
=COUNT({1,2,3,"a1","a2","",TRUE})结果为3，而=COUNTA({1,2,3,"a1","a2","",TRUE})结果为7。
类似的函数AVERAGE与AVERAGEA、MAX与MAXA、MIN与MINA、STDEV与STDEVA、STDEVP与STDEVPA、VAR与VARA、VARP与VARAP。它们的共同特征是处理的范围不同，大家可以自行查阅Excel的帮助文件。
②带B的函数与不带B的函数
例如FIND与、FINDB：
FIND函数
FIND用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从within_text的首字符开始返回find_text的起始位置编号。
FINDB函数
用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并基于每个字符所使用的字节数从within_text的首字符开始返回find_text的起始位置编号。此函数用于双字节字符。
示例：
在以下示例中，FIND返回2，而FINDB返回3，因为每个字符均按字节进行计数，而第一个字符包含2个字节，所以第二个字符从第三个字节开始。
=FIND("术","技术联盟")等于2
=FINDB("术","技术联盟")等于3
类似的函数还有LEFT与LEFTB、LEN与LENB、RIGHT与RIGHTB、REPLACE与REPLACEB、SEARCH与SEARCHB、FIND与、FINDB，大家可以自行查阅Excel的帮助文件。在编辑、修改、计算工作簿数据时，经常会用到许多汉字字符，如果使用以上带字母B的函数对汉字字符进行操作，就会很方便。
③函数的名称类似：
例如SUMX2MY2与SUMX2PY2
SUMX2MY2函数
返回两数组中对应数值的平方差之和。
SUMX2PY2函数
返回两数组中对应数值的平方和之和，平方和之和总在统计计算中经常使用。
=SUMX2MY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})等于-55
=SUMX2PY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})等于-521
类似函数ASIN与ASINH、ACOS与ACOSH、ATAN与ATAN2与ATANH、LN与LNT、RANK与RAND、DB与DDB等。这类函数“一字之差，缪之千里”，但只要认真对待书写过程就完全可以避免。
2 引用定义的名称时发生的错误
定义名称使公式本身的可阅读性明显加强，但名称内部的公式不能直接通过工作表显示。如果公式运算得错误的结果，内部的公式问题往往容易被忽视。
例如：
题意：利用定义名称使B列每格与A格的数值一一对应。
正确的方法：选择B1后，定义名称“data”为=A1，工作表B1输入=data，再下拉填充柄到B5。
容易忽视的问题：
1 选择其他的单元格，而不是B1定义了名称。如：选择C1后，定义名称“data”为=A1，工作表B1输入=data，实际上B1引用的是IV1，而非A1。
2 名称定义时，忽视了引用方式。如选择B1后，定义名称“data”为=$A$1，工作表B1输入=data，再下拉填充柄到B5。结果所有的B1到B5都等于A1。
定义的名称时容易发生的，所以要调试函数时候要多长个心眼。这种情况同样会发生在利用“数据有效性”制作下来列表用时（数据源与下拉所在表不是同一个工作表需要利用名称），也值得关注。
（3）运算的误差
Excel的运算精度是15位，超过这个范围的计算就不精确了。如：111111111111111111111+111111111111111111111应该等于222222222222222222222，但Excel中就成了111111111111111000000+111111111111111000000等于222222222222222000000，少了222222，这个问题归属于误差问题，准确地讲并不是真正的错误问题。该问题一般不会对运算结果产生大的负面影响，然而这个小小的负面影响也会产生不良的后果。
解决方法：
1 将输入数值的改为文本型数值（即首先修改单元格格式，再输入数字），这样首先避免了输入时不精确的问题；
2 运算的精确就需要使用VBA模拟“自然运算法”。所谓的“自然运算法”就是我们用纸和笔的情况下运算的方法。“自然运算法”虽然比较笨拙，但行之有效。下面例子是模拟零或正整数相加的 “自然运算法”的自定义函数的代码：
“自然运算法”示例

子过程            IntegerSum
' 目的
'                   计算超长的零和正整数加法
'
' 参数
'       sAddend        (String) 加数
'       sSummand       [String) 被加数.
'
' 返回值
'       (String)
'       IntegerSum        返回两个超长的零和正整数相加的值

跳转到最佳答案楼层

蝶·舞

公式错误的话，那就重写啊

木文

来个附件.

wengrobert

我问的是如何通过公式审核的方法知道哪里错了？

dafuma

还是那具体例子说明比较好

wjh619993018

目前没发现有人专门整理这方面的资料，有一个和错误值相关的，帖上来看一下吧

一、数据运算结果错误
（1）显性错误
出现显性错误时，存在公式的单元格会出现错误的信息。下面列举错误信息的错误原因和解决方法：
1.＃＃＃＃＃
错误原因：输入到单元格中的数值太长或公式产生的结果太长，单元格容纳不下。
解决方法：适当增加列宽度。
2.＃DIV/0!
错误原因：公式被0(零)除。
解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。
3.＃N/A
错误原因：当在函数或公式中没有可用的数值时，将产生错误值#N/A。
解决方法：如果工作表中某些单元格暂时没有数值，在这些单元格中输入#N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。
4.＃NAME？
错误原因：在公式中使用了MicrosoftExcel不能识别的文本。
解决方法：确认使用的名称确实存在。如果所需的名称没有被列出，添加相应的名称。如果名称存在拼写错误，修改拼写错误。
5.＃NULL！
错误原因：试图为两个并不相交的区域指定交叉点。
解决方法：如果要引用两个不相交的区域，使用联合运算符(逗号)。
6.＃NUM！
错误原因：公式或函数中某些数字有问题。
解决方法：检查数字是否超出限定区域，确认函数中使用的参数类型是否正确。
7.＃REF！
错误原因：单元格引用无效。
解决方法：更改公式。在删除或粘贴单元格之后，立即单击[撤消]按钮以恢复工作表中的单元格。
8.＃VALUE！
错误原因：使用错误的参数或运算对象类型，或自动更改公式功能不能更正公式。
解决方法：确认公式或函数所需的参数或运算符是否正确，并且确认公式引用的单元格所包含均为有效的数值。
注意：2～8的错误处理可以使用If和信息函数（ERROR.TYPE、ISERR、ISERROR和ISNA等）处理，例如=IF(ERROR.TYPE(A1)=2,"A1除数为0",A1)。
（2）隐性错误
数据的运算结果产生错误，而在单元格不显示错误信息。
1公式运用错误
公式运用错误的原因是对功能相近或函数名称相似的公式的运用错误。
① 带A的函数与不带A的函数
例如COUNT和COUNTA：
COUNT函数
返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。函数COUNT在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。
COUNTA函数
返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。参数值可以是任何类型，它们可以包括空字符("")，但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。
可见COUNT与COUNTA的区别在于错误值或其他无法转换成数字的文字是否需要忽略，当应该忽略的时候用到COUNTA就产生无效性了。
示例：
=COUNT({1,2,3,"a1","a2","",TRUE})结果为3，而=COUNTA({1,2,3,"a1","a2","",TRUE})结果为7。
类似的函数AVERAGE与AVERAGEA、MAX与MAXA、MIN与MINA、STDEV与STDEVA、STDEVP与STDEVPA、VAR与VARA、VARP与VARAP。它们的共同特征是处理的范围不同，大家可以自行查阅Excel的帮助文件。
②带B的函数与不带B的函数
例如FIND与、FINDB：
FIND函数
FIND用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从within_text的首字符开始返回find_text的起始位置编号。
FINDB函数
用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并基于每个字符所使用的字节数从within_text的首字符开始返回find_text的起始位置编号。此函数用于双字节字符。
示例：
在以下示例中，FIND返回2，而FINDB返回3，因为每个字符均按字节进行计数，而第一个字符包含2个字节，所以第二个字符从第三个字节开始。
=FIND("术","技术联盟")等于2
=FINDB("术","技术联盟")等于3
类似的函数还有LEFT与LEFTB、LEN与LENB、RIGHT与RIGHTB、REPLACE与REPLACEB、SEARCH与SEARCHB、FIND与、FINDB，大家可以自行查阅Excel的帮助文件。在编辑、修改、计算工作簿数据时，经常会用到许多汉字字符，如果使用以上带字母B的函数对汉字字符进行操作，就会很方便。
③函数的名称类似：
例如SUMX2MY2与SUMX2PY2
SUMX2MY2函数
返回两数组中对应数值的平方差之和。
SUMX2PY2函数
返回两数组中对应数值的平方和之和，平方和之和总在统计计算中经常使用。
=SUMX2MY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})等于-55
=SUMX2PY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})等于-521
类似函数ASIN与ASINH、ACOS与ACOSH、ATAN与ATAN2与ATANH、LN与LNT、RANK与RAND、DB与DDB等。这类函数“一字之差，缪之千里”，但只要认真对待书写过程就完全可以避免。
2 引用定义的名称时发生的错误
定义名称使公式本身的可阅读性明显加强，但名称内部的公式不能直接通过工作表显示。如果公式运算得错误的结果，内部的公式问题往往容易被忽视。
例如：
题意：利用定义名称使B列每格与A格的数值一一对应。
正确的方法：选择B1后，定义名称“data”为=A1，工作表B1输入=data，再下拉填充柄到B5。
容易忽视的问题：
1 选择其他的单元格，而不是B1定义了名称。如：选择C1后，定义名称“data”为=A1，工作表B1输入=data，实际上B1引用的是IV1，而非A1。
2 名称定义时，忽视了引用方式。如选择B1后，定义名称“data”为=$A$1，工作表B1输入=data，再下拉填充柄到B5。结果所有的B1到B5都等于A1。
定义的名称时容易发生的，所以要调试函数时候要多长个心眼。这种情况同样会发生在利用“数据有效性”制作下来列表用时（数据源与下拉所在表不是同一个工作表需要利用名称），也值得关注。
（3）运算的误差
Excel的运算精度是15位，超过这个范围的计算就不精确了。如：111111111111111111111+111111111111111111111应该等于222222222222222222222，但Excel中就成了111111111111111000000+111111111111111000000等于222222222222222000000，少了222222，这个问题归属于误差问题，准确地讲并不是真正的错误问题。该问题一般不会对运算结果产生大的负面影响，然而这个小小的负面影响也会产生不良的后果。
解决方法：
1 将输入数值的改为文本型数值（即首先修改单元格格式，再输入数字），这样首先避免了输入时不精确的问题；
2 运算的精确就需要使用VBA模拟“自然运算法”。所谓的“自然运算法”就是我们用纸和笔的情况下运算的方法。“自然运算法”虽然比较笨拙，但行之有效。下面例子是模拟零或正整数相加的 “自然运算法”的自定义函数的代码：
“自然运算法”示例

子过程            IntegerSum
' 目的
'                   计算超长的零和正整数加法
'
' 参数
'       sAddend        (String) 加数
'       sSummand       [String) 被加数.
'
' 返回值
'       (String)
'       IntegerSum        返回两个超长的零和正整数相加的值

MissSun

[em07]跟着最佳答案学习