看不见的浮点误差，一不留神就出错

咱们先来计算一个算式：

4.1-4.2+1=？

这个估计小朋友也会算吧，4.1-4.2是-0.1，然后加上1，就是0.9嘛。

太简单了是不是？咱们增加小数位，看看会是什么样的结果？

当我们不断增加小数位，计算结果从0.90变成了0.89999999。

是我们操作有问题，还是Excel犯迷糊了？

其实都不是，这种现象，就是传说中的浮点误差。

那这种现象，对我们的工作有什么影响呢？老祝估计感触最深的应该就是财会人员了，发票做账时，就经常会出现差一分钱的情况。

接下来咱们先说说什么是浮点误差。

简单说，在计算机的计算中，只能存储和处理二进制数据，就是1和0。Excel在计算时，首先要把十进制的数值转换为二进制，交给计算机处理，最后再把二进制的结果转换为十进制，显示到Excel中。

十进制数值转换为二进制数值的计算过程为：

1、整数部分：

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

如果将十进制数值22转换为二进制数值，其计算步骤如下：

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

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

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

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

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

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

2、小数部分：

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

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

首先用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、整数和小数混合数值：

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

但是这种转换有时候会陷入无限循环，比如按照上述方法将小数0.6转换为二进制代码，计算结果就是：

0.10011001100110011……

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

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

咱们简单了解了浮点误差产生的原因，那如何弥补这种误差呢？

实际工作中，大家经常使用的有两种方法，最常用的是使用ROUND函数强制将数字四舍五入。

比如加上ROUND函数来限制公式的小数位数：

=ROUND(4.1-4.2+1,1)

这样公式就会返回保留一位小数的计算结果0.9。

这种四舍五入属于直接进位，从统计学的角度来看会偏向大数，形成误差积累。还有一种比较科学的计数保留方法，就是四舍六入五成双。

具体讲就是保留数字后一位小于等于4时舍去，大于等于6时进位，等于5且后面有非零数字时进位，等于5且后面没有非零数字时分两种情况：保留数字为偶数时舍去，保留数字为奇数时进位。

使用这种方法，误差均值趋向于零，因此在一些有特殊精度要求的计算时比较常用。

假设A2单元格是需要修约的数值，E2是指定修约的位数，公式的写法是：

=IF(ROUND(MOD(ABS(A2*POWER(10,E$2)),2),5)=0.5,ROUNDDOWN(A2,E$2),ROUND(A2,E$2))

公式看起来比较复杂，如果你使用的是Excel 2016或以上版本，还可以借助Power Query功能来完成数据的舍入计算。

单击数据区域任意单元格，在【数据】选项卡下点击【自表格】，根据提示将数据加载到PQ编辑器里。

选中数据所在列，然后点击【转换】→【舍入】→【舍入】，然后指定小数位数，再单击【确定】按钮。在Power Query中的舍入规则，默认就是按四舍六入五成双的规则，不需要咱们特意的设置，操作还是比较简单的。