为什么数字相加差一分钱，为什么有些0无法显示为-，都是因为这个

一、问题

在《“偷懒”的技术：打造财务Excel达人》读者群里，经常会有朋友问：

  • 为什么对一列数字求和，求和结果不对，总差一分钱；

  • 为什么有些零值在点了按千位分隔符显示后，不是显示短横杠-，总是显示0.00；

  • 我在核对数字时，为什么有些数字明明存在，但用VLOOKUP就是找不到？

比如下面三个示例：

1、两数相减后居然出现很多尾数

2、相减后的零值点击按千位分隔符显示后无法显示为短横杠-

3、导出的银行流水，与明细账核对，两个明明相等的数字却查找不到

这种情况比较少见，我们就是将数字显示很多位（如G11、G12），显示的值还是相等的，比如都是126.17，但是如果我们使用公式=POWER(A4,8)、=POWER(C3,8)让他们变成八次方，就可看出二数字计算结果的差异（见G14、G15单元格）

实际上这些都是由于一个Excel的先天缺陷：浮点计算误差。

大家可能会觉得，Excel这么强大的电子表格软件，居然连一些简单的加减法都计算不正确，

比如：
在单元格输入=42-41.7，计算结果居然不是0.3，而是0.299999999999997。
在单元格输入=(43.1-43.2)+1，计算结果也不是0.9，而是0.899999999999999。
嗯，是的，Excel有时候就是做不了简单的算术。
不要奇怪，也不要莫名惊诧，Excel就是这么任性。
还有更奇怪的，Excel连你手工输入的数字都会弄错。
不信？
你在单元格输入39524.848、55556.848、65535.848看看。
是不是小数点后的848变成了8479999999？

这搞的什么鬼？
这是浮点计算引起的误差！
这很常见，也并不奇怪。
走的夜路多了，自然会碰到鬼，你用久了电子表格，肯定会遇到这种情况。

为什么会这样？

由于Excel是采取二进制存储数字的（在Excel中浮点数分为三个部分，总长度为 65 位：符号、指数和尾数）。而某些十进制的有穷非循环数在二进制下是无穷的循环数，比如0.1。虽然这些数字在以 10 为底的情况下可以完美地表示，但相同数字在二进制格式下，在尾数中存储时就变成了以下二进制循环数字：
000110011001100110011（等类似数字）
Excel在储存这些数字时，它在尾数中只存储能够容纳的部分，并截断其余部分。这导致在存储数字时产生大约 -2.8E-17 或 0.000000000000000028 的误差。
这个道理就象在十进制下无法准确地表示1/3一样，只能用循环小数 0.33333333333333333333来近似表示。

遇到这种情况怎么办？

遇到这种情况，有二种解决方案：
方案一：

给原来的公式穿一层ROUND外套来四舍五入。

=ROUND(B3-C3,2)

=VLOOKUP(ROUND(C2,2),$A$2:$A$8,1,0)

方案二：

使用“将精度设为所显示的精度”选项。

但是此选项要谨慎使用，会影响本工作簿所有的数值。使用后记得切换回来。

－－－－－－－－－－－－－－－－－－－－

赞 (0)

相关推荐