Excel の正規方程式 (統計)

Question 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 のドキュメントは非常にひどいので、ちょっとしたクックブック的なアプローチを提供します。

データテーブル自体は範囲 N2:O23 に提供されます。
セルにはO2、調査する計算値が含まれています。このセルには、=J4値に関連付けられたESSである数式が含まれています。1つのセル内J3。
さまざまな価値の可能性1つの範囲内に配置されN3:N23、ESSの結果がセルに表示されますO3:O23。これにより、21の可能な値が提供されます。1つのこれは任意の選択であり、データテーブルにはこれより多いまたは少ない数の可能な値が含まれる場合があります。
一度1つの値が配置されている場合は、範囲を選択しN2:O23、[データ] メニューの [予測] グループの [What-if 分析] アイコンから [データテーブル...] を選択して、データテーブルダイアログを起動します。
ダイアログで、$J$3「列入力セル:」というラベルの付いたフィールドに入力し、「OK」ボタンを押します。
範囲には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になります。これらの新しい中央そしてインクリメント値は「制御テーブル」の列とに手動で入力されR、Sステップ番号はセルで手動で 1 ずつ増加されますO1。

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

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

Answer