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!

 

[Ngô Hải Đăng]

Bạn viết hàm TRANSPOSE nhận 2 tham số nên bị báo lỗi là đúng rồi. Còn hàm MINVERSE Vẫn chạy được 1000 cột x 1000 dòng nhưng chậm.

 

[Thư Sinh Áo Trắng]

Bạn viết hàm TRANSPOSE nhận 2 tham số nên bị báo lỗi là đúng rồi. Còn hàm MINVERSE Vẫn chạy được 1000 cột x 1000 dòng nhưng chậm.

- MINVERSE(MMULT(TRANSPOSE(vùng);(vùng))) => chỗ này là lồng 3 hàm vào để tính luôn ra ma trận nghịch đảo của ma trận tích X' nhân X anh @Ngô Hải Đăng ơi.
- Em dùng Function PTHoiQuy chưa đúng cách hay sao mà báo lỗi, anh hướng dẫn cách dùng em với!
- Hàm MMUL nhân 2 ma trận em tách ra vẫn lỗi với số cột lớn hơn 52. Giúp em xác nhận hàm này và cách dùng Function PTHoiQuy với ạ!

 

[Ngô Hải Đăng]

- MINVERSE(MMULT(TRANSPOSE(vùng);(vùng))) => chỗ này là lồng 3 hàm vào để tính luôn ra ma trận nghịch đảo của ma trận tích X' nhân X anh @Ngô Hải Đăng ơi.
- Em dùng Function PTHoiQuy chưa đúng cách hay sao mà báo lỗi, anh hướng dẫn cách dùng em với!
- Hàm MMUL nhân 2 ma trận em tách ra vẫn lỗi với số cột lớn hơn 52. Giúp em xác nhận hàm này và cách dùng Function PTHoiQuy với ạ!
View attachment 258903

Vùng X có bao gồm biến chặn không, nếu có thì bạn bỏ biến chặn ra.

 

[Thư Sinh Áo Trắng]

Vùng X có bao gồm biến chặn không, nếu có thì bạn bỏ biến chặn ra.

- Ra phương trình rồi anh @Ngô Hải Đăng
- Kỳ lạ ghê. Chỗ này nên hiểu thế nào các bác nhỉ?
+ MINVERSE(MMULT(TRANSPOSE(vùng);(vùng))) Nếu vùng có dưới 52 cột thì ra được một ma trận B.Còn trên 52 cột thì không ra được ma trận B
+ Nhưng cứ cho là dưới 52 cột đi MINVERSE(MMULT(TRANSPOSE(vùng);(vùng))) của Excel lại không ra kết quả như code của bác Đăng. Em có xem code thì đúng là tương đương nhau về cách tìm ra ma trận B. Đến đây rất oái oăm ở chỗ AnalysisToolPak = KQ em tính bằng các hàm có sẵn trong Excel, còn của bác Đăng thì tìm ra ma trận B đúng, nhưng lại khác kết quả của em và AnalysisToolPak.
+ Dùng hàm trong Application (VBA) thì lại ra được ma trận B (Bằng chứng là có ra phương trình hồi quy, nghĩa là có ước lượng các hệ số của phương trình)
Không hiểu tại sao lại thế nhỉ? Tại sao hàm trong Excel và trong Application(VBA) lại khác nhau vậy các bác?
- Với Function PTHoiquy của anh @Ngô Hải Đăng thì có khác với PTHQ của AnalysisToolPak, đây ạ:

- Bài này thực sự liên quan đến toán và VBA rất mong nhận được chia sử của các bác cả chuyên môn và VBA. Và trở lên khó vì sự giới hạn phần tử của các hàm trong Excel
- Xin anh @Ngô Hải Đăng dành chút quan tâm tới chủ đề này. Hay chăng không có cột biến phụ thuộc nó khác kết quả nhau à bác?
Em chân thành cảm ơn các anh!

 

[Ngô Hải Đăng]

- Ra phương trình rồi anh @Ngô Hải Đăng
- Kỳ lạ ghê. Chỗ này nên hiểu thế nào các bác nhỉ?
+ MINVERSE(MMULT(TRANSPOSE(vùng);(vùng))) Nếu vùng có dưới 52 cột thì ra được một ma trận B, lấy B*Y( Y là cột biến phụ thuộc) thì ra được các hệ số của phương trình hồi quy. Còn trên 52 cột thì không ra được ma trận B
+ Dùng hàm trong Application (VBA) thì lại ra được ma trận B (Bằng chứng là có ra phương trình hồi quy, nghĩ là có ước lượng các hệ số của phương trình)
Không hiểu tại sao lại thế nhỉ? Tại sao hàm trong Excel và trong Application(VBA) lại khác nhau vậy các bác?
- Với Function PTHoiquy của anh @Ngô Hải Đăng thì có khác với PTHQ của AnalysisToolPak, đây ạ:
View attachment 258909
- Bài này thực sự liên quan đến toán và VBA rất mong nhận được chia sử của các bác cả chuyên môn và VBA. Và trở lên khó vì sự giới hạn phần tử của các hàm trong Excel
- Xin anh @Ngô Hải Đăng dành chút quan tâm tới chủ đề này.
Em chân thành cảm ơn các anh!

Để lát mình kiểm tra lại, lúc chiều copy vội quá. Function này chỉ mới copy phần hệ số phương trình hồi quy. Còn các kết quả khác nữa. Bạn làm luôn phần kiểm định đi. Mình sẽ dựa theo file của bạn để viết code.

 

[Ngô Hải Đăng]

Mình mở file của bạn lên thì lại thấy kết quả đúng.

 

[Thư Sinh Áo Trắng]

Mình mở file của bạn lên thì lại thấy kết quả đúng.
View attachment 258917

Em xin lỗi nhé bác @Ngô Hải Đăng, cứ nhầm Ngô thành Nguyễn.
- Code của bác em thấy tương đương chắc cột biến chặn hay em mỗi lần chỉnh code lại chưa enter lại Func chăng.
- Em không chuyên môn về thống kê, hay kinh tế mà hoàn toàn học mót các ví dụ mà lắp ghép các hàm vào dịch ngược bảng mô tả của Analysis ToolPak
- Chỗ chọn độ tin cậy 95% em, cũng mới mò đến chỗ đó, chưa có nhập tâm để hiểu đoạn này. Mà em không phải dân chuyên ngành thống kê và kinh tế nên khá là khó khăn, file bác viết chắc hẳn bác bên chuyên môn thống kê, kinh tế rồi, không thì cũng thầy dạy đại học. Được bác giúp phần này mới trọn vẹn.
- Kiểm định thì nó khá là rộng, nào là bác bỏ Ho, rồi phát hiện đa cộng tuyến, rồi liệu nó có phải là tuyến tính hay là phi tuyến, nếu không phải là phi tuyến thì đặt ẩn để đưa về tuyến tính. Khá là dài và toàn toán là toán. Khi lập trình chỗ kiểm định này mới là trọng tâm của code, rất phức tạp. Em gọi đó là vòng For khung khiếp(chạy đị chạy lại phân tích rất nhiều) để ra được Y. Nhưng nếu có anh và các bác trợ giúp chắc sẽ dịch ngược đc các hàm. Rồi tứ đó viết code VBA là khả thi.
Thật sự em rất khoái làm tuốt tuồn tuột trên Excel(VBA) dăm bữa 1 tuần 1 tháng mở file ra kích 1 cái là có kết quả phân tích. Mà ngành khoa học nao hay bộ phận tinh hoa, dân đen như em cùng đều cần kết quả phân tích thống kê. Do ứng dụng lớn của chủ đề xin được đông đảo các bác quan tâm.
Em chân thành cảm ơn!

 

[Ngô Hải Đăng]

Do system của bạn dùng dấu , cho số thập phân nên hàm Val trong function CheckArray không chính xác. Bạn chỉnh

iArray(x, y) = Val(iArray(x, y))

thành

iArray(x, y) = CDbl(iArray(x, y))

là được.

 

[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ự.

- Test kỹ rồi anh @Ngô Hải Đăng ơi! Khi không dùng Function CheckArray này sảy ra lỗi khi gọi hàm từ lớp Application. Vì dữ liệu có thể có cột nào đó không định dạng là Double (hàm CDbl) do dữ liệu em đang xài là nhặt nhạnh mà có lên chưa kiểm soát hết được đầu vào.
- Vậy bài 53 thì phát biểu lại là, nếu dùng hàm có sẵn trong Excel thì có hạn chế các phần tử. Còn hàm gọi từ lớp Application (VBA) thì tầm 1000 cột cũng chạy nhanh đanh cái roẹt. =>Code của bác ngon, không có bác chỉ cho thì phát rồ mất. Đỡ đi dặt dẹo không biết đâu mà lần!
- Cảm ơn bác nhiều. Có vấn đề mới về kiểm định mong nhận được chia sẻ từ bác nhé! Em cảm ơn!

 

[Thư Sinh Áo Trắng]

Xin giúp đỡ chuyển ma trận hệ số tương quan giữ các biến độc lập đã giải bằng các hàm Excel có sẵn thành code VBA

Em cố gắng chuyển ma trận này đã tính bằng các Excel sang code VBA nhưng vẫn chưa chạy ra được. Mong nhận được sự đỡ của các anh chuyển cho em thành code VBA (cụ thể là Sub) với ạ!
Em xin cảm ơn!

 

[Ngô Hải Đăng]

Xin giúp đỡ chuyển ma trận hệ số tương quan giữ các biến độc lập đã giải bằng các hàm Excel có sẵn thành code VBA
View attachment 259125
Em cố gắng chuyển ma trận này đã tính bằng các Excel sang code VBA nhưng vẫn chưa chạy ra được. Mong nhận được sự đỡ của các anh chuyển cho em thành code VBA (cụ thể là Sub) với ạ!
Em xin cảm ơn!

Xài Function thì gõ trực tiếp kết quả lên Sheet luôn.

Function CorrelMatrix(iTableX As Range)
  Dim x&, y&, aCorrel, aColumnX
  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
  ReDim aCorrel(1 To UBound(aColumnX), 1 To UBound(aColumnX))
  With WorksheetFunction
    aCorrel(1, 1) = 1
    For x = 1 To UBound(aColumnX) - 1
      aCorrel(x + 1, x + 1) = 1
      For y = x + 1 To UBound(aColumnX)
        aCorrel(x, y) = .Correl(aColumnX(x), aColumnX(y))
        aCorrel(y, x) = aCorrel(x, y)
      Next y
    Next x
  End With
  CorrelMatrix = aCorrel
End Function

 

[Thư Sinh Áo Trắng]

Xài Function thì gõ trực tiếp kết quả lên Sheet luôn.

Function CorrelMatrix(iTableX As Range)
  Dim x&, y&, aCorrel, aColumnX
  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
  ReDim aCorrel(1 To UBound(aColumnX), 1 To UBound(aColumnX))
  With WorksheetFunction
    aCorrel(1, 1) = 1
    For x = 1 To UBound(aColumnX) - 1
      aCorrel(x + 1, x + 1) = 1
      For y = x + 1 To UBound(aColumnX)
        aCorrel(x, y) = .Correl(aColumnX(x), aColumnX(y))
        aCorrel(y, x) = aCorrel(x, y)
      Next y
    Next x
  End With
  CorrelMatrix = aCorrel
End Function

Em cảm ơn bác nhé. Kỳ cạch mấy ngày bằng key tiếng Anh mà chưa giải quyết được. Mảng đề tài này ít bài quá. Em cảm ơn bâc @Ngô Hải Đăng

 

[Thư Sinh Áo Trắng]

Xài Function thì gõ trực tiếp kết quả lên Sheet luôn.

Dựa vào Function CorrelMatrix chế được cái Sub cho thêm chọn lựa.

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

Sub Correl_Matrix_made_from_Func_by_NgoHaiDang()
Dim x&, y&, aCorrel, aColumnX, iTableX As Range
With Sheets("Sheet1")
sRow = .Range("A" & Rows.Count).End(xlUp).Row
sCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Set iTableX = .Range(Cells(2, 1), Cells(sRow, sCol))
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
ReDim aCorrel(1 To UBound(aColumnX), 1 To UBound(aColumnX))
With WorksheetFunction
    aCorrel(1, 1) = 1
    For x = 1 To UBound(aColumnX) - 1
      aCorrel(x + 1, x + 1) = 1 '<-------------- câu này bỏ khi offset chuẩn cột anh ạ
      For y = x + 1 To UBound(aColumnX)
        aCorrel(x, y) = .Correl(aColumnX(x), aColumnX(y))
        aCorrel(y, x) = aCorrel(x, y) '<-------------- câu này bỏ khi offset chuẩn cột anh ạ
      Next y
    Next x
End With
.Range("E20").Resize(iTableX.Columns.Count, iTableX.Columns.Count).Value = aCorrel
End With
End Sub

- Hiện tại kết quả cho ra từ Function CorrelMatrix vẫn có giá trị chưa trùng nhau với cách giải bằng tay(hàm excel) hoặc bằng AnalysisToolPak .Nhờ anh @Ngô Hải Đăng kiểm tra giúp em với. Chỗ này em nghi ngờ do chưa đúng thứ tự tìm tương quan giữa các cột biến X.
- Em mô tả lại quá trình Application.Correl để anh tiện sửa code: Ví dụ cho 3 cột biến X1, X2, X3 thì khi in kết quả ra Sheet thì có ma trận vuông 3*3 theo hình bên dưới.Giúp em với nhé! Viết ra code em không đủ khả năng, em có đánh dấu 2 dòng ở trên code bài 73 này, anh xem giúp em nhé!.Cảm ơn anh!
Chú ý:X1, X2, X3 là 3 cột giá trị có số dòng bằng nhau

 

[Ngô Hải Đăng]

Dựa vào Function CorrelMatrix chế được cái Sub cho thêm chọn lựa.

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

Sub Correl_Matrix_made_from_Func_by_NgoHaiDang()
Dim x&, y&, aCorrel, aColumnX, iTableX As Range
With Sheets("Sheet1")
sRow = .Range("A" & Rows.Count).End(xlUp).Row
sCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Set iTableX = .Range(Cells(2, 1), Cells(sRow, sCol))
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
ReDim aCorrel(1 To UBound(aColumnX), 1 To UBound(aColumnX))
With WorksheetFunction
    aCorrel(1, 1) = 1
    For x = 1 To UBound(aColumnX) - 1
      aCorrel(x + 1, x + 1) = 1 '<-------------- câu này bỏ khi offset chuẩn cột anh ạ
      For y = x + 1 To UBound(aColumnX)
        aCorrel(x, y) = .Correl(aColumnX(x), aColumnX(y))
        aCorrel(y, x) = aCorrel(x, y) '<-------------- câu này bỏ khi offset chuẩn cột anh ạ
      Next y
    Next x
End With
.Range("E20").Resize(iTableX.Columns.Count, iTableX.Columns.Count).Value = aCorrel
End With
End Sub

View attachment 259157
- Hiện tại kết quả cho ra từ Function CorrelMatrix vẫn có giá trị chưa trùng nhau với cách giải bằng tay(hàm excel) hoặc bằng AnalysisToolPak .Nhờ anh @Ngô Hải Đăng kiểm tra giúp em với. Chỗ này em nghi ngờ do chưa đúng thứ tự tìm tương quan giữa các cột biến X.
- Em mô tả lại quá trình Application.Correl để anh tiện sửa code: Ví dụ cho 3 cột biến X1, X2, X3 thì khi in kết quả ra Sheet thì có ma trận vuông 3*3 theo hình bên dưới.Giúp em với nhé! Viết ra code em không đủ khả năng, em có đánh dấu 2 dòng ở trên code bài 73 này, anh xem giúp em nhé!.Cảm ơn anh!
Chú ý:X1, X2, X3 là 3 cột giá trị có số dòng bằng nhau
View attachment 259166

Sửa lại hàm CheckArray như bài #68 rồi chạy lại thử xem.

 

[Thư Sinh Áo Trắng]

Sửa lại hàm CheckArray như bài #68 rồi chạy lại thử xem.

Ôi thần linh ơi! Cứ đúng mà tưởng sai phát rồ mất.
Đúng bác ạ! Đính chính bài 71,bài 73 phải áp dụng thêm hàm bài 68.
Đỡ em bao nhiêu công mấy nay mỗi ngày một bao thuốc lá không gặp được bác chắc cụ đi mất!

Code của bác cho ma trận hệ số tương quan này đúng là mỗi GPE mới có, có tìm ở google (các kiểu tìm từ khóa) mà không có code anh Tây nào viết ra mà em ưng cả. Tây toàn dùng AnalysisToolPak. Nếu có thì bây giờ mới có người chia sẻ trên Internet theo dạng hoàn chỉnh cả Sub/Function, vòng lặp thêm offset khó thật. Tặng bác ngàn bông hoa!

Em xin anh nhé! Em chân thành cảm ơn!

 

[soledad_90]

Ôi thần linh ơi! Cứ đúng mà tưởng sai phát rồ mất.
Đúng bác ạ! Đính chính bài 71,bài 73 phải áp dụng thêm hàm bài 68.
Đỡ em bao nhiêu công mấy nay mỗi ngày một bao thuốc lá không gặp được bác chắc cụ đi mất!

Code của bác cho ma trận hệ số tương quan này đúng là mỗi GPE mới có, có tìm ở google (các kiểu tìm từ khóa) mà không có code anh Tây nào viết ra mà em ưng cả. Tây toàn dùng AnalysisToolPak. Nếu có thì bây giờ mới có người chia sẻ trên Internet theo dạng hoàn chỉnh cả Sub/Function, vòng lặp thêm offset khó thật. Tặng bác ngàn bông hoa!

Em xin anh nhé! Em chân thành cảm ơn!

@Thư Sinh Áo Trắng . Topic này đâu đó khá tương đồng với Mô hình EOQ , thuật toán Solver
Theo hình hiểu nó đều là nhiều biến x, các biến sẽ làm ảnh hưởng tới y - tới lợi nhuận, tới cái tối ưu
Về EOQ mình có tham khảo được Video của Phạm Đức Huy
Và có làm lại ra cái file đính kèm này.
Bạn có nhận định và tư vấn đóng góp cùng mình cho tệp này nhé.
Cảm ơn bạn nhiều!
P/s : Mạo phạm topic đăng vấn đề riêng

 

[Ngô Hải Đăng]

Test thử hàm tự tạo REGRESSION, trên sheet gõ =REGRESSION(iTableX As Range, iColumnY As Range).
iTableX và iColumnY là dữ liệu cần tính toán, số dòng của iTableX và iColumnY phải bằng nhau.
File test đính kèm bên dưới.

 

[Thư Sinh Áo Trắng]

P/s : Mạo phạm topic đăng vấn đề riêng

- "Mạo" gì đâu, sẽ rất cô đơn khi có mỗi mình tớ nỉ non ở TOP này. Mong các bạn cùng tham gia để cho cách lập trình chạy hồi quy bằng VBA tìm ra các giá trị mà các phần mềm chuyên dụng có thể hiện qua bảng mô tả hồi quy. Điều này thành công ta chỉ việc mở file và kích đúng một nút là có kết quả phân tích, quá ưu việt và tiện lợi khi làm được điều đó.
- Chủ đề là Hồi quy tuyến tính đa biến hay nói các khác theo đại số (Toán) là giải hệ phương trình tuyến tính. Excel hay các phần khác đều giải chung theo cách tìm các ma trận. Đấy là ban đầu ta coi nó là tuyến tính (phương trình có đường thẳng y = ax+b) và giải xong hệ này mới chiếm % công việc rất nhỏ trong toán thống kê. Nhỡ đâu nó không phải đường thẳng thì sao? Thì nó là phi tuyến (không phải dạng đường thẳng mà là đường cong) thì sao? Thì lại xem xem có loại phi tuyến nào đưa được về tuyến tính cho phù với dữ liệu. Có nhiều dạng phi tuyến đưa về được dạng tuyến tính theo cách đặt ẩn.
- Rất may các hàm trong lớp Application (VBA) đã hỗ trợ đầy đủ cho việc tìm ra các ma trận. Vậy là khỏe re rồi. Nhưng Excel có 1.048.576 hàng với 16.384 cột, ta nhớ giá trị này nhé. Giờ ta chế ra 1 bảng dữ liệu có 1.048.576 hàng với 16.384 cột và gọi bảng này là X và chắc dữ liệu dữ liệu này ta khôn thể Transpose(X) để gán xuống sheet được vì khi Transpose xong phải cần tới Sheet có 1.048.576 Cột cơ, nhưng ta cũng không cần quan tâm việc gán ma trân chuyển vị Transpose(X) xuống Sheet làm gì. Ta test MMULT(Transpose(X),X) ra được ma trận có kích thước 16.348 cột là OK. Việc này mình chưa test. Nhưng nếu MMULT(Transpose(X),X) mà ra được ma trận có kích thước 16.384 x 16.384 thì Excel không sợ phần mềm nào cả. Khi gặp lỗi mình sẽ test sau hoặc bạn test giúp mình với, bạn điền kín dữ liệu trong 1 Sheet là các số, ra qua Sheet bên cạnh xem có gán được xuống Sheets ma trận MMULT(Transpose(X),X) có 16.384 x 16.384 không nhé!
- Mình đang ngâm cứu phần kiểm định.Phần này mới là 90% công việc trong toán thống kê. Và là trong tâm của code hồi quy đa biến trên VBA. Nói chung chuyển được lý thuyết(tìm các hàm tương ứng với công thức theo lý thuyết) thành bài tập thì nỉ non trên GPE và may mắn gặp các bác như @Ngô Hải Đăng @HieuCD @Maika8008 @snow25 @VetMini @ongke0711 @HeSanbi @buiquangthuan @phuocam
(các bác từng cho em code) ... thì khả năng có code.

Test thử hàm tự tạo REGRESSION, trên sheet gõ =REGRESSION(iTableX As Range, iColumnY As Range).
iTableX và iColumnY là dữ liệu cần tính toán, số dòng của iTableX và iColumnY phải bằng nhau.
File test đính kèm bên dưới.

Cảm ơn bác @Ngô Hải Đăng.

 

[HieuCD]

- "Mạo" gì đâu, sẽ rất cô đơn khi có mỗi mình tớ nỉ non ở TOP này. Mong các bạn cùng tham gia để cho cách lập trình chạy hồi quy bằng VBA tìm ra các giá trị mà các phần mềm chuyên dụng có thể hiện qua bảng mô tả hồi quy. Điều này thành công ta chỉ việc mở file và kích đúng một nút là có kết quả phân tích, quá ưu việt và tiện lợi khi làm được điều đó.
- Chủ đề là Hồi quy tuyến tính đa biến hay nói các khác theo đại số (Toán) là giải hệ phương trình tuyến tính. Excel hay các phần khác đều giải chung theo cách tìm các ma trận. Đấy là ban đầu ta coi nó là tuyến tính (phương trình có đường thẳng y = ax+b) và giải xong hệ này mới chiếm % công việc rất nhỏ trong toán thống kê. Nhỡ đâu nó không phải đường thẳng thì sao? Thì nó là phi tuyến (không phải dạng đường thẳng mà là đường cong) thì sao? Thì lại xem xem có loại phi tuyến nào đưa được về tuyến tính cho phù với dữ liệu. Có nhiều dạng phi tuyến đưa về được dạng tuyến tính theo cách đặt ẩn.
- Rất may các hàm trong lớp Application (VBA) đã hỗ trợ đầy đủ cho việc tìm ra các ma trận. Vậy là khỏe re rồi. Nhưng Excel có 1.048.576 hàng với 16.384 cột, ta nhớ giá trị này nhé. Giờ ta chế ra 1 bảng dữ liệu có 1.048.576 hàng với 16.384 cột và gọi bảng này là X và chắc dữ liệu dữ liệu này ta khôn thể Transpose(X) để gán xuống sheet được vì khi Transpose xong phải cần tới Sheet có 1.048.576 Cột cơ, nhưng ta cũng không cần quan tâm việc gán ma trân chuyển vị Transpose(X) xuống Sheet làm gì. Ta test MMULT(Transpose(X),X) ra được ma trận có kích thước 16.348 cột là OK. Việc này mình chưa test. Nhưng nếu MMULT(Transpose(X),X) mà ra được ma trận có kích thước 16.384 x 16.384 thì Excel không sợ phần mềm nào cả. Khi gặp lỗi mình sẽ test sau hoặc bạn test giúp mình với, bạn điền kín dữ liệu trong 1 Sheet là các số, ra qua Sheet bên cạnh xem có gán được xuống Sheets ma trận MMULT(Transpose(X),X) có 16.384 x 16.384 không nhé!
- Mình đang ngâm cứu phần kiểm định.Phần này mới là 90% công việc trong toán thống kê. Và là trong tâm của code hồi quy đa biến trên VBA. Nói chung chuyển được lý thuyết(tìm các hàm tương ứng với công thức theo lý thuyết) thành bài tập thì nỉ non trên GPE và may mắn gặp các bác như @Ngô Hải Đăng @HieuCD @Maika8008 @snow25 @VetMini @ongke0711 @HeSanbi @buiquangthuan @phuocam
(các bác từng cho em code) ... thì khả năng có code.

Cảm ơn bác @Ngô Hải Đăng.

Hồi quy đa biến phi tuyến rất phức tạp, một số dạng rất khó lập hệ phương trình tìm các tham số, công thức kiểm định các tham số cần xem lại, cách tính P Value thông thường đều dựa trên hệ số tương quan, nhưng hồi quy phi tuyến phải dựa trên tỷ số tương quan

 

[Hau151978]

- Rất may các hàm trong lớp Application (VBA) đã hỗ trợ đầy đủ cho việc tìm ra các ma trận. Vậy là khỏe re rồi. Nhưng Excel có 1.048.576 hàng với 16.384 cột, ta nhớ giá trị này nhé. Giờ ta chế ra 1 bảng dữ liệu có 1.048.576 hàng với 16.384 cột và gọi bảng này là X và chắc dữ liệu dữ liệu này ta khôn thể Transpose(X) để gán xuống sheet được vì khi Transpose xong phải cần tới Sheet có 1.048.576 Cột cơ, nhưng ta cũng không cần quan tâm việc gán ma trân chuyển vị Transpose(X) xuống Sheet làm gì. Ta test MMULT(Transpose(X),X) ra được ma trận có kích thước 16.348 cột là OK. Việc này mình chưa test. Nhưng nếu MMULT(Transpose(X),X) mà ra được ma trận có kích thước 16.384 x 16.384 thì Excel không sợ phần mềm nào cả. Khi gặp lỗi mình sẽ test sau hoặc bạn test giúp mình với, bạn điền kín dữ liệu trong 1 Sheet là các số, ra qua Sheet bên cạnh xem có gán được xuống Sheets ma trận MMULT(Transpose(X),X) có 16.384 x 16.384 không nhé!

Excel chắc không giải quyết mảng lớn như vậy được đâu bạn. Mảng số kiểu Long kín cả sheet chiếm 64GB trong VBA, nếu lưu vào file thì phải hơn nhiều lần vì excel lưu dữ liệu dạng xml chứa cả giá trị lẫn định dạng và địa chỉ ô dữ liệu được nén nhưng cũng không nhiều. Do đó cần công cụ khác để tính toán, chia nhỏ mảng ra để nhân rồi lưu kết quả vào file.