Excel - 为什么两个相等的整数有非零差异？

Question

我有 =2*10^15在一个计算结果为 2,000,000,000,000,000 的单元格中我有 =2+2*10^15在另一个计算结果为 2,000,000,000,000,000 的单元格中，这很好，因为精度只有 15 位。但是当我从另一个中减去一个时，我得到了 2 ，我不明白它来自哪里。据我所知2*10^15有一个精确的二进制表示，所以我会认为在添加 2 时对它来说，它只是“被丢弃”。但似乎我的理解是不正确的。

实际上，据我所知，2E+15 的二进制表示有 1在与 2 的幂相对应的地方等于 {50,49,48,44,43,41,39,38,37,36,35,34,32,30,27,24,23,19,18,16} ，那么为什么我们不能在适当的位置添加另一个位对应电源 1并具有 2+2E+15 的精确表示?

此外，如果不是 2我加 1 , 那么 2*10^15 之间的差异和 1+2*10^15是 0在 Excel 电子表格中，但是如果我在 Excel VBA 中进行相同的计算，那么我会返回 1。那是什么意思？

我已经读过(几次)“每个计算机科学家应该知道的关于浮点算术的知识”，但是那里的讨论似乎集中在小数而不是整数上，或者我就是不明白。

加 1

我记得前段时间我在 VBA 中写了一个函数来获取 Excel 存储的数字的二进制表示(该函数可以在这里找到
Extract mantissa, exponent and sign data from IEEE-754 double in VBA )。因此，如果我将该函数应用于 2 + 2E+15它确实返回带有 1 的二进制文件在尾数中的倒数第 4 位(虽然不知道为什么是第 4 位)，当函数应用于 2E+15 时，它不存在，因此即使底层存储的浮点数具有更高的精度，Excel 似乎也只是选择以 15 位精度显示数字。

加 2

原来2 + 2E+15尾数中多余的位的原因排在第 4 位(而不是第 2 位)的是尾数通过删除前导(最左边)位(因为它始终为 1)并通过将尾数的长度调整为 52 位来将所有内容向左移动添加 2 “简单”二进制表示的末尾为 0，以便1110-0011-0101-1111-1010-1001-0011-0001-1010-0000-0000-0000-010变成1100-0110-1011-1111-0101-0010-0110-0011-0100-0000-0000-0000-1000 .

似乎由于尾数的这种规范化，浮点 double 表示中的精度实际上是 18 位小数，例如，数字 1.75 + 2E+15可以用尾数精确表示1100-0110-1011-1111-0101-0010-0110-0011-0100-0000-0000-0000-0111 .

只需要弄清楚指数发生了什么......有一种叫做“偏置”的东西正在做......

Answer 1

所以，我做了一些研究，因为我对这个问题很感兴趣。虽然我不能完全保证这个答案是绝对正确的，但它很可能是我们最接近正确答案的答案(除非有专家介入)。

我将尝试剖析这个问题:

I have =2*10^15 in one cell which evaluates to 2,000,000,000,000,000 and I have =2+2*10^15 in another cell which also evaluates to 2,000,000,000,000,000, which is fine since the precision is only 15 digits. But when I then subtract one from another, I get back 2, which I dont understand where it came from. As far as I can tell 2*10^15has an exact binary representation, so I would have thought that when adding 2 to it, it just "gets dropped". But it seems that my understanding is incorrect.

我认为在我们继续做任何事情之前，我认为重要的是要声明 Excel (或其他一般的 MS 产品) 众所周知，在浮点精度方面令人印象深刻 .这里必须考虑一些不准确之处。

此外，这实际上是其中显示具有误导性的事情之一。 屏幕上显示的数字实际上与您计算的数字不同。 我的意思是，在您的情况下， 2E+15 和 2E+15 尽管显示相同，但​​不一定是相同的数字! (听起来很奇怪..) 它们只是显示为相同的数字，因为 float 的第 e^th 位置是如此之小，以至于 excel 认为显示它是微不足道的

Furthermore, if instead of 2 I add 1, then the difference between 2*10^15 and 1+2*10^15 is 0 in Excel spreadsheet, but if I do the same calculation in Excel VBA, then I get back 1. What is that about?

这是最难回答的部分。虽然我不能肯定地说，但我的猜测是，这是因为数字 1 的浮点运算末尾的 1 向下舍入为零，而 2 则保持原样。一般来说，这似乎是那些奇怪的 Excel float 事物之一，如果没有专业知识就很难解释，而且这种情况会发生。

I have read (several times) "What Every Computer Scientist Should Know About Floating-Point Arithmetic" but the discussion there seems to be focused on decimals rather than integers or I am just not getting it.

您已经在使用小数(准确地说是 double )。 Excel 中的“数字”格式会根据您的需要自动将您的数字转换为数据类型。 但是，您绝对不能使用整数

整数定义在 -2,147,483,648 到 2,147,483,647 的范围内。这意味着，我们的数字远远超出了最大整数范围。 如果我们将两个数字都转换为整数，则两者都将下限为数字 2,147,483,647，最终得到 0 差 我可以重现的最接近的表示是使用 =FLOOR.MATH()公式，向下舍入到最接近的倍数。

最后但并非最不重要的一点是，了解 excel 中的数学函数是如何计算的很重要。如果你有一个公式，例如。 2+2*10^-15+2计算结果为 4 .为什么？运算符优先级: *优先于 + , 和 ^超过 *
所以 ^ 表示，如果我们计算 2+2*10^15 - 2*10^15首先作为数学公式连接在一起并计算。这将导致 x.......2 - x........0 ，尽管在我们的 excel 显示屏上它会显示为 2E+15 - 2E+15
我创建了下表进行说明:

另请注意，使用 =FLOOR.MATH() 公式，我们将 double 数转换为最接近的倍数 [不是整数!](如果我们使用 =INT()，我们只会收到 #VALUE 超出整数范围的错误)。 之间的区别 =FLOOR.MATH(N1) - FLOOR.MATH(N2) 为 0，尽管 N2 - N1 不是，因为我们使用不同的数字和精度进行计数。 虽然这并不完全等同于使用整数(因为它们不是......显然)，但它们是我能想到的最接近的表示，如果我们用它们进行计算会发生什么。



然而让我感到困惑的一件事是 =FLOOR.MATH() N2 的函数似乎返回 .00十进制，而 N1 没有。尽管如此，两者都应该是完全相同的数字(最接近的倍数)。不确定这是否只是格式故障或任何意义重大的问题。

总而言之，我知道这个答案引发的问题多于答案，但希望其他人能够很好地回答这个问题，并且至少回答了你的一些问题，如果不是全部的话.