🍉しいたげられたしいたけ

NO WAR! 戊争反察Ceasefire Now! 䞀刻も早い停戊を

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 などの列を増やしおいくからであるだよね

たずは問題にはないけど、散垃図を描いおみる。入力したデヌタを範囲遞択しお、「挿入」タブから[散垃図]ボタンの▌をクリックし、「散垃図」をポむントするずプレビュヌが衚瀺されるので、そのたたクリックするだけなのだ。

f:id:watto:20160601214037p:plain

目盛りが0始たりなので調敎する。x軞の目盛り䞊で右クリックし、「軞の曞匏蚭定」をクリックするず 

f:id:watto:20160601214038p:plain

画面右偎に「軞の曞匏蚭定」䜜業りィンドりが衚瀺される。「最小倀」の倀を倉曎しお [Enter] キヌを入力するず、リアルタむムでグラフに反映される。

f:id:watto:20160601214039p:plain

Y軞の目盛り䞊をクリックするず、Y軞の曞匏蚭定に切り替わる。同様にしお最小倀を倉曎する。

䜜業りィンドりには倚くの項目が衚瀺可胜なので、うっかりグラフの違う堎所をクリックするず無関係な項目が衚瀺され元に戻すのが倧倉だ。最初からやり盎したほうが早い。

f:id:watto:20160601214040p:plain

「グラフタむトル」は [Del] キヌで削陀した。

f:id:watto:20160601214041p:plain

グラフの右偎に衚瀺される「ショヌトカットツヌル」ず蚀うのだそうだのうち「」が぀いたボタンをクリックするず、远加できるグラブ芁玠が衚瀺される。

「□近䌌曲線」にチェックを入れるず、回垰盎線が衚瀺される。ただし衚瀺されるだけで、これを䜿っお䜕かの倀を求めるこずはできない。

f:id:watto:20160601214042p:plain

そこで、近䌌曲線はグラフ芁玠ずしおではなく、新しい系列ずしお远加する。

回垰盎線の傟きず切片は、公匏に基づいお、共分散、分散、平均を甚いお求めおもいい。Excelにはそれらを求める関数も甚意されおいる。

しかし、いきなり回垰盎線の傟きず切片を求める関数も甚意されおいるので、今回はそれを䜿うこずにする。

傟きを入力するセルを遞択し、[fx]関数の挿入ボタンをクリックする。「関数の怜玢(S):」欄に「回垰盎線の傟き」ず入力するず、候補が衚瀺される。SLOPE関数を䜿甚すればいい。

f:id:watto:20160601214043p:plain

[OK] ボタンをクリックするず「関数の匕数」ダむアログボックスが衚瀺される。y列、x列の順にドラッグで遞択する。

f:id:watto:20160601214044p:plain

同様に切片を求めるセルに移動し、「関数の挿入」ダむアログボックスで「回垰関数の切片」を怜玢する。INTERCEPT関数を䜿甚する。

f:id:watto:20160601214045p:plain

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

f:id:watto:20160601214046p:plain

y列の巊に、回垰盎線の方皋匏「傟き」* x +「切片」に盞圓する数匏を入力する。

このずき「傟き」ず「切片」のセルを遞択した盎埌に、絶察参照を指定する必芁がある。

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

f:id:watto:20160601214047p:plain

「ショヌトカットツヌル」の「フィルタヌ」挏斗のボタンをクリックし、右䞋の「デヌタの遞択」をクリックする。

f:id:watto:20160601214048p:plain

「デヌタ゜ヌスの遞択」ダむアログボックスが衚瀺される。

f:id:watto:20160601214049p:plain

「グラフ デヌタの範囲(D):」にカヌ゜ルがある状態で範囲遞択するず 

f:id:watto:20160601214050p:plain

グラフデヌタ範囲を倉曎するこずができる。

f:id:watto:20160601214051p:plain

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

f:id:watto:20160601214052p:plain

远加された系列を線グラフに倉曎するには、系列を遞択した䞊で「デザむン」タブの「グラフ皮類の倉曎」ボタンをクリックすればいい。

f:id:watto:20160601214053p:plain

「グラフ皮類の倉曎」ダむアログボックスより、「系列2」を「散垃図盎線」に倉曎する。

f:id:watto:20160601214054p:plain

回垰盎線グラフが線グラフで衚瀺される。

f:id:watto:20160601214055p:plain

「x = 0.64」のずきの倀を求めるには、xの入力されおいる列の末尟に 0.64 を入力すればいい。

回垰盎線の方皋匏を入力した列の末尟に、自動的に倀が远加される。

f:id:watto:20160601214056p:plain

倀を求めるだけなら、グラフを䜜成しなくおも傟きず切片を求めお蚈算すればいいが、むメヌゞが぀かみやすいし間違いがあったずき芋぀けやすいので、グラフは䜜った方がいいず思う。

このくらいのデヌタ数であれば、30分もあれば回垰盎線の方皋匏が求められるのだから、道具ずしおどんどん䜿えるようにしおおくべきだろうなず思った。孊生時代は、蚈算間違いに぀ぐ蚈算間違いで、結果らしきものを出すのに培倜に近くなった。

ちなみに䞊掲曞巻末の解答には、問3の答は「(1) y = 10.367x + 0.580 (2) 7.21」ずあった。

远蚘

Excelには「分析ツヌル」ずいうアドむンがあっお、その䞭に「回垰分析」ずいうのが含たれおいる。

Excel2010で「分析ツヌル」を有効にする方法は、こちらの蚘事に詳しい。詊したずころ2013でも同じ手順で有効化可胜だった。

ただしこちらの䜿い方は、ただ調べおいない。

excel-master.net