迴歸基本概念：
2變數 X與Y之間的統計關係，為一非確定值得關係，當X的值確定後，Y的值並非唯一恆定值。而用以表示如此2變數X與Y間的數學模式稱為迴歸方程式或機遇模式。
圖1.
如圖1體重與身高的散佈圖，身高(X)與體重(Y)的關係，身高(X)為因變數，體重(Y)則為反應變數；我們通常在統計推論中，藉由一組樣本資料及統計學中相關理論找出變數間的統計關係，以區分變數，建立適當的數學模式來表示變數間的統計關係，並稱此等數學模式為迴歸方程式；而運用樣本資料配適一個最佳數學模式的統計學方法與過程，稱之為迴歸分析。
迴歸分析既然是統計推論，自然有其必要基本假設要驗證，小編強調是針對樣本歐，迴歸分析有三個基本特性的檢定：常態性檢定、同質性檢定與隨機性檢定，這三個檢定有機會在聊聊。
回到主題，我們簡單整理一個例子。💪
簡單線性迴歸的長相：
透過最小平方法求兩個B0、B1的參數：
舉例：
某一保險公司想要調查火災損失和火災發生地與最近的消防展的距離關係。進行某城市案例資料統計如表。
哇，實在是太學術了!!!😰
開始把題目資料整理成EXCEL先......😀
先把資料堆壘：
圖2.
Excel版：透過EXCEL內建的分析工具箱，點選好資料後如下。
圖3.
Private Sub CommandButton1_Click()
s1 = ActiveSheet.Range("A2000").End(xlUp).Row
'以下是引用excel內建函數
b1 = Application.WorksheetFunction.LinEst(ActiveSheet.Range("c2:" & "c" & s1), ActiveSheet.Range("b2:" & "b" & s1))
'在儲存格中使用僅會回傳一筆資料，
'但vba時會回傳一個陣列的結果，內含b0與b1兩個迴歸的參數。
If b1(2) > 0 Then
MsgBox "y=" & Format(b1(1), "##.00") & "+" & Format(b1(2), "##.00") & "x"
Else
MsgBox "y=" & Format(b1(1), "##.00") & "-" & Format(b1(2), "##.00") & "x"
End If
End Sub
執行結果：
圖6.
好像跟用資料分析功能少了很多東西!!!少了很重要的"判定係數"💭
來整理一下總變異(SST)，迴歸變異(SSR)與無法解釋的變異(SSE)的公式，
並看看判定係數怎求😆
判定係數公式：
SST=SSR+SSE
修改程式：
流程：1.先載入資料轉換為一維陣列。2.開始計算B1、B0參數找出迴歸方程式。3.計算 SST SSR SSE ERROR Y預測值4.計算判定係數5.輸出結果
Sub CommandButton1_Click()
'LOAD DATA
s1 = ActiveSheet.Range("A2000").End(xlUp).Row
Y = ActiveSheet.Range("c2:" & "c" & s1)
Y = WorksheetFunction.Transpose(Y)
AVERAGE_Y = Application.Sum(Y) / UBound(Y)
X = ActiveSheet.Range("b2:" & "b" & s1)
X = WorksheetFunction.Transpose(X)
AVERAGE_X = Application.Sum(X) / UBound(X)
'REDIM ARRAY
ReDim B1_1(UBound(X))
ReDim B1_2(UBound(X))
'計算 B1
For A = LBound(X) To UBound(X) Step 1
B1_1(A) = ((X(A) - AVERAGE_X) * (Y(A) - AVERAGE_Y))
B1_2(A) = (X(A) - AVERAGE_X) ^ 2
Next A
B1 = Application.Sum(B1_1) / Application.Sum(B1_2)
'計算 B0
B0 = AVERAGE_Y - B1 * AVERAGE_X
'計算 SST SSR SSE ERROR Y預測值
ReDim SSR(UBound(Y))
ReDim SSE(UBound(Y))
ReDim Y_HAT(UBound(Y))
ReDim ERROR_DIS(UBound(Y))
For A = LBound(Y) To UBound(Y) Step 1
SSR(A) = ((B0 + B1 * X(A)) - AVERAGE_Y) ^ 2
SSE(A) = (Y(A) - (B0 + B1 * X(A))) ^ 2
Y_HAT(A) = (B0 + B1 * X(A))
ERROR_DIS(A) = Y(A) - Y_HAT(A)
Next A
SST = Application.Sum(SSR) + Application.Sum(SSE)
SSR_TOTAL = Application.Sum(SSR)
R = SSR_TOTAL / SST '判斷係數
R_root = R ^ 0.5 '取根號
'輸出
ActiveSheet.Range("D1:" & "D" & s1) = WorksheetFunction.Transpose(Y_HAT)
ActiveSheet.Range("E1:" & "E" & s1) = WorksheetFunction.Transpose(ERROR_DIS)
ActiveSheet.Range("D1:" & "F" & 1) = Array("預測 Y", "殘差")
ActiveSheet.Range("F1") = "迴歸模型："
If B1 > 0 Then
ActiveSheet.Range("g1") = "y=" & Format(B0, "##.00") & "+" & Format(B1, "##.00") & "x"
Else
ActiveSheet.Range("G1") = "y=" & Format(B0, "##.00") & "-" & Format(B1, "##.00") & "x"
End If
ActiveSheet.Range("F2") = "判斷係數："
ActiveSheet.Range("G2") = Format(R, "##.00%")
ActiveSheet.Range("F3") = "R 的倍數："
ActiveSheet.Range("G3") = Format(R_root, "##.00%")
End Sub
結果：
圖7.
對答案(EXCEL 內建的分析工具箱)：
圖10.
下一篇：運用EXCEL內建工具箱以VBA完成
另外借文章感謝一個朋友，感謝他提醒分享的重要。
疫情期間大家要健康!!!😆😆
相關文章：
沒有留言:
張貼留言