función de tendencia y otras formas de hacer análisis de tendencias en Excel

El tutorial muestra cómo calcular la tendencia en Excel utilizando la función de tendencia, cómo crear tendencias en un gráfico y más.

en estos días en que las tecnologías, los mercados y las necesidades de los clientes están cambiando tan rápidamente, es fundamental que se mueva con las tendencias, y no contra ellas. El análisis de tendencias puede ayudarlo a identificar patrones subyacentes en los movimientos de datos pasados y actuales y proyectar el comportamiento futuro.,

• Excel TREND function – syntax
• Cómo usar la función de tendencia en Excel-ejemplos de fórmulas
  • Análisis de líneas de tendencia basado en datos históricos
  • proyectar tendencia hacia el futuro
  • fórmula de tendencia con múltiples conjuntos de valores x
• otras formas de mostrar tendencias en Excel
  • Excel FORECAST vs., TREND
  • Trendline chart and equation
  • media móvil

función de tendencia de Excel

La función de tendencia de Excel se utiliza para calcular una línea de tendencia lineal a través de un conjunto dado de valores y dependientes y, opcionalmente, un conjunto de valores x independientes y valores de retorno a lo largo de la línea de tendencia.

Además, la función TREND puede extender la línea de tendencia hacia el futuro para proyectar valores y dependientes para un conjunto de nuevos valores X.,

la sintaxis de la función de tendencia de Excel es la siguiente:

Where:

Known_y’s (required) – un conjunto de los valores y dependientes que ya conoce.

Known_x’s (Opcional) – uno o más conjuntos de los valores x independientes.

New_x’s (Opcional) – uno o más conjuntos de nuevos valores x para los que desea calcular la tendencia.

• debe tener el mismo número de columnas o filas que conocido_x.
• Si se omite, se asume que es igual a conocido_x.,

Const (opcional) – un valor lógico que especifica cómo se debe calcular la constante a en la ecuación y = bx + A.

• si es verdadero u omitido, la constante a se calcula normalmente.
• si es FALSE, la constante a es forzada a 0, y los valores b se ajustan para ajustarse a la ecuación y = bx.

cómo la función de tendencia calcula la línea de tendencia lineal

La función de tendencia de Excel encuentra la línea que mejor se adapta a sus datos mediante el método de mínimos cuadrados. La ecuación para la recta es la siguiente.,

Para un rango de valores de x:

y = bx + a

Para varios rangos de valores de x:

y = b1x1 + b2x2 + … + bnxn + una

Donde:

• y la variable dependiente que están tratando de calcular.
• x-la variable independiente que está utilizando para calcular y.
• a-la intersección (indica dónde la línea interseca el eje y y es igual al valor de Y cuando x es 0).
• b – la pendiente (indica la pendiente de la línea).

esta ecuación clásica para la línea de mejor ajuste también es utilizada por la función LINEST y el análisis de regresión lineal.,

función de tendencia como una fórmula de matriz

para devolver múltiples valores y nuevos, la función de tendencia debe ingresarse como una fórmula de matriz. Para esto, seleccione Todas las celdas donde desea que aparezcan los resultados, escriba la fórmula y presione Ctrl + Shift + Enter para completarla. Al hacer esto, la fórmula se obtiene encerrada en {llaves}, que es una indicación visual de una fórmula de matriz. Dado que los nuevos valores se devuelven como una matriz, no podrá editarlos o eliminarlos individualmente.,

ejemplos de fórmula de tendencia de Excel

a primera vista, la sintaxis de la función de tendencia puede parecer excesivamente complicada, pero los siguientes ejemplos harán las cosas mucho más fáciles.

fórmula de tendencia para el análisis de tendencias de series temporales en Excel

supongamos que está analizando algunos datos durante un período secuencial de tiempo y desea detectar una tendencia o patrón.

en este ejemplo, tenemos los números de mes (valores x independientes) en A2:A13 y los números de ventas (valores y dependientes) en B2:B13., Con base en estos datos, queremos determinar la tendencia general en la serie temporal ignorando colinas y valles.

para hacerlo, seleccione el rango C2:C13, escriba la siguiente fórmula y presione Ctrl + Shift + Enter para completarlo:

=TREND(B2:B13,A2:A13)

para dibujar la línea de tendencia, seleccione los valores de ventas y tendencia (B1:C13) y haga un gráfico de líneas (insertar pestaña > > gráfico de líneas o áreas).,

como resultado, tiene tanto los valores numéricos para la línea de mejor ajuste devuelta por la fórmula como una representación visual de esos valores en un gráfico:

proyectar una tendencia futura

para predecir una tendencia para el futuro, solo necesita incluir un conjunto de nuevos valores x en su fórmula de tendencia.,

Para ello, ampliamos nuestra serie de tiempo con un par de meses más números y hacer proyección de tendencias mediante el uso de esta fórmula:

=TREND(B2:B13,A2:A13,A14:A17)

Donde:

• B2:B13 es conocido_y
• A2:A13 es conocido_x
• A14:A17 es nueva_matriz_x

Escriba la fórmula anterior en las celdas C14:C17 y recuerde presionar Ctrl + Mayús + Entrar para completar de forma adecuada. Después de eso, cree un nuevo gráfico de líneas para el conjunto de datos extendido (B1:C17).,

la siguiente captura de pantalla muestra los nuevos valores y calculados y la línea de tendencia extendida:

fórmula de tendencia de Excel para múltiples conjuntos de valores x

en la situación en que tenga dos o más conjuntos de valores x independientes, introdúzcalos en columnas separadas y proporcione todo ese rango al argumento known_x de la función de tendencia.,C13, y los valores known_y en D2:D13, utiliza la siguiente fórmula para calcular la tendencia:

=TREND(D2:D13,B2:C13)

Además, puede ingresar los valores new_x1 y new_x2 en B14:B17 y C14:C17, respectivamente, y obtener los valores y proyectados con esta fórmula:

=TREND(D2:D13,B2:C13,B14:C17)

Si se ingresa correctamente (con el atajo Ctrl + Shift + Enter), las fórmulas arrojan los siguientes resultados:

otras formas de hacer análisis de tendencias en Excel

la función de tendencia es el método de proyección de tendencia más popular pero no el único en Excel., A continuación describiré brevemente algunas otras técnicas.

Excel FORECAST vs TREND

«Trend » y» forecast » son conceptos muy cercanos, pero aún así hay una diferencia:

• Trend es algo que representa los días actuales o pasados. Por ejemplo, al analizar los números de ventas recientes, puede determinar la tendencia del flujo de efectivo y comprender cómo su negocio se ha desempeñado y se está desempeñando actualmente.
• El pronóstico es algo que se relaciona con el futuro., Por ejemplo, al analizar los datos históricos, puede proyectar cambios futuros y predecir a dónde lo llevarán las prácticas comerciales actuales.

en términos de Excel, esta distinción no es tan obvia porque la función TREND no solo puede calcular las tendencias actuales, sino también devolver valores y futuros, es decir, hacer pronósticos de tendencias.

la diferencia entre Tendencia y pronóstico en Excel es la siguiente:

• La función de pronóstico solo puede predecir valores futuros basados en los valores existentes. La función de tendencia puede calcular las tendencias actuales y futuras.,
• La función de pronóstico se utiliza como una fórmula regular y devuelve un único nuevo valor y para un único nuevo valor-X. La función TREND se utiliza como una fórmula de matriz y calcula múltiples valores y para múltiples valores X.

cuando se utiliza para el pronóstico de series temporales, ambas funciones producen la misma tendencia/pronóstico lineal porque sus cálculos se basan en la misma ecuación.,

eche un vistazo a la captura de pantalla a continuación y compare los resultados devueltos por las siguientes fórmulas:

=TREND(B2:B13,A2:A13,A14:A17)

=FORECAST(A14,$B$2:$B$13,$A$2:$A$13)

para obtener más información, consulte usando la función de pronóstico en Excel.

Dibuja una línea de tendencia para visualizar la tendencia

Una Línea de tendencia se usa comúnmente para observar la tendencia general en tus datos actuales, así como para proyectar movimientos de datos futuros.,

para agregar una tendencia a un gráfico existente, haga clic con el botón secundario en la serie de datos y, a continuación, haga clic en Agregar línea de tendencia create esto creará la línea de tendencia lineal predeterminada para los datos actuales y abrirá el panel Formato de línea de tendencia donde puede elegir otro tipo de línea de tendencia.

para pronosticar una tendencia, especifique el número de períodos en previsión en el panel Formato línea de tendencia:

• para proyectar la tendencia en el futuro, escriba el número de períodos en el cuadro Reenviar.
• para extrapolar una tendencia al pasado, escriba el número deseado en el cuadro atrás.,

para mostrar la ecuación de la línea de tendencia, marque la casilla Mostrar ecuación en el gráfico. Para una mayor precisión, puede mostrar más dígitos en la ecuación de la línea de tendencia.

como se muestra en la imagen a continuación, los resultados de la ecuación de línea de tendencia están perfectamente en línea con los números devueltos por las fórmulas de pronóstico y tendencia:

para obtener más información, consulte Cómo agregar una línea de tendencia en Excel.,

tendencia suave con media móvil

otra técnica simple que puede ayudarlo a mostrar una tendencia se llama media móvil (también conocida como promedio móvil o promedio de carrera). Este método suaviza las fluctuaciones a corto plazo en una serie temporal de muestra y destaca patrones o tendencias a largo plazo.

Puede calcular la media móvil manualmente con sus propias fórmulas o hacer que Excel cree una línea de tendencia automáticamente.

para mostrar una línea de tendencia de media móvil en un gráfico, esto es lo que debe hacer:

1. haga clic con el botón derecho en la serie de datos y haga clic en Agregar línea de tendencia.,
2. En el panel Formato de línea de tendencia, seleccione media móvil y especifique el número deseado de períodos.

así es como usa la función de tendencia para calcular tendencias en Excel. Para echar un vistazo más de cerca a las fórmulas discutidas en este tutorial, le invitamos a descargar nuestro ejemplo de libro de tendencias de Excel. Gracias por leer y espero verte en nuestro blog la próxima semana!,

• Excel FORECAST y funciones relacionadas con ejemplos de fórmulas
• Cómo pronosticar en Excel: pronóstico de suavizado Lineal y exponencial
• Cómo usar Goal Seek en Excel para análisis hipotético