非線形の回帰分析を最小二乗法とエクセルのソルバー機能でやる方法【回帰分析シリーズ５】
今日は非線形の回帰分析をエクセルのソルバー機能と最小二乗法を使最小二乗法とエクセルのソルバー機能を使っての非線形の回帰分析のやり方についてです。最小二乗法の概念とそのエクセル上での準備の仕方を分かり易く説明し、ソルバーを使っての非線形の回帰式を導き出す図解説明をしています。
（動画時間：7:45）
非線形の回帰分析を最小二乗法を使って行えるエクセルのソルバー機能
こんにちは、リーンシグマ、ブラックベルトのマイク根上です。
業務改善コンサルをしています。
以前最小二乗法についての動画を配信して、
次のリクエストを頂きました。
「とてもわかりやすかったです。非線形の最小二乗法もおしえていただきたいです。」
KTRXさん、リクエストありがとうございました！
その動画は最小二乗法の基本についてでした。
⇒「最小二乗法の概要と回帰分析との違い、最小二乗法で会社の固定費の簡単な求め方」
今回はその動画の続きになります。
その動画ではサンプルデータを使い、
散布図を使ってY = a X + b の単回帰式を求めました。
線形の単回帰分析や重回帰分析は
エクセルの散布図や分析ツールを使って簡単に行えます。
しかし Y = ａ X + b X^2 + c などの直線でない非線形では使えません。
その非線形の回帰式を最小二乗法で求める方法が今日のテーマです。
結論から言いますと、これもエクセルのある機能で求める事ができます。
エクセルには標準アドインで「ソルバー」と言う機能がついてます。
その「ソルバー」機能を使う事で非線形の回帰分析を行えます。
今日はまず、これを使って単回帰分析をしてソルバーの働きを理解してから、
非線形の回帰分析をやってみます。
単回帰分析の最小二乗法の説明
上図がサンプルデータとそれで作った散布図です。
平面の直線の回帰式はY= a X + bで表します。
傾きと切片である係数aとbを求めるのが回帰分析なのです。
前の動画でも説明しましたが、
各データ点からその回帰直線までのY軸方向の距離をY – (a X + b)で表せます。
サンプルデータのYとXの数字を全て使って、上図の様になります。
この全体の距離が最小になる係数aとbを求めるのに最小二乗法を使い、
エクセルでは自動で計算してくれます。
上記の距離の式をサンプルデータの横に（G列）書きました。
距離の和として各距離を二乗して全て足す二乗和を使います。
その二乗和が最小になる係数 a と b を探すので最小二乗法というのです。
その a と b を図内の黄色のセル（セルH1とJ1）にして、
データ表内のX軸（E列）も参照して、
セルH3内
=(F3-($H$1*E3+$J$1))^2
この数式で距離の二乗の数式となります。
それらをSUM関数で合計したのが二乗和になるのです。
ここまで準備をするとソルバー機能を使って係数 a と b を求められます。
やってみましょう。
「ソルバー」アドインの有効化の仕方
「ソルバー」機能を使うのに、最初にそのアドインを有効化する必要があります。
次の手順でその有効化をして下さい。
- メニューバーの「ファイル」
- （画面左下）「オプション」
- 次の画面の左ペインで「アドイン」
- 管理で「Excel アドイン」の状態で「設定」
- 次の小画面で「ソルバー アドイン」にチェックをつけて「OK」
最小二乗法とソルバーを使った単回帰分析の仕方
上記の処理によりメニューバーの「データ」から「ソルバー」を使える様になり、
下図がそのソルバーの作業画面で、その下が作業手順です。
① まず最初の「目的セルの設定」でさっきの二乗和を選択します。
② 次の「目標値」では、さっきの二乗和が最小になるのを探すので
「最小値」を選びます。
この他に「最大値」になるとか、
ある特定の値を指定する「指定値」も選べます。
③ 次の「変数セル」でさっきのaとbのセルを選びます。
④ この係数aとbはマイナスになる事もあるので、「制約のない変数を非負数にする」のチェックを外します。
⑤そして「解決」ボタンを押します。
すると「ソルバーによって解が見つかりました。」の画面が表示され、
a と b のセル（黄色セル）にその数値が出てきました。
切片の数値が多少違いますが、
最初の回帰式の係数とほぼ一致しています。
これがソルバーの働きです。
最小二乗法とソルバーを使った非線形の回帰分析の仕方
このソルバーは応用範囲が広く色んな用途で使えます。
今回の質問者さんの非線形の最小二乗法もこれでやれます。
非線形とは直線でないという事です。
簡単な式でいうとY = a X + b X^2 + cです。
次にその係数 a と b、c を求める回帰分析をやってみます。
ここにサンプルデータを用意しました。
隣がこのデータの散布図です。
なめらかな曲線ですね。
答えを先に言うとこの回帰式は Y=−3.4 𝑋+12.1 𝑋^2+5.2 なのです。
これを最小二乗法とソルバーで解いてみます。
まず係数の a と b、c のセルを用意します。（下図内、セルG3、H3、I3）
それが変数セルになり、それを参照して、
Xの値（E列）を使ってYを計算する数式をD列に書きます。
セルD3内
=$G$3*B3+$H$3*B3^2+$I$3
絶対参照を使ってますので、そのまま下にコピーします。
変数セルがまだどれも０なので計算結果もどれも０になります。
このC列とD列が同じになる変数セルの a、b、c を求めるのです。
E列にC列とD列の差の二乗する式を入れます。
セルE3内
=(C3-D3)^2
それを下までコピーして、その下のセルでSUM関数で二乗和を求めます。
これでソルバーの準備ができました。ソルバーを起動します。
① 今回も目的セルは二乗和です。
② 目標値は「最小値」で、
③「変数セル」はa、b、cのセルです。
④「制約のない変数を非負数にする」のチェックを外します。
⑤ 非線形の場合はもう少しやる事があって、
「オプション」をクリックして、
⑥「GRG非線形」タブで、
⑦「微分係数」を「中央」にします。
線形の時は「前方」でも同じ解を得ますが、
非線形の場合「中央」の方が誤差が小さく正確です。
⑧ これで「OK」と、最初の画面の「解決」をクリックします。
「ソルバーによって解が見つかりました」と出たら成功です。
変数セルの結果を見ると、最初の係数と全く同じ数字が出てきて、
非線形の最小二乗法が成功したのが分かります。
この様に最小二乗法とソルバーを使う事で
線形でも非線形でも色んな回帰分析ができる様になります。
ですのでぜひ使ってみて下さい。
今回は学術的な話でしたが次回の動画では
このソルバー機能をどう実務で使えるかを話します。
＜＜ 回帰分析シリーズ ＞＞
第一話：回帰分析をエクセルの散布図でわかりやすく説明します！
第二話：単回帰分析の結果の見方（エクセルのデータ分析ツール）
第四話：← 今回の記事
