「Excel ・グラフウィザード」の近似曲線 (線形近似)は、Excelが提供する便利な機能の一つです。
線形近似は、「y = a + bx」 (Excelではy = mx + b )の式です。
(線形曲線とはグラフのデータが直線に近い並び方をしています。)
(1)データは、A1セルからB7セルに入力されています。(下記の図)
B8セルには、予想値を求めます。
(2)線形近似は、「y = a + bx」(Excelではy = mx + b )で表わされていますので、定数「b」を求めます。
「b」回帰係数(方向係数)です。「傾き」又は「勾配」とも云います
Excelでは、線形近似の「傾き」を求める関数としてSLOPE関数があります。
「b」の値は5.1429です。
SLOPE関数以外の方法で求める式は、ど素人の「Excel 回帰分析」表の見方を参照してください。
定数「a」は、「切片」又は「定数項」とも云います。
Excelでは、線形近似の「切片」を求める関数としてINTERCEPT関数があります。
「a」の値は14です。
INTERCEPT関数以外の方法で求める式は、ど素人の「Excel 回帰分析」表の見方を参照してください。
(3)決定係数R2乗(寄与率)を求めます。
決定係数R2乗は直線回帰の当てはまりの精度をみる尺度として用いられています。
Excelでは、線形近似の決定係数R2乗を求める関数としてRSQ関数があります。
決定係数R2乗の値は0.9643です。
なお、RSQ関数以外の方法で求める式は、ど素人の「Excel 回帰分析」表の見方を参照してください。
(4)グラフを作成 (グラフで確認)
・グラフウィザードを起動します。
・「グラフウィザード 1/4」が表示されるので、散布図を選択します。
・「グラフウィザード 2/4」が表示されるので、データ範囲を指定します。
系列は、「列」を選択。
完了ボタンを押します。
・散布図が表示されるのでプロットを右クリックします。
･近似曲線の追加をクリックします。
･近似曲線の追加ダイアロクボックスが表示されるので、「種類」から「線形近似」を選択します。
･「オプション」を選択します。
<近似曲線名>
自動のままです。
<予測>
後方補外(B)を「1」にします。データ「期(x)」の「7」に対する予測がありますので。
「グラフに数式を表示する」にﾚ点を入れます。
「グラフにR-2乗値を表示する」にﾚ点を入れます。
OKボタンを押します。
・式及び決定係数が表示されますので先に求めた値が確認できます。
また、x軸の「7」がy軸の50と交わっています。データ「期(x)」の「7」の予測値は「50」です。
Excel ソルバーを使用して線形近似の定数を求めます。 (線形近似の定数は、Excel ゴールシークでも求められます。
HPの「応用編」 → ゴールシーク → (3).線形近似(y=a+bx)のYからXの値を求めます。(参照))
・ソルバーを使用する場合には、作業列が必要です。下記のとおりです。
・A23セル及びB23セルに適当な数値を入力します(この場合は1です。)
・A23セル及びB23セルは、変化させるセルです。
・C26セルに「=$A$23+$B$23*A26」と入力
・D26セルに「=(B26-C26)^2」と入力
・C33セルに残差平方和と入力
・D33セルに「=SUM(D26:D31)」と入力
・C34セルの式はD33セルの値の確認用です。必要はありません。式=SUMXMY2(B26:B31,C26:C31)
・なお、データA列には、「期xの7」の値は抜いて表示しています。
残差平方和を簡易的に求める。
・C22セルに残差平方和と入力
・C23セルに「=SUMSQ(B26:B31-(A23+B23*A26:A31))」と入力
配列数式なので「Ctrl」キーと「Shift」キーを押しながら「OK」ボタンを押すか、「Enter」キーを押してください。
(6) ソルバーを行うため「ツール」→「ソルバー」をクリック
(7).「ソルバー：パラメータ設定」ダイアログボックスに下記のとおり入力します。
目的セル：$D$33
目標値：最小値を選択します。
変化させるセル：$A$23:$B$23
(8).「ソルバー：パラメータ設定」ダイアログボックス入力後に「実行」ボタンをクリック
｢ソルバー：探索結果」ダイアログボックスが表示されます。
解を記入する：を選択します。
(9).｢ソルバー：探索結果」ダイアログボックス確認後に「OK」ボタンをクリック
下記の図のように表示され、定数a及びbの値が求められます。
(10)関数等を使用せずに簡易に予測値を求める方法があります。
B2からB7の範囲を選択します。
B7セルの右下角(フィルハンドル)にマウスを当てますとマウスポインタが太く＋印に変わります。
その状態でB8セルまでドラックするとB8に予想値が表示されます。
(11)予測値(y ')を求める。
(12) 回帰直線上で｢x｣に対する予想値を求める
Excelでは、回帰直線上で｢x｣に対する予想値を求める関数としてFORECAST関数があります。
また、TREND関数でも求められます。
線形近似の予測値y ' を、SERIESSUM関数を使用しても求められます。<統計編>→最適な近似曲線を選ぶ
(part2) にありますので参考に！
(13) 決定係数R2乗 (表は(11)を使用)
実績値と予想値y' から求める求める。
決定係数R2乗 = 予測値y' の分散÷実績値の分散 (=77.14286/80は、0.9643)
実績値の分散(又は不偏分散) <=VARP(B2:B7)の値80、 =VAR(B2:B7の値96>
予測値y' の分散(又は不偏分散) <=VARP(C2:C7)の値77.14286、 =VAR(C2:C7)の値92.57143>
<使用した関数>
・SLOPE(即知のy,即知のx)[スロープ] ： 回帰直線の傾きを返します。
・INTERCEPT(即知のy,即知のx)[インターセフト] ： 回帰直線の切片の値を返します。
・RSQ(既知のy,既知のx)[スクエア･オブ･コリレーション] ： ピアソンの積率相関係数を2
乗した値を返します。
・SUMXMY2（配列1,配列2)[サム・オブ・エックス・マイナス・ワイスクエアド]
： 一つの配列に対応する要素の差を求め、差の平方したものの総和を計算します。
・FORECAST(X,既知のy,既知のx)[フォレキャスト]「既知のx」と「既知のy」から得られる回帰直線上で｢x｣に対する予想値を返します。
・TREND(既知のy,既知のx,新しいx,定数)[トレンド] ： 直線上の値を返します。