Excelのグラフの機能やデータ分析を使えばだれでも簡単に最小二乗法での近似線をかくことは一応できます。
というか、専門的な知識が無くてもかけるようにと準備されているのがグラフの「近似線の追加」や「データ分析」です。
逆に言えば、やり方さえ分かれば自分で近似線の係数を計算して自分で線を引くことができます。
そんな面倒なこと、どうして自分で計算すんの？文明の利器頼ろうよ！！って声が聞こえてきそうなんですが・・・。
自分で出来ると何が良いかというと、応用が利くんですよね。
どんな応用が利くのかは・・・・とりあえず見て行ってください。
▼PICK UP!▼
ソルバーとは何か？簡単ザックリ説明ソルバーについて
ソルバーについて
ソルバーが何なのかを知らずにエクセルで最小二乗法を計算しようとしている人はあまりいないとは思いますが・・・・一応お約束なんで言わせといてください。
ソルバーとはMicrosoft OfficeのExcelについている最適化分析ツールです。
最適化分析ってなんぞ？⇒状況を希望の状態に近づけるためのパラメータを推定するものです。
ソルバーはExcelに標準装備されていて、Excelさえ持っていれば無償で使うことができますが、アドインソフトのため通常状態ではOFFになっています。
ソルバーの使い方
OFFになっているソルバーをONにする作業をまず行います。
ファイル＞オプション＞アドイン＞一番下のプルダウンを『Excelアドイン』にして設定をクリック
するとこのようなウィンドウがポップするので
ソルバーにチェックボックスを入れてOKをクリック
ちなみに今回はソルバーに計算をやってもらいますが、『分析ツール』にチェックボックスを入れてOKを押すと一緒に使えるようになります
データタブの一番右端にソルバーが出てきて使えるようになります(分析ツールもソルバーの下に出てくる)
なお、Excel2010では開発タブが出ている場合には開発タブに歯車が二つついた『Excelアドイン』というアイコンが出現しているので、いきなりチェックボックスのウィンドウを出すこともできるそうです(私がいじっているのは2007なので見た目や色が少し違う可能性がありますが大体同じところにあるはずです)
ソルバーに最小二乗法を解いてもらう
ということで、最小二乗法をソルバーにお願いしていきたいと思います・・・・が、その前にその仕組みをある程度は理解しておかないと、数式何書いてるのか分からなくなるので・・・(もちろんコピペで出来るようにファイルは用意する予定ではいますが)
そのため、まずはこれからソルバーに何をお願いするのかをザックリと見ていきたいと思います。
あくまでザックリ、なぜなら私自身が超絶数学苦手マンだからです。
数学苦手マンが自分でも分かるように理解するために見つけた道筋なので、細かいところでの間違いはあるかもしれませんが、大筋とイメージで立ち向かうのでよろしくお願いします。
最小二乗法と近似線
最小二乗法を使うのは近似線を書くときが多いかなと思うのですが、それは理系の偏見でしょうか。
今回は分かりやすさ優先で行きたいので、近似線を書くことを目的とした最小二乗法の利用ということで行きますのでよろしくお願いします。
まず例としてですが、こんなグラフがあったとします。
この点たちを代表する線を書きたい・・・・と言うと難しい表現なので噛み砕くと「この点たちから一番近い感じが表せそうな線」を書くとしたらどのあたりに線を引きますか？
こんなイメージ・・・・もう少し傾きが大きくてもいいかな～？って感じですよね。
私はフリーハンドで書いてみましたが、この線をもっと理論的に、「何でここに線引いたの？」って聞かれたらちゃんと理由を応えられるような線を書きたい！
それが近似線です。
そして近似線が「どうしてここの線が点たちからみて丁度いい感じのところを通っているのか？」というのの理由を表わすのが最小二乗法です(正確には違うけどイメージではそんな感じ)
じゃあ最小二乗法ってどうやって線の位置を決めているのか？というと
こんな風に、線から離れている距離をそれぞれ計ってみて、その合計の数値が小さければ小さいほどど真ん中をいい感じにとおっているよね？！
・・・・ってやりたかったんですが、それぞれの点がプラス方向とマイナス方向に点在していて距離を合計したらプラスマイナス相殺しちゃうぞ・・・・で、困ったので『距離の二乗』を合計することにしたと。
それが『最小二乗法』というわけです。
どうやってソルバーに最小二乗法を解かせるのか？
さて『線と点の距離の二乗の合計』を一番小さくしたい。
数学センスのある人はここで「ソルバーで最小値を求めるんだな！」って気が付くんですが 私はまーーーーったく気づきませんでした。
そう、最小二乗法では最小になる値を逐一計算しなければならない・・・！(そうとも限らないんですがそういうことにしておいてください)
「こっちの値はどうかな」「その値はどうかな？」「係数のそっちとこっちの組み合わせは？」
な～んて、悠長にやってられっか！
ということで、Excelに頼んでマシンパワーに任せてとにかく数字を当てはめていって、差の二乗の和が一番小さくなる近似線の係数(一次関数なら傾きaと切片b)を計算して出してもらおう！ってわけなんです。
試しに問題を解いてみる
大まかな理屈が分かったところで、では実際に解いて行ってみます。
問題はあるクラスの数Aと数Ⅰのテストの点数です。そのままコピペできるかも
出席番号 “数学A
点数” “数Ⅰ
点数”
1 78 69
2 32 43
3 89 93
4 54 67
5 68 46
6 72 76
7 8 16
8 46 39
9 56 49
10 93 94
11 81 70
12 68 50
13 23 39
14 69 70
15 45 45
16 65 58
17 62 64
18 93 98
19 72 59
20 41 48
21 29 37
22 48 61
23 39 42
24 60 74
25 48 50
26 62 79
27 64 53
28 65 59
29 80 87
30 58 66
※架空のクラスのテストの点数です
数学は得手不得手があるので、Aが苦手だとⅠも苦手なことが多いんですよねぇ(遠い目)
もしよければ一緒に解いてみてください
★計算がそのままできるExcelのシートのダウンロードする
※良識の範囲内でご利用ください。再配布などはお控えください。
ただ、くれぐれも数学苦手マンが作っているので、間違いがあるかもしれません。その点は大目に見てください(変なところがあったら教えていただければ幸いです)
近似直線を書いてみる
まずはExcelに入力していきます
他人が作ったExcelの表ほど見づらい物はないのは事実ですが申し訳ありません、お付き合いください。
傾きa、切片b、実測Xを使って計算した推定のYの値です(初期値が0なので今は全て０)。aとbをこの後推定するので、計算された値は推定のYとなります。
実測のYと推定のYの差の二乗の値です(残差＝差、平方＝二乗)
ここが先ほどの説明にあった「距離の二乗の合計を小さくしたい」の「距離の二乗」の部分になります・・・・ということはあとでコレを合計するところがあるわけです。
これは後ほど説明するR^2値を計算するための一時計算用の列です。
このセルが先ほどの残差を平方した物の合計値、つまり和です。
このセルの値を最小にするための傾きaと切片bを探していくのがソルバーでやることです。
決定係数R二乗値とは、こうして書かれた近似線がどれぐらいフィットしているか(線で点を説明できているか)を表す数字で、1に近ければ近いほどフィットしているということです。
他の関数を使った近似線を書いた際に比較のために使います。
さて、この表を用いて計算をしてもらいます。
ソルバーの設定はこの通りです。
目標値：最小値
変更セルの変更：C2とC3・パラメータのセル
「制約のない変数を非負数にする」のチェックボックスを外すのを忘れないようにしてください。
これは、変数などがマイナスにならないようにするための制約条件のようなものですが、今回求める「関数の変数」はマイナスになることがしばしばあります。
そのためチェックボックスが入っていると正しい答えが出てこない可能性があります。
この状態で解決ボタンをぽちっとな。
するとこんな形で計算が完了します。
本当にこれが当たっているかどうかが問題だ。
散布図のプロットからExcelに勝手に計算してもらった近似線の値とR2乗値と比較してみて、さて、どうでしょうか？
当たってますね！
ということで、入力がまず大変ですが、入力できればソルバー自体は簡単に動かすことができます。
応用・他の関数を使ってみる
初っ端にも言いましたが、確かにこの程度の計算、大学生の課題でもなければExcelに計算してもらう方が圧倒的に楽で早くて正確です。
しかし基本系である近似線の推定が出来るといい事・・・・それは他の関数を使うこともできるというわけです。
先ほどの散布図のプロットですが・・・
若干こんな感じに、湾曲した線の方がいい感じにフィットする感ありません？ない？ごめんなさいあるってことにしてください。
イメージは三次関数。
ということで、今度は同じデータで三次関数のパラメータの推定と、その当てはまり具合R＾2を出してみたいと思います(サンプルのExcelファイルをダウンロードしてくれた方は二枚目のシートです)
シートですが
変わったのは囲んだ２か所
先ほどの一次関数からかなり複雑になりましたが基本は同じです。
一次関数の場合にはaとbだけでしたが、今回はパラメータは４つの変数です
これをソルバーにお願いする時には
先ほどとほとんど同じですが、パラメータが増えた分だけ範囲指定が変わるので注意してください。
これで計算をお願いすると・・・
それっぽい感じで推定してくれました！
これがExcelに勝手に計算してもらったのと比較してみると・・・
cだけちょっと違うんですが(何でかよく分かってない)、ほとんど同じ答えが出てきました。
R^2の値が三次関数の方が直線よりも１に近いので、こちらの方がフィットはしているみたいです。
と、計算しておいて何なんですが、実は多項式ならExcelは自動で線を引いてくれる機能があります。
多項式近似で次数を増やしていくだけの簡単な作業・・・・
ですが、ソルバーは関数の変数が増えても200までなら受け付けつけてくれる上に、関数の形に制限は基本的にありません(ただし物凄く時間はかかりますが)
関数の形に制限はないってなんだ？と思われるかもしれませんが例えば
こんな曲線を描くプロットにもぴったり当てはめることができます(正規分布・以外にもパラメータは二種類)。
※ただし理論的な根拠なしに多項式や他の当てはめるのはあまり好ましくないので、あくまでも何か理由があっての場合に推奨
こうやってただの多項式ではない式を当てはめて試すことができるので、ソルバーで何をやっているのか理屈を知っておくといつか役に立つことも・・・・？あるかもしれないしないかもしれないしそれは分かりません(笑)
パラメータ推定を行う際の注意点
小難しいけど応用が利くソルバーでの最小二乗法ですが、実は注意しなければならないことがいくつかあります。
もし適用したいデータが膨大・数式が複雑・パラメータがたくさんだったりする場合には特に気を付けた方がいい事です。
本当に収束した？
ソルバーが「計算終わりましたよ」と止まるのには実はカラクリがあります。
ソルバーは最初はあてずっぽうに数字を当てはめて「これかな？こっちかな？」ってやっていくイメージなのです。
しかし徐々に正解の数値が絞れてくると「じゃああと0.001上げよう、もう0.001上げたらどうなる？」というように、小刻みに足を動かす感じになって計算が終わりそうなポイントを探します。
そしてその変化の差がある一定割合以下になると「ほぼ終わった」ということで計算終了でパラメータを返してきます(イメージ)
そのため実は数式が複雑だったりパラメータが多かったりすると、変化の割合なのでまだ小さくなる余地があるのに止まってしまうことがあります。
そのため複雑であればあるほど、一回ではなく二回、三回とソルバーを使う方が、より正確な数値を引き出すことができます。
外れ値は取り除く？そのままやる？
これはデータの側の問題なのですが、外れ値が入ったままだとやはり精度は落ちます。
そのため、明らかに何かおかしいデータをシートに入力した段階で見つけてしまった場合、それが意味を持つデータでなければ取り除いて計算することも一つ視野に入れてみる方がいいかもしれません。
初期値は色々試してみる
ソルバーは途中で止まってしまうことがあると言いましたが、実は困ったことに間違いを算出することもあります。
どうしてそんなことが生じるかというと
ソルバーは目隠しをして「こっちの方が高いぞ？こっちはもっと高いぞ？」と山に登って行くようなイメージだからです(今回は最小値ですが)
そのため初期値の位置が違うと、本来の頂上よりも低い場所なのに「ここが一番高い気がするよ！」と答えを返してくる場合があります。
複雑な数式やパラメータが増えるほどにこれが生じやすくなるので、収束した数値を控えて置いた後、再度違う初期値から推定を行って比較してみるとより正確な数値を引き出すことができます。
近似曲線の線がぐちゃぐちゃになる！
サンプルのExcelファイルの三枚目のシートにありますが、実は多項式の場合にはダミーのXを使わないと綺麗に線を書くことができません。(二枚目はプロットに対して近似線を入れている実はちょっとダメなグラフ)
これは一番最初のデータの入力ところでちらりとふれた「お互いのデータが不連続」ということに由来しています(入力の出席番号の列のところの説明)
このデータは出席番号順に並んでいるのであって、点数順に並んでいるわけではありません。
そのためバラバラな実測のXを推定した式に代入すると、バラバラな値の推定のYが計算で出てきます。
このバラバラを順番につなぎ合わせてしまうと、右のグラフのような線がジグザグで「どこら辺が三次関数？」って感じになってしまうわけです。
そこで小さい数字から順番に並んでいるダミーのXの列を作ってあげることで、計算して出てきた推定のYも綺麗に順序良く並んでいる列を作成、これを参照することできれいな線を表示するようにしているのが左のグラフです。
もちろんダミーX以外に利用できるデータがシート内に存在すればそれを使ってしまって問題はありません。
あるいは推定を行う前に、実測Xを順番に並び替えてしまうことも方法の一つ・・・ではありますが、数式がすでに入っていると崩壊することもあるので、あくまでも並び替えはデータ入力時に行っておいた方が無難です。
最後に
学生以来数年ぶりに計算してみましたが、意外とセルの参照間違いや、XとYがひっくり返っていたりして上手くグラフが書けなかったりと苦労をしました。
何か上手くいかない、数字は出たけどグラフが書けないとか、上手く推定できずに全部0になってしまうとか、そう言う事態に陥った時は一端ブレイク。
根を詰めれば詰めるほど、Excelの計算ミスは見つからなくなるので、少し甘いものでも食べて落ち着いてから見直ししてみてください。
一晩寝て起きたら一瞬でミスが見つかるとか、良くある話です。
スポンサーリンク
コメント