关于条件统计函数的错误探讨。

kuangben8

    在日常工作中条件统计函数使用的很频繁，从excel2003的sumif、countif到excel2010的sumifs、countifs、averageif、averageifs。给我们的工作带来了很大的便利，但是在最近的一次条件统计时，发现结果错了！
    在工作中需要的数据源记录如下左图所示：
     

    在选择条件  统计分析时如上右图所示，使用条件统计函数得出的结果有错误！探究一下原因，发现是受文本格式的钢包编号的影响！
本来文本格式的钢包编号并不相同！但是在条件统计函数的条件中却认为是相同的！




对比发现有几个共同点：

将文本格式的钢包编号和日期联系起来：该现象在excel2003、excel2007及excel2010中均存在！

1、当钢包编号横线的两边数字都可以转换成正常的日期格式(月-日)时(就是说横线两边的数字都小于等于12)，统计不会出错。比如12-11，11-12,9-12,12-9等。

2、当钢包编号横线的两边数字都不能转换成正常的日期格式(月-日)时(横线两边的数字都大于12)，统计不会出错。比如15-13,13-15等。

3、当钢包编号其中一个数字超过日期中的天数时，也就是不能转换成正常的日期格式，统计也不会出错。

比如10-33,33-10等，编号为2-28和28-2会出错，但是为2-29和29-2就不会出错。

4、当钢包编号可以转换成正常的日期格式且其中一个数字大于12时，统计就会出错！比如9-25,25-9,13-12,12-13,11-13,13-11等。

不仅在统计时会出现错误，在结合其他函数运算时同样会出现错误！例如G列求不重复编号，和F列的结果就不同。

即使在这些文本编号前面添加半角单引号仍然没有作用！

5、另外一个问题：空格("")和空单元格的不同！如13-12,12-13的高铝浇注料统计问题。

不明白为什么不同的钢包编号在条件统计函数的条件位置却认为是相同的！在此想向各位老师讨教学习一下。感谢各位老师的围观和帮助！谢谢。。。


附件： 条件统计函数的错误探讨.zip (29.34 KB, 下载次数: 27)

2013-4-16 15:41 上传

点击文件名下载附件

该贴已经同步到 kuangben8的微博

skyzxh

用文本格式,不要用日期格式

kuangben8

skyzxh 发表于 2013-4-16 17:25
用文本格式,不要用日期格式

没有日期格式，就是因为文本格式出现错误才疑惑的！感谢关注。

kuangben8

虽然使用sum函数或是sumproduct函数可以解决这个问题，但是正常的条件统计函数不应该出现这样的问题才对啊，不知道为什么？

丢丢表格

日期格式作条件是表格的大忌
=SUMIFS(D:D,A:A,K10&"*",B:B,L10,C:C,M10&"")
不是一种好办法，条件中不应有日期格式。

nkgsm

X-Y数字书写形式在excel默认这种形式是日期形式，只有在其合理范围才有效，否则变成文本形式。为了避免这种乱套现象，最好将-号用另外表达形式代替，我将你的替换成\号就结果全部正确了。

kuangben8

丢丢表格 发表于 2013-4-16 20:57
日期格式作条件是表格的大忌
=SUMIFS(D:D,A:A,K10&"*",B:B,L10,C:C,M10&"")
不是一种好办法，条件中不应有 ...

那不是日期格式，是钢包编号！是文本。感谢围观。

丢丢表格

kuangben8 发表于 2013-4-17 11:43
那不是日期格式，是钢包编号！是文本。感谢围观。

俺可不是围观的，是有公式写给的！

chenzicheng

[引子]
在常规格式下，输入11-13与13-11，都会默认为11月13日，即都是一个数值；输入001与1，道理相同。

[推理]
1、条件求和（平均、计数）函数sumif、sumifs在计算时，先进行数据类型转换，再进行IF判断，文本型数字转换为数值型，不能转换的还是文本型；
2、根据[引子]，11-13、13-11会转换为一个相同的数值；15-24、24-15不能转换为数值，就是不同的文本；12-11、11-12都能转换为数值，但是两个不同的数值；
3、归根结底是IF进行转换、判断时，文本型数字转换为数值的问题，不仅是日期，0001、1 作为sumif类函数的条件时也会以出现此问题。

以上只是推理，不代表真相

kuangben8

丢丢表格 发表于 2013-4-17 11:48
俺可不是围观的，是有公式写给的！

哦，感谢关注和给予的帮助！作为文本，编号是不相同的。但是在统计函数的条件位置认为是相同的！我想这个和统计函数的条件位置书写日期需要添加半角双引号有关！