Análise de correlacións en Excel. Un exemplo de realización dunha análise de correlación

A análise de correlación é un método de investigación común que se emprega para determinar o nivel de dependencia do 1o valor sobre o 2o. A folla de cálculo conta cunha ferramenta especial que permite implementar este tipo de investigacións.

A esencia da análise de correlación

É necesario determinar a relación entre dúas cantidades diferentes. Noutras palabras, revela en que dirección (menor/maior) cambia o valor dependendo dos cambios no segundo.

Finalidade da análise de correlacións

A dependencia establécese cando se inicia a identificación do coeficiente de correlación. Este método difire da análise de regresión, xa que só hai un indicador calculado mediante correlación. O intervalo cambia de +1 a -1. Se é positivo, entón un aumento do primeiro valor contribúe a un aumento do segundo. Se é negativo, un aumento do 2º valor contribúe a unha diminución do 1º. Canto maior sexa o coeficiente, o valor máis forte afectará ao segundo.

Importante! No coeficiente 0, non hai relación entre as cantidades.

Cálculo do coeficiente de correlación

Imos analizar o cálculo en varias mostras. Por exemplo, hai datos tabulares, onde o gasto en promoción publicitaria e o volume de vendas descríbense por meses en columnas separadas. A partir da táboa, coñeceremos o nivel de dependencia do volume de vendas do diñeiro gastado en promoción publicitaria.

Método 1: Determinación da correlación mediante o asistente de funcións

CORREL - unha función que lle permite implementar unha análise de correlación. Forma xeral - CORREL(masivo1;masivo2). Instrucións detalladas:

1. É necesario seleccionar a cela na que está previsto mostrar o resultado do cálculo. Fai clic en "Inserir función" situado á esquerda do campo de texto para introducir a fórmula.

2. Ábrese o asistente de funcións. Aquí tes que atopar CORREL, fai clic nel e despois en "Aceptar".

3. Ábrese a xanela de argumentos. Na liña "Matriz1" debes introducir as coordenadas dos intervalos do 1º dos valores. Neste exemplo, esta é a columna Valor de vendas. Só tes que seleccionar todas as celas que están nesta columna. Do mesmo xeito, cómpre engadir as coordenadas da segunda columna á liña "Matriz2". No noso exemplo, esta é a columna Custos de publicidade.

4. Despois de introducir todos os intervalos, fai clic no botón "Aceptar".

O coeficiente mostrouse na cela que se indicaba ao comezo das nosas accións. O resultado obtido é 0,97. Este indicador reflicte a alta dependencia do primeiro valor do segundo.

Método 2: Calcula a correlación usando o ToolPak de análise

Hai outro método para determinar a correlación. Aquí utilízase unha das funcións que se atopan no paquete de análise. Antes de usalo, cómpre activar a ferramenta. Instrucións detalladas:

1. Vaia á sección "Ficheiro".

2. Abrirase unha nova xanela, na que tes que facer clic na sección "Configuración".
3. Fai clic en "Complementos".
4. Atopamos o elemento "Xestión" na parte inferior. Aquí ten que seleccionar "Complementos de Excel" no menú contextual e facer clic en "Aceptar".

5. Abriuse unha ventá de complementos especiales. Coloque unha marca de verificación xunto ao elemento "Paquete de análise". Facemos clic en "Aceptar".
6. A activación foi exitosa. Agora imos a Datos. Apareceu o bloque "Análise", no que cómpre facer clic en "Análise de datos".
7. Na nova xanela que aparece, seleccione o elemento "Correlación" e prema en "Aceptar".

8. A xanela de configuración da análise apareceu na pantalla. Na liña "Intervalo de entrada" é necesario introducir o intervalo de absolutamente todas as columnas que participan na análise. Neste exemplo, estas son as columnas "Valor de vendas" e "Custos de publicidade". A configuración de visualización de saída está configurada inicialmente en Nova folla de traballo, o que significa que os resultados mostraranse nunha folla diferente. Opcionalmente, pode cambiar a localización de saída do resultado. Despois de facer todos os axustes, faga clic en "Aceptar".

As puntuacións finais están fóra. O resultado é o mesmo que no primeiro método: 0,97.

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

Para identificar o nivel de dependencia de varias cantidades utilízanse múltiples coeficientes. No futuro, os resultados resúmense nunha táboa separada, chamada matriz de correlación.

Guía detallada:

1. Na sección "Datos", atopamos o xa coñecido bloque "Análise" e fai clic en "Análise de datos".

2. Na xanela que aparece, fai clic no elemento "Correlación" e fai clic en "Aceptar".
3. Na liña "Intervalo de entrada" diriximos o intervalo de tres ou máis columnas da táboa de orixe. O rango pódese introducir manualmente ou simplemente seleccionalo co LMB, e aparecerá automaticamente na liña desexada. En "Agrupación" seleccione o método de agrupación adecuado. En "Parámetro de saída" especifica a localización onde se mostrarán os resultados da correlación. Facemos clic en "Aceptar".

4. Listo! Construíuse a matriz de correlación.

Coeficiente de correlación de pares en Excel

Imos descubrir como debuxar correctamente o coeficiente de correlación de pares nunha folla de cálculo de Excel.

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

Por exemplo, tes valores x e y.

X é a variable dependente e y é a independente. É necesario atopar a dirección e a forza da relación entre estes indicadores. Instrución paso a paso:

1. Imos atopar os valores medios usando a función CORAZÓN.

2. Calculemos cada un х и xavg, у и avg usando o operador «-».

3. Multiplicamos as diferenzas calculadas.

4. Calculamos a suma dos indicadores desta columna. O numerador é o resultado atopado.

5. Calcula os denominadores da diferenza х и media x, y и y-medio. Para iso, realizaremos o cadrado.

6. Usando a función AUTOSUMA, atopa os indicadores nas columnas resultantes. Facemos multiplicación. Usando a función ROOT cadrado o resultado.

7. Calculamos o cociente utilizando os valores do denominador e do numerador.

8. CORREL é unha función integrada que permite evitar cálculos complexos. Imos ao "Asistente de funcións", seleccionamos CORREL e especificamos as matrices de indicadores х и у. Construímos unha gráfica que mostra os valores obtidos.

Matriz de coeficientes de correlación por pares en Excel

Analizamos como calcular os coeficientes de matrices pareadas. Por exemplo, hai unha matriz de catro variables.

Instrución paso a paso:

1. Imos á "Análise de datos", situada no bloque "Análise" da pestana "Datos". Seleccione Correlación na lista que aparece.
2. Establecemos todos os axustes necesarios. "Intervalo de entrada": o intervalo das catro columnas. "Intervalo de saída" - o lugar onde queremos mostrar os totais. Facemos clic no botón "Aceptar".
3. Construíuse unha matriz de correlación no lugar elixido. Cada intersección dunha fila e dunha columna é un coeficiente de correlación. O número 1 móstrase cando as coordenadas coinciden.

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

CORREL: función usada para calcular o coeficiente de correlación entre 2 matrices. Vexamos catro exemplos de todas as habilidades desta función.

Exemplos de uso da función CORREL en Excel

Primeiro exemplo. Hai unha placa con información sobre os salarios medios dos empregados da empresa ao longo de once anos e o tipo de cambio de $. É necesario identificar a relación entre estas dúas cantidades. A táboa ten este aspecto:

O algoritmo de cálculo ten o seguinte aspecto:

A puntuación mostrada é próxima a 1. Resultado:

Determinación do coeficiente de correlación do impacto das accións sobre o resultado

Segundo exemplo. Dous licitadores acudiron a dúas axencias diferentes para pedir axuda cunha promoción de quince días. Todos os días realizouse unha enquisa social, na que se determinaba o grao de apoio a cada solicitante. Calquera entrevistado podería escoller un dos dous candidatos ou opoñerse a todos. É necesario determinar canto influíu cada promoción publicitaria no grao de apoio aos solicitantes, que empresa é máis eficiente.

Usando as seguintes fórmulas, calculamos o coeficiente de correlación:

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

Resultados:

Dos resultados obtidos despréndese que o grao de apoio ao 1o solicitante aumentaba con cada día de promoción publicitaria, polo tanto, o coeficiente de correlación achégase ao 1. Cando se puxo en marcha a publicidade, o outro solicitante tiña un gran número de confianza, e para 5 días houbo unha tendencia positiva. Entón o grao de confianza diminuíu e no décimo quinto día caeu por debaixo dos indicadores iniciais. As puntuacións baixas suxiren que a promoción ten un impacto negativo no apoio. Non esqueza que outros factores concomitantes que non se consideran en forma de táboa tamén poden afectar aos indicadores.

Análise da popularidade do contido por correlación de visualizacións de vídeos e repostos

Terceiro exemplo. Unha persoa para promocionar os seus propios vídeos no hospedaxe de vídeos de YouTube usa as redes sociais para anunciar a canle. Observa que existe algunha relación entre o número de repostos nas redes sociais e o número de visualizacións na canle. É posible prever o rendemento futuro usando ferramentas de follas de cálculo? É necesario identificar a razoabilidade de aplicar a ecuación de regresión lineal para predecir o número de visualizacións de vídeo dependendo do número de republicacións. Táboa con valores:

Agora é necesario determinar a presenza dunha relación entre 2 indicadores segundo a seguinte fórmula:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Relación directa forte”;”Relación inversa forte”);”Relación débil ou nula”)' class='fórmula'>

Se o coeficiente resultante é superior a 0,7, entón é máis apropiado utilizar a función de regresión lineal. Neste exemplo, facemos:

Agora estamos construíndo un gráfico:

Aplicamos esta ecuación para determinar o número de visualizacións en 200, 500 e 1000 accións: =9,2937*D4-206,12. Obtemos os seguintes resultados:

función PREVISIÓN permite determinar o número de visualizacións no momento, se houbo, por exemplo, duascentas cincuenta republicacións. Aplicamos: 0,7;PREDICCIÓN(D7;B3:B8;A3:A8);"Os valores non están relacionados")' class='fórmula'>. Obtemos os seguintes resultados:

Características do uso da función CORREL en Excel

Esta función ten as seguintes características:

1. Non se teñen en conta as celas baleiras.
2. Non se teñen en conta as celas que conteñen información de tipo booleano e de texto.
3. A dobre negación "-" úsase para dar conta de valores lóxicos en forma de números.
4. O número de celas das matrices estudadas debe coincidir, se non, amosarase a mensaxe #N/A.

Valoración da significación estatística do coeficiente de correlación

Ao probar a significación dun coeficiente de correlación, a hipótese nula é que o indicador ten un valor de 0, mentres que a alternativa non. Para a verificación úsase a seguinte fórmula:

Conclusión

A análise de correlacións nunha folla de cálculo é un proceso sinxelo e automatizado. Para realizalo só precisa saber onde se atopan as ferramentas necesarias e como activalas a través da configuración do programa.