Excel の正規方程式 (統計)

Excel の正規方程式 (統計)

私は統計学について学ぼうとしていますインフラ資産管理業界


特定の資産の状態を見つけるために使用される既存の指数回帰方程式があります。

Y = B - e

関連する質問で、誰かが私に「変数をa直接取得できます正規方程式「:」

基本的な指数回帰

ここに画像の説明を入力してください

それは有望に思えます。しかし、数学の知識がない私にとっては、その方程式の数学的表記を Excel 構文に変換するのが困難です。


質問:

どうやって計算すればいいですか?正規方程式Excel 2016 の構文/数式を使用していますか?

答え1

これは、投稿された回答に対するコメントで提起された問題に対処する代替案です。数学スタック交換

あなたのモデルは:

y = B - exp(a*x)

そして、あなたは、アプリオリ、B = 21 であると判定されました。

このモデルはy(i)とx(i)の値の正確な関係ではないので、通常は誤差項e(i)を追加してモデルを次のように表す。

y(i) = B - exp(a*x(i)) + e(i)

各x値に対してモデルによって推定されたyの値はy'(i)で表され、ここで

y'(i) = B - exp(a*x(i))

最小二乗法実際のy(i)値と対応する推定値またはy'(i)値との差の二乗の合計を最小化するaの値を選択することを目的としています。

y(i) - y'(i) = [B - exp(a* x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)

したがって、y(i)とy'(i)の値の差の二乗の合計は

合計[(y(i) - y'(i))^2] = 合計[e(i)^2]

右辺は誤差項の二乗和なので、誤差二乗和またはESS

y(i)値をz(i)値に変換するプロセスは、

z(i) = LN(21 - y(i))

線形モデルを作成する

z = 斧

これにより、LINEST関数を使用して「最適な」値を推定することができます。1つのあなたの回答(またはmath.stackexchangeの質問)で提供されたデータでは、この最も適合する値は1つの0.147233 です。これは、正規方程式を実装した回答で導き出した答えと同じです。

この値に関連付けられたESSは1つの8.27991です。しかし、この値はESSの最小達成値ではありません。これは、1つの値は 0.149140 となり、対応する ESS は 6.66073 になります。

下のスクリーンショットは計算結果を示しています。

ここに画像の説明を入力してください

モデルy = 21 - exp(a*x)の2つのバージョンについて、推定されたy'(i)と関連する誤差の二乗値およびESS値が表示されます。

バージョン1では、1つのLINESTは、変換されたモデルz = axに基づくアプローチを使用して導出されます。バージョン2では、1つのは、(変換されていない)モデルのESSを最小化する値です。この値がどのように機能するかについての詳細は、1つの得られた結果を以下に示す。

y = mx + c などの線形モデルでは、正規方程式は ESS を最小化する m と c の値を推定する便利な方法を提供します。関数はLINEST(他の機能とともに) 正規方程式を実装します。

非線形モデル(y = 21 - exp(a*x)など)の場合、このような便利な方程式は一般に存在しないため、他の方法を使用して値を見つける必要があります。1つのESS を最小限に抑えます。

一つのアプローチは検索方法を使うことです。基本的には、さまざまな可能性を試して1つのそして、ESS が最小になるものを選択します。

これは次のスクリーンショットに示されています。これはマイクロソフトがデータ表データ テーブルはデータのテーブルではないため、この名前は適切ではありません。むしろ、計算内の 1 つまたは 2 つの要素が変更されたときに計算の値がどのように変化するかを判断するためのツールです。データ メニューの予測グループのリボンで、[What-if 分析] アイコンから [データ テーブル...] を選択すると見つかります。

ここに画像の説明を入力してください

データ テーブルの作成と使用に関する Microsoft のドキュメントは非常にひどいので、ちょっとしたクックブック的なアプローチを提供します。

  1. データ テーブル自体は範囲 N2:O23 に提供されます。
  2. セルにはO2、調査する計算値が含まれています。このセルには、=J4値に関連付けられたESSである数式が含まれています。1つのセル内J3
  3. さまざまな価値の可能性1つの範囲内に配置されN3:N23、ESSの結果がセルに表示されますO3:O23。これにより、21の可能な値が提供されます。1つのこれは任意の選択であり、データ テーブルにはこれより多いまたは少ない数の可能な値が含まれる場合があります。
  4. 一度1つの値が配置されている場合は、範囲を選択しN2:O23、[データ] メニューの [予測] グループの [What-if 分析] アイコンから [データ テーブル...] を選択して、データ テーブル ダイアログを起動します。
  5. ダイアログで、$J$3「列入力セル:」というラベルの付いたフィールドに入力し、「OK」ボタンを押します。
  6. 範囲にはO3:O23、次の値に対応するESS値が入力されました。1つのN3:N23のいずれかの値を変更すると、のN3:N23ESS 値が更新されますO3:O23

1つのの値は、N3:N23入力するのではなく、数式で設定されます。値は、より細かい値のセットを探す検索戦略を使用して設定されます。1つの

21人1つのN3:N23の値は、中央位置11のセルの値N13- この上と下のセルは連続して異なるインクリメント21 個の値の範囲全体が昇順になるようにします。

検索戦略はいくつかのステップを経て実行されますが、ステップ数はセルの値によって制御されますO1

ステップ1では、中央値は0.15(セル内R3)に設定されており、インクリメントは0.001(セルS3)に設定されており、0.14から0.16の範囲の値を与えますN3:N23。この範囲は、バージョン1の値に基づいて選択されています。1つの最小 ESS 値がこの範囲内に収まると予想されます。

これは事実である。1つの0.14から始まり、0.001ずつ増加して0.16まで進むと、対応するESS値は39以上から始まります(1つの0.14)、減少するにつれて1つの増加するまで1つの0.149の値が(ESSが6.66972のとき)あり、その後増加してESS値が70を超えると、1つの0.16です。これは、1つのESS を最小化する値は 0.149 付近になります。

(もし、ある値の範囲内に最小値があることが判明しなかったら、ESS値はすべて増加または減少し、最小値は範囲の一方の端に置かれることになる。この場合、中央値(セル内R3)は調整が必要となり、インクリメントS3中間範囲の最小値が見つかるまで、セル内の値を繰り返します。

の任意の範囲の値についてN3:N23、セルO27とセルN27はそれぞれ最小ESS値との値を特定します。1つの最小限の成果を生み出すもの。

の価値1つの最小限の生産で、新たな中央検索の次のステップで値を設定します。新しいインクリメント以前の値の10分の1になります。これらの新しい中央そしてインクリメント値は「制御テーブル」の列とに手動で入力されRSステップ番号はセルで手動で 1 ずつ増加されますO1

検索は連続したステップを経て進み、ESS 値の実質的な削減が得られなくなった時点で終了します。

スクリーンショットは、検索のステップ 2 の結果を示しています。

答え2

Excel で表示すると次のようになります。

ここに画像の説明を入力してください


擬似コードで説明してみました:

  1. セット内の各レコードについて、x*LN(21-y) を計算します。それらの値の合計を計算します (これを「合計 1」と呼びます)。
  2. セット内の各レコードについて、x^2 を計算します。それらの値の合計を計算します (これを「合計 2」と呼びます)。
  3. 合計 1 を合計 2 で割ります。

同僚より:

は特定の観測値を表します。これらの計算はすべて、通常、観測値のペア。例えば、データに20組の観測値があったとします。ここでのペアとは、バツそしてええ値を一緒にすると、通常は と表記されます(x, y), (0, 20), (1, 20)....(20, 2)を表す'番目の観察ペアペア。

だから、もし(0, 20)= 1の場合、これは最初のペアを参照していることを意味します。= 14の場合、14番目のペアを取ります(14, 12)。一般に、数学的には、番目の観測ペアは(xi, yi)下付き文字です。

シグマ記号は= 1 からは、本質的には、最初の観測から最後の観測まで、すべての観測ペアを取得することを意味します。

関連情報