文档章节

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

大数据之路
 大数据之路
发布于 2017/02/22 22:40
字数 973
阅读 1664
收藏 0

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

© 著作权归作者所有

大数据之路
粉丝 1613
博文 514
码字总数 334218
作品 0
武汉
架构师
私信 提问
Excel 如何对多列区域按条件求和?

通常,对单列区域求和,SUMIF 函数就可以搞定。但是,SUMIF 函数只能对单列区域求和,无法满足我们今天的问题。 当然,一个方法用不了,Excel 里总能找到其他方法达到目标。今天我们学习使用...

懒人Excel
2018/06/19
0
0
Excel 统计一列区域中不重复项数量

不重复唯一值,这是在 Excel 里处理数据经常能遇到的问题。例如,统计不重复数量、筛选不重复值、删除重复值、提取重复值等。今天我们来学习,写统计不重复数量的公式。 问题描述 如何统计一...

懒人Excel
2018/05/23
0
0
Excel如何快速统计一列中相同数值出现的个数

最近经常看到论坛和百度知道的朋友提问关于“excel中如何快速统计一列中相同数值出现的个数”,看来这是个普遍问题。所以,笔者单独写一篇经验分享一下该问题的快速解决方案。数据如下图,A...

crazys_蘑菇
2018/06/29
0
0
数据处理神器Pandas的相关函数

最近参加比赛,需要处理各种各样的数据。不得不说,pandas给我带来了很大的方便。困于一直没整理,每一次用到都要查官方文档。现在打算慢慢把用到的函数记录下来,积少成多。 (1)df.value_...

flying_sfeng
2017/07/15
0
0
玩转Excel系列-SUMIFS函数使用及实例

前边的文章我们讲解了SUMIF单条件求和,如果我们需要多条件求和呢,这时间就得使用SUMIFS函数了,我们先看一下该函数的语法 SUMIFS(sumrange, criteriarange1, criteria1, criteriarange2, ...

bjjiefei
2018/06/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

代理模式之JDK动态代理 — “JDK Dynamic Proxy“

动态代理的原理是什么? 所谓的动态代理,他是一个代理机制,代理机制可以看作是对调用目标的一个包装,这样我们对目标代码的调用不是直接发生的,而是通过代理完成,通过代理可以有效的让调...

code-ortaerc
今天
5
0
学习记录(day05-标签操作、属性绑定、语句控制、数据绑定、事件绑定、案例用户登录)

[TOC] 1.1.1标签操作v-text&v-html v-text:会把data中绑定的数据值原样输出。 v-html:会把data中值输出,且会自动解析html代码 <!--可以将指定的内容显示到标签体中--><标签 v-text=""></......

庭前云落
今天
8
0
VMware vSphere的两种RDM磁盘

在VMware vSphere vCenter中创建虚拟机时,可以添加一种叫RDM的磁盘。 RDM - Raw Device Mapping,原始设备映射,那么,RDM磁盘是不是就可以称作为“原始设备映射磁盘”呢?这也是一种可以热...

大别阿郎
今天
12
0
【AngularJS学习笔记】02 小杂烩及学习总结

本文转载于:专业的前端网站☞【AngularJS学习笔记】02 小杂烩及学习总结 表格示例 <div ng-app="myApp" ng-controller="customersCtrl"> <table> <tr ng-repeat="x in names | orderBy ......

前端老手
昨天
16
0
Linux 内核的五大创新

在科技行业,创新这个词几乎和革命一样到处泛滥,所以很难将那些夸张的东西与真正令人振奋的东西区分开来。Linux内核被称为创新,但它又被称为现代计算中最大的奇迹,一个微观世界中的庞然大...

阮鹏
昨天
20
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部