Coeficiente de correlación en Excel: Cómo entenderlo y calcularlo

Información clave

El coeficiente de correlación -también conocido como “Coeficiente de correlación de Pearson”- es una medida de la relación que existe entre dos variables, es decir, cómo se mueve una ante movimientos en la otra. En Excel se puede calcular principalmente de dos formas, dependiendo si queremos obtener la correlación entre solamente dos variables o si queremos obtener la matriz de correlación entre varios pares de ellas.

¿Qué es la correlación?

La correlación entre dos variables es la relación proporcional que existe entre ellas, es decir, cómo cambia una ante cambios en la otra. Por ejemplo, podríamos esperar que exista cierta relación entre los ingresos y costos de una empresa. Lo más probable es que en este caso tengamos una correlación positiva, ya mientras mayores sean los ingresos, mayores sean los costos también.

Para medir la correlación trabajamos con el coeficiente de correlación, que puede tomar un valor entre -1 y 1 según las diferentes relaciones que existan entre dos variables.

Si es que las variables tienen un coeficiente de correlación de 1 significa que tienen una correlación positiva perfecta. Cuando una variable aumenta, la otra también aumenta. Cuando una variable disminuye, la otra también lo hace. Para entender a lo que nos referimos, veamos el siguiente ejemplo en el que tenemos los ingresos y costos de una empresa:

Podemos ver en el ejemplo que a medida que aumentan los ingresos también aumentan los costos, lo que queda claramente reflejado en el gráfico de líneas.

Tip Ninja: El coeficiente de correlación es muy usado para ver la correlación de dos variables en el tiempo, por lo que es muy común que vaya acompañado de un gráfico de líneas para que sea más fácil ilustrar la relación. Si quieres aprender cómo hacer un gráfico de líneas en Excel fácilmente puedes ir al siguiente artículo. Si quieres aprender a hacer otro tipo de gráficos haz click aquí.

Por otro lado, si las variables presentan un coeficiente de correlación de -1 significa que tienen una correlación negativa perfecta (también conocida como relación inversa). En este caso, a medida que una de las variables aumenta, la otra disminuye:

Ahora en el ejemplo ocurre el caso contrario que el caso pasado, a medida que aumentan los ingresos, los costos caen en una proporción similar.

Si es que el coeficiente de correlación entre las variables es muy cercano a 0, significa que no existe ningún tipo de relación entre ellas y que los movimientos de una variable no van acompañados de cambios en la otra. De esta forma, a medida que nos acercamos al 0 la relación entre las variables es menor y no es de una relación proporcional constante. Generalmente coefientes de correlación mayores a 0,5 o menores a -0,5 describen relaciones relativamente importantes.

Hay que tener en cuenta que el coeficiente de correlación describe una relación, no que una variable esté teniendo un efecto directo en la otra. Para entender más acerca de esta diferencia haz click aquí.

Forma 1: Utilizando la función COEF DE CORREL

La primera forma de obtener la correlación entre 2 variables es usar la función COEF DE CORREL. Esta forma es la más simple y rápida, pero sólo permite obtener la correlación entre un par de variables a la vez:

• Sintáxis:

=COEF.DE.CORREL(matriz1; matriz2)

• Argumentos:

matriz1 = Obligatorio. Primer rango de celdas sobre el que queremos calcular el coeficiente de correlación. Contiene la información de la primera de las variables a usar.

matriz2 = Obligatorio. Segundo rango de celdas sobre el que queremos calcular el coeficiente de correlación. Contiene la información de la segunda de las variables a usar.

Volviendo al ejemplo de los ingresos y costos de nuestra empresa, lo único que debemos hacer es seleccionar nuestra primera variable, en este caso los “Ingresos”, como nuestra matriz1 y nuestra segunda variable, los “Costos”, como la matriz2:

Con lo que obtenemos el siguiente resultado:

Cómo podemos ver tenemos un coeficiente de correlación de 0,7, lo que significa que los ingresos y costos presentan una correlación positiva.

Forma 2: Obteniendo varios coeficientes de correlación a la vez

La segunda forma para obtener el coeficiente de correlación es un poco más compleja, pero nos permite obtener en un mismo cálculo los diferentes coeficientes de correlación entre más de un par de variables.

Activando las herramientas para calcula el coeficiente correlación en Excel

El primer paso al intentar calcular la correlación en Excel con este método es asegurarse de que tengamos activada la herramienta llamada “Análisis de datos”. Para esto hacemos lo siguiente:

Paso 1: Hacemos click en la opción “Archivo” en la barra principal de Excel:

Paso 2: En la barra lateral izquierda hacemos click en “Opciones”.

Paso 3: Se abrirá una nueva ventana, en la cual hacemos click en “Complementos”:

Paso 4: En la ventana de complementos hacemos click en la casilla “Ir”:

Paso 5: Se abrirá una nueva ventana, en la que debemos asegurarnos de que la opción “Herramientas para análisis” tenga un ticket en la casilla a su izquierda. Luego de esto hacemos click en “Aceptar”:

Paso 6: Si es que realizamos todos estos pasos correctamente la herramienta “Análisis de datos” debería estar en la pestaña “Datos” de la barra principal de Excel, en la sección “Análisis”:

Usando “Análisis de datos” para calcular los coeficientes correlación

Ahora para calcular el coeficiente de correlación en Excel usando la herramienta “Análisis de datos” veamos un ejemplo similar al de la forma 1, pero ahora incluyendo que tenemos tanto costos variables como costos fijos:

Paso 1: Hacemos click en la herramienta “Análisis de datos”, lo que abrirá la siguiente ventana:

Paso 2: Seleccionamos la opción “Coeficiente de correlación” y hacemos click en “Aceptar” (también podemos hacer doble click), lo que abrirá la siguiente ventana:

Paso 3: En la sección “Rango de entrada” indicamos el lugar donde se encuentran nuestros datos, asegurándonos de incluir los nombres de las variables en la selección de celdas. Para hacer este proceso más fácilmente podemos hacer click en la flecha negra de la derecha y seleccionar las celdas manualmente:

Paso 4: Ahora indicamos en “Agrupado por” que nuestras variables se encuentran en diferentes columnas, seleccionando la opción “Columnas”. Además, indicamos que los nombres de las variables están en la primera fila, con la opción “Rótulos en la primera fila”:

Paso 5: Lo último que queda por hacer es indicar dónde queremos que Excel nos entregue la tabla con los diferentes coeficientes de correlación. En este caso queremos que aparezca en la celda F2, por lo que seleccionamos esa celda:

Tip Ninja: Tengamos en cuenta que la tabla que nos entrega Excel ocupa más de una celda, incluso aunque indiquemos sólo una celda, por lo que hay que asegurarse de tener espacio suficiente para que la tabla de salida no sobrescriba información que tengamos.

¡Listo! Haciendo click en “Aceptar” aparecerá la información que estamos buscando, en lo que se conoce como “Matriz de correlación”:

Para ver el coeficiente de correlación entre dos variables sólo es necesario ir a la tabla y ver dónde se intersecta la fila de una con la columna de otra. Por ejemplo, si queremos ver la intersección entre “Ingresos” y “Costos fijos”:

Podemos ver entonces que el coeficiente de correlación entre esas dos variables es -0,1.

Tip Ninja: La herramienta “Análisis de datos” permite acceder en Excel a otras funciones estadísticas muy útiles. Una de las más importantes son las regresiones. Si quieres aprender a hacer regresiones en Excel puedes ir al siguiente artículo.