浮動小数点演算が Excel で不正確な結果をもたらす可能性がある
まとめ
この記事では、Microsoft Excel が浮動小数点数を格納および計算する方法について説明します。 これは、丸めまたはデータの切り捨てのために、一部の数値または数式の結果に影響を与える可能性があります。
概要
Microsoft Excel は、浮動小数点数の格納方法と計算方法を決定するために IEEE 754 仕様に基づいて設計されました。 IEEE (Institute of Electrical and Electronics Engineers) は、特にコンピューター ソフトウェアとハードウェアの標準を決定する国際機関です。 754 仕様は、浮動小数点値をバイナリ コンピューターに格納する方法を記述する広く採用されている仕様です。 これは、浮動小数点値を適切な量の領域に格納し、計算が比較的迅速に行われることを可能にするため、一般的です。 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 では-0 という概念がありません)。
- オーバーフロー: オーバーフローは、数値が大きすぎて表現できない場合に発生します。 この場合、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 を使用すると、プログラムは無効な操作を超えて続行できます。 代わりに、
#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: 表示桁数で計算
場場合によっては、[表示桁数で計算] オプションを使用して、丸め誤差が作業に影響を与えるのを防ぐことができる場合があります。 このオプションは、シートの各数値の値を強制的に表示値にします。 このオプションをオンにするには、次の手順を実行します。
- [ファイル] メニューの [オプション] をクリックし、[詳細設定] カテゴリをクリックします。
- [次のブックを計算するとき] セクションで、目的のブックを選択し、[表示桁数で計算する] チェック ボックスをオンにします。
たとえば、小数点以下 2 桁を表示する数値形式を選択し、[表示桁数で計算] オプションをオンにすると、ブックを保存するときに小数点以下 2 桁を超えるすべての精度が失われます。 このオプションは、すべてのシートを含むアクティブなブックに影響します。 このオプションを元に戻して、失われたデータを回復することはできません。 このオプションを有効にする前に、ブックを保存することをお勧めします。
結果がほぼ 0 の 2 進数と計算を繰り返す
二項形式の浮動小数点数の格納に影響を与えるもう 1 つの混乱する問題は、10 進数の底 10 の数値を繰り返していない有限の数値が無限であり、2 進数で数値を繰り返していることです。 この最も一般的な例は、値 0.1 とその変動です。 これらの数値は基数 10 で完全に表すことができますが、仮数に格納されている場合、バイナリ形式で同じ数値が次の繰り返し 2 進数になります。
000110011001100110011 (など)
IEEE 754 仕様では、任意の数値を特別に許可していません。 これにより、仮数に格納できる値が格納され、残りは切り捨てられます。 これにより、格納時に約 -2.8E-17、または 0.00000000000000028 というエラーが発生します。
10 進数 0.0001 などの一般的な小数部であっても、バイナリで正確に表すことはできません。 (0.0001 は、104 ビットの周期を持つ繰り返しの 2 進小数です)。 これは、分数 1/3 を 10 進数で正確に表現できない理由と似ています (繰り返し 0.333333333333333)。
たとえば、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 進数で表す際の小さな誤差は、合計に伝播します。
例: 負の数値を追加する
新しいブックに次の情報を入力します。
A1: =(43.1-43.2)+1
セル A1 を右クリックし、[セルの書式設定] をクリックします。 [数値] タブで、[分類] の下の [指数] をクリックします。 [小数点以下の桁数] を 15 に設定します。
0.9 を表示する代わりに、Excel は 0.899999999999999 を表示します。 (43.1-43.2) が最初に計算されるため、-0.1 が一時的に格納され、-0.1 の格納によるエラーが計算に導入されます。
値が 0 に達した場合の例
Excel 95 以前では、新しいブックに次のように入力します。
A1: =1.333+1.225-1.333-1.225
セル 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 サイトを参照してください。