[XL]LINEST 関数を使用した多変数の線形回帰分析

詳細

Excel 5.0 で用意されているワークシート関数についての詳細は、[?] - [キー
ワードで検索] コマンドを実行して「キーワード」に関数名を入力して検索する
か、または [挿入] - [関数] コマンドを実行して [関数ウィザード] の <ヘル
プ> を参照することができます。
本文書ではいくつかのワークシート関数を紹介しています。各関数についての詳
細は、オンライン ヘルプを参照してください。
ここでは、次の表をもとに解説しています。

従属変数 (Dependent Variable)

目的変数 (Criterion Variable) ともいいます。

独立変数 (Independent Variable)

説明変数 (Explanation Variable) ともいいます。

1. 線形回帰分析

最小二乗法を使って、指定したデータに最もよく当てはまる直線を算出し、
この直線を記述する係数と y 切片との配列を返します。直線の方程式は次
のように表すことができます。

  y = m1x1 + m2x2 + ... + b
    または、
  y = mx + b

ここで、従属変数の y は独立変数 x の関数です。また、m はそれぞれの x に
対応する係数で、b は y 切片と呼ばれる定数です。y、x、m はベクトル (1 次
元の配列) であることに注意してください。LINEST 関数によって返される配列
は、{mn,mn-1,...,m1,b} となります。

2. 回帰直線式の各係数と y 切片を求める方法

まず、LINEST 関数を使用して各係数と y 切片 ({mn,mn-1,...,m1,b}) を求めます。 LINEST 関数はベクトル (1 次元の配列) 配列で係数と y 切片を返しますので、戻
り値を配列で返すように関数を記述します。
この表では、独立変数が 3 個あることから、それぞれの係数が 3 つ、y 切片が
1 つの戻り値を持ち、求めた値を表示するために必要なセルは全部で 4 つとなる
ことがわかります。
各係数と y 切片をセル範囲 A10:D10 (1 行 4 列の 4 つのセル) に表示する手順
は次の通りです。

手順

1. セル範囲 A10:D10 を選択します。
2. 数式「=LINEST(E2:E7,A2:C7) 」を入力します。 引数には従属変数、独立変数の入力されているセル範囲を指定します。
3. 配列数式として入力するため、[Ctrl] + [Shift] + [Enter] キーを押して 式を確定します。 配列数式が確定されると [数式バー] には次のように中カッコ ({}) で囲 まれた数式が表示されます。

        {=LINEST(E2:E7,A2:C7)}
   

4. 計算結果は次のようになります。
   

配列数式「=LINEST(E2:E7,A2:C7) 」では、 {A10:D10} = {0.7093176,-0.8931,1.3988,38.1165} と計算されます (それぞれ
の数値は、そのセルの表示形式により桁数が異なります)。
この値は「 y = 0.7093176x1 + (-0.8931)x2 + 1.39882x3 + 38.11654 」と
して近似直線の各係数にあてはめることができます。
近似値を計算するときは、数式「=A10*A2-B10*B2...」のように、LINEST 関
数で計算されたセルを参照して係数の値にしてください。セル内には、セル
に表示されていない細かい範囲の値まで実際は格納されています。そのため、
数式「=0.7093176*A3-0.8931*B3.....」のように、セルに表示されている数
値「 0.7093176 」を係数として直接入力して計算すると誤差が発生する場合が
あります。

3. 回帰直線の補正項を求める方法

LINEST 関数を使用して、各係数と y 切片だけでなく R2 (決定係数
Coefficient of Determination) や 残差平方和 (Residual Sum of Squares)
などを簡単に求めることができます。
係数や y 切片を求めるのと同様に、配列数式として関数を入力することによっ
てそれぞれの値を求めることができます。
追加の補正項が配列として返される順序は次の通りです。

この表では、「 2. 回帰直線式の各係数と y 切片の求める方法」で示した通り、
それぞれの係数が 3 つ、y 切片が 1 つの戻り値を持つため、配列の列数は 4
列となります。行数は 5 行です。
回帰直線の補正項をセル範囲 A12:D16 (5 行 4 列の 20 個のセル) に表示する
手順は次の通りです。

手順

1. セル範囲 A12:D16 を選択します。
2. 数式「=LINEST(E2:E7,A2:C7,TRUE,TRUE) 」を入力します。 引数には従属変数、独立変数の入力されているセル範囲を指定し、定数 および補正のオプションをどちらも「 TRUE 」に指定します。
   • 定数： 定数 b を 0 にするかどうかを、論理値で指定します。 定数 に TRUE を指定するか省略すると、b の値も計算されます。FALSE を 指定すると、b の値が 0 に設定され、y = mx となるように m の値が調整 されます。
   • 補正： 回帰直線の補正項を追加情報として返すかどうかを論理値で指定します。
3. 配列数式として入力するため、[Ctrl] + [Shift] + [Enter] キーを押して式を 確定します。 配列数式が確定されると [数式バー] には次のように中カッコ ({}) で 囲まれた数式が表示されます。

        {=LINEST(E2:E7,A2:C7,TRUE,TRUE)}
   

4. 計算結果は次のようになります。
   

配列数式「=LINEST(E2:E7,A2:C7,TRUE,TRUE) 」では、{A12:D16} = {0.7093176,-0.8931,1.3988,38.1165;0.190115,0.754839,0.8165,64.7762;
0.9333103,2.781367,#N/A,#N/A;9.3298849,2,#N/A,#N/A;216.528,15.472,#N/A,#N/A} と計算されます (それぞれの数値は、そのセルの表示形式により桁数が異なります)。

4. LINEST 関数の戻り値を個別に求める方法

「 2. 回帰直線式の各係数と y 切片の求める方法」および「 3. 回帰直線の補正
項を求める方法」では、LINEST 関数を配列数式として使用し一度に複数の値を
算出しました。
配列の各要素を「配列」としてではなく「単独の数値」として求めたい場合は、
ワークシート関数である INDEX 関数を使います。この場合は、配列を返す数式で
はないため、一つのセルに数式を入力後 [Enter] キーのみで確定します。

5. 参照

それぞれの計算数式の化学的原理や用語については、市販の専門書などを参照
してください。
また、[分析ツール] コマンドの [回帰分析] ツールでは、これらの計算結果を一覧表として出力することができます。詳細は、ユーザーズ ガイド 第 31 章 統計データの解析を参照してください。