Previsión en Excel: 6 formas probadas

A previsión é un elemento moi importante de case calquera campo de actividade, que vai desde a economía e a finalización coa enxeñaría. Hai unha gran cantidade de software especializado nesta dirección. Desafortunadamente, non todos os usuarios saben que o habitual procesador de mesa de Excel ten nas súas ferramentas de arsenal para predecir, que na súa eficacia son pouco inferiores aos programas profesionais. Imos descubrir que tipo de ferramentas son e como facer un pronóstico na práctica.

Previsión de procedemento

O obxectivo de calquera predición é identificar a tendencia actual e a definición do resultado previsto en relación ao obxecto que se está a estudar nun determinado punto no futuro.

Método 1: liña de tendencia

Unha das especies de predición gráfica máis populares en Excele é unha extrapolación feita construíndo unha liña de tendencia.

Intentemos predecir o importe dos beneficios da compañía despois de 3 anos con base en datos sobre este indicador para os últimos 12 anos.

1. Estamos construíndo un calendario baseado en datos tabulares que consiste en argumentos e valores de función. Para iso, seleccione a área da táboa e, a continuación, mentres está na pestana "Inserir", faga clic sobre o ícono do tipo desexado de diagrama, que está no bloque de "gráfico". A continuación, escolla o tipo axeitado para unha situación particular. É mellor elixir un diagrama de punto. Pode escoller outro aspecto, pero entón os datos móstranse correctamente, terá que editar, en particular, eliminar a liña de argumento e seleccionar outra escala do eixe horizontal.



2. Agora necesitamos construír unha liña de tendencia. Facemos un clic co botón dereito do rato sobre calquera dos puntos do diagrama. No menú contextual activado, detén a selección no parágrafo "Engadir a liña de tendencia".



3. Abre a xanela de formato de liña de tendencia. Pode elixir un dos seis tipos de aproximación:
   • Linear;
   • Logarítmico;
   • Exponencial;
   • Poder;
   • Polinomio;
   • Filtración lineal.

   Primeiro escolle unha aproximación lineal.

   No bloque de configuracións "previsión" no campo "Avanzar a", fixamos o número "3.0", xa que debemos facer unha previsión durante tres anos por diante. Ademais, pode instalar as garrapatas preto da configuración "Mostrar a ecuación no diagrama" e "poñer o valor do valor da aproximación (R ^ 2)" no diagrama. O último indicador mostra a calidade da liña de tendencia. Despois de que se realicen a configuración, faga clic no botón "Pechar".



4. A liña de tendencia está construída e podemos determinar a cantidade aproximada de beneficio en tres anos. Como vemos, por ese tempo debería traducir en 4500 mil rublos. O coeficiente R2, como se mencionou anteriormente, mostra a calidade da liña de tendencia. No noso caso, o valor de R2 é de 0,89. Canto maior sexa o coeficiente, maior será a credibilidade da liña. O valor máximo de que pode ser igual a 1. Crese que co coeficiente de máis de 0,85 a liña de tendencia é fiable.



5. Se non está satisfeito co nivel de fiabilidade, pode volver á xanela de formato de liña de tendencia e seleccionar calquera outro tipo de aproximación. Podes probar todas as opcións dispoñibles para atopar o máis preciso.

   

   Nótese que unha previsión efectiva usando a extrapolación a través dunha liña de tendencia pode ser se o período de predición non supera o 30% do período analizado por períodos. É dicir, ao analizar un período de 12 anos, non podemos facer unha previsión efectiva por máis de 3-4 anos. Pero aínda neste caso, será relativamente fiable se durante este tempo non haberá forza maior ou o contrario de circunstancias extremadamente favorables que non estaban en períodos anteriores.

Lección: Como construír unha liña de tendencia en Excel

Método 2: Operador de predición

A extrapolación dos datos tabulares pódese realizar a través da característica estándar da predición de Excel. Este argumento refírese á categoría de instrumentos estatísticos e ten a seguinte sintaxe:

= Prever (x; coñecido_stations_y; valores coñecidos_x)

"X" é un argumento, o valor da función para a que ten que definir. No noso caso, como argumento será o ano en que se debe facer a previsión.

"Valores coñecidos e" - a base dos valores de función coñecidos. No noso caso, no seu papel, a magnitude dos beneficios por períodos anteriores.

Os "valores coñecidos X" son argumentos que coinciden cos valores coñecidos da función. No seu papel, temos a numeración dos anos, para a que se recollían información sobre os beneficios dos anos anteriores.

Por suposto, como argumento non necesariamente é un segmento temporal. Por exemplo, poden ser unha temperatura e a función da función pode ser o nivel de expansión de auga durante a calefacción.

Ao calcular este método, úsase un método de regresión lineal.

Analicemos os matices da aplicación do operador prevé nun exemplo específico. Tome a mesma mesa. Necesitaremos descubrir a previsión de beneficio para 2018.

1. Destacamos unha célula en branco na folla onde se planea o resultado do procesamento. Fai clic no botón "Función de pasta".



2. Abre o asistente. Na categoría "Estatística" asignar o nome "prevé" e, a continuación, prema no botón "Aceptar".



3. Comeza a xanela de argumentos. No campo "X", especificamos o valor do argumento ao que se debe atopar o valor da función. No noso caso é 2018. Polo tanto, presentamos o rexistro "2018". Pero é mellor especificar este indicador na cela da folla, e no campo "X" simplemente dá unha ligazón a ela. Isto permitirá ao futuro automatizar os cálculos e cambiar facilmente o ano.

   No campo "Valores coñecidos e", especificou as coordenadas da columna "Beneficio Enterprise". Isto pódese facer instalando o cursor no campo e, a continuación, subir o botón esquerdo do rato e resaltar a columna adecuada na folla.

   Do mesmo xeito, no campo "coñecidos de X valores", presentamos a dirección do ano do ano cos datos do período pasado.

   Despois de que se realice toda a información, faga clic no botón "Aceptar".



4. O operador calcula a base dos datos introducidos e mostra o resultado na pantalla. Para 2018, está previsto beneficiarse ao redor de 4564,7 mil rublos. Con base na táboa resultante, podemos construír un calendario usando as ferramentas de creación de gráficos que foron discutidos anteriormente.



5. Se cambias o ano na célula que se usou para ingresar ao argumento, o resultado cambiará de conformidade e a programación será automaticamente actualizada. Por exemplo, segundo as previsións en 2019, a cantidade de beneficio será de 4637,8 mil rublos.

Pero non hai que esquecer que, como na construción da liña de tendencia, o tempo ata o período previsto non debe exceder o 30% do período total para o que se acumulou a base de datos.

Lección: Extrapolación en Excel.

Método 3: Tendencia do operador

Para predecir, pode usar outra función: a tendencia. Tamén se refire á categoría de operadores estatísticos. A súa sintaxe se asemella en gran parte a sintaxe da ferramenta de deédicos e parece así:

= Tendencia (valores coñecidos; coñecidos valores_x; new_dation_x; [const])

Como podemos ver, os argumentos "coñecidos valores y" e "valores x coñecidos" corresponden íntegramente aos elementos similares do Operador Predspot, e as corresponde argumento "novos valores X" para o argumento de "X" da ferramenta anterior. Ademais, a tendencia ten un argumento adicional da constante, pero non é obrigatorio e só se usa se hai factores permanentes.

Este operador úsase de forma máis efectiva na presenza dunha dependencia de dependencia lineal.

Vexamos como funcionará esta ferramenta coa mesma matriz de datos. Para comparar os resultados obtidos, o punto de previsión determinará 2019.

1. Producimos a designación da cela para mostrar o resultado e executar a función Master de xeito habitual. Na categoría "Estatística" atopamos e reservamos o nome de "Trend". Fai clic no botón "Aceptar".



2. Argumentos do operador Xanela A tendencia ábrese. Nos "valores coñecidos de Y", o método de coordenadas dos beneficios da compañía xa foi descrito anteriormente. No campo "Valores coñecidos X", introduza a dirección da columna do ano. No campo "novos valores X" introducimos unha ligazón á cela, onde o número do ano está en que se debe especificar a previsión. No noso caso, é 2019. O campo "constante" deixa baleiro. Fai clic no botón "Aceptar".



3. O operador procesa os datos e mostra o resultado na pantalla. Como podes ver, a cantidade de beneficios previstos para 2019, calculada polo método de dependencia lineal, será como no método anterior de cálculo, 4637,8 mil rublos.

Método 4: Crecemento do operador

Outra función coa que podes predecir en Excele, é un crecemento do operador. Tamén se refire ao grupo estatístico de instrumentos, pero, a diferenza dos anteriores, non aplica un método de dependencia lineal, senón exponencial. A sintaxe desta ferramenta parece:

= Crecemento (valores coñecidos; coñecidos valores_x; new_stations_x; [const])

Como vemos, os argumentos desta función son repetidos exactamente a tendencia dos argumentos do operador, polo que non imos deter a segunda vez na súa descrición, pero inmediatamente pasaremos ao uso desta ferramenta na práctica.

1. Asignamos a célula de saída e xa adoitan causar as funcións das funcións. Na lista de operadores estatísticos, estamos a buscar unha cláusula de "crecemento", reservámola e prema no botón "Aceptar".



2. A xanela do argumento da función especificada está activada. Entramos no campo desta xanela, os datos son completamente similares a como ingresamos nas argumentos do operador da xanela da tendencia. Despois de que se realice a información, faga clic no botón "Aceptar".



3. O resultado do procesamento de datos móstrase no monitor na cela previamente especificada. Como vemos, esta vez o resultado é de 4682,1 mil rublos. As diferenzas dos resultados do procesamento de datos pola tendencia do operador son insignificantes, pero están dispoñibles. Isto débese ao feito de que estas ferramentas apliquen diferentes métodos de cálculo: o método de dependencia lineal eo método de adicción exponencial.

Método 5: operador Linene

O operador lineal ao calcular usa o método de aproximación lineal. Non é necesario confundirse co método de dependencia lineal utilizado pola ferramenta de tendencia. A súa sintaxe ten este tipo:

= Linene (coñecidas valores_y; valores coñecidos_x; new_stations_x; [const]; [estatísticas])

Os dous últimos argumentos son opcionais. Cos dous primeiros estamos familiarizados nas formas anteriores. Pero probablemente notou que non hai argumento nesta función, indicando novos valores. O feito é que esta ferramenta determina só o cambio na cantidade de ingresos por unidade de período, que no noso caso é dun ano, pero o resultado xeral a calcular por separado, engadindo o resultado do cálculo do operador lineal, multiplicado por o número de anos.

1. Producimos a selección da célula na que se fará o cálculo e executará as funcións das funcións. Restauramos o nome "Linene" na categoría "Estatística" e prema no botón "Aceptar".



2. No campo "coñecidos valores r", que abriu a xanela dos argumentos, introduciu as coordenadas da columna "Beneficio Enterprise". No campo "Os valores de X Famosos", introducimos a dirección da columna "Ano". Os campos restantes quedan baleiros. A continuación, faga clic no botón "Aceptar".



3. O programa calcula e mostra o valor da tendencia lineal á cela seleccionada.



4. Agora temos que descubrir a magnitude do beneficio proxectado para 2019. Instala o botón "=" En calquera cela baleira na folla. Fai clic nunha cela, que contén o valor real dos beneficios do último ano do ano (2016). Pon o sinal "+". Fai clic máis na cela, que contén unha tendencia lineal calculada previamente. Poñemos o sinal "*". Xa que entre o último ano do período estudado (2016) eo ano para facer unha previsión (2019) atópase un prazo de tres anos, fixamos o número "3" na cela. Para facer un cálculo premendo no botón Intro.

Como podes ver, o valor de beneficio proxectado calculado polo método de aproximación lineal, en 2019 será de 4614,9 mil rublos.

Método 6: operador LGRFPRIBL

A última ferramenta que imos ver será LGRFPRIBL. Este operador fai cálculos en función do método de aproximación exponencial. A súa sintaxe ten a seguinte estrutura:

= LGRFPRIBLIN (valores coñecidos; coñecidos valores_x; new_stations_x; [const]; [estatísticas])

Como podes ver, todos os argumentos repiten completamente os elementos correspondentes da función anterior. O algoritmo de cálculo de previsión cambiará un pouco. A función calculará a tendencia exponencial que mostrará cantas veces a cantidade de ingresos cambia nun só período, é dicir, para o ano. Necesitaremos atopar a diferenza de beneficios entre o último período real e a primeira planificación, multiplicala polo número de períodos planificados (3) e engadir ao resultado da suma do último período real.

1. Na lista de operadores do Máster de Funcións, destinamos o nome "LGRFPRIBL". Facemos un clic no botón "Aceptar".



2. Comeza a xanela de argumentos. Nela introducimos os datos exactamente como fixeron ao usar a función lineal. Fai clic no botón "Aceptar".



3. O resultado da tendencia exponencial calcúlase e se estende á célula designada.



4. Poñemos o sinal "=" nunha cela baleira. Abrimos corchetes e seleccionamos a cela que contén o valor dos ingresos para o último período real. Poñemos o sinal "*" e resaltamos unha cela que contén unha tendencia exponencial. Poñemos un sinal de menos e de novo premer no elemento no que a cantidade de ingresos está no último período. Pechamos o soporte e diriximos os personaxes "* 3 +" sen comiñas. De novo, fai clic na mesma cela, que foi asignada por última vez. Para realizar o cálculo, prema o botón Intro.

A cantidade prevista de beneficios en 2019, que foi calculada polo método de aproximación exponencial, será de 4639,2 mil rublos, que é de novo non moi diferente dos resultados obtidos ao calcular os métodos previos.

Lección: Outras funcións estatísticas en Excel

Descubrimos que métodos poden estar prevendo no programa de Excel. Pódese facer gráficamente a través do uso dunha liña de tendencia e analítica - usando unha serie de funcións estatísticas incorporadas. Como resultado do procesamento de datos idénticos, estes operadores poden resultar ser un resultado diferente. Pero non é sorprendente, xa que todos usan diferentes métodos de cálculo. Se a oscilación é pequena, todas estas opcións aplicables a un caso específico poden considerarse relativamente fiables.