KB402428 - [XL]近似曲線「多項式近似曲線」について

詳細

多項式近似

近似曲線を多項方程式 y = ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g で
作成します。多項式回帰は曲線回帰とも呼ばれます。
Excel 5.0 では、作成した「近似曲線」の回帰方程式をグラフ上に表示すること
ができます。
ワークシート上で回帰方程式の係数を求めるためには、ワークシート関数である
LINEST 関数を使用します。

グラフ上で回帰方程式の係数を求める方法

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

  __|___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|
   8|      7|        16,650|

「多項式近似曲線」と「近似曲線ラベル」の表示

この表のセル範囲 A2:B8 から、グラフの散布図を作成します。作成した散布図の系
列を選択し、[挿入] - [近似曲線] コマンドを実行すると [近似曲線の追加] ダイ
アログボックスが表示されます。[種類] パネルの [近似 / 回帰の種類] グルー
プから [多項式近似] を選択し、[次数] ボックスに「 6 」と入力します。続けて
[オプション] パネルの [グラフに数式を表示] チェックボックスをオン (×が
ついている状態) にし、<OK> ボタンをクリックします。散布図に「多項式近似曲線」が追加され、「近似曲線ラベル」として「近似曲線」の回帰方程式が表
示されます。
y = 5.75x^6 - 130.75x^5 + 1193.8x^4 - 5442.9x^3 + 12501x^2 - 11716x + 7790

・・・・・ <数式 1>

「近似曲線ラベル」で得た係数の値

「近似曲線ラベル」で表示される係数を多項方程式 y = ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g に代入した数値を使って折れ線グラフを作成すると、[挿入] -
[近似曲線] コマンドで追加された「多項式近似曲線」とは異なったグラフになる
ケースがあります。
この「ずれ」の原因は、Excel 5.0 の [表示形式] の設定によるものです。
「近似曲線ラベル」に表示されている数式の [表示形式] を変更すると、それらの数値の実際の値は、画面で表示されている値よりも小さい桁の部分が存在することが
確認できます。

確認方法

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

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

     y = 5.750000257045x^6 - 130.750006273388x^5 + 1193.750060796730x^4
        - 5442.916955947870x^3 + 12500.500648498500x^2 - 11716.334896087600x
        + 7790.003189086910

・・・・・ <数式 2>

<数式 1> と <数式 2> を比較してみると、「傾き」と「 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|
   8|      7|        16,650|

LINEST 関数を使用して係数を求める方法

ワークシート関数である LINEST 関数は、最小二乗法を使って指定したデータに
もっともよく当てはまる直線を算出し、この直線を記述する係数の配列を返します。まず、C、D、E、F、G、H の各列に数式を入力し、A 列に対する累乗の値を計算しま
す。

  <数式>
  __|___C___|___D___|___E___|___F___|___G___|___H___|
   1|X^1    |X^2    |X^3    |X^4    |X^5    |X^6    |
   2|=A2^1  |=A2^2  |=A2^3  |=A2^4  |=A2^5  |=A2^6  |
   3|=A3^1  |=A3^2  |=A3^3  |=A3^4  |=A3^5  |=A3^6  |
   4|=A4^1  |=A4^2  |=A4^3  |=A4^4  |=A4^5  |=A4^6  |
   5|=A5^1  |=A5^2  |=A5^3  |=A5^4  |=A5^5  |=A5^6  |
   6|=A6^1  |=A6^2  |=A6^3  |=A6^4  |=A6^5  |=A6^6  |
   7|=A7^1  |=A7^2  |=A7^3  |=A7^4  |=A7^5  |=A7^6  |
   8|=A8^1  |=A8^2  |=A8^3  |=A8^4  |=A8^5  |=A8^6  |
  <値>
  __|___C___|___D___|___E___|___F___|___G___|___H___|
   1|X^1    |X^2    |X^3    |X^4    |X^5    |X^6    |
   2|      1|      1|      1|      1|      1|      1|
   3|      2|      4|      8|     32|     32|     64|
   4|      3|      9|     27|     81|    243|    729|
   5|      4|     16|     64|    256|   1024|   4096|
   6|      5|     25|    125|    625|   3125|  15625|
   7|      6|     36|    216|   1296|   7776|  46656|
   8|      7|     49|    343|   2401|  16807| 117649|
   LINEST(B2:B8,C2:H8)
   =  5.750001, -130.75, 1193.75, -5442.92,12500.5, -11716.3, 7790.001

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

手順

ワークシートのセル範囲 A10:G10 を選択します。
数式を入力します。
```
     =LINEST(B2:B8,C2:H8)
```
配列を返す数式のため、数式を確定する際に [Ctrl] + [Shift] + [Enter] キーを押します。
それぞれのセルの値は次のように表示されます (セルの幅や [表示形式] の設定によって表示される桁数は異なります)。 __|____A____|____B____|____C____|____D____|____E____|____F_____|____G____| 9|a |b |c |d |e |f |g |
```
  10| 5.750001|  -130.75|  1193.75| -5442.92|  12500.5|  -11716.3| 7790.001|
```

備考

I 列に B 列の近似値 (y の値) を求めるには、多項方程式 y = ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g により次のような数式を入力します。[書式] - [セル]
コマンドを実行し、[表示形式] パネルの [定義] ボックスの設定を「 0.000000000 」に変更すると、実際の値にかなり近い数値が求められていることが確認できます。

  __|_____I______|____________________________数式____________________________|
   1|y の値      |I 列の数式                                                  |
   2| 4200.000001|=$A$10*H2+$B$10*G2+$C$10*F2+$D$10*E2+$E$10*D2+$F$10*C2+$G$10|
   3| 6099.999994|=$A$10*H3+$B$10*G3+$C$10*F3+$D$10*E3+$E$10*D3+$F$10*C3+$G$10|
   4| 7300.000012|=$A$10*H4+$B$10*G4+$C$10*F4+$D$10*E4+$E$10*D4+$F$10*C4+$G$10|
   5| 7849.999986|=$A$10*H5+$B$10*G5+$C$10*F5+$D$10*E5+$E$10*D5+$F$10*C5+$G$10|
   6|       10500|=$A$10*H6+$B$10*G6+$C$10*F6+$D$10*E6+$E$10*D6+$F$10*C6+$G$10|
   7|       16540|=$A$10*H7+$B$10*G7+$C$10*F7+$D$10*E7+$E$10*D7+$F$10*C7+$G$10|