通过Excel VBA创建关联矩阵

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

任何帮助将不胜感激！

3 个解决方案

#1

 Application.Run "ATPVBAEN.XLAM!Mcorrel", ActiveSheet.Range("$C$3:$F$6"), _
    ActiveSheet.Range("$C$10"), "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

要运行它，您必须首先启用Data Analysis Toolpack（包）。您可以通过UI，选项卡数据分析 - >关联矩阵来使用它

here:

这里：

"$C$3:$F$6" - input (square matrix)
$C$10 - output cell
"K" (or "C") - group by columns
false - labels=no

#2

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

#3

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

#1

 Application.Run "ATPVBAEN.XLAM!Mcorrel", ActiveSheet.Range("$C$3:$F$6"), _
    ActiveSheet.Range("$C$10"), "K", False //"K" might be "C"=column