深入分析诡异的 Excel 求和统计缺失问题

2019独角兽企业重金招聘Python工程师标准>>>

1、背景

昨天有同学在用 Excel 做数据统计时偶然发现 Excel 会少算一些数据，而且这个坑让这位同学排查了很久才确认不是自己统计程序错误而只是 Excel 简单的汇总出错。最初看到这个问题时，我也觉得好奇：历史如此悠久、普及率这么高、一流公司的产品都会有这种低级 bug？虽说历史上 Excel 也曾经出过很低级的 bug，但一般很快会被 MS 官方修复，应该属于昙花一现的 bug 才对。（比如 Excel 2007 在正式发布后出过一个著名的“低级” bug：850 x 77.1 = 100000）

2、问题

那咱们先来看看这个问题（数据和问题我做了简化，方便大家理解）：

看以看到 C 列值汇总后并不等于 B 列，也就是 15。

那这是什么问题导致 对 c 列 sum 缺失数据了呢？我们将 C 列复制粘贴到记事本看看：

3、说好的所见即所得呢？

发现 C9 所在单元格的值非常特殊，带有双引号且换行了，那为啥单元格里却看不到双引号呢？

Windows 系列不是号称 WYSIWYG 的吗？那咱们再来换个角度看下这个问题，

将其保存为 csv 格式，再用 notepad++ 打开，开启上帝视角：

原来是这个单元格里带有换行符 \n，它在 ascii 码表里位列第10，属于不可见字符。

很显然，Excel 在这种情况下解析非常特殊，不知道是否属于bug，将前后的双引号都“吃掉了”，导致界面显示里只看到了数字 2，肉眼难以看出区别。

4、怎样避开 Excel 这个坑？

由于 Excel 没有类似 word 那种文字处理软件显示不可见字符（non-printing）的功能，那咱们只有自己解决了，可以借助 excel 内置的 VBA 写一段代码过滤掉所有的不可见字符，或者用第三方插件来实现，例如 Kutools  和 Ablebits 是两个强大的 Excel 插件，都可以轻松处理这个问题：

这样处理之后，再去 sum 这些单元格之后的值就正常了。

5、总结

也许有同学会说，这个问题这么简单还用这么大费周折搞什么插件，我一眼就看出那个单元格的数字没对齐，肯定有问题！话虽如此，但那是我简化了业务场景的，真正的实际业务中几百上千行的数字，早就让人眼花缭乱了，不大可能一眼就看出有问题的，而且最可怕的是你不知道你少统计了数据，或者说少统计了哪些数据。那么最后借着本例总结以下几个小 Tips：

• 数字所在单元格如果被格式化成文本或者单元格含有空格/不可见字符，是不会参与计算的；
• 重要的数据在计算前用工具全部格式化一遍，确保都格式化为数字参与了计算，没有漏网之鱼；
• double check，不同技术手段/不同的人验证数据；
• 小心来路不明的数据，例如网页上复制粘贴的数据到Excel一定要小心，很多时候看起来是数字，但其实是是文本字符串，正如本例。

做数据的同学其实每天都会遇到类似很诡异的事情，路子广，严谨细致很重要。

6、Refer：

[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

转载于:https://my.oschina.net/leejun2005/blog/844028