Análisis de correlación en Excel. Un ejemplo de cómo realizar un análisis de correlación

El análisis de correlación es un método de investigación común utilizado para determinar el nivel de dependencia del primer valor con respecto al segundo. La hoja de cálculo tiene una herramienta especial que le permite implementar este tipo de investigación.

La esencia del análisis de correlación.

Es necesario determinar la relación entre dos cantidades diferentes. En otras palabras, revela en qué dirección (menor/mayor) cambia el valor dependiendo de los cambios en el segundo.

Propósito del análisis de correlación

La dependencia se establece cuando comienza la identificación del coeficiente de correlación. Este método difiere del análisis de regresión, ya que solo hay un indicador calculado mediante la correlación. El intervalo cambia de +1 a -1. Si es positivo, entonces un aumento en el primer valor contribuye a un aumento en el segundo. Si es negativo, un aumento en el primer valor contribuye a una disminución en el segundo. Cuanto mayor sea el coeficiente, más fuerte un valor afecta al segundo.

¡Importante! En el coeficiente 0, no hay relación entre las cantidades.

Cálculo del coeficiente de correlación

Analicemos el cálculo en varias muestras. Por ejemplo, hay datos tabulares, donde el gasto en promoción publicitaria y el volumen de ventas se describen por meses en columnas separadas. Según la tabla, descubriremos el nivel de dependencia del volumen de ventas del dinero gastado en promoción publicitaria.

Método 1: determinar la correlación a través del asistente de funciones

CORREL: una función que le permite implementar un análisis de correlación. forma general - CORREL(masivo1;masivo2). Instrucciones detalladas:

1. Es necesario seleccionar la celda en la que se planea mostrar el resultado del cálculo. Haga clic en "Insertar función" ubicado a la izquierda del campo de texto para ingresar la fórmula.

2. Se abre el asistente de funciones. Aquí tienes que encontrar CORREL, haga clic en él, luego en "OK".

3. Se abre la ventana de argumentos. En la línea “Array1” debe ingresar las coordenadas de los intervalos del 1° de los valores. En este ejemplo, esta es la columna Valor de ventas. Solo necesita seleccionar todas las celdas que están en esta columna. Del mismo modo, debe agregar las coordenadas de la segunda columna a la línea "Array2". En nuestro ejemplo, esta es la columna Costos de publicidad.

4. Después de ingresar todos los rangos, haga clic en el botón "Aceptar".

El coeficiente se mostró en la celda que se indicó al comienzo de nuestras acciones. El resultado obtenido es 0,97. Este indicador refleja la alta dependencia del primer valor con respecto al segundo.

Método 2: Calcular la correlación usando el paquete de herramientas de análisis

Hay otro método para determinar la correlación. Aquí se utiliza una de las funciones que se encuentran en el paquete de análisis. Antes de usarlo, debe activar la herramienta. Instrucciones detalladas:

1. Vaya a la sección "Archivo".

2. Se abrirá una nueva ventana, en la que debe hacer clic en la sección "Configuración".
3. Haga clic en "Complementos".
4. Encontramos el elemento “Gestión” en la parte inferior. Aquí debe seleccionar "Complementos de Excel" en el menú contextual y hacer clic en "Aceptar".

5. Se ha abierto una ventana de complementos especiales. Coloque una marca de verificación junto al elemento "Paquete de análisis". Hacemos clic en “Aceptar”.
6. La activación fue exitosa. Ahora vamos a Datos. Apareció el bloque "Análisis", en el que debe hacer clic en "Análisis de datos".
7. En la nueva ventana que aparece, seleccione el elemento "Correlación" y haga clic en "Aceptar".

8. La ventana de configuración de análisis apareció en la pantalla. En la línea "Intervalo de entrada" es necesario ingresar el rango de absolutamente todas las columnas que participan en el análisis. En este ejemplo, estas son las columnas "Valor de ventas" y "Costos de publicidad". La configuración de visualización de salida se establece inicialmente en Nueva hoja de trabajo, lo que significa que los resultados se mostrarán en una hoja diferente. Opcionalmente, puede cambiar la ubicación de salida del resultado. Después de realizar todos los ajustes, haga clic en "Aceptar".

Los puntajes finales están fuera. El resultado es el mismo que en el primer método: 0,97.

Definición y cálculo de coeficiente de correlación múltiple en MS Excel

Para identificar el nivel de dependencia de varias cantidades, se utilizan coeficientes múltiples. En el futuro, los resultados se resumen en una tabla separada, denominada matriz de correlación.

Guía detallada:

1. En la sección "Datos", encontramos el ya conocido bloque "Análisis" y hacemos clic en "Análisis de datos".

2. En la ventana que aparece, haga clic en el elemento "Correlación" y haga clic en "Aceptar".
3. En la línea "Intervalo de entrada" manejamos el intervalo para tres o más columnas de la tabla de origen. El rango se puede ingresar manualmente o simplemente seleccionarlo con el LMB, y automáticamente aparecerá en la línea deseada. En "Agrupación", seleccione el método de agrupación adecuado. En "Parámetro de salida", especifica la ubicación donde se mostrarán los resultados de la correlación. Hacemos clic en “Aceptar”.

4. ¡Listo! Se construyó la matriz de correlación.

Coeficiente de correlación de pares en Excel

Averigüemos cómo dibujar correctamente el coeficiente de correlación de pares en una hoja de cálculo de Excel.

Cálculo del coeficiente de correlación de pares en Excel

Por ejemplo, tiene valores de x e y.

X es la variable dependiente e y es la independiente. Es necesario encontrar la dirección y la fuerza de la relación entre estos indicadores. Instrucciones paso a paso:

1. Encontremos los valores promedio usando la función CORAZÓN.

2. Calculemos cada uno х и xpromedio, у и avg utilizando el operador «-».

3. Multiplicamos las diferencias calculadas.

4. Calculamos la suma de los indicadores en esta columna. El numerador es el resultado encontrado.

5. Calcular los denominadores de la diferencia. х и x-promedio, y и y-medio. Para ello, realizaremos el escuadrado.

6. Usando la función AUTOSUMMA, busque los indicadores en las columnas resultantes. Hacemos la multiplicación. Usando la función RAÍZ cuadrar el resultado.

7. Calculamos el cociente utilizando los valores del denominador y el numerador.

8. CORREL es una función integrada que le permite evitar cálculos complejos. Vamos al "Asistente de funciones", seleccionamos CORREL y especificamos las matrices de indicadores х и у. Construimos un gráfico que muestra los valores obtenidos.

Matriz de coeficientes de correlación por pares en Excel

Analicemos cómo calcular los coeficientes de matrices apareadas. Por ejemplo, hay una matriz de cuatro variables.

Instrucción paso a paso:

1. Nos dirigimos al “Análisis de datos”, ubicado en el bloque “Análisis” de la pestaña “Datos”. Seleccione Correlación de la lista que aparece.
2. Establecemos todos los ajustes necesarios. “Intervalo de entrada”: el intervalo de las cuatro columnas. “Intervalo de salida”: el lugar donde queremos mostrar los totales. Damos clic en el botón “Aceptar”.
3. Se construyó una matriz de correlación en el lugar elegido. Cada intersección de una fila y una columna es un coeficiente de correlación. El número 1 se muestra cuando las coordenadas coinciden.

Función CORREL para determinar relación y correlación en Excel

CORREL: una función utilizada para calcular el coeficiente de correlación entre 2 matrices. Veamos cuatro ejemplos de todas las capacidades de esta función.

Ejemplos de uso de la función CORREL en Excel

Primer ejemplo. Hay una placa con información sobre los salarios promedio de los empleados de la empresa en el transcurso de once años y la tasa de cambio de $. Es necesario identificar la relación entre estas dos cantidades. La tabla se ve así:

El algoritmo de cálculo se ve así:

La puntuación mostrada es cercana a 1. Resultado:

Determinación del coeficiente de correlación del impacto de las acciones en el resultado.

Segundo ejemplo. Dos postores se acercaron a dos agencias diferentes en busca de ayuda con una promoción de quince días. Todos los días se realizaba una encuesta social, que determinaba el grado de apoyo a cada postulante. Cualquier entrevistado podía elegir a uno de los dos postulantes u oponerse a todos. Es necesario determinar cuánto influyó cada promoción publicitaria en el grado de apoyo a los postulantes, qué empresa es más eficiente.

Usando las fórmulas a continuación, calculamos el coeficiente de correlación:

• =CORREL(A3:A17;B3:B17).
• =CORRECCIÓN(A3:A17;C3:C17).

Resultados:

De los resultados obtenidos, queda claro que el grado de apoyo para el 1er solicitante aumentaba con cada día de promoción publicitaria, por lo tanto, el coeficiente de correlación se aproxima a 1. Cuando se lanzó la publicidad, el otro solicitante tenía un gran número de confianza, y por 5 días hubo una tendencia positiva. Luego el grado de confianza disminuyó y al decimoquinto día cayó por debajo de los indicadores iniciales. Las puntuaciones bajas sugieren que la promoción ha tenido un impacto negativo en el apoyo. No olvide que otros factores concomitantes que no se consideran en forma tabular también podrían afectar los indicadores.

Análisis de la popularidad del contenido por correlación de vistas de video y reenvíos

Tercer ejemplo. Una persona para promocionar sus propios videos en el alojamiento de videos de YouTube utiliza las redes sociales para publicitar el canal. Se da cuenta de que existe cierta relación entre la cantidad de reposts en las redes sociales y la cantidad de vistas en el canal. ¿Es posible predecir el rendimiento futuro utilizando herramientas de hoja de cálculo? Es necesario identificar la razonabilidad de aplicar la ecuación de regresión lineal para predecir el número de visualizaciones de vídeo en función del número de reposts. Tabla con valores:

Ahora es necesario determinar la presencia de una relación entre 2 indicadores según la siguiente fórmula:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Relación directa fuerte”;”Relación inversa fuerte”);”Relación débil o sin relación”)' class='formula'>

Si el coeficiente resultante es superior a 0,7, entonces es más adecuado utilizar la función de regresión lineal. En este ejemplo, hacemos:

Ahora estamos construyendo un gráfico:

Aplicamos esta ecuación para determinar el número de visualizaciones en 200, 500 y 1000 compartidos: =9,2937*D4-206,12. Obtenemos los siguientes resultados:

Función FORECAST le permite determinar el número de vistas en este momento, si hubo, por ejemplo, doscientos cincuenta reenvíos. Aplicamos: 0,7;PREDICCIÓN(D7;B3:B8;A3:A8);”Los valores no están relacionados”)' class='formula'>. Obtenemos los siguientes resultados:

Características del uso de la función CORREL en Excel

Esta función tiene las siguientes características:

1. Las celdas vacías no se tienen en cuenta.
2. Las celdas que contienen información de tipo booleano y de texto no se tienen en cuenta.
3. La doble negación "-" se usa para dar cuenta de valores lógicos en forma de números.
4. El número de celdas en las matrices estudiadas debe coincidir; de lo contrario, se mostrará el mensaje #N/A.

Evaluación de la significación estadística del coeficiente de correlación

Al probar la significación de un coeficiente de correlación, la hipótesis nula es que el indicador tiene un valor de 0, mientras que la alternativa no. La siguiente fórmula se utiliza para la verificación:

Conclusión

El análisis de correlación en una hoja de cálculo es un proceso simple y automatizado. Para realizarlo, solo necesita saber dónde se encuentran las herramientas necesarias y cómo activarlas a través de la configuración del programa.