Excel で浮動小数点演算の結果が正しくない場合がある

概要

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

概要

Microsoft Excel は、IEEE 754 の仕様に基づいて設計され、浮動小数点数の格納方法と計算方法を決定していました。 IEEE は、その他の技術者が、コンピューターのソフトウェアとハードウェアの標準を決定する米国電気技術者およびエレクトロニクス技術者の技術者です。 754仕様は、バイナリコンピューターに浮動小数点数を格納する方法を説明する、非常に広く採用されている仕様です。 これは、浮動小数点数を適度な量の領域に格納し、相対的にすばやく発生する計算を可能にするため、広く使用されています。 754標準は、従来の PC ベースのマイクロプロセッサのほぼすべての浮動小数点単位および数値データプロセッサで使用されています。これは、Intel、Motorola、Sun、MIPS の各プロセッサを含む浮動小数点演算を実装しています。

数値が格納されている場合、対応するバイナリ番号は、すべての数値または小数を表すことができます。 たとえば、分数1/10 は10進数の値で0.1 として表現できます。 ただし、バイナリ形式の同じ数は次の繰り返し2進値になります。

0001100110011100110011 (以降)

これは無限に繰り返すことができます。 この数は、限定された (限られた) 容量では表現できません。 そのため、この数値は格納されている場合は、約 2.8 E-17 まで切り捨てられます。

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

  • 最大/最小制限
  • 精度
  • 繰り返しバイナリ番号

詳細

最大/最小制限

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

IEEE 754 に準拠するケース

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

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

  • 非正規化数値: 非正規化された数値は指数0で示されます。 その場合、数値全体が仮数に格納され、仮数には暗黙的な先行する1がありません。 その結果、精度が失われ、数値が小さいほど精度は失われます。 この範囲の小数点以下の数値は、1桁の精度になります。

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

    正規化されていない数値は基本的に、通常より小さい値を格納するようにするための回避策です。 正規化された数値が非常に多くの有効桁数を持つため、Microsoft はこのオプション部分を実装しません。 これにより、重大なエラーを計算に使用することができます。

  • 正/負の Infinities: Infinities は0で除算したときに発生します。 Excel では、infinities がサポートされていません。 #DIV/0! このような場合、エラーが発生します。

  • 非数 (NaN): NaN は、無効な操作 (無限大/無限大、無限大-無限大、-1 の平方根など) を表すために使用されます。 NaNs は、無効な操作を行ってからプログラムを続行することを許可します。 代わりに、Excel がすぐに #NUM のようなエラーを生成します。 または #DIV/0!.

精度

浮動小数点数は、65ビット範囲内の3つの部分 (符号、指数、および仮数) 内に2進で格納されます。 ||||| |---|---|---|---| |1個の符号ビット|11ビットの指数|1つの暗黙的ビット|52ビット仮数|

符号は数値の符号 (正または負) を格納します。指数は、数値が発生したときの2のべき乗を格納します ([2 の最大値] は + 1023 と-1022)。仮数には実際の数が格納されます。 仮数の有限記憶領域は、隣接する2つの浮動小数点数値を (精度) に制限します。

仮数と指数は、どちらも別個のコンポーネントとして格納されます。 その結果、操作対象の数値 (仮数) のサイズに応じて、可能な桁数が変わることがあります。 Excel の場合、Excel は 1.79769313486232 E308 から 2.2250738585072 E 308 の数値を格納できますが、これを実行できるのは15桁の精度のみです。 この制限は、厳密に IEEE 754 仕様に従うことによる直接的な結果であり、Excel の制限ではありません。 この精度レベルは、他のスプレッドシートプログラムでも同様に検出されます。

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

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

分母は数値の正規化された小数部です。指数部は、先頭のビットが常に1であるように調整されるため、正規化されます。 この方法では、保存する必要はありません。さらに、もう1つの精度が得られます。 これは、暗黙のビットが存在する理由です。 これは科学的表記法に似ていますが、指数を操作して小数点の左側に1桁の数字を指定します。binary の場合を除き、常に指数を操作して、最初のビットが1であるようにすることができます。1と0があるからです。

Bias は負の指数を格納しないようにするために使用されるバイアス値です。 倍精度浮動小数点型の値のバイアスは、127および 1023 (10 進数) です。 Excel は、倍精度の数値を格納します。

非常に大きな数の使用例

新しいブックに、次のように入力します。

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

セル C1 の結果の値は、1.2 E + 200 で、セル A1 と同じ値になります。 実際には、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 では、丸め誤差を補正するための2つの基本的な方法があります。これは、ROUND 関数と精度を表示するか、または [表示形式として有効にする] オプションです。

方法 1: ROUND 関数

次の例では、前のデータを使用して、ROUND 関数を使用して数値を5桁にします。 これにより、結果を別の値と正常に比較できます。

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5) 

この結果、 1.2 e + 200になります。

D1: = IF (C1 = 1.2 E + 200、TRUE、FALSE)

この結果、値は TRUE になります。

方法 2: 表示桁数で精度を高める

場合によっては、* * 精度で表示される * * オプションを使用して、丸め誤差が作業に影響しないようにすることができます。 このオプションを使用すると、ワークシート内の各番号の値は、表示された値になります。 このオプションをオンにするには、次の手順を実行します。

  1. [ファイル] メニューの [オプション] をクリックし、[詳細設定] カテゴリをクリックします。
  2. [このブックを計算する] セクションで、対象のブックを選択し、[ 表示桁数で精度を設定する] チェックボックスをオンにします。

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

連続する2進数値と、ほぼゼロの結果の計算

バイナリ形式で浮動小数点数の格納に影響を与えるもう1つの混乱のある問題として、有限数の数字、10進数で繰り返されていない数の数字があります。 最も一般的な例として、値0.1 とそのバリエーションが挙げられます。 これらの数値は、底10では完全に表現できますが、仮数に格納されている場合、バイナリ形式の同じ番号は次の繰り返しバイナリ番号になります。 

000110011001100110011 (以降)

IEEE 754 仕様では、任意の番号に対して特別手当は使用されません。 このメソッドは、仮数にあるものを格納し、残りの部分を切り捨てます。 この結果、保存されたときに、約 2.8 E-17 または0.000000000000000028 のエラーが発生します。

10進数の0.0001 などの一般的な小数部を、バイナリで正確に表現することはできません。 (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 を表す小さなエラーは、sum に伝達されます。 

例: 負の数の追加

  1. 新しいブックに、次のように入力します。

    A1: = (43.1-43.2) + 1

  2. セル A1 を右クリックし、[セルの書式設定] をクリックします。 [数値] タブで、[分類] の下の [指数] をクリックします。 小数点以下の桁数を15に設定します。

0.899999999999999 は、0.9 を表示する代わりに、Excel に表示されます。 最初に (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.22044604925031 E-16

ただし、Excel 97 では、この問題に対して修正を試みる最適化が導入されています。 加算または減算の操作によって値が0になるか、または非常に近い場合、Excel 97 以降では、オペランドをバイナリとの間で変換した結果として発生したエラーが補正されます。 次の例は、Excel 97 で実行された場合に、指数表記で0または 0.000000000000000 E + 00 を正確に表示します。

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