[XL]近似曲線「線形近似曲線」について

詳細

線形近似

近似直線 (回帰直線) を一次方程式 y = mx + b で作成します。
Excel 5.0 では、作成した「近似曲線」の回帰方程式をグラフ上に表示することが
できます。
ワークシート上で回帰方程式の「傾き (m) 」と「 y 切片 (b) 」を求めるためには、 ワークシート関数である LINEST 関数を使用します。また、「傾き」や「 y 切片」
を求めることなく、直接 <新しい x> の配列に対する y の値を近似的に求めるに は、ワークシート関数である TREND 関数を使用します。

グラフ上で回帰方程式の「傾き」と「切片」を求める方法

次の表は、1 月から 6 月までの月別の売上を示しています。

  __|___A___|_______B______|
   1|売上月 |売上 (単位:万)|
   2|      1|         4,200|
   3|      2|         6,100|
   4|      3|         7,300|
   5|      4|         7,850|
   6|      5|         8,700|
   7|      6|        10,500|

「線形近似曲線」と「近似曲線ラベル」の表示

この表のセル範囲 A2:B7 から、グラフの散布図を作成します。作成した散布図の系
列を選択し、[挿入] - [近似曲線] コマンドを実行すると [近似曲線の追加] ダイ
アログ ボックスが表示されます。[種類] パネルの [近似 / 回帰の種類] グルー
プから [線形近似] を選択後、続けて [オプション] パネルの [グラフに数式を
表示] チェックボックスをオン (×がついている状態) にし、<OK> ボタンを
クリックします。散布図に「線形近似曲線」が追加され、「近似曲線ラベル」
として「近似曲線」の回帰方程式が表示されます。

  y = 1138.6x + 3456.7    ・・・・・・ <数式 1>

「近似曲線ラベル」で得た「傾き」と「 y 切片」の値

「近似曲線ラベル」で表示される係数「 m = 1138.6 」、「 b = 3456.7 」を一次方程式 y = mx + b に代入した数値を使って折れ線グラフを作成すると、[挿入] - [近似
曲線] コマンドで追加された「線形近似曲線」とは若干異なったグラフになるケー
スがあります。
この「ずれ」の原因は、Excel 5.0 の [表示形式] の設定によるものです。
「 m = 1138.6 」、「 b = 3456.7 」と表示されている数式の [表示形式] を変更する と、それらの数値の実際の値は、画面で表示されている値よりも小さい桁の部分が
存在することが確認できます。

確認方法

1. 「線形近似曲線」が追加されているグラフをアクティブにします。
2. 「近似曲線ラベル」を選択します。
3. [書式] - [選択されたデータ ラベル] コマンドを実行します。
4. [データ ラベルの書式設定] ダイアログ ボックスの [表示形式] パネルを選択 します。
5. [定義] ボックスに表示されている「 G/標準」を削除し、「 0.00000000000000 」 と入力後 <OK> ボタンをクリックします。
6. 「近似曲線ラベル」の回帰方程式は次のように表示されます。

        y = 1138.57142857143000x + 3456.66666666667000    ・・・・・・ <数式 2>
   

7. <数式 1> と <数式 2> を比較してみると、「傾き」と「 y 切片」の値に違い が生じていることが確認できます。

ワークシート上で回帰方程式の「傾き」と「切片」および「近似値」を求める方法

前項での記述の通り、[挿入] - [近似曲線] コマンドで表示された回帰方程式の
係数をそのまま使用して計算するにはやや限界があります。しかし、ワークシー
ト関数で求めた「傾き」と「 y 切片」を使用した回帰方程式の値、またはワーク
シート関数で求めた近似値 (予測値) を使用することによって、[挿入] - [近似
曲線] コマンドで追加された「線形近似曲線」と同様のグラフを作成することが
できます。
次の表は、1 月から 6 月までの月別の売上を示しています。

  __|___A___|_______B______|
   1|売上月 |売上 (単位:万)|
   2|      1|         4,200|
   3|      2|         6,100|
   4|      3|         7,300|
   5|      4|         7,850|
   6|      5|         8,700|
   7|      6|        10,500|

LINEST 関数を使用して「傾き」と「 y 切片」を求める方法

ワークシート関数である LINEST 関数は、最小二乗法を使って指定したデータに
もっともよく当てはまる直線を算出し、この直線を記述する「傾き」と「 y 切片」
との配列を返します。

  LINEST(B2:B7,A2:A7) = 1138.57,3456.7

この数式から「傾き」が 1138.57、「 y 切片」が 3456.7 の直線であると判断できま す。
セルに表示される「傾き」および「 y 切片」の値の桁数は、セルの幅や [表示形式] の設定によって変化します。しかし、内部的にはそれ以下の桁の数値も格納されて
います (IEEE 規格により精度は 15 桁まで)。これらのセルを参照する数式を
作成することによって、グラフの「近似曲線ラベル」で表示されるような「ず
れ」のない値を得ることができます。
LINEST 関数を使用して「傾き」と「 y 切片」を求める方法は次の通りです。

手順

1. ワークシートのセル範囲 A9:B9 を選択します。
2. 数式 を入力します。

        =LINEST(B2:B7,A2:A7)
   

3. 配列を返す数式のため、数式を確定する際に [Ctrl] + [Shift] + [Enter] キー を押します。
4. それぞれのセルの値は次のように表示されます (セルの幅や [表示形式] の設定に よって表示される桁数は異なります)。

        __|______A______|______B______|
         8|傾き         |y 切片       |
         9|  1138.571429|  3456.666667|
   

備考 1

「傾き」と「 y 切片」を配列としてではなく「単独の数値」として求めたい場合に
は、次のような数式を入力します。この場合は配列を返す数式ではないため、数
式を確定する際は [Enter] キーを押すか、または [数式バー] の [入力ボック
ス] をクリックします。

  「傾き」  INDEX(LINEST(B2:B7,A2:A7),1)    計算結果は 1138.57
  「 y 切片」 INDEX(LINEST(B2:B7,A2:A7),2)    計算結果は 3456.7

備考 2

C 列に B 列の近似値 (y の値) を求めるには、一次方程式 y = mx+b により次のよ
うな数式を入力します。

  __|_____A_____|_______B_______|______C______||_____数式_____|
   1|売上月     |売上 (単位:万) |y の値       ||C 列の数式    |
   2|          1|          4,200|  4595.238095||=$A9*A2+$B$9  |
   3|          2|          6,100|  5733.809524||=$A9*A3+$B$9  |
   4|          3|          7,300|  6872.380952||=$A9*A4+$B$9  |
   5|          4|          7,850|  8010.952381||=$A9*A5+$B$9  |
   6|          5|          8,700|   9149.52381||=$A9*A6+$B$9  |
   7|          6|         10,500|  10288.09524||=$A9*A7+$B$9  |
   8|傾き       |y 切片         |             ||              |
   9|1138.571429|    3456.666667|             ||              |

TREND 関数を使用して「傾き」と「 y 切片」を求めずに近似値 (y の値) を求める方法 ============================================================================== ワークシート関数である TREND 関数は、<既知の y> と <既知の x> のデータを最 小二乗法を使って直線にあてはめ、その直線上で、指定した <新しい x> の配列に 対する <y の値> を近似的に計算します。

  =TREND(B2:B7,A2:A7) = 4595.238,5733.81,6872.381,8010.952,9149.524, 10288.1

この数式から B 列の近似値 (y の値) を求めることができます。
セルに表示される近似値の桁数は、セルの幅や [表示形式] の設定によって変化しま す。
TREND 関数を使用して近似値を求める方法は次の通りです。

手順

1. ワークシートのセル範囲 C2:C7 を選択します。
2. 数式 を入力します。

        =TREND(B2:B7,A2:A7)
   

3. 配列を返す数式のため、数式を確定する際に [Ctrl] + [Shift] + [Enter] キーを 押します。
4. それぞれのセルの値は次のように表示されます (セルの幅や [表示形式] の設定に よって表示される桁数は異なります)。 __|_____A____|_______B______|______C______||_________数式_________| 1|売上月 |売上 (単位:万)|y の値 ||C 列の数式 | 2| 1| 4,200| 4595.238095||=TREND(B2:B7,A2:A7) | 3| 2| 6,100| 5733.809524||=TREND(B2:B7,A2:A7) | 4| 3| 7,300| 6872.380952||=TREND(B2:B7,A2:A7) | 5| 4| 7,850| 8010.952381||=TREND(B2:B7,A2:A7) | 6| 5| 8,700| 9149.52381||=TREND(B2:B7,A2:A7) | 7| 6| 10,500| 10288.09524||=TREND(B2:B7,A2:A7) |