Como fazer uma regressão linear no Excel 2010?

E ai pessoas!

Por estes dias, tive que fazer uma regressão linear, só que eu estava com um computador sem SPSS, PSPP ou qualquer outra ferramenta estatística, ou seja, só estava com o Excel 2010.

Dai lembrei que em uma aula de métodos quantitativos no mestrado, na qual o professor mostrou que havia um add-in no Excel de ferramentas de análises de dados; assim fui verificar se tinha algum método de regressão nestas ferramentas, para a minha felicidade, havia.

Primeiramente, desculpem o Excel em inglês, pois eu fico totalmente perdido com a versão de pt-BR.

Nas opções do Excel,

Menu Add-Ins, Manage [Excel Add-ins], Go…,

Selecione o Analysis ToolPak,

Crie ou obtenha alguns dados,

Vá ao menu Data, Analysis, Data Analysis,

Regression,

Informe o intervalo da variável dependente (Y) e da variável independente (X). Em ‘Output Range’, informe a célula onde o resultado da análise irá aparecer,

Feito isso, teremos a análise dos dados,

Para quem só precisa verificar a tendência de X para Y ou apresentar ter uma previsão, existem diversas as funções do Excel que são muito boas para isso:

Função PEARSON (intervalo de Y, intervalo de X) para calcular a correlação entre Y e X.

Função TREND (intervalo de Y, intervalo de X, valor de X) para prever o valor de Y.

Função FORECAST (valor de X, intervalo de Y, intervalo de X) para prever o valor de Y.

Para quem quer fazer uma trendline para algum gráfico, este tutorial pode ajudar bastante:

http://www.officetooltips.com/excel/tips/adding_a_trend_line.html

UNPIVOT ou Reverse PIVOT no Excel 2010 – Transformando colunas em linhas

Bom dia pessoas,

Como um autentico viciado em SQL Server, tenho problemas com o Excel para fazer coisas que normalmente são bem “simples” no T-SQL, sendo a principal delas o UNPIVOT, para transformar colunas em linhas.

Como penso que eu não sou o único que sofre com isso, elaborei um tutorial simples (totalmente workaround), conforme abaixo.

Primeiramente, temos a massa de dados com vários itens com valores em colunas por ano:

Utilizando o atalho “ALT + D + P”, entramos no tradicional PivotTable Wizard, informando a opção “Multiple consolidation ranges”:

Prossigo para a tela seguinte:

Selecionamos o “Range” da massa de dados, ou seja, a tabela inteira:

Criamos uma nova aba para a PivotTable:

Nesta nova aba, teremos a PivotTable:

Com o botão direito sobre o último campo da PivotTable, clicamos em “Show Details”:

E teremos os dados no formato esperado:

Agora só formatar:

Excel: Agrupando Datas

Pessoalmente considero o Excel a segunda melhor ferramentas para trabalhar com banco de dados (depois do conjunto de ferramentas do SQL Server – SSRS, SSAS, SSAS e Management Studio). O maior diferencial do Excel esta na facilidade de criar uma interface amigável e flexível para com o usuário final, uma atividade que é um pouco mais complexa com o Report Builder do SQL Server (SSRS).

Neste meu primeiro tópico trabalhando com o Excel, estarei demonstrando a flexibilidade da ferramenta para tratar dados de data por meio de uma tabela dinâmica. Mesmo que para DBAs e para alguns desenvolvedores familiarizados com SQL esta tarefa seja muito fácil, nem se pode auxiliar usuário final trabalhar com os dados direto do banco de dados, desta forma ferramentas como Excel e Report Builder se fazem necessárias.

Agrupar valores por Dia, Mês e Ano em SQL pode ser resolvido por queries simples como:

SELECT YEAR(dataPla) AS Ano
, MONTH(dataPla) AS Mes
, DAY(dataPla) AS Dia
, SUM(valorPla) AS Total
FROM Planilha
GROUP BY YEAR(dataPla)
, MONTH(dataPla)
, DAY(dataPla)

Ainda assim é um desafio ensinar o usuário final fazer suas próprias queries. Então vamos ao Excel! Tendo a massa de dados no Excel, que pode ser conseguida exportando os resultados de uma consulta SQL pelo Management Studio ou importada pelo próprio Excel ou gerada de alguma outra ferramenta, vamos analisar os dados.

No exemplo, tenho uma coluna Data e outra de Valor, uma estrutura bem simples para ser utilizada como modelo para outras situações.

Selecione algum campo de sua massa de dados, de preferência a primeira linha da primeira coluna.

Formate como uma tabela, utilizando a opção “Formatar como Tabela” do menu “Início”:

Informe a seleção e marque a opção “Minha tabela tem cabeçalhos” se for válida.

Pronto, o primeiro passo resultará numa tabela onde estão identificadas as colunas e linhas (estas colunas permitem filtros e ordenação dos dados de forma simples).

Como segundo passo, no menu Design, selecione a opção “Resumir com Tabela Dinâmica”.

Defina a tabela ou intervalo a ser analisado.

Após prosseguir, o segundo passo terá como resultado a estrutura para a manipulação da tabela dinâmica.

No terceiro passo, arraste o campo de data para a área “Rótulos de Linha” e o campo de valores para a área “Valores”, tendo o seguinte resultado.

Selecione a coluna de “Rotulo de Linha” da tabela dinâmica e verifique se os tipos de dados são do tipo Data.

Conforme o menu “Início”:

Neste quarto passo, selecione no menu Opções, a opção “Agrupar Seleção”:

Será exibida a seguinte janela, nela informe os agrupamentos que você deseja utilizar.

Pronto, o resultado deste tutorial é uma tabela dinâmica agrupando os valores por data (ano, trimestre, mês, dia).

Espero que as informações deste post venham a auxiliar em futuras necessidades.

Esqueci de um detalhe, para gerar os totais pelos grupos (ano, trimestre, mes), é possivel pela opção “Mostrar Todos os Subtotais no Início do Grupo” no menu “Design\Subtotais”.

 

E o resultado será a soma dos valores por Mês, Trimestre e Ano!

Se quiserem saber mais informações sobre trabalhar com Excel para tratamento de dados, me deixo à disposição a desenvolver novos temas que forem sugeridos e esclarecer dúvidas sobre este e outros assuntos.

Agradecimentos a Ísis Sayuri pelo desafio que me motivou a escrever este post.