30 septiembre, 2023

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA

Hola a todos:

Espero que hayáis tenido unas buenas fiestas!. Ahora que ya se acaban, es hora de volver a lo cotidiano y como no podía ser de otra manera, voy a escribir un sencillo post para mostrar cómo podemos calcular una matriz de correlaciones (aunque se podría utilizar para calcular otros estadísticos).

Para este trabajo me he descargado los datos de temperatura media durante el año 2015 en ciertos puntos de España (Fuente: INE). Aquí podéis ver la tabla de ejemplo que vamos a usar:

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA

Es solo una muestra de algunos de los puntos de extracción de información climatológica, pero que nos va a servir perfectamente para realizar nuestro ejemplo.

A continuación,  en una hoja nueva, vamos a crear una matriz de 15 X 15 dado que son el total de elementos que tenemos:

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA1

El siguiente paso es utilizar una sencilla UDF, para ayudarnos con el cálculo de las correlaciones, para ello, en un módulo estándar de nuestro editor de VBA insertáis este código:

Function Correlacion_matrix(rng As Range)
'Declaramos variables
Dim nColum As Long, i As Long, j As Long
Dim matrix As Variant
nColum = rng.Columns.Count - 1
'dimensionamos la matriz
ReDim matrix(nColum, nColum)
'Iniciamos loop para calcular matriz de correlaciones
For i = 0 To nColum
For j = 0 To nColum
matrix(i, j) = Application.WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))
Next j
Next i
'Pasamos resultado a la función
Correlacion_matrix = matrix
End Function

Ahora ya podemos utilizar esta función en nuestra hoja para realizar el cálculo matricial. Para introducir la función, debemos ir a la hoja MATRIZ y seleccionar el espacio que vamos a utilizar, es decir el rango B2:P16

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA2.jpg

Y ahora, esto es importante, sin desmarcar el área seleccionada, os situáis en el espacio de fórmulas y escribís el nombre de la función: =Correlacion_matrix( y os posicionáis en la hoja DATOS, seleccionando el área que contiene los datos de temperatura, que es el rango B2:P13. Es decir, la fórmula quedaría así: =Correlacion_matrix(DATOS!B2:P13)

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA3

Ahora que tenemos la fórmula completa, finalizamos la función seleccionando la fórmula y luego presionando CTRL + MAYUS + ENTRAR

El resultado es este:

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA4

Como podéis observar, ya tenemos nuestra matriz de correlaciones entre las temperaturas medias mensuales durante el año 2015. Dado que las cifras hacen referencia a las temperaturas recogidas en el total de las estaciones, y estas son las mismas para todos los lugares con variaciones similares (no de la temperatura, sino de los incrementos y decrementos y su proporción), la correlación es alta. Las variaciones las debemos buscar en el segundo y tercer decimal, si bien, el dato a interpretar la correlación de la proporción de aumento o disminución de temperatura en el rango de tiempo seleccionado.

Obviamente, en la diagonal de la matriz siempre aparecerá una línea con un 1, que es donde se cruzan los datos del mismo lugar y la correlación es perfecta.

Y esto es todo por hoy, espero que os haya resultado de interés!!.
Descarga el archivo de ejemplo pulsando en: CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Comparte este post

Si te ha gustado o tienes alguna duda, puedes dejar aquí tu comentario.

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.plugin cookies

ACEPTAR
Aviso de cookies