Análise de correlação em Excel. Um exemplo de execução de uma análise de correlação

A análise de correlação é um método de pesquisa comum usado para determinar o nível de dependência do 1º valor em relação ao 2º. A planilha possui uma ferramenta especial que permite implementar esse tipo de pesquisa.

A essência da análise de correlação

É necessário determinar a relação entre duas quantidades diferentes. Em outras palavras, revela em qual direção (menor/maior) o valor muda dependendo das mudanças no segundo.

Objetivo da análise de correlação

A dependência é estabelecida quando se inicia a identificação do coeficiente de correlação. Esse método difere da análise de regressão, pois há apenas um indicador calculado por correlação. O intervalo muda de +1 para -1. Se for positivo, então um aumento no primeiro valor contribui para um aumento no 2º. Se negativo, então um aumento no 1º valor contribui para uma diminuição no 2º. Quanto maior o coeficiente, mais forte um valor afeta o segundo.

Importante! No coeficiente 0, não há relação entre as quantidades.

Cálculo do coeficiente de correlação

Vamos analisar o cálculo em várias amostras. Por exemplo, há dados tabulares, onde os gastos com promoção publicitária e volume de vendas são descritos por meses em colunas separadas. Com base na tabela, descobriremos o nível de dependência do volume de vendas em relação ao dinheiro gasto na promoção publicitária.

Método 1: Determinando a correlação por meio do Assistente de funções

CORREL – uma função que permite implementar uma análise de correlação. Forma geral - CORREL(massiv1;massiv2). Instruções detalhadas:

1. É necessário selecionar a célula na qual se planeja exibir o resultado do cálculo. Clique em “Inserir Função” localizado à esquerda do campo de texto para inserir a fórmula.

2. O Assistente de Função é aberto. Aqui você precisa encontrar CORREL, clique nele e depois em “OK”.

3. A janela de argumentos é aberta. Na linha “Array1” você deve inserir as coordenadas dos intervalos do 1º dos valores. Neste exemplo, esta é a coluna Valor de vendas. Você só precisa selecionar todas as células que estão nesta coluna. Da mesma forma, você precisa adicionar as coordenadas da segunda coluna à linha “Array2”. Em nosso exemplo, esta é a coluna Custos de publicidade.

4. Após inserir todos os intervalos, clique no botão “OK”.

O coeficiente foi exibido na célula indicada no início de nossas ações. O resultado obtido é 0,97. Este indicador reflete a alta dependência do primeiro valor em relação ao segundo.

Método 2: Calcular Correlação Usando o Analysis ToolPak

Existe outro método para determinar a correlação. Aqui uma das funções encontradas no pacote de análise é usada. Antes de usá-lo, você precisa ativar a ferramenta. Instruções detalhadas:

1. Vá para a seção "Arquivo".

2. Uma nova janela será aberta, na qual você precisa clicar na seção "Configurações".
3. Clique em “Complementos”.
4. Encontramos o elemento “Gerenciamento” na parte inferior. Aqui você precisa selecionar “Excel Add-ins” no menu de contexto e clicar em “OK”.

5. Uma janela especial de complementos foi aberta. Coloque uma marca de seleção ao lado do elemento "Pacote de análise". Clicamos em “OK”.
6. A ativação foi bem-sucedida. Agora vamos para Dados. Apareceu o bloco "Análise", no qual você precisa clicar em "Análise de dados".
7. Na nova janela que aparece, selecione o elemento “Correlação” e clique em “OK”.

8. A janela de configurações de análise apareceu na tela. Na linha “Intervalo de entrada” é necessário inserir o intervalo de absolutamente todas as colunas que participam da análise. Neste exemplo, estas são as colunas “Valor de vendas” e “Custos de publicidade”. As configurações de exibição de saída são definidas inicialmente como Nova Planilha, o que significa que os resultados serão exibidos em uma planilha diferente. Opcionalmente, você pode alterar o local de saída do resultado. Depois de fazer todas as configurações, clique em “OK”.

Saiu a pontuação final. O resultado é o mesmo do primeiro método – 0,97.

Definição e cálculo do coeficiente de correlação múltipla no MS Excel

Para identificar o nível de dependência de várias grandezas, são utilizados vários coeficientes. No futuro, os resultados serão resumidos em uma tabela separada, chamada matriz de correlação.

Guia detalhado:

1. Na seção “Dados”, encontramos o já conhecido bloco “Análise” e clicamos em “Análise de Dados”.

2. Na janela que aparece, clique no elemento “Correlação” e clique em “OK”.
3. Na linha “Intervalo de entrada” dirigimos no intervalo para três ou mais colunas da tabela de origem. O intervalo pode ser inserido manualmente ou simplesmente selecioná-lo com o LMB, e ele aparecerá automaticamente na linha desejada. Em “Agrupamento”, selecione o método de agrupamento apropriado. Em “Output Parameter” especifica o local onde os resultados da correlação serão exibidos. Clicamos em “OK”.

4. Preparar! A matriz de correlação foi construída.

Coeficiente de correlação de pares no Excel

Vamos descobrir como desenhar corretamente o coeficiente de correlação de pares em uma planilha do Excel.

Cálculo do coeficiente de correlação de pares no Excel

Por exemplo, você tem valores x e y.

X é a variável dependente e y é a independente. É necessário encontrar a direção e a força da relação entre esses indicadores. Instrução passo a passo:

1. Vamos encontrar os valores médios usando a função CORAÇÃO.

2. Vamos calcular cada х и xavg, у и avg usando o operador «-».

3. Multiplicamos as diferenças calculadas.

4. Calculamos a soma dos indicadores nesta coluna. O numerador é o resultado encontrado.

5. Calcule os denominadores da diferença х и x média, y и y-médio. Para fazer isso, vamos realizar a quadratura.

6. Usando a função AUTOSUMA, encontre os indicadores nas colunas resultantes. Fazemos multiplicação. Usando a função ROOT quadrado o resultado.

7. Calculamos o quociente usando os valores do denominador e do numerador.

8. CORREL é uma função integrada que permite evitar cálculos complexos. Vamos ao “Assistente de Função”, selecionamos CORREL e especificamos as matrizes de indicadores х и у. Construímos um gráfico que exibe os valores obtidos.

Matriz de coeficientes de correlação de pares no Excel

Vamos analisar como calcular os coeficientes de matrizes pareadas. Por exemplo, existe uma matriz de quatro variáveis.

Instruções passo a passo:

1. Vamos para a “Análise de Dados”, localizada no bloco “Análise” da guia “Dados”. Selecione Correlação na lista que aparece.
2. Definimos todas as configurações necessárias. “Intervalo de entrada” – o intervalo de todas as quatro colunas. “Intervalo de saída” – o local onde queremos exibir os totais. Clicamos no botão "OK".
3. Uma matriz de correlação foi construída no local escolhido. Cada interseção de uma linha e uma coluna é um coeficiente de correlação. O número 1 é exibido quando as coordenadas coincidem.

Função CORREL para determinar relacionamento e correlação no Excel

CORREL – uma função usada para calcular o coeficiente de correlação entre 2 arrays. Vejamos quatro exemplos de todas as habilidades desta função.

Exemplos de uso da função CORREL no Excel

Primeiro exemplo. Há uma placa com informações sobre os salários médios dos funcionários da empresa ao longo de onze anos e o câmbio de $. É necessário identificar a relação entre essas duas grandezas. A tabela fica assim:

O algoritmo de cálculo fica assim:

A pontuação exibida é próxima de 1. Resultado:

Determinação do coeficiente de correlação do impacto das ações no resultado

Segundo exemplo. Dois licitantes abordaram duas agências diferentes para obter ajuda com uma promoção de quinze dias. Todos os dias era realizada uma pesquisa social, que determinava o grau de apoio de cada candidato. Qualquer entrevistado poderia escolher um dos dois candidatos ou se opor a todos. É necessário determinar o quanto cada promoção publicitária influenciou no grau de apoio aos candidatos, qual empresa é mais eficiente.

Usando as fórmulas abaixo, calculamos o coeficiente de correlação:

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

Resultados:

Dos resultados obtidos, fica claro que o grau de apoio ao 1º candidato aumentou a cada dia de promoção publicitária, portanto, o coeficiente de correlação aproxima-se de 1. Quando a publicidade foi lançada, o outro candidato tinha um grande número de confiança, e para 5 dias houve uma tendência positiva. Então o grau de confiança diminuiu e no décimo quinto dia caiu abaixo dos indicadores iniciais. Pontuações baixas sugerem que a promoção impactou negativamente o suporte. Não esqueça que outros fatores concomitantes que não são considerados de forma tabular também podem afetar os indicadores.

Análise da popularidade do conteúdo por correlação de visualizações de vídeo e republicações

Terceiro exemplo. Uma pessoa para promover seus próprios vídeos na hospedagem de vídeos do YouTube usa as redes sociais para divulgar o canal. Ele percebe que existe alguma relação entre o número de repostagens nas redes sociais e o número de visualizações no canal. É possível prever o desempenho futuro usando ferramentas de planilha? É necessário identificar a razoabilidade da aplicação da equação de regressão linear para prever o número de visualizações de vídeos em função do número de reposts. Tabela com valores:

Agora é necessário determinar a presença de uma relação entre 2 indicadores de acordo com a fórmula abaixo:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Forte relacionamento direto”;”Forte relacionamento inverso”);”Fraco ou nenhum relacionamento”)' class='formula'>

Se o coeficiente resultante for superior a 0,7, é mais apropriado usar a função de regressão linear. Neste exemplo, fazemos:

Agora estamos construindo um gráfico:

Aplicamos esta equação para determinar o número de visualizações em 200, 500 e 1000 compartilhamentos: =9,2937*D4-206,12. Obtemos os seguintes resultados:

função PREVISÃO permite determinar o número de visualizações no momento, se houve, por exemplo, duzentos e cinquenta republicações. Aplicamos: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Os valores não estão relacionados”)' class='formula'>. Obtemos os seguintes resultados:

Recursos do uso da função CORREL no Excel

Esta função tem as seguintes características:

1. As células vazias não são consideradas.
2. As células que contêm informações do tipo Boolean e Text não são levadas em consideração.
3. A dupla negação “-” é usada para explicar valores lógicos na forma de números.
4. O número de células nas matrizes estudadas deve corresponder, caso contrário a mensagem #N/A será exibida.

Avaliação da significância estatística do coeficiente de correlação

Ao testar a significância de um coeficiente de correlação, a hipótese nula é que o indicador tenha valor 0, enquanto a alternativa não. A seguinte fórmula é usada para verificação:

Conclusão

A análise de correlação em uma planilha é um processo simples e automatizado. Para realizá-lo, você só precisa saber onde estão localizadas as ferramentas necessárias e como ativá-las através das configurações do programa.