부동 소수점 연산은 Excel에서 부정확한 결과를 제공할 수 있습니다.
요약
이 문서에서는 Microsoft Excel에서 부동 소수점 숫자를 저장하고 계산하는 방법에 대해 설명합니다. 반올림 또는 데이터 잘림으로 인해 일부 숫자 또는 수식의 결과에 영향을 줄 수 있습니다.
개요
Microsoft Excel은 부동 소수점 숫자를 저장하고 계산하는 방법을 결정하기 위해 IEEE 754 사양에 따라 설계되었습니다. IEEE는 주로 컴퓨터 소프트웨어 및 하드웨어에 대한 표준을 결정하는 국제 기구인 Institute of Electrical and Electronics Engineers입니다. 754 사양은 부동 소수점 숫자를 이진 컴퓨터에 저장하는 방법을 설명하는 매우 널리 채택된 사양입니다. 부동 소수점 숫자를 적절한 공간에 저장할 수 있으며 계산이 비교적 빠르게 수행될 수 있기 때문에 인기가 높습니다. 754 표준은 Intel, Motorola, Sun 및 MIPS 프로세서를 포함하여 부동 소수점 수학을 구현하는 거의 모든 오늘날 PC 기반 마이크로프로세서의 부동 소수점 단위 및 숫자 데이터 프로세서에 사용됩니다.
숫자가 저장될 때 해당 이진 숫자는 모든 숫자나 소수점 숫자를 나타낼 수 있습니다. 예를 들어 1/10 분수는 10진수 형식인 0.1로 나타낼 수 있습니다. 그러나 이진 형식의 동일한 숫자는 다음처럼 반복되는 이진 소수점이 됩니다.
0001100110011100110011 (등)
이것은 무한 반복될 수 있습니다. 이 숫자는 유한(제한된) 공간의 양으로 표현할 수 없습니다. 따라서 이 숫자는 저장될 때 약 -2.8E-17로 내림됩니다.
그러나 IEEE 754 사양에는 다음과 같은 세 가지 일반 범주에 속하는 몇 가지 제한 사항이 있습니다.
- 최대/최소 제한
- 정밀도
- 반복되는 이진수
추가 정보
최대/최소 제한
모든 컴퓨터에는 처리할 수 있는 최대 및 최소 수가 있습니다. 숫자가 저장되는 메모리 비트 수가 제한적이기 때문에 저장할 수 있는 최대 또는 최소 개수 역시 유한합니다. Excel의 경우 저장할 수 있는 최대 수는 1.79769313486232E+308이며, 저장할 수 있는 최소 양수는 2.2250738585072E-308입니다.
IEEE 754를 준수하는 경우
- 언더플로: 너무 작은 숫자가 생성되어 나타내지 못하면 언더플로가 발생합니다. IEEE 및 Excel의 경우 결과는 0입니다(단, IEEE는 -0 개념을 가지고 있고 Excel은 그렇지 않음).
- 오버플로: 숫자가 나타내지 못할 정도로 너무 크면 오버플로가 발생합니다. Excel은 이 경우에 자체적인 특수 표현을 사용합니다(#NUM!).
IEEE 754를 준수하지 않는 경우
비정규화된 숫자: 비정규화된 숫자는 0의 지수로 나타냅니다. 이 경우 전체 숫자가 가수부(mantissa)에 저장되고 이 가수부에는 암시적인 선행 1이 없습니다. 따라서 정밀도가 손실되는데 숫자가 작을수록 손실되는 정밀도가 더 많습니다. 이 범위의 작은 쪽 끝에 있는 숫자는 한 자릿수의 정밀도만 가집니다.
예: 정규화된 숫자에는 암시적인 선행 1이 있습니다. 예를 들어 가수부가 0011001을 나타내면 암시적인 선행 1로 인해 정규화된 숫자가 10011001이 됩니다. 비정규화된 숫자에는 암시적 선행 1이 없습니다. 따라서 이 0011001 예에서 비정규화된 숫자가 동일하게 유지됩니다. 이 경우 정규화된 숫자에는 8개의 유효 자릿수(10011001)가 있는 반면 비정규화된 숫자에는 5개의 유효 자릿수(11001)가 존재하고 선행 0은 유효하지 않습니다.
비정규화된 숫자는 기본적으로 표준 하한보다 작은 숫자를 저장할 수 있도록 하는 해결 입니다. 비정규화된 숫자의 유효 자릿수가 가변적이기 때문에 Microsoft는 본질적으로 사양의 이 선택적 부분을 구현하지 않습니다. 이렇게 하면 계산에 심각한 오류가 발생할 수 있습니다.
양수/음수 무한대: 0으로 나누면 무한대가 발생합니다. Excel은 무한대를 지원하지 않는 대신 #DIV/0!을 반환합니다. 오류가 발생할 수 있습니다.
Not-a-Number(NaN): NaN은 잘못된 연산(예: 무한대/무한대, 무한대-무한대 또는 -1의 제곱근)을 나타내는 데 사용됩니다. NaN을 사용하면 프로그램이 잘못된 연산을 계속 진행할 수 있습니다. 대신 Excel은 즉시 #NUM! 또는 #DIV/0!과 같은 오류를 생성합니다.
정밀도
부동 소수점 숫자는 부호, 지수 및 가수부 등 65비트 범위 내의 세 부분으로 이진 파일에 저장됩니다.
| 기호 | 지수 | 가수부 |
|---|---|---|
| 1 부호 비트 | 11비트 | 1 암시적 비트 + 52비트 분수 |
부호는 숫자의 부호(양수 또는 음수)를 저장하고, 지수는 숫자를 높이거나 낮출 2의 거듭제곱을 저장합니다(2의 최대/최소 거듭제곱은 +1,023 및 -1,022). 가수부는 실제 숫자를 저장합니다. 가수부의 유한 저장 영역은 인접한 두 개의 부동 소수점 숫자가 서로 얼마나 가까운지(즉, 정밀도)를 제한합니다.
가수부와 지수는 둘 다 별도의 구성 요소로 저장됩니다. 따라서 가능한 정밀도의 양은 조작하는 숫자(가수부)의 크기에 따라 달라질 수 있습니다. Excel의 경우, Excel은 1.79769313486232E308에서 2.2250738585072E-308까지의 숫자를 저장할 수 있지만 15자리 정밀도 내에서만 가능합니다. 이 제한은 IEEE 754 사양을 엄격하게 준수한 직접적인 결과이며, Excel의 제한은 아닙니다. 이 정밀도 수준은 다른 스프레드시트 프로그램에서도 찾을 수 있습니다.
부동 소수점 숫자는 다음 형태로 표현됩니다. 여기서 지수는 이진 지수입니다.
X = 분수 * 2^(지수 - 편차)
소수점은 숫자의 정규화된 소수 부분으로, 선행 비트가 항상 1이 되도록 지수가 조정되므로 정규화됩니다. 이렇게 하면 저장할 필요가 없으며 정밀도를 한 번 더 얻을 수 있습니다. 이것이 암시적인 비트가 있는 이유입니다. 이것은 소수점 왼쪽에 한 자리 숫자를 들이기 위해 지수를 조작하는 과학적 표기법과 비슷합니다. 1과 0만 있기 때문에 이진을 제외하고는 항상 첫 번째 비트가 1이 되도록 지수를 조작할 수 있습니다.
편차는 음수 지수를 저장해야 하는 필요성을 없애는 데 사용되는 편차 값입니다. 단정밀도 숫자의 편차는 배정도 숫자에 대해 127과 1,023(10진수)입니다. Excel은 배정도를 사용하여 숫자를 저장합니다.
매우 큰 수를 사용하는 예제
새 통합 문서에 다음을 입력합니다.
A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
C1 셀의 결과 값은 셀 A1과 같은 값인 1.2E+200입니다. 실제로 IF 함수(예: IF(A1=C1)를 사용하여 셀 A1과 C1을 비교하면 결과는 TRUE가 됩니다. 이것은 정밀도의 15자리 유효 자릿수만 저장하는 IEEE 사양으로 인해 발생했습니다. 위의 계산을 저장하려면 Excel에서 100자릿수 이상의 정밀도를 필요로 하게 됩니다.
매우 작은 숫자를 사용하는 예제
새 통합 문서에 다음을 입력합니다.
A1: 0.000123456789012345
B1: 1
C1: =A1+B1
C1 셀의 결과 값은 1.000123456789012345 대신 1.00012345678901입니다. 이것은 정밀도의 15자리 유효 자릿수만 저장하는 IEEE 사양으로 인해 발생했습니다. 위의 계산을 저장하려면 Excel에서 19자릿수 이상의 정밀도를 필요로 하게 됩니다.
정밀도 오류 수정
Excel은 반올림 오류를 보정하는 두 가지 기본 방법을 제공합니다. 하나는 ROUND 함수이고 다른 하나는 표시된 정밀도 또는 표시된 대로 정밀도 설정 통합 문서 옵션입니다.
방법 1: ROUND 함수
이전 데이터를 사용하여, 다음은 ROUND 함수를 사용하여 숫자를 5자리까지 강제로 설정하는 예제입니다. 이렇게 하면 결과를 다른 값과 성공적으로 비교할 수 있습니다.
A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
그러면 결과가 1.2E+200이 됩니다.
D1: =IF(C1=1.2E+200, TRUE, FALSE)
그러면 값이 TRUE가 됩니다.
방법 2: 표시된 정밀도
경우에 따라 표시된 정밀도 옵션을 사용하여 반올림 오류가 작업에 영향을 미치는 것을 방지할 수 있습니다. 이 옵션을 사용하면 워크시트에 있는 각 숫자의 값이 강제로 표시됩니다. 이 옵션을 켜려면 다음 단계를 따르십시오.
- 파일 메뉴에서 옵션을 클릭한 다음, 고급 카테고리를 클릭합니다.
- 이 통합 문서 계산 시 섹션에서 원하는 통합 문서를 선택하고 표시된 대로 정밀도 설정 확인란을 선택합니다.
예를 들어 소수점 두 자리를 표시하는 숫자 형식을 선택한 다음, 표시된 정밀도 옵션을 켜면 통합 문서 저장 시 소수점 이하는 두 자리를 초과하는 모든 정확도가 손실됩니다. 이 옵션은 모든 워크시트를 포함한 활성 통합 문서에 영향을 미치게 됩니다. 이 옵션은 취소할 수 없고 손실된 데이터를 복구할 수도 없습니다. 이 옵션을 활성화하기 전에 통합 문서를 저장하는 것이 좋습니다.
결과가 거의 0에 가까운 반복되는 이진 수 및 계산
부동 소수점 숫자를 이진 형식으로 저장하는 데 영향을 주는 또 다른 혼란스러운 문제는 소수점 10의 유한하고 반복되지 않는 숫자 중 일부가 이진수로 반복되는 무한한 숫자라는 점입니다. 가장 일반적인 예로는 값 0.1 및 해당 변형이 있습니다. 이러한 숫자는 10진수로 완벽하게 표시될 수 있지만, 가수부에 저장될 때 이진 형식의 동일한 숫자는 다음과 같은 반복되는 이진수가 됩니다.
000110011001100110011 (등)
IEEE 754 사양은 어떤 숫자도 특별한 허용을 하지 않습니다. 이것은 가능한 것은 가수부에 저장하고 나머지는 잘라냅니다. 이로 인하여 저장 시 -2.8E-17 또는 0.000000000000000028 정도의 오류가 발생합니다.
10진수 0.0001과 같은 일반적인 소수점 분수도 정확히 이진수로 표현할 수 없습니다. (0.0001은 104비트의 주기를 가진 반복되는 이진 분수입니다). 이것은 분수 1/3이 10진수로 정확히 표현될 수 없는 이유와 유사합니다(0.33333333333333333333 반복).
예를 들어 다음과 같은 응용 프로그램용 Microsoft Visual Basic에서 간단한 예제를 고려해보세요.
Sub Main()
MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub
그러면 출력으로 0.999999999999996이 인쇄됩니다. 이진수로 0.0001을 나타낼 때 작은 오류는 합계로 전파됩니다.
예: 음수 추가
새 통합 문서에 다음을 입력합니다.
A1: =(43.1-43.2)+1
A1 셀을 마우스 오른쪽 단추로 클릭한 다음 셀 서식을 클릭합니다. 번호 탭에서 범주 아래에 있는 과학적을 클릭합니다. 소수점 자리를 15로 설정합니다.
Excel은 0.9를 표시하는 대신 0.899999999999999를 표시합니다. (43.1-43.2)이 먼저 계산되어 -0.1이 일시적으로 저장되고 -0.1 저장 오류가 계산에 됩니다.
값이 0에 도달할 때의 예
Excel 95 이전 버전에서 새 통합 문서에 다음을 입력하십시오.
A1: =1.333+1.225-1.333-1.225
A1 셀을 마우스 오른쪽 단추로 클릭한 다음 셀 서식을 클릭합니다. 번호 탭에서 범주 아래에 있는 과학적을 클릭합니다. 소수점 자리를 15로 설정합니다.
Excel 95에서는 0을 표시하는 대신 -2.22044604925031E-16을 표시합니다.
하지만 Excel 97에서는 이 문제의 해결을 시도하는 최적화를 도입했습니다. 추가 또는 빼기 작업으로 인해 값이 0 또는 0에 매우 가깝게 표시될 경우 Excel 97 이상이면 이진 파일을 대상으로 피연산자를 변환한 결과로 발생하는 모든 오류를 보상합니다. 위 예제는 Excel 97 이상에서 수행될 때 0 또는 0.000000000000000E+00을 과학적 표기법으로 올바르게 표시하는 예제입니다.
부동 소수점 숫자 및 IEEE 754 사양에 대한 자세한 내용은 다음 World Wide Web 사이트를 참조하세요.
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기