¿Cuál es r 2 en una línea de tendencia? Línea de tendencia en Excel en diferentes gráficos. Pronóstico usando una tendencia lineal usando la función de predicción

La previsión es muy elemento importante casi cualquier campo de actividad, desde la economía hasta la ingeniería. existe gran número software, especializada en esta área. Desafortunadamente, no todos los usuarios saben que lo habitual procesador de mesa Excel tiene en su arsenal herramientas para realizar pronósticos, que no son muy inferiores en efectividad. programas profesionales. Averigüemos cuáles son estas herramientas y cómo hacer un pronóstico en la práctica.

El propósito de cualquier pronóstico es identificar la tendencia actual y determinar el resultado esperado en relación con el objeto en estudio en un momento determinado en el futuro.

Método 1: línea de tendencia

Uno de los más tipos populares El pronóstico gráfico en Excel es una extrapolación realizada mediante la construcción de una línea de tendencia.

Intentemos predecir la cantidad de ganancias de la empresa en 3 años basándonos en los datos de este indicador de los 12 años anteriores.

Método 2: operador PRONÓSTICO

La extrapolación de datos tabulares se puede realizar utilizando función estándar Sobresalir PREDICCIÓN. Este argumento se clasifica como una herramienta estadística y tiene la siguiente sintaxis:

PREDICCIÓN(X, valores_conocidos_y, valores_conocidos_x)

"INCÓGNITA" es el argumento para el cual es necesario determinar el valor de la función. En nuestro caso, el argumento será el año para el que se debe realizar la previsión.

« Valores conocidos y"— una base de valores de funciones conocidos. En nuestro caso, su papel lo desempeña el monto de la ganancia de períodos anteriores.

"Valores conocidos de x"— estos son los argumentos que corresponden a los valores conocidos de la función. En su función, utilizamos la numeración de los años para los que se recopiló información sobre los beneficios de años anteriores.

Naturalmente, el argumento no tiene por qué ser necesariamente un período de tiempo. Por ejemplo, puede ser la temperatura y el valor de la función puede ser el nivel de expansión del agua cuando se calienta.

Al calcular de esta manera, se utiliza el método de regresión lineal.

Veamos los matices del uso del operador. PREDICCIÓN en ejemplo específico. Tomemos la misma mesa. Necesitaremos conocer la previsión de beneficios para 2018.

Pero no olvide que, al igual que cuando se construye una línea de tendencia, el período de tiempo anterior al período de pronóstico no debe exceder el 30% de todo el período para el cual se acumuló la base de datos.

Método 3: operador TENDENCIA

Para realizar pronósticos, puede utilizar una función más: TENDENCIA. También entra en la categoría de operadores estadísticos. Su sintaxis es en muchos aspectos similar a la sintaxis de la herramienta. PREDICCIÓN y se ve así:

TENDENCIA(Valores_y_conocidos; valores_x_conocidos; valores_x_nuevos; [const])

Como vemos, los argumentos "Valores y conocidos" Y "Valores conocidos de x" corresponden completamente a elementos similares del operador PREDICCIÓN, y el argumento "Nuevos valores de x" coincide con el argumento "INCÓGNITA" herramienta anterior. Además, en TENDENCIA hay un argumento adicional "Constante", pero no es obligatorio y se utiliza sólo en presencia de factores constantes.

Este operador se utiliza con mayor eficacia cuando hay dependencia lineal funciones.

Veamos cómo funcionará esta herramienta con la misma matriz de datos. Para comparar los resultados obtenidos, definiremos el año 2019 como punto de previsión.

Método 4: operador CRECIMIENTO

Otra función que se puede utilizar para realizar pronósticos en Excel es el operador CRECIMIENTO. También se aplica a grupo estadístico herramientas, pero, a diferencia de los anteriores, a la hora de calcular no se utiliza el método de dependencia lineal, sino el exponencial. La sintaxis de esta herramienta se ve así:

CRECIMIENTO(Valores conocidos_y; valores conocidos_x; nuevos_valores_x; [const])

Como puede ver, los argumentos de esta función repiten exactamente los argumentos del operador TENDENCIA, por lo que no nos detendremos en su descripción por segunda vez, sino que pasaremos inmediatamente al uso de esta herramienta en la práctica.

Método 5: operador ESTIMACIÓN LINEAL

Operador ESTILO LINEAL Al calcular, utiliza el método de aproximación lineal. Esto no debe confundirse con el método de relación lineal utilizado por la herramienta. TENDENCIA. Su sintaxis se ve así:

LINEST(Valores_y conocidos; valores_x_conocidos; valores_x_nuevos;[const];[estadísticas])

Los dos últimos argumentos son opcionales. Conocemos los dos primeros. metodos anteriores. Pero habrás notado que a esta función le falta un argumento que apunte a los nuevos valores. El caso es que esta herramienta determina solo el cambio en la cantidad de ingresos por unidad de período, que en nuestro caso es igual a un año, pero tenemos que calcular el resultado total por separado sumando el resultado del cálculo del operador al último valor de ganancia real ESTILO LINEAL, multiplicado por el número de años.

Como podemos ver, el beneficio proyectado, calculado mediante el método de aproximación lineal, en 2019 será de 4.614,9 mil rublos.

Método 6: operador LGRFPRIBL

La última herramienta que veremos será LGRFPRIBL. Este operador realiza cálculos basados ​​en el método de ajuste exponencial. Su sintaxis tiene la siguiente estructura:

LGRFPRIBL (Valores conocidos_y; valores conocidos_x; nuevos_valores_x; [const]; [estadísticas])

Como puede ver, todos los argumentos repiten completamente los elementos correspondientes. función anterior. El algoritmo para calcular el pronóstico cambiará ligeramente. La función calculará una tendencia exponencial, que mostrará cuántas veces cambiará la cantidad de ingresos en un período, es decir, en un año. Necesitaremos encontrar la diferencia en las ganancias entre el último período real y el primer período planificado, multiplicarla por el número de períodos planificados. (3) y sume el importe del último período real al resultado.

La cantidad prevista de ganancias en 2019, calculada mediante el método de aproximación exponencial, será de 4639,2 mil rublos, lo que nuevamente no difiere mucho de los resultados obtenidos mediante el cálculo utilizando métodos anteriores.

Descubrimos cómo hacer pronósticos en Excel. Esto se puede hacer gráficamente mediante el uso de una línea de tendencia y analíticamente, utilizando una serie de funciones integradas. funciones estadísticas. El procesamiento de datos idénticos por parte de estos operadores puede dar lugar a resultados diferentes. Pero esto no es sorprendente, ya que todos usan diferentes metodos cálculo. Si la fluctuación es pequeña, entonces todas estas opciones, aplicables a un caso particular, pueden considerarse relativamente fiables.

Observando cualquier conjunto de datos distribuidos en el tiempo ( serie de tiempo), podemos identificar visualmente los mínimos y máximos de los indicadores que contiene. Un patrón de subidas y bajadas se denomina tendencia y puede indicarnos si nuestros datos aumentan o disminuyen.

Quizás comience la serie de artículos sobre pronósticos con lo más simple: construir una función de tendencia. Por ejemplo, tomemos datos de ventas y construyamos un modelo que describa la dependencia de las ventas en el tiempo.

Conceptos básicos

Creo que todo el mundo está familiarizado con la función lineal desde la escuela; es precisamente lo que subyace a la tendencia:

Y(t) = a0 + a1*t + E

Y es el volumen de ventas, la variable que explicaremos por tiempo y de la que depende, es decir, Y(t);

t - número de período ( número de serie mes), que explica el plan de ventas Y;

a0 es coeficiente cero regresión, que muestra el valor de Y(t), en ausencia de la influencia del factor explicativo (t=0);

a1 es el coeficiente de regresión, que muestra en qué medida el indicador de ventas Y estudiado depende del factor que influye t;

E son perturbaciones aleatorias que reflejan la influencia de otros factores no tenidos en cuenta en el modelo, excepto el tiempo t.

construcción de modelos

Entonces, conocemos el volumen de ventas de los últimos 9 meses. Así es como se ve nuestro letrero:

Lo siguiente que debemos hacer es determinar los coeficientes. a0 Y a1 para pronosticar el volumen de ventas para el décimo mes.

Determinación de los coeficientes del modelo

Estamos construyendo un cronograma. Horizontalmente vemos los meses diferidos, verticalmente el volumen de ventas:

EN Hojas de cálculo de Google elegir editor de gráficos -> Adicional y pon una marca al lado Líneas de tendencia. En la configuración seleccionamos Etiqueta — Ecuación Y Mostrar R^2.

Si hace todo en MS Excel, haga clic derecho en el gráfico y seleccione "Agregar línea de tendencia" en el menú desplegable.

Construido por defecto función lineal. A la derecha, seleccione "Mostrar ecuación en el diagrama" y "Valor de confiabilidad de aproximación R^2".

Esto es lo que pasó:

En la gráfica vemos la ecuación de la función:

y = 4856*x + 105104

Describe el volumen de ventas en función del número de mes para el que queremos pronosticar estas ventas. Cerca vemos el coeficiente de determinación R^2, que indica la calidad del modelo y qué tan bien describe nuestras ventas (Y). Cuanto más cerca de 1, mejor.

Tengo R^2 = 0,75. Este es un indicador promedio, indica que el modelo no toma en cuenta algún otro factores significativos además del tiempo t, por ejemplo, podría ser estacionalidad.

predecimos

y = 4856*10 + 105104

Obtenemos 153664 ventas en mes próximo. si sumamos nuevo punto En el gráfico, vemos inmediatamente que R^2 ha mejorado.

De esta manera, puede pronosticar datos con varios meses de anticipación, pero sin tener en cuenta otros factores, su pronóstico estará en la línea de tendencia y no será tan informativo como le gustaría. Además, una previsión a largo plazo realizada de esta forma será muy aproximada.

Puede mejorar la precisión del modelo agregando estacionalidad a la función de tendencia, lo cual haremos en el próximo artículo.

Para predecir un evento basándose en datos existentes, si no hay tiempo, puede utilizar una línea de tendencia. Al usarlo, puede comprender visualmente qué dinámica tienen los datos a partir de los cuales se construye el gráfico. El paquete de software de Microsoft tiene una maravillosa característica de Excel que le ayudará a crear un pronóstico bastante preciso utilizando esta herramienta: la línea de tendencia en Excel. Construir esta herramienta de análisis es bastante simple, a continuación se detalla descripción detallada proceso y tipos de líneas de tendencia.

Línea de tendencia en Excel. Proceso de construcción

Una línea de tendencia es una de las principales herramientas de análisis de datos.

Para formar una línea de tendencia, es necesario completar tres etapas, a saber:
1. Crea una tabla;
2.
3. Seleccione el tipo de línea de tendencia.

Después de recolectar todo información necesaria, puedes proceder directamente a los pasos para obtener el resultado final.

Primero, debes crear una tabla con los datos de origen. A continuación, seleccione el rango requerido y vaya a la pestaña "Insertar" y seleccione la función "Gráfico". Después de la construcción, en resultado final se puede aplicar características adicionales, en forma de títulos y leyendas. Para hacer esto, simplemente haga clic izquierdo en el gráfico, seleccione la pestaña llamada "Diseñador" y seleccione "Diseño". Lo siguiente que debes hacer es simplemente ingresar el título.

El siguiente paso es construir la propia línea de tendencia. Entonces, para hacer esto, debe resaltar el gráfico nuevamente y seleccionar la pestaña "Diseño" en la cinta de tareas. Siguiente en este menú debe hacer clic en el botón "Línea de tendencia" y seleccionar "aproximación lineal" o "aproximación exponencial".

Varias variaciones de llíneas de tendencia

Dependiendo de las características de los datos ingresados ​​por el usuario, vale la pena elegir una de las opciones presentadas a continuación se describe los tipos de líneas de tendencia;
Aproximación exponencial. Si la tasa de cambio de los datos de entrada aumenta, y de manera continua, entonces es esta linea será de gran utilidad. Sin embargo, si los datos que se ingresaron en la tabla contienen cero o características negativas, este tipo inaceptable.

Aproximación lineal. Esta línea es de naturaleza recta y generalmente se usa en casos elementales cuando la función aumenta o disminuye a una velocidad aproximadamente constante.

Aproximación logarítmica. Si inicialmente el valor crece correcta y rápidamente o, por el contrario, disminuye, pero luego, después de algunos valores, se estabiliza, entonces esta línea de tendencia será útil.

Aproximación polinomial . El aumento y la disminución variables son las características que caracterizan a esta línea. Además, el grado de los polinomios (polinomios) en sí está determinado por el número de máximos y mínimos.

Aproximación de potencia. Caracteriza el aumento y disminución monótono de un valor, pero su aplicación es imposible si los datos tienen valores negativos y cero.

media móvil. Se utiliza para mostrar claramente la dependencia directa de uno respecto del otro, suavizando todos los puntos de oscilación. Esto se logra tomando el valor promedio entre dos puntos adyacentes. Por lo tanto, el gráfico se promedia y el número de puntos se reduce al valor seleccionado en el menú "Puntos" por el usuario.

¿Cómo se utiliza? D Para predecir opciones económicas, es precisamente polinomio una línea cuyo grado polinómico se determina con base en varios principios: maximizar el coeficiente de determinación, así como la dinámica económica del indicador durante el período para el cual se requiere el pronóstico.

Siguiendo todas las etapas de formación y comprendiendo las características, se puede construir sólo una línea de tendencia primaria, que sólo corresponde vagamente a las previsiones reales. Pero después de configurar los parámetros, ya podemos hablar de una imagen más realista del pronóstico.

Línea de tendencia en Excel. Configuración de parámetrosen la línea funcional

Al hacer clic en el botón "Línea de tendencia", seleccione el menú requerido llamado " Opciones adicionales" En la ventana que aparece, haga clic en "Formato de línea de tendencia" y luego marque la casilla junto al valor "coloque el valor de confiabilidad de aproximación R^2 en el diagrama". Después de eso, cierre el menú haciendo clic en el botón correspondiente. En el propio diagrama aparece el coeficiente R^2 = 0,6442.

Luego de esto cancelamos los cambios realizados. Seleccionando el gráfico y haciendo clic en la pestaña "Diseño", luego haga clic en "Línea de tendencia" y haga clic en "No". A continuación, yendo a la función “Formato de línea de tendencia”, haga clic en polinomio línea e intente lograr el valor R ^ 2 = 0.8321 cambiando el grado.

Para ver fórmulas o crear otras variaciones de pronósticos que difieran de los estándar, basta con no tener miedo de experimentar con valores, y especialmente con polinomios. Así, utilizando sólo una programa excel, puede crear un pronóstico bastante preciso basado en los datos de entrada.

¿Qué debo hacer si no existen mediciones de tiempos para ciertos volúmenes/tamaños de productos? ¿O el número de mediciones es insuficiente y no se podrán realizar observaciones adicionales en un futuro próximo? la mejor manera La solución a este problema es crear dependencias calculadas (ecuaciones de regresión) utilizando líneas de tendencia en MS Excel.

Consideremos una situación real: en un almacén, para establecer el importe de los costes laborales para la preparación de cajas de un pedido, se realizaron observaciones de tiempos. Los resultados de estas observaciones se presentan en la Tabla 1 a continuación.

Posteriormente surgió la necesidad de determinar el tiempo necesario para seleccionar 0,6 y 0,9 m3 de mercancía/pedido. Debido a la imposibilidad de realizar estudios de tiempos adicionales, el tiempo empleado en seleccionar estos volúmenes de pedidos se calculó mediante ecuaciones de regresión en MS Excel. Para lograr esto, la Tabla 1 se convirtió en la Tabla 2.

Seleccionando un diagrama de dispersión, fig. 1

Siguiente paso: el cursor del mouse se colocó en uno de los puntos del gráfico y usando botón derecho el ratón fue causado menú contextual, en el que se seleccionó el ítem: “añadir línea de tendencia” (Figura 2).

Añadiendo una línea de tendencia, fig. 2

En la ventana que aparece para configurar el formato de la línea de tendencia (Figura 3) Se seleccionaron secuencialmente: tipo de línea lineal/potencia y se verificaron los siguientes ítems: “mostrar la ecuación en el diagrama” y “colocar el valor de confiabilidad de aproximación (R^2) en el diagrama” (coeficiente de determinación).

Formato de línea de tendencia, fig. 3

Como resultado, se obtuvieron los gráficos presentados en la Fig. 1. 4 y 5.

Dependencia lineal calculada, Fig. 4

Dependencia calculada de la ley de potencia, Fig. 5

Un análisis visual de los gráficos indica claramente la cercanía de las dependencias obtenidas. Además, el valor de confiabilidad de aproximación (R^2), que también se denomina coeficiente de determinación, en el caso de ambas dependencias es el mismo valor de 0,97. Se sabe que cuanto más se acerca el coeficiente de determinación a 1, más se corresponde la línea de tendencia con la realidad. También se puede afirmar que el 97% del cambio en el tiempo dedicado al procesamiento de pedidos se explica por un cambio en la cantidad de bienes. Por lo tanto, en en este caso no es importante: qué dependencia calculada debe elegirse como la principal para el cálculo posterior de los costos de tiempo.

Tomemos como principal la dependencia lineal calculada. Luego los valores del tiempo invertido en función de la cantidad de mercancías vendrán determinados por la fórmula: y = 54,511x + 0,1489. Los resultados de estos cálculos para la cantidad de bienes para los cuales se realizaron observaciones temporales anteriormente se presentan en el Cuadro 3 a continuación.

Determinemos la desviación promedio del tiempo invertido calculado utilizando la ecuación de regresión del tiempo invertido calculado a partir de las observaciones del cronometraje: (-0,05+0,10-0,05+0,01)/4=0,0019. Por lo tanto, el gasto de tiempo calculado usando la ecuación de regresión difiere del gasto de tiempo calculado a partir de datos de cronometraje solo en 0,19%. La discrepancia entre los datos es insignificante.

Utilizando la fórmula: y = 54,511x + 0,1489, estableceremos los costos temporales de la cantidad de bienes para los cuales no se han realizado observaciones temporales previamente. (Tabla 4).

Por lo tanto, construir dependencias calculadas utilizando líneas de tendencia en MS Excel - Este gran manera estableciendo el tiempo dedicado a operaciones que, por diversas razones, no fueron cubiertas por observaciones de cronometraje.

Los cuadros y gráficos se utilizan para analizar datos numéricos, por ejemplo, para evaluar la relación entre dos tipos de valores. Para ello, se puede agregar a los datos en un cuadro o gráfico una línea de tendencia y su ecuación, valores de pronóstico calculados para varios períodos hacia adelante o hacia atrás.

Línea de tendencia representa una línea recta o curva que aproxima (acerca) los datos originales basándose en una ecuación de regresión o media móvil. La aproximación está determinada por el método. mínimos cuadrados. Dependiendo de la naturaleza del comportamiento de los datos de origen (decrecientes, crecientes, etc.), se selecciona un método de interpolación que debe usarse para construir una tendencia.

Hay varias opciones para formar una línea de tendencia.

Función lineal: y=mx+b

donde m es la tangente del ángulo de inclinación de la recta, b es el desplazamiento.

Línea de tendencia recta (tendencia lineal) de la mejor manera posible adecuado para cantidades que varían con velocidad constante. Se utiliza en casos donde los puntos de datos están ubicados cerca de una línea recta.

Función logarítmica: y=c*ln⁡x+b

donde c y b son constantes.

Una línea de tendencia logarítmica corresponde a una serie de datos cuyos valores inicialmente aumentan o disminuyen rápidamente y luego se estabilizan gradualmente. Se puede utilizar para datos positivos y negativos.

Función polinómica (hasta el sexto grado inclusive): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6

donde b, c 1, c 2, ... c 6 son constantes.

Se utiliza una línea de tendencia polinómica para describir datos que aumentan y disminuyen alternativamente. El grado del polinomio se selecciona de modo que sea igual a uno. mas cantidad extremos (máximos y mínimos) de la curva.

Función de potencia: y = cxb

donde c y b son constantes.

La línea de tendencia de potencia da buenos resultados para datos positivos con aceleración constante. Para filas con cero o valores negativos dibujar la línea de tendencia especificada es imposible.

Función exponencial: y = cebx

donde c y b son constantes, e es la base del logaritmo natural.

La tendencia exponencial se utiliza cuando el cambio en los datos aumenta continuamente. Construir la tendencia indicada es imposible si el conjunto de valores de los miembros de la serie contiene datos cero o negativos.

Usando filtrado lineal según la fórmula: F t = (A t +A (t-1) +⋯+A (t-n+1))/n

donde n es el número total de miembros de la serie, t es el número dado de puntos (2 ≤ t< n).

Tendencia con filtración lineal le permite suavizar las fluctuaciones de los datos, demostrando claramente la naturaleza de las dependencias. Para construir la línea de tendencia especificada, el usuario debe especificar un número: un parámetro de filtro. Si el número es 2, entonces el primer punto de la línea de tendencia se define como el promedio de los dos primeros elementos de datos, el segundo punto se define como el promedio del segundo y tercer elementos de datos, etc.

Para algunos tipos de gráficos, en principio no se puede construir una línea de tendencia: gráficos apilados, gráficos volumétricos, gráficos de radar, gráficos circulares, gráficos de superficie y gráficos de anillos. Si es posible, puedes agregar varias líneas con diferentes parámetros al diagrama. La correspondencia de la línea de tendencia con los valores reales de la serie de datos se establece mediante el coeficiente de fiabilidad de aproximación:

La línea de tendencia y sus parámetros se agregan a los datos del gráfico mediante los siguientes comandos:

Si es necesario, puede cambiar los parámetros de la línea haciendo clic en una serie de datos del gráfico o líneas de tendencia en la ventana Formatear línea de tendencia. Puede agregar (o eliminar) una ecuación de regresión, un coeficiente de confiabilidad de aproximación, determinar la dirección y el pronóstico de cambios en una serie de datos y también corregir los elementos de diseño de la línea de tendencia. La línea de tendencia seleccionada también se puede eliminar.

La figura muestra una tabla de datos sobre cambios en el valor de un valor. Con base en estos datos condicionales, se construyó un diagrama de dispersión, se agregó una línea de tendencia polinómica de tercer orden (definida por una línea discontinua) y algunos otros parámetros. El valor obtenido del coeficiente de confiabilidad de aproximación R2 en el diagrama es cercano a la unidad, lo que indica la cercanía de la línea de tendencia calculada a los datos del problema. El valor previsto de los cambios en el valor de un título está dirigido hacia el crecimiento.