KB402427 - [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 」と表示されている数式の [表示形式] を変更すると、それらの数値の実際の値は、画面で表示されている値よりも小さい桁の部分が存在することが確認できます。

確認方法

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

「近似曲線ラベル」の回帰方程式は次のように表示されます。

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

<数式 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 切片」を求める方法は次の通りです。

手順

ワークシートのセル範囲 A9:B9 を選択します。
数式を入力します。
```
     =LINEST(B2:B7,A2:A7)
```
配列を返す数式のため、数式を確定する際に [Ctrl] + [Shift] + [Enter] キーを押します。
それぞれのセルの値は次のように表示されます (セルの幅や [表示形式] の設定によって表示される桁数は異なります)。
```
     __|______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 関数を使用して近似値を求める方法は次の通りです。

手順

ワークシートのセル範囲 C2:C7 を選択します。
数式を入力します。
```
     =TREND(B2:B7,A2:A7)
```
配列を返す数式のため、数式を確定する際に [Ctrl] + [Shift] + [Enter] キーを押します。

それぞれのセルの値は次のように表示されます (セルの幅や [表示形式] の設定によって表示される桁数は異なります)。

  __|_____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) |