Correlación en Excel: Coeficiente, Matriz y Gráfico
En el vasto océano de datos con el que navegamos diariamente, encontrar patrones y relaciones puede ser un verdadero desafío. ¿Alguna vez te has preguntado si existe una conexión entre dos variables en tus hojas de cálculo? La correlación es la clave para desvelar estos secretos y Excel se convierte en tu mejor aliado en esta aventura de análisis. En este artículo, exploraremos cómo calcular el coeficiente de correlación, crear matrices que revelen interrelaciones y diseñar gráficos que visualicen la magia de los datos. Prepárate para transformar números en historias significativas que te ayudarán a tomar decisiones más acertadas y estratégicas. ¡Comencemos a descubrir lo que esconden esos datos!
Excel es una herramienta poderosa que puede ayudarnos a analizar y visualizar datos de manera efectiva. Entre sus muchas funciones, Excel nos permite calcular la correlación entre variables utilizando diferentes métodos, como el coeficiente de correlación, la matriz de correlación y los gráficos. Estas herramientas no solo nos ayudan a entender la relación entre las variables, sino que también nos permiten tomar decisiones informadas y precisas. En este artículo, exploraremos cómo utilizar estas funciones en Excel y cómo interpretar los resultados obtenidos. ¡Comencemos a descubrir la correlación en Excel!
El tutorial explica los conceptos básicos de la correlación en Excel, muestra cómo calcular un coeficiente de correlación, construir una matriz de correlación e interpretar los resultados.
Uno de los cálculos estadísticos más simples que puedes hacer en Excel es la correlación. Aunque simple, es muy útil para comprender las relaciones entre dos o más variables. Microsoft Excel proporciona todas las herramientas necesarias para ejecutar análisis de correlación, sólo necesita saber cómo utilizarlas.
Correlación en Excel: conceptos básicos
Correlación Es una medida que describe la fuerza y dirección de una relación entre dos variables. Se utiliza comúnmente en estadística, economía y ciencias sociales para presupuestos, planes de negocios y similares.
El método utilizado para estudiar qué tan estrechamente relacionadas están las variables se llama Análisis de correlación.
Aquí hay un par de ejemplos de fuerte correlación:
- La cantidad de calorías que consumes y tu peso (correlación positiva)
- La temperatura exterior y tus facturas de calefacción (correlación negativa)
Y aquí los ejemplos de datos que tienen correlación débil o nula:
- El nombre de tu gato y su comida favorita.
- El color de tus ojos y tu altura.
Una cosa esencial que hay que entender acerca de la correlación es que solo muestra cuán estrechamente relacionadas están dos variables. Sin embargo, la correlación no implica causalidad. El hecho de que los cambios en una variable estén asociados con cambios en la otra variable no significa que una variable realmente cause que la otra cambie.
Si está interesado en aprender sobre causalidad y hacer predicciones, dé un paso adelante y realice un análisis de regresión lineal.
Coeficiente de correlación en Excel – interpretación de la correlación
La medida numérica del grado de asociación entre dos variables continuas se llama coeficiente de correlación (r).
El valor del coeficiente siempre está entre -1 y 1 y mide tanto la fuerza como la dirección de la relación lineal entre las variables.
Fortaleza
Cuanto mayor sea el valor absoluto del coeficiente, más fuerte será la relación:
- Los valores extremos de -1 y 1 indican una relación lineal perfecta cuando todos los puntos de datos caen en una línea. En la práctica, rara vez se observa una correlación perfecta, ya sea positiva o negativa.
- Un coeficiente de 0 indica que no hay una relación lineal entre las variables. Esto es lo que probablemente obtendrá con dos conjuntos de números aleatorios.
- Los valores entre 0 y +1/-1 representan una escala de relaciones débiles, moderadas y fuertes. Como r se acerca a -1 o 1, la fuerza de la relación aumenta.
Dirección
El signo del coeficiente (más o menos) indica la dirección de la relación.
- Positivo Los coeficientes representan una correlación directa y producen una pendiente ascendente en un gráfico: a medida que una variable aumenta, también aumenta la otra y viceversa.
- Negativo Los coeficientes representan una correlación inversa y producen una pendiente descendente en un gráfico: a medida que una variable aumenta, la otra variable tiende a disminuir.
Para una mejor comprensión, eche un vistazo a los siguientes gráficos de correlación:
- un coeficiente de 1 significa una relación positiva perfecta: a medida que una variable aumenta, la otra aumenta proporcionalmente.
- un coeficiente de -1 significa una relación negativa perfecta: a medida que una variable aumenta, la otra disminuye proporcionalmente.
- un coeficiente de 0 significa que no hay relación entre dos variables: los puntos de datos están dispersos por todo el gráfico.
>Correlación de Pearson
En estadística, miden varios tipos de correlación según el tipo de datos con los que se trabaja. En este tutorial, nos centraremos en el más común.
correlación de Pearsonel nombre completo es el Correlación del momento del producto de Pearson (PPMC), se utiliza para evaluar lineal relaciones entre datos cuando un cambio en una variable se asocia con un cambio proporcional en la otra variable. En términos simples, la correlación de Pearson responde a la pregunta: ¿Se pueden representar los datos en una línea?
En estadística, es el tipo de correlación más popular, y si se trata de un «coeficiente de correlación» sin mayor calificación, lo más probable es que sea el de Pearson.
Esta es la fórmula más utilizada para encontrar el coeficiente de correlación de Pearson, también llamado R de Pearson:
>
En ocasiones, es posible que te encuentres con otras dos fórmulas para calcular el coeficiente de correlación muestral (r) y el coeficiente de correlación poblacional (ρ).
Cómo hacer la correlación de Pearson en Excel
Calcular manualmente el coeficiente de correlación de Pearson implica bastantes operaciones matemáticas. Por suerte, Microsoft Excel ha simplificado mucho las cosas. Dependiendo de su conjunto de datos y su objetivo, puede utilizar una de las siguientes técnicas:
Cómo calcular el coeficiente de correlación en Excel
Para calcular un coeficiente de correlación a mano, tendrías que usar esta larga fórmula. Para encontrar el coeficiente de correlación en Excel, aproveche la función CORREL o PEARSON y obtenga el resultado en una fracción de segundo.
Función CORREL de Excel
La función CORREL devuelve el coeficiente de correlación de Pearson para dos conjuntos de valores. Su sintaxis es muy fácil y directa:
CORREL(matriz1, matriz2)
Dónde:
- Matriz1 es el primer rango de valores.
- matriz2 es el segundo rango de valores.
Las dos matrices deben tener la misma longitud.
Suponiendo que tenemos un conjunto de variables independientes (X) en B2:B13 y variables dependientes (y) en C2:C13, nuestra fórmula de coeficiente de correlación es la siguiente:
=CORREL(B2:B13, C2:C13)
O podríamos intercambiar los rangos y seguir obteniendo el mismo resultado:
=CORREL(C2:C13, B2:B13)
De cualquier manera, la fórmula muestra una fuerte correlación negativa (aproximadamente -0,97) entre la temperatura mensual promedio y la cantidad de calentadores vendidos:
>3 cosas que debes saber sobre la función CORREL en Excel
Para calcular correctamente el coeficiente de correlación en Excel, tenga en cuenta estos 3 hechos simples:
- Si una o más celdas de una matriz contienen texto, valores lógicos o espacios en blanco, dichas celdas se ignoran; Se calculan las celdas con valores cero.
- Si las matrices proporcionadas tienen diferentes longitudes, se devuelve un error #N/A.
- Si cualquiera de las matrices está vacía o si la desviación estándar de sus valores es igual a cero, ¡#DIV/0! se produce un error.
Función de Excel PEARSON
La función PEARSON en Excel hace lo mismo: calcula el coeficiente de correlación del momento del producto de Pearson.
PEARSON(matriz1, matriz2)
Dónde:
- Matriz1 es un rango de valores independientes.
- matriz2 es un rango de valores dependientes.
Debido a que PEARSON y CORREL calculan el coeficiente de correlación lineal de Pearson, sus resultados deberían coincidir, y generalmente lo hacen en las versiones recientes de Excel 2007 a Excel 2019.
Sin embargo, en Excel 2003 y versiones anteriores, la función PEARSON puede mostrar algunos errores de redondeo. Por lo tanto, en versiones anteriores, se recomienda utilizar CORREL con preferencia a PEARSON.
En nuestro conjunto de datos de muestra, ambas funciones muestran los mismos resultados:
=CORREL(B2:B13, C2:C13)
=PEARSON(B2:B13, C2:C13)
>
Cómo hacer una matriz de correlación en Excel con Análisis de Datos
Cuando es necesario probar las interrelaciones entre más de dos variables, tiene sentido construir una matriz de correlación, que a veces se denomina coeficiente de correlación múltiple.
El matriz de correlación es una tabla que muestra los coeficientes de correlación entre las variables en la intersección de las filas y columnas correspondientes.
La matriz de correlación en Excel se construye utilizando el Correlación herramienta de la Paquete de herramientas de análisis complemento. Este complemento está disponible en todas las versiones de Excel 2003 hasta Excel 2019, pero no está habilitado de forma predeterminada. Si aún no lo ha activado, hágalo ahora siguiendo los pasos descritos en Cómo habilitar el paquete de herramientas de análisis de datos en Excel.
Con las herramientas de análisis de datos agregadas a su cinta de Excel, estará preparado para ejecutar análisis de correlación:
>
>
- Haga clic en el Rango de entrada y seleccione el rango con sus datos de origen, incluidos los encabezados de columna (B1:D13 en nuestro caso).
- En el Agrupado por sección, asegúrese de que columnas La casilla de radio está seleccionada (dado que sus datos de origen están agrupados en columnas).
- Selecciona el Etiquetas en la primera fila casilla de verificación si el rango seleccionado contiene encabezados de columna.
- Elija la opción de salida deseada. Para tener la matriz en la misma hoja, seleccione Rango de salida y especifique la referencia a la celda más a la izquierda en la que se generará la matriz (A15 en este ejemplo).
Cuando termine, haga clic en el DE ACUERDO botón:
>
Su matriz de coeficientes de correlación está lista y debería verse como se muestra en la siguiente sección.
Interpretación de los resultados del análisis de correlación
En su matriz de correlación de Excel, puede encontrar los coeficientes en la intersección de filas y columnas. Si las coordenadas de la columna y la fila son las mismas, se genera el valor 1.
En el ejemplo anterior, nos interesa conocer la correlación entre la variable dependiente (número de calentadores vendidos) y dos variables independientes (temperatura promedio mensual y costos de publicidad). Entonces, solo miramos los números en la intersección de estas filas y columnas, que están resaltados en la siguiente captura de pantalla:
>
El coeficiente negativo de -0,97 (redondeado a 2 decimales) muestra una fuerte correlación inversa entre la temperatura mensual y las ventas de calentadores: a medida que aumenta la temperatura, se venden menos calentadores.
El coeficiente positivo de 0,97 (redondeado a 2 decimales) indica una fuerte conexión directa entre el presupuesto publicitario y las ventas: cuanto más dinero se gasta en publicidad, mayores serán las ventas.
Cómo hacer análisis de correlación múltiple en Excel con fórmulas
Crear la tabla de correlación con la herramienta de Análisis de datos es fácil. Sin embargo, esa matriz es estática, lo que significa que deberá ejecutar un análisis de correlación nuevamente cada vez que cambien los datos de origen.
La buena noticia es que usted mismo puede crear fácilmente una tabla de correlación similar y esa matriz se actualizará automáticamente con cada cambio en los valores de origen.
Para hacerlo, utilice esta fórmula genérica:
CORREL(DESPLAZAMIENTO(primer_rango_variable 0, FILAS($1:1)-1), DESPLAZAMIENTO(primer_rango_variable0, COLUMNAS($A:A)-1))
¡Nota IMPORTANTE! Para que la fórmula funcione, debes bloquear el primer rango de variables utilizando referencias de celda absolutas.
En nuestro caso, el primer rango de variable es $B$2:$B$13 (observe el signo $ que bloquea la referencia), y nuestra fórmula de correlación toma esta forma:
=CORREL(OFFSET($B$2:$B$13, 0, ROWS($1:1)-1), OFFSET($B$2:$B$13, 0, COLUMNS($A:A)-1))
Con la fórmula lista, construyamos una matriz de correlación:
Como resultado, tenemos la siguiente matriz con múltiples coeficientes de correlación. Tenga en cuenta que los coeficientes devueltos por nuestra fórmula son exactamente los mismos que los generados por Excel en el ejemplo anterior (los relevantes están resaltados):
>Cómo funciona esta fórmula
Como ya sabes, el Función CORREL de Excel devuelve el coeficiente de correlación para dos conjuntos de variables que especifique. El principal desafío es proporcionar los rangos apropiados en las celdas correspondientes de la matriz. Para esto, ingresa solo el primer rango de variables en la fórmula y usa las siguientes funciones para realizar los ajustes necesarios:
- OFFSET: devuelve un rango que es un número determinado de filas y columnas de un rango específico.
- FILAS y COLUMNAS: devuelven el número de filas y columnas de un rango, respectivamente. En nuestra fórmula de correlación, ambos se utilizan con un propósito: obtener el número de columnas que se deben desplazar del rango inicial. Y esto se consigue utilizando inteligentemente referencias absolutas y relativas.
Para comprender mejor la lógica, veamos cómo la fórmula calcula los coeficientes resaltados en la captura de pantalla anterior.
Primero, examinemos la fórmula en B18, que encuentra una correlación entre la temperatura mensual (B2:B13) y los calentadores vendidos (D2:D13):
=CORREL(OFFSET($B$2:$B$13, 0, ROWS($1:3)-1), OFFSET($B$2:$B$13, 0, COLUMNS($A:A)-1))
En la primera función DESPLAZAMIENTO, FILAS($1:1) se ha transformado en FILAS($1:3) porque la segunda coordenada es relativa, por lo que cambia según la posición relativa de la fila donde se copia la fórmula (2 filas hacia abajo). Por lo tanto, ROWS() devuelve 3, del cual restamos 1 y obtenemos un rango que está 2 columnas a la derecha del rango de origen, es decir, $D$2:$D$13 (ventas de calentadores).
El segundo DESPLAZAMIENTO no cambia el rango especificado $B$2:$B$13 (temperatura) porque COLUMNAS($A:A)-1 devuelve cero.
Como resultado, nuestra fórmula larga se convierte en un CORREL simple($D$2:$D$13, $B$2:$B$13) y devuelve exactamente el coeficiente que queremos.
La fórmula en C18 que calcula un coeficiente de correlación para el costo de publicidad (C2:C13) y las ventas (D2:D13) funciona de manera similar:
=CORREL(OFFSET($B$2:$B$13, 0, ROWS($1:3)-1), OFFSET($B$2:$B$13, 0, COLUMNS($A:B)-1))
La primera función OFFSET es absolutamente la misma que la descrita anteriormente, devolviendo el rango de $D$2:$D$13 (ventas de calentadores).
En el segundo DESPLAZAMIENTO, COLUMNAS($A:A)-1 cambia a COLUMNAS($A:B)-1 porque hemos copiado la columna de fórmula 1 a la derecha. En consecuencia, OFFSET obtiene un rango que está 1 columna a la derecha del rango de origen, es decir, $C$2:$C$13 (costo publicitario).
Cómo trazar un gráfico de correlación en Excel
Al hacer correlación en Excel, la mejor manera de obtener una representación visual de las relaciones entre sus datos es dibujar una gráfico de dispersión con un línea de tendencia. Así es cómo:
Para obtener instrucciones detalladas paso a paso, consulte:
Para nuestro conjunto de datos de muestra, los gráficos de correlación se ven como se muestra en la imagen a continuación. Además, mostramos el valor R cuadrado, también llamado Coeficiente de determinación. Este valor indica qué tan bien se corresponde la línea de tendencia con los datos: cuanto más cerca R2 a 1, mejor será el ajuste.
De la R2 valor que se muestra en su diagrama de dispersión, puede calcular fácilmente el coeficiente de correlación:
Por ejemplo, la R.2 El valor en el segundo gráfico es 0,9174339392. Entonces, puedes encontrar el coeficiente de correlación para Publicidad y Calentadores vendidos con una de estas fórmulas:
=SQRT(0.9174339392)
=0.9174339392^0.5
Como puede asegurarse, los coeficientes calculados de esta manera están perfectamente en línea con los coeficientes de correlación encontrados en los ejemplos anteriores. excepto el signo:
>
Posibles problemas con la correlación en Excel
El Correlación del momento del producto de Pearson sólo revela un lineal relación entre las dos variables. Es decir, sus variables pueden estar fuertemente relacionadas de otra manera, curvilínea, y aún tener el coeficiente de correlación igual o cercano a cero.
La correlación de Pearson no es capaz de distinguir dependiente y independiente variables. Por ejemplo, cuando utilizamos la función CORREL para encontrar la asociación entre una temperatura mensual promedio y la cantidad de calentadores vendidos, obtuvimos un coeficiente de -0,97, lo que indica una correlación negativa alta. Sin embargo, puede cambiar las variables y obtener el mismo resultado. Entonces, alguien puede concluir que las mayores ventas de calentadores hacen que la temperatura baje, lo que obviamente no tiene sentido. Por lo tanto, cuando ejecute un análisis de correlación en Excel, tenga en cuenta los datos que proporciona.
Además, la correlación de Pearson es muy sensible a valores atípicos. Si tiene uno o más puntos de datos que difieren mucho del resto de los datos, puede obtener una imagen distorsionada de la relación entre las variables. En este caso, sería prudente utilizar la correlación de rango de Spearman.
Así es como se hace la correlación en Excel. Para ver más de cerca los ejemplos analizados en este tutorial, puede descargar nuestro libro de trabajo de muestra a continuación. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Libro de práctica
Calcular la correlación en Excel (archivo .xlsx)
Usted también podría estar interesado en
Matriz de correlacion con Excel Coeficiente de correacion
Correlación en Excel: coeficiente, matriz y gráfico
La correlación es una herramienta estadística utilizada para medir la relación entre dos variables. En Excel, puedes calcular la correlación utilizando el coeficiente de correlación, la matriz de correlación y representarla en un gráfico. En este artículo, responderemos algunas preguntas frecuentes sobre la correlación en Excel.
1. ¿Qué es el coeficiente de correlación en Excel?
El coeficiente de correlación en Excel es una medida que indica la fuerza y dirección de la relación entre dos variables. Puede tener valores que van desde -1 hasta 1. Un valor de -1 indica una correlación perfectamente negativa, 0 indica ninguna correlación y 1 indica una correlación perfectamente positiva.
Puedes calcular el coeficiente de correlación en Excel utilizando la función «CORREL» seguida de los rangos de las dos variables. Por ejemplo, «=CORREL(A1:A10, B1:B10)» calculará el coeficiente de correlación entre los valores en los rangos A1:A10 y B1:B10.
2. ¿Cómo obtener una matriz de correlación en Excel?
Una matriz de correlación muestra las correlaciones entre varias variables. Para obtener una matriz de correlación en Excel, debes utilizar la función «CORREL» en combinación con la función «MATRIZ.DE.CORREL».
La sintaxis para crear una matriz de correlación es «=MATRIZ.DE.CORREL(rango_de_variables)», donde el «rango_de_variables» es el rango que contiene las variables para las cuales deseas calcular las correlaciones.
3. ¿Cómo representar la correlación en un gráfico en Excel?
Excel ofrece diferentes tipos de gráficos para representar la correlación entre dos variables. Puedes utilizar un gráfico de dispersión (scatter plot) para visualizar la relación entre los valores de las variables.
Para crear un gráfico de dispersión en Excel, selecciona los datos que deseas graficar y ve a la pestaña «Insertar». Luego, elige el tipo de gráfico de dispersión que mejor se adecúe a tus datos. Puedes personalizar el gráfico añadiendo etiquetas, leyendas y otros elementos visuales.
Puedes obtener más información y ejemplos de la representación de la correlación en Excel en este enlace.
Conclusión
En resumen, Excel proporciona diferentes herramientas para calcular y representar la correlación entre variables. Puedes utilizar el coeficiente de correlación para medir la relación, la matriz de correlación para obtener un panorama más completo y los gráficos de dispersión para visualizar la correlación. Estas herramientas son útiles para analizar datos y tomar decisiones informadas.
[automatic_youtube_gallery type=»search» search=»Correlación en Excel: coeficiente, matriz y gráfico.» limit=»1″]
De -1 a 1. Un valor de -1 indica una fuerte correlación negativa, 0 indica que no hay correlación y 1 indica una fuerte correlación positiva.
- ¿Cómo se calcula el coeficiente de correlación en Excel?
Puedes calcularlo utilizando la función CORREL que toma como argumentos dos rangos de datos y devuelve el coeficiente de correlación. La sintaxis es:
excel
=CORREL(rango1, rango2)
- ¿Qué es una matriz de correlación?
Una matriz de correlación es una tabla que muestra los coeficientes de correlación entre múltiples variables. Cada celda en la matriz representa la correlación entre dos variables. Utilizando las herramientas de análisis de datos de Excel, puedes generar fácilmente esta matriz.
- ¿Cómo se puede visualizar la correlación en Excel?
Puedes trazar un gráfico de dispersión y agregar una línea de tendencia. Esto permite ver cómo se relacionan visualmente las variables. Adicionalmente, puedes calcular el valor R cuadrado para tener una idea del ajuste entre la línea de tendencia y tus datos.
- ¿Cuál es la diferencia entre correlación y causalidad?
La correlación no implica causalidad. Aunque dos variables puedan estar correlacionadas, esto no significa que una causa la otra. Otras variables pueden influir en la relación, y es importante tener en cuenta el contexto de los datos.
- ¿Qué precauciones deben tomarse al analizar correlaciones?
– Valores atípicos: Los valores extremos pueden distorsionar los resultados de la correlación. Es recomendable revisar tus datos y considerar su impacto.
– Relaciones no lineales: La correlación de Pearson solo detecta relaciones lineales. Para relaciones no lineales, es mejor considerar otras métricas, como la correlación de rango de Spearman.
- ¿Qué hacer si mis datos cambian frecuentemente?
Puedes utilizar la fórmula de matriz de correlación que se mencionó anteriormente, asegurando que se actualice automáticamente a medida que cambien los datos.
Con estos conceptos y procedimientos, puedes realizar análisis de correlación en Excel de manera efectiva y comprender mejor las relaciones entre tus datos.
Lluis miquel: ¡Totalmente de acuerdo, sadornil! La matriz de correlación es clave. Yo también la usé cuando estaba organizando unos datos para un trabajo y fue increíble ver cómo ciertas variables se relacionaban. Sin duda, un recurso que todos deberíamos dominar. ¡Gracias por compartirlo!
Castiñeiras: ¡Totalmente, chicos! La correlación en Excel es un must. Yo me acuerdo que cuando estaba analizando unas encuestas para mi tesis, usar el gráfico de correlación me ayudó un montón a visualizar las tendencias. Es increíble cómo puede simplificar todo el rollo de los datos. ¡Gran artículo!
¡Excelente post! Me encanta cómo explicaste la correlación en Excel, es súper útil para analizar datos. Recuerdo cuando estaba trabajando en un proyecto de investigación y tuve que usar la matriz de correlación; me salvó la vida al ayudarme a identificar las relaciones entre variables. ¡Gracias por compartir esto, sadornil!