Excel 統計関数: INTERCEPT

概要

この記事では、Microsoft Excel の INTERCEPT 関数について説明し、関数の使用方法を説明し、Excel 2003 およびそれ以降のバージョンの Excel の結果と、以前のバージョンの Excel の結果を比較します。

詳細情報

INTERCEPT(known_y's,known_x's) 関数は、x 値から y 値を予測するために使用される線形回帰直線のインターセプトを返します。

構文

INTERCEPT(known_y's,known_x's)

引数 (known_yknown_x) は、同じ数値データ値を含む配列またはセル範囲である必要があります。 多くの場合、INTERCEPT には、INTERCEPT(A1:A100、B1:B100) などのデータを含む 2 つのセル範囲が含まれます。

使用例

INTERCEPT 関数を説明するには、空の Excel ワークシートを作成し、次の表をコピーし、空の Excel ワークシートでセル A1 を選択し、次の表にワークシートのセル A1:D13 が入力されるようにエントリを貼り付けます。

A B C D
y 値 x-values
1 = 3 + 10^$D$3 データに追加する 10 の累乗
2 =4 + 10^$D$3 0
3 =2 + 10^$D$3
4 =5 + 10^$D$3
5 =4 +10^$D$3
6 =7 +10^$D$3 Excel 2002 以前
D3 = 7.5 の場合
=SLOPE(A2:A7,B2:B7) -23717082.0762629
=INTERCEPT(A2:A7,B2:B7) -24516534.4029667
= AVERAGE(A2:A7) - A9*AVERAGE(B2:B7) D3 = 8 の場合
=AVERAGE(A2:A7) - 0.775280899*AVERAGE(B2:B7) #DIV/0!
-77528089.6303371

注:

このテーブルを新しい Excel ワークシートに貼り付けたら、[ 貼り付けオプション ] ボタンをクリックし、[ コピー先の書式に一致] をクリックします。 貼り付けられた範囲を選択したまま、実行している Excel のバージョンに応じて、次のいずれかの手順を使用します。

  • Microsoft Office Excel 2007 で、[ホーム] タブをクリックし、[セル] グループの [書式] をクリックして、[列の幅の自動調整] をクリックします。
  • Excel 2003 で、[書式] メニューの [列] をポイントし、[オートフィットの選択] をクリックします。

セル B2:B7 を小数点以下 0 桁の数値として書式設定し、セル A9:D13 を小数点以下 6 桁の数値として書式設定できます。

セル A2:A7 と B2:B7 には、セル A10 で INTERCEPT を呼び出す y 値と x 値が含まれます。

Excel 2003 より前のバージョンの Excel では、INTERCEPT によって丸めエラーが発生する可能性があります。 Excel 2003 以降のバージョンの Excel では、INTERCEPT の動作が向上します。 INTERCEPT(known_y、known_x) は AVERAGE(known_y) – SLOPE(known_y,known_x) * AVERAGE(known_x)評価した結果です。 Intercept のコードは Excel 2003 およびそれ以降のバージョンの Excel では直接変更されていませんが、SLOPE のコードが改善されたため、INTERCEPT の動作が改善されます。

以前のバージョンの Excel を使用している場合は、前に作成したワークシートを使用して実験を実行し、丸めエラーが発生した場合を検出できます。 B2:B7 の各観測値に正の定数を追加すると、SLOPE の値に影響を与えることはありません。 x,y ペアを縦軸に xy をプロットし、各 x 値に正の定数を追加すると、データは右にシフトするだけです。 最適回帰直線の傾きは同じです。 ただし、シフトされたデータのインターセプトは異なります。

D3 の既定値は 0 で、A9 の SLOPE は 0.775280899 です。 セル A10 は INTERCEPT の値を示し、セル A11 は INTERCEPT の計算時に評価される式の値を示します。

AVERAGE(known_y) – SLOPE(known_y、known_x) * AVERAGE(known_x)

セル A9 と A10 の値は常に一致します。これは、A10 の値が INTERCEPT から返される値であるためです。 KNOWN_Xに異なる正の定数を追加するため、SLOPE は変 わるべきではありません。 セル A11 には AVERAGE(known_y) – 0.775280899 * AVERAGE(known_x) が表示されます。 SLOPE は変更すべきではなく、D3 = 0 の場合は 0.775280899 が SLOPE の値であるため、A11 のこの式の値もセル A9 と A10 の値と一致する必要があります。

D3 で値を大きくすると、B2:B7 に大きな定数が追加されます。 D3 <= 7 の場合、SLOPE の小数点以下の先頭 6 桁に表示される丸めエラーはありません。 ただし、7.25、7.5、7.75、8 を試すと、A9 の SLOPE が変わります。 その結果、セル A11 (A10 と一致) と A12 の値が異なります。 ただし、A11 (または A10) と A12 の値は同じである必要があります。 これは、known_x に定数を追加しても SLOPE には影響しないためです。

D7:D13 は、INTERCEPT が返す値と、SLOPE が変更されていない場合に INTERCEPT が返す必要がある値を示します。 これらの値のペアは、それぞれ D3 = 7.5 と 8 の場合に表示されます。 丸め誤差が非常に厳しくなったため、D3 = 8 の場合に 0 による除算が発生します。

以前のバージョンの Excel では、これらのバージョンで使用される計算数式を使用すると、丸め誤差の影響が大きくなるため、このような場合は間違った回答が得られます。 それでも、この実験は、エラーが発生するケースが極端であることを示しています。

Excel 2003 以降のバージョンの Excel を使用している場合は、A10 と A11 の共通値と、実験を試してみると A12 の値の間にほとんどまたはまったく違いはありません。 ただし、セル D7:D13 には、以前のバージョンの Excel で取得した丸めエラーが表示されます。

以前のバージョンの Excel の結果

SLOPE に関する記事では、以前のバージョンで使用される数値的に堅牢でない数式について説明します。 数式では、データを通過する必要があるのは 1 つだけです。 これらのバージョンの SLOPE の欠点のみが原因で、極端なケースでは INTERCEPT によってラウンドオフ エラーが発生します。

Excel 2003 以降のバージョンの Excel の結果

Excel 2003 以降のバージョンの Excel では、改善されたプロシージャを使用して SLOPE を計算します。 その結果、INTERCEPT のパフォーマンスが向上します。 改善された手順では、データを 2 回通過する必要があります。 ここでも、SLOPE に関する次の記事では改善について説明しています。

EXCEL 2003 およびそれ以降のバージョンの Excel の SLOPE の機能強化の詳細については、次の記事番号をクリックして、Microsoft サポート技術情報の記事を参照してください。

Excel 統計関数828142: SLOPE

結論

Excel 2003 以降のバージョンの Excel は、1 パス アプローチを 2 パス アプローチに置き換えるため、Excel 2003 以降のバージョンの Excel の SLOPE の数値パフォーマンスは、以前のバージョンの Excel よりも優れています。 そのため、INTERCEPT の数値パフォーマンスが向上します。 Excel 2003 以降のバージョンの Excel の結果は、以前のバージョンの Excel の結果よりも正確ではありません。

通常、Excel 2003 以降のバージョンの Excel の結果と、以前のバージョンの Excel の結果には違いはありません。これは、この実験が示す異常な動作でデータが頻繁に動作することはないためです。 数値の不安定性は、データに多数の有効数字が含まれ、データ値間の変動が少ない場合に、以前のバージョンの Excel に表示される可能性が最も高くなります。

次の手順では、標本平均に関する二乗誤差の合計を見つけます。

  1. サンプル平均を見つけます。
  2. 各 2 乗誤差を計算します。
  3. 2 乗誤差を合計します。

この手順は、次の代替手順よりも正確です ("電卓の数式" とも呼ばれます)、少数のデータ ポイントに対する電卓での使用に適していました。

  1. すべての観測値の平方和、サンプル サイズ、およびすべての観測値の合計を見つけます。
  2. すべての観測値の平方和から減算 ((すべての観測値の合計)^2)/サンプル サイズ) を計算します。

この後者のワンパス プロシージャを、最初のパスでサンプル平均を検索し、2 番目のパスでそれに関する平方偏差の合計を計算する 2 パスプロシージャに置き換えることで、Excel 2003 以降のバージョンの Excel では、他の多くの関数が改善されます。 このような関数の簡単な一覧には、VAR、VARP、STDEV、STDEVP、DVAR、DVARP、DSTDEV、DSTDEVP、FORECAST、SLOPE、INTERCEPT、ピアソン、RSQ、および STEYX が含まれます。 Microsoft では、Analysis ToolPak の 3 つの分散分析ツールのそれぞれについて同様の改善を行いました。