En este tutorial, encontrará la descripción detallada de todas las opciones de línea de tendencia disponibles en Excel y cuándo usarlas. También aprenderá a mostrar una ecuación de línea de tendencia en un gráfico y a encontrar la pendiente de la línea de tendencia.
Es muy fácil agregar una línea de tendencia en Excel. El único desafío real es elegir el tipo de línea de tendencia que mejor se corresponda con el tipo de datos que está analizando. En este tutorial, encontrará la descripción detallada de todas las opciones de línea de tendencia disponibles en Excel y cuándo usarlas. Si está buscando cómo insertar una línea de tendencia en un gráfico de Excel, consulte el tutorial vinculado anteriormente.
Tipos de línea de tendencia de Excel
Al agregar una línea de tendencia en Excel, tiene 6 opciones diferentes para elegir. Además, Microsoft Excel permite mostrar una ecuación de línea de tendencia y un valor de R cuadrado en un gráfico:
- Ecuación de línea de tendencia es una fórmula que encuentra una línea que mejor se ajusta a los puntos de datos.
- Valor R cuadrado mide la fiabilidad de la línea de tendencia: cuanto más cerca está R2 de 1, mejor se ajusta la línea de tendencia a los datos.
A continuación, encontrará una breve descripción de cada tipo de línea de tendencia con ejemplos de gráficos.
línea de tendencia lineal
La línea de tendencia lineal se utiliza mejor con conjuntos de datos lineales cuando los puntos de datos en un gráfico se asemejan a una línea recta. Por lo general, una línea de tendencia lineal describe un aumento o una caída continuos a lo largo del tiempo.
Por ejemplo, la siguiente línea de tendencia lineal muestra un aumento constante en las ventas durante 6 meses. Y el valor R2 de 0,9855 indica un ajuste bastante bueno de los valores de línea de tendencia estimados a los datos reales.
línea de tendencia exponencial
La línea de tendencia exponencial es una línea curva que ilustra un aumento o una caída en los valores de los datos a un ritmo creciente, por lo tanto, la línea suele ser más curva en un lado. Este tipo de línea de tendencia se usa a menudo en ciencias, por ejemplo, para visualizar el crecimiento de una población humana o la disminución de las poblaciones de vida silvestre.
Tenga en cuenta que no se puede crear una línea de tendencia exponencial para datos que contengan ceros o valores negativos.
Un buen ejemplo de una curva exponencial es la disminución de la población total de tigres salvajes en la tierra.
línea de tendencia logarítmica
La línea de mejor ajuste logarítmico generalmente se usa para trazar datos que aumentan o disminuyen rápidamente y luego se estabilizan. Puede incluir valores tanto positivos como negativos.
Un ejemplo de una línea de tendencia logarítmica puede ser una tasa de inflación, que primero aumenta pero luego de un tiempo se estabiliza.
línea de tendencia polinomial
La línea de tendencia curvilínea polinomial funciona bien para grandes conjuntos de datos con valores oscilantes que tienen más de una subida y bajada.
Generalmente, un polinomio se clasifica por el grado del mayor exponente. El grado de la línea de tendencia polinomial también se puede determinar por el número de curvas en un gráfico. Por lo general, una línea de tendencia polinómica cuadrática tiene una curva (colina o valle), un polinomio cúbico tiene 1 o 2 curvas y un polinomio cuártico tiene hasta 3 curvas.
Al agregar una línea de tendencia polinomial en un gráfico de Excel, especifique el grado escribiendo el número correspondiente en el Ordenar en el panel Formato de línea de tendencia, que es 2 de forma predeterminada:
Por ejemplo, la tendencia del polinomio cuadrático es evidente en el siguiente gráfico que muestra la relación entre la ganancia y la cantidad de años que el producto ha estado en el mercado: aumento al principio, pico en el medio y caída cerca del final.
línea de tendencia de potencia
La línea de tendencia de potencia es muy similar a la curva exponencial, solo que tiene un arco más simétrico. Se usa comúnmente para trazar medidas que aumentan a una cierta tasa.
No se puede agregar una línea de tendencia de potencia a un gráfico de Excel que contiene valores cero o negativos.
Como ejemplo, dibujemos una línea de tendencia de potencia para visualizar la tasa de reacción química. Tenga en cuenta el valor de R cuadrado de 0,9918, lo que significa que nuestra línea de tendencia se ajusta a los datos casi a la perfección.
Línea de tendencia de la media móvil
Cuando los puntos de datos en su gráfico tienen muchos altibajos, una línea de tendencia de promedio móvil puede suavizar las fluctuaciones extremas en los valores de datos para mostrar un patrón más claramente. Para ello, Excel calcula la media móvil de la cantidad de periodos que especifiques (2 por defecto) y pone esos valores medios como puntos en la línea. Cuanto mayor sea el valor del Período, más suave será la línea.
Un buen ejemplo práctico es usar la línea de tendencia del promedio móvil para revelar fluctuaciones en el precio de una acción que, de otro modo, serían difíciles de observar.
Para obtener más información, consulte: Cómo agregar una línea de tendencia de promedio móvil a un gráfico de Excel.
Ecuaciones y fórmulas de línea de tendencia de Excel
Esta sección describe las ecuaciones que utiliza Excel para diferentes tipos de líneas de tendencia. No tiene que crear estas fórmulas manualmente, simplemente dígale a Excel que muestre la ecuación de la línea de tendencia en un gráfico.
Además, discutiremos la fórmula para encontrar la pendiente de una línea de tendencia y otros coeficientes. Las fórmulas asumen que tiene 2 conjuntos de variables: variable independiente x y variable dependiente y. En sus hojas de trabajo, puede usar estas fórmulas para obtener los valores de y pronosticados para cualquier valor dado de x.
Para mantener la coherencia, utilizaremos el mismo conjunto de datos con valores ligeramente diferentes para todos los ejemplos. Sin embargo, tenga en cuenta que es solo para fines de demostración. En sus hojas de trabajo reales, debe elegir el tipo de línea de tendencia correspondiente a su tipo de datos.
¡Nota IMPORTANTE! Las fórmulas de la línea de tendencia solo deben usarse con gráficos de dispersión XY porque solo este gráfico traza los ejes x e y como valores numéricos. Para obtener más información, consulte Por qué la ecuación de línea de tendencia de Excel puede ser incorrecta.
Ecuación y fórmulas de línea de tendencia lineal
La ecuación de línea de tendencia lineal utiliza los métodos de mínimos cuadrados para buscar los coeficientes de pendiente e intersección de manera que:
y = bx + a
Dónde:
- b es el Pendiente de una línea de tendencia.
- un es el intercepto en yque es el valor medio esperado de y cuando todas las variables x son iguales a 0. En un gráfico, es el punto donde la línea de tendencia cruza el eje y.
Para la regresión lineal, Microsoft Excel proporciona funciones especiales para obtener los coeficientes de pendiente e intersección.
Pendiente de la línea de tendencia
b: =SLOPE(y,x)
Intersección Y
a: =INTERCEPT(y,x)
Suponiendo que el rango x es B2:B13 y el rango y es C2:C13, las fórmulas de la vida real son las siguientes:
=SLOPE(C2:C13, B2:B13)
=INTERCEPT(C2:C13,B2:B13)
Se pueden lograr los mismos resultados usando la función ESTIMACION.LINEAL como fórmula matricial. Para esto, seleccione 2 celdas adyacentes en la misma fila, ingrese la fórmula y presione Ctrl + Shift + Enter para completarla:
=LINEST(C2:C13,B2:B13)
Como se muestra en la siguiente captura de pantalla, los coeficientes de pendiente e intercepción devueltos por las fórmulas están perfectamente alineados con los coeficientes de la ecuación de la línea de tendencia lineal que se muestra en el gráfico, solo que estos últimos se redondean a 4 decimales:
Ecuación y fórmulas de línea de tendencia exponencial
Para la línea de tendencia exponencial, Excel usa la siguiente ecuación:
y = abx
Donde a y b son coeficientes calculados y e es la constante matemática e (la base del logaritmo natural).
Los coeficientes se pueden calcular utilizando estas fórmulas genéricas:
a: =EXP(INDEX(LINEST(LN(y), x), 1, 2))
b: =INDEX(LINEST(LN(y), x), 1)
Para nuestro conjunto de datos de muestra, las fórmulas toman la siguiente forma:
a: =EXP(INDEX(LINEST(LN(C2:C13), B2:B13), 1, 2))
b: =INDEX(LINEST(LN(C2:C13), B2:B13), 1)
Fórmulas y ecuación de línea de tendencia logarítmica
Aquí está la ecuación de línea de tendencia logarítmica en Excel:
y = a*ln(x)+b
Donde a y b son constantes y ln es la función logaritmo natural.
Para obtener las constantes, use estas fórmulas genéricas, que solo difieren en el último argumento:
a: =INDEX(LINEST(y, LN(x)), 1)
b: =INDEX(LINEST(y, LN(x)), 1, 2)
Para nuestro conjunto de datos de muestra, usamos estos:
a: =INDEX(LINEST(C2:C13, LN(B2:B13)), 1)
b: =INDEX(LINEST(C2:C13, LN(B2:B13)), 1, 2)
Ecuación y fórmulas de la línea de tendencia polinomial
Para calcular la línea de tendencia del polinomio, Excel usa esta ecuación:
y = b6x6 + … + b2x2 + b1x + a
Donde b1…b6 y a son constantes.
Según el grado de la línea de tendencia polinomial, utilice uno de los siguientes conjuntos de fórmulas para obtener las constantes.
Línea de tendencia polinomial cuadrática (segundo orden)
Ecuación: y = b2x2+ b1x + a
b2: =INDEX(LINEST(y, x^{1,2}), 1)
b1: =INDEX(LINEST(y, x^{1,2}), 1, 2)
a: =INDEX(LINEST(y, x^{1,2}), 1, 3)
Línea de tendencia polinomial cúbica (3er orden)
Ecuación: y = b3x3 + b2x2+ b1x + a
b3: =INDEX(LINEST(y, x^{1,2,3}), 1)
b2: =INDEX(LINEST(y, x^{1,2,3}), 1, 2)
b1: =INDEX(LINEST(y, x^{1,2,3}), 1, 3)
a: =INDEX(LINEST(y, x^{1,2,3}), 1, 4)
Las fórmulas para líneas de tendencia polinómicas de mayor grado se pueden construir usando el mismo patrón.
Para nuestro conjunto de datos, la línea de tendencia polinomial de segundo orden se adapta mejor, por lo que estamos usando estas fórmulas:
b2: =INDEX(LINEST(C2:C13, B2:B13^{1,2}), 1)
b1: =INDEX(LINEST(C2:C13, B2:B13^{1,2}), 1, 2)
a: =INDEX(LINEST(C2:C13, B2:B13^{1,2}), 1, 3)
Ecuación y fórmulas de línea de tendencia de potencia
Se dibuja una línea de tendencia de potencia en Excel basada en esta simple ecuación:
y = axb
Donde a y b son constantes, que se pueden calcular con estas fórmulas:
a: =EXP(INDEX(LINEST(LN(y), LN(x),,), 1, 2))
b: =INDEX(LINEST(LN(y), LN(x),,), 1)
En nuestro caso, las siguientes fórmulas funcionan de maravilla:
a: =EXP(INDEX(LINEST(LN(C2:C13), LN(B2:B13),,), 1, 2))
b: =INDEX(LINEST(LN(C2:C13), LN(B2:B13),,), 1)
La ecuación de línea de tendencia de Excel es incorrecta: razones y correcciones
Si cree que Excel ha dibujado una línea de tendencia incorrectamente o que la fórmula de la línea de tendencia que se muestra en su gráfico es incorrecta, los siguientes dos puntos pueden arrojar algo de luz sobre la situación.
La ecuación de línea de tendencia de Excel es correcta solo en gráficos de dispersión
Las fórmulas de línea de tendencia de Excel solo deben usarse con gráficos XY (dispersión) porque solo en este tipo de gráfico, tanto el eje y como el eje x se trazan como valores numéricos.
En los gráficos de líneas, columnas y barras, los valores numéricos se representan solo en el eje y. El eje x está representado por una serie lineal (1, 2, 3,…) sin importar si las etiquetas del eje son números o texto. Cuando crea una línea de tendencia en estos gráficos, Excel usa esos valores x asumidos en la fórmula de la línea de tendencia.
Los números se redondean en la ecuación de línea de tendencia de Excel
Para ocupar menos espacio en el gráfico, Excel muestra muy pocos dígitos significativos en una ecuación de línea de tendencia. Agradable en términos de diseño, reduce significativamente la precisión de la fórmula cuando proporciona manualmente valores x en la ecuación.
Una solución fácil es mostrar más lugares decimales en la ecuación. Como alternativa, puede calcular los coeficientes utilizando una fórmula correspondiente a su tipo de línea de tendencia y formatear las celdas de la fórmula para que muestren una cantidad suficiente de lugares decimales. Para esto, simplemente haga clic en el botón Aumentar decimal en la pestaña Inicio en el grupo Número.
Así es como puede crear diferentes tipos de líneas de tendencia en Excel y obtener sus ecuaciones. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!