엑셀은 부동소수점 숫자가 부정확함에도 불구하고 어떻게 성공적으로 반올림합니까?
예를 들어, 이 블로그는 0.005가 정확히 0.005는 아니지만, 그 숫자를 반올림하면 올바른 결과를 얻을 수 있다고 말합니다.
저는 C++로 모든 종류의 반올림을 시도해 보았는데 숫자를 소수점 이하로 반올림하면 실패합니다.예를 들어, 반올림(x,y)은 x를 y의 배수로 반올림합니다.따라서 라운드(37.785,0.01)는 37.78이 아니라 37.79를 제공해야 합니다.
커뮤니티에 도움을 요청하기 위해 이 질문을 다시 시작합니다.문제는 부동 소수점 번호의 부정확성에 있습니다(37,785는 37.784999999로 표현됨).
문제는 엑셀이 이 문제를 어떻게 해결할 수 있느냐는 것입니다.
C++의 float에 대한 이 라운드()의 솔루션은 위의 문제에 대해 올바르지 않습니다.
"라운드(37.785,0.01)는 37.78이 아니라 37.79를 제공해야 합니다."
먼저, 여기서 37.78이 아니라 37.79가 "옳은" 답이라는 합의는 없습니까?타이브레이커들은 항상 약간 터프합니다.타이의 경우 항상 반올림하는 것이 널리 사용되는 접근 방식이지만, 확실히 유일한 접근 방식은 아닙니다.
두 번째로, 이것은 동점 상황이 아닙니다.IEEE 바이너리 64 부동 소수점 형식의 숫자 값은 37.7849999999997(약)입니다.사람이 37.785의 값을 입력하는 것 외에도 37.784999999999997의 값을 얻는 많은 방법이 있습니다. 그리고 그것을 부동소수점 표현으로 변환합니다.대부분의 경우 정답은 37.79가 아니라 37.78입니다.
다음 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.79가 아니라 37.78로 반올림해야 합니다.하지만 엑셀은 그것을 반올림합니다. 왜죠?
그 이유는 실제 숫자가 컴퓨터에서 어떻게 표현되는지와 관련이 있습니다.Microsoft도 다른 많은 제품과 마찬가지로 IEEE 64비트 부동 소수점 형식을 사용합니다.숫자 37785/1000은 이 형식으로 표시될 때 정밀도 손실을 겪습니다.이 정밀도 손실은 19810222/2^19+21474836/2^47에서 발생하는 것이 아니라 "정확한 숫자"입니다.
저는 정확한 숫자를 부정확한 37785/1000과 동일한 부동소수점 표현을 갖도록 의도적으로 구성했습니다.이 이 한 값을 의 Excel 이 이 한 값 내 이 의 올 반 하 림 정 Excel 열 쇠 입 니 다 는 하 결 을 식 는 이 것 올 방 업 작 으 정 로 림 림 확 닌 을 아 ▁this ▁ ▁to ▁excel ▁key ▁that ▁how ▁determ ▁the ▁value ▁up ▁is ' ▁excel ▁exact 다 니 입 ▁rounds 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;
}
매우 정확한 임의 정밀도와 부동소수점 숫자를 소수점 고정 집합으로 반올림하려면 GNU MPFR과 같은 수학 라이브러리를 살펴봐야 합니다.C 라이브러리이지만, C를 사용하지 않으려면 제가 올린 웹 페이지도 몇 가지 다른 C++ 바인딩으로 연결됩니다.
여러분은 또한 제록스 팔로 알토 연구 센터에서 데이비드 골드버그의 "부동점 산술에 대해 모든 컴퓨터 과학자들이 알아야 할 것"이라는 제목의 논문을 읽고 싶을지도 모릅니다.이진 데이터의 모든 것을 나타내는 컴퓨터에서 부동소수점 숫자를 근사화할 수 있는 기본 프로세스와 FPU 기반 부동소수점 수학에서 라운딩 오류 및 기타 문제가 어떻게 슬금슬금 발생할 수 있는지를 보여주는 훌륭한 기사입니다.
엑셀이 어떻게 하는지는 모르겠지만 부동소수점 번호를 멋지게 인쇄하는 것은 어려운 문제입니다: http://www.serpentine.com/blog/2011/06/29/here-be-dragons-advances-in-problems-you-didnt-even-know-you-had/
그래서 당신의 실제 질문은 어떻게 정확하게 둥근 부동소수점 -> 문자열 변환을 얻을 수 있는가 하는 것 같습니다.이러한 용어를 검색하면 많은 문서를 얻을 수 있지만, 사용할 항목에 관심이 있다면 대부분의 플랫폼에서 sprintf()/snprintf()의 적절한 구현을 제공합니다.따라서 이를 사용하여 버그를 발견하면 공급업체에 보고서를 제출합니다.
부동소수점 숫자를 인수로 사용하고 주어진 소수점 숫자로 정확하게 반올림한 다른 부동소수점 숫자를 반환하는 함수는 쓸 수 없습니다. 왜냐하면 무한 이진 표현을 가진 유한 소수점 표현을 가진 많은 숫자가 있기 때문입니다. 가장 간단한 예 중 하나는 0.1입니다.
원하는 값을 얻으려면 반올림 함수의 결과로 다른 유형을 사용할 수 있어야 합니다.숫자를 인쇄해야 할 경우 문자열과 형식 지정 기능을 사용할 수 있습니다. 문제는 원하는 형식 지정을 정확하게 얻는 방법입니다.그렇지 않으면 계산을 수행하는 경우와 같이 이 숫자를 정확하게 계산하기 위해 이 숫자를 저장해야 하는 경우 십진수를 정확하게 나타낼 수 있는 라이브러리가 필요합니다.이 경우 가장 일반적인 접근 방식은 척도화된 표현, 즉 십진수 숫자와 함께 값에 대한 정수를 사용하는 것입니다.값을 눈금까지 올린 10으로 나누면 원래 숫자가 표시됩니다.
이러한 접근 방식 중에 적합한 것이 있다면, 저는 실용적인 제안으로 답변을 확장하도록 노력할 것입니다.
Excel은 작업을 수행하여 이와 같은 숫자를 "정확하게" 반올림합니다.그들은 1985년에 꽤 "정상적인" 부동소수점 루틴 세트로 시작했고, 스케일 정수의 가짜 부동소수점을 추가했습니다. 그리고 그 이후로 그들은 그것들을 조정하고 특별한 경우를 추가했습니다.앱 DID는 다른 모든 사람들이 했던 것과 동일한 대부분의 "명백한" 버그를 가지고 있었습니다. 단지 오래 전에 대부분 버그를 가지고 있었다는 것입니다.제가 90년대 초에 그들을 위해 기술 지원을 할 때, 제가 직접 몇 개를 제출했습니다.
아래의 C# 코드는 엑셀에서 반올림된 숫자로 반올림된 것으로 생각합니다.C++에서 동작을 정확하게 복제하려면 특수한 소수점 유형을 사용해야 할 수 있습니다.
일반 영어에서는 이중 정밀도 숫자가 소수점으로 변환된 다음 15개의 유의한 숫자로 반올림됩니다(소수점 15개와 혼동되지 않음).결과는 지정된 소수 자릿수로 두 번째 반올림됩니다.
이상하게 보일 수도 있지만, 이해해야 할 것은 Excel이 항상 15개의 유의한 숫자로 반올림된 숫자를 표시한다는 것입니다.ROUD() 함수가 해당 표시값을 시작점으로 사용하지 않고 내부 이중 표현을 대신 사용했다면, ROUD(A1,N)가 A1의 실제 값과 일치하지 않는 경우가 있을 수 있습니다.그것은 비기술적인 사용자에게는 매우 혼란스러울 것입니다.
37.785에 가장 가까운 배수의 정확한 소수점 값은 37.78499999999999965893948683519107818603515625입니다.(1/4, 1/8, 1/16 등이 모두 유한한 소수 확장을 가지고 있기 때문에 임의의 이중은 유한한 소수로 정확하게 표현될 수 있습니다.)이 숫자를 소수점 이하 두 자리로 반올림하면 비기기가 없고 결과는 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);
}
필요한 것은 다음과 같습니다.
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;
}
base-10 숫자가 base-2로 변환될 때 반올림해야 하는 것처럼, base-2에서 base-10으로 변환될 때 반올림할 수 있습니다.숫자에 10진수 표시가 있으면 반올림하려는 숫자의 오른쪽에 있는 숫자를 보고 간단한 방법으로 다시 반올림할 수 있습니다.
위의 주장에는 아무런 문제가 없지만, 훨씬 더 실용적인 해결책이 있습니다.문제는 이진 표현이 소수점보다 작더라도 소수점에 최대한 근접하려고 한다는 것입니다.오류의 양이 참 값의 [-0.5,0.5] 최하위 비트(LSB) 내에 있습니다.반올림을 위해 오차가 항상 양의 값이 되도록 [0,1] LSB 내에 있는 것이 좋으나 부동 소수점 연산의 모든 규칙을 변경하지 않고는 불가능합니다.
한 가지 방법은 값에 1LSB를 추가하는 것이므로 오차가 참 값의 [0.5,1.5] LSB 내에 있습니다.이 값은 전체적으로 정확도가 낮지만 매우 작은 양에 불과합니다. 소수로 표현하기 위해 값을 반올림하면 오차가 항상 양수이므로 적절한 소수로 반올림될 가능성이 훨씬 높습니다.
반올림하기 전에 값에 1LSB를 추가하려면 이 질문에 대한 답을 참조하십시오.예를 들어 Visual Studio C++ 2010의 경우 절차는 다음과 같습니다.
Round(_nextafter(37.785,37.785*1.1),0.01);
통계적, 수치적...을 사용하여 부동소수점 값의 결과를 최적화하는 방법은 여러 가지가 있습니다.알고리즘
가장 쉬운 것은 정확도 범위에서 반복적인 9 또는 0을 찾는 것입니다.만약 있다면, 그 9개는 중복된 것일 수도 있습니다. 그냥 그것들을 하세요.하지만 많은 경우에는 효과가 없을 수도 있습니다.다음은 예입니다.float6자리 정밀도:
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
두 숫자 모두 두 자릿수의 16-17자리 정밀도 범위 내에 있으므로, 두 숫자의 합은 두 자릿수 중 큰 숫자인 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실제 결과가 두 배의 정밀도를 초과하기 때문에 두 배의 정확도만 취할 것입니다.
또 다른 효율적인 알고리즘은 Grisu이지만 시도할 기회가 없었습니다.
2010년, 플로리안 로이치는 PLDI에 "정수로 빠르고 정확하게 부동소수점 숫자를 인쇄"라는 멋진 논문을 발표했는데, 이는 20년 만에 이 분야에서 가장 큰 단계를 보여줍니다: 그는 정확한 렌더링을 수행하기 위해 기계 정수를 사용하는 방법을 대부분 알아냈습니다!왜 내가 "대부분"이라고 말합니까?Loitch의 "Grisu3" 알고리즘은 매우 빠르지만, 약 0.5%의 숫자를 포기하기 때문에, 이 경우 Dragon4 또는 파생 모델로 다시 떨어져야 합니다.
사실 엑셀은 여러가지 방법들을 결합해야만 최고의 결과를 얻을 수 있다고 생각합니다.
값이 0에 도달하는 예
Excel 95 이전 버전에서는 새 워크북에 다음을 입력합니다.
A1: =1.333+1.225-1.333-1.225셀 A1을 마우스 오른쪽 단추로 클릭한 다음 셀 형식 지정을 클릭합니다.숫자 탭에서 범주 아래의 과학을 클릭합니다.소수 자릿수를 15로 설정합니다.
0을 표시하는 대신 Excel 95가 표시됩니다.
-2.22044604925031E-16.그러나 Excel 97은 이 문제를 수정하려고 시도하는 최적화를 도입했습니다.더하기 또는 빼기 연산으로 0 또는 매우 가까운 값이 발생하는 경우 Excel 97 이상은 피연산자를 이진수로 변환하거나 이진수에서 변환할 때 발생하는 모든 오류를 보상합니다.Excel 97 이상에서 수행할 때 위의 예는 과학적 표기법으로 0 또는 0.0000000000000E+00을 올바르게 표시합니다.
mjfgates가 말했듯이, Excel은 이 "올바르게" 하기 위해 열심히 노력합니다.이를 다시 구현하려고 할 때 가장 먼저 해야 할 일은 "옳다"는 의미를 정의하는 것입니다.명확한 솔루션:
합리적인 계산을 하다.
느리지만 믿을 만합니다.
많은 휴리스틱을 실행합니다.
신속하지만 정확하게 파악하기가 까다롭습니다("몇 년 동안의 버그 보고서"라고 가정).
그것은 정말로 당신의 신청에 달려 있습니다.
대부분의 소수점 분수는 이진법으로 정확하게 표현할 수 없습니다.
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을 사용하여 아무것도 표시하지 않음...).
다른 기법은 입력/출력 시 스케일링을 사용합니다.이것은 거의 모든 수학이 정수 수학이라는 장점이 있습니다.
언급URL : https://stackoverflow.com/questions/6930786/how-does-excel-successfully-round-floating-point-numbers-even-though-they-are-im
'sourcecode' 카테고리의 다른 글
| 해결 방법: 'keyWindow'는 iOS 13.0에서 더 이상 사용되지 않습니다. (0) | 2023.05.09 |
|---|---|
| "ReferenceEquals(myObject, null)"이 "myObject == null"보다 더 나은 방법입니까? (0) | 2023.05.09 |
| 병합을 미리 보려면 어떻게 해야 합니까? (0) | 2023.05.09 |
| HEAD GIT은 무엇입니까? (0) | 2023.05.09 |
| npm을 통해 트위터 부트스트랩을 설치하는 목적은 무엇입니까? (0) | 2023.05.09 |