またしても訂正とお詫びです。以前Excelは二項分布を扱えないと書きましたが “BINOM.DIST” 関数他二項分布を扱う関数が用意されています

すみません、またしても訂正とお詫びです。6月11日の記事 には、「Excel二項分布を扱うことはできない」とも書いてしまいましたが、 “BINOM.DIST” 関数他いくつかの二項分布を扱う関数が用意されていました。また同日の記事に、インプレス「できるシリーズ」の『やさしく学ぶExcel統計入門』には組み合わせや二項分布の記載がないという意味のことを書きましたが、P74に組み合わせが “COMBIN” 関数で、二項分布の確率は “BINOM.DIST” 関数で求められることと、二項分布は試行回数を増やすと正規分布に近づく旨の説明がありました。また二項分布は「用語集」と「関数INDEX」にも載っていました。

当該記事に追記訂正を行うとともに、新規エントリーを起こして告知しお詫びします。すみませんでした。

Ecxelの「関数の挿入」ダイアログボックスから「二項分布」で検索した結果です。たったこれだけのことを、なんでやらなかったんだろう？ なおExcelのバージョンは2013です。“BINOM.DIST” 関数は2007以前では “BINOMDIST” になるそうです。

スポンサーリンク

せっかくだから、いつもの教科書『新確率統計』に載っている問題を二つほど解いてみる。

P76練習問題1-Aの「1.」と「2.」を。まず「1.」というのは、こんな問題だ。

1. 2枚の硬貨を同時に投げることを10回繰り返すとき，2枚とも表が出る回数をXとする．

(1) Xの確率分布を求めよ．

(2) 2枚とも表が出る確率が2以下となる確率を求めよ。

 (1) は二項分布の公式をそのまま書けばいいんだと思う。こんな感じ。いつもの通り Wordで作った画像ファイル を貼り付けます。

解答見たら合ってた(^^) 公式写しただけだけど。

で、(2)。これをExcelを使って二通りの解き方で解いてみた。

まずは組み合わせ “COMBIN” 関数とべき乗を使った解き方。設定は数式バーをご覧ください。例によって数式のほうはコメントに過ぎず計算には何の役割も果たしていません。

念のため数式バーの内容をテキストで貼ります。これをExcel数式バーにコピペすれば計算してくれるはずです。

=COMBIN(10,0)*(1/4)^0*(3/4)^10+COMBIN(10,1)*(1/4)^1*(3/4)^9+COMBIN(10,2)*(1/4)^2*(3/4)^8

続いて二項分布確率を求める “BINOM.DIST” 関数を使った解き方。なんと1つの関数で求まってしまった！

これも数式バーの内容をテキストで貼ります。引数の設定が重要ですが、Excelのヘルプを参照ください。

=BINOM.DIST(2,10,0.25,TRUE)

さて「2.」。

2. ある型のコンピュータの故障率は0.001であることが知られている．このコンピュータ1000台を使用したとき，4台以上故障する確率はいくらか．

 これは、二項分布関数を力技で解こうとすると計算量が膨大になってしまうから、ポアソン分布で近似することを想定した問題だ。だがExcelを使えば、力ずくで解けてしまうのだ！

三通りの方法で求めてみた。ただし今回はスクショは1枚しか貼りません。代わりに数式をテキストで貼ります。

結果を三通り表示している。一番上は “COMBIN” 関数とべき乗を使って解いた。数式が長いので数式バーで表示しきれない。テキストを貼り付けると、こんな感じ。分数の代わりに小数を使っているのは、なんとなくその方が入力が簡単そうに思えたからという以上の意味はない。

=1-COMBIN(1000,0)*(0.001)^0*(0.999)^1000-COMBIN(1000,1)*(0.001)^1*(0.999)^999-COMBIN(1000,2)*(0.001)^2*(0.999)^998-COMBIN(1000,3)*(0.001)^3*(0.999)^997 

 数式を見ただけでもうんざりするが、これを手計算で解こうと思ったら、さらに大変なのだ。私の計算能力と根気では、まず不可能と言っていい。

二番目の値は、 “BINOM.DIST” 関数を使った。こんな感じ。

=1-BINOM.DIST(3,1000,0.001,TRUE)

これで結果が同じというから、すごくないですか？

1から引き算しているのは、 “BINOM.DIST” 関数がゼロから指定値までの累積確率を求める関数なので、指定値から無限大までの確率を求めるには1から引き算をする必要があるからだ。

一番下の少し値が違うのは、ポアソン分布関数を用いた近似値。二項分布の公式は値が大きくなると計算量が飛躍的に増大するので、条件に応じてポアソン分布なり標準正規分布なりを用いて近似するのが、計算機が普及する前の数表を用いて値を求めていた時代からの伝統だ。

=1-POISSON.DIST(3,1,TRUE)

これも1から引き算しています。小数点以下5桁目に誤差が生じていて、巻末の解答には小数点以下5桁目を四捨五入した「0.0190」が正解として載っています。

二項分布を手計算で求めるのがいかに大変かを示すため、Microsoft Mathematicsを使って計算してみた結果も貼ってみる。Excelでは分数形式は表示できないのだ。

まずは練習問題「1.(2)」のほう。

「近似表示」は表示桁数の違いだけでExcelで求めた結果と一致しているが、「出力」の分数表示が、分子、分母ともデカくなっていることを、おわかりいただけますでしょうか？

続いて「2.」。

さすがに分数形式での出力は処理能力を超えたみたい（別の問題を解かせたときに、分子、分母とも「1.(2)」よりも大きい数字になったのを見たことはある）。

それでもちゃんと結果を出してくれるのは、たいしたもんだ。過去のエントリーでアホ呼ばわりしてごめん！

Microsoft Office Home & Business 2021(最新 永続版)|オンラインコード版|Windows11、10/mac対応|PC2台