La función TENDENCIA
Devuelve valores que resultan de una tendencia lineal.
Sintaxis
TENDENCIA(conocido_y, [conocido_x], [nueva_matriz_x], [constante])
- conocido_y (obligatorio): Valores y, conocidos en la ecuación y = mx + b.
- conocido_x (opcional): Valores x, conocidos en la ecuación y = mx + b.
- nueva_matriz_x (opcional): Nuevos valores x para los que se desea obtener los valores de y.
- constante (opcional): Valor de la constante b en la ecuación y = mx + b.
Esta función, en vez de pronosticar lo que va a ocurrir en un sólo período, "extiende" el pronóstico a un rango de celdas que abarcan varios períodos (ya sean días, meses, años, etc.) que previamente debemos seleccionar, logrando una continuidad en el tiempo. Abajo podemos ver a la izquierda el resultado de la función y a la derecha la gráfica que representa a dicha solución.
Para saber como utilizar la función TENDENCIA, podemos ver el siguiente vídeo-tutorial:
Otros ejemplos :
Líneas de tendencia y función PRONOSTICO
Los resultados comparativos de la evolución del precio de un producto en el extranjero y en nuestro país a lo largo una serie de meses se han colocado en una hoja de cálculo (B2:D11).
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Vamos a repetir el ejercicio en otra hoja usando la función PRONOSTICO.
En F3 escribimos 1.
Se constata que el precio se va encareciendo a un ritmo superior en el extranjero, por lo que, si la evolución continúa al mismo ritmo, llegará un momento en que ambos precios se igualarán y, a partir de entonces, será más económica la compra en el mercado nacional.
Como es lógico, interesa calcular cuándo se produce el encuentro. Para ello, trazaremos el gráfico de la evolución de precios, obtendremos las líneas de regresión (líneas de tendencia en Excel) y calcularemos dónde se cruzan.
Seleccionamos B2:D11 y accedemos a Insertar + Dispersión + Dispersión con líneas rectas y marcadores.
Hacemos doble clic en la leyenda y, en Opciones de leyenda, ponemos una marca en Inferior.
Para hallar la línea de tendencia, hacemos clic con el botón derecho en la línea de la serie Nacional(marrón), elegimos Agregar línea de tendencia y ponemos:
- Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Nac / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado).
Repetimos la operación con la línea de la serie Extranjero (azul), poniendo los mismos valores excepto el nombre:
- Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Ext / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado).
Podemos cambiar el tamaño del gráfico, los colores de las líneas, marcadores, fondos..., y dejarlo a nuestro gusto. También conviene arrastrar las ecuaciones a lugares que permitan asociarlas fácilmente con sus correspondientes líneas de regresión.
Se aprecia que entre los meses 19 y 20 se cruzan las dos líneas. A partir de ese momento será más rentable comprar en el mercado nacional. Aunque esto es una previsión, y no tiene mucho sentido conocer el momento exacto del encuentro, podemos calcular matemáticamente cuándo se producirá usando la herramienta de análisis de hipótesis Buscar objetivo. Lo haremos en F2:I3.
Ponemos 1 en F3 (vale cualquier cifra). En el mes 1, los precios en el Extranjero y en el mercado Nacional son 1,1 y 35,5 respetivamente pero las ecuaciones de las líneas de regresión nos darán valores ligeramente diferentes (no pasan exactamente por los puntos del gráfico).
En G3:
=3,7033*F3-4,0944 [Resultado: -0,3911]
En H3:
=1,7433*F3+34,017 [Resultado: 35,7603]
En I3:
=G3-H3 [Resultado: -36,1514]
Con Buscar objetivo calcularemos el valor de hay que poner en G3 para que la diferencia de precios obtenida en I3 sea 0.
Accedemos a Datos + Análisis Y si + Buscar objetivo. En Definir la celda, ponemos I3; en Con valor, escribimos 0; en Para cambiar la celda, seleccionamos F3. Pulsando Aceptar Excel nos indica que ha encontrado la solución.
Cuando transcurran 19,4445918 meses se prevé que se produzca la coincidencia de precios.
El ejercicio puede resolverse sin crear el gráfico. La función PRONOSTICO devuelve los mismos valores que los obtenidos usando las ecuaciones de las líneas de regresión.
PRONOSTICO(x;conocido_y;conocido_x)
Calcula o pronostica un valor futuro a través de los valores existentes. La predicción del valor es un valor y teniendo en cuenta un valor x. Los valores conocidos son valores x y valores y existentes, y el nuevo valor se pronostica utilizando regresión lineal. Esta función se puede utilizar para realizar previsiones de ventas, establecer requisitos de inventario o tendencias de los consumidores.
- x: Obligatorio. El punto de datos cuyo valor se desea predecir.
- conocido_y: Obligatorio. La matriz o rango de datos dependientes.
- conocido_x: Obligatorio. La matriz o rango de datos independientes.
Vamos a repetir el ejercicio en otra hoja usando la función PRONOSTICO.
En F3 escribimos 1.
En G3:
=PRONOSTICO(F3;C3:C11;B3:B11) [Resultado: -0,39111111]
En H3:
=PRONOSTICO(F3;D3:D11;B3:B11) [Resultado: 35,76]
En I3:
=G3-H3 [Resultado: -36,1511111]
Los valores no coinciden exactamente con los obtenidos por el método de las líneas de tendencia debido al número limitado de decimales empleados. PRONOSTICO utiliza todos los decimales posibles, de modo que su resultado es más preciso.
Ahora, accedemos a Datos + Análisis Y si + Buscar objetivo y volvemos a poner los mismos datos:
El resultado es:
