サイコロ統計学第１話

Hidemasa Oda

Published in

UeSaku Diary

12 min readDec 6, 2015

こんにちは。今日から何話かに分けて統計学の話をしていこうと思います。
基本的にはサイコロの話しかしません。
今回は Excel を用いてサイコロの平均と分散を求めましょう。

次回は「推定」の話をしましょう。

完成図

下の画像が今回作成する Excel シートになります。
A 列はサイコロの目 X です。B 列は X の分布 P(X) です。
C8 セルにサイコロの目の平均 E[X] が表示されます。
F8 セルにサイコロの目の分散 V[X] が表示されます。
青文字で描かれている箇所（B2 セルから B7 セルまで）は自由な値に設定できます。
B2 セルの値から B7 セルまで値までの和が 1.00 にならない場合は、B9 セルに赤文字で “ERROR” という文字列を表示させます。
下の画像では、B2 セルから B7 セルまでに入力されている値が、有効桁数の関係で 1.7 となっていますが、実際には 1/6 が入っています。

到達目標

Excel の基本的な活用方法を習得する。
Excel シートを他者へと連携する際の注意点を習得する。
Excel シートを他者から連携された際の注意点を習得する。

小田は日本（内資）の金融機関で１年ほど働きました。
その際の経験をもとに Excel の活用方法を説明しています。

サイコロ統計学のコーナーはあくまでも統計学について解説をすることが目的です。Excel の使い方を説明するコーナーではありません。

References

[1] 『外資系金融のExcel作成術: 表の見せ方&財務モデルの組み方』慎泰俊
[2] 『外資系投資銀行のエクセル仕事術』熊野整

作成手順

⌘ を押しながら A を押して、シート全体を選択する。
背景を「塗りつぶしなし」から「白色」に変更する。
フォントを「Arial」などの見栄えの良いものに変更する。

A 列・B 列・C 列に「X」・「P(X)」・「X P(X)」を入力する。
A2 セル・A3 セルに半角英数字で「1」・「2」を入力する。
A3 セルの右下にカーソルを合わせて、カーソルの形が矢印（通常のカーソル）から十字（プラスマーク）に変わるのを確認する。
カーソルの形を十字に保ったまま A7 セルまでドラッグする。

A4 セルから A7 セルまで値が入力されたことを確認する。
B2 セルを選択して、半角英数字で「=」を入力する。
「=」の直後に「1/6」を入力する。
Enter キーを押して B2 セルの値を確定させた後、B2 セルの右下にカーソルを合わせて、B7 セルまでドラッグをする。

B3 セルから B7 セルまで値が入力されたことを確認する。
C2 セルを選択して、半角英数字で「=」を入力する。
C2 セルを選択した状態で A2 セルをクリックする。
C2 セルに「=A2」が入力されていることを確認する。

C2 セルの「=A2」の直後に「*」を入力する。
C2 セルを選択した状態で B2 セルをクリックする。
C2 セルに「=A2*B2」が入力されていることを確認する。
Enter キーを押して C2 セルの値を確定させた後、C2 セルの右下にカーソルを合わせて、C7 セルまでドラッグをする。

C7 セルに「=A7*B7」が入力されていることを確認する。

C2 セルの入力値「=A2*B2」に含まれる A2 と B2 が、C2 → C7 へと引きずられる過程で、A2 → A7 かつ B2 → B7 と引きずられていることに十分に注意してください。

B2 セルから C7 セルまでを選択する。
書式を「標準」から「数値」に変換する。

B2 セルから C7 セルまでの数値は小数点以下２桁を表示させる。

B8 セルを選択して、半角英数字で「=」を入力する。
「=」の直後に「SUM(」を入力する。
B8 セルを選択した状態で B2 セルから B7 セルまでを選択する。
B8 セルに「=SUM(B2:B7)」が入力されていることを確認する。

Enter キーを押して B8 セルの値を確定させた後、B8 セルの右下にカーソルを合わせて、B7 セルまでドラッグをする。

C8 セルに「=SUM(C2:C7)」が入力されていることを確認する。

C8 セルは X の期待値 E[X] を表示しています。

D1 セルに「X-E[X]」を入力する。
D2 セルを選択して、半角英数字で「=」を入力する。
D2 セルを選択した状態でA2 セルをクリックする。
D2 セルの「=A2」の直後に「-」を入力する。
D2 セルを選択した状態で C8 セルをクリックする。
C8 セルに「=A2-C8」が入力されていることを確認する。
カーソルが C8 セルに入力されている「=A2-C8」の「C8」の上にあることを確認してから、⌘ を押しながら T を押す。

C8 セルに「=A2-$C$8」が入力されていることを確認する。
C8 セルに「=A2-$C$8」が入力されていない場合は、C8 セルに「=A2-$C$8」が入力されるまで、⌘ を押しながら T を押すことを繰り返す。

Enter キーを押して D2 セルの値を確定させた後、D2 セルの右下にカーソルを合わせて、D7 セルまでドラッグをする。

D7 セルに「=A7-$C$8」が入力されていることを確認する。

D2 セルの入力値「=A2-$D$8」に含まれる A2 と D8 が、C2 → C7 へと引きずられる過程で、A2 → A7 は引きずられましたが、C8 は C8 のままで引きずられていないことに十分に注意してください。

D2 セルから D7 セルまでを選択して ⌘ を押しながら 1 を押す。

「セルの書式設定」が表示されたことを確認する。
下画像に従って「負の数の表示形式」を整える。

E1 セルに「(X-E[X])^2」を入力する。
E2 セルを選択して、半角英数字で「=」を入力する。
「=」の直後に「POWER(」を入力する。
E2 セルを選択した状態でD2 セルをクリックする。
E2 セルに「=POWER(D2」が入力されていることを確認する。
E2 セルの「=POWER(D2」の直後に「,2)」を入力する。
E2 セルに「=POWER(D2, 2)」が入力されていることを確認する。
Enter キーを押して E2 セルの値を確定させた後、E2 セルの右下にカーソルを合わせて、E7 セルまでドラッグをする。

C8 セルを選択して、C8 セルの右下にカーソルを合わせて、E8 セルまでドラッグをする。

B2 セルから E8 セルまでを選択して、⌘ を押しながら 1 を押して、「セルの書式設定」を開く。
下画像に従って「負の数の表示形式」を整える。

A1 セルから E8 セルまでを選択して⌘ を押しながら 1 を押して、「セルの書式設定」を開く。
「セルの書式設定」のタブから「罫線」を選択する。
下画像に従って「罫線（単線）」を設定する。

下画像に従って「罫線（破線）」を設定する。

A7 セルから E7 セルまでを選択して、下画像に従って「罫線（二重線）」を設定する。

A1 セルから E1 セルまでを選択して、下画像に従ってフォントを設定する。

フォントサイズは 11 よりも 10 の方が見栄えが良かったので、好みで調整してください。

B2 セルから B7 セルまでを選択して、文字色を青色に設定する。

第三者が変更しても良いセルは青文字で書く習慣をつけておくと良いでしょう。これは日本では内資外資を問わず（特に金融業界では）広く知られている習慣です。また海外の一部の国々でも通用します。

ここまで資料を作って１列足りていないことに気がつきました・・・（すいません・・・）
E 列（の書式）をコピーして F 列を作りましょう。

E1 セルから E8 セルまでを選択して、E8 セルの右下にカーソルを合わせて、F8 セルまでドラッグをする。

F1 セルから F8 セルまでを選択して、Delete を押す。（環境に依っては Fn を押しながら Delete を押す必要がある。）
F1 セルから F8 セルに空白が入力されていることを確認する。

F1 セルに「((X-E[X])^2)P(X)」を入力する。
F 列を選択して、G 列との境界部分をダブルクリックする。
F1 セルに入力されている文字列がセルの表示領域に収まっていることを確認する。

もうここからは説明しません・・・
画像を見ながら各自やってください・・・

複数のセルを選択する場合は、⌘ を押しながらクリックをします。
Shift を押しながらクッリクをすると何が起こるかは、各自で確かめてください。

重要な値（KPI）を強調する場合はオレンジ系の薄い色で強調してください。

青文字のセルの値は自由に変更できます。
下図は 3, 4, 5, 6, の目が 1, 2 の目よりも２倍出やすいサイコロの場合のテーブルになります。
青文字のセル以外は一切変更していないにも関わらず、X の分布 P(X) の変更に伴い、C8 セルに表示されている X の平均 E[X] と F8 セルに表示されている分散 V[X] も変更されていることに注意をしてください。

Excel シートを第三者に提出した場合、第三者は「青文字のセルを変更すると背景色で強調されているセルは自動的に変更される」ことを期待します。第三者は Execl シートに含まれている全ての数式をチェックするとは限りません。Excel シートの文字色や背景色には十分に注意を払ってください。

また、他者が作成した Excel シートについては、黒文字のセルを変更することは避けて下さい。Excel シート作成者は黒文字のセルが変更を受けることを想定しておらず、シート内外のセルに入力されている数式も黒文字のセルは変更されないことを前提に記述されているはずです。

青文字の部分は自由に設定できるとはいっても、P(X=1) + P(X=2) + P(X=3) + P(X=4) + P(X=5) + P(X=6) = 1 が成立する必要があります。
Excel シートが第三者に渡る際にも、その作成者の意図を確実に伝達する必要があります。
「P(X=1) + P(X=2) + P(X=3) + P(X=4) + P(X=5) + P(X=6) = 1 が成立」しない場合には、B9 セルに赤文字で “ERROR” と表示しましょう。