song1st

參考網址:http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

最近在分析資料時會需要用到EXCEL圖表產生的趨勢線(Trendline)，這功能很方便可以自動產出趨勢線的公式及誤差(R)，但是圖表產生的公式參數用VBA好像取不出來...(倒)
找到另一個近似的方法

Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

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

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

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

2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

若想要在VBA運作時，先關掉所有的conrtrols，等運作完再打開可以參考以下的做法 Controls項目昰在UserForm上

 

Private Sub CommandButton_Click()
    'Disable All Controls while running
    DisableAllControls Me
 
    'Do anything you want...

    'Enable All Controls after finish
    EnableAllControls Me
End Sub


Function EnableAllControls(frm As UserForm)
    Dim ctrl As Controls
    For Each ctrl In frm.Control
        If TypeName(ctrl) = "CommandButton" Or TypeName(ctrl) = "ListBox" Or TypeName(ctrl) = "CheckBox" Or TypeName(ctrl) = "TextBox" Then
            ctrl.Enabled = True
        End If
    Next
    Set ctrl = Nothing
End Function

Function DisableAllControls(frm As UserForm)
    Dim ctrl As Controls
    For Each ctrl In frm.Control
        
        If TypeName(ctrl) = "CommandButton" Or TypeName(ctrl) = "ListBox" Or TypeName(ctrl) = "CheckBox" Or TypeName(ctrl) = "TextBox" Then
            ctrl.Enabled = False
        End If
    Next
    Set ctrl = Nothing
End Function

 

如果Controls昰放在Workbook上的

Private Sub CommandButton_Click()
    'Disable All Controls while running
    DisableAllControls
 
    'Do anything you want...

    'Enable All Controls after finish
    EnableAllControls
End Sub

Function EnableAllControls()
    Dim wks As Worksheet
    For Each wks In Worksheets
        Worksheet.DrawingObjects.Enabled = True
    Next
End Function

Function DisableAllControls()
    Dim wks As Worksheet
    For Each wks In Worksheets
        Worksheet.DrawingObjects.Enabled = False
    Next
End Function