[Excel]如何利用規劃求解工具進行自訂函數迴歸工作?

藉由Excel圖表的"加上趨勢線"功能，可進行簡易線性和對數的迴歸分析以達到目標。然而，若針對非線性或是自訂函數的迴歸需求，則要進一步善用Excel的規劃求解工具。本文以高斯分佈(Gaussian distribution)為範例，說明如何以Excel規劃求解工具進行迴歸分析工作。

產出高斯分佈數據

a.首先，利用Excel亂數工具箱產出高斯分佈數據，該範例依照平均值為5和標準差為0.01的條件取得25個數據。
b.利用Frequency函數產出bin數據，對應的次數直方圖如本文封面圖，其中灰色直方條即為發生的次數，部分數據呈現如圖三所示。

以Excel規劃求解工具進行迴歸分析

a.依據高斯分佈和初始參數猜測值(a, b,c)，計算對應次數以及與原數據的殘差平方和，如圖三的Distribution by initial para. 資料表。
b.開啟規劃求解工具箱，根據高斯分佈公式意義，定義(a, b,c)合理的上下限數值，並依序新增於限制式中。
c.將平方和欄位定義在設定目標儲存格選項中，並設定為最小值，換句話說，目標為取得最小平方和的(a, b,c)數值。
d.因該分佈的(a, b,c)數值不為負值，提醒勾選"採用非負值"選項。
e.上述設定如圖一和圖二所示。

圖一 規劃求解設定 

圖二 規劃求解設定

結果討論

1.當初始參數值(a, b,c)=(8, 4.92 , 0.2)，其平方和約為212.2，同時R-Square為負值，如圖三紅框所示。

2.經過迴歸擬合後，由演算結果可知，當參數(a, b,c)=(7.95, 4.99 , 0.04)，其平方和為最小，R-Square約為0.57，如圖三藍框所示。換句話說，該組參數(7.95, 4.99 , 0.04)更能夠描述這25個數值的出現次數分佈。

3.數值和圖表結果可參考本文封面圖和圖三。

圖三 迴歸結果與比較

延伸閱讀: [Excel]如何快速取得符合條件的亂數群? 亂數產生器