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の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関数についても同様の検証を行ったが，結果はほぼ同様であった。