Excel의 부동 소수점 연산에서 부정확한 결과가 나올 수 있음

기술 자료 번역 기술 자료 번역
기술 자료: 78113 - 이 문서가 적용되는 제품 보기.
이 문서는 이전에 다음 ID로 출판되었음: KR78113
모두 확대 | 모두 축소

이 페이지에서

요약

본 문서에서는 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로 표현할 수 있습니다. 그러나 0.1을 이진 형식으로 표현하면 순환 이진 소수가 됩니다
0001100110011100110011(계속)
그리고 무한히 반복될 수 있습니다. 이 숫자를 유한한(제한된) 공간에서는 표현할 수 없습니다. 따라서 이 숫자는 저장할 때 약 -2.8E-17로 반올림됩니다.

그러나 IEEE 754 규정을 세 가지 일반적인 범주로 제한하는 경우가 있습니다. ?
  • 최대값/최소값 제한
  • 정밀도
  • 순환 이진수

추가 정보

최대값/최소값 제한

모든 컴퓨터에는 처리할 수 있는 최대 숫자와 최소 숫자가 있습니다. 숫자를 저장하는 메모리 비트 수는 유한하기 때문에 저장할 수 있는 최대 또는 최소 숫자 역시 유한합니다. Excel의 경우 저장할 수 있는 최대 숫자는 1.79769313486232E+308이며 저장할 수 있는 최소 양수는 2.2250738585072E-308입니다.

IEEE 754를 준수하는 경우

  • 언더플로: 언더플로는 생성된 숫자가 너무 작아 표현할 수 없을 때 발생합니다. IEEE는 -0의 개념이 있고 Excel에서는 없다는 예외가 있지만 IEEE와 Excel에서 결과는 0입니다.
  • 오버플로: 오버플로는 생성된 숫자가 너무 커 표현할 수 없을 때 발생합니다. 이런 경우 Excel은 특수한 표현(#NUM!)을 사용합니다.

IEEE 754를 준수하지 않는 경우

  • 정규화되지 않은 숫자: 정규화되지 않은 숫자는 지수 0으로 나타냅니다. 이런 경우 전체 숫자는 가수로 저장되며 가수에는 암시적인 선행 1이 없습니다. 결과적으로 정밀도는 떨어지고 작은 숫자일수록 정밀도는 더욱 떨어집니다. 이 범위의 작은 쪽에 있는 숫자는 한자리의 정밀도만 갖습니다.
    예: 정규화된 숫자는 암시적인 선행 1을 갖습니다. 예를 들어, 가수가 0011001을 나타내면 정규화된 숫자는 암시적 선행 1 때문에 10011001이 됩니다. 정규화되지 않은 숫자에는 암시적인 선행 1이 없으므로 0011001의 예에서 정규화되지 않은 숫자는 동일하게 유지됩니다. 이 경우 정규화된 숫자에는 8자리의 유효 자릿수(10011001)가 있는 반면 정규화되지 않은 숫자에는 유효하지 않은 선행 0이 있는 5자리의 유효 자릿수(11001)가 있습니다.

    정규화되지 않은 숫자는 기본적으로 정상 저장 한계보다 작은 숫자를 저장할 수 있는 해결 방법입니다. Microsoft는 정규화되지 않은 숫자는 특성상 유효 자릿수가 변하기 때문에 규정에서 이 선택적인 부분은 구현하지 않았습니다. 이것은 계산할 때 큰 오류를 허용할 수 있습니다.
  • 양/음의 무한수: 무한수는 0으로 나눌 때 발생합니다. Excel은 무한수를 지원하지 않고, 이런 경우 #DIV/0! 오류를 생성합니다.
  • 비숫자(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에서 제한하는 것은 아닙니다. 이 정밀도 수준은 다른 스프레드시트 프로그램에서도 발견됩니다.

부동 소숫점 숫자는 다음 형식으로 표현됩니다. 여기서 exponent는 이진 지수입니다.
X = Fraction * 2^(exponent - bias)
Fraction은 지수는 선행 비트가 항상 1이 되도록 조정하기 때문에 숫자의 정규화된 소수부입니다. 따라서 저장할 필요가 없어 정밀도가 한 비트 늘어납니다. 그 이유는 암시적 비트가 있기 때문입니다. 이것은 지수가 소수점 왼쪽에 한 자리가 있도록 계산하는 공학용 표기법과 비슷합니다. 이진수를 제외하고는 1과 0만 있기 때문에 항상 첫번째 비트가 1이 되도록 지수를 처리할 수 있습니다.

bias는 음의 지수를 저장하지 못하도록 하는 데 사용되는 바이어스 값입니다. 단정밀도 숫자에 대한 바이어스는 127이고 배정밀도 숫자의 경우는 1,023(십진수)입니다. Excel에서는 배정밀도를 사용하여 숫자를 저장합니다.?

매우 큰 수를 사용하는 예

새 통합 문서에 다음을 입력하십시오.?
   A1: 1.2E+200
   B1: 1E+100
   C1: =A1+B1
C1 셀의 결과 값은 A1 셀과 같은 값인 1.2E+200입니다. 사실 IF(A1=C1)처럼 IF 함수를 사용하여 A1과 C1 셀을 비교하면 결과는 TRUE가 됩니다. 이 결과는 정밀도의 15자리 유효 자릿수만 저장하는 IEEE 규정으로 인한 것입니다. 위의 계산을 저장하려면 Excel에서는 적어도 100자리의 정밀도를 요구합니다.

매우 작은 수를 사용하는 예

새 통합 문서에 다음을 입력하십시오.
   A1: 0.000123456789012345
   B1: 1
   C1: =A1+B1
C1 셀의 결과 값은 000123456789012345가 아니고 1.00012345678901이 됩니다. 이 결과는 정밀도의 15자리 유효 자릿수만 저장하는 IEEE 규정으로 인한 것입니다. 위의 계산을 저장하려면 Excel에서는 적어도 100자리의 정밀도를 요구합니다.?

정밀도 오류 수정

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: 표시된 정밀도

경우에 따라 표시된 정밀도 옵션을 사용하여 반올림 오류가 작업에 영향을 미치는 것을 방지할 수 있습니다. 이 옵션은 워크시트에 있는 각 숫자 값을 표시된 값으로 만듭니다. 이 옵션을 설정하려면 다음과 같이 하십시오.
  1. Excel 2003 및 이전 버전의 경우 도구 메뉴에서 옵션을 클릭합니다.
  2. 계산 탭에서 표시된 정밀도 확인란을 선택합니다.
  1. Excel 2007의 경우 Microsoft Office 단추를 클릭하고 Excel 옵션을 클릭한 다음 고급 범주를 클릭합니다.
  2. 이 통합 문서의 계산 대상 섹션에서 원하는 통합 문서를 선택한 다음 표시된 정밀도 설정 확인란을 선택합니다.
  1. Excel 2013 및 2010에서는 파일을 클릭하고 옵션을 클릭한 다음 고급 범주를 클릭합니다.?
  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 for Applications으로 나타낸 간단한 예입니다.
   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub
결과로 0.999999999999996을 출력합니다. 이진수로 0.0001의 작은 오류가 합계에 전파됩니다.?

음수를 더하는 예

  1. 새 통합 문서에 다음을 입력하십시오.
       A1: =(43.1-43.2)+1
  2. A1 셀을 마우스 오른쪽 단추로 클릭한 다음 셀 서식을 클릭합니다. 숫자 탭의 범주에서 공학용을 클릭합니다. 소수 자릿수를 15로 설정합니다.
0.9를 표시하는 대신 Excel에서는 0.899999999999999를 표시합니다. (43.1-43.2)가 먼저 계산되기 때문에 -0.1이 임시로 저장되고 -0.1을 저장할 때의 오류가 계산에 포함됩니다.

값이 0에 도달할 때의 예

  1. Excel 95 이전 버전에서 다음을 새 통합 문서에 입력합니다.
       A1: =1.333+1.225-1.333-1.225
  2. A1 셀을 마우스 오른쪽 단추로 클릭한 다음 셀 서식을 클릭합니다. 숫자 탭의 범주에서 공학용을 클릭합니다. 소수 자릿수를 15로 설정합니다.
0을 표시하는 대신 Excel 95는 -2.22044604925031E-16을 표시합니다.

그러나 Excel 97에는 이 문제 수정을 시도하기 위한 최적화가 도입되었습니다. 덧셈이나 뺄셈 연산이 0에 매우 가까운 값이 될 경우 Excel 97 이상의 버전은 피연산자와 이진수 간에 변환하는 결과로 초래된 오류를 보정합니다. Excel 97 이상에서 위의 예를 수행하면 공학용 표기법으로 정확하게 0 또는 0.000000000000000E+00을 표시합니다. ? 자세한 내용은 다음 문서 번호를 클릭하여 Microsoft 기술 자료 문서를 참조하십시오.
172911 10을 아주 높은 거듭제곱이나 아주 낮은 거듭제곱으로 올리면 잘못된 결과 발생
214373 10을 아주 높은 거듭제곱이나 아주 낮은 거듭제곱으로 올리면 잘못된 결과 발생
부동 소수점 숫자와 IEEE 754 규정에 대한 자세한 내용은 다음 웹 사이트를 참조하십시오.
http://www.ieee.org

http://steve.hollasch.net/cgindex/coding/ieeefloat.html

참조

이 오류의 해결 방법에 대한 자세한 내용은 다음 문서 번호를 클릭하여 Microsoft 기술 자료 문서를 참조하십시오.
214118 부동 소수점 연산에서 반올림 오류를 수정하는 방법
참고 이것은 Microsoft 기술 지원 서비스 내에서 직접 작성한 “빠른 게시” 문서입니다. 여기에 포함된 정보는 발생한 문제에 대해 있는 그대로 제공됩니다. 이 문서는 즉시 참조할 수 있도록 빠르게 작성되어서 표기상의 오류가 포함되어 있을 수 있고 언제든지 예고 없이 수정될 수 있습니다. 기타 고려 사항은사용 약관을 참조하십시오. 정보

속성

기술 자료: 78113 - 마지막 검토: 2013년 2월 26일 화요일 - 수정: 1.0
본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft Excel 2013
  • Microsoft Excel 2010
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2011 for Mac
  • Microsoft Office Excel 2008 for Mac
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
키워드:?
kbinfo KB78113

피드백 보내기

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com