Cómo encontrar la correlación en Excel

El coeficiente de correlación de Spearman, rS, fue la primera prueba no paramétrica basada en rangos. Para una muestra de individuos, cada uno medido en dos variables en Excel, la idea es clasificar cada puntaje dentro de su propia variable. Luego, para cada individuo, reste un rango del otro. Si la correlación es perfecta (en la dirección positiva), todas las diferencias son cero.

La siguiente imagen muestra un ejemplo. Un psicólogo industrial calificó la sociabilidad de 20 empleados de FarDrate Timepiece Corporation. La escala varió de 1 (menos sociable) a 100 (más sociable). Cada empleado de FarDrate también calificó su satisfacción laboral en una escala de 1 (menor satisfacción) a 80 (mayor satisfacción). La hipótesis nula es que la sociabilidad no se correlaciona con la satisfacción laboral. La hipótesis alternativa es que estas dos variables están correlacionadas.

Los datos están en las columnas B y C, y los rangos están en las columnas E y F. Las diferencias entre cada par de rangos están en la columna G.

RS de Spearman.

La formula es

donde d es una diferencia entre pares. Como es el caso con el coeficiente de correlación regular, si la hipótesis nula es cierta, el valor de rS debería estar alrededor de cero.

Para calcular los rangos en la columna E, escriba

= RANK.AVG (B2, $ B $ 2: $ B $ 21,1)

en E2 y se rellena automáticamente. Para los rangos en la columna E, escriba

= RANK.AVG (C2, $ C $ 2: $ C $ 21,1)

en F2 y se rellena automáticamente.

No es necesario que escriba una fórmula complicada de Excel en la celda J4 para calcular el coeficiente de correlación. ¿Por qué? Porque Excel y las estadísticas matemáticas se unen para una gran sorpresa: todo lo que tienes que hacer es escribir

= CORREL (E2: E21, F2: F21)

en J4. Eso es todo al respecto. El uso de CORREL en los rangos da la misma respuesta que la fórmula anterior. (Por lo tanto, no es realmente necesario calcular las diferencias de rango entre pares en la columna G.)

Calcular

N es el número de pares y la prueba tiene N-2 grados de libertad.

Puede definir Number_of_pairs como el nombre del valor en la celda I2. Así que escribe

= J4 * SQRT (Número_de_pares-2) / SQRT (1-J4 ^ 2)

en J6 y

= DISTR.T.2T (J6, Número_de_pares-2)

en J7. Puede utilizar la función de distribución t de dos colas si no conoce la dirección de la correlación de antemano. Y una vez más, el valor p bajo le dice que rechace la hipótesis nula.