即使不精确，Excel如何成功舍入浮点数? [英] How does Excel successfully round floating point numbers even though they are imprecise?
问题描述
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()中针对C ++中的float的解决方案对于以上问题.
推荐答案
"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()函数在不同的Microsoft产品之间没有以一致的方式实现."(
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一样，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如何成功舍入浮点数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
