看不见的浮点数

流浪铁匠

在excel里浮点数是新手最常见的问题之一,论坛里已经有很多老师有所解释                                                
但是关于浮点,有一些性质可能是连老手都不太清楚,故开个帖介绍下浮点数的一些性质                                                
                                                
1,浮点产生原因与常见性质                                                
excel使用的是IEEE754(IEEE二进制浮点数算术标准)存储和计算数据                                                
由于电脑计算时十进制与二进制转化与保留精度等等影响,结果可能出现异常                                                
                                                
常见影响:                                                
1)  计算结果出现大量小数或者变成科学计数法下的非0极小数                                                
例,  =4.1-4 结果在excel里为0.0999999999999996                                                


2)  有些数值无法在excel里直接录入,不然你就试下32768.598                                
(这数字直接录入下会变为32768.5979999999 )                                

流浪铁匠

2,浮点数的处理:                                                
新手容易犯的另一个错误是修改单元格格式的小数位数来处理浮点精度,                                                
但这操作只治标不是本,并没有处理数值精度,                                                
通常建议套嵌round函数解决浮点精度(excel选项里的将精度设置为所显示的精度 这个功能会处理全部数据且无法返回,尽量避免用这功能处理)                                                            
                                                
3,高精度浮点数带来的函数问题                                                
在之前帖子里有介绍，rand函数结果的精度是高于15位的                                                
因此用rand随机产生一个小数并对照手录2次，                                                
再配合其他函数结果对比,大家会发现一个问题                                            部分函数能识别这种高于15位已经无法用调高小数位数发现的精度差异
例如rank,frequency,match,vlookup,mode,lookup等等,都是能识别这种精度差异的

      
看到这里肯定有人会问        
有没有什么函数能直接识别b2和b4为不同数值        
答案是当然有,有个函数 delta 可以识别这种浮点精度  (注意识别超15位精度差异的性质只存在于excel,这函数在wps似乎只识别15位精度)      





借助这个函数,我们还可以发现
聚合函数与四则运算并不能修整这种浮点精度到15位精度

流浪铁匠

4,看不见的浮点                                       
在上面这个案例相信很多熟悉函数的人都相对清楚                                       
在看到数值小数位数过高或者涉及时间的问题时都知道需要修正精度处理                                       
但是,excel里有一类更危险的性质,                                       
目前这类算式里找到的最简单的算式为 =0.1+0.2                                       
这个值无论使用F9,公式求值还是单元格格式调高,结果都是0.3                                       
但是,使用公式 =match(0.1+0.2,{0.2,0.3},0)                                       
结果是报错的


                                       
原因上述已经解释过了,但是这个浮点数很有意思,并没有产生大量小数位数使我们下意识规避                                       
这点在检查数组公式时F9返回计算结果时尤其重要                                       
在群里有些函数高手都被这个性质坑过

因此,在碰到涉及本文提及的函数,以及在确保公式本身无误的前提下公式结果异常,请考虑本文最后提到的这条性质，谢谢                                       

新手路上

以前在EH微信公众号里面就看过前辈的文章，当时好像是100条EXCEL常识，感觉说的很不错，后来才知道前辈是EXCEL发烧友，失敬失敬哈哈

单身深蓝

下载学习，楼主辛苦！！！！

流浪铁匠

更正个细节
早上群里聊浮点发现，有些特定小数位数的值,在round的2参特定位数(原小数位数)下也是无法修正的
造成这批数值无法准确在excel里录入和修正精度
使用"将精度设置为显示的精度"也不能修正

hhwtt

有时间看一下，解释的挺全