Cómo calcular el coeficiente de correlación lineal. Coeficiente de correlación de pares en Excel
Se puede obtener una característica cuantitativa de la relación calculando el coeficiente de correlación.
Análisis de correlación en Excel
La función en sí tiene la forma general CORREL(matriz1, matriz2). En el campo "Array1", ingrese las coordenadas del rango de celdas de uno de los valores, cuya dependencia debe determinarse. Como puedes ver, el coeficiente de correlación en forma de número aparece en la celda que seleccionamos previamente. Se abre una ventana con parámetros de análisis de correlación. A diferencia del método anterior, en el campo "Intervalo de entrada" ingresamos el intervalo no de cada columna por separado, sino de todas las columnas que participan en el análisis. Como puede ver, la aplicación Excel ofrece dos métodos de análisis de correlación a la vez.
grafico de correlacion en excel
6) El primer elemento de la mesa final aparecerá en la celda superior izquierda del área seleccionada. Por tanto, se rechaza la hipótesis H0, es decir, los parámetros de regresión y el coeficiente de correlación no son aleatoriamente diferentes de cero, sino que son estadísticamente significativos. 7. Las estimaciones obtenidas de la ecuación de regresión permiten utilizarla para realizar pronósticos.
Cómo calcular el coeficiente de correlación en Excel
Si el coeficiente es 0, esto indica que no existe relación entre los valores. Para encontrar la relación entre las variables e y, utilice la función integrada de Microsoft Excel "CORREL". Por ejemplo, para "Array1" seleccione los valores de y, y para "Array2" seleccione los valores de x. Como resultado, recibirá el coeficiente de correlación calculado por el programa. A continuación, debe calcular la diferencia entre cada x, xav y yav. En las celdas seleccionadas, escriba las fórmulas x-x, y-. No olvide fijar celdas con promedios. El resultado obtenido será el coeficiente de correlación deseado.
La fórmula anterior para calcular el coeficiente de Pearson muestra cuán laborioso es este proceso si se realiza manualmente. En segundo lugar, recomiende qué tipo de análisis de correlación se puede utilizar para diferentes muestras con una gran variedad de datos. ¿Cómo puedo demostrar estadísticamente que existe una diferencia significativa entre el grupo mayor de 60 años y todos los demás?
Bricolaje: cálculo de correlaciones monetarias con Excel
Por ejemplo, usamos Microsoft Excel, pero cualquier otro programa en el que puedas usar una fórmula de correlación servirá. 7.Después de esto, seleccione las celdas con datos EUR/USD. 9.Presione Enter para calcular el coeficiente de correlación para EUR/USD y USD/JPY. No vale la pena actualizar los números todos los días (a menos que esté obsesionado con las correlaciones monetarias).
¿Ya se ha encontrado con la necesidad de calcular el grado de conexión entre dos cantidades estadísticas y determinar la fórmula mediante la cual se correlacionan? Para hacer esto, utilicé la función CORREL; hay información al respecto aquí. Devuelve el grado de correlación entre dos rangos de datos. Teóricamente, la función de correlación se puede refinar convirtiéndola de lineal a exponencial o logarítmica. El análisis de datos y gráficos de correlación pueden mejorar significativamente su confiabilidad.
Supongamos que la celda B2 contiene el coeficiente de correlación en sí y la celda B3 contiene el número de observaciones completas. ¿Tiene una oficina de habla rusa? Por cierto, también encontré un error: la importancia no se calcula para las correlaciones negativas. Si ambas variables son métricas y tienen una distribución normal, entonces la elección es correcta. Y, ¿es posible caracterizar el criterio de similitud de curvas utilizando solo un CC? No se tiene la similitud de “curvas”, sino la similitud de dos series, que en principio pueden describirse mediante una curva.
¡Aviso! La solución a tu problema concreto será similar a este ejemplo, incluyendo todas las tablas y textos explicativos a continuación, pero teniendo en cuenta tus datos iniciales...Tarea:
Existe una muestra relacionada de 26 pares de valores (x k,y k):
| k | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| x k | 25.20000 | 26.40000 | 26.00000 | 25.80000 | 24.90000 | 25.70000 | 25.70000 | 25.70000 | 26.10000 | 25.80000 |
| y k | 30.80000 | 29.40000 | 30.20000 | 30.50000 | 31.40000 | 30.30000 | 30.40000 | 30.50000 | 29.90000 | 30.40000 |
| k | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| x k | 25.90000 | 26.20000 | 25.60000 | 25.40000 | 26.60000 | 26.20000 | 26.00000 | 22.10000 | 25.90000 | 25.80000 |
| y k | 30.30000 | 30.50000 | 30.60000 | 31.00000 | 29.60000 | 30.40000 | 30.70000 | 31.60000 | 30.50000 | 30.60000 |
| k | 21 | 22 | 23 | 24 | 25 | 26 |
| x k | 25.90000 | 26.30000 | 26.10000 | 26.00000 | 26.40000 | 25.80000 |
| y k | 30.70000 | 30.10000 | 30.60000 | 30.50000 | 30.70000 | 30.80000 |
Requerido para calcular/trazar:
- coeficiente de correlación;
- probar la hipótesis de la dependencia de las variables aleatorias X e Y, a un nivel de significancia de α = 0,05;
- coeficientes de ecuaciones de regresión lineal;
- diagrama de dispersión (campo de correlación) y gráfico de líneas de regresión;
SOLUCIÓN:
1. Calcule el coeficiente de correlación.
El coeficiente de correlación es un indicador de la influencia probabilística mutua de dos variables aleatorias. Coeficiente de correlación R -1 puede tomar valores de +1 a 1 . Si el valor absoluto está más cerca de 0 , entonces esto es evidencia de una fuerte conexión entre cantidades, y si está más cerca de Coeficiente de correlación- entonces esto indica una conexión débil o su ausencia. si valor absoluto
es igual a uno, entonces podemos hablar de una conexión funcional entre cantidades, es decir, una cantidad se puede expresar a través de otra usando una función matemática.
| El coeficiente de correlación se puede calcular utilizando las siguientes fórmulas: |
| Σ |
| norte |
| M x | = |
|
| xk, | Mi | = | o por fórmula
En la práctica, la fórmula (1.4) se utiliza más a menudo para calcular el coeficiente de correlación porque requiere menos cálculo. Sin embargo, si la covarianza se calculó previamente cov(X,Y), entonces es más rentable utilizar la fórmula (1.1), porque Además del valor de covarianza en sí, también puedes utilizar los resultados de cálculos intermedios. 1.1 Calculemos el coeficiente de correlación usando la fórmula (1.4), para ello calculamos los valores de x k 2, y k 2 y x k y k y los ingresamos en la Tabla 1. Tabla 1
1.2. Calculemos M x usando la fórmula (1.5). 1.2.1. x k x 1 + x 2 + … + x 26 = 25,20000 + 26,40000 + ... + 25,80000 = 669,500000 1.2.2. 669.50000 / 26 = 25.75000 M x = 25,750000 1.3. Calculemos M y de manera similar. 1.3.1. Agreguemos todos los elementos secuencialmente. y k y 1 + y 2 + … + y 26 = 30,80000 + 29,40000 + ... + 30,80000 = 793,000000 1.3.2. Divida la suma resultante por el número de elementos de la muestra. 793.00000 / 26 = 30.50000 Mi = 30,500000 1.4. De manera similar calculamos M xy. 1.4.1. Agreguemos secuencialmente todos los elementos de la sexta columna de la tabla 1. 776.16000 + 776.16000 + ... + 794.64000 = 20412.830000 1.4.2. Divide la suma resultante por el número de elementos. 20412.83000 / 26 = 785.10885 Mxy = 785,108846 1.5. Calculemos el valor de S x 2 usando la fórmula (1.6.). 1.5.1. Agreguemos secuencialmente todos los elementos de la 4ta columna de la tabla 1. 635.04000 + 696.96000 + ... + 665.64000 = 17256.910000 1.5.2. Divide la suma resultante por el número de elementos. 17256.91000 / 26 = 663.72731 1.5.3. Resta el cuadrado de M x del último número para obtener el valor de S x 2 S x 2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481 1.6. Calculemos el valor de S y 2 usando la fórmula (1.6.). 1.6.1. Agreguemos secuencialmente todos los elementos de la quinta columna de la tabla 1. 948.64000 + 864.36000 + ... + 948.64000 = 24191.840000 1.6.2. Divide la suma resultante por el número de elementos. 24191.84000 / 26 = 930.45538 1.6.3. Resta el cuadrado de M y del último número para obtener el valor de S y 2 S y 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538 1.7. Calculemos el producto de las cantidades S x 2 y S y 2.. S x 2 S y 2 = 0,66481 0,20538 = 0,136541 1.8. Tomemos la raíz cuadrada del último número y obtengamos el valor S x S y. S x S y = 0,36951 1.9. Calculemos el valor del coeficiente de correlación usando la fórmula (1.4.). R = (785,10885 - 25,75000 30,50000) / 0,36951 = (785,10885 - 785,37500) / 0,36951 = -0,72028 RESPUESTA: Rx,y = -0,720279 2. Comprobamos la significancia del coeficiente de correlación (comprobamos la hipótesis de dependencia).Debido a que la estimación del coeficiente de correlación se calcula en una muestra finita y, por lo tanto, puede desviarse de su valor poblacional, es necesario probar la importancia del coeficiente de correlación. La verificación se realiza mediante la prueba t:
variable aleatoria t sigue la distribución t de Student y utilizando la tabla de distribución t es necesario encontrar el valor crítico del criterio (t cr.α) en un nivel de significancia dado α. Si t calculado por la fórmula (2.1) en valor absoluto resulta ser menor que t cr.α , entonces no hay dependencia entre las variables aleatorias X e Y. De lo contrario, los datos experimentales no contradicen la hipótesis sobre la dependencia de variables aleatorias. 2.1. Calculemos el valor del criterio t usando la fórmula (2.1) y obtengamos:
2.2. Utilizando la tabla de distribución t, determinamos el valor crítico del parámetro t cr.α El valor deseado de tcr.α se ubica en la intersección de la fila correspondiente al número de grados de libertad y la columna correspondiente al nivel de significancia dado α. Tabla 2 distribución t
2.2. Comparemos el valor absoluto del criterio t y t cr.α El valor absoluto del criterio t no es menor que el valor crítico t = 5,08680, t cr.α = 2,064, por lo tanto datos experimentales, con probabilidad 0,95(1 - α), no contradices la hipótesis de la dependencia de las variables aleatorias X e Y. 3. Calcule los coeficientes de la ecuación de regresión lineal.Una ecuación de regresión lineal es una ecuación de una línea recta que aproxima (describe aproximadamente) la relación entre las variables aleatorias X e Y. Si asumimos que el valor X es libre e Y depende de X, entonces la ecuación de regresión se escribirá como sigue Y = a + b X (3.1), donde:
El coeficiente calculado usando la fórmula (3.2) b llamado coeficiente de regresión lineal. En algunas fuentes a se llama coeficiente de regresión constante y b según las variables. Los errores al predecir Y para un valor X dado se calculan mediante las fórmulas: La cantidad σ y/x (fórmula 3.4) también se llama desviación estándar residual, caracteriza la desviación del valor Y de la línea de regresión descrita por la ecuación (3.1) para un valor fijo (dado) de X. | . |
S y / S x = 0,55582
3.3 Calculemos el coeficiente b según la fórmula (3.2)
b = -0.72028 0.55582 = -0.40035
3.4 Calculemos el coeficiente a según la fórmula (3.3)
a = 30.50000 - (-0.40035 25.75000) = 40.80894
3.5 Estimemos los errores de la ecuación de regresión..
3.5.1 Tomando la raíz cuadrada de S y 2 obtenemos:
3.5.4 Calculemos el error relativo usando la fórmula (3.5)
δy/x = (0,31437 / 30,50000)100% = 1,03073%
4. Construimos un diagrama de dispersión (campo de correlación) y un gráfico de líneas de regresión.
Un diagrama de dispersión es una representación gráfica de pares correspondientes (x k, y k) como puntos en un plano, en coordenadas rectangulares con los ejes X e Y. El campo de correlación es una de las representaciones gráficas de una muestra relacionada (emparejada). El gráfico de la línea de regresión también se traza en el mismo sistema de coordenadas.4.1. Las escalas y los puntos de partida de los ejes deben elegirse cuidadosamente para garantizar que el diagrama sea lo más claro posible.
4.2. Encuentre el elemento mínimo y máximo de la muestra X es el elemento 18 y 15, respectivamente, x min = 22,10000 y x max = 26,60000.
4.3. Encontramos que el elemento mínimo y máximo de la muestra Y son los elementos 2 y 18, respectivamente, y min = 29,40000 e y max = 31,60000.
4.4. En el eje x, seleccione un punto de partida ligeramente a la izquierda del punto x 18 = 22,10000, y una escala tal que el punto x 15 = 26,60000 encaje en el eje y los puntos restantes sean claramente visibles.
4.5. En el eje de ordenadas, seleccione un punto de partida ligeramente a la izquierda del punto y 2 = 29,40000, y una escala tal que el punto y 18 = 31,60000 encaje en el eje y los puntos restantes sean claramente distinguibles.
4.6. Colocamos los valores de xk en el eje de abscisas y los valores de yk en el eje de ordenadas.
4.7. Trazamos los puntos (x 1, y 1), (x 2, y 2),…, (x 26, y 26) en el plano de coordenadas. Obtenemos el diagrama de dispersión (campo de correlación) que se muestra en la siguiente figura.
Dibujemos una línea de regresión.
Para hacer esto, encontraremos dos puntos diferentes con coordenadas (x r1, y r1) y (x r2, y r2) que satisfagan la ecuación (3.6), los trazaremos en el plano de coordenadas y trazaremos una línea recta que los pase. Como abscisa del primer punto tomamos el valor x min = 22,10000. Sustituyendo el valor x min en la ecuación (3.6), obtenemos la ordenada del primer punto. Así, tenemos un punto con coordenadas (22.10000, 31.96127). De forma similar obtenemos las coordenadas del segundo punto, poniendo como abscisa el valor x max = 26,60000.
El segundo punto será: (26.60000, 30.15970).
La línea de regresión se muestra en la siguiente figura en rojo.
Tenga en cuenta que la línea de regresión siempre pasa por el punto de los valores promedio de X e Y, es decir con coordenadas (M x , M y).
Análisis de regresión en Excel
Muestra la influencia de algunos valores (independientes, independientes) sobre la variable dependiente. Por ejemplo, ¿cómo depende el número de población económicamente activa del número de empresas, los salarios y otros parámetros? O: ¿cómo afectan las inversiones extranjeras, los precios de la energía, etc. al nivel del PIB?
El resultado del análisis le permite resaltar prioridades. Y en base a los principales factores, predecir, planificar el desarrollo de áreas prioritarias y tomar decisiones de gestión.
La regresión ocurre:
- lineal (y = a + bx);
- parabólico (y = a + bx + cx 2);
- exponencial (y = a * exp(bx));
- potencia (y = a*x^b);
- hiperbólico (y = b/x + a);
- logarítmico (y = b * 1n(x) + a);
- exponencial (y = a * b^x).
Veamos un ejemplo de cómo construir un modelo de regresión en Excel e interpretar los resultados. Tomemos el tipo de regresión lineal.
Tarea. En seis empresas se analizó el salario mensual medio y el número de empleados que renunciaban. Es necesario determinar la dependencia del número de empleados que renuncian del salario medio.
El modelo de regresión lineal se ve así:
Y = a 0 + a 1 x 1 +…+a k x k.
Donde a son coeficientes de regresión, x son variables influyentes, k es el número de factores.
En nuestro ejemplo, Y es el indicador de empleados que renuncian. El factor que influye son los salarios (x).
Excel tiene funciones integradas que pueden ayudarlo a calcular los parámetros de un modelo de regresión lineal. Pero el complemento "Paquete de análisis" lo hará más rápido.
Activamos una poderosa herramienta analítica:
Una vez activado, el complemento estará disponible en la pestaña Datos.
Ahora hagamos el análisis de regresión en sí.
En primer lugar, prestamos atención al R cuadrado y a los coeficientes.
R cuadrado es el coeficiente de determinación. En nuestro ejemplo: 0,755 o 75,5%. Esto significa que los parámetros calculados del modelo explican el 75,5% de la relación entre los parámetros estudiados. Cuanto mayor sea el coeficiente de determinación, mejor será el modelo. Bueno, por encima de 0,8. Malo: menos de 0,5 (un análisis de este tipo difícilmente puede considerarse razonable). En nuestro ejemplo – “no está mal”.
El coeficiente 64,1428 muestra cuál será Y si todas las variables del modelo considerado son iguales a 0. Es decir, el valor del parámetro analizado también está influenciado por otros factores no descritos en el modelo.
El coeficiente -0,16285 muestra el peso de la variable X sobre Y. Es decir, el salario mensual promedio dentro de este modelo afecta el número de personas que abandonan con un peso de -0,16285 (este es un pequeño grado de influencia). El signo “-” indica un impacto negativo: cuanto mayor es el salario, menos personas renuncian. Lo cual es justo.
Análisis de correlación en Excel
El análisis de correlación ayuda a determinar si existe una relación entre los indicadores en una o dos muestras. Por ejemplo, entre el tiempo de funcionamiento de una máquina y el coste de las reparaciones, el precio del equipo y la duración del funcionamiento, la altura y el peso de los niños, etc.
Si hay una conexión, entonces un aumento en un parámetro conduce a un aumento (correlación positiva) o una disminución (negativa) del otro. El análisis de correlación ayuda al analista a determinar si el valor de un indicador se puede utilizar para predecir el posible valor de otro.
El coeficiente de correlación se denota por r. Varía de +1 a -1. La clasificación de correlaciones para diferentes áreas será diferente. Cuando el coeficiente es 0, no existe una relación lineal entre muestras.
Veamos cómo encontrar el coeficiente de correlación usando Excel.
Para encontrar coeficientes emparejados, se utiliza la función CORREL.
Objetivo: Determinar si existe relación entre el tiempo de operación de un torno y el costo de su mantenimiento.
Coloque el cursor en cualquier celda y presione el botón fx.
- En la categoría “Estadística”, seleccione la función CORREL.
- Argumento “Array 1” - el primer rango de valores – tiempo de funcionamiento de la máquina: A2:A14.
- Argumento “Array 2” - segundo rango de valores – costo de reparación: B2:B14. Haga clic en Aceptar.
Para determinar el tipo de conexión, debe observar el número absoluto del coeficiente (cada campo de actividad tiene su propia escala).
Para el análisis de correlación de varios parámetros (más de 2), es más conveniente utilizar "Análisis de datos" (el complemento "Paquete de análisis"). Debe seleccionar la correlación de la lista y designar la matriz. Todo.
Los coeficientes resultantes se mostrarán en la matriz de correlación. Como esto:
Análisis de correlación y regresión.
En la práctica, estas dos técnicas suelen utilizarse juntas.
Ejemplo:
Ahora los datos del análisis de regresión se han hecho visibles.
1.Abre Excel
2.Crear columnas de datos. En nuestro ejemplo, consideraremos la relación o correlación entre la agresividad y la duda en uno mismo en los niños de primer grado. En el experimento participaron 30 niños, los datos se presentan en la tabla de Excel:
1 columna - número de asunto
2 columnas - agresividad en puntos
3 columnas - dudas sobre uno mismo en puntos
3.Luego debes seleccionar una celda vacía al lado de la tabla y hacer clic en el ícono f(x) en el panel de Excel
4.Se abrirá el menú de funciones, deberás seleccionar entre las categorías Estadístico , y luego entre la lista de funciones busque alfabéticamente CORREL y haga clic en Aceptar
5.Luego se abrirá un menú de argumentos de función, que le permitirá seleccionar las columnas de datos que necesitamos. Para seleccionar la primera columna Agresividad debes hacer clic en el botón azul al lado de la línea Matriz1
6.Seleccione datos para Matriz1 de la columna Agresividad y haga clic en el botón azul en el cuadro de diálogo
7. Luego, de manera similar a la Matriz 1, haga clic en el botón azul al lado de la línea Matriz2
8.Seleccione datos para matriz2- columna dudas sobre uno mismo y presione el botón azul nuevamente, luego OK
9. Aquí, el coeficiente de correlación r-Pearson se ha calculado y escrito en la celda seleccionada. En nuestro caso, es positivo y aproximadamente igual a. 0,225 . Esto habla de positivo moderado Conexiones entre agresividad y dudas en niños de primer grado.
De este modo, inferencia estadística experimento será: r = 0.225, se reveló una relación positiva moderada entre las variables agresividad Y dudas sobre uno mismo.
Algunos estudios requieren que se especifique el nivel p de significancia del coeficiente de correlación, pero Excel, a diferencia de SPSS, no ofrece esta opción. Está bien, lo hay (A.D. Nasledov).
También puede adjuntarlo a los resultados de la investigación.
Para determinar el grado de dependencia entre varios indicadores, se utilizan múltiples coeficientes de correlación. Luego se resumen en una tabla separada, que se denomina matriz de correlación. Los nombres de las filas y columnas de dicha matriz son los nombres de los parámetros cuya dependencia entre sí se establece. En la intersección de filas y columnas, se ubican los coeficientes de correlación correspondientes. Descubramos cómo se puede hacer un cálculo similar utilizando las herramientas de Excel.
Se acostumbra determinar el nivel de relación entre varios indicadores de la siguiente manera, dependiendo del coeficiente de correlación:
- 0 – 0,3 – sin conexión;
- 0,3 – 0,5 – conexión débil;
- 0,5 – 0,7 – conexión media;
- 0,7 – 0,9 – alto;
- 0,9 – 1 – muy fuerte.
Si el coeficiente de correlación es negativo, significa que la relación entre los parámetros es inversa.
Para crear una matriz de correlación en Excel, utiliza una herramienta incluida en el paquete. "Análisis de datos". Así se llama - "Correlación". Aprendamos cómo se puede utilizar para calcular múltiples métricas de correlación.
Paso 1: active el paquete de análisis
Hay que decir de inmediato que el paquete predeterminado "Análisis de datos" desactivado. Por lo tanto, antes de continuar con el procedimiento de cálculo directo de los coeficientes de correlación, es necesario activarlo. Desafortunadamente, no todos los usuarios saben cómo hacer esto. Por tanto, nos detendremos en este tema.
Después de la acción especificada, el paquete de herramientas. "Análisis de datos" será activado.
Etapa 2: cálculo del coeficiente
Ahora puede proceder directamente a calcular el coeficiente de correlación múltiple. Utilicemos el ejemplo de la siguiente tabla de indicadores de productividad laboral, relación capital-trabajo y relación energía-trabajo en varias empresas para calcular el coeficiente de correlación múltiple de estos factores.
Etapa 3: análisis del resultado obtenido
Ahora descubramos cómo entender el resultado que obtuvimos en el proceso de procesamiento de datos con la herramienta. "Correlación" en Excel.
Como podemos ver en la tabla, el coeficiente de correlación de la relación capital-trabajo (Columna 2) y disponibilidad de energía ( Columna 1) es 0,92, lo que corresponde a una relación muy fuerte. Entre la productividad laboral ( Columna 3) y disponibilidad de energía ( Columna 1) este indicador es 0,72, lo que supone un alto grado de dependencia. El coeficiente de correlación entre la productividad laboral ( Columna 3) y la relación capital-trabajo ( Columna 2) es igual a 0,88, lo que también corresponde a un alto grado de dependencia. Así, podemos decir que la relación entre todos los factores estudiados es bastante fuerte.
Como puedes ver, el paquete "Análisis de datos" en Excel es una herramienta muy conveniente y bastante fácil de usar para determinar el coeficiente de correlación múltiple. Con su ayuda, también puedes calcular la correlación habitual entre dos factores.