Previsão no Excel: 6 maneiras comprovadas

O previsão é um elemento muito importante de praticamente qualquer campo de atividade, desde economia e finalização com engenharia. Há um grande número de software especializado nessa direção. Infelizmente, nem todos os usuários sabem que o usual processador de tabela do Excel tem em suas ferramentas de arsenal para prever, que em sua eficácia são pouco inferiores a programas profissionais. Vamos descobrir que tipo de ferramentas são, e como fazer um prognóstico na prática.

Previsão de procedimento

O objetivo de qualquer previsão é identificar a tendência atual e a definição do resultado pretendido em relação ao objeto que está sendo estudado em um determinado ponto no futuro.

Método 1: linha de tendência

Uma das espécies de previsão gráfica mais populares em Excelee é uma extrapolação feita pela construção de uma linha de tendência.

Vamos tentar prever o montante dos lucros da empresa após 3 anos com base nos dados sobre este indicador para os 12 anos anteriores.

1. Estamos construindo uma programação baseada em dados tabulares que consistem em argumentos e valores de função. Para fazer isso, selecione a área da tabela e, em seguida, enquanto estiver na guia "Inserir", clique no ícone do tipo desejado de diagrama, que está no bloco "gráfico". Em seguida, escolha o tipo adequado para uma situação específica. É melhor escolher um diagrama de pontos. Você pode escolher outra aparência, mas os dados são exibidos corretamente, você terá que editar, em particular, remover a linha de argumentos e selecionar outra escala do eixo horizontal.



2. Agora precisamos construir uma linha de tendência. Nós clicamos com o botão direito do clique em qualquer um dos pontos do diagrama. No menu de contexto ativado, você interrompe a seleção no parágrafo "Adicionar linha de tendência".



3. A janela de formatação da linha de tendência é aberta. Pode escolher um dos seis tipos de aproximação:
   • Linear;
   • Logarítmico;
   • Exponencial;
   • Poder;
   • Polinomial;
   • Filtração linear.

   Vamos primeiro escolher uma aproximação linear.

   No bloco de configurações "Previsão" no campo "Encaminhar para", definimos o número "3.0", como precisamos fazer uma previsão para três anos à frente. Além disso, você pode instalar carrapatos perto das configurações "mostrar equação no diagrama" e "colocar o valor do valor da aproximação (R ​​^ 2)" no diagrama. O último indicador exibe a qualidade da linha de tendência. Depois que as configurações forem feitas, clique no botão "Fechar".



4. A linha de tendência é construída e podemos determinar a quantidade aproximada de lucro em três anos. Como vemos, naquele tempo ela deve traduzir em 4500 mil rublos. O coeficiente R2, como mencionado acima, exibe a qualidade da linha de tendência. No nosso caso, o valor de R2 é de 0,89. Quanto maior o coeficiente, maior a credibilidade da linha. O valor máximo disso pode ser igual a 1. Considera-se que com o coeficiente de mais de 0,85 a linha de tendência é confiável.



5. Se você não estiver satisfeito com o nível de confiabilidade, você pode retornar à janela Formato da linha de tendência e selecionar qualquer outro tipo de aproximação. Você pode tentar todas as opções disponíveis para encontrar o mais preciso.

   

   Deve-se notar que uma previsão efetiva utilizando extrapolação através de uma linha de tendência pode ser se o período de previsão não exceda 30% dos períodos analisados ​​pelo período. Ou seja, ao analisar um período de 12 anos, não podemos fazer uma previsão efetiva para mais de 3-4 anos. Mas, mesmo neste caso, será relativamente confiável se durante esse tempo não haverá força maior ou o oposto de circunstâncias extremamente favoráveis ​​que não estavam em períodos anteriores.

Lição: Como construir uma linha de tendência no Excel

Método 2: Operador de Previsão

A extrapolação para dados tabulares pode ser feita através do recurso padrão da previsão do Excel. Este argumento refere-se à categoria de instrumentos estatísticos e tem a seguinte sintaxe:

= Prever (x; conhecedor_stations_y; valores conhecidos_x)

"X" é um argumento, o valor da função para a qual você precisa definir. No nosso caso, como argumento será o ano para o qual a previsão deve ser feita.

"Valores conhecidos y" - a base dos valores de função conhecidos. No nosso caso, em seu papel, a magnitude dos lucros por períodos anteriores.

"Valores conhecidos X" são argumentos que correspondem aos valores conhecidos da função. Em seu papel, temos a numeração dos anos, para as quais foram coletadas informações sobre os lucros dos anos anteriores.

Naturalmente, como um argumento não é necessariamente um segmento temporário. Por exemplo, eles podem ser uma temperatura, e a função da função pode ser o nível de expansão da água durante o aquecimento.

Ao calcular esse método, é usado um método de regressão linear.

Vamos analisar as nuances da aplicação do operador prediz em um exemplo específico. Pegue a mesma mesa. Precisamos descobrir a previsão de lucro para 2018.

1. Destacamos uma célula em branco na folha onde o resultado de processamento é planejado. Clique no botão "Colar Função".



2. Assistente abre. Na categoria "estatística" alocar o nome "prevei" e, em seguida, clique no botão "OK".



3. A janela Argumentos começa. No campo "X", especificamos o valor do argumento ao qual o valor da função precisa ser encontrado. No nosso caso, é 2018. Portanto, introduzimos o registro "2018". Mas é melhor especificar este indicador na célula na folha, e no campo "x" simplesmente dar um link para ele. Isso permitirá que no futuro automatize os cálculos e mude facilmente o ano.

   No campo "Valores conhecidos y", especificamos as coordenadas da coluna "Lucro Enterprise". Isso pode ser feito instalando o cursor no campo e, em seguida, subindo o botão esquerdo do mouse e realce a coluna apropriada na folha.

   Da mesma forma, no campo "Conhecido X valores", introduzimos o endereço do ano do ano com os dados do período anterior.

   Depois que todas as informações forem feitas, clique no botão "OK".



4. O operador calcula com base nos dados inseridos e exibe o resultado na tela. Para 2018, é planejado lucrar em torno de 4564,7 mil rublos. Com base na tabela resultante, podemos construir uma programação usando as ferramentas de criação de gráficos discutidas acima.



5. Se você alterar o ano na célula que foi usado para inserir o argumento, o resultado será alterado em conformidade e o cronograma será automaticamente atualizado. Por exemplo, de acordo com as previsões em 2019, a quantidade de lucro será de 4637,8 mil rublos.

Mas não se esqueça disso, como na construção da linha de tendência, o período de tempo até que o período previsto não deve exceder 30% do período total para o qual o banco de dados se acumulou.

Lição: Extrapolação no Excel.

Método 3: Tendência do Operador

Para prever, você pode usar outra função - a tendência. Também se refere à categoria de operadores estatísticos. Sua sintaxe se assemelha a sintaxe da ferramenta Predic e se parece com isso:

= Tendência (valores conhecidos_y; valores conhecidos_x; new_dation_x; [const])

Como vemos, os argumentos "valores y conhecidos" e "valores x conhecidos" correspondem plenamente aos elementos semelhantes do operador do PredSpot, e o argumento "Novos Valores X" corresponde ao argumento "X" da ferramenta anterior. Além disso, a tendência tem um argumento adicional da constante, mas não é obrigatória e usada apenas se houver fatores permanentes.

Este operador é usado de forma mais eficaz na presença de uma dependência de dependência linear.

Vamos ver como esta ferramenta funcionará todos com a mesma matriz de dados. Para comparar os resultados obtidos, o ponto de previsão determinará 2019.

1. Produzimos a designação da célula para exibir o resultado e executar o mestre das funções da maneira usual. Na categoria "estatística" encontramos e alocamos o nome "tendência". Clique no botão "OK".



2. Janela dos argumentos do operador A tendência é aberta. Nos "Valores conhecidos do Y", o método de coordenadas dos lucros da empresa já foi descrito acima. No campo "Valores conhecidos X", insira o endereço da coluna do ano. No campo "Novos Valores X", inserimos um link para a célula, onde o número do ano é em que a previsão deve ser especificada. No nosso caso, é 2019. O campo "constante" deixa vazio. Clique no botão "OK".



3. O operador processa os dados e exibe o resultado na tela. Como você pode ver, a quantidade de lucros previstos para 2019, calculada pelo método de dependência linear, será como no método anterior de cálculo, 4637,8 mil rublos.

Método 4: crescimento do operador

Outra função com a qual você pode prever em Excelee, é um crescimento do operador. Refere-se também ao grupo estatístico de instrumentos, mas, ao contrário dos anteriores, não aplica um método de dependência linear, mas exponencial. A sintaxe desta ferramenta parece:

= Crescimento (valores conhecidos_y; valores conhecidos_x; new_stations_x; [const])

Como vemos, os argumentos para esse recurso são exatamente repetidos a tendência dos argumentos do operador, então não vamos parar a segunda vez em sua descrição, mas passaremos imediatamente para o uso desta ferramenta na prática.

1. Alocamos a célula de saída e já costumamos causar as funções de funções. Na lista de operadores estatísticos, estamos à procura de uma cláusula "Growth", alocamos e clique no botão "OK".



2. A janela de argumento da função especificada é ativada. Entramos no campo desta janela Os dados são completamente semelhantes a como as entramos na janela Argumentos do operador a tendência. Depois que as informações forem feitas, clique no botão "OK".



3. O resultado do processamento de dados é exibido no monitor na célula especificada anteriormente. Como vemos, desta vez o resultado é de 4682,1 mil rublos. As diferenças dos resultados do processamento de dados pela tendência do operador são insignificantes, mas estão disponíveis. Isso se deve ao fato de que essas ferramentas aplicam diferentes métodos de cálculo: o método de dependência linear e o método de dependência exponencial.

Método 5: Linene do operador

O operador linear ao calcular o método de aproximação linear. Não é necessário ser confundido com o método de dependência linear usado pela ferramenta Tendency. Sua sintaxe tem esse tipo:

= Linene (valores conhecidos_y; valores conhecidos_x; new_stations_x; [const]; [estatística])

Os dois últimos argumentos são opcionais. Com os dois primeiros estamos familiarizados nos caminhos anteriores. Mas você provavelmente notou que não há argumento nesta função, indicando novos valores. O fato é que esta ferramenta determina apenas a mudança na quantidade de receita por unidade de período, que no nosso caso é de um ano, mas o resultado geral a ser calculado separadamente, adicionando o resultado do cálculo do operador linear, multiplicado por o número de anos.

1. Produzimos a seleção da célula na qual o cálculo será feito e executará as funções das funções. Alocamos o nome "linene" na categoria "Estatística" e clique no botão "OK".



2. No campo "Conhecido R valores", que abriu a janela dos argumentos, introduzam as coordenadas da coluna "Lucro Enterprise". No campo "Famoso X Valores", introduzimos o endereço da coluna "Ano". Os campos restantes são deixados vazios. Em seguida, clique no botão "OK".



3. O programa calcula e exibe o valor da tendência linear para a célula selecionada.



4. Agora temos que descobrir a magnitude do lucro projetado para 2019. Instale o sinal "=" em qualquer célula vazia na folha. Clique em uma célula, que contém o valor real dos lucros para o último ano do ano (2016). Coloque o sinal "+". Clique ainda na célula, que contém uma tendência linear previamente calculada. Nós colocamos o sinal "*". Desde o último ano do período estudado (2016) e o ano para fazer uma previsão (2019) encontra-se um termo de três anos, estabelecemos o número "3" na célula. Para fazer um cálculo clicando no botão Enter.

Como você pode ver, o valor de lucro projetado calculado pelo método de aproximação linear, em 2019 será de 4614,9 mil rublos.

Método 6: Operador LgrfPribl

A última ferramenta que olharemos será LGRFPRIBL. Este operador faz cálculos com base no método de aproximação exponencial. Sua sintaxe tem a seguinte estrutura:

= Lgrfpriblin (valores conhecidos_y; valores conhecidos_x; new_stations_x; [const]; [estatística])

Como você pode ver, todos os argumentos repetem completamente os elementos correspondentes da função anterior. O algoritmo de cálculo de previsão mudará um pouco. A função calculará a tendência exponencial que mostrará quantas vezes a quantidade de receita é alterada em um período, ou seja, para o ano. Precisamos encontrar a diferença nos lucros entre o último período real e o primeiro planejamento, multiplique-o pelo número de períodos planejados (3) e adicione ao resultado da soma do último período real.

1. Na lista de operadores do mestre das funções, alocamos o nome "LGRFPRIBL". Nós formamos um clique no botão "OK".



2. A janela Argumentos começa. Nele apresentamos os dados exatamente como eles fizeram ao usar a função linear. Clique no botão "OK".



3. O resultado da tendência exponencial é calculado e estendido para a célula designada.



4. Colocamos o sinal "=" em uma célula vazia. Abrimos colchetes e selecionamos a célula que contém o valor da receita para o último período real. Colocamos o sinal "*" e destacamos uma célula contendo uma tendência exponencial. Colocamos um sinal de menos e novamente clicamos no elemento no qual a quantidade de receita está no último período. Nós fechamos o suporte e impulsionamos os caracteres "* 3 +" sem citações. Mais uma vez, clique na mesma célula, que foi alocada pela última vez. Para realizar o cálculo, pressione o botão ENTER.

A quantidade prevista de lucros em 2019, que foi calculada pelo método de aproximação exponencial, será de 4639,2 mil rublos, o que é novamente não muito diferente dos resultados obtidos ao calcular métodos anteriores.

Lição: Outras funções estatísticas no Excel

Descobrimos quais métodos podem ser predicando no programa do Excel. Pode ser feito graficamente através do uso de uma linha de tendência e analítico - usando uma série de funções estatísticas internas. Como resultado de processamento de dados idênticos, esses operadores podem ser um resultado diferente. Mas não é surpreendente, uma vez que todos usam diferentes métodos de cálculo. Se a oscilação for pequena, todas essas opções aplicáveis ​​a um caso específico podem ser consideradas relativamente confiáveis.