这是学员发来的一份表格,左边是产品全称,要统计右边简称对应的总数量。
粗略的看了一眼,误以为好简单,1分钟就能搞定,可是实际的操作过程出现了一堆奇怪、让人无法解释的现象。
如果8888ZZ-XE没有空格的话,那就是最基本的SUMIF加通配符的用法。
=SUMIF(A:A,"*"&D2&"*",B:B)
现在有的含有空格,有的没有,按照这个思路,应该将空格替换掉就可以。
可是当用函数替换空格的时候,发现左边压根替换不了,那个不是普通的空格。
=SUBSTITUTE(A2," ",)
复制特殊空格,发现只能替换这一个,其他空格是替换不了。公式看起来一样,实际是不一样。
=SUBSTITUTE(A2," ",)
将2个公式合并起来,替换掉普通空格和特殊空格。
=SUBSTITUTE(SUBSTITUTE(A3," ",)," ",)
右边也用同样的方法处理。
=SUBSTITUTE(SUBSTITUTE(D2," ",)," ",)
现在两边都有辅助列,剩下的跟原来一样用SUMIF+通配符。截图的结果是对的,刚开始操作的时候100没统计进去。
=SUMIF(C:C,"*"&F2&"*",B:B)
但奇怪的事就发生了,第一行无法统计进去。于是,我重启了好几次Excel发现还是不行。
于是,我发给张哥测试,发现也不行。
一头雾水,换了SUMPRODUCT统计,发现结果是对的。
=SUMPRODUCT((ISNUMBER(FIND(F2,$C$2:$C$6)))*$B$2:$B$6)
最后,我将这奇怪的一幕写成文章,原来的SUMIF统计结果奇迹般又对了。
用了十几年Excel,第一次被Excel玩的团团转,而且还找不到原因。公式对的,结果却是错,错了也就错了,过了1个小时居然又对了,苦笑一声。。。
推荐:写了10多年公式,第一次遇到这种奇葩错误,你能遇到算我输!
你是否也有类似的经历,本来Excel出问题,突然间就好了。
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服