Вопрос:
Какой лучший способ создать корреляционную матрицу через Excel VBA? Мои данные имеют 45 столбцов (которые могут в конечном итоге меняться) и 12000 строк (что также может измениться). Я собирался просто использовать функцию correl на листе, но, как я уже сказал, мои столбцы и строки могут меняться со временем.
Любая помощь будет принята с благодарностью!
Лучший ответ: Application.Run «ATPVBAEN.XLAM!Mcorrel», ActiveSheet.Range(«$C$3:$F$6»), _ ActiveSheet.Range(«$C$10»), «K», False //»K» might be «C»=column
чтобы запустить это, сначала необходимо включить Data Analysis Toolpack (пакет).
Вы можете использовать это через пользовательский интерфейс, вкладку “Анализ данных” → корреляционная матрица
здесь:
«$C$3:$F$6» — input (square matrix) $C$10 — output cell «K» (or «C») — group by columns false — labels=no Ответ №1
Я искал в Интернете для VBA Correlation Matrix Code и havent нашел что-то из существа. Сама какая-то кодировка, она прекрасна, однако она выполняет эту работу.
Этот код создаст матрицу справа от последней серии данных.
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 Ответ №2
Эта пара функций выдает результат в матрице (выберите диапазон, в котором он должен появиться, введите формулу, затем нажмите 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 Ответ №3
Он отлично работает:
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
