相関係数

相関係数の定義と意味を説明しました。

回帰直線

講義資料(Microsoftのサイト)これまでの講義資料を全部載せていますので、日付から選んでください。
ブログ上で見られるようにもしておきますが、式が文字化けだらけなので上の講義資料(Microsoftのサイト)で見てください。

回帰直線

二変量のデータに対して、最小二乗法を用いて回帰直線を求める方法を説明しました。

Visual Basic for Application

次週は祝日です。その次の週は休講で、次回の講義は5月13日です。

今日の内容

1. 先週の解説
2. 2次式の当てはめ。1次式との比較。
   
3. VBA入門

1次式をあてはめる場合の係数、定数項の求め方

を最小にするa,bを求めます。見ての通りa,bに関する2次式で、しかも2乗してから足しているのでf(a,b)は0以上、つまり必ず最小値を持ちますから、a,bで偏微分して0とおいた式を解けば良いです。
さらに、未知数はaとbの2個、式は「aで偏微分した式＝0」と「bで偏微分した式＝0」の2個ですから個数は一致していますし、2次式を偏微分すれば1次式、つまり2元連立一次方程式ですから、たまに例外もありますが殆どの場合解けます。
まず微分して

両辺を-2で割って、括弧を外して

Σを分けて、a,bで整理して

この時点で確かにa,bに関する2元連立一次方程式になっています。両辺をnで割って

ごちゃごちゃしてきましたが、平均をで表すと

下の式からとなります。これを上の式に代入して

aについて整理して

従って
となります。
このまま計算しても良いのですが、分母はxの分散、分子はx,yの共分散なので

となります。

この方法で数式を書くと準備にやたらと時間がかかり過ぎて肝心の部分が書けないので、次回から別の表示法を考えます。

2次式の当てはめ。1次式との比較。

先週と同じ方法で、今度は2次式をあてはめてみましょう。

この係数はどうやって求めましょうか？直線y=ax+bを当てはめた時同様、今度はを当てはめますので

を最小にするを求めます。
微分で求めるのは3週間の宿題として、まずは前回のようにソルバーを使って求めてみましょう。

VBA入門

左上の丸いボタンを押して、「Excelのオプション」をクリックします。そして「[開発]タブをリボンに表示する」にチェックをつけてOKを押します。

すると右端に「開発」が表示されるのでクリックして、マクロのセキュリティをクリックして、「すべてのマクロを有効にする」に●をつけてOKを押します。


これで準備は完了です。
ではマクロでどんなことが出来るか見てみましょう。「マクロの記録」をクリックしてください。マクロの名前を聞かれますので、ここはそのままOKを押します。


何か適当にExcelの操作をしてください。ここでは1から5の数字を入力してみました。
そして、記録終了をクリックしてください。

すると、Excelは今の操作を全部覚えています。確かめるために、今入力したものを全部消してから、今度は「マクロ」をクリックしてください。するとどのマクロを実行するか聞かれます。今は一つしかないのでそのまま「実行」を押します。
すると、先ほど自分でやったことがそのまま繰り返されたはずです。


一体どんな仕組みになっているか見るために「Visual Basic」のボタンを押します。するとVisual Basicの画面が開くので、左側のModule1をダブルクリックするとこの画面になります。


先ほど「マクロの記録」をクリックしてから「記録終了」をクリックするまでの操作が全てプログラムに書かれたのです。今すぐ覚える必要はありませんが、ActiveCell.FormulaR1C1 = "1"は選択しているセルに1を代入しなさい、Range("A2").SelectはA2セルを選択しなさい、という命令です。
このように自分の操作をプログラムに記録するのではなくて、今度は自分でプログラムを書いてみましょう。今、人によって色々違うことを書いたと思うので、一旦Excelを終了させて、もう一度立ち上げて、先ほど同様「開発」をクリックして「Visual Basic」をクリックしてください。
プログラムを書く部分を用意したいので「挿入」をクリックして「標準モジュール」をクリックしてください。

現れた画面にプログラムを書きます。
まず
sub 練習
とだけ書いて、Enterキーを押してください。すると自動的に

Sub 練習()

End Sub

となり、カーソルは真ん中の空行にあります。そこに
cells(1,1)=1
と書いてください。これは上から1番目、左から1番目のセルに1を代入しなさい、という命令です。
プログラムを書いたら、上の右向きの三角をクリックしてください。するとさっきと同じように、どのプログラムを実行するか聞かれます。今は一つしか書いていないのでそのまま実行を押すと、A1セルに1が代入されます。Excelの画面で「マクロ」を押しても実行できます。

このプログラムを保存する時の注意。
左上の「上書き保存」を押すと、どの形式でファイルを保存するか尋ねられます。ファイルの種類として「Excelマクロ有効ブック」を指定してください。
2階実習室で保存するときは、P:ドライブに保存してください。

さてこれでプログラムを書いて実行できるようになったので、具体的なプログラミングを始めましょう。
先ほどのCells(1,1)=1とEnd Subの間に
cells(2,1)=2
cells(3,1)=3
と書き込んで実行してみてください。
あるいは
cells(1,2)=-2
cells(1,3)=-3
と書いて、cellsの二つの添え字がExcelのどのセルに対応しているか確認しましょう。

10個続けて書くには、
cells(1,1)=1
cells(2,1)=2
cells(3,1)=3
cells(4,1)=4
cells(5,1)=5
cells(6,1)=6
cells(7,1)=7
cells(8,1)=8
cells(9,1)=9
cells(10,1)=10

と書くのは大変なので、これをFor文を使って

for i=1 to 10
　cells(i,1)=i
next　i

と書きます。

次に下のような九九の表ができるようにプログラムを考えて見ましょう。

データの特性値

ヒストグラムの書き方を紹介し、データの特性値として代表値(平均値、中央値)と散布度(分散と標準偏差)を説明しました。
講義で使ったスライドをMicrosoftのサイトに載せました
ブログに貼り付けられるようにGoogle Docsにも載せましたが、文字化けしまくりです。

デジカメのホワイトバランス

デジカメのホワイトバランスについて書く予定ですが、内容が大事なのではなく、講義のスライドを続けるとブログの表示が遅くなるので間に何か入れる必要があって書いただけです。大したことは書きません。

Adobe Photoshop ElementsでRAW画像を扱う場合
Elements3ならCamera Raw 3.6
Elements4ならCamera Raw 3.7
Elements5ならCamera Raw 4.6
Elements6,7ならCamera Raw 5.6
が最新版です。
Camera RAW 6からは、レンズの収差補正が出来るようです。

飛び出た？GIFアニメで明治初期の写真を3Dにしてみたから見て。 21枚。
Pet Store Hamster Olympics
YouTubeが5周年　最初の投稿は……
「こんなカラフルな木が存在するなんて…」と話題のレインボー・ユーカリ
官僚だけど質問ある？
台湾のお菓子のCMに初音ミクとかが出ているんだが
上海万博ＰＲ曲に盗作された疑いの岡本真夜さんの「そのままの君でいて」が正式決定聖母たちのララバイの方が驚きました。
驚異の技巧…ボールペン1本で写真のようにリアルな絵を描く
「お客様は神様だ！」　なぜ、日本では客が神なのか神様のように全てを見通す能力があって、手抜きを許してくれないから、最高のステージを見せるように努力しなければならない、という意味だと思います。
人間の脳は1度に2つまでの事しか処理できないことが判明
冤罪
佐川急便に派遣され残業月平均約１００時間　裁判長「業務起因性は認められない」自殺した社員の遺族の賠償請求を棄却…仙台地裁
新入社員を叱ったら今さっきそのお母さんから電話がかかってきた
採用するなら新卒より団塊…豊富な人脈と経験、若手社員より人件費も安い
獣医だけど聞きたいことある？
ヨドバシ･ドット･コムでのアップル製品の販売終了。アップル社の要望でネットでの販売禁止にアップルはずっとこの調子ですね。人気が出るとワガママになり人気が無くなる、の繰り返し。
養育費（毎月８万円）貰ってますが、足りません。
民主議員、有権者に逆ギレ「子ども手当批判するならもう民主党を支援してくれなくていい！」
母、新宿駅で痴漢に間違われ大学生らから暴行を受け、取り調べ後自殺した息子の情報をブログで求める 今一人の男の人生が終わった瞬間を帰りの満員電車で見たｗｗｗｗｗ

データの特性値

計算統計学II

この講義ではMicrosoft Excelを用いてデータ解析を行う方法を説明します。
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のオプション」をクリックします。そして左側の「アドイン」をクリックして、下の「管理: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までのセルを変化させると入力して「実行」をクリックします。

すると最小にする値を求めてくれます。

今回はソルバーを使わなくても直接計算出来る問題でしたので比較してみましょう。
少し値が違いますね。
このようにソルバーには誤差がありますので、今回のように直接計算できる問題の場合は実は使わない方が良いのですが、複雑な問題の場合直接計算できないことがありますので、そのような問題に対してはソルバーは有効です。