即使浮点数不精确，Excel 如何成功舍入?

问题描述

例如，这个博客 说 0.005 不完全是 0.005，但四舍五入这个数字会产生正确的结果.

For example, this blog says 0.005 is not exactly 0.005, but rounding that number yields the right result.

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

I have tried all kinds of rounding in C++ and it fails when rounding numbers to certain decimal places. For example, Round(x,y) rounds x to a multiple of y. So Round(37.785,0.01) should give you 37.79 and not 37.78.

我重新打开这个问题是为了向社区寻求帮助.问题在于浮点数的不精确性(37,785 表示为 37.78499999999).

I am reopening this question to ask the community for help. The problem is with the impreciseness of floating point numbers (37,785 is represented as 37.78499999999).

问题是 Excel 如何解决这个问题?

The question is how does Excel get around this problem?

此round() for float in C++ 中的解决方案对于以上问题.

The solution in this round() for float in C++ is incorrect for the above problem.

推荐答案

Round(37.785,0.01) 应该给你 37.79 而不是 37.78."

"Round(37.785,0.01) should give you 37.79 and not 37.78."

首先，没有人认为 37.79 而不是 37.78 是正确"的答案?决胜局总是有点困难.虽然在平局的情况下总是四舍五入是一种广泛使用的方法，但它肯定不是唯一的方法.

First off, there is no consensus that 37.79 rather than 37.78 is the "right" answer here? Tie-breakers are always a bit tough. While always rounding up in the case of a tie is a widely-used approach, it certainly is not the only approach.

其次，这不是决胜局.IEEE binary64 浮点格式中的数值为 37.784999999999997(大约).除了人类输入 37.785 的值并碰巧将其转换为浮点表示之外，还有很多方法可以获得 37.784999999999997 的值.在大多数情况下，正确答案是 37.78 而不是 37.79.

Secondly, this isn't a tie-breaking situation. The numerical value in the IEEE binary64 floating point format is 37.784999999999997 (approximately). There are lots of ways to get a value of 37.784999999999997 besides a human typing in a value of 37.785 and happen to have that converted to that floating point representation. In most of these cases, the correct answer is 37.78 rather than 37.79.

附录
考虑以下 Excel 公式:

Addendum
Consider the following Excel formulae:

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

两个单元格将显示相同的值，37.79.关于 37785/1000 是否应该四舍五入到 37.78 或 37.79 并具有两位精度，这是一个合理的争论.如何处理这些极端情况有点武断，也没有一致的答案.微软内部甚至没有一个一致的答案:由于历史原因，Round() 函数在不同的微软产品之间没有以一致的方式实现."(http://support.microsoft.com/kb/196652 ) 给定一个无限精度的机器，微软的 VBA 会将 37.785 舍入到 37.78(银行家的圆)而 Excel 将产生 37.79(对称算术圆).

Both cells will display the same value, 37.79. There is a legitimate argument over whether 37785/1000 should round to 37.78 or 37.79 with two place accuracy. How to deal with these corner cases is a bit arbitrary, and there is no consensus answer. There isn't even a consensus answer inside Microsoft: "the Round() function is not implemented in a consistent fashion among different Microsoft products for historical reasons." ( http://support.microsoft.com/kb/196652 ) Given an infinite precision machine, Microsoft's VBA would round 37.785 to 37.78 (banker's round) while Excel would yield 37.79 (symmetric arithmetic round).

对于后一个公式的四舍五入没有争论.它严格小于 37.785，所以它应该四舍五入到 37.78，而不是 37.79.然而，Excel 会将其四舍五入.为什么?

There is no argument over the rounding of the latter formula. It is strictly less than 37.785, so it should round to 37.78, not 37.79. Yet Excel rounds it up. Why?

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

The reason has to do with how real numbers are represented in a computer. Microsoft, like many others, uses the IEEE 64 bit floating point format. The number 37785/1000 suffers from precision loss when expressed in this format. This precision loss does not occur with 19810222/2^19+21474836/2^47; it is an "exact number".

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

I intentionally constructed that exact number to have the same floating point representation as does the inexact 37785/1000. That Excel rounds this exact value up rather than down is the key to determining how Excel's ROUND() function works: It is a variant of symmetric arithmetic rounding. It rounds based on a comparison to the floating point representation of the corner case.

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;
}   

这篇关于即使浮点数不精确，Excel 如何成功舍入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！