看不见的浮点数，0.1+0.2居然都算错-英雄云拓展知识分享

说到浮点误差，有一个比较精深的计算机概念——浮点数，有兴趣的同学可以问度娘。简单说，在计算机的计算中，只能存储和处理2进制数据，就是1和0。Excel在计算时，首先要把10进制的数值转换为2进制，交给计算机处理，最后再把2进制的结果转换为10进制，显示到Excel中。

10进制数值转换为2进制数值的计算进程为：

1、整数部份：

连续用该整数除以2取余数，然后用商再除以2，直到商等于0为止，最后把各个余数按相反的顺序排列。

如果将10进制数值22转换为2进制数值，其计算步骤以下：

22除以2结果为11，余数为0。

11除以2结果为5，余数为1。

5除以2结果为2，余数为1。

2除以2结果为1，余数为0。

1除以2结果为0，余数为1。

最后将余数按相反的顺序排列，整数22的2进制结果为10110。

2、小数部份：

用2乘以10进制小数，将得到的整数部份取出，再用2乘以余下的小数部份，然后再将积的整数部份取出。如此往复，直到积中的小数部份为0或到达所要求的精度为止，最后把取出的整数部份按顺序排列。

如果将10进制数值0.8125转换为2进制数值，其计算步骤是：

首先用0.8125乘以2等于1.625，取整结果为1，小数部份是0.625。

0.625乘以2等于1.25，取整结果为1，小数部份是0.25。

0.25乘以2等于0.5，取整结果为0，小数部份是0.5。

0.5乘以2等于1.0，取整结果为1，小数部份是0，计算结束。

将乘积的取整结果顺序排列，结果就是0.1101。

3、整数和小数混合数值：

含有小数的10进制数转换成2进制时，先将整数、小数部份分别进行转换，然后将转换结果相加。

但是这类转换有时候会堕入无穷循环，比如依照上述方法将小数0.6转换为2进制代码，计算结果就是：

0.10011001100110011……

其中的0011部份会无穷重复，没法用有限的空间量来表示。当结果超越Excel计算精度，产生了一个因太小而没法表示的数字时，在Excel中的处理结果就是0。

所以在不同进制之间转换时，就很容易出现一些非常细小的误差，这就是浮点误差，

Excel里的浮点由于函数机制等缘由，在某些极端情况下隐蔽性很高，所以我再写篇文章介绍下这个坑。

1，计算时产生的大量小数位数

这个结果出现大量小数位数的缘由在之前的文章里已介绍得很详细了，这里不再赘述。

2，没法录入的数据

由于浮点的问题，有些特殊数录入就可以生成浮点，致使没法录入正确内容，32768.598有兴趣你就试试在excel里录入这个数。

但是这两类浮点数，在把小数位数调高后都很容易发现异常，因此略微对Excel有一定了解的火伴都会知道用ROUND函数修正精度，但是，Excel里存在一类更危险的浮点数。

截图为至今为止这类数里至今为止测试下最简单的算式。0.1+0.2，不管如何调高小数位数还是显示为0.3，使用公式求值或F9抹黑算式检查也不会出现大量小数位数，均为0.3。等号判断下和直接键入的0.3也是相同的。

但是，如果你把这个算式用于MATCH等函数内，结果却是报错的……

由于对Excel来讲，浮点精度是可以超过15位的，可以视为高精度的浮点误差

百度后发现JS里0.1+0.2就是典型浮点误差结果为：

0.30000000000000004

由于Excel有15位精度限制，故只显示出0.3，由于15位下剩余小数位数均为0未显示出来，因此，这种情况下，这个性质说明Excel的浮点精度是可以高于15位的。

在实际测试中，发现部份函数能辨认这类高于15位的精度差异，而且这类差异会作用公式结果，这些函数包括：

RANK、FREQUENCY、MATCH、MODE、VLOOKUP、MODE.MULT、HLOOKUP、LOOKUP

有好多同学已被类似0.1+0.2这类不产生大量小数位数的浮点误差坑过，由于使用F9或等号检测时都没法检测出这类高于15位的精度差异。

看到这里肯定有人会问，有无甚么函数能直接辨认这类精度差？

答案是固然有，有个DELTA函数专门判断参数是否是相等的，可以辨认出来，这个函数的结果为1，说明参数完全相等，为0则说明是有差异的：

除使用ROUND修正精度的方式外，有时候我们也能够使用像COUNTIF这类不辨认高于15位的精度的函数来解决这类数值的匹配问题，由于COUNTIF的第2参数在没有没有比较运算符和通配符等时，会将数字全部辨认为数值型统计且不会辨认高于15位的精度。

好了，今天我们的内容就是这些吧，祝各位小火伴一天好心情！

图文制作：流浪铁匠