Dónde encontrar una línea de tendencia en Excel. Cómo dibujar una línea de tendencia en MS Excel
Al observar cualquier conjunto de datos distribuidos en el tiempo (series de tiempo), podemos determinar visualmente el ascenso y la caída 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 es el número del período (número ordinal del mes), que explica el plan de ventas Y;
a0 es el coeficiente de regresión cero, 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 Google Sheets seleccionamos 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.
De forma predeterminada, se construye una 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 tiene en cuenta ningún otro factor significativo además del tiempo t, por ejemplo, puede ser la estacionalidad.
predecimos
y = 4856*10 + 105104
Obtenemos 153664 ventas el próximo mes. Si agregamos un nuevo punto al gráfico, inmediatamente vemos 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 aumentar la precisión del modelo agregando estacionalidad a la función de tendencia, lo cual haremos en el próximo artículo.
Objeto del servicio. El servicio se utiliza para calcular los parámetros de tendencia de la serie temporal y t en línea utilizando el método de mínimos cuadrados (LS) (ver el ejemplo de cómo encontrar la ecuación de tendencia), así como el método desde cero condicional. Para ello se construye un sistema de ecuaciones:un 0 norte + un 1 ∑t = ∑y
un 0 ∑t + un 1 ∑t 2 = ∑y t
y una tabla como esta:
| t | y | t 2 | y 2 | t y | y(t) |
| 1 | |||||
| ... | ... | ... | ... | ... | ... |
| norte | |||||
| TOTAL | ∑ | ∑ | ∑ | ∑ | ∑ |
Instrucciones. Especifique la cantidad de datos (número de filas). La solución resultante se guarda en un archivo de Word y Excel.
La tendencia de una serie temporal caracteriza un conjunto de factores que tienen una influencia a largo plazo y forman la dinámica general del indicador en estudio.
Método para contar el tiempo desde un inicio condicional.
Para determinar los parámetros de una función matemática al analizar una tendencia en una serie de tiempo, se utiliza el método de contar el tiempo desde un comienzo condicional. Se basa en la notación de la serie temporal de modo que ∑t i . En este caso, en una serie dinámica con un número impar de niveles, el número de serie del nivel ubicado en el medio de la serie se designa con un valor cero y se toma como el comienzo condicional del conteo de tiempo con un intervalo de + 1 de todos los niveles posteriores y –1 de todos los niveles anteriores. Por ejemplo, al denotar tiempo habrá: –2, –1, 0, +1, +2. Con un número par de niveles, los números de serie de la mitad superior de la fila (desde el medio) se designan con los números: –1, –3, –5, y la mitad inferior de la fila se indica con +1, + 3, +5.Ejemplo. Estudio estadístico de la dinámica poblacional.
- Utilizando indicadores de dinámica en cadena, básicos y promedio, evalúe el cambio en los números y escriba sus conclusiones.
- Utilizando el método de alineación analítica (línea recta y parábola, determinando los coeficientes mediante MCO), identifique la tendencia principal en el desarrollo del fenómeno (población de la República de Komi). Evaluar la calidad de los modelos resultantes utilizando errores y coeficientes de aproximación.
- Determine los coeficientes de tendencia lineal y parabólica utilizando el Asistente para gráficos. Proporcione pronósticos de población puntuales y de intervalo para 2010. Escriba sus conclusiones.
| 1990 | 1996 | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 |
| 1249 | 1133 | 1043 | 1030 | 1016 | 1005 | 996 | 985 | 975 | 968 |
a) La ecuación de tendencia lineal tiene la forma y = bt + a
1. Encuentra los parámetros de la ecuación usando el método de mínimos cuadrados.. Usamos el método de contar el tiempo desde un comienzo condicional.
El sistema de ecuaciones de mínimos cuadrados para una tendencia lineal tiene la forma:
un 0 norte + un 1 ∑t = ∑y
un 0 ∑t + un 1 ∑t 2 = ∑y t
| t | y | t 2 | y 2 | t y |
| -9 | 1249 | 81 | 1560001 | -11241 |
| -7 | 1133 | 49 | 1283689 | -7931 |
| -5 | 1043 | 25 | 1087849 | -5215 |
| -3 | 1030 | 9 | 1060900 | -3090 |
| -1 | 1016 | 1 | 1032256 | -1016 |
| 1 | 1005 | 1 | 1010025 | 1005 |
| 3 | 996 | 9 | 992016 | 2988 |
| 5 | 985 | 25 | 970225 | 4925 |
| 7 | 975 | 49 | 950625 | 6825 |
| 9 | 968 | 81 | 937024 | 8712 |
| 0 | 10400 | 330 | 10884610 | -4038 |
Para nuestros datos, el sistema de ecuaciones tomará la forma:
10a 0 + 0a 1 = 10400
0a 0 + 330a 1 = -4038
De la primera ecuación expresamos un 0 y lo sustituimos en la segunda ecuación.
Obtenemos un 0 = -12.236, un 1 = 1040
Ecuación de tendencia:
y = -12,236 t + 1040
Evaluemos la calidad de la ecuación de tendencia utilizando el error de aproximación absoluto.
El error de aproximación entre el 5% y el 7% indica un buen ajuste de la ecuación de tendencia a los datos originales. 
b) alineación parabólica
La ecuación de tendencia es y = at 2 + bt + c
1. Encuentra los parámetros de la ecuación usando el método de mínimos cuadrados.
Sistema de ecuaciones de mínimos cuadrados:
un 0 norte + un 1 ∑t + un 2 ∑t 2 = ∑y
un 0 ∑t + un 1 ∑t 2 + un 2 ∑t 3 = ∑yt
un 0 ∑t 2 + un 1 ∑t 3 + un 2 ∑t 4 = ∑yt 2
| t | y | t 2 | y 2 | t y | t 3 | t 4 | t 2 años |
| -9 | 1249 | 81 | 1560001 | -11241 | -729 | 6561 | 101169 |
| -7 | 1133 | 49 | 1283689 | -7931 | -343 | 2401 | 55517 |
| -5 | 1043 | 25 | 1087849 | -5215 | -125 | 625 | 26075 |
| -3 | 1030 | 9 | 1060900 | -3090 | -27 | 81 | 9270 |
| -1 | 1016 | 1 | 1032256 | -1016 | -1 | 1 | 1016 |
| 1 | 1005 | 1 | 1010025 | 1005 | 1 | 1 | 1005 |
| 3 | 996 | 9 | 992016 | 2988 | 27 | 81 | 8964 |
| 5 | 985 | 25 | 970225 | 4925 | 125 | 625 | 24625 |
| 7 | 975 | 49 | 950625 | 6825 | 343 | 2401 | 47775 |
| 9 | 968 | 81 | 937024 | 8712 | 729 | 6561 | 78408 |
| 0 | 10400 | 330 | 10884610 | -4038 | 0 | 19338 | 353824 |
Para nuestros datos, el sistema de ecuaciones tiene la forma
10a 0 + 0a 1 + 330a 2 = 10400
0a 0 + 330a 1 + 0a 2 = -4038
330a 0 + 0a 1 + 19338a 2 = 353824
Obtenemos a 0 = 1,258, a 1 = -12,236, a 2 = 998,5
Ecuación de tendencia:
y = 1.258t 2 -12.236t+998.5
Error de aproximación para la ecuación de tendencia parabólica. 
Dado que el error es inferior al 7%, esta ecuación se puede utilizar como tendencia.
Error de aproximación mínimo para alineación parabólica. Además, el coeficiente de determinación R2 es mayor que el lineal. Por tanto, para realizar predicciones es necesario utilizar una ecuación parabólica.
Previsión de intervalos.
Determinemos la raíz del error cuadrático medio del indicador predicho. 
m = 1: el número de factores que influyen en la ecuación de tendencia.
Uy = y n+L ± K
Dónde 
L - período de avance; y n+L - pronóstico puntual según el modelo en el (n + L)-ésimo momento en el tiempo; n es el número de observaciones de la serie temporal; Sy es el error estándar del indicador previsto; T tab - valor tabular de la prueba de Student para el nivel de significancia α y para el número de grados de libertad igual a n-2.
Usando la tabla de Student encontramos Ttable
Tabla T (n-m-1;α/2) = (8;0,025) = 2,306
Pronóstico puntual, t = 10: y(10) = 1,26*10 2 -12,24*10 + 998,5 = 1001,89 mil personas.
1001.89 - 71.13 = 930.76 ; 1001.89 + 71.13 = 1073.02
Pronóstico de intervalo:
t = 9+1 = 10: (930,76;1073,02)
Para ilustrar visualmente las tendencias de los precios, se utiliza una línea de tendencia. Un elemento de análisis técnico es una imagen geométrica de los valores medios del indicador analizado.
Veamos cómo agregar una línea de tendencia a un gráfico en Excel.
Agregar una línea de tendencia a un gráfico
Por ejemplo, tomemos los precios promedio del petróleo desde 2000 a partir de fuentes abiertas. Ingresemos los datos para el análisis en la tabla:
Una línea de tendencia en Excel es una gráfica de una función de ajuste. ¿Por qué es necesario? Hacer pronósticos basados en datos estadísticos. Para ello es necesario ampliar la línea y determinar sus valores.
Si R2 = 1, entonces el error de aproximación es cero. En nuestro ejemplo, la elección de la aproximación lineal dio baja confiabilidad y malos resultados. El pronóstico será inexacto.
¡¡¡Atención!!! No puede agregar una línea de tendencia a los siguientes tipos de gráficos y tablas:
- pétalo;
- circular;
- superficie;
- anular;
- volumen;
- con acumulación.
Ecuación de línea de tendencia en Excel
En el ejemplo anterior, se eligió la aproximación lineal sólo para ilustrar el algoritmo. Como lo demostró el valor de confiabilidad, la elección no fue del todo exitosa.
Debe elegir el tipo de visualización que ilustre con mayor precisión la tendencia en la entrada del usuario. Veamos las opciones.
Aproximación lineal
Su imagen geométrica es una línea recta. Por lo tanto, la aproximación lineal se utiliza para ilustrar un indicador que aumenta o disminuye a un ritmo constante.
Consideremos el número condicional de contratos celebrados por el gerente durante 10 meses:
Con base en los datos de la tabla de Excel, crearemos un diagrama de dispersión (ayudará a ilustrar el tipo lineal):
Seleccione el gráfico - "agregar línea de tendencia". En los parámetros, seleccione el tipo lineal. Agregue el valor de confianza de aproximación y la ecuación de la línea de tendencia en Excel (simplemente marque las casillas en la parte inferior de la ventana "Parámetros").
Obtenemos el resultado:
¡Prestar atención! Con el tipo de aproximación lineal, los puntos de datos se ubican lo más cerca posible de la línea recta. Esta vista utiliza la siguiente ecuación:
y = 4,503x + 6,1333
- donde 4,503 es el índice de pendiente;
- 6.1333 – desplazamientos;
- y – secuencia de valores,
- x – número de período.
La línea recta del gráfico muestra un aumento constante en la calidad del trabajo del gerente. El valor de confiabilidad de la aproximación es 0.9929, lo que indica una buena concordancia entre la línea calculada y los datos originales. Los pronósticos deben ser precisos.
Para predecir el número de contratos celebrados, por ejemplo, en el período 11, es necesario sustituir el número 11 en lugar de x en la ecuación. Durante los cálculos descubrimos que en el undécimo período este gerente celebrará entre 55 y 56 contratos.
Línea de tendencia exponencial
Este tipo es útil si los valores de entrada cambian a un ritmo que aumenta continuamente. El ajuste exponencial no se utiliza cuando hay características nulas o negativas.
Construyamos una línea de tendencia exponencial en Excel. Tomemos, por ejemplo, los valores condicionales de la oferta productiva de electricidad en la región X:
Estamos construyendo un cronograma. Agrega una línea exponencial.
La ecuación se ve así:
y = 7,6403е^-0,084x
- donde 7,6403 y -0,084 son constantes;
- e es la base del logaritmo natural.
El indicador de fiabilidad de la aproximación fue 0,938: la curva corresponde a los datos, el error es mínimo y los pronósticos serán precisos.
Línea de tendencia logarítmica en Excel
Se utiliza para los siguientes cambios en el indicador: primero, crecimiento o disminución rápidos, luego estabilidad relativa. La curva optimizada se adapta bien a este “comportamiento” de la cantidad. La tendencia logarítmica es adecuada para pronosticar las ventas de un nuevo producto que recién se está introduciendo en el mercado.
En la etapa inicial, la tarea del fabricante es aumentar la base de clientes. Cuando un producto tiene su propio comprador, es necesario retenerlo y atenderlo.
Construyamos un gráfico y agreguemos una línea de tendencia logarítmica para pronosticar las ventas de un producto condicional:
R2 tiene un valor cercano a 1 (0,9633), lo que indica un error de aproximación mínimo. Pronostiquemos los volúmenes de ventas en períodos posteriores. Para hacer esto, debes sustituir el número del período en la ecuación en lugar de x.
Por ejemplo:
| Período | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| Pronóstico | 1005,4 | 1024,18 | 1041,74 | 1058,24 | 1073,8 | 1088,51 | 1102,47 |
Para calcular las cifras previstas se utilizó una fórmula de la forma: =272,14*LN(B18)+287,21. Donde B18 es el número del período.
Línea de tendencia polinómica en Excel
Esta curva se caracteriza por aumentos y disminuciones variables. Para polinomios (polinomios), el grado está determinado (por el número de valores máximo y mínimo). Por ejemplo, un extremo (mínimo y máximo) es el segundo grado, dos extremos son el tercer grado, tres son el cuarto.
La tendencia polinómica en Excel se utiliza para analizar un gran conjunto de datos sobre una cantidad inestable. Veamos el ejemplo del primer conjunto de valores (precios del petróleo).
Para obtener tal valor de confiabilidad de aproximación (0,9256), fue necesario fijarlo en el grado 6.
Pero esta tendencia nos permite hacer previsiones más o menos precisas.
Información teórica
En la práctica, al modelar varios procesos, en particular económicos, físicos, técnicos y sociales, se utiliza ampliamente uno u otro método para calcular valores aproximados de funciones a partir de sus valores conocidos en ciertos puntos fijos.
Este tipo de problema de aproximación de funciones surge a menudo:
- al construir fórmulas aproximadas para calcular los valores de cantidades características del proceso en estudio utilizando datos tabulares obtenidos como resultado del experimento;
- en integración numérica, diferenciación, resolución de ecuaciones diferenciales, etc.;
- si es necesario calcular los valores de funciones en puntos intermedios del intervalo considerado;
- al determinar los valores de las cantidades características de un proceso fuera del intervalo considerado, en particular al realizar predicciones.
Si, para modelar un determinado proceso especificado por una tabla, construimos una función que describa aproximadamente este proceso basándose en el método de mínimos cuadrados, se llamará función de aproximación (regresión) y el problema de construir funciones de aproximación en sí se llamará un problema de aproximación.
Este artículo analiza las capacidades del paquete MS Excel para resolver este tipo de problemas; además, proporciona métodos y técnicas para construir (crear) regresiones para funciones tabuladas (que es la base del análisis de regresión).
Excel tiene dos opciones para construir regresiones.
- Agregar regresiones seleccionadas (líneas de tendencia) a un diagrama construido sobre la base de una tabla de datos para la característica del proceso en estudio (disponible solo si se ha construido un diagrama);
- Utilizando las funciones estadísticas integradas de la hoja de cálculo de Excel, lo que le permite obtener regresiones (líneas de tendencia) directamente desde la tabla de datos de origen.
Agregar líneas de tendencia a un gráfico
Para una tabla de datos que describe un proceso y está representada por un diagrama, Excel tiene una herramienta eficaz de análisis de regresión que le permite:
- construir sobre la base del método de mínimos cuadrados y agregar cinco tipos de regresiones al diagrama, que modelan el proceso en estudio con diversos grados de precisión;
- agregue la ecuación de regresión construida al diagrama;
- determine el grado de correspondencia de la regresión seleccionada con los datos mostrados en el gráfico.
Basado en los datos del gráfico, Excel le permite obtener tipos de regresiones lineales, polinomiales, logarítmicas, de potencia y exponenciales, que se especifican mediante la ecuación:
y = y(x)
donde x es una variable independiente que muchas veces toma los valores de una secuencia de números naturales (1; 2; 3;...) y produce, por ejemplo, una cuenta regresiva del tiempo del proceso en estudio (características).
1 . La regresión lineal es buena para modelar características cuyos valores aumentan o disminuyen a un ritmo constante. Este es el modelo más simple de construir para el proceso en estudio. Ella
y = mx + b
donde m es la tangente de la pendiente de regresión lineal al eje x; b - coordenada del punto de intersección de la regresión lineal con el eje de ordenadas.
2 . Una línea de tendencia polinómica es útil para describir características que tienen varios extremos distintos (máximos y mínimos). La elección del grado del polinomio está determinada por el número de extremos de la característica en estudio. Por tanto, un polinomio de segundo grado bien puede describir un proceso que tiene sólo un máximo o un mínimo; polinomio de tercer grado: no más de dos extremos; polinomio de cuarto grado: no más de tres extremos, etc.
En este caso, la línea de tendencia se construye de acuerdo con la ecuación:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
donde los coeficientes c0, c1, c2,... c6 son constantes cuyos valores se determinan durante la construcción.
3 . La línea de tendencia logarítmica se utiliza con éxito al modelar características cuyos valores inicialmente cambian rápidamente y luego se estabilizan gradualmente.
Construido de acuerdo con la ecuación:
y = c ln(x) + b
4 . Una línea de tendencia de ley de potencia da buenos resultados si los valores de la relación en estudio se caracterizan por un cambio constante en la tasa de crecimiento. Un ejemplo de tal dependencia es la gráfica del movimiento uniformemente acelerado de un automóvil. Si hay valores cero o negativos en los datos, no se puede utilizar una línea de tendencia eléctrica.
Construido de acuerdo con la ecuación:
y = cxb
donde los coeficientes b, c son constantes.
5 . Se debe utilizar una línea de tendencia exponencial cuando la tasa de cambio en los datos aumenta continuamente. Para datos que contienen valores cero o negativos, este tipo de aproximación tampoco es aplicable.
Construido de acuerdo con la ecuación:
y = c ebx
donde los coeficientes b, c son constantes.
Al seleccionar una línea de tendencia, Excel calcula automáticamente el valor de R2, que caracteriza la confiabilidad de la aproximación: cuanto más cerca está el valor de R2 de la unidad, más confiablemente se aproxima la línea de tendencia al proceso en estudio. Si es necesario, el valor R2 siempre se puede mostrar en el gráfico.
Determinado por la fórmula:
Para agregar una línea de tendencia a una serie de datos:
- activar un gráfico basado en una serie de datos, es decir, hacer clic dentro del área del gráfico. El elemento Diagrama aparecerá en el menú principal;
- después de hacer clic en este elemento, aparecerá un menú en la pantalla en el que deberá seleccionar el comando Agregar línea de tendencia.
Las mismas acciones se pueden implementar fácilmente moviendo el puntero del mouse sobre el gráfico correspondiente a una de las series de datos y haciendo clic derecho; En el menú contextual que aparece, seleccione el comando Agregar línea de tendencia. El cuadro de diálogo Línea de tendencia aparecerá en la pantalla con la pestaña Tipo abierta (Fig. 1).
Después de esto necesitas:
Seleccione el tipo de línea de tendencia requerido en la pestaña Tipo (el tipo Lineal está seleccionado de forma predeterminada). Para el tipo de polinomio, en el campo Grado, especifique el grado del polinomio seleccionado.
1 . El campo Serie integrada enumera todas las series de datos del gráfico en cuestión. Para agregar una línea de tendencia a una serie de datos específica, seleccione su nombre en el campo Serie integrada.
Si es necesario, yendo a la pestaña Parámetros (Fig. 2), puede configurar los siguientes parámetros para la línea de tendencia:
- cambie el nombre de la línea de tendencia en el campo Nombre de la curva de aproximación (suavizada).
- establezca el número de períodos (hacia adelante o hacia atrás) para el pronóstico en el campo Pronóstico;
- mostrar la ecuación de la línea de tendencia en el área del diagrama, para lo cual debe habilitar la casilla de verificación mostrar ecuación en el diagrama;
- muestre el valor de confiabilidad de aproximación R2 en el área del diagrama, para lo cual debe habilitar la casilla de verificación Colocar el valor de confiabilidad de aproximación en el diagrama (R^2);
- establezca el punto de intersección de la línea de tendencia con el eje Y, para lo cual debe habilitar la casilla de verificación para la intersección de la curva con el eje Y en un punto;
- Haga clic en el botón Aceptar para cerrar el cuadro de diálogo.
Para empezar a editar una línea de tendencia ya dibujada, existen tres formas:
utilice el comando Línea de tendencia seleccionada del menú Formato, habiendo seleccionado previamente la línea de tendencia;Aparecerá en la pantalla el cuadro de diálogo Formato de línea de tendencia (Fig. 3), que contiene tres pestañas: Ver, Tipo, Parámetros, y el contenido de las dos últimas coincide completamente con pestañas similares del cuadro de diálogo Línea de tendencia (Fig. 1). -2). En la pestaña Ver, puede configurar el tipo de línea, su color y grosor.
Para eliminar una línea de tendencia que ya se ha dibujado, seleccione la línea de tendencia que desea eliminar y presione la tecla Eliminar.
Las ventajas de la herramienta de análisis de regresión considerada son:
- la relativa facilidad de construir una línea de tendencia en los gráficos sin crear una tabla de datos para ella;
- una lista bastante amplia de tipos de líneas de tendencia propuestas, y esta lista incluye los tipos de regresión más utilizados;
- la capacidad de predecir el comportamiento del proceso en estudio mediante un número arbitrario (dentro de los límites del sentido común) de pasos hacia adelante y hacia atrás;
- la capacidad de obtener la ecuación de la línea de tendencia en forma analítica;
- la posibilidad, en caso necesario, de obtener una evaluación de la fiabilidad de la aproximación.
Las desventajas incluyen las siguientes:
la construcción de una línea de tendencia se lleva a cabo solo si hay un diagrama construido sobre una serie de datos;Las líneas de tendencia se pueden utilizar para complementar series de datos presentadas en gráficos, como gráficos, histogramas, gráficos de áreas planas no estandarizadas, gráficos de barras, gráficos de dispersión, gráficos de burbujas y gráficos de acciones.
No puede agregar líneas de tendencia a series de datos en gráficos 3D, normalizados, de radar, circulares y de anillos.
Usando las funciones integradas de Excel
Excel también tiene una herramienta de análisis de regresión para trazar líneas de tendencia fuera del área del gráfico. Hay varias funciones de hojas de cálculo estadísticas que puede utilizar para este propósito, pero todas ellas sólo le permiten crear regresiones lineales o exponenciales.
Excel tiene varias funciones para construir regresión lineal, en particular:
- TENDENCIA;
- LINEAL;
- PENDIENTE y CORTE.
Además de varias funciones para construir una línea de tendencia exponencial, en particular:
- ALTURA;
- LGRFPRIBL.
Cabe señalar que las técnicas para construir regresiones utilizando las funciones TENDENCIA y CRECIMIENTO son casi las mismas. Lo mismo puede decirse del par de funciones LINEST y LGRFPRIBL. Para estas cuatro funciones, la creación de una tabla de valores utiliza funciones de Excel como fórmulas de matriz, lo que satura un poco el proceso de creación de regresiones. Observemos también que la construcción de una regresión lineal, en nuestra opinión, se logra más fácilmente utilizando las funciones PENDIENTE e INTERCEPCIÓN, donde la primera determina la pendiente de la regresión lineal y la segunda determina el segmento interceptado por la regresión en el eje y.
Las ventajas de la herramienta de funciones integrada para el análisis de regresión son:
- un proceso bastante simple y uniforme para generar series de datos de la característica en estudio para todas las funciones estadísticas integradas que definen las líneas de tendencia;
- metodología estándar para construir líneas de tendencia basadas en series de datos generadas;
- la capacidad de predecir el comportamiento del proceso en estudio mediante el número requerido de pasos hacia adelante o hacia atrás.
Las desventajas incluyen el hecho de que Excel no tiene funciones integradas para crear otros tipos de líneas de tendencia (excepto lineales y exponenciales). Esta circunstancia muchas veces no permite elegir un modelo suficientemente preciso del proceso en estudio, así como obtener previsiones cercanas a la realidad. Además, cuando se utilizan las funciones TENDENCIA y CRECIMIENTO, no se conocen las ecuaciones de las líneas de tendencia.
Cabe señalar que los autores no se propusieron presentar el curso del análisis de regresión con ningún grado de exhaustividad. Su tarea principal es mostrar, mediante ejemplos específicos, las capacidades del paquete Excel a la hora de resolver problemas de aproximación; demostrar qué herramientas efectivas tiene Excel para crear regresiones y pronósticos; ilustran cómo estos problemas pueden ser resueltos con relativa facilidad incluso por un usuario que no tiene amplios conocimientos de análisis de regresión.
Ejemplos de resolución de problemas específicos.
Veamos cómo resolver problemas específicos utilizando las herramientas de Excel enumeradas.
Problema 1
Con un cuadro de datos sobre los beneficios de una empresa de transporte por carretera para 1995-2002. necesitas hacer lo siguiente:
- Construye un diagrama.
- Agregue líneas de tendencia lineales y polinómicas (cuadráticas y cúbicas) al gráfico.
- Utilizando las ecuaciones de las líneas de tendencia, obtenga datos tabulares sobre las ganancias empresariales para cada línea de tendencia para 1995-2004.
- Haga una previsión de las ganancias de la empresa para 2003 y 2004.
solución del problema
- En el rango de celdas A4:C11 de la hoja de cálculo de Excel, ingrese la hoja de cálculo que se muestra en la Fig. 4.
- Habiendo seleccionado el rango de celdas B4:C11, construimos un diagrama.
- Activamos el diagrama construido y, de acuerdo con el método descrito anteriormente, después de seleccionar el tipo de línea de tendencia en el cuadro de diálogo Línea de tendencia (ver Fig. 1), agregamos alternativamente líneas de tendencia lineales, cuadráticas y cúbicas al diagrama. En el mismo cuadro de diálogo, abra la pestaña Parámetros (ver Fig. 2), en el campo Nombre de la curva de aproximación (suavizada), ingrese el nombre de la tendencia que se agregará y en el campo Pronóstico hacia adelante para: períodos, configure el valor 2, ya que se prevé realizar una previsión de beneficios para los próximos dos años. Para mostrar la ecuación de regresión y el valor de confiabilidad de la aproximación R2 en el área del diagrama, active las casillas de verificación Mostrar ecuación en la pantalla y coloque el valor de confiabilidad de la aproximación (R^2) en el diagrama. Para una mejor percepción visual, cambiamos el tipo, color y grosor de las líneas de tendencia construidas, para lo cual usamos la pestaña Ver del cuadro de diálogo Formato de línea de tendencia (ver Fig. 3). El diagrama resultante con líneas de tendencia agregadas se muestra en la Fig. 5.
- Obtener datos tabulares sobre las ganancias empresariales para cada línea de tendencia para 1995-2004. Usemos las ecuaciones de la línea de tendencia presentadas en la Fig. 5. Para hacer esto, en las celdas del rango D3:F3, ingrese información de texto sobre el tipo de línea de tendencia seleccionada: Tendencia lineal, Tendencia cuadrática, Tendencia cúbica. Luego, ingrese la fórmula de regresión lineal en la celda D4 y, usando el marcador de relleno, copie esta fórmula con referencias relativas al rango de celdas D5:D13. Cabe señalar que cada celda con una fórmula de regresión lineal del rango de celdas D4:D13 tiene como argumento una celda correspondiente del rango A4:A13. De manera similar, para la regresión cuadrática, complete el rango de celdas E4:E13, y para la regresión cúbica, complete el rango de celdas F4:F13. Así se ha elaborado una previsión de beneficios de la empresa para los años 2003 y 2004. utilizando tres tendencias. La tabla de valores resultante se muestra en la Fig. 6.
Problema 2
- Construye un diagrama.
- Agregue líneas de tendencia logarítmicas, de potencia y exponenciales al gráfico.
- Deducir las ecuaciones de las líneas de tendencia obtenidas, así como los valores de confiabilidad de la aproximación R2 para cada una de ellas.
- Utilizando las ecuaciones de las líneas de tendencia, obtenga datos tabulares sobre las ganancias de la empresa para cada línea de tendencia para 1995-2002.
- Haga un pronóstico de las ganancias de la empresa para 2003 y 2004 utilizando estas líneas de tendencia.
solución del problema
Siguiendo la metodología dada al resolver el problema 1, obtenemos un diagrama al que se le agregan líneas de tendencia logarítmica, de potencia y exponencial (Fig. 7). A continuación, utilizando las ecuaciones de la línea de tendencia obtenidas, completamos una tabla de valores para las ganancias de la empresa, incluidos los valores previstos para 2003 y 2004. (Figura 8).
En la figura. 5 y fig. Se puede observar que el modelo con tendencia logarítmica corresponde al valor más bajo de confiabilidad de aproximación.
R2 = 0,8659
Los valores más altos de R2 corresponden a modelos de tendencia polinómica: cuadrático (R2 = 0,9263) y cúbico (R2 = 0,933).
Problema 3
Con la tabla de datos sobre las ganancias de una empresa de transporte por carretera para 1995-2002, que figura en la tarea 1, se deben realizar los siguientes pasos.
- Obtenga series de datos para líneas de tendencia lineales y exponenciales utilizando las funciones TENDENCIA y CRECIMIENTO.
- Utilizando las funciones TENDENCIA y CRECIMIENTO, haga un pronóstico de las ganancias de la empresa para 2003 y 2004.
- Construya un diagrama para los datos originales y la serie de datos resultante.
solución del problema
Usemos la hoja de trabajo para el Problema 1 (ver Fig. 4). Comencemos con la función TENDENCIA:
- seleccione el rango de celdas D4:D11, que deben completarse con los valores de la función TENDENCIA correspondientes a los datos conocidos sobre las ganancias de la empresa;
- Llame al comando Función desde el menú Insertar. En el cuadro de diálogo Asistente de funciones que aparece, seleccione la función TENDENCIA de la categoría Estadística y luego haga clic en el botón Aceptar. La misma operación se puede realizar haciendo clic en el botón (Insertar función) en la barra de herramientas estándar.
- En el cuadro de diálogo Argumentos de función que aparece, ingrese el rango de celdas C4:C11 en el campo Valores_conocidos_y; en el campo Known_values_x - el rango de celdas B4:B11;
- Para que la fórmula ingresada se convierta en una fórmula matricial, use la combinación de teclas + +.
La fórmula que ingresamos en la barra de fórmulas se verá así: =(TENDENCIA(C4:C11,B4:B11)).
Como resultado, el rango de celdas D4:D11 se llena con los valores correspondientes de la función TENDENCIA (Fig. 9).
Realizar una previsión de los beneficios de la empresa para los años 2003 y 2004. necesario:
- seleccione el rango de celdas D12:D13 donde se ingresarán los valores predichos por la función TENDENCIA.
- llame a la función TENDENCIA y en el cuadro de diálogo Argumentos de función que aparece, ingrese en el campo Valores_conocidos_y - el rango de celdas C4:C11; en el campo Known_values_x - el rango de celdas B4:B11; y en el campo New_values_x - el rango de celdas B12:B13.
- convierta esta fórmula en una fórmula matricial usando la combinación de teclas Ctrl + Shift + Enter.
- La fórmula ingresada se verá así: =(TENDENCIA(C4:C11;B4:B11;B12:B13)), y el rango de celdas D12:D13 se completará con los valores predichos de la función TENDENCIA (ver Fig. 9).
La serie de datos se completa de manera similar usando la función CRECIMIENTO, que se usa en el análisis de dependencias no lineales y funciona exactamente de la misma manera que su contraparte lineal TENDENCIA.
La Figura 10 muestra la tabla en modo de visualización de fórmulas.
Para los datos iniciales y la serie de datos obtenidos, el diagrama que se muestra en la Fig. 11.
Problema 4
Con el cuadro de datos sobre la recepción de solicitudes de servicios por parte del servicio de despacho de una empresa de autotransporte para el período del 1 al 11 del mes en curso, se deben realizar las siguientes acciones.
- Obtenga series de datos para regresión lineal:utilizando las funciones PENDIENTE y CORTE; utilizando la función ESTIMACIÓN LINEAL.
- Obtenga una serie de datos para regresión exponencial usando la función LGRFPRIBL.
- Utilizando las funciones anteriores, haga una previsión de la recepción de solicitudes al servicio de despacho para el período del 12 al 14 del mes en curso.
- Cree un diagrama para la serie de datos original y recibida.
solución del problema
Tenga en cuenta que, a diferencia de las funciones TENDENCIA y CRECIMIENTO, ninguna de las funciones enumeradas anteriormente (PENDIENTE, INTERCEPCIÓN, ESTIMACIÓN LINEAL, LGRFPRIB) es regresión. Estas funciones desempeñan sólo un papel de apoyo, determinando los parámetros de regresión necesarios.
Para las regresiones lineales y exponenciales construidas utilizando las funciones PENDIENTE, INTERCEPCIÓN, ESTILO LINEAL, LGRFPRIB, la apariencia de sus ecuaciones siempre se conoce, a diferencia de las regresiones lineales y exponenciales correspondientes a las funciones TENDENCIA y CRECIMIENTO.
1 . Construyamos una regresión lineal con la ecuación:
y = mx+b
utilizando las funciones PENDIENTE e INTERCEPCIÓN, con la pendiente de regresión m determinada por la función PENDIENTE y el término libre b por la función INTERCEPCIÓN.
Para ello llevamos a cabo las siguientes acciones:
- ingrese la tabla original en el rango de celdas A4:B14;
- el valor del parámetro m se determinará en la celda C19. Seleccione la función Pendiente de la categoría Estadística; ingrese el rango de celdas B4:B14 en el campo valores_conocidos_y y el rango de celdas A4:A14 en el campo valores_conocidos_x. La fórmula se ingresará en la celda C19: =PENDIENTE(B4:B14,A4:A14);
- Utilizando una técnica similar, se determina el valor del parámetro b en la celda D19. Y su contenido se verá así: =SEGMENTO(B4:B14,A4:A14).Así, los valores de los parámetros myb necesarios para construir la regresión lineal se almacenarán en las celdas C19, D19, respectivamente;
- Luego, ingrese la fórmula de regresión lineal en la celda C4 en la forma: =$C*A4+$D. En esta fórmula, las celdas C19 y D19 están escritas con referencias absolutas (la dirección de la celda no debe cambiar durante una posible copia). El signo de referencia absoluto $ se puede escribir desde el teclado o usando la tecla F4, después de colocar el cursor en la dirección de la celda. Usando el controlador de relleno, copie esta fórmula en el rango de celdas C4:C17. Obtenemos la serie de datos requerida (Fig. 12). Debido a que el número de solicitudes es un número entero, debe establecer el formato numérico con el número de decimales en 0 en la pestaña Número de la ventana Formato de celda.
2 . Ahora construyamos una regresión lineal dada por la ecuación:
y = mx+b
utilizando la función ESTIMACIÓN LINEAL.
Para hacer esto:
- Ingrese la función ESTIMACIÓN LINEAL en el rango de celdas C20:D20 como una fórmula matricial: =(ESTIMACIÓN LINEAL(B4:B14,A4:A14)). Como resultado, obtenemos el valor del parámetro m en la celda C20 y el valor del parámetro b en la celda D20;
- ingrese la fórmula en la celda D4: =$C*A4+$D;
- copie esta fórmula usando el marcador de relleno en el rango de celdas D4:D17 y obtenga la serie de datos deseada.
3 . Construimos una regresión exponencial con la ecuación:
y = bmx
utilizando la función LGRFPRIBL se realiza de manera similar:
En el rango de celdas C21:D21 ingresamos la función LGRFPRIBL como fórmula matricial: =( LGRFPRIBL (B4:B14,A4:A14)). En este caso, el valor del parámetro m se determinará en la celda C21 y el valor del parámetro b se determinará en la celda D21;
En la figura. La Figura 13 muestra una tabla donde se pueden ver las funciones que utilizamos con los rangos de celdas requeridos, así como fórmulas.
Para los datos iniciales y la serie de datos obtenidos, el diagrama que se muestra en la Fig. 14.
Lo que hace diferente la realización de tareas para construir una línea de tendencia es que los datos de origen pueden ser un conjunto de números no relacionados entre sí.
Es imposible hacer pronósticos utilizando un cronograma regular, ya que su coeficiente de determinismo (R^2) será cercano a cero.
Por eso se utilizan funciones especiales.
Ahora los construiremos, configuraremos y analizaremos.
Versión fácil de construir
El proceso de construcción de una línea de tendencia consta de tres etapas: ingresar datos iniciales en Excel, trazar un gráfico y seleccionar una línea de tendencia y sus parámetros.
Comencemos con la entrada de datos.
1. Crea una tabla en Excel con los datos de origen.
(Figura 1)
2. Seleccione las celdas B3: B17 y vaya a la pestaña "Insertar" y seleccione "Gráfico".
(Figura 2)
3. Una vez creado el gráfico, puede agregar leyendas y un título.
Primero, haga clic izquierdo en el borde del gráfico para seleccionarlo.
Luego vaya a la pestaña "Diseño" y seleccione "Diseño 1".
(Figura 3)
4. Pasemos a construir una línea de tendencia. Para hacer esto, seleccione el gráfico nuevamente y vaya a la pestaña "Diseño".
(Figura 4)
5. Haga clic en el botón "Línea de tendencia" y seleccione "ajuste lineal" o "ajuste exponencial".
(Figura 5)
Así que construimos una línea de tendencia primaria, que puede no corresponderse mucho con la realidad.
Este es nuestro resultado intermedio.
(Figura 6)
Y por tanto necesitaremos ajustar los parámetros de nuestra línea de tendencia o seleccionar otra función.
Versión profesional: selección de una línea de tendencia y configuración de parámetros
6. Haga clic en el botón "Línea de tendencia" y seleccione "Parámetros adicionales y líneas de tendencia".
(Figura 7)
7. En la ventana “Formato de línea de tendencia”, marcamos la casilla junto a “colocar el valor de confiabilidad de aproximación R^2 en el diagrama y hacemos clic en el botón “cerrar”.
Vemos en el diagrama el coeficiente R^2= 0.6442
(Figura 8)
8. Cancele los cambios. Seleccione el gráfico, haga clic en la pestaña "Diseño", botón "línea de tendencia" y seleccione "No".
9. Vaya a la ventana "Formato de línea de tendencia", pero para seleccionar la línea de tendencia "Polinomial", cambiamos el grado, logrando indicadores de coeficiente R^2 = 0,8321
(Figura 9)
Pronóstico
Si necesitamos adivinar qué datos se podrían obtener en la siguiente medición, en la ventana “Formato de línea de tendencia” indicamos el número de periodos para los que se realiza la previsión.
(Figura 10)
Según el pronóstico, podemos suponer que el 25 de enero el número de puntos anotados estaría entre 60 y 70.
Conclusión
Y finalmente, si está interesado en la fórmula mediante la cual se construye la tendencia, en la sección "Formato de línea de tendencia", marque la casilla junto a "mostrar la ecuación en el gráfico".
Ahora sabes cómo completar la tarea y construir una línea de tendencia, incluso en un programa como Excel 2010.
Haz preguntas, no seas tímido.
