ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 59416|回复: 68

[分享] 把电脑中的浮点运算误差找出来 - 任何人的电脑都会有这个误差的

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2010-10-12 23:40 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:数学运算
很多人在日常使用Excel,可能都没有注意到浮点小数带来的计算误差问题

因为日常使用的运算结果,肯定都是四舍五入之后的结果,可以排除浮点误差的影响

但是,浮点小数的误差,也会出现在运算的过程,有可能导致最终结果出错。

比方说 countif(A1:A10000,"=B1")这样的简单统计,也有可能出错。

附件生成5万个随机小数(四舍五入到2位小数位),加上然后马上减去同一个整数

对比计算前后的数值是否相等(数学上当然是绝对相等的,但是电脑的运算结果就不一定了。。。。。。)

附件会自动找出一批在你的电脑上会出现浮点小数误差导致错误的运算。

出现浮点小数运算误差的原因很简单,小数有无穷多个

甚至一个小数也有无穷多位,如十进制的1/3,或者二进制的0.1

所以计算机存储小数的时候,必定只能存储所有小数的一部分,而无穷小数则必须截断

因此,一个小数有时候就只能取一个接近值而不是精确值

而对应的浮点数运算,也只能得到接近值,而不是精确值

不同的CPU,比方说Intel的跟AMD的,或者奔腾跟赛扬,在硬件上对于截断的位置是不一样的

所以,同一个运算在不同的机器就可能有不同的结果

这也是为什么论坛经常有人说xx运算不正确,然后回帖的人说我的机器没事,其实就是CPU不同而已

另一方面,不同的软件,内部计算的时候,也许用了不同的CPU硬件实现的数据类型,或者精度设置也不一样,

所以,同一个运算在不同的软件也可能有不同的结果

编程,写公式要注意,假设 x,y 都是浮点数,er。。。。。。excel的每个小数单元格都是浮点数

那么永远不要写 if x=y 这样的句子,因为很可能数学上相等的x和y,在计算机内部是不等的

应该写成 if |x-y| / max(|x|,|y|) <= 0.0000000000001 之类

或者简单一点,用if round(x) = round(y) 来判断,这样最末一位的尾数浮点存储误差就不会影响整个数值了。

幸运的是,整数和定点小数不会受影响,编程时应该优先考虑使用整数和定点小数类型

另一方面,即使浮点小数可以完全表示我们要计算的数值,但是计算结果却也有可能产生误差

浮点,定点的意思是指小数点的位置

定点小数:小数点位置固定。

以十进制为例,假设一个数值类型可以有6个十进制位置,那么定点类型无须记录小数点位置,可以有以下类型:

整数类型,小数点位置就一定是个位后面,能够表达的最精确数字就是1,能够表达的范围是 000000 - 999999

两位小数类型,小数点位置就一定是百分位,能够表达的最精确数字就是0.01,,能够表达的范围是 0000.00 - 9999.99
。。。。。。等等

浮点小数:小数点位置可以变动,并且需要额外的记录去描述这个变动的小数点位置。

假设这里把其中一个位置用于记录小数点位置(可变范围 0-9),并且规定:

小数点位置取5,表示精度是1,小数点在个位后面,这个数值类型有0个小数位,可以表示 00000 到 99999 的数 (注意:这里只有5位有效数字)

小数点位置取6,表示精度是 0.1,小数点在十分位后面,这个数值类型有1个小数位,可以表示 0000.0 到 9999.9 的数

小数点位置取9,表示精度是 0.0001,小数点在万分位后面,这个数值类型有4个小数位,可以表示 0.0000 到 9.9999 的数
......
小数点位置取4,表示精度是 10,表示小数点在十位,这个数值类型可以表示 00000 x 10 到 999990 的数,精度是 10
......
小数点位置取0,表示精度是 100000,表示小数点在十万位,这个数值类型可以表示 00000 x 100000 到 9999900000 的数,精度是 100000

可见浮点小数通过可变的小数位置,使一个数值类型可以用一部分表示有效数字,另一部分表述每一单位有效数字的倍率

从而灵活地表示很大范围的数值,代价是:由于用了一部分记录内容去记录小数点位置,所以有效数字的长度比定点小数要低。

而且,一些定点数运算不会出现的问题,在浮点数运算会出现

如两个数相加
如果第一个浮点数是 100.01,第二个浮点数是 9999900000

按上述规则,这两个都是合法的浮点数,可是它们的和 99999000100.01 却无法用同一个浮点数类型表示出来。

一般计算机碰到这种情况,就会自动按比较大的数值来四舍五入,于是

100.01 + 9999900000 = 9999900000 (出现了100.01的误差)

这也是浮点数误差产生的一个原因:小的数值被大的数值“吸收”了。

要在Excel证明这一点就太简单了,因为excel的double类型浮点小数只有15个有效数字(即最多记录15个十进制数字)

所以Excel的单元格运算 12345012345678900000 + 12345.0123456789 = 12345012345678900000

更多的浮点小数知识,可以搜索论坛或者网络。
http://support.microsoft.com/kb/78113

附件按xiangzi728版主 13楼的意见加了两句

[ 本帖最后由 灰袍法师 于 2011-6-8 22:49 编辑 ]

找一找 - 电脑的浮点误差出现在哪里.rar

11.25 KB, 下载次数: 1432

评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-10-13 00:17 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-10-13 00:54 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-10-13 02:32 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
原帖由 hfyk007 于 2010-10-13 00:54 发表
出现了两个错误!!以前没注意过啊


才两个吖,远远不止的,实际上应该是无穷多个,只不过程序无法遍历所有的计算数字组合而已

顶楼附件已经更新,可以在第二个工作表直接看到那些数值是一定出错的。

我发现最搞笑的是:Excel单元格公式认为是不相等的两个数,在VBA程序内部就有可能被认为是相等的。

[ 本帖最后由 灰袍法师 于 2010-10-13 02:36 编辑 ]

TA的精华主题

TA的得分主题

发表于 2011-1-22 21:35 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
正好遇到了这个问题的困扰,谢谢楼主讲解

TA的精华主题

TA的得分主题

发表于 2011-3-10 20:31 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-3-18 09:43 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-3-18 19:31 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-3-29 15:31 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
感谢LZ的发帖啊,这个问题真的很麻烦,现在看了可以知道点解决方法了。

TA的精华主题

TA的得分主题

发表于 2011-6-8 08:40 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 20:04 , Processed in 0.051834 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表