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

Thư Sinh Áo Trắng · 5/5/21

- Đ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!

soledad_90 · 7/5/21

Thư Sinh Áo Trắng đã viết:
Bạn thành viên kỳ cựu VBA mình phải hỏi bạn ấy chứ! Bạn sửa được mà

Quá ngại đi mất.
VBA là đam mê của em. Và em cũng chỉ là cóp nhặt kiến thức của các anh trên diễn đàn ạ.

Thư Sinh Áo Trắng · 8/5/21

Kích thước tối đa của một ma trận tao ra trong excel là bao nhiêu theo chỉ số dòng và cột mà phần mềm excel có thể tính toán được. Em cảm ơn!

soledad_90 · 8/5/21

Thư Sinh Áo Trắng đã viết:
Kích thước tối đa của một ma trận tao ra trong excel là bao nhiêu theo chỉ số dòng và cột mà phần mềm excel có thể tính toán được. Em cảm ơn!

Đơn toàn tập từ lúc đọc được tin nhắn

Thư Sinh Áo Trắng · 8/5/21

soledad_90 đã viết:
Đơn toàn tập từ lúc đọc được tin nhắn

Sao thế @soledad_90

soledad_90 · 8/5/21

Thư Sinh Áo Trắng đã viết:
Sao thế @soledad_90

không đủ kiến thức để hiểu ý bạn nói á .

Thư Sinh Áo Trắng · 8/5/21

soledad_90 đã viết:
không đủ kiến thức để hiểu ý bạn nói á .

Bạn đọc cái này xem sao.
Biết giải bằng tay thì hiểu. Muốn Automatic thì dùng VBA.
Muốn có code VBA thể hiện những gì mình viết ra bằng tay thì như mình đây này. Mình chụp ảnh, đính file, mô tả ý muốn rồi mình "BẨM" nên đây.
=> Nắm lý thuyết + BẨM phải chuẩn + may mắn thì sẽ được code(học được khơ khớ luôn ấy)

CHAOQUAY · 8/5/21

Thư Sinh Áo Trắng đã viết:
Kích thước tối đa của một ma trận tao ra trong excel là bao nhiêu theo chỉ số dòng và cột mà phần mềm excel có thể tính toán được. Em cảm ơn!

Hình như là kích thước này tùy thuộc vào công cụ excel sử dụng thì phải.

Thư Sinh Áo Trắng · 8/5/21

CHAOQUAY đã viết:
Hình như là kích thước này tùy thuộc vào công cụ excel sử dụng thì phải.

Vâng.
Em dùng Core i5 và Office2013 thì thấy nên tối đa dung lượng file VBA là 200 Mê, thực ra file 15 Mê là thấy khó chịu rồi.
Mỗi 1 Sub gán kết quả xuống Sheet tầm 2 Triệu Cells một lần thì chạy không đau tim.( nặng hơn là Not ResPonding, hiện tượng treo gây bức xúc)
Đây là em nói máy của em, còn bác nào khỏe hơn thì đỡ hơn rồi!
Khả năng ma trận tính toán được bằng đúng kích thước của một Sheet, nhưng cứ thấy Not ResPonding thì phải xem lại kích thước đầu vào vì tùy thuộc cấu hình máy phải không ạ!
Cảm ơn bác nhé!

Maika8008 · 8/5/21

Thư Sinh Áo Trắng đã viết:
Vâng.
Em dùng Core i5 và Office2013 thì thấy nên tối đa dung lượng file VBA là 200 Mê, thực ra file 15 Mê là thấy khó chịu rồi.
Mỗi 1 Sub gán kết quả xuống Sheet tầm 2 Triệu Cells một lần thì chạy không đau tim.( nặng hơn là Not ResPonding, hiện tượng treo gây bức xúc)
Đây là em nói máy của em, còn bác nào khỏe hơn thì đỡ hơn rồi!
Khả năng ma trận tính toán được bằng đúng kích thước của một Sheet, nhưng cứ thấy Not ResPonding thì phải xem lại kích thước đầu vào vì tùy thuộc cấu hình máy phải không ạ!
Cảm ơn bác nhé!

Máy tôi cùi bắp i3 4GB ram chắc khỏi chạy luôn quá.

Thư Sinh Áo Trắng · 8/5/21

Maika8008 đã viết:
Máy tôi cùi bắp i3 4GB ram chắc khỏi chạy luôn quá.

Nhưng em máy cũ!Nhưng nếu chậm bác đã xử dụng đến code này chưa?
Ỏ đây ạ của ông ban Jacob Hilderbrand, được tìm thấy bới tiên sinh @levanduyet . Em chi sợ nó xơi sạch dữ liệu của em và lệch vùng tham chiếu của code thì toang! Bác và các bác xem xem em với!
Không biết công dụng nó là chi

PHP:

Option Explicit
Sub ExcelDiet()
    Dim j               As Long
    Dim k               As Long
    Dim LastRow         As Long
    Dim LastCol         As Long
    Dim ColFormula      As Range
    Dim RowFormula      As Range
    Dim ColValue        As Range
    Dim RowValue        As Range
    Dim Shp             As Shape
    Dim ws              As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     On Error Resume Next
    For Each ws In Worksheets
        With ws
             ' Tìm ô sử dụng cuối cùng với công thức và giá trị
             ' Tìm theo cột và hàng
            On Error Resume Next
            Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            On Error Goto 0
             ' Xác định cột cuối cùng
            If ColFormula Is Nothing Then
                LastCol = 0
            Else
                LastCol = ColFormula.Column
            End If
            If Not ColValue Is Nothing Then
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column)
            End If
             ' Xác định hàng cuối
            If RowFormula Is Nothing Then
                LastRow = 0
            Else
                LastRow = RowFormula.Row
            End If
            If Not RowValue Is Nothing Then
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row)
            End If
           
             ' Xác định xem có shapes nào nằm ngoài hàng cuối và cột cuối
            For Each Shp In .Shapes
                j = 0
                k = 0
                On Error Resume Next
                j = Shp.TopLeftCell.Row
                k = Shp.TopLeftCell.Column
                On Error Goto 0
                If j > 0 And k > 0 Then
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
                        j = j + 1
                    Loop
                    If j > LastRow Then
                        LastRow = j
                    End If
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
                        k = k + 1
                    Loop
                    If k > LastCol Then
                        LastCol = k
                    End If
                End If
            Next
            .Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete
            .Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete
        End With
    Next
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Maika8008 · 8/5/21

Thư Sinh Áo Trắng đã viết:

Nhưng em máy cũ!Nhưng nếu chậm bác đã xử dụng đến code này chưa?
Ỏ đây ạ của ông ban Jacob Hilderbrand, được tìm thấy bới tiên sinh @levanduyet . Em chi sợ nó xơi sạch dữ liệu của em và lệch vùng tham chiếu của code thì toang! Bác và các bác xem xem em với!
Không biết công dụng nó là chi

PHP:

Option Explicit
Sub ExcelDiet()
    Dim j               As Long
    Dim k               As Long
    Dim LastRow         As Long
    Dim LastCol         As Long
    Dim ColFormula      As Range
    Dim RowFormula      As Range
    Dim ColValue        As Range
    Dim RowValue        As Range
    Dim Shp             As Shape
    Dim ws              As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     On Error Resume Next
    For Each ws In Worksheets
        With ws
             ' Tìm ô sử dụng cuối cùng với công thức và giá trị
             ' Tìm theo cột và hàng
            On Error Resume Next
            Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            On Error Goto 0
             ' Xác định cột cuối cùng
            If ColFormula Is Nothing Then
                LastCol = 0
            Else
                LastCol = ColFormula.Column
            End If
            If Not ColValue Is Nothing Then
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column)
            End If
             ' Xác định hàng cuối
            If RowFormula Is Nothing Then
                LastRow = 0
            Else
                LastRow = RowFormula.Row
            End If
            If Not RowValue Is Nothing Then
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row)
            End If
         
             ' Xác định xem có shapes nào nằm ngoài hàng cuối và cột cuối
            For Each Shp In .Shapes
                j = 0
                k = 0
                On Error Resume Next
                j = Shp.TopLeftCell.Row
                k = Shp.TopLeftCell.Column
                On Error Goto 0
                If j > 0 And k > 0 Then
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
                        j = j + 1
                    Loop
                    If j > LastRow Then
                        LastRow = j
                    End If
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
                        k = k + 1
                    Loop
                    If k > LastCol Then
                        LastCol = k
                    End If
                End If
            Next
            .Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete
            .Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete
        End With
    Next
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Nó xóa hết các dòng, cột thừa để làm sạch các trang tính của bảng tính.

Máy tôi mua 2017, cũ mèm rồi.

Thư Sinh Áo Trắng · 8/5/21

Maika8008 đã viết:
Nó xóa hết các dòng, cột thừa để làm sạch các trang tính của bảng tính.

Máy tôi mua 2017, cũ mèm rồi.

Tý em test xem hai bảng cách nhau 1 cột thì cột để cách nhau 2 bảng có bị xóa không?
Máy em còn cũ hơn.

Maika8008 · 8/5/21

Thư Sinh Áo Trắng đã viết:
Tý em test xem hai bảng cách nhau 1 cột thì cột để cách nhau 2 bảng có bị xóa không?
Máy em còn cũ hơn.

Nhưng code viết cho Excel 2003, bạn phải sửa IV65536 thành XFD1048576 thì mới được

Thư Sinh Áo Trắng · 8/5/21

Maika8008 đã viết:
Nhưng code viết cho Excel 2003, bạn phải sửa IV65536 thành XFD1048576 thì mới được

May nếu không đăng nên để hỏi thì lại kết luận là code dỏm. Thank bác! :yahoo:

Thư Sinh Áo Trắng · 11/5/21

soledad_90 đã viết:
Em cảm ơn code này nhé.
Anh có thể chỉnh code để dữ liệu yêu cầu một sheet, và sheet bên là đáp án theo tệp em gửi với ạ.
Em cảm ơn !

@soledad_90 ơi mình đính chính lại nhé! Chạy hồi quy tuyến tính trên Excel nó chỉ cho tối đa 16 biến X thôi nhé! Nhiều hơn tìm phần mềm khác hoặc giải bằng tay (vẫn dùng excel để tính bằng tay vì không dùng được Add-in khi có hơn 16 biến

Pha này vui đây. Bước ngoặt vào hố -+*/

soledad_90 · 11/5/21

Thư Sinh Áo Trắng đã viết:
@soledad_90 ơi mình đính chính lại nhé! Chạy hồi quy tuyến tính trên Excel nó chỉ cho tối đa 16 biến X thôi nhé! Nhiều hơn tìm phần mềm khác hoặc giải bằng tay (vẫn dùng excel để tính bằng tay vì không dùng được Add-in khi có hơn 16 biến
View attachment 258411
Pha này vui đây. Bước ngoặt vào hố

Cảm ơn nhiều nhé.
Khi đó các khoản chi phí sẽ phải gom chung nhóm rồi.
Mình Xin code cho phiên bản biến nhiều nhất này nhé

Thư Sinh Áo Trắng · 13/5/21

- 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!

HieuCD · 13/5/21

Thư Sinh Áo Trắng đã viết:
- 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!

Tìm trên diễn đàn cách giải hệ phương trình bậc 1

Thư Sinh Áo Trắng · 13/5/21

HieuCD đã viết:
Tìm trên diễn đàn cách giải hệ phương trình bậc 1

Không thấy anh @HieuCD ơi!
Có lẽ ít người gặp phải vấn đề về giới hạn phần tử của các hàm:
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ử?
Nên không có vết trên google anh ạ!
Test sao để biết giới hạn hoặc tài liệu nào có nói về nó anh nhỉ?
Cảm ơn anh!

HieuCD · 13/5/21

Thư Sinh Áo Trắng đã viết:
Không thấy anh @HieuCD ơi!
Có lẽ ít người gặp phải vấn đề về giới hạn phần tử của các hàm:
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ử?
Nên không có vết trên google anh ạ!
Test sao để biết giới hạn hoặc tài liệu nào có nói về nó anh nhỉ?
Cảm ơn anh!

Bạn tự test sẽ biết giới hạn bao nhiêu phần tử, giải hệ phương trình bạn tiếp tục tìm trên diễn đàn nha

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

Thành viên hoạt động

Thành viên thường trực

Thành viên hoạt động

Thành viên thường trực

Thành viên hoạt động

Thành viên thường trực

Thành viên hoạt động

Thành viên gắn bó

Thành viên hoạt động

Thành viên gạo cội

Thành viên hoạt động

Thành viên gạo cội

Thành viên hoạt động

Thành viên gạo cội

Thành viên hoạt động

Thành viên hoạt động

Thành viên thường trực

Thành viên hoạt động

Chuyên gia GPE

Thành viên hoạt động

Chuyên gia GPE