読者です 読者をやめる 読者になる 読者になる

About connecting the dots.

statistics/machine learning adversaria.

Excelの分布関数まとめ

分布関数

前回の記事で,いくつかの分布について紹介しましたが,実はこれらの確率分布ってエクセルでもさくっと計算できたりします.そんなわけで,エクセルに入ってる分布関数についてざっくりまとめてみました*1丸め誤差とか怖いので,精緻なシミュレーションするのにはどうかと思いますけれども,ざっくりやるだけならエクセルでもいいんじゃないですかね,とか思ったりします.

分布名 エクセル関数名
二項分布 BINOMDIST(x,試行回数,成功率,形式)*2
負の二項分布 NEGBINOMDIST(x,成功数,成功率)
ベータ分布 BETADIST(x,\alpha,\beta)
ポアソン分布 POISSON(x,平均,形式)
ガンマ分布 GAMMADIST(x,\alpha,\beta)
超幾何分布 HYPGEOMDIST(x,標本数,母集団内成功数,母集団数)
正規分布 NORMDIST(x,平均,標準偏差,形式)
対数正規分布 LOGNORMDIST(x,平均,標準偏差)
カイ二乗分布 CHIDIST(x,自由度)
F分布 FDIST(x,自由度1,自由度2)
t分布 TDIST(x,自由度,端指定)*3
指数分布 EXPONDIST(x,\lambda,形式)

確率分布に従ったランダムサンプル生成

上でまとめたのは,確率密度関数の値を返すものでした.しかし実際にシミュレーションをする場合,上記の確率分布に従ったランダムサンプル生成をしたくなります.しかしエクセルには,Rでいうrnorm()みたいな便利なサンプル生成関数はありません.なので,いくつかの分布について,ランダムサンプルを生成するためのスニペットをまとめてみました*4

分布名 エクセルスニペット
正規分布 =NORMINV(RAND(),平均値,標準偏差)*5
二項分布 =IF(RAND()>成功確率,1,0)
ベータ分布 =BETAINV(RAND(),\alpha,\beta)
ガンマ分布 =GAMMAINV(RAND(),\alpha,\beta)

ベータ分布でのランダムサンプル生成の例

実際にエクセルの例を示すと,以下のように,セルに値を入力します.

セル
B1 \alphaの値を直接入力
B2 \betaの値を直接入力
A4-A103 BETAINV(RAND(),$B$1,$B$2)
C4-C14 ヒストグラムの範囲を直接入力
D4-D14 =FREQUENCY(A4:A103,C4:C14)*6

するとシミュレーション結果が得られます.簡単ですね*7

f:id:SAM:20140430153152p:plain

*1:Rでの分布関数は,RjpWikiにまとまっています

*2:形式については,すべての関数共通で,TRUEなら累積分布関数,FLASEなら確率密度関数を返します

*3:1だと片側分布の値,2だと両側分布の値を返します

*4:ポアソン分布の乱数生成はVBA使わないといけなさそうで,面倒なのでまとめてません

*5:このあたりの逆関数を使ったやり方の意味については,こちらの記事をみていただければわかりやすいです.

*6:ヒストグラムの作り方はこちらを参照してください

*7:ただしこのやり方,どこかしらのセルを変更するたびにRANDOM()の再計算が走るので,若干うざい...