Hồi quy tuyến tính đa biến trong VBA (1 người xem)

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!

VetMini · 5/5/21

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 · 5/5/21

- 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

VetMini đã viết:
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 · 5/5/21

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 · 5/5/21

VetMini đã viết:
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 · 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.

HieuCD · 5/5/21

Thư Sinh Áo Trắng đã viết:
- Đú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 · 5/5/21

HieuCD đã viết:
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

VetMini đã viết:
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 · 5/5/21

Thư Sinh Áo Trắng đã viết:
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 · 6/5/21

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 · 6/5/21

Nguyễn Duy Tuân đã viết:
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 · 6/5/21

Thư Sinh Áo Trắng đã viết:
- 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 · 6/5/21

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

PHP:

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 · 6/5/21

Thư Sinh Áo Trắng đã viết:
View attachment 258158
Code ghi marco được có thế này thôi bác @HieuCD ơi

PHP:

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 · 7/5/21

HieuCD đã viết:
"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 · 7/5/21

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

Thư Sinh Áo Trắng đã viết:
View attachment 258158
Code ghi marco được có thế này thôi bác @HieuCD ơi

PHP:

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 · 7/5/21

soledad_90 đã viết:
Ở đâ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:

PHP:

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 · 7/5/21

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

PHP:

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

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.

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

CHAOQUAY · 14/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!

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

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

CHAOQUAY đã viết:
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 · 14/5/21

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 · 14/5/21

Maika8008 đã viết:
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 · 14/5/21

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

Thư Sinh Áo Trắng đã viết:
+ 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 · 14/5/21

HieuCD đã viết:
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 · 15/5/21

Thư Sinh Áo Trắng đã viết:
- 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 · 15/5/21

HieuCD đã viết:
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 · 15/5/21

Thư Sinh Áo Trắng đã viết:
- 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 · 15/5/21

HieuCD đã viết:
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 · 15/5/21

Thư Sinh Áo Trắng đã viết:
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 · 15/5/21

HieuCD đã viết:
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 · 17/5/21

*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 · 18/5/21

Thư Sinh Áo Trắng đã viết:
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 · 18/5/21

Ngô Hải Đăng đã viết:
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 · 18/5/21

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

PHP:

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 · 18/5/21

Ngô Hải Đăng đã viết:
Copy code này vô module

PHP:

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 · 18/5/21

Ngô Hải Đăng đã viết:
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

Ngô Hải Đăng đã viết:
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 · 18/5/21

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

PHP:

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 · 18/5/21

Ngô Hải Đăng đã viết:

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

PHP:

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!

Ngô Hải Đăng đã viết:
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?

Ngô Hải Đăng · 18/5/21

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 · 18/5/21

Ngô Hải Đăng đã viết:
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 · 18/5/21

Thư Sinh Áo Trắng đã viết:
- 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 · 18/5/21

Ngô Hải Đăng đã viết:
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 · 18/5/21

Thư Sinh Áo Trắng đã viết:
- 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 · 18/5/21

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

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

Ngô Hải Đăng đã viết:
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 · 18/5/21

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

PHP:

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

thành

PHP:

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

là được.

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

Ngô Hải Đăng đã viết:
Copy code này vô module

PHP:

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 · 21/5/21

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 · 21/5/21

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

PHP:

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 · 21/5/21

Ngô Hải Đăng đã viết:

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

PHP:

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 · 21/5/21

Ngô Hải Đăng đã viết:
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.

PHP:

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 · 21/5/21

Thư Sinh Áo Trắng đã viết:
Dựa vào Function CorrelMatrix chế được cái Sub cho thêm chọn lựa.

PHP:

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 · 21/5/21

Ngô Hải Đăng đã viết:
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 · 22/5/21

Thư Sinh Áo Trắng đã viết:
Ô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 · 22/5/21

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 · 22/5/21

soledad_90 đã viết:
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.

Ngô Hải Đăng đã viết:
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 · 23/5/21

Thư Sinh Áo Trắng đã viết:
- "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 · 23/5/21

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

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

HieuCD đã viết:
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

Vâng em chọn một số loại có thể đưa về tuyến tính rồi xét. Nhờ bác thi thoảng đảo qua Top giúp em với.

Hau151978 đã viết:
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.

Bất khả thi bạn ạ. Mình có lập 1 chủ đề khác để test rồi, giờ hi vọng chuyển về kích thước 16385 hàng x 16384 cột để test, file này cũng hơn 2 Gg

Hồi quy tuyến tính đa biến trong VBA (1 người xem)

Người dùng đang xem chủ đề này

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

Đang đi tìm hòn đá

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

Đang đi tìm hòn đá

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

Đang đi tìm hòn đá

Chuyên gia GPE

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

Chuyên gia GPE

Nghị Hách

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

Nghị Hách

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

File đính kèm

Chuyên gia GPE

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

Chuyên gia GPE

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

File đính kèm

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

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

Chuyên gia GPE

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

Chuyên gia GPE

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

Chuyên gia GPE

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

File đính kèm

Chuyên gia GPE

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

File đính kèm

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

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

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

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

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

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

File đính kèm

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

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

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

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

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

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

File đính kèm

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

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

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

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

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

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

File đính kèm