Regressão é uma ferramenta de análise que usamos para analisar grandes quantidades de dados e fazer previsões e previsões no Microsoft Excel.
Quer prever o futuro? Não, não vamos aprender astrologia. Gostamos de números e vamos aprender a análise de regressão no Excel hoje.
Para prever estimativas futuras, estudaremos:
- ANÁLISE DE REGRESSÃO USANDO FUNÇÕES EXCEL (LOCALIZAÇÃO DE REGRESSÃO MANUAL)
- ANÁLISE DE REGRESSÃO USANDO O COMPLEMENTO DO EXCEL'S ANALYSIS TOOLPAK
- TABELA DE REGRESSÃO NO EXCEL
Vamos fazê-lo…
Cenário:
Vamos supor que você venda refrigerantes. Será muito bom se você puder prever:
- Quantos refrigerantes serão vendidos no próximo ano com base nos dados do ano anterior?
- Quais campos precisam ser focados?
- E como você pode aumentar suas vendas mudando sua estratégia?
Será lucrativamente incrível. Certo? … Eu sei. Então vamos começar.
Você tem 11 registros de vendedores e refrigerantes vendidos.
Agora, com base nesses dados, você deseja prever o número de vendedores necessários para atingir 2.000 vendas de refrigerantes.
A equação de regressão é uma ferramenta para fazer estimativas aproximadas. Para fazer isso, precisamos primeiro conhecer a Regressão.
ANÁLISE DE REGRESSÃO USANDO FUNÇÕES EXCEL (LOCALIZAÇÃO DE REGRESSÃO MANUAL)
Esta parte fará com que você entenda melhor a regressão do que apenas contar o procedimento de regressão do Excel.
Introdução:
Regressão Linear Simples:
O estudo da relação entre duas variáveis é denominado Regressão Linear Simples. Onde uma variável depende da outra variável independente. A variável dependente é freqüentemente chamada por nomes como Driven, Response e Target variable. E a variável independente é freqüentemente pronunciada como uma variável impulsionadora, preditora ou simplesmente independente. Esses nomes os descrevem claramente.
Agora vamos comparar isso com o seu cenário. Você deseja saber o número de vendedores necessários para alcançar Vendas de 2000. Portanto, aqui, a variável dependente é o número de vendedores e a variável independente é o refrigerante vendido.
A variável independente é principalmente denotada como x e variável dependente como y.
No nosso caso, refrigerantes são vendidos x e o número de vendedores é y.
Se quisermos saber quantos refrigerantes serão vendidos se nomearmos 200 vendedores, então o cenário será vice-versa.
Se movendo.
A matemática "simples" da equação de regressão linear:
Bem, não é simples. Mas o Excel tornou isso simples.
Precisamos prever o número necessário de vendedores para todos os 11 casos para obter a 12ª previsão mais próxima.
Digamos:
Refrigerante vendido é x
O número de vendedores é y
O previsto y (número de vendedores) também chamado Equação de regressão, seria
| x * Inclinação + Interceptação (relaxe, eu tenho tudo sob controle) |
Agora você deve estar se perguntando onde o Estado você obterá a inclinação e a interceptação. Não se preocupe, o Excel tem funções para eles. Você não precisa aprender a encontrar o declive e interceptá-lo manualmente.
Se você quiser, prepararei um tutorial separado para isso. Deixe-me saber na seção de comentários. Estas são algumas ferramentas de análise de dados importantes.
Agora vamos entrar em nosso cálculo:
Passo 1: Prepare esta pequena mesa
Passo 2: Encontre a inclinação da linha de regressão
A função Excel para inclinações é
| = SLOPE (known_y’s, known_x’s) |
Seus known_y’s estão dentro do alcance B2: B12 e val_conhecidos_x estão dentro do intervalo C2: C12
Na cela B16, escreva a fórmula abaixo
| = INCLINAÇÃO (B2: B12, C2: C12) |
(Nota: a inclinação também é chamada de coeficiente de x na equação de regressão)
Você vai ter 0.058409. Arredonde para 2 dígitos decimais e você obterá 0.06.
etapa 3: Encontre a linha de interceptação da regressão
A função do Excel para a interceptação é
| =INTERCEPTAR (known_y’s, known_x’s) |
Nós sabemos qual é o nosso xs e ys conhecidos
Na cela B17, escreva esta fórmula
| = INTERCEPÇÃO (B2: B12, C2: C12) |
Você obterá um valor de -1,11118969. Arredondar para 2 dígitos decimais. Você vai ter -1.11.
Nossa equação de regressão linear é = x * 0,06 + (-1,11). Agora podemos prever o possível y dependendo do alvo x facilmente.
Passo 4: Em D2 escreva a fórmula abaixo
| =C2* $ B $ 16 + $ B $ 17(Equação de regressão) |
Você receberá um valor de 13.55.
Selecione D2 a D13 e pressione CTRL + D para preencher a fórmula no intervalo D2: D13
Na cela D13 você tem o número necessário de vendedores.
| Portanto, para atingir a meta de 2000 Vendas de refrigerantes, você precisa de uma estimativa de 115,71 vendedores ou, digamos, 116, uma vez que é ilegal cortar humanos em pedaços. |
Agora, usando isso, você pode facilmente conduzir análises de variações hipotéticas no Excel. Basta alterar o número de vendas e isso mostrará que muitos vendedores serão necessários para atingir a meta de vendas.
Experimente para descobrir:
Quanta força de trabalho você precisa para aumentar as vendas?
Quantas vendas aumentarão se você aumentar seus vendedores?
Faça sua estimativa mais confiável:
Agora você sabe que precisa de 116 vendedores para realizar 2.000 vendas.
Na análise, nada é apenas dito e acreditado. Você deve dar uma porcentagem de confiabilidade em sua estimativa. É como dar um certificado de sua equação.
Fórmula do coeficiente de correlação:
A próxima coisa que você será perguntado é o quanto essas duas variáveis estão relacionadas. Em termos estáticos, você precisa saber o coeficiente de correlação.
A função Excel para correlação é
| = CORREL (matriz1, matriz2) |
No seu caso, known_x’s e Know_y’s são array1 e array2, independentemente.
Em B18, insira esta fórmula
| = CORREL (((B2: B12, C2: C12) |
Voce terá 0.919090. Formate a célula B2 em porcentagem. Agora tem 92% de correlação.
Agora, o que é isso 92% meios. Isso significa que há 92% de chances de vendas aumentarem se você aumentar o número de vendedores e 92% das vendas diminuem se você diminuir o número de vendedores. É chamado Coeficiente de correlação positiva.
R Squire (R 2):
O valor de R Squire informa em que porcentagem sua equação de regressão não é um acaso. O quanto é preciso de acordo com os dados fornecidos.
A função do Excel para R squire é RSQ.
| RSQ (known_y’s, Known_x’s) |
Em nosso caso, obteremos o valor de R squire na célula B19.
Em B19, insira esta fórmula
| = RSQ (B2: B12, C2: C12) |
Portanto, temos 84% do valor de r Square. O que é uma explicação muito boa para nossa regressão. Diz que 84% dos nossos dados não são por acaso. Y (número de vendedores) depende muito de X (vendas de refrigerantes).
Existem muitos outros testes que podemos fazer com esses dados para garantir nossa regressão. Mas manualmente será um procedimento complexo e demorado. É por isso que o Excel fornece Analysis Toolpak. Usando essa ferramenta, podemos fazer essa análise de regressão em segundos.
REGRESSÃO NO EXCEL USANDO O COMPLEMENTO DO EXCEL'S ANALYSIS TOOLPAK
Se você já sabe o que são equações de regressão e deseja apenas seus resultados rapidamente, esta parte é para você. Mas se você quiser entender as equações de regressão facilmente, vá até ANÁLISE DE REGRESSÃO USANDO FUNÇÕES EXCEL (LOCALIZAÇÃO DE REGRESSÃO MANUAL).
O Excel fornece um monte de ferramentas para análise em seu Analysis Toolpak. Por padrão, não está disponível na guia Dados. Você precisa adicioná-lo. Então, vamos adicioná-lo primeiro.
Adicionando ferramentas de análise ao Excel 2016
Se você não sabe onde está a análise de dados no Excel, siga estas etapas
Etapa 1: Vá para as opções do Excel: Arquivo? Opções? Suplementos
Etapa 2: Clique em Add-Ins. Você verá uma lista de suplementos disponíveis.
Selecione Analysis ToolPak e, na parte inferior da janela, localize gerenciar. Em gerenciar, selecione Suplementos do Excel e clique em IR.
A janela de suplementos será aberta. Aqui, selecione Analysis ToolPak. Em seguida, clique no botão ok.
Agora você pode acessar todas as funções do ToolPak de análise de dados na guia Dados.
Usando ferramentas de análise para regressão
Etapa 1: Vá para a guia Dados, Localizar análise de dados. Em seguida, clique nele.
Uma caixa de diálogo aparecerá.
Passo 2: Encontre ‘Regressão’ na lista de Ferramentas de Análise e clique no botão OK.
A regressão a janela de entrada aparecerá. Você verá várias opções de entrada disponíveis. Mas, por enquanto, vamos nos concentrar apenas no intervalo Y e no intervalo X, deixando todo o resto como padrão.
Etapa 4: Fornecer Entradas:
No. de vendedores é Y
As vendas de refrigerantes são X
Portanto
- Intervalo Y = B2: B11
E
- Intervalo X = C2: C11
Para o intervalo de saída, selecionei E4 na mesma folha. Você pode selecionar uma nova planilha para obter resultados em uma nova planilha na mesma pasta de trabalho ou em uma nova pasta de trabalho completa. Quando você terminar com suas entradas, aperte o botão OK.
Resultados:
Você receberá uma variedade de informações de seus dados. Não fique sobrecarregado. Você não precisa consumir todos os pratos.
Só lidaremos com os resultados que nos ajudarão a estimar o número necessário de vendedores
Etapa 5: Nós conhecemos a equação de regressão para estimativa de y, isso é
x * Inclinação + Interceptação
Só precisamos localizar Declive e Interceptar nos resultados.
E aqui estão eles.
O coeficiente de interceptação é claramente mencionado.
A inclinação é escrita como ‘Variável X 1', Algumas vezes também mencionado como o coeficiente de X. Arredonde-os e obteremos -1,11 como interceptar e 0,06 como inclinação.
Etapa 6: A partir dos resultados, podemos conduzir a equação de regressão. E isso seria
= x * (0,06) + (-1,11)
Prepare esta tabela em excel.
Por enquanto, x é 2000, que está na célula E2.
Na célula F2, insira esta fórmula
= E2 * F21 + F20
Você obterá um resultado de 115.7052757.
Arredondar nos dará 116 de Vendedores Obrigatórios.
Portanto, aprendemos como formar a equação de regressão manualmente e usando o Analysis ToolPak. Como você pode usar esta equação para estimar estatísticas futuras?
Agora vamos entender a saída de regressão fornecida pelo Analysis Toolpak.
Compreendendo o resultado da regressão:
Não há nenhum benefício se você fizer análise de regressão usando o pacote de ferramentas de análise do Excel e não conseguir interpretar seu significado.
Seção de Resumo:
Como o nome sugere, é um resumo dos dados.
-
- R múltiplo: indica como a equação de regressão se ajusta aos dados. Também é chamado de coeficiente de correlação.
No nosso caso, é 0.919090619 ou 0.92 (arredondar para cima). Isso significa que há 92% de chance de aumento nas vendas se aumentarmos o número de vendedores.
-
- R Square: Diz a confiabilidade da regressão encontrada. Diz-nos quantas observações fazem parte da nossa linha de regressão. Em nosso caso, é 0,844727566 ou 0,85. Isso significa que nossa regressão está ajustada em 85%.
- Quadrado R ajustado: O quadrado ajustado é apenas uma versão mais comprovada do quadrado R. Principalmente útil em Análise de Regressão Múltipla.
- Erro padrão: Enquanto R. Squire informa quantos pontos de dados caem perto da linha de regressão, o erro padrão informa a que distância um ponto de dados pode ir da linha de regressão.
No nosso caso, é 6.74.
- Observação: Este é simplesmente o número de observações, que é 11 em nosso exemplo.
Seção Anova:
Esta seção dificilmente é usada na regressão linear.
- df. É um grau de liberdade. É usado ao calcular a regressão manualmente.
- WL. Soma dos quadrados. É apenas uma soma de quadrados de variâncias. Usado para encontrar valores de R squire.
- em. Isso significa valor ao quadrado.
- E 5. F e significância de F. Se a significância de F (valor p da inclinação) for menor do que o teste F, então você pode descartar a hipótese nula e provar sua hipótese. Em uma linguagem simples, você pode concluir que há algum efeito de x sobre y quando alterado.
Em nosso caso, F é 48,96264 e a Significância de F é 0,000063. Isso significa que nossa regressão se ajusta aos dados.
Seção de regressão:
Nesta seção, temos os dois valores mais importantes para nossa equação de regressão.
- Interceptar: temos uma interceptação aqui que informa onde x-intercepta em Y. Esta é uma parte importante da equação de regressão. É -1,11 em nosso caso.
- Variável X 1 (Declive). Também chamado de coeficiente de x. Ele define a tangente da linha de regressão.
TABELA DE REGRESSÃO NO EXCEL
No Excel, é fácil traçar um gráfico de regressão. Basta seguir estas etapas. Para adicionar Gráfico de Regressão no Excel 2016, 2013 e 2010, siga estas etapas simples.
Passo 1. Tenha seus x's conhecidos na primeira coluna e os y's na segunda.
Em nosso caso, sabemos que Known_ x’s são refrigerantes vendidos. E known_y’s são vendedores.
Passo 2. Selecione seu intervalo conhecido de x e y.
Etapa 3: Vá para a guia Inserir e clique no gráfico de dispersão.
Você terá um gráfico parecido com este.
Etapa 4. Adicione a linha de tendência: Vá para o layout e localize a opção de linha de tendência na seção de análise.
Na opção Trendline, clique em Linear Trendline.
Você terá seu gráfico assim.
Este é o seu gráfico de regressão.
Agora, se você adicionar os dados abaixo e estender os dados selecionados. Você verá uma mudança em seu gráfico.
Para nosso exemplo, adicionamos 2.000 ao refrigerante vendido e deixamos os vendedores em branco. E quando estendemos o alcance do gráfico, é isso que teremos.
Ele fornecerá o número necessário de vendedores para fazer 2.000 vendas de refrigerantes na forma gráfica. O que está um pouco abaixo de 120 no gráfico. E pela nossa equação de regressão, sabemos que é 116.
Neste artigo, tentei cobrir tudo na Análise de Regressão do Excel. Eu expliquei a regressão no excel 2016. A regressão no excel 2010 e excel 2013 é a mesma que no excel 2016.
Para qualquer outra consulta sobre este tópico, use a seção de comentários. Faça uma pergunta, dê uma opinião ou apenas mencione meus erros gramaticais. Tudo é bem vindo. Apenas não hesite em usar a seção de comentários.
Como calcular a função MODE no Excel
Como calcular a função média no Excel
Como Criar Gráfico de Desvio Padrão
Estatísticas descritivas no Microsoft Excel 2016
Como usar a função DIST.NORM do Excel
Como usar o gráfico e a análise de Pareto
Artigos populares:
50 Atalho do Excel para aumentar sua produtividade
Como usar a função PROCV no Excel
Como usar a função CONT.SE no Excel 2016
Como usar a função SUMIF no Excel