xの欄に0から10まで入力します。一つずつ入れていると大変なので、 まずA6に0を入れた後は自動で埋めましょう。0を入れたセルをクリックして 編集→フィル→連続データの作成を選びます。
列方向に1つずつ10まで増やします。
するとこのようになります。
次に確率関数を入力します。確率関数の式に従ってx=0に対しては
=COMBIN(B$2,A6)*B$3^A6*(1-B$3)^(B$2-A6)
と入力します。
それをB16セルまでコピーして、確率関数の完成です。
次に分布関数を入力します。 x=0に対しては=SUM(B$6:B6)と入力します。
それをC16セルまでコピーします。
次に期待値μ=Σxp(x)を求めます。まずx=0に対して=a6*b6と入力します。
それをD16セルまでコピーします。
それらを合計したものが期待値です。実は期待値はnpとして求めることが出来ます。
次に分散σ^2=Σ(x-μ)^2p(x)を求めます。まずx=0に対して=(a6-d$18)^2*b6と入力します。
それをE16セルまでコピーします。
それらを合計したものが分散です。実は分散はnp(1-p)として求めることが出来ます。
さて、n=10の場合はこのように確率関数、分布関数を計算することが出来ましたが、
nが大きくなると確率関数を計算することが出来なくなります。
よって正規分布N(μ,σ^2)の分布関数で近似します。
B(n,p)の場合、期待値μ=np、分散σ^2=np(1-p)なのでN(np,np(1-p))で近似します。
Excelには正規分布の分布関数を求める関数NORMDISTが用意されています。
まずx=0に対して
=NORMDIST(a6,d$18,sqrt(e$18),true)と入力します。3番目の引数には分散ではなく
その平方根である標準偏差を代入します。
それをF16セルまでコピーします。
さて近似してみましたが、C列とF列を見比べてみると値はかなり異なっています。
nが大きくなければ正規分布に近くないのも理由の一つですが、一番の理由は
二項分布の確率関数と正規分布の密度関数のグラフを重ねて、面積を比較するとき、
二項分布の確率関数は幅が±0.5あったことを無視しているからです。よってその分補正します。
まずx=0に対して
=NORMDIST(a6+0.5,d$18,sqrt(e$18),true)と入力します。つまり幅の分0.5増やしています。
それをG16セルまでコピーします。
如何ですか。小数点以下2桁までは合いました。まだ近似としては不十分ですが、
そもそもn=10ならば直接確率関数を計算した方が話は早いです。
ここまで出来た人は、このSheet1をSheet2へコピーして、n=100の場合について
同じ計算をして見ましょう。これよりnが大きくなると、確率関数が計算出来なくなったり、
出来たとしても確率関数を足すときの誤差が大きくなってしまいます。
小数点以下3桁までは合っているようですね。n=100の場合の計算まで頑張って終わらせください。
発展問題
以上が終わった人は、確率分布を擬似乱数を用いたシミュレーションで調べましょう。
演習1
- 確率分布
1.新しくブックを開いてください。
2.A1セルに[0,1]の一様乱数を作ります。先ほどと同じ手順で関数の挿入を行います。「数学/三角」の「RAND」を選択してOKを押してください。
3.100個の一様乱数を作るため、A1セルをコピーしてA100セルまで貼り付けます。
4.B2セルに>0.5と入力してください。
5.先ほど発生させた100個の一様乱数の中で、0.5よりも大きいものの個数を調べます。B1セルにCOUNTIF関数を挿入します。「統計」の「COUNTIF」を選択してOKを押してください。
6.範囲にA1:A100、検索条件にB2とそれぞれ入力してください。検索条件は4.で入力してもらった>0.5という条件を指定しています。
7.ファンクションキー「F9」を押すと、新しく乱数を発生させることができます。0.5より大きい個数も、F9を押す度に変化します。
8.これらの値がどの区間に入っているかを調べます。まずC1セルからC11セルに0から1までの数を0.1刻みで、次のように入力してください。
9.度数分布を調べます。ツールタブ→分析ツール→ヒストグラムを選択してください。
10.入力範囲にA1セルからA100セルのデータを選択してください。データ区間に先ほど入力したC1セルからC11セルを選択してください。出力先にはE3セルを指定してください。グラフ作成にチェックを入れてください。最終的には次のような形になっていると思います。
11.OKを押してください。適当にグラフを移動し、広げて見やすい形にしてください。
演習2
-
1.演習1のワークシートをそのまま利用します。
2.A1セルに10個の一様乱数の和を作ります。A1セルに=RAND()+RAND()+RAND()+RAND()+RAND()+RAND()+RAND()+RAND()+RAND()+RAND()と入力してください。
3.先ほどと同じく、A1セルのコピーをA100セルまで貼り付けてください。
4.B2セルには>5と入力してください。するとB1セルに5より大きいものの個数が表示されます。
5.C1セルからC21セルに0から10までの数を0.5刻みで次のように入力してください。
6.演習1と同じ要領でヒストグラムを作ってください。データ区間にはC1セルからC21セルを選択してください。
このようなヒストグラムの図になっているでしょうか。
0 件のコメント:
コメントを投稿