Método de aproximación en Microsoft Excel

Entre os distintos métodos de predición, non se pode diferenciar a aproximación. Usándoo, pode facer estimacións aproximadas e calcular os indicadores planificados substituíndo os obxectos orixinais por outros máis sinxelos. En Excel, existe tamén a posibilidade de usar este método para a previsión e a análise. Vexamos como se pode aplicar este método no programa especificado con ferramentas incorporadas.

Aproximación

O nome deste método vén da palabra latina proxima - "máis próxima", aproximándose simplificando e suavizando os indicadores coñecidos, converténdoos nunha tendencia que é a súa base. Pero este método pode usarse non só para a previsión, senón tamén para estudar os resultados existentes. Ao final, a aproximación é, de feito, unha simplificación dos datos da fonte e unha versión simplificada é máis fácil de explorar.

A principal ferramenta coa que se realiza o suavizado en Excel é a construción dunha liña de tendencias. A conclusión é que, en función dos indicadores existentes, estase a completar un gráfico de funcións para períodos futuros. O propósito principal da liña de tendencias, como podería supor, é facer previsións ou identificar unha tendencia xeral.

Pero pódese construír usando un dos cinco tipos de aproximación:

• Lineal;
• Exponencial;
• Logarítmico;
• Polinomio;
• Potencia

Consideramos cada unha das opcións con máis detalle por separado.

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

Método 1: suavización lineal

En primeiro lugar, vexamos a opción de aproximación máis sinxela, é dicir, empregar unha función lineal. Abondaremos nela con máis detalle, xa que exporemos os puntos xerais característicos doutros métodos, a saber, a construción dun calendario e algúns outros matices nos que non afondaremos ao considerar as seguintes opcións.

En primeiro lugar, construiremos un gráfico, sobre a base do cal levaremos a cabo o procedemento de suavización. Para elaborar un calendario, tomamos unha táboa na que se indica o custo mensual dunha unidade de produción producida pola empresa e o beneficio correspondente nun determinado período. A función gráfica que construiremos reflectirá a dependencia do aumento do beneficio da diminución do custo de produción.

1. Para debuxar, primeiro, selecciona as columnas "Custo unitario" e Beneficio. Despois diso, vaia á lapela Inserir. A continuación, faga clic no botón da cinta na caixa de ferramentas Gráficos "Spot". Na lista que se abre, seleccione o nome "Spot con curvas e marcadores suaves". É este tipo de gráficos o máis adecuado para traballar cunha liña de tendencias e, polo tanto, para aplicar o método de aproximación en Excel.



2. O horario está construído.



3. Para engadir unha liña de tendencias, selecciónea facendo clic co botón dereito do rato. Aparece un menú contextual. Escolle o elemento nel "Engade unha liña de tendencia ...".

   

   Hai outra opción para engadila. Nun grupo adicional de pestanas na cinta "Traballar con gráficos" desprácese ata a lapela "Maquetación". Máis no bloque de ferramentas "Análise" fai clic no botón Liña de tendencias. Ábrese a lista. Dado que necesitamos aplicar unha aproximación lineal, seleccionamos entre as posicións presentadas "Aproximación lineal".



4. Se non obstante elixiu a primeira opción con engadir accións a través do menú contextual, abrirase unha xanela de formato.

   No bloque de parámetros "Construír unha liña de tendencias (aproximación e suavización)" configure o interruptor na posición "Lineal".
   Se o desexa, pode marcar a caixa xunto á posición "Mostrar ecuación no diagrama". Despois diso, mostrarase no diagrama a ecuación da función de alisado.

   Tamén no noso caso, para comparar diferentes opcións de aproximación, é importante marcar a caixa xunto "Pon no gráfico o valor da aproximación fiable (R ^ 2)". Este indicador pode variar de 0 antes 1. Canto maior sexa, mellor será a aproximación (máis fiable). Crese que co valor deste indicador 0,85 e superior, o alisado pode considerarse fiable, pero se o indicador é menor, entón non.

   Despois de completar todas as configuracións anteriores. Fai clic no botón Pecharsituado na parte inferior da xanela.



5. Como podes ver, a liña de tendencias móstrase na gráfica. Cunha aproximación lineal, indícase cunha recta negra. O tipo de suavizado especificado pódese empregar nos casos máis sinxelos cando os datos cambian bastante rápido e a dependencia do valor da función do argumento é evidente.

O alisado que se usa neste caso descríbese coa seguinte fórmula:

y = ax + b

No noso caso particular, a fórmula toma a seguinte forma:

y = -0.1156x + 72.255

O valor da precisión da aproximación é igual a 0,9418, que é un resultado bastante aceptable que caracteriza a suavización como fiable.

Método 2: Aproximación Exponencial

Vexamos agora o tipo de aproximación exponencial en Excel.

1. Para cambiar o tipo da liña de tendencias, selecciónea facendo clic co botón dereito do rato e selecciona o elemento no menú emerxente. "O formato da liña de tendencias ...".



2. Despois, a xanela do formato familiar comeza. No bloque de selección de tipo de aproximación, configure a opción "Exponencial". A configuración restante seguirá sendo a mesma que no primeiro caso. Fai clic no botón Pechar.



3. Despois diso, a liña de tendencias representarase no gráfico. Como podes ver, ao usar este método, ten unha forma lixeiramente curva. Neste caso, o nivel de confianza é 0,9592, que é maior que cando se usa aproximación lineal. O método exponencial úsase mellor cando os valores cambian rapidamente e logo adoptan unha forma equilibrada.

A forma xeral da función de alisado é a seguinte:

y = ser ^ x

onde e é a base do logaritmo natural.

No noso caso particular, a fórmula tomou a seguinte forma:

y = 6282,7 * e ^ (- 0,012 * x)

Método 3: suavización logarítmica

Agora toca considerar o método de aproximación logarítmica.

1. Do mesmo xeito que a época anterior, lanzamos a ventá de formato de liña de tendencias a través do menú contextual. Axuste o interruptor á posición "Logarítmica" e fai clic no botón Pechar.



2. Hai un procedemento para construír unha liña de tendencias cunha aproximación logarítmica. Como no caso anterior, esta opción úsase mellor cando inicialmente os datos cambian rapidamente e logo teñen un aspecto equilibrado. Como podes ver, o nivel de confianza é de 0,946. Isto é superior ao empregar o método lineal, pero inferior á calidade da liña de tendencia con suavización exponencial.

En xeral, a fórmula de alisado semella:

y = a * ln (x) + b

onde ln é o valor do logaritmo natural. De aí o nome do método.

No noso caso, a fórmula toma a seguinte forma:

y = -62.81ln (x) +404,96

Método 4: suavización polinómica

Chegou o momento de considerar o método de suavización polinómica.

1. Vai á xanela do formato de liña de tendencias, como se fixo máis dunha vez. En bloque "Construíndo unha liña de tendencias" configure o interruptor na posición "Polinomio". Á dereita deste elemento atópase un campo "Titulación". Ao escoller un valor "Polinomio" vólvese activa. Aquí pode especificar calquera valor de potencia 2 (por defecto) a 6. Este indicador determina o número de máximos e mínimos da función. Ao instalar un polinomio de segundo grao, só se describe un máximo e ao instalar un polinomio de sexto grao pódense describir ata cinco máximos. En primeiro lugar, deixemos a configuración predeterminada, é dicir, indicaremos o segundo grao. Deixamos o resto de configuracións o mesmo que as fixamos nos métodos anteriores. Fai clic no botón Pechar.



2. A liña de tendencias mediante este método está representada. Como podes ver, é aínda máis curvo que cando se emprega unha aproximación exponencial. O nivel de confianza é maior que con calquera dos métodos utilizados anteriormente, e é 0,9724.

   

   Este método pódese aplicar con máis éxito se os datos son variables constantemente. A función que describe este tipo de suavización semella:

   y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n

   No noso caso, a fórmula tomou a seguinte forma:

   y = 0.0015 * x ^ 2-1.7202 * x + 507.01

3. Cambiamos agora o grao de polinomios para ver se o resultado será diferente. Volvemos á xanela do formato. Deixamos o polinomio de aproximación, pero fronte a el, na xanela de grao, fixamos o máximo valor posible 6.



4. Como podes ver, despois disto, a nosa liña de tendencias tomou a forma dunha curva pronunciada, na que o número de máximos é seis. O nivel de confianza aumentou aínda máis, ascendendo a 0,9844.

A fórmula que describe este tipo de suavización ten a seguinte forma:

y = 8E-08x ^ 6-0,0003x ^ 5 + 0,3725x ^ 4-269,33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09

Método 5: suavización de potencia

En conclusión, consideramos o método de aproximación de potencia ao dereito en Excel.

1. Pasamos á fiestra Formato de liña de tendencia. Estableza a posición do tipo de interruptor de suavización "Potencia". A visualización da ecuación e do nivel de confianza, coma sempre, continúa. Fai clic no botón Pechar.



2. O programa forma unha liña de tendencia. Como podes ver, no noso caso trátase dunha liña con lixeira curva. O nivel de confianza é 0,9618, que é unha taxa bastante alta. De todos os métodos anteriores, o nivel de confianza foi maior só ao usar o método polinómico.

Este método úsase efectivamente en casos de cambio intensivo de datos de función. É importante considerar que esta opción é aplicable só a condición de que a función e o argumento non acepten valores negativos ou cero.

A fórmula xeral que describe este método ten a seguinte forma:

y = bx ^ n

No noso caso particular, semella este:

y = 6E + 18x ^ (- 6.512)

Como podes ver, ao usar os datos específicos que empregamos como exemplo, o método de aproximación polinómica cun polinomio ao sexto grao mostrou o maior nivel de fiabilidade (0,9844), o menor nivel de confianza no método lineal (0,9418) Pero isto non significa en absoluto que se teña a mesma tendencia con outros exemplos. Non, o nivel de eficiencia dos métodos anteriores pode variar significativamente, dependendo do tipo específico de función para a que se creará a liña de tendencias. Polo tanto, se o método seleccionado é o máis eficaz para esta función, isto non significa en absoluto que tamén será óptimo noutra situación.

Se aínda non pode determinar inmediatamente, segundo as recomendacións anteriores, que tipo de aproximación é adecuado especialmente para o seu caso, entón ten sentido probar todos os métodos. Despois de construír unha liña de tendencias e ver o seu nivel de confianza, será posible escoller a mellor opción.