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の各種統計関数の処理時間

背景

大量のセルの統計量を算出する場合，関数によって処理時間が大きく異なる場合がある。

そこで，各種統計関数の処理時間を調べてみた。

検証環境

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

検証方法

以下の手順で検証した。

1. 約100万行×50列（=約5000万個）の乱数列を作成する。ただし，再計算されないように，値は固定する。
2.  5000万個のセルに対して，統計関数によって統計量を算出する。
3. Application.CalculateFull を実行するのに要する時間を，timeGetTime関数を利用して計測する。
4. 2.～3.を，各種関数ごとに実行，比較する。
5. ただし，キャッシュの効果などもあるので，どの関数をテストするかの順序については無作為実験を行う。また，Excel再起動ごとに2セットずつ測定し，計10回ずつの実験を行う。

結果

結果は下図のとおり。誤差棒は10回の標準偏差。

DEVSQ関数を除いては，計算内容から定性的に予想される結果とほぼ同じになった。
また，PERCENTILE.INC関数を使うと遅くなると感じていたのだが，それを裏付ける結果となった。
SUMSQとVAR.Sとの違いはあまりない。これに対し，DEVSQはVAR.Sと比較して大幅に遅い。理由は不明。定義上は，VAR.SとDEVSQの計算量は大して違わないはずであるが・・・。

Excel 2013のVAR.S関数とSTDEV.S関数の速度差

背景

STDEV.関数は，平方根をとるぶんだけ，VAR.S関数よりも処理に時間がかかることも考えられる。

そこで，速度を比較した。

検証環境

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

検証方法

以下の手順で検証した。

1. 2個の数値をセルに入力する。平方根の処理の差を際立たせるため，個数は少ないほうがよい。
2. VAR.S関数で2個の数値の分散をApplication.Calculatefullメソッドで10000回再計算する。
3. 同様に，STDEV.S関数で標準偏差を計算する。
4. 2，3の処理時間を比較する。

結果

有意な違いはなさそう。
処理時間（=速度）を気にすることなく，便利なほうを使えばよい。

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で標準偏差の計算結果が不正確になる例

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

確認環境

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