De la matriz a de covarianzas a la de correlaciones con Excel

Me preguntan cómo construir la matriz de correlaciones a partir de la de covarianzas con Excel. Mis lectores más versados en R conocerán la existencia de la función cov2cor (cuyo código fuente merece ser examinado).

Sin embargo, ¿cómo hacerlo con Excel? No es tan complicado, aunque infinitamente más prolijo: en la posición (i,j) de la matriz de correlaciones hay que asignar:

  • el valor (i,j) de la correspondiente matriz de covarianzas
  • dividido por la raíz cuadrada del producto de los valores (i,i) y (j,j) de la matriz de covarianzas.

Tan fácil como parece, implementarlo en Excel es poco menos que una tortura. Partiendo de una matriz de covarianzas A1:C3,

creamos una matriz adjunta de acuerdo con la fórmula que aparece en el gráfico:

Copiamos la nueva matriz y la pegamos trasponiendo los datos:

Finalmente, multiplicamos las tres matrices de acuerdo con la fórmula que aparece en el gráfico:

Quien haya seguido estas instrucciones habrá aprendido dos cosas:

  1. Cómo convertir una matriz de covarianzas en una de correlaciones usando Excel.
  2. Por qué Excel mata la productividad (y si tu productividad mensual no excede los 1000 euros, además, a entender por qué eres un mileurista).

4 comentarios sobre “De la matriz a de covarianzas a la de correlaciones con Excel

  1. Juan José Gibaja Martíns 27 noviembre, 2010 21:03

    Hola:

    Vaya por delante que no soy un fan de Excel pero dicha hoja de cálculo (y también la de OpenOffice) dispone de la función =TRANSPONER(Matriz), que puede ser muy útil para los menesteres de los que se trata en este post.

  2. datanalytics 27 noviembre, 2010 21:50

    @Juan José Gibaja Martíns
    Bien, bien… pero ¿hay que hacer algo “a mano”? Yo no he sabido extraer la diagonal de la matriz original sin copiar y pegar… (aunque admito que sé nada de Excel: es una herramienta que más que usar sufro y de la que principalmente sólo uso la función de exportación a csv).

  3. Juan José Gibaja Martíns 28 noviembre, 2010 11:58

    Yo procuro huir de Excel en la medida de lo posible y, como tú, casi me limito a exportar xls a csv. Sin embargo, debo reconocer que me viene bien para explicar el modelo de regresión lineal múltiple a alumnos de grado en ADE, para lo que me sirvo de las funciones =TRANSPONER, =MMULT y =MINVERSA.

    De todas maneras, cuando les muestro que el tortuoso camino que han debido seguir para calcular B (el vector de coeficientes estimados del modelo de regresión) puede sustituirse en R con el comando B<-solve(t(X)%*%X)%*%t(X)%*%Y suelen mirarme con cara de odio.

Los comentarios están desabilitados.