Excelの統計分布系の関数の話 1
Excelを使っていて、これまで集計したデータを元に予測分析したくなることがあります。
しかし、統計に関して出回っている書籍の多くが「標準偏差」から「回帰分析」あたりの関数までを主な範囲としていて、「統計」カテゴリの関数をあまり使う機会はありませんでした。
今回は、自分自身のデータの分析と予測で必要となった「ポワソン分布」を初めとして、以下の簡単な解説を掲載します。
免責
- 初版は、公式との対応、もしくはパラメータの解釈のみです
- 近似できる部分などの「証明」については、追って掲載します
- 数学としての正確な書き方はしておりません
取り扱う統計関数
- ポワソン分布 (POISSON.DIST)
- ガンマ分布 (GAMMA.DIST)
- 負の二項分布(NEGBINOM.DIST)
基本的に～.DISTとつくものは、結果の値は $0 \leq y \leq 1$ の小数値で返され、書式を%に合わせると良いです。
サンプルデータ
| a | b |
|---|---|
| 2 | 0 |
| 3 | 1 |
| 2 | 1 |
| 2 | 0 |
| 3 | 1 |
| 1 | 1 |
後で使う値 $\lambda$ は以下の通りとします。
\begin{align}
\lambda &= \sum_{k=1}^{n} \frac{b_k}{a_k} = 0.308...\\
\Leftrightarrow \lambda^{-1} &= 3.25
\end{align}
ポワソン分布編
ポワソン分布は、まれに発生する事象（1週間に発生する交通事故など）に適用出来る確率分布です。
単位時間(または事象1回の)の発生確率 $\lambda$ に対し、 $x$ 件発生する確率を求められます。
数式
グラフは直接はご用意できませんが、カシオの計算サイトが分かりやすいのでご覧下さい。
⇒ポアソン分布（グラフ）
ポワソン分布は、$x \in \mathbb{N}$ すなわち $x = 0, 1, 2, 3, ...$ の自然数（諸説あり）と、xの値1個（単位）ごとの平均 $\lambda$ をパラメータにとります。数式は以下の通り。
f(x, \lambda) = \frac{e^{-\lambda}\lambda^x}{g(x)}, \quad g(x) =
\left\{
\begin{array}{ll}
x \in \mathbb{Z} &\Rightarrow \Gamma(x+1) \\
x \in \mathbb{N} &\Rightarrow x!
\end{array}
\right.
以上の式が、POISSON.DIST(x,λ,FALSE) に置き換えられます。
補足
- $x = 0$ のとき、 $0! = \Gamma(1) = 1$ です。
- $x = 1$ のとき、指数分布 $f(x) = \lambda e^{-\lambda x}$ と一致します。
- $g(x)$ の上段はガンマ関数 $\Gamma(x)$ といい、階乗の取る値を正の実数へ拡張したものです。
- ガンマ関数は、 $x \in \mathbb{N}$ なら $\Gamma(x+1) = x!$ で置き換えられます。
- Excelは下段の自然数の場合を採用しています。
なお、ガンマ関数の定義は以下の通りです。
\Gamma(s) = \int_{0}^{\infty}x^{s-1}e^{-x}dx
Excelの関数使用時の注意（エラーとなる範囲）
- $x \in \mathbb{Z}$ の場合、小数点以下は切り捨てられ $x \in \mathbb{N}$ と扱われます。(実数としての実装は自力のみ）
- 数値以外はエラーになります。
- また、$x < 0, \lambda < 0$ の場合もエラーとなります。
※以下は検証中のため、正確さは保証できません
- 条件は $x \geq 0$ , $0 < \lambda \leq 6$ あたりが望ましそうです。(再度検証の可能性あり)
累積確率
関数を使って $0 \leq k \leq x$ で累積確率を取りたい場合、Excelの場合は $x \in \mathbb{N}$ を取るので、Σを使います。
このときの関数を下記の式に対応させると、 POISSON.DIST(x,λ,TRUE) です。
\sum_{k=0}^{x} f(k,\lambda) = e^{-\lambda}\sum_{k=0}^{x} \frac{\lambda^k}{k!} \quad (0! = 1)
例題（追記予定）
問題は用意してあるのですが、後日追記します。