昨天有同学在用 Excel 做数据统计时偶然发现 Excel 会少算一些数据,而且这个坑让这位同学排查了很久才确认不是自己统计程序错误而只是 Excel 简单的汇总出错。最初看到这个问题时,我也觉得好奇:历史如此悠久、普及率这么高、一流公司的产品都会有这种低级 bug?虽说历史上 Excel 也曾经出过很低级的 bug,但一般很快会被 MS 官方修复,应该属于昙花一现的 bug 才对。(比如 Excel 2007 在正式发布后出过一个著名的“低级” bug:850 x 77.1 = 100000)
那咱们先来看看这个问题(数据和问题我做了简化,方便大家理解):
看以看到 C 列值汇总后并不等于 B 列,也就是 15。
那这是什么问题导致 对 c 列 sum 缺失数据了呢?我们将 C 列复制粘贴到记事本看看:
发现 C9 所在单元格的值非常特殊,带有双引号且换行了,那为啥单元格里却看不到双引号呢?
Windows 系列不是号称 WYSIWYG 的吗?那咱们再来换个角度看下这个问题,
将其保存为 csv 格式,再用 notepad++ 打开,开启上帝视角:
原来是这个单元格里带有换行符 \n,它在 ascii 码表里位列第10,属于不可见字符。
很显然,Excel 在这种情况下解析非常特殊,不知道是否属于bug,将前后的双引号都“吃掉了”,导致界面显示里只看到了数字 2,肉眼难以看出区别。
由于 Excel 没有类似 word 那种文字处理软件显示不可见字符(non-printing)的功能,那咱们只有自己解决了,可以借助 excel 内置的 VBA 写一段代码过滤掉所有的不可见字符,或者用第三方插件来实现,例如 Kutools 和 Ablebits 是两个强大的 Excel 插件,都可以轻松处理这个问题:
这样处理之后,再去 sum 这些单元格之后的值就正常了。
也许有同学会说,这个问题这么简单还用这么大费周折搞什么插件,我一眼就看出那个单元格的数字没对齐,肯定有问题!话虽如此,但那是我简化了业务场景的,真正的实际业务中几百上千行的数字,早就让人眼花缭乱了,不大可能一眼就看出有问题的,而且最可怕的是你不知道你少统计了数据,或者说少统计了哪些数据。那么最后借着本例总结以下几个小 Tips:
做数据的同学其实每天都会遇到类似很诡异的事情,路子广,严谨细致很重要。
[1] 知无涯之回车换行的故事
http://feihu.me/blog/2014/end-of-line/
[2] 回车和换行
http://www.ruanyifeng.com/blog/2006/04/post_213.html
[3] Removing unwanted characters in Excel
https://www.ablebits.com/excel-clean-cells/howto-remove-chars.php
[4] How To Remove Some Special Characters From String In Excel?
https://www.extendoffice.com/documents/excel/3483-excel-remove-special-characters.html