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))

這最小平方法旨在選擇 a 的值，使實際 y(i) 值與對應的估計值或 y'(i) 值之間的差異平方和最小化。

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

因此 y(i) 和 y'(i) 值差的平方和為

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

右邊是誤差項的平方和，因此稱為誤差平方和或ESS。

將 y(i) 值轉換為 z(i) 值的過程

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

建立線性模型

z = 軸

它允許使用該LINEST函數來估計“最佳擬合”值A。根據您的答案（或您在 math.stackexchange 上的問題）中提供的數據，該最佳擬合值為A是 0.147233 - 與您在實現正規方程式的答案中得出的答案相同。

與該值相關的 ESSA是 8.27991。然而，該值並不是 ESS 可達到的最小值。發生這種情況時A取值為0.149140，對應的ESS為6.66073。

下面的螢幕截圖顯示了計算結果。

顯示了模型 y = 21 - exp(a*x) 的兩個版本的估計值 y'(i) 以及相關的誤差平方和 ESS 值。

在版本 1 中，A是使用該LINEST方法基於變換後的模型 z = ax 導出的。在版本 2 中，A是最小化（未轉換的）模型的 ESS 的值。更多關於這個值如何A下面提供了所獲得的。

對於 y = mx + c 等線性模型，正規方程式提供了一種便捷的方法來估計 m 和 c 的值，從而最小化 ESS。函數LINEST實現了（除其他外）正規方程式。

對於非線性模型（如 y = 21 - exp(a*x)）一般不存在這樣方便的方程，因此需要使用其他方法來求A這最大限度地減少了 ESS。

一種方法是使用搜尋方法：本質上嘗試一系列不同的可能性A並選擇 ESS 最小的那個。

這實際上就是下一個螢幕截圖所顯示的內容。它使用微軟所謂的數據表。這是一個糟糕的名稱選擇，因為資料表不是資料表。相反，它是一種工具，用於確定計算值如何隨著計算中的一個或兩個元素的變更而變化。透過從“假設分析”圖示中選擇“資料表...”，可以在功能區的“資料”選單的“預測”群組中找到它。

Microsft 建立和使用資料表的文件非常糟糕，因此我將提供一些食譜方法。

資料表本身在 N2:O23 範圍內提供。
單元格O2包含要探索的計算值。此儲存格包含=J4與下列值相關聯的 ESS公式：A在細胞中J3。
不同值的可能性A放置在範圍內N3:N23，ESS 的結果將出現在儲存格中O3:O23。這提供了 21 個可能的值A。這是任意選擇，資料表可以涉及更多或更少數量的可能值。
一旦A值就位後，選擇範圍N2:O23並透過從「資料」選單的「預測」群組中的「假設分析」圖示中選擇「資料表…」來啟動「資料表」對話方塊。
在對話方塊中，輸入$J$3標記為「列輸入儲存格：」的字段，然後按下「確定」按鈕。
O3:O23現在將使用與下列值相對應的 ESS 值填入該範圍A在N3:N23。更改任何值N3:N23都會更新中的 ESS 值O3:O23。

這A中的值N3:N23是透過公式設定的，而不是透過鍵入來設定的。A。

21號A中的值N3:N23是基於中央位置 11 的值 - 單元格N13- 其上方和下方的單元格依次不同增量量，以便 21 個值的整個範圍依升序排列。

搜尋策略經歷多個步驟，步驟數由 cell 中的數值控制O1。

在步驟 1 中，中央值設定為 0.15（在儲存格中R3）並且增量設定為 0.001（在儲存格中S3），給出的值N3:N23範圍為 0.14 到 0.16。該範圍是根據版本 1 值選擇的A，預計最小 ESS 值將落在該範圍內。

事實證明確實如此。對於 21 個值A從 0.14 開始，增加 0.001 到 0.16，對應的 ESS 值從超過 39 開始（當A為 0.14)，減少為A增加直到A值為 0.149（當 ESS 為 6.66972 時），然後增加，當 ESS 值超過 70 時A是0.16。這表明，值A使 ESS 最小化的值在 0.149 附近。

（如果沒有證明在某個值的範圍內找到最小值，則 ESS 值將全部增加或減少，將最小值放在該範圍的一端。在這種情況下，中央值（在單元格中R3）將需要調整，可能會增加增量值（在單元格中S3），直到找到中間範圍的最小值。

對於中的任何值範圍N3:N23，單元格O27和N27分別確定最小 ESS 值和的值A產生最小值。

的價值A生產最少的，提供新的中央下一步搜尋的值。新的增量是之前的值減少了 10 倍。中央和增量值手動輸入到「控製表」的列中R，並且S步驟編號在儲存格中手動增加 1 O1。

搜尋透過連續的步驟進行，當無法獲得 ESS 值的實際減少時終止。

螢幕截圖顯示了搜尋步驟 2 的結果。

Answer