概要
この記事では、Microsoft Excel で浮動小数点数を格納および計算する方法について説明します。 これは、丸めまたはデータの切り捨てにより、一部の数値または数式の結果に影響を与える可能性があります。
概要
Microsoft Excel は、IEEE 754 仕様を中心に設計され、浮動小数点数の格納方法と計算方法を決定しました。 IEEEは電気電子工学研究所であり、とりわけ、コンピュータソフトウェアとハードウェアの基準を決定する国際的な機関です。 754 仕様は、浮動小数点値をバイナリ コンピューターに格納する方法を記述する広く採用されている仕様です。 これは、浮動小数点値を適切な量の領域に格納し、計算が比較的迅速に行われることを可能にするため、一般的です。 754 標準は、Intel、Motorola、Sun、MIPS プロセッサを含む浮動小数点演算を実装する今日の PC ベースのマイクロプロセッサのほぼすべての浮動小数点ユニットと数値データ プロセッサで使用されます。
数値が格納されている場合、対応する 2 進数はすべての数値または小数部を表すことができます。 たとえば、分数 1/10 は 0.1 として 10 進数値システムで表すことができます。 ただし、バイナリ形式の同じ数値は、次の繰り返しバイナリ 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) があり、先頭のゼロは意味がありません。
非正規化された数値は、基本的に、通常の下限よりも小さい数値を格納できるようにするための回避策です。 Microsoft では、仕様のこの省略可能な部分を実装していません。その性質上、非正規化された数値には有効桁数が可変であるためです。 これにより、計算に大きなエラーが発生する可能性があります。
正/負の無限大: 0 で除算すると無限大が発生します。 Excel では無限大はサポートされません。代わりに、#DIV/0 が提供されます。 このような場合にはエラーが発生します。
Not-a-Number (NaN): NaN は、無効な操作 (無限大/無限大、無限大-無限大、-1の平方根など) を表すために使用されます。 NaN を使用すると、プログラムは無効な操作を超えて続行できます。 代わりに、
#NUM!や#DIV/0!などのエラーがすぐに生成されます。
精度
浮動小数点数は、符号、指数、仮数の 65 ビット範囲内の 3 つの部分でバイナリに格納されます。
| 看板 | 指数 | 仮数 |
|---|---|---|
| 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 桁の数字を持つ科学的表記に似ています。バイナリを除き、1 と 0 しかないため、最初のビットが 1 になるように指数を常に操作できます。
バイアスは、負の指数を格納する必要を回避するために使用されるバイアス値です。 単精度数値のバイアスは、倍精度数値の場合は 127 と 1,023 (10 進数) です。 Excel は倍精度を使用して数値を格納します。
非常に大きな数値を使用する例
新しいブックに次のように入力してください。
A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
セル C1 の結果の値は 1.2E+200 になり、セル A1 と同じ値になります。 実際、IF 関数 (IF(A1=C1 など) を使用してセル A1 と C1 を比較すると、結果は TRUE になります。 これは、精度の有効桁数が15桁に限られるというIEEEの仕様が原因です。 上記の計算を格納できるようにするには、Excel には少なくとも 100 桁の有効桁数が必要です。
非常に小さい数値を使用する例
新しいブックに次の内容を入力します。
A1: 0.000123456789012345
B1: 1
C1: =A1+B1
セル C1 の結果の値は、1.0001234567890123456789012345 ではなく 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 進数と計算の繰り返し
浮動小数点数を2進数形式で格納する際に生じるもう1つの混乱する問題は、10進数で有限かつ非循環の数値が、2進数では無限の循環数になることです。 この最も一般的な例は、値 0.1 とそのバリエーションです。 これらの数値は10 進数で完全に表すことができますが、仮数部に格納されると、2 進形式で同じ数値が次のような繰り返し 2 進数になります。
000110011001100110011 (など)
IEEE 754 仕様では、任意の数に対して特別な制限はありません。 それは仮数部にできる限りを格納し、残りを切り捨てます。 これにより、格納時に約 -2.8E-17、または 0.00000000000000028 というエラーが発生します。
10 進数 0.0001 などの一般的な小数部であっても、バイナリで正確に表すことはできません。 (0.0001 は、104 ビットのピリオドを持つ繰り返しバイナリ分数です)。 これは、分数 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.99999999999996 が出力されます。 バイナリで 0.0001 を表す小さなエラーが合計に反映されます。
例: 負の数を追加する
新しいブックに次の内容を入力します。
A1: =(43.1-43.2)+1
セル A1 を右クリックし、[セルの 書式設定] をクリックします。 [数値] タブで、[カテゴリ] の [科学] をクリックします。 小数点以下の 桁数 を 15 に設定します。
0.9 を表示する代わりに、Excel では 0.89999999999999999 が表示されます。 (43.1-43.2) が最初に計算されるため、-0.1 は一時的に格納され、-0.1 の格納からのエラーが計算に導入されます。
値が 0 に達した場合の例
次の内容を新しいブックに入力します（Excel 95 以前のバージョン）。
A1: =1.333+1.225-1.333-1.225
セル A1 を右クリックし、[セルの 書式設定] をクリックします。 [数値] タブで、[カテゴリ] の [科学] をクリックします。 小数点以下の 桁数 を 15 に設定します。
Excel 95 では、0 ではなく -2.22044604925031E-16 が表示されます。
ただし、Excel 97 では、この問題の修正を試みる最適化が導入されました。 加算演算または減算演算の結果、値が 0 または非常に近い場合、Excel 97 以降は、オペランドをバイナリとの間で変換した結果として発生したエラーを補正します。 Excel 97 以降で実行した場合の上記の例では、0 または 0.0000000000000000E+00 が指数表記で正しく表示されます。
浮動小数点数と IEEE 754 仕様の詳細については、次の World Wide Web サイトを参照してください。