Excelの使い方

最尤推定量の求め方として、一番シンプルなのは尤度関数を最大にする値の式を解析的に求めておくことです。しかし問題によっては解けないこともありますので、計算機を使って最大になるパラメータを求めることになります。そのための方法も色々あるのですが、この講義は計算機の講義ではありませんので、Excelを用いて手早く計算する方法を紹介します。

準備

まずExcelを立ち上げましょう。

初めて使う時にはいくつか聞かれますので、共にOKを押してください。


まず準備です。左上の丸いボタンを押して、「Excelのオプション」をクリックします。そして左側の「アドイン」をクリックして、下の「管理:Excelアドイン」の横の「設定」をクリックします。

するとアドインの画面に切り替わるので「ソルバーアドイン」にチェックをつけてOKをクリックします。 「インストールしますか」と聞かれたら「はい」と答えます。

Excel入門

まずExcelの使い方に慣れる為に、この表を作成してください。一番上とか一番左のA,B,Cとか1,2,3はExcelに表示されているもので、入力する必要はありません。数字は情報基盤センター利用の手引き第2分冊のもので、いつの数字なのか知りません。

	A	B	C	D	E	F
1	県名	男	女	人口総数	男比率	女比率
2	鳥取	294	321
3	島根	368	402
4	岡山	939	1014
5	広島	1398	1484
6	山口	734	817

人口総数は自分で入力しなくても、既に入力されたデータから計算できます。鳥取の人口総数欄であるD2に鳥取の男性人数B2と鳥取の女性人数C2の合計ということで　=B2+C2　と入力してください。
鳥取の男比率は鳥取の男性人数B2を鳥取の人口総数D2で割りますからE2に　=B2/D2　と入力します。女比率F2は、割り算しなくても　=1-E2　で求められます。

他の4県についても計算式を入力しますが、いちいち式を書くと面倒なのでコピーします。まずD2にマウスをあわせて左ボタンを押してそのままF2までマウスを移動してから左ボタンを離します。

するとコピーしたい部分が枠で囲まれますので、その中にマウスを合わせて右ボタンを押すとメニューが表示されます。その中から「コピー(C)」にマウスを合わせて左ボタンを押します。

次にコピー先のC3にマウスを合わせて左ボタンを押してそのままF6までマウスを移動してから左ボタンを離します。

その枠の中にマウスを合わせて右ボタンを押すとメニューが表示されます。その中から「貼り付け(P)」にマウスを合わせて左ボタンを押すと貼り付けられます。

先ほどはD2セルに =B2+C2 と書いたのですが、それをD3セルに貼り付けると =B3+C3に変わっています。式をコピーすると貼り付け先に応じて式が変化するので便利です。下へコピーするとB2がB3, B4,…に変化し、右にコピーするとB2がC2, D2に変化します。

次に合計を求めましょう。A7セルに「合計」と書きます。B7セルに、5つの県の男性の合計を計算させたいのですが =B2+B3+B4+B5+B6 と書くのは面倒ですので =SUM(B2:B6) と書くことで合計を計算します。次にその式をC7, D7にコピーして、女性の合計、人口総数の合計も求めてください。

そして5つの県それぞれの、中国地方の総人口に対する割合を求めます。G1セルに「割合」と書いて、G2セルに鳥取の人口÷総人口、つまり =D2/D7 と書きます。
さてG3セルに島根の割合を計算させたいので、先ほどのG2セルをコピーすると#DVI/0!と表示されます。これは式を下にコピーしたので =D3/D8 になってしまったからです。本来は、分子のD2は下へコピーするとD3に変わって欲しいけれど、分母のD7はD7のままでいて欲しいのです。
こんな時は、最初にG2セルに式を書くときに、変わって欲しくない7の前に$をつけて =D2/D$7 と書きます。これを下にコピーすると、分母はD$7のまま変化しないのでちゃんと計算できます。同様に、もしDの前に$をつけると右にコピーしてもDはDのまま変化しません。

練習問題

九九の表を作ってみましょう。
まず、Excelの左下のsheet2をクリックして、新しい表に切り替えます。

今まで作った表を見たいときはsheet1をクリックすれば見ることが出来ます。

まずこのように一番上と一番左に1から9までの数を書きます。
そして表の内側のセルには、その行の一番左の数字と、その列の一番上の数字を掛け算する式を書きます。例えばB2セルは =A2*B1 ですし、J10セルは =A9*J1 です。
但し81個も式を書くのは大変ですので、B2セルの =A2*B1 に上手に$をつけて、B2セルの式を表全体にコピーすることで表が完成するように、どこに$をつければ良いか考えてください。

教科書のデータ

一般化線形モデル入門22ページ表2.1のデータです。

都市群 0 1 1 0 2 3 0 1 1 1 1 2 0 1 3 0 1 2 1 3 3 4 1 3 2 0

農村群 2 0 3 0 0 1 1 1 1 0 0 2 2 0 1 2 0 0 1 1 1 0 2

出典

これらのデータが、どんな母集団分布に従って観測されたのか、特に都市群と農村群で同じ分布と考えるのが妥当なのか、違うと考えるのが妥当なのか、考えます。
母集団分布を寸分の狂いも無く推定するには無限個のデータが必要ですのでそれは諦めて、ポアソン分布を用いて推定してみます。他にももっと適当な分布があるかも知れません。
ポアソン分布の確率関数は f(x|λ)=λ^xe^-λ/x! です。そしてそのグラフをいくつかのλに関して書いてみると

になります。今日はまず都市群のデータ

に一番良く当てはまるλの値を最尤法を用いて求めてみましょう。

パラメータのベクトルをθとすると、対数尤度関数は∑_α=1ⁿlog(f(x_α|λ))です。ポアソン分布の場合はパラメータはλ一つですから、θ=λです。ポアソン分布の場合は対数尤度関数をλで微分して=0とおいた式を解くことでλをx₁x₂,…,x_nを用いて表すことも出来ますが、それは数理統計学Iで習っていると思いますし、今回は解けない場合に計算機を使ってどうやって求めるか、という問題を考えていますので、Excelで尤度関数の値を求めて、それを最大にするλを探しましょう。

「都市部」の左端にマウスをあわせて左ボタンを押して、押したままマウスを下へ移動して都市部のデータを反転させます。データの最後まで反転させたら左ボタンを離して、反転した部分にマウスを合わせて右ボタンを押して、表示されたメニューの中の「コピー(C)」をクリックします。

そしてエクセルのA1セルにマウスを合わせて貼り付けます。

貼り付けたら、今度はλの値を入力します。後で変化させますがまずはλ=1にしましょう。B1セルにλ、C1セルに1と書いてください。
そして対数尤度関数はlog(f(x_α|λ))を足したものですので、B2セルに、この式のxのところにA2セルの値が、λのところにC1セルの値が来るように式を書いてください。但し、この式を後でB3以降にコピーしますので、C1はC$1と書いて、下へコピーしても番号が変わらないようにしてください。
ポアソン分布の確率関数の式 f(x|λ)=λ^xe^-λ/x! をエクセルで書く場合、
λ^xはExcelではλ^x
e^-λはExcelではEXP(-λ)
x!はExcelではFACT(x)
そして自然対数のlog xはExcelではLN(x)と書きます。

練習問題

都市群のデータの対数尤度関数を計算し、C1セルに色々な値を入れて、対数尤度が最大になるλの値を探してください。
また、対数尤度関数をλで微分して=0とおいた式を解いて、λの最尤推定量をx₁x₂,…,x_nを用いた式で書き、その式に都市群のデータを代入した値と比較してください。

次回予告

色々な値を入れて対数尤度が最大になる値を探すのは手間がかかりますし、また最尤推定量がx₁x₂,…,x_nを用いた式で書けない場合もあります。
そこでExcelのソルバーを使って最大になる値を探す方法を紹介します。そしてAICを用いてどの統計モデルを使うのが適当なのか考えます。

メールの使い方

Linuxで使っている@stud.ems.okayama-u.ac.jpのメールをWindowsから読み書きすることも出来ます。でも設定がちょっと面倒ですので、今年から使えるようになった情報基盤センターのメールの使い方を書いておきました。自宅からも使うことが出来ます。特に就職活動のときは大学のメールアドレスを要求されることもこれまでにありましたので、学外からも読み書きできるからという理由でYahooメールなどを使っている人は、就職活動の時はこちらを使った方が良いです。勿論Linuxで使っている@stud.ems.okayama-u.ac.jpのアドレスも就職活動に使えます。

プリントアウトの方法

ファイルから印刷を選びます。

もし「OneNote 2007に送る」と書かれていたら、OKを押さずに設定を押します。

プリンタ名の部分で\\http://を選びます。

使用するプリンタが\\http://になったらOKを押します。

これでプリントアウトできます。