El tutorial explica cómo hacer un análisis de tendencias en Excel: cómo insertar una línea de tendencia en un gráfico, mostrar su ecuación y obtener la pendiente de una línea de tendencia.
Al trazar datos en un gráfico, a menudo querrá visualizar la tendencia general en sus datos. Esto se puede hacer agregando una línea de tendencia a un gráfico. Afortunadamente, Microsoft Excel ha hecho que insertar una línea de tendencia sea muy fácil, especialmente en las versiones más nuevas. Sin embargo, hay algunos pequeños secretos que marcan una gran diferencia, y los compartiré contigo en un momento.
Línea de tendencia en Excel
A línea de tendenciatambién conocido como un línea de mejor ajustees una línea recta o curva en un gráfico que muestra el patrón general o la dirección general de los datos.
Esta herramienta analítica se usa con mayor frecuencia para mostrar los movimientos de datos durante un período de tiempo o la correlación entre dos variables.
Visualmente, una línea de tendencia se parece un poco a un gráfico de líneas, pero no conecta los puntos de datos reales como lo hace un gráfico de líneas. Una línea de mejor ajuste muestra la tendencia general en todos los datos, ignorando los errores estadísticos y las excepciones menores. En algunos casos, también se puede utilizar para pronosticar tendencias.
Gráficos de Excel que admiten líneas de tendencia
Se puede agregar una línea de tendencia a una variedad de gráficos de Excel, que incluyen dispersión XY, burbuja, stock, así como gráficos de barras, columnas, áreas y líneas en 2D no apilados.
No puede agregar una línea de tendencia a gráficos tridimensionales o apilados, circulares, de radar y elementos visuales similares.
A continuación, hay un ejemplo de un diagrama de dispersión con un línea de tendencia extendida:
Cómo agregar una línea de tendencia en Excel
En Excel 2019, Excel 2016 y Excel 2013, agregar una línea de tendencia es un proceso rápido de 3 pasos:
- Haga clic en cualquier parte del gráfico para seleccionarlo.
- En el lado derecho del gráfico, haga clic en el botón Elementos del gráfico (el botón de la cruz) y luego realice una de las siguientes acciones:
- Marque la casilla Trendline para insertar el lineal por defecto línea de tendencia:
- Haga clic en la flecha junto al cuadro Trendline y elija uno de los tipos sugeridos:
- Haga clic en la flecha junto a Línea de tendencia y luego haga clic en Mas opciones. Esto abrirá el panel Formato de línea de tendencia, donde cambiará a la Opciones de línea de tendencia para ver todos los tipos de líneas de tendencia disponibles en Excel y elija el que desee. La línea de tendencia lineal predeterminada se preseleccionará automáticamente. Opcionalmente, también puede mostrar la ecuación de la línea de tendencia en el gráfico.
Consejo. Otra forma rápida de agregar una línea de tendencia a un gráfico de Excel es hacer clic con el botón derecho en la serie de datos y luego hacer clic en Agregar línea de tendencia….
Cómo hacer una línea de tendencia en Excel 2010
Para agregar una línea de tendencia en Excel 2010, siga una ruta diferente:
- En un gráfico, haga clic en la serie de datos para la que desea dibujar una línea de tendencia.
- En Herramientas de gráfico, vaya a la pestaña Diseño > grupo Análisis, haga clic en línea de tendencia y también:
- Elija una de las opciones predefinidas, o
- Hacer clic Más opciones de línea de tendencia…y luego elija el tipo de línea de tendencia para su gráfico.
Cómo insertar varias líneas de tendencia en el mismo gráfico
Microsoft Excel permite agregar más de una línea de tendencia a un gráfico. Hay dos escenarios que deben manejarse de manera diferente.
Agregar una línea de tendencia para cada serie de datos
Para poner una línea de tendencia en un gráfico que tiene dos o más series de datos, haga lo siguiente:
- Haga clic con el botón derecho en los puntos de datos de interés (los azules en este ejemplo) y elija Agregar línea de tendencia… desde el menú contextual:
- Esto abrirá el Opciones de línea de tendencia pestaña del panel, donde puede elegir el tipo de línea deseado:
- Repita los pasos anteriores para la otra serie de datos.
Como resultado, cada serie de datos tendrá su propia línea de tendencia del color correspondiente:
Alternativamente, puede hacer clic en el botón Elementos del gráfico, luego hacer clic en la flecha al lado de línea de tendencia y elige el tipo que quieras. Excel mostrará una lista de las series de datos trazadas en su gráfico. Eliges el que necesitas y haces clic en Aceptar.
Dibujar diferentes tipos de líneas de tendencia para la misma serie de datos
Para crear dos o más líneas de tendencia diferentes para la misma serie de datos, agregue la primera línea de tendencia como de costumbre y luego realice una de las siguientes acciones:
- Haga clic derecho en la serie de datos, seleccione Agregar línea de tendencia… en el menú contextual y luego elija un tipo de línea de tendencia diferente en el panel.
- Haga clic en el botón Elementos del gráfico, haga clic en la flecha junto a línea de tendencia y elija el tipo que desea agregar.
De cualquier manera, Excel mostrará múltiples líneas de tendencia en el gráfico, lineal y promedio móvil en nuestro caso, para lo cual puede establecer diferentes colores:
Cómo formatear una línea de tendencia en Excel
Para que su gráfico sea aún más comprensible y fácil de interpretar, es posible que desee cambiar la apariencia predeterminada de una línea de tendencia. Para esto, haga clic derecho y luego haga clic en Formato de línea de tendencia… . O simplemente haga doble clic en la línea de tendencia para abrir el panel Formato de línea de tendencia.
En el panel, cambie a la pestaña Relleno y línea y elija el color, el ancho y el tipo de guión para su línea de tendencia. Por ejemplo, puede convertirlo en una línea sólida en lugar de una línea discontinua:
Cómo extender la línea de tendencia en Excel
Para proyectar las tendencias de datos hacia el futuro o el pasado, esto es lo que debe hacer:
- Haga doble clic en la línea de tendencia para abrir el panel Formato de línea de tendencia.
- En la pestaña Opciones de línea de tendencia (la última), escriba los valores deseados en el Delantero y/o Hacia atrás casillas bajo Pronóstico:
En este ejemplo, elegimos extender la línea de tendencia durante 8 períodos más allá del último punto de datos:
Ecuación de línea de tendencia de Excel
Ecuación de línea de tendencia es una fórmula que describe matemáticamente la línea que mejor se ajusta a los puntos de datos. Las ecuaciones son diferentes para diferentes tipos de líneas de tendencia, aunque en cada ecuación Excel usa el mínimos cuadrados método para encontrar el mejor ajuste para una línea a través de puntos de datos. Puede encontrar las ecuaciones para todos los tipos de líneas de tendencia de Excel en este tutorial.
Al dibujar la línea de mejor ajuste en Excel, puede mostrar su ecuación en un gráfico. Además, puede mostrar el valor R-cuadrado.
Valor R cuadrado (Coeficiente de determinación) indica qué tan bien se corresponde la línea de tendencia con los datos. Cuanto más cerca esté el valor de R2 de 1, mejor será el ajuste.
Cómo mostrar la ecuación de la línea de tendencia en un gráfico
Para mostrar la ecuación y el valor de R cuadrado en un gráfico, haga lo siguiente:
- Haga doble clic en la línea de tendencia para abrir su panel.
- En el panel, cambie a la pestaña Opciones de línea de tendencia y marque estas casillas:
- Mostrar ecuación en el gráfico
- Muestra el valor R-cuadrado en el gráfico
Esto colocará la fórmula de la línea de tendencia y el valor R2 en la parte superior de su gráfico, y puede arrastrarlos donde lo considere oportuno.
En este ejemplo, el valor de R cuadrado es igual a 0,957, lo que significa que la línea de tendencia se ajusta a alrededor del 95 % de los valores de los datos.
Mostrar más dígitos en la ecuación de la línea de tendencia
Si la ecuación de la línea de tendencia de Excel ofrece resultados inexactos cuando le proporciona valores x manualmente, lo más probable es que se deba al redondeo. De forma predeterminada, los números en la ecuación de la línea de tendencia se redondean a 2 – 4 lugares decimales. Sin embargo, puede hacer visibles más dígitos fácilmente. Así es cómo:
- Seleccione la fórmula de la línea de tendencia en el gráfico.
- En el panel Formato de etiqueta de línea de tendencia que aparece, vaya a la Opciones de etiqueta pestaña.
- En la lista desplegable Categoría, seleccione Número.
- En el cuadro Posiciones decimales, escriba el número de lugares decimales desea mostrar (hasta 30) y presione Entrar para actualizar la ecuación en el gráfico.
Cómo encontrar la pendiente de una línea de tendencia en Excel
Para obtener la pendiente de la línea de tendencia linealMicrosoft Excel proporciona una función especial del mismo nombre:
PENDIENTE(conocido_y, conocido_x)
Dónde:
- Conocidos es un rango de los puntos de datos dependientes trazados en el eje y.
- conocidos_x es un rango de los puntos de datos independientes trazados en el eje x.
Con los valores de x en B2:B13 y los valores de y en C2:C13, la fórmula es la siguiente:
=SLOPE(C2:C13, B2:B13)
La pendiente también se puede calcular usando la función ESTIMACION.LINEAL en una fórmula regular:
=LINEST(C2:C13,B2:B13)
Si se ingresa como un fórmula matricial al presionar Ctrl + Shift + Enter, devolvería la pendiente de la línea de tendencia y la intersección y en dos celdas adyacentes en la misma fila. Para obtener más información, consulte Cómo usar la función ESTIMACION.LINEAL en Excel.
Como puede ver en la siguiente captura de pantalla, el valor de pendiente devuelto por las fórmulas coincide exactamente con el coeficiente de pendiente en la ecuación de la línea de tendencia lineal que se muestra en nuestro gráfico:
Los coeficientes de otros tipos de ecuaciones de línea de tendencia (exponencial, polinómica, logarítmica, etc.) también se pueden calcular, pero necesitará usar fórmulas más complejas que se explican en las ecuaciones de línea de tendencia de Excel.
Cómo eliminar una línea de tendencia en Excel
Para eliminar una línea de tendencia de su gráfico, haga clic con el botón derecho en la línea y luego haga clic en Borrar:
O haga clic en el botón Elementos del gráfico y anule la selección del línea de tendencia caja:
De cualquier manera, Excel eliminará inmediatamente la línea de tendencia de un gráfico.
Así es como se hace una línea de tendencia en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!