Hồi quy tuyến tính đa biến trong VBA

[Thư Sinh Áo Trắng]

- Đang lần mò về xây dựng đường hồi quy tuyến tính trong kinh tế lượng. Có các biến định lượng ví dụ: 1 kg thịt bò loại 1 giá 300K...và các biến định tính(biến giả) , ví dụ, thích và không thích.. Cả 2 biến này em có khoảng vài chục biến trong mô hình đang xây dựng.
- Excel cung cấp cho 2 Add-in: Analysis ToolPak và Analysis ToolPak - VBA

Sao em chon cả 2 mà chỉ hiện có một Add-in vậy nhỉ?

- Analysis ToolPak -> xem youtube thì biết cách làm
- Còn Analysis ToolPak - VBA chưa biết nó là cái gì. Mà mục đích của em là muốn dùng VBA để chạy mô hình hồi quy tuyến tính đa biến (trong môn kinh tế lượng)
- Mở đầu chủ đề "Hồi quy tuyến đa biến trong VBA" thì em xin được các anh chị từng làm qua xin giải đáp cho em. Là cách dùng Analysis ToolPak - VBA? Có thể dùng VBA chạy các Add-in này không ạ?
Em chân thành cảm ơn!

 

[CHAOQUAY]

- Các bác ơi, em đang mày mò lập trình xây dựng hồi quy bằng VBA. VBA hồi quy dưới 16 biến cố định thì cứ dùng Add-in Analysis ToolPaK là chạy được rồi! Và chắc hẳn rồi cũng mày mò cho giống giống phần mềm được nếu dưới 16 biến!
- Nhưng khi trên 16 biến thì Add-in Analysis ToolPaK không hỗ trợ chạy hồi quy nữa. Vậy phải lập trình hồi quy bằng VBA, và cách này thì có làm đến đâu đi nữa cũng không cách nào xử lý được vấn vấn đề đa cộng tuyến so với phần mềm chuyên dụng. Đến đây có lẽ nên oải về vấn đề "không xử lý được vấn đề đa cộng tuyến này"
- Nhưng đã leo nên lưng hổ rồi đành phải dành thêm thời gian cho nó nữa mới rõ vấn đề dùng luôn phần mềm chuyên dụng hay vẫn định hướng dùng VBA. Và với VBA thì khi xây dựng code em vướng một số vấn đề về sử dụng những hàm có sẵn trong excel và giới hạn phần tử của các hàm đó.
Xin các bác có gặp phải vấn đề giới hạn phần tử của một hàm, các bác thông tin em với. Em có seach nhưng không thấy nói về giới hạn các phần tử của một hàm tích hợp trong excel, cụ thể các hàm là:
TRANSPOSE giới hạn bao nhiêu phẩn tử?
MMULT giới hạn bao nhiêu phẩn tử?
MINVERSE giơi hạn bạn nhiêu phần tử?
Em đang tắc ở chỗ giới hạn phần tử của 3 hàm này! Có biết giới hạn của nó mới tạo ra các ma trận. Mới biết dữ liệu đầu vào có bao nhiêu quan sát và bao nhiêu biến cố định X được.
Xin chân thành cảm ơn! Mong nhận được giúp đỡ và thông tin!

Bạn gửi file > 16 biến lên xem sao

 

[Thư Sinh Áo Trắng]

Bạn gửi file > 16 biến lên xem sao

- Cứ có hơn 16 cột dữ liệu, dữ liệu thì cứ cho rand() rồi test nó báo vậy anh ơi. Cái này thì chắc chắn Analysis tolpak hỗ trợ tối đa 16 biến anh ạ.
- Tuy Analysis Toolpak không hỗ trợ hơn 16 biến nhưng vẫn dùng Excel để làm hồi quy được, đã test thử tới 255 biến, và hơn 1000 quan sát ( 1 triệu quan sát cũng được) Excel vẫn tính đc các ma trận. Vậy thì lập trình đc. Mà ko cần tới Analysis Toolpak

 

[Maika8008]

Mấy chục năm rồi không gặp dạng code kiểu này nên tôi đọc vụ này xem như mù tạm thời. Nói chuyện hồi quy đã nghe xa lạ lắm vì quên hết toán hồi xưa rồi, chưa nói đến tuyến tính với phi tuyến, 1 biến với nhiều biến.

 

[Thư Sinh Áo Trắng]

Mấy chục năm rồi không gặp dạng code kiểu này nên tôi đọc vụ này xem như mù tạm thời.

- Code về món này rất ít vọc khổ lắm anh ơi. Nhưng nếu là đề bài cụ thể thì GPE giải trong 1 nốt nhạc, em nghĩ là vậy.
- Code chủ yếu là tạo ra các ma trận:
+ Tạo rao ma trận chuyển vị từ ma trận dữ liệu đầu vào X
+ Tìm ma trận tích của ma trận đầu vào và ma trận chuyển vị của nó X(T)*X
+ Khi có X(T)*X thì có ma trận ngịch đảo của ma trận tích (X(T)*X)
+ Tìm ma trận hệ số tương quan
+ Tìm ma trận phương sai-hiệp phương sai.
Đến đây có 3 ma trận vuông: Ma trận nghịch đảo, Ma trân hệ số tương quan, Ma trận Phương sai- hiệp phương sai
Và đến đây giải 1 chút nữa là ra kết quả y như Analysis Toolpak bác ạ

 

[HieuCD]

+ Tạo rao ma trận chuyển vị từ ma trận dữ liệu đầu vào X
+ Tìm ma trận tích của ma trận đầu vào và ma trận chuyển vị của nó X(T)*X
+ Khi có X(T)*X thì có ma trận ngịch đảo của ma trận tích (X(T)*X)

Giải hệ phương trình ngoài phương pháp trên còn có nhiều cách khác

+ Tìm ma trận hệ số tương quan
+ Tìm ma trận phương sai-hiệp phương sai.
Và đến đây giải 1 chút nữa là ra kết quả y như Analysis Toolpak bác ạ

Muốn như Analysis Toolpak phải có kiến thức thật vững về toán thống kê như lý thuyết phân phối, kiểm định, tương quan ... để truy ngược công thức tính các kết quả, sau đó mới tính đến chuyện viết code

 

[Thư Sinh Áo Trắng]

Giải hệ phương trình ngoài phương pháp trên còn có nhiều cách khác

Muốn như Analysis Toolpak phải có kiến thức thật vững về toán thống kê như lý thuyết phân phối, kiểm định, tương quan ... để truy ngược công thức tính các kết quả, sau đó mới tính đến chuyện viết code

- Dùng những thứ sẵn có trong Excel giải được giống Analysis ToolPack phần Regression được bác ạ!
- Nếu bỏ qua "hiện tượng cộng tuyến"chỉ dự đoán lên xuống giá cả, cổ phiếu...giáo trình có nói điều này "chương hồi quy bội" thì làm trên Excel hoặc lập trình VBA giải quyết được vấn đề giới hạn số biến của Add-in Analysis ToolPack
- Qua thời gian mày mò em chốt được Max biến X là 255 (nếu hơn 255 biến thì một số hàm có sẵn không hỗ trợ) và Max quan sát bằng đúng bằng Rows.Count

 

[HieuCD]

- Dùng những thứ sẵn có trong Excel giải được giống Analysis ToolPack phần Regression được bác ạ!
- Nếu bỏ qua "hiện tượng cộng tuyến"chỉ dự đoán lên xuống giá cả, cổ phiếu...giáo trình có nói điều này "chương hồi quy bội" thì làm trên Excel hoặc lập trình VBA giải quyết được vấn đề giới hạn số biến của Add-in Analysis ToolPack
- Qua thời gian mày mò em chốt được Max biến X là 255 (nếu hơn 255 biến thì một số hàm có sẵn không hỗ trợ) và Max quan sát bằng đúng bằng Rows.Count

Làm sao tính
+ Tìm ma trận hệ số tương quan
+ Tìm ma trận phương sai-hiệp phương sai
+ Pvalue ...

 

[Thư Sinh Áo Trắng]

Làm sao tính
+ Tìm ma trận hệ số tương quan
+ Tìm ma trận phương sai-hiệp phương sai
+ Pvalue ...

- Khi có đủ ma trận giải được phương trình tìm hệ số be_ta thì qua khâu kiểm định thì phải tính ra P-value rồi,cứ theo lý thuyết rồi dùng hàm trong excel là lần lần ra hết anh ạ!(hồi quy bội kinh tế lượng xử dụng excel anh nhé)
- Em có bài trả lời với thời gian sớm nhất nhé!Vì lý thuyết của nó rất nhiều chi tiết, chưa sâu chuỗi lại được, Nhưng em khẳng định làm giông giống phần mềm được.

 

[HieuCD]

- Khi có đủ ma trận giải được phương trình tìm hệ số be_ta thì qua khâu kiểm định thì phải tính ra P-value rồi,cứ theo lý thuyết rồi dùng hàm trong excel là lần lần ra hết anh ạ!(hồi quy bội kinh tế lượng xử dụng excel anh nhé)
- Em có bài trả lời với thời gian sớm nhất nhé!Vì lý thuyết của nó rất nhiều chi tiết, chưa sâu chuỗi lại được, Nhưng em khẳng định làm giông giống phần mềm được.

Chờ sản phẩm của bạn, giai đoạn đầu chỉ cần vài biến với dữ liệu ít để test thử sau mới phát triển hoàn chỉnh

 

[Thư Sinh Áo Trắng]

Chờ sản phẩm của bạn, giai đoạn đầu chỉ cần vài biến với dữ liệu ít để test thử sau mới phát triển hoàn chỉnh

Code VBA các bác viết chứ em lẹt đẹt lắm, hàng tháng mới xong được!

 

[HieuCD]

View attachment 258648
Code VBA các bác viết chứ em lẹt đẹt lắm, hàng tháng mới xong được!

Quá giỏi, bạn trình các bảng tương đối tách biệt rồi dùng bộ thu macro thu lại các thao tác sau đó chỉnh lại theo vòng for là được

 

[Thư Sinh Áo Trắng]

Quá giỏi, bạn trình các bảng tương đối tách biệt rồi dùng bộ thu macro thu lại các thao tác sau đó chỉnh lại theo vòng for là được

- Đây là file em đã chuyển 99% lý thuyết thành hàm trong excel. Còn 1% là tìm khoảng tin cậy em chưa mò ra được. Mong nhận được sợ giúp đỡ 1% này
- Nhưng với 99% này nó đã đại diện mà minh họa thật đầy đủ phần tính toán trong Analysis ToolPack-Regression. Và em tin rằng GPE sẽ giúp được em chuyển thành code VBA. Em nghĩ hướng tối ưu khi lập trình bài toán này thì không nên sử dụng các hàm Application.WorsheetFuntion(hàm transpose giới hạn 5461 phần tử,nếu quá 5461phan tử thì không có ma trận chuyển vị ghi ra Sheets được) vì sợ sẽ phát sinh lỗi giới hạn phần tử.Dựa vào các hàm đã dùng sãn trong Sheets("Manual") này là gợi ý tạo ra các hàm riêng không bị giới hạn phần tử. Ưu việt nhất nên xây dựng các hàm tự tạo và dùng Sub gọi ra!
- Chúc các bác mạnh khỏe!
- Em chào các bác @HieuCD @Maika8008 @phuocam @snow25 @VetMini ...GPE!
****Em xin phép do em không phải chuyên môn thống kê và lập trình, có thiếu sót xin được góp ý!

 

[Thư Sinh Áo Trắng]

*Cho một bảng dữ liệu đầu vào có số dòng là j và k cột. Ở bảng dữ liệu này ta thêm vào một cột có các giá trị đều bằng 1 (1 này là 1# hay 1 Double chứ khổng phải 1 của integer đâu các bác nhé). Vậy lúc này ta có bảng dữ liệu m = j - 1(nếu bảng đó bao gôm cả cột Y, cột biến phụ thuộc) dòng và n = k+1 ( vì có thêm cột biến chặn). Và gọi bảng dữ liệu này là một ma trận X(ma trận của các biến độc lập) có cấp m*n (hay ký hiệu thường gặp ) . Và ở đây ta xử lý X để ước lượng được các hệ số của phương trình hồi quy bằng các hàm có sẵn trong Excel.Như sau:
- Tìm ma trận chuyển bị của X, tức là tìm X'. Ta dùng hàm TRANSPOSE: =TRANSPOSE(vùng dữ liệu của ma trận X). Ap dụng với ma trận có n <= 52
- Nhân ma trận X với ma trận X', được một ma trậ tích gọi tắt là A, ta dùng hàm MMULT: A =MMULT(X'X), hàm này chưa kiểm tra được giới hạn phần tử của nó, nhưng cứ ma trận X nào mà dùng được TRANSPOSE thì dùng được MMULT phải không nào. Nhưng chắc chắn muốn áp dụng được n <= 52 mới dùng được công thức này
- Khi có ma trận tích X'X là A thì ta dùng hàm MINVERSE(A) ta được ma trận B là ma trận ngịch đảo của A.Hàm MINVERSE có giới hạn là 52 cột và 52 dòng.
* Vậy rõ ràng là:
- Khi có 52 biến ( kể cả biến chặn) hay nói các khác ma trận có n <= 52 ta lập trình bằng các hàm có sẵn của Excel được, ghi marco nó hiện ra tham chiếu kiểu RC, cũng dễ chỉnh sửa để auto thôi
- Khi 52 biến trở lên áp dụng các hàm có sẵn thì gây lỗi #Value. Vậy ở trường hợp có trên 52 biến bắt buộc phải dùng các hàm tự tao:
+ Hàm tự tạo tìm ma trận chuyển vị (n >52)
+ Hàm tự tạo nhân hai ma trận (n>52)
+ Hàm tự tạo ngich đảo ma trận (n > 52)
(với là n chiều thứ 2 của Array)
- Còn với ma trận phương sai hiệp phương sai sẽ tính được khi có ma trận B
- Còn ma trận hệ số tương quan thì dùng hàm CORRE áp dụng không vướng mắc gì
* 3 hàm tự tạo này em xin các anh @HieuCD @Maika8008 và các thầy các bác chia sẻ, giúp đỡ phần code 3 hàm tự tạo này giúp em với ạ! Mấu chốt lập trình chạy hồi quy trên VBA là lập trình 3 hàm này thôi ạ.
Em xin chân thành cảm ơn sự giúp đỡ! Rất mong được phản hồi!

 

[Ngô Hải Đăng]

Hàm MINVERSE có giới hạn là 52 cột và 52 dòng.

Chắc do có ô nào đó ko có dữ liệu. Mình chạy thử 1000 cột 1000 dòng vẫn ok với excel 2013.

 

[Thư Sinh Áo Trắng]

Chắc do có ô nào đó ko có dữ liệu. Mình chạy thử 1000 cột 1000 dòng vẫn ok

Em đang 200 cột và 1000 dòng. Và tắc ngay ở chỗ hàm transpose. Không tìm được ma trận chuyển vị, nên chưa test được, cảm ơn anh có gì em sẽ đính chính lại.

 

[Ngô Hải Đăng]

Em đang 200 cột và 1000 dòng. Và tắc ngay ở chỗ hàm transpose. Không tìm được ma trận chuyển vị, nên chưa test được, cảm ơn anh có gì em sẽ đính lại.

Copy code này vô module

Function CheckArray(iArray)
  If TypeName(iArray) = "Range" Then iArray = iArray
  Dim x&, y&
  For x = LBound(iArray) To UBound(iArray)
    For y = LBound(iArray, 2) To UBound(iArray, 2)
      iArray(x, y) = Val(iArray(x, y))
    Next y
  Next x
  CheckArray = iArray
End Function

Trước khi dùng hàm tính toán ma trận thì thêm hàm CheckArray phía trước. Thay vì TRANSPOSE(Array) thì dùng TRANSPOSE(CheckArray(Array)), các hàm khác tương tự.

 

[Thư Sinh Áo Trắng]

Copy code này vô module

Function CheckArray(iArray)
  If TypeName(iArray) = "Range" Then iArray = iArray
  Dim x&, y&
  For x = LBound(iArray) To UBound(iArray)
    For y = LBound(iArray, 2) To UBound(iArray, 2)
      iArray(x, y) = Val(iArray(x, y))
    Next y
  Next x
  CheckArray = iArray
End Function

Trước khi dùng hàm tính toán ma trận thì thêm hàm CheckArray phía trước. Thay vì TRANSPOSE(Array) thì dùng TRANSPOSE(CheckArray(Array)), các hàm khác tương tự.

Cảm ơn bác! Em đinh tối về coutif. Với lại dữ liệu nó rộng quá kéo chuột xem dòng cuối các điểm cuối cũng mệt quá bác ạ.
Nhưng bài em hướng tới không dùng hàm tranpose đc rồi. Nếu mà TRANSPOSE(CheckArray(Array)) không bị #Value mà tới 1000 cột như bác đã làm thì quá tuyệt hảo. Và kết quả hồi quy giống bảng mô ta của Evews(uy tín hơn Analysis Toolpak)
Rất mong được anh chia sẻ

 

[Thư Sinh Áo Trắng]

Chắc do có ô nào đó ko có dữ liệu. Mình chạy thử 1000 cột 1000 dòng vẫn ok với excel 2013.

- Chỗ này ý bác bảo là dùng Tranpose được cho vùng 1000 dòng và 1000 cột được phải không ạ?
!!!!!!!!!!! Hôm trước với vùng bé hơn anh em dùng Transpose mà lỗi không hiểu sao nay làm lại lại transpose được. Dùng Function CheckArray của anh với không dùng hàm CheckArray đều Transpose được. Không hiểu sao.
Vậy chõ này em có lẽ xuất phát từ lỗi em lồng ghép các hàm =MINVERSE(MMULT(TRANSPOSE(vùng;vùng)) nên phát sinh lỗi không kiểm soát được. Chỗ này xin đính chính lại bài 53 là hàm transpose với vùng 1000 cột vẫn được

Chắc do có ô nào đó ko có dữ liệu. Mình chạy thử 1000 cột 1000 dòng vẫn ok với excel 2013.

Em cũng dùng Excel 2013. Không biết chỗ này 1000 cột với 1000 dòng bác có dùng 2 hàm MINSERVE(Trả vê ma trận nghịch đảo) và hàm MMULT(nhân 2 ma trận) được không. Còn em đã test báo lỗi dư đối số với #Value.

 

[Ngô Hải Đăng]

Bạn thử code này xem, mình làm dựa theo file excel của bạn. Trước cũng có học môn này mà quên sạch rồi

Function PTHoiQuy(iTableX As Range, iColumnY As Range) As String
  Dim x&, y&, aColumnX, aColumnY, a1X, aT1X, aI_T1X_1X, aCoefficients
 
  ReDim aColumnX(1 To iTableX.Columns.Count)
  For x = 1 To iTableX.Columns.Count
    aColumnX(x) = CheckArray(iTableX.Resize(, 1).Offset(0, x - 1).Value)
  Next x
  aColumnY = CheckArray(iColumnY.Value)
  ReDim a1X(1 To UBound(aColumnY), 0 To UBound(aColumnX))
  For y = 1 To UBound(aColumnY)
    a1X(y, 0) = 1
  Next y
  For y = 1 To UBound(aColumnY)
    For x = 1 To UBound(aColumnX)
      a1X(y, x) = aColumnX(x)(y, 1)
    Next x
  Next y
 
  With WorksheetFunction
    aT1X = .Transpose(a1X)
    aI_T1X_1X = .MInverse(.MMult(aT1X, a1X))
    aCoefficients = .MMult(aI_T1X_1X, .MMult(aT1X, aColumnY))
  End With
 
  PTHoiQuy = "Y ="
  For x = 2 To UBound(aCoefficients)
    PTHoiQuy = PTHoiQuy & IIf(aCoefficients(x, 1) >= 0, " + ", " - ") & Abs(aCoefficients(x, 1)) & "X" & x - 1
  Next x
  PTHoiQuy = PTHoiQuy & IIf(aCoefficients(1, 1) >= 0, " + ", " - ") & Abs(aCoefficients(1, 1))
  PTHoiQuy = Replace(PTHoiQuy, "Y = +", "Y =")
End Function

Private Function CheckArray(iArray)
  If TypeName(iArray) = "Range" Then iArray = iArray
  Dim x&, y&
  For x = LBound(iArray) To UBound(iArray)
    For y = LBound(iArray, 2) To UBound(iArray, 2)
      iArray(x, y) = Val(iArray(x, y))
    Next y
  Next x
  CheckArray = iArray
End Function

 

[Thư Sinh Áo Trắng]

Bạn thử code này xem, mình làm dựa theo file excel của bạn. Trước cũng có học môn này mà quên sạch rồi

Function PTHoiQuy(iTableX As Range, iColumnY As Range) As String
  Dim x&, y&, aColumnX, aColumnY, a1X, aT1X, aI_T1X_1X, aCoefficients
 
  ReDim aColumnX(1 To iTableX.Columns.Count)
  For x = 1 To iTableX.Columns.Count
    aColumnX(x) = CheckArray(iTableX.Resize(, 1).Offset(0, x - 1).Value)
  Next x
  aColumnY = CheckArray(iColumnY.Value)
  ReDim a1X(1 To UBound(aColumnY), 0 To UBound(aColumnX))
  For y = 1 To UBound(aColumnY)
    a1X(y, 0) = 1
  Next y
  For y = 1 To UBound(aColumnY)
    For x = 1 To UBound(aColumnX)
      a1X(y, x) = aColumnX(x)(y, 1)
    Next x
  Next y
 
  With WorksheetFunction
    aT1X = .Transpose(a1X)
    aI_T1X_1X = .MInverse(.MMult(aT1X, a1X))
    aCoefficients = .MMult(aI_T1X_1X, .MMult(aT1X, aColumnY))
  End With
 
  PTHoiQuy = "Y ="
  For x = 2 To UBound(aCoefficients)
    PTHoiQuy = PTHoiQuy & IIf(aCoefficients(x, 1) >= 0, " + ", " - ") & Abs(aCoefficients(x, 1)) & "X" & x - 1
  Next x
  PTHoiQuy = PTHoiQuy & IIf(aCoefficients(1, 1) >= 0, " + ", " - ") & Abs(aCoefficients(1, 1))
  PTHoiQuy = Replace(PTHoiQuy, "Y = +", "Y =")
End Function

Private Function CheckArray(iArray)
  If TypeName(iArray) = "Range" Then iArray = iArray
  Dim x&, y&
  For x = LBound(iArray) To UBound(iArray)
    For y = LBound(iArray, 2) To UBound(iArray, 2)
      iArray(x, y) = Val(iArray(x, y))
    Next y
  Next x
  CheckArray = iArray
End Function

Em cảm ơn anh @Ngô Hải Đăng nhé!
Bác có nhớ thì xác nhận cho bài 54 giúp em với!

Chắc do có ô nào đó ko có dữ liệu. Mình chạy thử 1000 cột 1000 dòng vẫn ok với excel 2013.

Chỗ này em dùng 3 hàm lồng vào nhau MINVERSE(MMULT(TRANSPOSE(vùng;vùng)) thì báo lỗi, như trên thì không phải do Transpose rồi. Vậy lỗi do giới hạn phần tử của 2 hàm còn lại. Không biết 2 hàm MINVERSE(MMUL....))) bác có bị lỗi với bảng 1000 cột và 1000 dòng không?