浮点 Excel中的浮点误差，你听说过吗？

优秀的家庭

朋友们，你们好。今天，我想和大家分享一点很少有人谈到的Excel知识，但却困扰了很多人。

我们先算一个公式:

4.1-4.2+1=?

估计孩子也会数，4.1-4.2是-0.1，然后加1，是0.9。

太简单了吧？让我们在Excel中输入公式来看看:

好像没什么问题，不用担心。让我们加上小数位，看看结果会是什么。

当我们不断增加小数位数时，计算结果从0.90变为0.89999。

是我们操作有问题，还是Excel混乱了？

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

这种现象对我们的工作有什么影响？老朱估计，感受最深的是会计。开发票的时候往往会有一分钱的差价。

接下来，我们慢慢聊。先说什么是浮点错误。

说到浮点错误，还有一个比较高级的计算机概念——浮点数。有兴趣的同学可以问问杜娘。简单来说，在计算机计算中，只能存储和处理二进制数据，即1和0。Excel计算时，首先要把十进制值转换成二进制值，交给计算机处理，最后把二进制结果转换成十进制值，在Excel中显示出来。

将十进制值转换为二进制值的计算过程如下:

1.整数部分:

将整数连续除以2得到余数，再除以2直到商等于0，最后将余数逆序排列。

如果十进制值22被转换成二进制值，计算步骤如下:

22除以2得到11，余数为0。

11除以2得到5，余数为1。

5除以2得到2，余数为1。

除以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.整数和十进制混合值:

当包含小数的十进制数转换为二进制数时，整数和小数部分分别转换，然后将转换结果相加。

说了这些，朋友们就明白了，还有1和0。

然而，这种转换有时会陷入无限循环。例如，根据上述方法，将十进制0.6转换为二进制码，计算结果为:

0.10011001100110011……

0011部分会无限重复，不能用空之间的限定量表示。当结果超过Excel的计算精度，一个数字太小无法表达时，Excel中的处理结果为0。

因此，在不同系统之间进行转换时，很容易出现一些非常小的错误，这就是浮点错误。该死，我终于一针见血了...

我们对浮点错误的原因有一个简单的了解，那么如何弥补这个错误呢？

在实践中，我们经常使用两种方法，最常见的一种是使用ROUND函数强制将数字四舍五入。

例如，添加ROUND函数来限制公式的小数位数:

=ROUND(4.1-4.2+1，1)

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

这种舍入属于直接进位，从统计角度来看倾向于大数，导致误差累积。还有一种比较科学的计数保管方法，就是四房六房百分之五十对。

具体来说，有两种情况:保留号码的最后一位小于等于4，大于等于6时进位，等于5后接非零位时进位，等于5后接非零位时进位。

使用这种方法，平均误差趋于零，所以是常用的数字归约规则。

假设单元格A2是要修改的值，公式如下:

=round(a2,2)-(mod(a2*10^3,20)=5)*10^(-2)

另一个简单粗暴的方法是使用“主题显示准确性”选项。使用此选项会强制工作表中每个数字的值都是显示的值。

单击【文件】→【选项】，在【Excel选项】对话框中单击【高级】，选择“设置精度为显示精度”。

需要注意的是，开启此选项会影响到工作簿中的所有工作表，并且此操作丢失的数据无法恢复，所以大家在使用时一定要小心。

当当书香节