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!

 

[VetMini]

Hồi quy tuyến tính đa biến tiếng Anh là Multivariable Regression. Công cụ Analysis Toolpak của Excel có thể giải đơcj phần lớn các vấn đề này. Nhưng cần kiến thức về xác suất thống kê. Nếu không dùng Addin mà viết VBA thì phải khá giỏi toán số ứng dụng, biết làm con toán "best-fit".

Trình độ toán của bạn cỡ năm thứ 2 đại học hay hơn nữa? Tối thiểu phải biết môn Toán Số Giải Tích (Numerical Analysis)
Nhiều người học kinh tế nhưng không đủ căn bản toán, gặp mấy môn định lượng là té ngữa, bơi hồng hộc. Nhất là môn Kinh Tế Kế Lượng (Econometrics)

Thường thường con toán hồi quy đa biến người ta giải trên R hoặc Python. VBA không dủ Library để giải những ma trận lớn.

 

[Thư Sinh Áo Trắng]

- Trước hay phải thi lại giải tích 1,2 (vì trường em mấy môn này thì nổi tiếng rắn) thành ra em khá là nhớ cho đến khi đi làm sau vài năm thì quên tiệt. Nếu cần kiến thức chắc tự học lại được ạ.
- Giả thiết là biết tìm các thông số đầu vào, và dùng VBA để chạy cái Add-in Analysis ToolPak (*1). Không biết có được không các bác nhỉ?
- Và em thấy có một Add-in Analysis ToolPak - VBA (*2) không biết cái này dùng sao, em thì muốn dùng VBA để xây dựng đường hồi quy giống như Add-in Analysis ToolPak mà mọi người thường dùng.
- Không biết Add-in Analysis ToolPak - VBA là gì các bác nhỉ? Mà như bác nói

Nhất là môn Kinh Tế Kế Lượng (Econometrics)
Thường thường con toán hồi quy đa biến người ta giải trên R hoặc Python. VBA không dủ Library để giải những ma trận lớn.

Như bác nói VBA không đủ Library để giải ma trận lớn. Nhưng trường hợp cụ thể bài giải được bằng Add-in Analysis ToolPak, thì bài đó có giải bằng VBA được không ạ!
Em có đọc được các bài của bác, em đoán mò tý bác chắc trong tổ tham mưu cấp bộ trở lên, và liên quan đến toán và kinh tế. Nếu được bác chỉ cho chắc là hỏi đúng chỗ.
- Cô đọng lại là em đang không biết VBA điều khiển được Analysis ToolPak. Và giả thiết đã tìm ra các thông số đầu vào thì VBA có làm giống Analysis ToolPak được không? Mong nhận được hồi âm từ bác!
(*1) và (*2) là hai cái Add-in mà Excel có.

 

[VetMini]

Nếu trường bạn có dạy môn Giải tích cấp cao thì cũng có dạy cách hồi quy đa biến.
Nếu lập trình, như tôi đã nói trên, chỉ R với Python, và Matlab đủ sức làm cái loại này thôi.
Analysis ToolPak của Excel chỉ miễn cưỡng tính cho bạn có đường theo thôi chứ không đủ sức cho bạn biến hoá, tức là thử xê dịch mô-đen.
Tập sử dụng nó trước rồi hỏi tiếp.

 

[Thư Sinh Áo Trắng]

Nếu trường bạn có dạy môn Giải tích cấp cao thì cũng có dạy cách hồi quy đa biến.
Nếu lập trình, như tôi đã nói trên, chỉ R với Python, và Matlab đủ sức làm cái loại này thôi.
Analysis ToolPak của Excel chỉ miễn cưỡng tính cho bạn có đường theo thôi chứ không đủ sức cho bạn biến hoá, tức là thử xê dịch mô-đen.
Tập sử dụng nó trước rồi hỏi tiếp.

- Đúng bác là thành viên tổ quân sư rồi. Bác đúng là bách khoa toàn thư vậy!
- Có học giải tích 1,2 nhưng cũng tùy ngành học, và em tay ngang không phải dân học kinh tế. Do thích nghiên cứu và xây dưng đường hồi quy. Mà khá là liên quan nên em muốn dùng VBA, sau trải nghiệm với nó xong mới thử sang phần mềm chuyên dụng.
- Giờ em muốn dùng VBA điều khiển cái Analysis ToolPak( trong đấy có nhiểu kiểu phân tích) không biết có được không?
Analysis ToolPak - VBA có phải dùng cho code VBA không.
Em có 2 chỗ vướng đó. Đưa lên cầu may có bác nào từng làm thì mách cho chút, có hướng để mày mò.

 

[VetMini]

Bạn chịu khso chjowf ai đó biết cái này đi.
Tôi cũng ngày xưa có làm trong Excel cho mấy bài toán nhỏ. Nhưng khi bài toán lớn rồi thì bắt buộc phải học R để làm.
Biết R rồi không muốn trở lại với Excel làm hồi quy nữa.
Cũng như bạnk biết đánh Bridge rồi sẽ không còn muốn chơi "tiến lên" nữa.

 

[HieuCD]

- Đúng bác là thành viên tổ quân sư rồi. Bác đúng là bách khoa toàn thư vậy!
- Có học giải tích 1,2 nhưng cũng tùy ngành học, và em tay ngang không phải dân học kinh tế. Do thích nghiên cứu và xây dưng đường hồi quy. Mà khá là liên quan nên em muốn dùng VBA, sau trải nghiệm với nó xong mới thử sang phần mềm chuyên dụng.
- Giờ em muốn dùng VBA điều khiển cái Analysis ToolPak( trong đấy có nhiểu kiểu phân tích) không biết có được không?
Analysis ToolPak - VBA có phải dùng cho code VBA không.
Em có 2 chỗ vướng đó. Đưa lên cầu may có bác nào từng làm thì mách cho chút, có hướng để mày mò.

Analysis ToolPak - VBA dùng VBA chạy Analysis ToolPak

 

[Thư Sinh Áo Trắng]

Analysis ToolPak - VBA dùng VBA chạy Analysis ToolPak

Em có seach nhưng không thấy nói phần đó để đọc và cũng chưa thấy có người chia sẻ cách dùng Analysis ToolPak - VBA.
Nếu là như vậy việc xây dựng hồi quy bằng VBA là khả thi cho người yếu code. Bác có link bài em xin với, web Việt chưa có ai chia sẻ, em không chuyên nên không biết mò chỗ nào.
Cảm ơn anh @HieuCD, cảm ơn anh cho code nội suy tuyến tính.

Bài đã được tự động gộp: 5/5/21

Bạn chịu khso chjowf ai đó biết cái này đi.
Tôi cũng ngày xưa có làm trong Excel cho mấy bài toán nhỏ. Nhưng khi bài toán lớn rồi thì bắt buộc phải học R để làm.
Biết R rồi không muốn trở lại với Excel làm hồi quy nữa.
Cũng như bạnk biết đánh Bridge rồi sẽ không còn muốn chơi "tiến lên" nữa.

Vâng. Được đọc những trải nghiểm của bác bằng mấy đọc sách ấy ạ. Cảm ơn bác nhiều.

 

[HieuCD]

Em có seach nhưng không thấy nói phần đó để đọc và cũng chưa thấy có người chia sẻ cách dùng Analysis ToolPak - VBA.
Nếu là như vậy việc xây dựng hồi quy bằng VBA là khả thi cho người yếu code. Bác có link bài em xin với, web Việt chưa có ai chia sẻ, em không chuyên nên không biết mò chỗ nào.
Cảm ơn anh @HieuCD, cảm ơn anh cho code nội suy tuyến tính.

Bài đã được tự động gộp: 5/5/21

Vâng. Được đọc những trải nghiểm của bác bằng mấy đọc sách ấy ạ. Cảm ơn bác nhiều.

Lâu thật là lâu không dùng Analysis ToolPak - VBA nên quên hết, bạn dùng bộ thu macro sẽ được code, sau đó chỉnh các tham số lung tung hy vọng code chạy theo ý mình

 

[Nguyễn Duy Tuân]

Bạn tìm sách môn “Nguyên lý thống kê” có chương dạy hồi quy đa biến (tên gọi tôi k nhớ chính xác). Nếu bạn đã hiểu rồi thì dùng ngôn ngữ VBA giải là ra.

 

[Thư Sinh Áo Trắng]

Bạn tìm sách môn “Nguyên lý thống kê” có chương dạy hồi quy đa biến (tên gọi tôi k nhớ chính xác). Nếu bạn đã hiểu rồi thì dùng ngôn ngữ VBA giải là ra.

- Anh làm một video về xử dụng Analysis ToolPak - VBA cho kênh của anh đi anh ơi!
- Tuy vậy lằng nhằng về lập trình lắm anh ạ! Nhân tùm lum các ma trân và tìm các hệ số hồi quy yếu về code VBA khó xơi lắm anh ơi!

 

[Nguyễn Duy Tuân]

- Anh làm một video về xử dụng Analysis ToolPak - VBA cho kênh của anh đi anh ơi!
- Tuy vậy lằng nhằng về lập trình lắm anh ạ! Nhân tùm lum các ma trân và tìm các hệ số hồi quy yếu về code VBA khó xơi lắm anh ơi!

Lâu rồi mình không làm đến thống kê nên quên khá nhiều, nếu dành thời gian đọc lại thì ok. Nhưng giờ lục lại kiến thức mà chỉ để quay video thì ngại thật . Ngày trước mình từng viết Foxpro để giải hổi quy, cả tuyến tính và phi tuyến tính mà không thấy khó khăn gì với số liệu nho nhỏ và mấy hàm hồi quy trong lý thuyết thống kê. Về ngôn ngữ lập trình thì cơ bản dùng cấu trúc IF .. Then, While là được. Làm trong Excel còn thuận tiện hơn nhiều vì có các hàm hỗ trợ thống kê. Bạn đang làm trong công việc thống kê thì chịu khó lập trình với yêu cầu cụ thể của bạn là được thôi.

 

[Thư Sinh Áo Trắng]

Code ghi marco được có thế này thôi bác @HieuCD ơi

Sub hoiquytuyentinh()
With Sheets("Sheet1")
sRow = .Range("B" & Rows.Count).End(xlUp).Row - 7
sRow1 = .Range("G" & Rows.Count).End(xlUp).Row
.Range("G3:O" & sRow1).ClearContents
Application.Run "ATPVBAEN.XLAM!Regress", .Range("B6:B" & sRow), _
.Range("C6:E" & sRow), False, False, 98, .Range( _
"G3"), False, False, False, False, , False
End With
End Sub

- Đỡ phải nhân ma trận, nhưng còn 3 mục phải viết code vba về toán rồi anh ạ!
- @Nguyễn Duy Tuân Về ngôn ngữ lập trình thì cơ bản dùng cấu trúc IF .. Then, While là được.
Chắc ổn rồi nhỉ. Em cứ sợ phải nhân ma trận bằng VBA(bằng tay) thì căng!

 

[HieuCD]

View attachment 258158
Code ghi marco được có thế này thôi bác @HieuCD ơi

Sub hoiquytuyentinh()
With Sheets("Sheet1")
sRow = .Range("B" & Rows.Count).End(xlUp).Row - 7
sRow1 = .Range("G" & Rows.Count).End(xlUp).Row
.Range("G3:O" & sRow1).ClearContents
Application.Run "ATPVBAEN.XLAM!Regress", .Range("B6:B" & sRow), _
.Range("C6:E" & sRow), False, False, 98, .Range( _
"G3"), False, False, False, False, , False
End With
End Sub

- Đỡ phải nhân ma trận, nhưng còn 3 mục phải viết code vba về toán rồi anh ạ!
- @Nguyễn Duy Tuân Về ngôn ngữ lập trình thì cơ bản dùng cấu trúc IF .. Then, While là được.
Chắc ổn rồi nhỉ. Em cứ sợ phải nhân ma trận bằng VBA(bằng tay) thì căng!

"nhưng còn 3 mục phải viết code vba về toán rồi anh ạ!" 3 mục nào?
Viết code lập đường hồi quy không đơn giản, phải có kiến thức toán học tìm giá trị cực tiểu, nhiều lúc không giải được phải nhờ công cụ Solver

 

[Thư Sinh Áo Trắng]

"nhưng còn 3 mục phải viết code vba về toán rồi anh ạ!" 3 mục nào?

Kiểm định và ước lượng lại, chạy đi chạy lại thêm bớt biến ấy ạ. Chỗ này chắc phức tạp về code.
Code vba chỉ cần điều khiển công cụ sẵn có là được rồi ạ.

 

[HieuCD]

Kiểm định và ước lượng lại, chạy đi chạy lại thêm bớt biến ấy ạ. Chỗ này chắc phức tạp về code.
Code vba chỉ cần điều khiển công cụ sẵn có là được rồi ạ.

Thêm bớt biến nên làm thủ công dể kiểm soát hơn, nhìn vào cột P-value quyết định chọn hay loại biến
Các biến trong file còn thiếu

Lợi nhuận y	Giá thành x1	Chi phí quản lý x2	Chi phí bán hàng x3
(tr đồng)	(nghìn đồng)	(tr đồng)	(tr đồng)

Đường hồi quy dể làm hiểu nhầm: chi phí tăng sẽ làm lợi nhuận tăng
y = 0,2*x1 + 3,3*x2 +1,8*x3 + 315,6

 

[soledad_90]

View attachment 258158
Code ghi marco được có thế này thôi bác @HieuCD ơi

Sub hoiquytuyentinh()
With Sheets("Sheet1")
sRow = .Range("B" & Rows.Count).End(xlUp).Row - 7
sRow1 = .Range("G" & Rows.Count).End(xlUp).Row
.Range("G3:O" & sRow1).ClearContents
Application.Run "ATPVBAEN.XLAM!Regress", .Range("B6:B" & sRow), _
.Range("C6:E" & sRow), False, False, 98, .Range( _
"G3"), False, False, False, False, , False
End With
End Sub

- Đỡ phải nhân ma trận, nhưng còn 3 mục phải viết code vba về toán rồi anh ạ!
- @Nguyễn Duy Tuân Về ngôn ngữ lập trình thì cơ bản dùng cấu trúc IF .. Then, While là được.
Chắc ổn rồi nhỉ. Em cứ sợ phải nhân ma trận bằng VBA(bằng tay) thì căng!

Em có đọc được bài viết của anh và rất muốn mở rộng thêm kiến thức.
Mong nhận được sự chia sẻ từ anh ạ.
Ở đây em thêm nhiều cột và dòng dữ liệu mong muốn ? Vì công ty sản xuất thì chi phí thì rất là nhiều.
Anh có thể mở rộng code khi dữ liệu yêu cầu bổ sung thêm được không ạ ?
Em có gửi tệp đính kèm ạ.
Em tìm hiểu thì phần mềm SPSS sẽ giúp đỡ rất tốt trong việc tính toán hồi quy này, Nhưng trong thời gian ngắn thì chưa thể hiểu để sử dụng được
Em cảm ơn!

 

[Thư Sinh Áo Trắng]

Ở đây em thêm nhiều cột và dòng dữ liệu mong muốn ? Vì công ty sản xuất thì chi phí thì rất là nhiều.
Anh có thể mở rộng code khi dữ liệu yêu cầu bổ sung thêm được không ạ ?

- Mình tay ngang không chuyên, dạng đọc giáo trình thì cũng mày mò sai đâu sửa đấy thôi
- Theo giáo trình thống kê dự báo có nhiều phương pháp trung bình giản đơn, phương pháp trung bình dài hạn, phương pháp san bằng hàm mũ, phương pháp trung bình dài hạn, phương pháp trung bình động, phương pháp hồi quy tương quan...Mô hình thì có nhiều dạng đường, ông thì cong, ông thì thẳng, ông thì ziczac, nhưng đều đưa về được dạng tuyến tính.Mà đang nói ở đây là hồi phương pháp hồi quy tuyến tính chạy trên excel. Do excel không chuyên nên khi chạy bằng Addin của excel thì phải làm bằng tay(VBA tự viết) để kiểm định lại mô hình, nghĩa là chạy đi chạy lại để kiểm tra lại mô hình.
- Mình có nói là mình không chuyên kinh tế và thống kê. Nhưng ban hỏi thêm cột biến cố định X (có X1,X2...,Xn)
Có bao nhiêu biến X cũng đều cho vào mô hình được nhé. Cái khó là phải viết tiếp phần code để kiểm định lại mô hình theo như giáo trình. Như vậy chạy hồi quy trên Excel thì cho ra được phương trình nghĩa là dự báo được biến phụ thuộc Y. Nôm na là Excel giúp cho việc tìm ra các ma trận. Còn để đánh giá Y thì phải 90% công việc về code nữa.
- Giờ bạn có 3 biến X1,X2,X3 rồi còn 2 cột nữa thì nó là X4,X5 chỉ là chọn cái vùng X1...X5 và code thôi phải không?

Thì sửa như này:

Sub hoiquytuyentinh_5bien()
With Sheets("Sheet2")
sRow = .Range("B" & Rows.Count).End(xlUp).Row 'chi so dong cuoi cot B
sRow1 = .Range("I" & Rows.Count).End(xlUp).Row 'chi so dong cuoi cot I
.Range("I4:Q" & sRow1).Clear 'xoa vung co ket qua cu
'Bien Y la cot B
'Bien X1,X2,X3,X4,X5 cot C4 den G&sRow
'.Range("C4:G" & sRow) them bot cot(them bot bien X) sua cho nay
Application.Run "ATPVBAEN.XLAM!Regress", .Range("B4:B" & sRow), _
.Range("C4:G" & sRow), False, False, 98, .Range("I4" _
        ), False, False, False, False, , False
End With
End Sub

Còn kiểm định và viết code cho nó chay đi chạy lại đúng giáo trình mới được!(hic hic)

 

[soledad_90]

- Mình tay ngang không chuyên, dạng đọc giáo trình thì cũng mày mò sai đâu sửa đấy thôi
- Theo giáo trình thống kê dự báo có nhiều phương pháp trung bình giản đơn, phương pháp trung bình dài hạn, phương pháp san bằng hàm mũ, phương pháp trung bình dài hạn, phương pháp trung bình động, phương pháp hồi quy tương quan...Mô hình thì có nhiều dạng đường, ông thì cong, ông thì thẳng, ông thì ziczac, nhưng đều đưa về được dạng tuyến tính.Mà đang nói ở đây là hồi phương pháp hồi quy tuyến tính chạy trên excel. Do excel không chuyên nên khi chạy bằng Addin của excel thì phải làm bằng tay(VBA tự viết) để kiểm định lại mô hình, nghĩa là chạy đi chạy lại để kiểm tra lại mô hình.
- Mình có nói là mình không chuyên kinh tế và thống kê. Nhưng ban hỏi thêm cột biến cố định X (có X1,X2...,Xn)
Có bao nhiêu biến X cũng đều cho vào mô hình được nhé. Cái khó là phải viết tiếp phần code để kiểm định lại mô hình theo như giáo trình. Như vậy chạy hồi quy trên Excel thì cho ra được phương trình nghĩa là dự báo được biến phụ thuộc Y. Nôm na là Excel giúp cho việc tìm ra các ma trận. Còn để đánh giá Y thì phải 90% công việc về code nữa.
- Giờ bạn có 3 biến X1,X2,X3 rồi còn 2 cột nữa thì nó là X4,X5 chỉ là chọn cái vùng X1...X5 và code thôi phải không?
View attachment 258192
Thì sửa như này:

Sub hoiquytuyentinh_5bien()
With Sheets("Sheet2")
sRow = .Range("B" & Rows.Count).End(xlUp).Row 'chi so dong cuoi cot B
sRow1 = .Range("I" & Rows.Count).End(xlUp).Row 'chi so dong cuoi cot I
.Range("I4:Q" & sRow1).Clear 'xoa vung co ket qua cu
'Bien Y la cot B
'Bien X1,X2,X3,X4,X5 cot C4 den G&sRow
'.Range("C4:G" & sRow) them bot cot(them bot bien X) sua cho nay
Application.Run "ATPVBAEN.XLAM!Regress", .Range("B4:B" & sRow), _
.Range("C4:G" & sRow), False, False, 98, .Range("I4" _
        ), False, False, False, False, , False
End With
End Sub

Còn kiểm định và viết code cho nó chay đi chạy lại đúng giáo trình mới được!(hic hic)

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 !

 

[Thư Sinh Áo Trắng]

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 !

Mình cũng ghi marco rồi sửa thôi! 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à
Tóm lại nói là chạy hồi quy nhưng thực ra mới tìm được phương trình chưa kiểm định mô hình và các hệ số <= mới 10% công việc thôi, ta mới nhờ excel tìm các ma trận và nhân ma trận giúp mình tìm ra phương trình thôi.
90% công việc còn lại toàn là code mà mình cũng đang giải bằng tay trước rồi chuyển sang code.