Como calcular o coeficiente de correlação entre duas variáveis no Excel?
Geralmente, usamos o coeficiente de correlação (um valor que varia de -1 a 1) para indicar a força e a direção da relação linear entre duas variáveis. O coeficiente de correlação é uma estatística amplamente utilizada que ajuda você a entender relações, como a conexão entre vendas e gastos com publicidade, temperatura e vendas de sorvete ou outros dados em pares. No Excel, existem vários métodos simples para calcular o coeficiente de correlação, incluindo funções embutidas e ferramentas de análise.
Método A: Use diretamente a função CORREL
Método B: Aplique a Análise de Dados e gere a análise
Método C: Use a função PEARSON como alternativa
Método D: Use código VBA para calcular coeficientes de correlação para múltiplos pares
Método A: Use diretamente a função CORREL
Considere duas listas de dados, cada uma representando uma variável. Se você deseja calcular o coeficiente de correlação entre essas duas variáveis no Excel, este método é rápido e eficiente.
Para uso prático, certifique-se de que ambas as faixas de dados sejam numéricas e contenham o mesmo número de observações. Por exemplo, se você tiver os seguintes dados em pares:
Selecione uma célula em branco onde você deseja exibir o resultado do cálculo. Insira a seguinte fórmula e, em seguida, pressione a tecla "Enter" para calcular o coeficiente de correlação:
=CORREL(A2:A7,B2:B7)
Nesta fórmula, A2:A7 e B2:B7 representam as duas listas de variáveis que você deseja analisar. As faixas devem ter o mesmo comprimento, e cada par deve corresponder à mesma observação.
Dica prática: CORREL ignora automaticamente células vazias e texto, mas se não houver pares numéricos válidos nas duas colunas, ele retornará um erro #DIV/0!. Certifique-se de que seus dados estejam alinhados corretamente e contenham pares numéricos para um cálculo de correlação preciso.
Depois de calcular o coeficiente de correlação, você pode inserir um gráfico de linha para observar visualmente as relações e interpretar melhor a correlação, conforme mostrado abaixo:
Este método é ideal para verificações rápidas entre dois pequenos conjuntos de dados ou ao trabalhar interativamente na sua planilha. É adequado para usuários que procuram um resultado imediato sem a necessidade de saída estatística avançada.
Desbloqueie a Magia do Excel com o Kutools AI
- Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
- Codificação VBA: Escreva e implemente código VBA sem esforço.
- Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
- Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.
Método B: Aplique a Análise de Dados e gere a análise
Se você precisa analisar a correlação entre várias variáveis de uma só vez ou deseja uma tabela de saída mais abrangente, o “Suplemento de Análise de Dados” do Excel fornece uma solução útil. Esse suplemento gera uma matriz de correlação e permite comparar várias variáveis em uma única etapa, o que é valioso para grandes conjuntos de dados ou construção de relatórios estatísticos.
1. Se você já adicionou o Suplemento de Análise de Dados à sua guia Dados, pode pular para a etapa 3. Caso contrário, clique em Arquivo > Opções. Na caixa de diálogo “Opções do Excel”, escolha Suplementos no painel esquerdo e, em seguida, clique no botão Ir ao lado da caixa “Suplementos do Excel”.
2. Na caixa de diálogo “Suplementos”, marque a caixa rotulada Pacote de Ferramentas de Análise, depois clique em OK. Isso adicionará o grupo "Análise de Dados" à guia Dados .
3. Em seguida, clique em Dados > Análise de Dados. Na janela pop-up “Análise de Dados”, escolha Correlação da lista e, em seguida, clique em OK.
4. Na caixa de diálogo Correlação, configure o seguinte:
1) Selecione a faixa que contém seus dados.
2) Escolha a opção "Colunas" ou "Linhas", dependendo de como seus dados estão organizados.
3) Se seus dados incluem cabeçalhos, marque a opção “Rótulos na primeira linha”.
4) Especifique um local de saída em “Opções de saída” para exibir os resultados.
5. Clique em OK para gerar a tabela de análise de correlação. Os coeficientes de correlação serão apresentados na faixa especificada.
Este método é adequado quando você precisa avaliar as relações entre mais de duas variáveis ou deseja uma tabela resumo para fins de relatório. A saída da Análise de Dados é concisa, mas não fornece estatísticas de significância adicionais. Se você receber resultados inesperados, verifique novamente seus dados quanto à consistência, células vazias e seleção de faixa correta.
Método C: Use a função PEARSON como alternativa
Além de CORREL, o Excel fornece a função PEARSON, que também calcula o coeficiente de correlação de Pearson entre duas variáveis. Funcionalmente, PEARSON e CORREL retornam o mesmo resultado. No entanto, PEARSON segue estritamente a fórmula matemática original, enquanto CORREL é otimizado para o ambiente do Excel. Se você está acostumado à teoria estatística ou trabalhando com ferramentas estatísticas fora do Excel, PEARSON pode parecer mais familiar.
Por exemplo, com duas listas numéricas em A2:A7 e B2:B7, você pode calcular a correlação da seguinte forma:
1. Selecione uma célula onde deseja exibir o resultado e insira esta fórmula:
=PEARSON(A2:A7,B2:B7)2. Pressione Enter para concluir o cálculo. Se você deseja analisar pares de dados adicionais, ajuste as faixas de células de acordo ou arraste a fórmula para outras células.
Dicas: PEARSON ignora texto ou valores lógicos, então certifique-se de que ambas as faixas contenham apenas valores numéricos e tenham o mesmo comprimento. Se houver dados ausentes em uma coluna, alinhe suas faixas de acordo para evitar erros.
Usar PEARSON é especialmente prático para usuários que migram de outros softwares estatísticos ou para ambientes acadêmicos onde a aderência estrita à terminologia é esperada. Ambos CORREL e PEARSON fornecem o mesmo resultado para casos de uso típicos no Excel.
Se você encontrar um erro #DIV/0!, verifique se ambas as faixas têm o mesmo comprimento e não contêm células vazias ou não numéricas desiguais.
Prós: Fácil de usar, consistente com software estatístico; Contras: Não oferece diferença significativa de CORREL para a maioria dos usuários.
Método D: Use código VBA para calcular coeficientes de correlação para múltiplos pares
Se você precisa automatizar o cálculo de coeficientes de correlação para vários pares de dados (por exemplo, ao trabalhar com muitas combinações de variáveis), escrever uma macro VBA simples é uma escolha eficiente. Este método é adequado para usuários avançados que desejam processar grandes conjuntos de dados ou automatizar tarefas de análise repetitivas.
1. Para usar este método, primeiro abra o editor VBA clicando Desenvolvedor > Visual Basic. Na janela Visual Basic para Aplicações, vá para Inserir > Módulo e, em seguida, cole o seguinte código no módulo:
Sub BatchCalculateCorrelations()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim lastRow As Long
Dim i As Long
Dim resultCol As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
If rng1.Rows.Count <> rng2.Rows.Count Then
MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
For i = 1 To rng2.Columns.Count
resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
Next i
End Sub2. Após inserir o código, feche o editor VBA. No Excel, pressione Alt + F8, selecione BatchCalculateCorrelations e clique em Executar. Você será solicitado a selecionar:
- O intervalo da primeira variável (uma única coluna, por exemplo, A2:A7)
- O intervalo da segunda variável (uma ou mais colunas, por exemplo, B2:D7)
- A célula onde você deseja que os resultados comecem (por exemplo, F2)
A macro então calcula o coeficiente de correlação entre a primeira variável e cada coluna no segundo intervalo, exibindo os resultados horizontalmente a partir da célula escolhida.
Vantagens: Automatiza cálculos repetitivos, economiza tempo significativo com grandes conjuntos de dados e garante consistência.
Se você encontrar problemas como “As duas faixas de dados devem ter o mesmo número de linhas”, certifique-se de que todas as colunas selecionadas compartilhem exatamente a mesma contagem de linhas e não tenham linhas em branco. Para solução de problemas de erro, verifique se as macros estão habilitadas e as faixas foram selecionadas corretamente.
Ao trabalhar com coeficientes de correlação no Excel, escolher o método certo depende da estrutura dos seus dados e das necessidades de análise. Para cálculos rápidos únicos entre duas séries, fórmulas como CORREL ou PEARSON são eficientes e fáceis de usar. Para múltiplas variáveis ou a necessidade de tabelas resumo, o Pacote de Ferramentas de Análise é muito prático. Se você precisar de análises repetidas em grandes conjuntos de dados ou quiser fluxos de trabalho personalizados, considere automatizar com VBA para economizar tempo e reduzir erros humanos.
Sempre certifique-se de que suas faixas de dados estejam alinhadas, limpas e não contenham células vazias ou não numéricas para evitar erros de fórmula. Se você encontrar resultados inesperados, verifique as seleções novamente e os tipos de dados.
Artigos Relacionados
- Calcular a mudança percentual ou diferença entre dois números no Excel
Este artigo discute o cálculo da mudança percentual ou diferença entre dois números no Excel.
- Calcular ou atribuir conceito por letras no Excel
Atribuir conceitos por letras para cada aluno com base em suas notas pode ser uma tarefa comum para um professor. Por exemplo, eu tenho uma escala de notas definida onde a pontuação 0-59 = F, 60-69 = D, 70-79 = C, 80-89 = B e 90-100 = A, veja mais.
- Calcular taxa de desconto ou preço no Excel
Quando o Natal está chegando, deve haver muitas promoções de vendas nos shoppings. Mas se os diferentes tipos de itens têm diferentes descontos, como você pode calcular as taxas de desconto ou preços dos diferentes itens?
- Contar o número de dias / dias úteis / finais de semana entre duas datas no Excel
Talvez, às vezes, você apenas queira calcular os dias úteis entre duas datas, e às vezes, você precisa contar apenas os dias de fim de semana entre as duas datas.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins