通过 Excel VBA 创建相关矩阵
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16046274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a correlation matrix via Excel VBA
提问by Kristina
What would be the best way to create a correlation matrix via Excel VBA? My data has 45 columns (which may eventually change) and 12000 rows (which can change as well). I was going to just use the correlfunction on the sheet but like I said, my columns and rows may change with time.
通过 Excel VBA 创建相关矩阵的最佳方法是什么？我的数据有 45 列（最终可能会更改）和 12000 行（也可能会更改）。我打算只使用correl工作表上的功能，但就像我说的，我的列和行可能会随着时间而改变。
Any help will be greatly appreciated!
任何帮助将不胜感激！
采纳答案by 4pie0
Application.Run "ATPVBAEN.XLAM!Mcorrel", ActiveSheet.Range("$C:$F"), _
ActiveSheet.Range("$C"), "K", False //"K" might be "C"=column
to run this you have to enable Data Analysis Toolpack (package) first. You can use this via UI, tab Data Analysis->correlation matrix
要运行它，您必须首先启用数据分析工具包（包）。您可以通过 UI 使用它，选项卡 Data Analysis->correlation matrix
here:
这里：
"$C:$F" - input (square matrix)
$C - output cell
"K" (or "C") - group by columns
false - labels=no
回答by Algos
I have searched the web for VBA Correlation Matrix Code and havent found anything of substance. Did some coding myself, it aint beautiful, however it does the job. This code will create a matrix to the right of the last data series.
我在网上搜索了 VBA 相关矩阵代码，但没有找到任何实质性内容。我自己做了一些编码，它并不漂亮，但是它可以完成工作。此代码将在最后一个数据系列的右侧创建一个矩阵。
Sub CorrelationMatrix()
Dim y As Range
Dim z As Range
funds = Application.Workbooks("VBAcorrelation").Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
rader = 0
For x = 1 To funds
nyrad = Cells(Rows.Count, x).End(xlUp).Row
If nyrad > rader Then
rader = Cells(Rows.Count, x).End(xlUp).Row
End If
Next x
p = 1
u = 2
For h = 1 To funds
For u = 1 To funds
Set y = ActiveSheet.Range(Cells(2, h), Cells(rader, h))
Set z = ActiveSheet.Range(Cells(2, u), Cells(rader, u))
Correl = WorksheetFunction.Correl(y, z)
Worksheets("Sheet1").Cells(h + 1, funds + u + 3).Select
ActiveCell = Correl
Next u
Next h
MsgBox "Done with Matrix"
End Sub
回答by Rho
This pair of functions give you the result in a matrix (select the range where it should appear, introduce the formula, then press F2 and then Ctrl+shift+enter to see the values). Copy both and paste on the VBA editor.
这对函数为您提供矩阵中的结果（选择它应该出现的范围，引入公式，然后按 F2，然后按 Ctrl+shift+enter 以查看值）。将两者复制并粘贴到 VBA 编辑器上。
'function to create a correlation matrix given the data
Function CorrMatriz(Mat_data As Variant)
Dim i As Integer, j As Integer, corr As Variant, M1 As Variant, M2 As Variant
ReDim corr(1 To Mat_data.Columns.Count, 1 To Mat_data.Columns.Count)
ReDim M1(1 To Mat_data.Rows.Count, 1 To 1)
ReDim M2(1 To Mat_data.Rows.Count, 1 To 1)
For i = 1 To Mat_data.Columns.Count
M1 = ExtraeMatriz(Mat_data, i)
For j = 1 To Mat_data.Columns.Count
M2 = ExtraeMatriz(Mat_data, j)
corr(i, j) = Application.Correl(M1, M2)
Next j
Next i
CorrMatriz = corr
End Function`
' function to extract one column
enter code here
Function ExtraeMatriz(Matriz As Variant, columna As Integer)
Dim i As Integer, data_final As Variant
ReDim data_final(1 To Matriz.Rows.Count, 1)
For i = 1 To Matriz.Rows.Count
data_final(i, 1) = Matriz(i, columna)
Next i
ExtraeMatriz = data_final
End Function
回答by Moreno
It works fine:
它工作正常：
Option base 1
Function MCorrelation(rango As Range) As Variant
Dim x As Variant, y As Variant, s As Integer, t As Integer, c() As Variant
ReDim c(rango.Columns.Count, rango.Columns.Count)
For i = 1 To rango.Columns.Count Step 1
For j = 1 To i Step 1
c(i, j) = Application.Correl(Application.Index(rango, , i), Application.Index(rango, , j))
Next j
Next i
MCorrelation = c
End Function
