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

Anuncios

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

w

Conectando a %s