• 注册
  • 推广免费拿钻石

    • 查看作者
    • 浮点误差,学Excel函数公式得知道这个坑 图文

      小伙伴们好啊,今天和大家分享一个很少有人讲到、但是却又困扰过很多人的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选项】对话框中单击【高级】,选中“将精度设为所显示的精度”。

      需要注意一点,开启此选项会影响工作簿中的全部工作表,并且无法恢复由此操作所丢失的数据,所以老祝特别提醒大家,在使用的时候一定要慎之又慎哦。

      End.

      运行人员:中国统计网小编

      中国统计网,是国内最早的大数据学习网站

      //www.itongji.cn

    • 0
    • 0
    • 0
    • 2
    • 单栏布局 侧栏位置: