[Excel]如何取得趨勢線的參數值

 

為了讓同事在提供規格或是工程量測試算可以更加快速且正確，現在提供給他們的試算表基本上都是以函數置入，讓他們只要做簡單的數字輸入，即可得到想要的試算結果。

關於一些客戶提出的推估要求，常常會需要以現有數值結合擬合(fitting)來估算結果，有時更要拿擬合得到的參數做進一步的分析。當面臨到龐大的數據量，複製貼上就顯得特別礙手又容易造成錯誤。

上述Excel的例子，是用一組x,y數字做簡單的線性擬合，其中藉由 slope和intercept的函數指令可以得到趨勢線擬合參數數值，如黃色表格的a和b值。可拿趨勢線圖上的公式做驗證。

另外，不同的擬合方程式，如多項式和Exponential ，也可以使用，函數指令如下所示，作法同上。

Linear 

Equation: y = a * x + b
a: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Logarithmic 

Equation: y = (a * LN(x)) + b
a: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Power 

Equation: y=a*x^b
a: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)

Exponential 

Equation: y = a *e ^(b * x)
a: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)

2nd Order Polynomial 

Equation: y = (a2 * x^2) + (a1 * x ) + b
a2: =INDEX(LINEST(y,x^{1,2}),1)
a1: =INDEX(LINEST(y,x^{1,2}),1,2)
b : =INDEX(LINEST(y,x^{1,2}),1,3)