Excel中的有效数字位数是15？还是17？
这是由一个小Bug引出的问题：Excel中的有效数字位数究竟是15位还是17位？
我们都知道，在Excel中，数字的有效位是是15位，任何超过15位的数字都会变成0。最经典的例子就是随便找一个单元格输入身份证号：
110108198810018951
单元格中就会显示：
而实际上真正的值是：
后面3位早已变成了0。
这是我们都早已经知道的了。所以我们不去违反这个限制。可现在的问题是我们尽管遵守了这个限制，结果仍然是错的。
在单元格B2:B7中的数字都是一样的。它们全部都是15位有效数字，没有其他隐藏的内容了。按说用RANK函数计算排名时，它们全部应该是1才对啊。
问题出在哪里？
我们检查一下，比如使用公式：
=B2=$B$7
发现都跟B7单元相等的。
用条件格式判断下重复值：
发现B7跟其他单元格也是重复的？
问题出在什么地方呢？
这个问题实际上是因为Excel本身存储的并不是15位数字。只不过是显示了15位数字而已。
下面我们来看个究竟：
我们可以将Excel文件添加一个.zip的后缀：
然后用Rar打开这个文件，并且进入xl\worksheets目录下，用记事本打开其中的sheet1.xml文件。
注意看彩色加亮的就是Excel实际存储的单元格的值，仔细数一数，黄色加亮的都是17位的：
0.12345678901234566
四舍五入到15位就是我们看到的值：
0.123456789012346
而最后一个绿色加亮的值：
0.123456789012346
我们知道
0.123456789012346>0.12345678901234566
所以最后一个排名第1，其余的都一样，所以排名第2。
原来如此！
至于为什么Excel显示15位有效数字，而实际存储17位的原因，我猜是因为Excel为了保证这15位有效数字在四舍五入时的准确性。这也没什么问题。毕竟在绝大部分下都没问题，而且Excel的各种计算和函数（例如，if，逻辑函数，条件格式等等）都可以很方便的处理这个问题（即它们只会取前15位来计算）。但是还是有一些漏网之鱼，他们直接比较了17位的数字，这就造成了Bug。现在发现的至少有两个函数有这个问题：RANK和FREQUENCY。后一个是计算频次的函数，相对不常用。RANK可是经常使用的一个函数，所以在使用时需要稍微留心一下是否有15位的问题。
另外，还有一个常用的功能也是用17位来处理的，就是“删除重复项”，这个大家使用的时候已经要注意：
大家看到上图中，Excel把前5个作为重复的对待，删除了4个。而最后一个没有重复。这显然是根据17位的数字进行的判断。
最后还有一个出乎你意料的地方：透视表。如果对我们的这个简单的数据做透视，你会发现，Excel也将前5个作为重复的对待，而最后一个跟他们都不一样：
取得本文模板文件的方式：
关注本公众号
点击底部菜单“联系客服”，与客服取得联系，索取“17位数字”模板文件