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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏钱曙光的专栏

一周极客热文:Java开发的10位牛人

1983年,Gosling获得了卡尔加里大学的计算机科学学士学位。1990年,他获得了卡内基梅隆大学的计算机科学博士学位,师从Bob Sproull。在攻读博士...

20310
来自专栏web前端教室

不用那么多,每天一点点,学习React,贵在持之以恒

React,应该是目前前端领域最热的框架之一了,对于它的起源,现在我们大家应该都已经比较清楚了,它是fackbook搞出来的开源项目。它要解决的就是前端开发过程...

1979
来自专栏hightopo

基于HTML5的WebGL应用内存泄露分析

1162
来自专栏编程

Immutable.js 到底值不值得用?

导语 我是一个前端开发人员,拥有四年工作经验,目前在一个大型软件团体里工作,制作一个以React框架和Redux库为基础建立起来的新单页程序。 创作一个前所未有...

4545
来自专栏编程之旅

微信小程序——使用setData修改数组中的单个对象

微信小程序已经出来挺久的时间了,之前只是在文档上粗略的看了一下,最近稍得空闲,便利用微信小程序平台写一个练手的项目,顺便学习一下小程序开发,感觉大体跟前端开发基...

2792
来自专栏企鹅号快讯

应用广泛的语言ECMAScript 2018来了,新特性都在这里

原文:What’s new in ECMAScript 2018 作者:Paul Krill 翻译:不二 译者注:ECMAScript是应用广泛的语言,它常常被...

2098
来自专栏IMWeb前端团队

开放-封闭原则(OCP,Open - Closed Priciple)

开放-封闭原则(OCP,Open - Closed Priciple) 1 前言 害羞地看完了《单一职责简述》,自然想到了另外一个重要的原则——开放&封闭原则 ...

2809
来自专栏大数据挖掘DT机器学习

利用d3.js对QQ群资料进行大数据可视化分析

对于前段时间流出的QQ群数据大家想必已经有所了解了,处理后大小将近100G,多达15亿条关系数据(QQ号,群内昵称,群号,群内权限,群内性别和年龄)和将近900...

8127
来自专栏咸鱼不闲

科大讯飞语音识别和语音播放dome

首先登陆科大讯飞开发者平台,注册账号,(走你->http://www.xfyun.cn/) 可以根据功能(语音识别,语音播放等),平台(java,window等...

1K5
来自专栏hightopo

HT图形组件设计之道(三)

1413

扫码关注云+社区

领取腾讯云代金券