浮動小数点演算を使用すると、計算結果が不正確Excel

注意

Office 365 用リソース は、 エンタープライズ向け Microsoft 365 アプリに名前変更されています。 この変更の詳細については、 このブログの投稿を参照してください。

概要

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

概要

Microsoft Excelは、IEEE 754 仕様を中心に設計され、浮動小数点数の格納方法と計算方法を決定しました。 IEEE は、コンピューター ソフトウェアとハードウェアの標準を決定する国際的な機関である電気電子工学研究所です。 754 仕様は、浮動小数点数をバイナリ コンピューターに格納する方法を記述する非常に広く採用されている仕様です。 浮動小数点数を適度な量のスペースに格納し、計算を比較的迅速に行えるので、一般的です。 754 標準は、Intel、Motorola、Sun、MIPS プロセッサを含む浮動小数点演算を実装する、今日の PC ベースのマイクロプロセッサーのほぼすべての浮動小数点単位と数値データ プロセッサで使用されます。

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

0001100110011100110011 (など)

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

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

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

詳細情報

最大/最小の制限

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

IEEE 754 に準拠しているケース

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

IEEE 754 に準拠していないケース

  • 非正規化された数値: 非正規化された数値は、0 の指数で示されます。 その場合は、数値全体がマンティッサに格納され、mantissa には暗黙的な先頭 1 はありません。 その結果、精度が失われ、数値が小さいほど精度が失われます。 この範囲の小さな末尾の数値の精度は 1 桁のみです。

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

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

  • 正/負の Infinities: 0 で除算すると、インフィニティが発生します。 Excelの機能はサポートされていませんが、代わりに、#DIV/0 を提供します。 これらの場合はエラーです。

  • Not-a-Number (NaN): NaN は、無効な操作 (無限大、無限大、-1 の平方根など) を表す場合に使用されます。 NaN を使用すると、プログラムは無効な操作を過ぎ続ける可能性があります。 Excel代わりに、エラーなどのエラーがすぐに#NUM! または #DIV/0!

精度

浮動小数点数は、符号、指数、および mantissa の 65 ビット範囲内の 3 つの部分にバイナリで格納されます。

記号 指数 mantissa
1 符号ビット 11 ビット指数 1 暗黙のビット + 52 ビットの分数

符号には、数値の符号 (正または負) が格納され、指数には、数値の上げまたは下げの 2 の電力が格納されます (2 の最大/最小電力は +1,023 と -1,022)、mantissa には実際の数値が格納されます。 mantissa の有限格納域は、隣接する 2 つの浮動小数点数の近さ (つまり精度) を制限します。

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

浮動小数点数は次の形式で表され、指数はバイナリ指数です。

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

分数は数値の正規化された小数部で、指数は先頭ビットが常に 1 に調整されます。 この方法では、格納する必要が生じ、もう 1 ビットの精度が得されます。 これは、暗黙的なビットがある理由です。 これは、指数を操作して小数点の左側に 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) などの IF 関数を使用してセル A1 と C1 を比較すると、結果は TRUE になります。 これは、15 桁の有効桁数のみを格納する IEEE 仕様によって発生します。 上記の計算を格納するには、Excel 100 桁以上の精度が必要になります。

非常に小さい数値を使用する例

新しいブックに次の情報を入力します。

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

セル C1 の結果の値は、1.00012345678901 ではなく 1.000123456789012345 になります。 これは、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 進数形式の浮動小数点数の格納に影響を与えるもう 1 つのわかりにくい問題は、10 進数ベース 10 で有限で繰り返しでない数値の中には、バイナリで繰り返される数値が無限である点です。 この最も一般的な例は、値 0.1 とそのバリエーションです。 これらの数値は、基本 10 で完全に表現することができますが、バイナリ形式の同じ数値は、その数値が mantissa に格納されている場合、次の繰り返しバイナリ番号になります。

000110011001100110011 (など)

IEEE 754 仕様では、任意の数に特別な制限はありません。 これは、mantissa に格納できる値を格納し、残りの部分を切り捨てします。 これにより、格納時に -2.8E-17、または 0.00000000000000000028 のエラーが発生します。

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.9999999999999996 が出力として出力されます。 バイナリで 0.0001 を表す小さなエラーは、合計に伝達されます。

例: 負の数値を追加する

  1. 新しいブックに次の情報を入力します。

    A1: =(43.1-43.2)+1

  2. セル A1 を右クリックし、[セルの書式設定] をクリックします。 [数値] タブで、[カテゴリ] の下の [科学] をクリックします。 Decimal の 桁数を 15 に設定します。

0.9 を表示する代わりに、Excel 0.899999999999999 が表示されます。 (43.1-43.2) が最初に計算されるので、-0.1 が一時的に格納され、-0.1 を格納するエラーが計算に導入されます。

値が 0 に達した場合の例

  1. 95 Excel前に、新しいブックに次の情報を入力します。

    A1: =1.333+1.225-1.333-1.225

  2. セル A1 を右クリックし、[セルの書式設定] をクリックします。 [数値] タブで、[カテゴリ] の下の [科学] をクリックします。 Decimal の 桁数を 15 に設定します。

0 を表示する代わりに、Excel 95 は -2.22044604925031E-16 を表示します。

Excel 97 では、この問題を修正しようとする最適化が導入されました。 加算演算または減算演算の結果、値が 0 または非常に近い場合、Excel 97 以降は、オペランドをバイナリに変換した結果として発生したエラーを補います。 Excel 97 以降で実行した場合の上記の例では、0 または 0.000000000000000E+00 が科学的表記で正しく表示されます。

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