問題描述

例如，這個博客 說 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模板網！