原标题:Excel中的浮点误差,你听说过吗?
Excel之家ExcelHome
小伙伴们好啊,今天老祝和大家分享一个很少有人讲到、但是却又困扰过很多人的Excel小知识。
咱们先来计算一个算式:
4.1-4.2+1=?
这个估计小朋友也会算吧,4.1-4.2是-0.1,然后加上1,就是0.9嘛。
太简单了是不是?咱们在Excel中输入公式看看:
好像没什么问题,不要急。咱们增加小数位,看看会是什么样的结果?
当我们不断增加小数位,计算结果从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、整数和小数混合数值:
含有小数的十进制数转换成二进制时,先将整数、小数部分分别进行转换,然后将转换结果相加。
说完了这些,小伙伴们就明白了,原来还有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选项】对话框中单击【高级】,选中“将精度设为所显示的精度”。
需要注意一点,开启此选项会影响工作簿中的全部工作表,并且无法恢复由此操作所丢失的数据,所以大家在使用的时候一定要慎之又慎哦。
责任编辑: