Como obter a equação de regressão de um conjunto de dados no Excel?

Confira nosso curso online de QGIS

Descubra como estabelecer relações matemáticas (ou criar regressões) em duas variáveis ambientais. Veja também como verificar se a equação obtida representa bem os dados estudados usando R quadrado.

Mostramos numa postagem anterior como realizar os cálculos no Excel para obter o Índice de Qualidade de Água (IQA). Foi apresentado quais são os parâmetros envolvidos, uma descrição dos indicadores e quais são suas aplicações.

Leia mais em: O que é Índice de Qualidade da Água – IQA e como Calculá-lo no Excel?

Nesta postagem, iremos mostrar como as equações que apresentamos foram obtidas no Excel. Vamos apresentar três exemplos de equações nesta postagem, uma equação linear simples, uma equação logarítmica e uma equação gaussiana (as duas últimas foram muito utilizadas na postagem do IQA).

O que é Regressão?

Regressão é uma técnica estatística para avaliar a relação matemática entre duas variáveis (uma dependente – y e outra independente – x).

Por exemplo, você quer saber qual é a relação que há no solo entre a concentração de cálcio e magnésio. Ambos elementos estão intimamente relacionados e se pegarmos várias amostras de solo e criarmos um gráfico de dispersão, veremos que os pontos tenderão a criar uma linha ascendente – Ou seja, quanto mais cálcio, mais magnésio, e vice-versa.

Existem outras relação mais complexas no solo, como aquelas entre parâmetros físicos como granulometria e porosidade para estimar a condutividade hidráulica. Veja os trabalhos de Van Genuchten (1980), Durner (1994)Schaap et al. (2001) para mais detalhes.

A regressão irá representar a melhor forma de explicar a relação entre as variáveis dependente e independente. Porém, é importante lembrar que existe um grau de erro associado às regressões, pois, com dados reais, nenhuma regressão é perfeita.

Esse grau de erro é apresentado na forma do R2 (r quadrado ou coeficiente de determinação), onde é subtraído de 1 as somas das diferenças entre os dados reais e os dados estimados ao quadrado (SQres) divididos pela soma das diferenças entre os dados reais e a média ao quadrado (SQtot).

$latex SQ_{\text{res}}=\sum _{i}(y_{i}-{\hat{y}_{i}})^{2} $

$latex SQ_{\text{tot}}=\sum _{i}(y_{i}-{\bar {y}})^{2} $

$latex R^{2}\equiv 1-{SQ_{\rm {res}} \over SQ_{\rm {tot}}} $

Quanto mais próximo for nossa regressão (ou melhor, nossa equação de regressão) dos dados reais, mais próximo de 1 será o R quadrado. Outra forma de interpretar o R2 é a porcentagem da variância na variável dependente que é explicada pela variável independente.

Embora números elevados de R2 sejam bons, sempre confira o gráfico entre os resíduos e os dados estimados, caso haja um padrão nos pontos distribuídos, provavelmente a equação de regressão esta precisando de outras variáveis independentes, termos polinomiais ou de interação.

Agora que temos uma base para interpretar nossas equações e resultados, vamos verificar como gerar eles no Excel.

Equação de Regressão no Excel

Para trabalhar nossa primeira equação, utilizaremos os dados disponibilizados no 11º Relatório de Monitoramento Ambiental dos Indicadores Ambientais da Ação Civil Pública do Carvão, na tabela 13 (página 35), a qual dispõe sobre a qualidade dos efluentes das bocas de mina abandonadas.

A qualidade da água que sai dessas bocas de mina é impactada pela Drenagem Ácida de Mina, deixando seus valores de pH bastante baixos e com elevados valores de Acidez, Sulfatos, Alumínio, Ferro e Manganês.

Desses dados, você pode se perguntar, qual é a relação entre os valores de Alumínio e Acidez?

Depois de passar os dados para o Excel na forma de colunas, selecione as duas colunas contendo os dados que serão testados (i.e. Alumínio e Acidez), conforme figura abaixo.

Colunas selecionadas para criar o gráfico de dispersão e a equação de regressão
Colunas selecionadas para criar o gráfico de dispersão e a equação de regressão. Note que copiamos e colamos a coluna de acidez após a de alumínio, para que o Excel interprete a primeira (Alumínio) como X e a segunda (Acidez) como Y.

Após selecionar os seus dados, vá na aba Inserir (1) e selecione o ícone referente aos gráficos de dispersão e escolha o primeiro estilo (2).

Como inserir um gráfico de dispersão no Excel.
Como inserir um gráfico de dispersão no Excel.

Agora, selecione o gráfico (clicando sobre ele) e o Excel irá habilitar a aba Ferramentas de Gráficos. Nela, vá em Design e clique sobre o botão Adicionar Elemento Gráfico (é o primeiro botão do painel).

Um dos últimos itens do menu que irá abrir são as Linhas de Tendência, onde escolhemos a melhor tendência que se encaixa nos nossos dados. Aqui, iremos utilizar a Linear.

No gráfico de dispersão, irá aparecer um linha cruzando nossos dados (pontos), mas ainda esta faltando a nossa equação de regressão e o grau de erro atribuído à nossa equação.

Dê dois cliques sobre a linha de tendência gerada e o Excel irá abrir uma nova janela com as opções para editá-la. Na parte inferior desta janela, há duas caixas que deverão ser marcadas (1), a primeira se refere à equação (Exibir equação no gráfico) e a segunda ao R quadrado (Exibir valor de R quadrado no gráfico).

O resultado desta operação, com a equação e R2 (2), é apresentado na figura abaixo.

Gráfico com equação de regressão e R2 e caixas marcadas no Excel.
Gráfico com equação de regressão e R2 e caixas marcadas no Excel.

Com essa equação, podemos inseri-lá numa célula do Excel e calcular outros valores de Acidez. Porém, faça isso com cautela, veja no gráfico que há maior variação nos dados para valores mais elevados de Alumínio (possivelmente porque outros fatores estão contribuindo para isso, além do Alumínio).

Salienta-se também que a variação do parâmetro Acidez é respondida em 94,23% pela variação de Alumínio (outros 5,77% são respondidos por outras variáveis).

Outro fator mais importante para ser considerado é não ficar procurando relações entre parâmetros sem antes ter conhecimento do fenômeno e uma hipótese formulada para ser testada. No meio acadêmico, isso é chamado de p-hacking e deve ser evitado.

A química Nadja Alexandre nos dá mais detalhes desta reação, confira abaixo:

“As concentrações de alumínio e de ferro na forma oxidada (Fe +3) influenciam na concentração de acidez dos efluentes de boca de mina.

Isso ocorre pois quando é realizado a análise de acidez em laboratório, é feito a titulação da amostra ácida (com valor desconhecido) com hidróxido de sódio (NaOH), ou seja, uma titulação ácido-base, onde o OH- da soda vai neutralizar o H+ do ácido. Esse procedimento é realizado num pH até 8,3, o qual é o pH de virada da fenalftaleína, sendo este o método padrão.

Se fizéssemos isso com uma amostra padrão de ácido, teríamos todo o OH- neutralizando o H+, porém, nas águas de mineração de carvão, há muito alumínio e ferro (+3), sendo que o OH- da soda é disputado pelo H+ da solução, pelo alumínio (que precipita com pH em torno de 5,6) e pelo ferro +3 (que precipita com pH em torno de 3 e 4), pois estes dois últimos estão num ambiente que favorece sua precipitação).

Assim, ao invés de neutralizar o H+, o OH- acaba se ligando a esses metais (Al e Fe) para formar um precipitado.

No final, é gasto uma quantidade elevada de reagentes para elevar o pH, pois, primeiro, é necessário precipitar esses elementos para depois ocorrer a neutralização do H+. O ferro e o alumínio acabam criando um efeito tampão nas drenagens ácidas de mina.”

Regressões para as Equações do IQA

Nesta parte do tutorial, iremos mostrar como obtemos as equações do Índice de Qualidade da Água e usaremos dois parâmetros, Fósforo Total e pH, como exemplo.

Se você olhar a ficha do IQA com os gráficos de cada parâmetro, irá notar que o parâmetro Fósforo Total é representado por uma curva logarítmica.

O que devemos realizar para obtê-la no Excel?

Primeiro, vamos obter o gráfico para termos a equação relacionando o índice de qualidade com a concentração de fósforo total. No Excel, crie uma coluna para o índice e outra para a concentração, sendo que esses dados são obtidos nestes gráficos. O resultado pode ser visto abaixo.

Dados obtidos no gráfico do IQA para o parâmetro Fósforo Total.
Dados obtidos no gráfico do IQA para o parâmetro Fósforo Total.

Após inserir os dados, repita o procedimento que explicamos acima para a regressão linear:

  • Selecione os dados de interesse;
  • Crie um gráfico de dispersão;
  • Selecione o gráfico;
  • Adicione a linha de tendência (neste caso, a Logarítmica);
  • E marque para exibir a equação e o R quadrado.

Lembre-se que a interpretação dos dados do gráfico pode variar de pessoa para pessoa, modificando ligeiramente a equação de regressão final.

Após finalizar o procedimento, você terá uma equação relacionando o índice de qualidade com o parâmetro Fósforo Total.

Agora, para o pH, o procedimento já é diferente.

O gráfico para o parâmetro pH é na forma de um sino (ou montanha, se preferir). Esse tipo de gráfico é representado por uma equação gaussiana (a qual é oriunda da curva de Gauss, ou distribuição normal).

Se você observou bem as linhas de tendência disponíveis no Excel, irá notar que não há uma linha chamada “Gaussiana”. Assim, teremos que nós mesmos montá-la a partir da seguinte equação.

$latex {f(x)=ae^{-{\frac {(x-b)^{2}}{2c^{2}}}}} &s=3$

Esta equação representa, de forma geral, as equações gaussianas, que resultam no gráfico que queremos descrever. O parâmetro a é igual ao ponto mais alto da nossa curva, b é o centro deste ponto mais alto e c é o desvio padrão, que acaba controlando o tamanho do gráfico.

Os parâmetros a e b são fáceis de serem obtidos, mas o c é obtido a partir de tentativas, por isso, escreva os valores do gráfico do IQA, reserve uma coluna para montarmos nossa equação e deixe um campo mais afastado para simular diferentes valores de c. A imagem abaixo dá uma sugestão de organização.

Exemplo de organização dos dados para obtenção da equação gaussiana.
Exemplo de organização dos dados para obtenção da equação gaussiana.

Como iremos verificar se nossa equação esta se aproximando da real?

Vamos calcular o valor de R quadrado manualmente. Comece criando uma coluna nova para a soma dos quadrados dos resíduos (SQres), nela você irá subtrair o valor tabelado pelo valor que obtivemos na equação e depois elevar ao quadrado (faça isso para todas as linhas).

Em seguida, crie outra coluna para a soma total dos quadrados (SQtot), subtraia o valor tabelado pela média dos valores tabelados e eleve ao quadrado. Repita isso para cada linha.

Por fim, subtraia de 1 o resultado da divisão da soma dos quadrados dos resíduos pela soma total dos quadrados e você terá o valor de R quadrado. A figura seguinte mostra o resultado deste processo.

Procedimento para obter a equação gaussiana e calcular o r quadrado.
Procedimento para obter a equação gaussiana e calcular o r quadrado.

Agora, você pode obter a equação de qualquer tipo de gráfico, basta saber qual é a sua forma geral.

É importante lembrar que a curva do pH não é exatamente uma curva gaussiana, por isso há erros associados. Entretanto, você pode obter duas equações utilizando a função SE do Excel para calcular situações diferentes antes e depois do ponto mais elevado da curva.

Caso você divida a curva em duas, lembre-se que ela deixa de ser gaussiana para tornar-se uma curva sigmoíde (a qual utiliza outra equação geral).

E com isso, esperamos ter deixado para vocês mais claro como obtivemos as equações do IQA e explicado o que é uma regressão. Se esta postagem tenha te ajudado, compartilhe ela.

Referências Consultadas.

FROST, Jim. How To Interpret R-squared in Regression Analysis. Disponível em <http://statisticsbyjim.com/regression/interpret-r-squared-regression/>. Acesso em 16 dez. 2018.

GALLO, Amy. A Refresher on Regression Analysis. Harvard Business Review. 2015. Disponível em <https://hbr.org/2015/11/a-refresher-on-regression-analysis>. Acesso em 15 dez. 2018.


Clique na figura abaixo e assine nossa lista de emails para receber nosso ebook "Como criar mapas de localização com ArcGIS 10.x".

Apostila Mapa de Localização Banner

Author: Fernando BS

Engenheiro Ambiental e de Segurança do Trabalho. Atua nas áreas de geoprocessamento, mineração e hidrologia. Busca soluções utilizando softwares como QGIS, R e Python.

3 thoughts on “Como obter a equação de regressão de um conjunto de dados no Excel?”

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *