浮動小数点演算が Excel で不正確な結果をもたらす可能性がある

  • [アーティクル]
  • 適用対象:
    Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

概要

この記事では、Microsoft Excel が浮動小数点数を格納および計算する方法について説明します。 これは、丸めまたはデータの切り捨てのために、一部の数値または数式の結果に影響を与える可能性があります。

概要

Microsoft Excel は、浮動小数点数の格納方法と計算方法を決定するために IEEE 754 仕様に基づいて設計されました。 IEEE (Institute of Electrical and Electronics Engineers) は、特にコンピューター ソフトウェアとハードウェアの標準を決定する国際機関です。 754 仕様は、浮動小数点数を 2 進コンピューターに格納する方法を説明する非常に広く採用されている仕様です。 浮動小数点数を適度な量のスペースに格納でき、計算を比較的迅速に実行できるため、一般的です。 754 標準は、Intel、Motorola、Sun、MIPS プロセッサを含む、浮動小数点演算を実装する今日の PC ベースのマイクロプロセッサのほぼすべての浮動小数点ユニットおよび数値データ プロセッサで使用されています。

数値が格納される場合、対応する 2 進数はすべての数値または小数を表すことができます。 たとえば、小数 1/10 は、10 進数システムで 0.1 として表すことができます。 ただし、2 進数形式の同じ数値は、次の 2 進化 10 進数の繰り返しになります。

0001100110011100110011 (など)

これは無限に繰り返すことができます。 この数値は、有限の (制限された) スペースで表すことはできません。 したがって、この数値は、格納時に約 -2.8E-17 で切り捨てられます。

ただし、IEEE 754 仕様には、次の 3 つの一般的なカテゴリに分類されるいくつかの制限があります。

  • 最大/最小の制限
  • 精度
  • 2 進数の繰り返し

詳細情報

最大/最小の制限

すべてのコンピューターには、処理できる最大数と最小数があります。 数値が格納されるメモリのビット数は有限であるため、格納できる最大または最小の数値も有限になります。 Excel の場合、格納できる最大数は 1.79769313486232E+308 であり、格納できる最小の正数は 2.2250738585072E-308 です。

IEEE 754 に準拠している場合

  • アンダーフロー: アンダーフローは、表現するには小さすぎる数値が生成されたときに発生します。 IEEE と Excel では、結果は 0 になります (IEEE には -0 の概念があり、Excel にはないという例外があります)。
  • オーバーフロー: オーバーフローは、数値が大きすぎて表現できない場合に発生します。 この場合、Excel は独自の特別な表現 (#NUM!) を使用します。

IEEE 754 に準拠していない場合

  • 非正規化数: 非正規化数は、指数 0 で示されます。 その場合、数値全体が仮数に格納され、仮数には暗黙の先頭 1 がありません。 その結果、精度が低下し、数値が小さいほど精度が低下します。 この範囲の小さい方の数値の精度は 1 桁のみです。

    例: 正規化数には、暗黙の先頭 1 があります。 たとえば、仮数が 0011001 を表す場合、暗黙の先頭に 1 が含まれているため、正規化数は 10011001 になります。 非正規化数には暗黙の先頭数がないため、この 0011001 の例では、非正規化数は同じままです。 この場合、正規化数には有効桁数 8 桁 (10011001) があり、非正規化数には有効桁数 5 桁 (11001) があり、先行 0 は有効桁数ではありません。

    非正規化数は基本的に、通常の下限よりも小さい数値を格納できるようにするための回避策です。 非正規化数はその性質上、有効桁数が可変であるため、Microsoft は仕様のこのオプション部分を実装していません。 これにより、計算に重大なエラーが発生する可能性があります。

  • 正/負の無限大: 0 で割ると無限大が発生します。 Excel は無限大をサポートしていませんが、これらの場合に #DIV/0! エラーを提供します 。

  • 非数 (NaN): NaN は、無効な演算 (無限大/無限大、無限大-無限大、-1 の平方根など) を表すために使用されます。 NaN を使用すると、プログラムは無効な操作を超えて続行できます。 代わりに、Excel はすぐにエラー (#NUM! や #DIV/0! など) を生成します。

精度

浮動小数点数は、65 ビットの範囲内で、符号、指数、仮数の 3 つの部分に 2 進数で格納されます。

符号 指数 仮数
1 符号ビット 11 ビット指数 1 暗黙ビット + 52 ビット小数

符号には数値の符号 (正または負) が格納され、指数には数値を増減する 2 の累乗を格納され (2 の最大/最小累乗は +1,023 および -1,022)、仮数には実際の数値が格納されます。 仮数の有限の格納領域は、2 つの隣接する浮動小数点数の近さ (つまり精度) を制限します。

仮数と指数は、両方とも別々のコンポーネントとして格納されます。 その結果、可能な精度の量は、操作される数値 (仮数) のサイズによって異なる場合があります。 Excel の場合、Excel は 1.79769313486232E308 から 2.2250738585072E-308 までの数値を格納できますが、15 桁の精度でしか格納できません。 この制限は、IEEE 754 仕様に厳密に従った直接的な結果であり、Excel の制限ではありません。 このレベルの精度は、他のスプレッドシート プログラムにも見られます。

浮動小数点数は次の形式で表されます。ここで、指数は 2 進指数です。

X = 小数 * 2^(指数 - バイアス)

小数は、数値の正規化された小数部分であり、先頭ビットが常に 1 になるように指数が調整されるため、正規化されます。 このように、格納する必要はなく、もう 1 ビットの精度が得られます。 これが暗黙のビットがある理由です。 これは、指数を操作して小数点の左側に 1 桁になる指数表記に似ています。2 進数の場合を除いて、1 と 0 しかないため、最初のビットが 1 になるように指数をいつでも操作できます。

バイアスは、負の指数を格納する必要をなくすために使用されるバイアス値です。 単精度数のバイアスは 127 で、倍精度数のバイアスは 1,023 (10 進数) です。 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 の結果の値は、1.000123456789012345 ではなく 1.00012345678901 になります。 これは、有効桁数 15 桁の精度のみを格納するという IEEE 仕様が原因です。 上記の計算を格納するには、Excel で少なくとも 19 桁の精度が必要です。

精度エラーの修正

Excel には、丸め誤差を補正するための 2 つの基本的な方法があります。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. [ファイル] メニューの [オプション] をクリックし、[詳細設定] カテゴリをクリックします。
  2. [次のブックを計算するとき] セクションで、目的のブックを選択し、[表示桁数で計算する] チェック ボックスをオンにします。

たとえば、小数点以下 2 桁を表示する数値形式を選択し、[表示桁数で計算] オプションをオンにすると、ブックを保存するときに小数点以下 2 桁を超えるすべての精度が失われます。 このオプションは、すべてのシートを含むアクティブなブックに影響します。 このオプションを元に戻して、失われたデータを回復することはできません。 このオプションを有効にする前に、ブックを保存することをお勧めします。

結果がほぼ 0 の 2 進数と計算を繰り返す

2 進形式の浮動小数点数の格納に影響を与えるもう 1 つの紛らわしい問題は、10 進数の 10 を基数とする有限の非反復数である一部の数値が、2 進の無限の反復数である点です。 この最も一般的な例は、値 0.1 とその変動です。 これらの数値は基数 10 で完全に表すことができますが、仮数に格納される場合、2 進形式の同じ数値は次の繰り返し 2 進数になります。

000110011001100110011 (など)

IEEE 754 仕様では、任意の数値を特別に許可していません。 これにより、仮数に格納できる値が格納され、残りは切り捨てられます。 この結果、格納時に約 -2.8E-17、または 0.000000000000000028 のエラーが発生します。

10 進数 0.0001 などの一般的な小数でさえ、2 進数で正確に表すことはできません。 (0.0001 は、104 ビットの周期を持つ繰り返しの 2 進小数です)。 これは、小数 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 を 2 進数で表す際の小さな誤差は、合計に伝播します。

例: 負の数値を追加する

  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 または 0 に非常に近い場合、Excel 97 以降では、オペランドを 2 進数に変換した結果、または 2 進数から変換した結果として発生したエラーが補正されます。 上記の例を Excel 97 以降で実行すると、指数表記で 0 または 0.000000000000000E+00 が正しく表示されます。

浮動小数点数と IEEE 754 仕様の詳細については、次の World Wide Web サイトを参照してください。