Ferramentas de previsión en Microsoft Excel

A previsión é un elemento moi importante de case calquera campo de actividade, desde a economía ata a enxeñaría. Hai un gran número de software especializado nesta área. Por desgraza, non todos os usuarios saben que o habitual procesador de follas de cálculo Excel ten no seu arsenal ferramentas de previsión, que non son moi inferiores aos programas profesionais na súa eficiencia. Descubrimos cales son estas ferramentas e como facer unha previsión na práctica.

Procedemento de previsión

O obxectivo de calquera previsión é identificar a tendencia actual e determinar o resultado esperado en relación ao obxecto estudado nun determinado momento do futuro.

Método 1: liña de tendencias

Un dos tipos máis populares de previsión gráfica en Excel é a extrapolación construíndo unha liña de tendencias.

Tratemos de predicir a cantidade de beneficios da empresa en 3 anos a partir dos datos deste indicador dos últimos 12 anos.

1. Construímos un gráfico de dependencia baseado en datos tabulares consistentes en argumentos e valores de función. Para iso, selecciona a área da táboa e, a continuación, está na lapela Inserir, fai clic na icona do tipo de gráfico desexado, que se atopa no bloque Gráficos. Logo seleccionamos o tipo adecuado para unha situación concreta. O mellor é escoller un gráfico de dispersión. Pode escoller outra vista, pero logo, para que os datos se mostren correctamente, terá que realizar a edición, en particular, eliminar a liña do argumento e seleccionar outra escala do eixe horizontal.



2. Agora necesitamos construír unha liña de tendencias. Facemos clic co botón dereito sobre calquera dos puntos do diagrama. No menú contextual activado, detén a selección do elemento Engade liña de tendencia.



3. Ábrese a xanela de formato da liña de tendencias. Nel podes escoller un dos seis tipos de aproximación:
   • Lineal;
   • Logarítmico;
   • Exponencial;
   • Potencia;
   • Polinomio;
   • Filtrado lineal.

   Comezamos escollendo unha aproximación lineal.

   No bloque de configuracións "Previsión" no campo "Adiante a" establecer o número "3,0", xa que necesitamos facer unha previsión con tres anos de antelación. Ademais, pode marcar a caixa xunto á configuración. "Mostrar ecuación no diagrama" e "Coloque o valor de confianza aproximado (R ^ 2) no diagrama". O último indicador mostra a calidade da liña de tendencias. Despois de facer a configuración, faga clic no botón Pechar.



4. A liña de tendencias está construída e a partir dela podemos determinar a cantidade aproximada de beneficio en tres anos. Como vemos, naquel momento debería superar os 4500 mil rublos. Coeficiente R2Como se mencionou anteriormente, mostra a calidade da liña de tendencias. No noso caso, o valor R2 compón 0,89. Canto maior sexa o coeficiente, maior será a fiabilidade da liña. O seu valor máximo pode ser igual 1. Xeralmente acéptase que cun coeficiente anterior 0,85 a liña de tendencias é fiable.



5. Se o nivel de confianza non se adapta a vostede, pode volver á xanela do formato da liña de tendencias e seleccionar calquera outro tipo de aproximación. Podes probar todas as opcións dispoñibles para atopar as máis precisas.

   

   Cómpre salientar que a previsión mediante extrapolación a través da liña de tendencia pode ser efectiva se o período de previsión non supera o 30% da base de períodos analizada. É dicir, ao analizar un período de 12 anos, non podemos facer unha previsión efectiva durante máis de 3-4 anos. Pero incluso neste caso, será relativamente fiable se durante este tempo non haberá forza maior ou, pola contra, circunstancias extremadamente favorables, que non foron en períodos anteriores.

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

Método 2: o operador de previsión

A extrapolación para datos tabulares pódese realizar mediante a función Excel estándar PREDICIÓN. Este argumento pertence á categoría de ferramentas estatísticas e ten a seguinte sintaxe:

= PREDICTO (X; valores_coñecidos; valores coñecidos_x_)

"X" é un argumento para o que debe determinarse o valor da función. No noso caso, o argumento será o ano para o que se debería facer a previsión.

Valores coñecidos - base dos valores de función coñecidos. No noso caso, o seu papel está xogado pola cantidade de beneficios de períodos anteriores.

Valores x coñecidos son os argumentos aos que corresponden os valores coñecidos da función. No seu papel, temos a numeración dos anos nos que se recolleu información sobre o beneficio de anos anteriores.

Por suposto, o argumento non debe ser un período de tempo. Por exemplo, pode ser a temperatura e o valor da función pode ser o nivel de expansión da auga cando se quenta.

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

Vexamos os matices de uso do operador PREDICIÓN sobre un exemplo concreto. Toma a mesa enteira. Haberá que coñecer a previsión de beneficios para 2018.

1. Seleccione unha cela baleira na folla onde pretende mostrar o resultado do procesamento. Fai clic no botón "Función de inserción".



2. Abre Asistente de características. En categoría "Estatística" selecciona o nome "PREDICIÓN"e logo faga clic no botón "Está ben".



3. A xanela do argumento comeza. No campo "X" indique o valor do argumento ao que desexa atopar o valor da función. No noso caso, este é o 2018. Polo tanto, escribimos "2018". Pero é mellor indicar este indicador nunha cela da folla e no campo "X" basta dar unha ligazón a ela. Isto permitirá no futuro automatizar os cálculos e, se é necesario, cambiar facilmente o ano.

   No campo Valores coñecidos especifique as coordenadas da columna "Beneficio da empresa". Pódese facer colocando o cursor no campo, e despois mantén premido o botón esquerdo do rato e resaltando a columna correspondente na folla.

   Do mesmo xeito no campo Valores x coñecidos introduza o enderezo da columna "Ano" con datos do período pasado.

   Despois de introducir toda a información, faga clic no botón "Está ben".



4. O operador calcula en función dos datos introducidos e mostra o resultado na pantalla. Para 2018 está previsto que se produzan na rexión 4.564,7 mil rublos. Con base na táboa resultante, podemos construír un gráfico usando as ferramentas de gráficos discutidas anteriormente.



5. Se cambia o ano na cela que se utilizou para introducir o argumento, o resultado cambiará de acordo e a programación actualizarase automaticamente. Por exemplo, segundo as previsións en 2019, a cantidade de beneficio será de 4637,8 mil rublos.

Pero non esquezas que, do mesmo xeito que coa construción da liña de tendencias, o período anterior ao período previsto non debería superar o 30% do período total para o que se acumulou a base de datos.

Lección: Extrapolación en Excel

Método 3: operador TREND

Para a previsión, pode usar outra función - TENDENCIAS. Pertence tamén á categoría de operadores estatísticos. A súa sintaxe é moi similar á sintaxis de ferramentas PREDICIÓN e parece así:

= TREND (valores coñecidos_y; valores coñecidos_x; novos_valores_x; [const])

Como podes ver, os argumentos Valores coñecidos e Valores x coñecidos corresponden completamente a elementos similares do operador PREDICIÓN, e o argumento "Novos valores x" coincide co argumento "X" ferramenta anterior Ademais, TENDENCIAS hai un argumento adicional "Constante", pero é opcional e úsase só se hai factores constantes.

Este operador é o máis eficazmente usado ante unha dependencia lineal da función.

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

1. Designamos a cela para mostrar o resultado e executar Asistente de características do xeito habitual. En categoría "Estatística" busque e resalte o nome "TENDER". Fai clic no botón "Está ben".



2. Abre a xanela do argumento do operador TENDENCIAS. No campo Valores coñecidos polo método descrito arriba, entramos nas coordenadas da columna "Beneficio da empresa". No campo Valores x coñecidos introduza o enderezo da columna "Ano". No campo "Novos valores x" introducimos a ligazón á cela onde está situado o número do ano para o que se debe indicar a previsión. No noso caso, este é o 2019. O campo "Constante" déixao en branco. Fai clic no botón "Está ben".



3. O operador procesa os datos e mostra o resultado na pantalla. Como podes ver, o importe do beneficio proxectado para 2019, calculado polo método de dependencia lineal, será, como no método de cálculo anterior, de 4637,8 mil rublos.

Método 4: operador de crecemento

Outra función que pode empregarse para a previsión en Excel é o operador GROWTH. Tamén pertence ao grupo estatístico de ferramentas, pero a diferenza das anteriores, ao calculalo, non usa o método de dependencia lineal, senón o exponencial. A sintaxis desta ferramenta é a seguinte:

= GROWTH (valores coñecidos_y; valores coñecidos_x; novos_valores_x; [const])

Como podes ver, os argumentos desta función repiten exactamente os argumentos do operador TENDENCIAS, polo que non imos afondar na súa descrición por segunda vez, senón que procedamos inmediatamente á aplicación práctica desta ferramenta.

1. Seleccionamos a cela para producir o resultado e chamámola do xeito habitual Asistente de características. Na lista de operadores estatísticos, busque o elemento ROSTRO, selecciónea e fai clic no botón "Está ben".



2. A xanela do argumento da función anterior está activada. Introduza os datos nos campos desta xanela do mesmo xeito que os introducimos na xanela de argumentos do operador TENDENCIAS. Despois de introducir a información, faga clic no botón "Está ben".



3. O resultado do procesamento de datos móstrase no monitor na cela indicada anteriormente. Como podes ver, esta vez o resultado é de 4682,1 mil rublos. Diferenzas dos resultados do procesamento de datos do operador TENDENCIAS insignificantes, pero están dispoñibles. Isto débese a que estas ferramentas usan diferentes métodos de cálculo: o método de dependencia lineal e o método de dependencia exponencial.

Método 5: operador LINEAR

Operador LIÑA no cálculo usa o método de aproximación lineal. Non se debe confundir co método de dependencia lineal empregado pola ferramenta. TENDENCIAS. A súa sintaxis é a seguinte:

= LINE (valores coñecidos_y; valores coñecidos_x; novos_valores_x; [const]; [estatísticas])

Os dous últimos argumentos son opcionais. Cos dous primeiros, coñecemos os métodos anteriores. Pero probablemente notou que non hai ningún argumento nesta función que apunte a novos valores. O certo é que esta ferramenta só determina o cambio de ingresos por unidade de período, que no noso caso é igual a un ano, pero temos que calcular o resultado total por separado, engadindo o resultado do cálculo do operador ao último valor de beneficio real. LIÑAveces o número de anos.

1. Seleccionamos a cela na que se realizará o cálculo e executamos o Asistente de funcións. Seleccione o nome LINEIN na categoría "Estatística" e fai clic no botón "Está ben".



2. No campo Valores coñecidos, a xanela aberta de argumentos, introduza as coordenadas da columna "Beneficio da empresa". No campo Valores x coñecidos introduza o enderezo da columna "Ano". Os campos restantes quedan en branco. Despois faga clic no botón "Está ben".



3. O programa calcula e mostra o valor de tendencia lineal na cela seleccionada.



4. Agora temos que descubrir o tamaño do beneficio proxectado para 2019. Establece o signo "=" a calquera cela baleira da folla. Facemos clic na cela que contén a cantidade real de beneficios do último ano estudado (2016). Poñemos un cartel "+". A continuación, faga clic na cela que contén a tendencia lineal previamente calculada. Poñemos un cartel "*". Xa que entre o último ano do período de estudo (2016) e o ano para o que desexa facer unha previsión (2019), hai un período de tres anos, fixamos o número na cela "3". Para facer un cálculo fai clic no botón Introduza.

Como podes ver, a marxe de beneficio proxectada calculada polo método de aproximación lineal en 2019 ascenderá a 4.614,9 mil rublos.

Método 6: operador LGRFPPRIBLE

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

= LGRFPRIBLE (valores coñecidos_y; valores coñecidos_x; novos_valores_x; [const]; [estatísticas])

Como podes ver, todos os argumentos repiten por completo os elementos correspondentes da función anterior. O algoritmo de cálculo das previsións cambiará lixeiramente. A función calcula a tendencia exponencial, que mostra cantas veces cambiará a cantidade de ingresos por un período, é dicir, por un ano. Haberá que atopar a diferenza de beneficio entre o último período real e o primeiro planificado, multiplicalo polo número de períodos previstos (3) e engade ao resultado a suma do último período real.

1. Na lista de operadores do Asistente de funcións, seleccione o nome LGRFPPRIBL. Fai clic no botón "Está ben".



2. A xanela do argumento comeza. Nel, introducimos os datos exactamente como o fixemos, usando a función LIÑA. Fai clic no botón "Está ben".



3. O resultado da tendencia exponencial calcúlase e móstrase na cela designada.



4. Poñemos un cartel "=" nunha cela baleira. Abre os parénteses e selecciona a cela que contén o valor de ingresos do último período real. Poñemos un cartel "*" e selecciona a cela que contén a tendencia exponencial. Colocamos un signo menos e facemos de novo clic no elemento no que se atopa o valor de ingresos do último período. Pecha o paréntese e conduce nos caracteres "*3+" sen comiñas. De novo, fai clic na mesma cela que foi seleccionada por última vez. Para realizar o cálculo, faga clic no botón Introduza.

A cantidade de beneficio proxectada en 2019, que se calculou mediante o método de aproximación exponencial, será de 4639,2 mil rublos, o que de novo non difire moito dos resultados obtidos no cálculo anterior.

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

Descubrimos como facer prediccións no programa Excel. Isto pódese facer de forma gráfica mediante o uso dunha liña de tendencias e de forma analítica cunha serie de funcións estatísticas incorporadas. Como resultado de procesar datos idénticos por parte destes operadores, pódese obter un resultado diferente. Pero isto non é sorprendente, xa que todos eles usan diferentes métodos de cálculo. Se a flutuación é pequena, todas estas opcións aplicables a un caso particular poden considerarse relativamente fiables.