Excel2013で回帰直線グラフを描いてみたらあまりの簡単さに30数年前の学生時代のあの苦労は何だったのかと情けなくなった

高専や大学の教科書として使われているというこんな本を使って、確率・統計の再勉強中である。 

新確率統計

上掲書P51に、こんな問題があった。

［問３］次の表は，ある森林から任意に選ばれた6本のパインの木について，幹の周囲x（単位m）と高さy（単位m）を測定したものである。

x	0.75	0.55	0.72	0.61	0.66	0.58
y	8.7	6.8	7.9	7.0	7.1	6.1

(1) y の x への回帰直線を求めよ．

(2) 幹の周囲が0.64mのパインの木の高さを，回帰直線を用いて推定せよ． 

回帰分析というやつである。

回帰分析は、道具として広い用途を持つのみならず、その導出過程が、平均・分散・共分散・相関係数といった道具立てが最小二乗の原理によって鮮やかに結びついてしまうという意味で、数学的にも美しいものでもある。

上掲書ではP49にその導出がある。いやしくも確率統計の本で、導出過程を載せていないものはあるまい。30と何年かぶりにその過程をたどると、煩雑な数式が、分散、共分散、平均などの見慣れた公式に収斂していく様を見て、ため息が出るほど美しいと思った。

ネットでは、例によって「高校数学の美しい物語」さんが導出過程を載せているので、リンクを貼らせてもらいます。ただしこの回は、見たところいつもほどにノリノリじゃないな。私ほどには興味を感じなかったのかな？

mathtrain.jp

だが問題は、膨大な計算量が必要になることである。そして一箇所でも間違いを犯すと、正しい結果は求まらない！ 自慢じゃないが、私は計算間違いを犯す能力においては天才的な才能を発揮する。学生時代に、少なくとも1度は回帰直線を計算した記憶があるが、苦行以外の何物でもなかった。とても「美しい」なんて感想を抱いていられる状況ではなかった。

今は、当時と比べて脳細胞は大量に死滅しているであろうが、代わりにパソコンというものがある。ちょっくら上に引用した問題を、Excelで解いてみようと思った。例によってExcelのバージョンは2013、OSはWindows10です。

スポンサーリンク

上記のデータをExcelのシートに入力する。表の縦横を入れ替えたのは、どっちでもいいんだけど、確率統計の計算を行う場合、慣習的に列の右にxの二乗、xy…などの列を増やしていくからである（だよね？

まずは問題にはないけど、散布図を描いてみる。入力したデータを範囲選択して、「挿入」タブから[散布図]ボタンの▼をクリックし、「散布図」をポイントするとプレビューが表示されるので、そのままクリックするだけなのだ。

目盛りが0始まりなので調整する。x軸の目盛り上で右クリックし、「軸の書式設定」をクリックすると…

画面右側に「軸の書式設定」作業ウィンドウが表示される。「最小値」の値を変更して [Enter] キーを入力すると、リアルタイムでグラフに反映される。

Y軸の目盛り上をクリックすると、Y軸の書式設定に切り替わる。同様にして最小値を変更する。

作業ウィンドウには多くの項目が表示可能なので、うっかりグラフの違う場所をクリックすると無関係な項目が表示され元に戻すのが大変だ。最初からやり直したほうが早い。

「グラフタイトル」は [Del] キーで削除した。

グラフの右側に表示される「ショートカットツール」（と言うのだそうだ）のうち「＋」がついたボタンをクリックすると、追加できるグラブ要素が表示される。

「□近似曲線」にチェックを入れると、回帰直線が表示される。ただし表示されるだけで、これを使って何かの値を求めることはできない。

そこで、近似曲線はグラフ要素としてではなく、新しい系列として追加する。

回帰直線の傾きと切片は、公式に基づいて、共分散、分散、平均を用いて求めてもいい。Excelにはそれらを求める関数も用意されている。

しかし、いきなり回帰直線の傾きと切片を求める関数も用意されているので、今回はそれを使うことにする。

傾きを入力するセルを選択し、[fx]（関数の挿入）ボタンをクリックする。「関数の検索(S):」欄に「回帰直線の傾き」と入力すると、候補が表示される。SLOPE関数を使用すればいい。

[OK] ボタンをクリックすると「関数の引数」ダイアログボックスが表示される。y列、x列の順にドラッグで選択する。

同様に切片を求めるセルに移動し、「関数の挿入」ダイアログボックスで「回帰関数の切片」を検索する。INTERCEPT関数を使用する。

やはりy列、x列の順に選択する。

y列の左に、回帰直線の方程式（「傾き」* x +「切片」に相当する数式）を入力する。

このとき「傾き」と「切片」のセルを選択した直後に、絶対参照を指定する必要がある。

フィルハンドルをドラッグして、新たな系列にする。

「ショートカットツール」の「フィルター」（漏斗のボタン）をクリックし、右下の「データの選択」をクリックする。

「データソースの選択」ダイアログボックスが表示される。

「グラフ データの範囲(D):」にカーソルがある状態で範囲選択すると…

グラフデータ範囲を変更することができる。

右下の「OK」ボタンをクリックすると、グラフに系列が追加される。

追加された系列を線グラフに変更するには、系列を選択した上で「デザイン」タブの「グラフ種類の変更」ボタンをクリックすればいい。

「グラフ種類の変更」ダイアログボックスより、「系列2」を「散布図（直線）」に変更する。

回帰直線グラフが線グラフで表示される。

「x = 0.64」のときの値を求めるには、xの入力されている列の末尾に 0.64 を入力すればいい。

回帰直線の方程式を入力した列の末尾に、自動的に値が追加される。

値を求めるだけなら、グラフを作成しなくても傾きと切片を求めて計算すればいいが、イメージがつかみやすいし間違いがあったとき見つけやすいので、グラフは作った方がいいと思う。

このくらいのデータ数であれば、30分もあれば回帰直線の方程式が求められるのだから、道具としてどんどん使えるようにしておくべきだろうなと思った。学生時代は、計算間違いにつぐ計算間違いで、結果らしきものを出すのに徹夜に近くなった。

ちなみに上掲書巻末の解答には、問3の答は「(1) y = 10.367x + 0.580 (2) 7.21ｍ」とあった。

追記：

Excelには「分析ツール」というアドインがあって、その中に「回帰分析」というのが含まれている。

Excel2010で「分析ツール」を有効にする方法は、こちらの記事に詳しい。試したところ2013でも同じ手順で有効化可能だった。

ただしこちらの使い方は、まだ調べていない。

excel-master.net

Microsoft Office Home & Business 2021(最新 永続版)|オンラインコード版|Windows11、10/mac対応|PC2台