Excel 如何成功地对不精确的浮点数进行舍入？

Question

例如，此博客 说 0.005 并不完全是 0.005，但对该数字进行四舍五入会产生正确的结果。

我已经尝试过 C++ 中的各种舍入，但在将数字舍入到某些小数位时失败。例如，Round(x,y) 将 x 四舍五入为 y 的倍数。所以 Round(37.785,0.01) 应该给你 37.79 而不是 37.78。

我重新提出这个问题是为了向社区寻求帮助。问题在于浮点数的不精确性（37,785 表示为 37.78499999999）。

问题是Excel如何解决这个问题呢？

对于上述问题，round() for float in C++ 中的解决方案是不正确的。

Answer 1

“Round(37.785,0.01) 应该给你 37.79 而不是 37.78。”

首先，对于 37.79 而不是 37.78 才是“正确”答案没有达成共识？决胜局总是有点艰难。虽然平局时总是向上舍入是一种广泛使用的方法，但它肯定不是唯一的方法。

其次，这不是打破平局的情况。 IEEE 二进制 64 浮点格式的数值为 37.784999999999997（大约）。除了人工输入 37.785 值并碰巧将其转换为浮点表示形式之外，还有很多方法可以获得 37.784999999999997 的值。在大多数情况下，正确答案是 37.78，而不是 37.79。

附录
考虑以下 Excel 公式：

=ROUND(37785/1000,2)
=ROUND(19810222/2^19+21474836/2^47,2)

两个单元格将显示相同的值 37.79。关于 37785/1000 是否应该以两位精度舍入为 37.78 还是 37.79，存在一个合理的争论。如何处理这些极端情况有点武断，并且没有一致的答案。 Microsoft 内部甚至没有达成共识的答案：“由于历史原因，Round() 函数在不同的 Microsoft 产品中并未以一致的方式实现。”( http://support.microsoft.com/kb/196652 ) 给定无限精度的机器，Microsoft 的 VBA 会将 37.785 舍入为37.78（银行家回合），而 Excel 将产生 37.79（对称算术回合）。

对于后一个公式的舍入没有争议。它严格小于 37.785，因此应四舍五入为 37.78，而不是 37.79。但 Excel 将其四舍五入。为什么？

原因与实数在计算机中的表示方式有关。与许多其他公司一样，Microsoft 使用 IEEE 64 位浮点格式。数字 37785/1000 在以这种格式表示时会出现精度损失。 19810222/2^19+21474836/2^47 不会出现这种精度损失；这是一个“确切的数字”。

我特意构造了该精确数字，使其具有与不精确的 37785/1000 相同的浮点表示形式。 Excel 向上而不是向下舍入这个精确值是确定 Excel 的 ROUND() 函数如何工作的关键：它是对称算术舍入的变体。它基于与极端情况的浮点表示的比较进行舍入。

C++ 中的算法：

#include <cmath> // std::floor

// Compute 10 to some positive integral power.
// Dealing with overflow (exponent > 308) is an exercise left to the reader.
double pow10 (unsigned int exponent) { 
   double result = 1.0;
   double base = 10.0;
   while (exponent > 0) {
      if ((exponent & 1) != 0) result *= base;
      exponent >>= 1;
      base *= base;
   }
   return result;
}   

// Round the same way Excel does.
// Dealing with nonsense such as nplaces=400 is an exercise left to the reader.
double excel_round (double x, int nplaces) {
   bool is_neg = false;

   // Excel uses symmetric arithmetic round: Round away from zero.
   // The algorithm will be easier if we only deal with positive numbers.
   if (x < 0.0) {
      is_neg = true;
      x = -x; 
   }

   // Construct the nearest rounded values and the nasty corner case.
   // Note: We really do not want an optimizing compiler to put the corner
   // case in an extended double precision register. Hence the volatile.
   double round_down, round_up;
   volatile double corner_case;
   if (nplaces < 0) {
      double scale = pow10 (-nplaces);
      round_down  = std::floor (x * scale);
      corner_case = (round_down + 0.5) / scale;
      round_up    = (round_down + 1.0) / scale;
      round_down /= scale;
   }
   else {
      double scale = pow10 (nplaces);
      round_down  = std::floor (x / scale);
      corner_case = (round_down + 0.5) * scale;
      round_up    = (round_down + 1.0) * scale;
      round_down *= scale;
   }

   // Round by comparing to the corner case.
   x = (x < corner_case) ? round_down : round_up;

   // Correct the sign if needed.
   if (is_neg) x = -x; 

   return x;
}   

Answer 2

为了获得非常准确的任意精度并将浮点数舍入到一组固定的小数位，您应该查看 数学库就像 GNU MPFR 一样。虽然它是一个 C 库，但如果您想避免使用 C，我发布的网页还链接到几个不同的 C++ 绑定。

您可能还想阅读一篇题为 "每个计算机科学家应该了解的浮点知识施乐帕洛阿尔托研究中心的大卫·戈德堡（David Goldberg）的算术”。这是一篇优秀的文章，演示了允许在表示二进制数据中的所有内容的计算机中近似浮点数的基本过程，以及舍入误差和其他问题如何在基于 FPU 的浮点数学中蔓延。

Answer 3

我不知道Excel是如何做到的，但是很好地打印浮点数是一个难题： http://www.serpentine.com/blog/2011/06/29/here-be-dragons-advances-in-problems-you-didnt-even-know-you-had/

Answer 4

所以你的实际问题似乎是，如何获得正确舍入的浮点数 ->字符串转换。通过谷歌搜索这些术语，您会得到一堆文章，但如果您对使用的东西感兴趣，大多数平台都提供了 sprintf()/snprintf() 的相当有效的实现。因此，只需使用这些，如果发现错误，请向供应商提交报告。

Answer 5

无法编写以浮点数作为参数并返回另一个浮点数（精确舍入到给定十进制位数）的函数，因为许多具有有限十进制表示形式的数字具有无限二进制表示形式；最简单的例子之一是 0.1 。

为了实现您想要的效果，您必须接受使用不同的类型作为舍入函数的结果。如果您迫切需要打印数字，您可以使用字符串和格式化函数：问题变成如何准确获得您期望的格式。否则，如果您需要存储此数字以便对其执行精确计算，例如如果您正在进行会计，则需要一个能够准确表示十进制数字的库。在这种情况下，最常见的方法是使用缩放表示：值的整数加上小数位数。将值除以十，得到原始数字。

如果这些方法中的任何一个合适，我将尝试通过实用的建议来扩展我的答案。

Answer 6

我相信以下 C# 代码对数字进行四舍五入，就像在 Excel 中对数字进行四舍五入一样。要准确复制 C++ 中的行为，您可能需要使用特殊的十进制类型。

用简单的英语来说，双精度数被转换为小数，然后四舍五入到十五位有效数字（不要与十五位小数混淆）。结果再次四舍五入到指定的小数位数。

这可能看起来很奇怪，但您必须了解的是，Excel始终显示四舍五入到 15 位有效数字的数字。如果 ROUND() 函数不使用该显示值作为起点，而是使用内部双精度表示形式，则可能会出现 ROUND(A1,N) 似乎与 A1 中的实际值不对应的情况。这对于非技术用户来说会非常困惑。

最接近 37.785 的双精度数的精确十进制值为 37.784999999999996589394868351519107818603515625。 （任何双精度数都可以用有限的十进制小数精确表示，因为四分之一、八分之一、十六分之一等都具有有限的小数扩展。）如果该数字直接四舍五入到小数点后两位，则与打破，结果将是 37.78。如果先四舍五入到 15 位有效数字，您将得到 37.7850000000000。如果进一步四舍五入到小数点后两位，则得到 37.79，所以这根本就没有什么神秘之处。

    // Convert to a floating decimal point number, round to fifteen 
    // significant digits, and then round to the number of places
    // indicated.
    static decimal SmartRoundDouble(double input, int places)
    {
        int numLeadingDigits = (int)Math.Log10(Math.Abs(input)) + 1;

        decimal inputDec = GetAccurateDecimal(input);

        inputDec = MoveDecimalPointRight(inputDec, -numLeadingDigits);

        decimal round1 = Math.Round(inputDec, 15);

        round1 = MoveDecimalPointRight(round1, numLeadingDigits);

        decimal round2 = Math.Round(round1, places, MidpointRounding.AwayFromZero);

        return round2;
    }

    static decimal MoveDecimalPointRight(decimal d, int n)
    {
        if (n > 0)
            for (int i = 0; i < n; i++)
                d *= 10.0m;
        else
            for (int i = 0; i > n; i--)
                d /= 10.0m;

        return d;
    }

    // The constructor for decimal that accepts a double does
    // some rounding by default. This gets a more exact number.
    static decimal GetAccurateDecimal(double r)
    {
        string accurateStr = r.ToString("G17", CultureInfo.InvariantCulture);
        return Decimal.Parse(accurateStr, CultureInfo.InvariantCulture);
    }

Answer 7

Excel 通过做 WORK 来“正确”地舍入这样的数字。他们从 1985 年开始，使用了一组相当“正常”的浮点例程，并添加了一些缩放整数假浮点，从那时起他们就一直在调整这些东西并添加特殊情况。该应用程序过去确实存在大多数与其他人相同的“明显”错误，只是它大多在很久以前就存在了。 90 年代初，当我为他们提供技术支持时，我自己提交了一些文件。

Answer 8

您需要的是：

 double f = 22.0/7.0;
    cout.setf(ios::fixed, ios::floatfield);
    cout.precision(6); 
    cout<<f<<endl;  

如何实现（只是四舍五入最后一位数字的概述）
:

long getRoundedPrec(double d,   double precision = 9)
{
    precision = (int)precision;
    stringstream s;
    long l = (d - ((double)((int)d)))* pow(10.0,precision+1);
    int lastDigit = (l-((l/10)*10));
    if( lastDigit >= 5){
        l = l/10 +1;
    }
    return l;
}

Answer 9

正如以 10 为基数的数字在转换为以 2 为基数时必须进行舍入一样，在将数字从 2 基数转换为以 10 为基数时也可以对其进行舍入。一旦数字具有以 10 为基数的表示形式，就可以通过查看要舍入的数字右侧的数字以直接的方式再次舍入。

虽然上述断言没有任何问题，但还有一个更务实的解决方案。问题是二进制表示试图尽可能接近十进制数，即使该二进制小于十进制。误差量在真实值的 [-0.5,0.5] 最低有效位 (LSB) 范围内。出于舍入目的，您希望它在 [0,1] LSB 内，以便误差始终为正，但如果不更改浮点数学的所有规则，这是不可能的。

您可以做的一件事是向该值添加 1 LSB，因此误差在真实值的 [0.5,1.5] LSB 范围内。总体而言，该结果不太准确，但准确程度也非常小。当值被四舍五入以十进制数表示时，它更有可能四舍五入为正确的十进制数，因为误差始终为正。

要在舍入之前向值添加 1 LSB，请参阅 这个问题。例如，在 Visual Studio C++ 2010 中，过程为：

Round(_nextafter(37.785,37.785*1.1),0.01);

Answer 10

有很多方法可以使用统计、数值算法来优化浮点值的结果。

最简单的方法可能是在精度范围内搜索重复的 9 或 0。如果有的话，也许那些 9 是多余的，只需将它们四舍五入即可。但这在很多情况下可能行不通。下面是一个具有 6 位精度的 float 示例：

2.67899999 → 2.679
12.3499999 → 12.35
1.20000001 → 1.2

Excel 始终将输入范围限制为 15 位数字，并将输出四舍五入到最大 15 位数字，因此这可能是 Excel 使用的方式之一

---

或者您可以 <包括精度和数字。每一步之后，根据操作数的精度调整精度。例如，

1.113   → 3 decimal digits
6.15634 → 5 decimal digits

由于两个数字都在 double 的 16-17 位精度范围内，因此它们的和将精确到其中较大的一个，即 5 位。类似地，3+5＜3+5。 16，所以他们的乘积将精确到 8 位十进制数

1.113 + 6.15634 = 7.26934    → 5 decimal digits
1.113 * 6.15634 = 6.85200642 → 8 decimal digits

但是 4.1341677841 * 2.251457145 只会采用 double 的精度，因为实际结果超过 double 的精度

---

另一种有效的算法是 Grisu 但我没有没有机会去尝试。

2010年，Florian Loitsch在PLDI上发表了一篇精彩的论文，"用整数快速准确地打印浮点数”，这代表了该领域 20 年来最大的一步：他主要弄清楚了如何使用机器整数进行准确的渲染！为什么我说“大部分”？因为虽然 Loitsch 的“Grisu3”算法非常快，但它会放弃大约 0.5% 的数字，在这种情况下你必须回退到 Dragon4 或衍生版本

这里有龙：在你甚至不知道自己遇到的问题上取得进展

事实上，我认为 Excel 必须结合许多不同的方法才能达到最佳结果

值达到零时的示例

在 Excel 95 或更早版本中，在新工作簿中输入以下内容：

A1：=1.333+1.225-1.333-1.225

右键单击单元格 A1，然后单击“设置单元格格式”。在“数字”选项卡上，单击“类别”下的“科学”。将小数位数设置为 15。

Excel 95 显示 -2.22044604925031E-16，而不是显示 0。

但是，Excel 97 引入了一项优化来尝试纠正此问题。如果加法或减法运算的结果为零或非常接近零，Excel 97 及更高版本将补偿由于操作数与二进制之间的转换而引入的任何错误。上面的示例在 Excel 97 及更高版本中执行时，可以以科学记数法正确显示 0 或 0.000000000000000E+00。

浮点运算可能会在 Excel 中给出不准确的结果

Answer 11

正如 mjfgates 所说，Excel 付出了很大的努力才能做到这一点“正确”。当您尝试重新实现这一点时，要做的第一件事就是定义“正确”的含义。明显的解决方案：

• 实现有理算术

  缓慢但可靠。

• 实施一系列启发式方法

  快速但很难做到正确（想想“多年的错误报告”）。

这实际上取决于您的应用程序。

Answer 12

大多数十进制分数无法用二进制精确表示。

double x = 0.0;
for (int i = 1; i <= 10; i++)
{
  x += 0.1;
}
// x should now be 1.0, right?
//
// it isn't. Test it and see.

一种解决方案是使用 BCD。它很旧了。但是，这也是经过验证的。我们每天都有很多其他的旧想法（比如用 0 代表什么都没有……）。

另一种技术使用输入/输出缩放。这样做的优点是几乎所有数学都是整数数学。