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

詳細

対数近似

近似曲線を対数方程式 y=cln(x)+b で作成します。
Excel 5.0 では、作成した「近似曲線」の回帰方程式をグラフ上に表示することが
できます。
ワークシート上で、対数方程式の係数を求めるためには、ワークシート関数である
LINEST 関数を使用します。

グラフ上で回帰方程式の係数（方程式上の c と b ） を求める方法

次の表は、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,540|

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

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

  y = 4836.6 Ln(x) + 2851.1   ・・・・・・ <数式 1>

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

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

確認方法

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

         y = 4836.55458378397000 Ln(x) + 2851.08453452015000      ・・・・・・ <数式 2>
   

7. <数式 1> と <数式 2> を比較してみると、値に違いが生じていることが確認 できます。 この「ずれ」の原因は、Excel 5.0 の [表示形式] の設定によるものです。 「近似曲線ラベル」に表示されている数式の [表示形式] を変更すると、それ らの数値の実際の値は、画面で表示されている値よりも小さい桁の部分が存在 することが確認できます。

ワークシート上で回帰方程式の係数を求める方法

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

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

ワークシート関数である LINEST 関数は、最小二乗法を使って指定したデータにもっ ともよく当てはまる直線を算出し、この直線を記述する係数の配列を返します。
LINEST 関数は、引数として 既知の X の値と 既知の Y の値が必要です。
既知の X としては、A 列にある売上月を 対数にした値（ LN 関数で計算）を使用
します。

  <数式>
  __|___C___|___D___|__
   1| LN(x) |       |
   2|=LN(A2)|       |
   3|=LN(A3)|       |
   4|=LN(A4)|       |
   5|=LN(A5)|       |
   6|=LN(A6)|       |
   7|=LN(A7)|       |
   8|=LN(A8)|       |
  <値>
  __|___C___|___D___|__
   1| LN(x) |       |
   2|     0 |       |
   3|0.69314|       |
   4|1.09886|       |
   5|1.38629|       |
   6|1.60943|       |
   7|1.79175|       |
   8|1.94591|       |

LN 関数により、X の値が求められたので、LINEST 関数に代入します。

  LINEST(B2:B8,C2:C8)
  =  4836.555, 2851.085

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

手順

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

        = LINEST(B2:B8,C2:C8)
   

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

        __|___A____|____B____|
        10|4836.555| 2851.085|
   

この値を使用して、対数近似方程式 y= cln(x) + b にそれぞれの LN(x) を代入する ことで近似値を求めることができます。

備考

既知の ln(X) と 既知 の Y だけで近似値を求めることができます。
下記の「 TREND 関数を使用して近似値を求める方法」をご参照ください。

TREND 関数を使用して近似値を求める方法

  =TREND(B2:B8,C2:C8)  =
  2851.085, 6203.529, 8164.583, 9555.973, 10635.22, 11517.03, 12262.59

これらは、それぞれの X に対しての近似値になります。
TREND 関数を使用して近似値を求める方法は次の通りです。

手順

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

         =TREND(B2:B8,C2:C8)
   

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

        __|___C___|___D___|__
         1| LN(x) |TREND()|
         2|     0 |2851.08|
         3|0.69314|6203.52|
         4|1.09886|8164.58|
         5|1.38629|9555.97|
         6|1.60943|10635.2|
         7|1.79175|11517.0|
         8|1.94591|12262.5|