Excel 2013で数式のカッコの有無で計算結果が変わる例

背景

Excelの演算誤差（三重大学 奥村研究室）で紹介されている記事によれば，

Excel 2000 でも .5 - .4 - .1 は 0 になるのに 1 * (.5 - .4 - .1) は -2.78E-17 になる。 いったいどのようなアルゴリズムを使っているのであろうか。
・・・ だんだんわかってきた。 トップレベルが引き算の場合は特別なことをするようだ。 ・・・
・・・ 1 をかけないでも引き算全体をかっこでくくるだけで例外処理をしなくなることを教えていただいた。・・・

ということである。

同じ内容をExcel 2013で試してみた。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. A1セルに =0.5 - 0.4 - 0.1 を入力する。
2. A2セルに =1 * (0.5 - 0.4 - 0.1) を入力する。
3. A3セルに =(0.5 - 0.4 - 0.1) を入力する。
4. A1～A3セルの書式を 0.00000000000000E+00 に設定する。
5. A1～A3セルの値を確認する。

結果

結果は以下のとおり。Excel 2013でもExcel 2000と同様の結果になった。う～む。

• A1セルに =0.5 - 0.4 - 0.1 を入力 → 0.00000000000000E+00
• A2セルに =1 * (0.5 - 0.4 - 0.1) を入力 → -2.77555756156289E-17
• A3セルに =(0.5 - 0.4 - 0.1) を入力 → -2.77555756156289E-17

Excelの精度関係リンク集

背景

一般のコンピュータによる計算結果と同様に，Excelによる計算結果ももちろん不正確である。

関連するリンクをまとめておく。

• Excel で浮動小数点演算の結果が正しくない場合がある（Microsoft）
• Excelの演算誤差（三重大学 奥村研究室）
• Excel使うな（Okumura's Blog）
• Excel使うな：2010版（Okumura's Blog）
• Excelが計算ミス（Okumura's Blog）
• Excelでの数値表現と計算精度（神奈川大学 経済学部）
• Excel:エクセルの計算精度（ガスコン研究所）
• “達人”芳坂和行氏に学ぶ、エクセル「演算誤差」対策講座（日経 PC online）
• Numeric precision in Microsoft Excel（Wikipedia英語版）

Excel 2013のLOG10関数の精度

背景

ある2個の数値の一致桁数を評価するときに，両者の相対誤差の常用対数をとる。このため，Excel上ではLOG10関数を利用している。しかし，一致桁数云々を議論する前に，そもそも，この関数の精度が重要である。

一方，CASIOの高精度計算サイト（50桁での計算が可能）にも，LOG関数という関数があり，常用対数を計算できる。

そこで，メタ議論になってしまうが，一致桁数評価ツールとしてのLOG10関数，LOG関数について，両者の結果を比較し，一致桁数を評価した。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. 相対誤差に基づいて一致桁数を評価するとき，相対誤差は概ね10^-16から10^+2までの範囲に収まる。そこで，この範囲の数値を等比的に分割して数列を作成する。10倍区間を100分割して得た等比数列の値をxとする。
2. ExcelのLOG10関数を利用して，xの常用対数を計算する。
3. CASIOの高精度計算サイトで，「桁数50桁」，「精度保証あり」でxの常用対数を計算する。
4. ただし，この結果を適切にExcelに貼り付けるには有効桁数を15桁にしておく必要があるので，高精度計算サイト側でプログラムを自作し，最終結果の有効桁数を15桁にする。
5. 手順2.の結果と手順4.の結果をExcel上で比較し，一致桁数を評価する。
6. 同様にして，相対誤差は概ね10^-1から10^+1までの範囲で，10倍区間を999分割して一致桁数を評価する。なお，1000分割ではなく，999分割にしたのは，CASIOの高精度計算サイトの制約による。

結果

結果は下図のとおり。

• 10^-16≦x≦10^+2の範囲で，一致桁数はほぼ14桁以上であった。
• ただし，x=1の近辺で一致桁数が低下するようなので，上記手順6のとおり，より細かくみてみると，一致桁数は13.5桁まで低下している。
• さらに細かくみれば，一致桁数がさらに低下する可能性もあるが，重要なのは x≦10^-1の領域なので，大きな問題にはならないであろう。

Excel 2013でコーシー分布のパーセント点を計算したときの精度

背景

コーシー分布のパーセント点を求めるには，分布関数の逆関数が必要である。この関数はExcelには実装されていないが，TAN関数を利用すれば計算できる。

一方，CASIOの高精度計算サイト（50桁での計算が可能）には，cauchyicdlower関数という関数があり，コーシー分布のパーセント点を直接計算できる。

そこで，両者の結果を比較し，一致桁数を評価した。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. ExcelのTAN関数を利用してコーシー分布のパーセント点を計算する。
2. CASIOの高精度計算サイトで，「桁数50桁」，「精度保証あり」でコーシー分布のパーセント点（cauchyicdlower関数）を計算する。
3. ただし，この結果を適切にExcelに貼り付けるには有効桁数を15桁にしておく必要があるので，高精度計算サイト側でプログラムを自作し，最終結果の有効桁数を15桁にする。
4. 1.の結果と3.の結果をExcel上で比較し，一致桁数を評価する。

結果

結果は下図のとおり。

• 下側確率で1%以上99%以下の領域では，一致桁数はほぼ14桁以上であった。
• それ以外の領域では一致桁数は低下した。

Excel 2013で標準正規分布のパーセント点（NORM.S.INV関数）を計算したときの精度

背景

Excelの統計関数は，何かとタタかれることが多い。

標準正規分布のパーセント点を求めるには，分布関数の逆関数が必要である。この関数はExcelではNORM.S.INV関数として実装されている。

一方，CASIOの高精度計算サイト（50桁での計算が可能）には，normalicdlower関数という関数があり，標準正規分布のパーセント点を直接計算できる。

そこで，両者の結果を比較し，一致桁数を評価した。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. ExcelのNORM.S.INV関数を利用して標準正規分布のパーセント点を計算する。
2. CASIOの高精度計算サイトで，「桁数50桁」，「精度保証あり」で標準正規分布のパーセント点（normalicdlower関数）を計算する。
3. ただし，この結果を適切にExcelに貼り付けるには有効桁数を15桁にしておく必要があるので，高精度計算サイト側でプログラムを自作し，最終結果の有効桁数を15桁にする。
4. 1.の結果と3.の結果をExcel上で比較し，一致桁数を評価する。
5. ついでに， NORM.S.INV関数と，その互換性関数であるNORMSINV関数との結果も比較する。

結果

結果は下図のとおり。

• 下側確率で0%以上99.99%以下の領域では，一致桁数はほぼ14桁以上であった。
• それを超えると一致桁数は減少した。
• なお，NORM.S.INV関数と，その互換性関数であるNORMSINV関数は，すべての領域で一致桁数は15桁であった。Microsoftによれば，新しい関数のほうがより精度が高いとのことであるが，NORM.S.INV関数に関しては精度の向上はないようである。

Excel 2013のVAR.S関数から求めた標準偏差と，STDEV.S関数から求めた標準偏差と一致桁数の比較

背景

標準偏差は，VAR.S関数の平方根をとって算出することもできる。

そこで，VAR.S関数を利用して標準偏差を求めた場合と，STDEV.S関数で標準偏差を直接求めた場合とで一致桁数を比較した。

検証環境

Excel 2013（64bit） + Windows 10 Pro（64bit）
CPU: Core i7 2.4GHz
RAM: 16GB
仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. 標本数nの一様乱数を生成する。
2. VAR.S関数で分散を計算する。
3. その平方根をとって，標準偏差とする。
4. この結果と，生データから直接，STDEV.S関数で求めた結果との一致桁数を算出する。

結果

いくつかの条件で試したところ，条件によらず，15桁まで一致した。
平方根をとるだけなので，さすがに内部で特殊なことはしていないということか?

VAR.P関数，STDEV.P関数についても同様の検証を行ったが，同じ結果であった。

Excel 2013のDEVSQ関数から求めた分散と，VAR.S関数から求めた分散との一致桁数の比較

背景

分散は，DEVSQ関数を利用して算出することもできる。

そこで，DEVSQ関数を利用して分散を求めた場合と，VAR.S関数で分散を直接求めた場合とで一致桁数を比較した。

検証環境

Excel 2013（64bit） + Windows 10 Pro（64bit）
CPU: Core i7 2.4GHz
RAM: 16GB
仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. 標本数nの一様乱数を生成する。
2. DEVSQ関数で偏差平方和を計算する。
3. n-1で割って，分散とする。
4. この結果と，生データから直接，VAR.S関数で求めた結果との一致桁数を算出する。

結果

 いくつかの条件で試した中で，一致の比較的悪かったものは下図のとおり。11桁は一致するとみてよさそうである。
Excelでの実際のアルゴリズムがよくわからないので，これ以上の考察はしない。

VAR.P関数についても同様の検証を行ったが，結果はほぼ同様であった。

Excel 2013でMOD関数を利用して小数点以下の桁数を取り出すときに問題になる例

1. A1セルに 1.001 と入力する。
2. B1セルに数式を =MOD(A1,1) と入力する。
3. B1セルをコピーして，C1セルに値のみ貼り付ける。
4. C1セルの値は，0.00099999999999989 となっている。本当は 0.001 となってほしいのだが・・・。

この現象は，MOD関数を利用して小数点以下の桁数（この例では3桁）を調べようとする場合に問題となる。

これまで試した範囲では，小数点以下の桁数を調べるには，数値としてではなく文字列として処理するのがよさそうだ。

確認環境

Excel 2013（64bit） + Windows 10 Pro（64bit）

Excel 2013で標準偏差の計算結果が不正確になる例

STDEV関数またはSTDEV.S関数を使って，100.1，100.1，100.1 の3個の数値の標準偏差を計算すると，0にならない。

確認環境

Excel 2013（64bit） + Windows 10 Pro（64bit）

Excel2013の「連続データの作成」で，結果が不正確になる例

1. 任意のセルに -0.1 と入力する。 
2. 「ホーム」 → 「フィル」 → 「連続データの作成」で，以下のように入力する。
   範囲: 列
   種類: 加算
   増分値: 0.01
   停止値: 1
3. OKをクリックする。
4. -0.06，-0.05などのところで，値が不正確になっている。浮動小数計算の結果なので，驚くことではないが・・・。

これと類似の事例で次のようなケースもある。

1. A1セルに -1 と入力する。
2. A2セルに数式を =A1+0.1 と入力する。
3. A2セルをコピーする。
4. A3セル以下の数十個のセルを選択し，貼り付ける。
5. A11セルは，値が0になってほしいが，0にならず，-1.38777878078145E-16 となってしまう。これに影響されるためか，A12セルでは，値が0.1になってほしいが 0.0999999999999999 になってしまう。それ以降，しばらくはよいが，A71セルで，値が6になってほしいが，5.99999999999999  になってしまう。

確認環境

Excel 2013（64bit） + Windows 10 Pro（64bit）