Excelは、表に数字や式を入力すればすぐ表示されるので、分かりやすく、試行錯誤もしやすいです。
起動方法
初めて使う時にはいくつか聞かれますので、共にOKを押してください。
統計学Iの復習
統計学Iの教科書の例1.6です。| 生徒 | 身長 | 座高 |
|---|---|---|
| 1 | 159 | 88 |
| 2 | 150 | 84 |
| 3 | 157 | 86 |
| 4 | 153 | 81 |
| 5 | 158 | 83 |
| 6 | 152 | 85 |
| 7 | 155 | 83 |
| 8 | 157 | 83 |
| 9 | 145 | 76 |
| 10 | 158 | 85 |
| 11 | 161 | 85 |
| 12 | 150 | 83 |
| 13 | 148 | 79 |
| 14 | 154 | 84 |
| 15 | 154 | 85 |
| 16 | 159 | 85 |
| 17 | 149 | 83 |
| 18 | 155 | 86 |
| 19 | 153 | 84 |
| 20 | 160 | 88 |
Internet Explorerを使うときには「コピー」してからExcelで「貼り付け(P)」を選びます。
FireFoxを使うときは「貼り付け(P)」ではなく「形式を選択して貼り付け(S)」を選んで「テキスト」を選んでください。
散布図を描きましょう。
B2セルの上でマウスの左ボタンを押して、押したままC21セルまでマウスを移動させて左ボタンを離します。
そして画面上の「挿入」から「散布図」の「散布図(マーカーのみ)」を選びます。
すると図のような散布図が得られます。マウスを合わせるとそれが何番目のデータで、x,yの値が何であるか表示されます。
次に回帰直線をExcelに書いてもらいましょう。データ点のどれかにマウスを合わせて右ボタンを押して、表示されるメニューから「近似曲線の追加」を選びます。
「近似または回帰の種類」は「線形近似」を選び、また式も見たいので、「グラフに数式を表示する」にチェックをつけて、閉じるをクリックすると、回帰直線とその式が表示されます。
さてこの係数0.4757や定数10.383はどうやって求めましたか？
データを, 直線を
とおくと、
から計算したyの値は
、
これと実際のyの値
との差
を2乗して正の値にしてから足した
を最小にするのでしたね。a,bに関して2次関数で非負ですから必ず最小値を持つので、a,bで偏微分してそれぞれ0とおけばa,bに関する2元連立1次方程式になるのでそれをa,bについて解くことで
と求めることが出来ます。但しはそれぞれ
や
の平均、つまり
です。
Excelを使った計算練習
実際に計算してみましょう。実はExcelには平均、分散、共分散だけでなく、このような回帰直線の係数を求める関数も備わっていますが、まずは練習のために合計を求める関数SUMだけを使って、上の式の通りに計算してみましょう。
まず合計を計算します。A22セルに「合計」と書いて、B22セルに「=SUM(B2:B21)」と書きます。これはB2からB21までの数を足し算した値という意味です。
同様にC2からC21までの合計を求める式をC22に書きますが、面倒ですのでB22セルの=SUM(B2:B21)をコピーしてC22セルに貼り付けます。
右に一つずらしてコピーすると=SUM(B2:B21)が=SUM(C2:C21)に変化します。
次に平均を計算しましょう。まずA23セルに「平均」と書きます。B23に、先ほど計算した合計をデータ数20で割った値を書きたいので「=B22/20」と書きます。C23は先ほど同様B23に書いた式をコピーします。
これでを計算できました。
次にを計算します。D1セルに「身長の平均との差」と書きましょう。D2セルには、1番目の生徒の身長B2と身長の平均B23の差を書きたいので「=B2-B23」と書けばとりあえず計算は出来ます。でもD3, D4,...,D21セルにいちいち式を書きたくないのでD2セルの式をコピーしたいです。でもD2セルに「=B2-B23」と書いてから一つ下にコピーすると「=B3-B24」になってしまいます。平均はB23セルですからB24になっては困ります。
「=B2-B23」を下にコピーした時に、B2はB3に変化して欲しいのですがB23はそのままの方が良いです。こんなときは、変わって欲しくない23の前に$をつけて「=B2-B$23」と書きます。すると下にコピーした時に「=B3-B$23」になります。
同様にを計算します。まずE1セルに「座高と平均との差」と書いて、次にE2セルに式を入力するのですが、先ほどD2セルに「=B2-B$23」と入力しているのでこれをコピーして一つ右のE2セルに貼りつけるとBがCに変わって「=C2-C$23」になりますから、書かなくてもコピーすれば良いです。
ここまで入力すると以下のようになっているはずです。
練習:D列の和を計算してみてください。
0になる筈ですが計算機の誤差のために1.14E-13となりました。これはという意味です。ほぼゼロですね。
次はとその合計、平均です。F1セルに「身長の平均との差の2乗」と書いて、F2セルに「=D2^2」と書いてそれをF21セルまでコピーします。そしてF22セルがそれらの合計、F23セルはF22セルを20で割って平均です。するとこうなります。
F23セルの値が身長の標本分散です。
次はとその合計、平均です。G1セルに「身長の平均との差と座高の平均との差の積」と書いて、G2セルに「=D2*E2」と書いてそれをG21セルまでコピーします。そしてG22セルがそれらの合計、G23セルはG22セルを20で割って平均です。するとこうなります。
G23セルが身長と体重の共分散です。
ここまでくればあと一息です。どこでもよいのですが、分かりやすいところということで例えばI3セルに「a」と書いてJ3セルにaの計算式を書いてください。
ですので、分母も分子も既に計算していますね。
同じくI4セルに「b」と書いてJ4セルにbの計算式を書いてください。ですから、右辺の値は既に全部計算しています。
このような値になれば正解です。
先ほどの散布図の式y=0.4757x+10.383と一致していますね。これで統計学Iの復習は終わりです。
ソルバー
もっと複雑な問題を考える時は準備
左上の丸いボタンを押して、「Excelのオプション」をクリックします。そして左側の「アドイン」をクリックして、下の「管理:Excelアドイン」の横の「設定」をクリックします。
するとアドインの画面に切り替わるので「ソルバーアドイン」にチェックをつけてOKをクリックします。 「インストールしますか」と聞かれたら「はい」と答えます。
座高の推定
先ほどの表には既に色々書き足して分かり辛いので、Sheet2に切り替えてもう一度同じ表をコピーしておきます。
今度はy=ax+bのaとbを、自分で計算した数式を使わず、Excelに直接計算させます。そのためにまずaやbの値を書く場所を用意しておきますのでA23セルに「a」、A24セルに「b」と書いてB23セルにaの値、B24セルにbの値を書くことにします。
空っぽだと数字にならないので何か適切な値を書いておきます。全く見当がつかないので、真横の線を書くことにしてaは傾きなので0、bは座高の大体の平均84にしておきます。
このaとbを用いて、y=ax+bという式を用いて身長から座高を推定しますので、D1セルに「座高の推定値」、D2セルに身長とa,bから推定した1番目の生徒の座高の式を書きます。y=ax+bなので「=B23*B2+B24」ですが、D3～D21にコピーしますからa,bを示すB23, B24が変わらないように「=B$23*B2+B$24」と書いておいて、それをコピーします。
当たり前ですが、真横の線で推定したので全て84です。
推定の様子をグラフに書いてみましょう。まず身長、座高、座高の推定値のデータを選択して散布図を描きます。
推定値の方は直線にしたいので推定値の点を右クリックして「データ系列の書式設定」を選びます。
「線の色」は「線(単色)」、「マーカーの種類」は「なし」を選んで「閉じる」をクリックします。
するとグラフがこのようになりますので、B23, B24セルの数字を変化させて直線が点に近付くように調整してみてください。
a=0.5, b=7だとこんな感じです。
あてはまりの良さの評価
見た目で評価したのでは不正確ですので、数値的に評価しましょう。推定値がどれくらいずれているか計算するために、E1セルに「推定誤差」と書いて、E2セルに推定値と実際の座高の差「=D2-C2」を書いて、その式をE21までコピーします。
誤差はプラスにもマイナスにもなるので、そのまま合計、平均を求めると打ち消しあってしまいます。だから二乗してから足し算します。
F1セルに「誤差の二乗」と書いて、F2セルに誤差を二乗した「=E2^2」と書いてF21セルまでコピーします。最後にF22に、その20個の誤差の二乗を合計する式「=SUM(F2:F21)」を書きます。
このF22の値が誤差平方和で、この値が小さいほどあてはまりが良いことになります。この値を小さくするようにa,bの値を調整します。
ソルバーの利用
a,bの値を自分で調整していては大変ですし不正確ですから、Excelに調整させましょう。「データ」を選んで右端の「ソルバー」をクリックします。
どのセルをどうしたいのか(最大にする、最小にする)、そのためにどのセルの値を変化させるのか、を尋ねられますので、今回は誤差平方和であるF22を最小にするためにB23からB24までのセルを変化させると入力して「実行」をクリックします。
すると最小にする値を求めてくれます。
今回はソルバーを使わなくても直接計算出来る問題でしたので比較してみましょう。
少し値が違いますね。
このようにソルバーには誤差がありますので、今回のように直接計算できる問題の場合は実は使わない方が良いのですが、複雑な問題の場合直接計算できないことがありますので、そのような問題に対してはソルバーは有効です。
0 件のコメント:
コメントを投稿