Interpolación lineal en Excel | Cómo hacer interpolación lineal con ejemplos

Interpolación lineal de Excel

La interpolación lineal en Excel significa pronosticar o adivinar el próximo valor próximo de cualquier variable determinada en los datos actuales, aquí creamos una línea recta que conecta dos valores y estimamos el valor futuro a través de ella, en Excel usamos la función de pronóstico y una búsqueda función para hacer una interpolación lineal.

La interpolación es una herramienta matemática o estadística que se utiliza para predecir los valores entre 2 puntos en una curva o línea. Esta herramienta no solo se usa en estadísticas, sino que también se usa en muchos otros campos como negocios, ciencia, etc., siempre que exista la oportunidad de predecir valores entre dos puntos de datos.

¿Cómo hacer una interpolación lineal en Excel?

Puede descargar esta plantilla de Excel de interpolación lineal aquí - Plantilla de Excel de interpolación lineal

Ejemplo 1

Realización de interpolación para conocer la temperatura del clima durante diferentes zonas horarias

Primero, anote las cifras de temperatura de la región de Bangalore para cada hora y los datos serían los siguientes: -

Los datos muestran que tenemos los detalles de temperatura de la región de Bangalore para alguna fecha. En la columna de tiempo tenemos las zonas horarias para todo el día y la columna de la hora mencionamos el número de horas desde el inicio del día como 12:00 a.m. sería 0 horas, 1:00 a.m. sería 1 hora, y así en.

Ahora vamos a realizar la interpolación de los datos para extraer el valor de temperatura para la zona horaria requerida, que puede ser en cualquier momento, no solo la hora exacta.

Para realizar la interpolación, tenemos que usar algunas fórmulas en Excel como PRONÓSTICO, DESPLAZAMIENTO, PARTIDO. Veamos brevemente estas fórmulas antes de seguir adelante.

PRONÓSTICO (): esta función de Excel de pronóstico calcula o predice el valor futuro en función de los valores existentes junto con una tendencia lineal.

X : este es el valor que queremos predecir.
Known_ys : estos son los valores dependientes de los datos y un campo obligatorio que se debe completar
Known_xs : estos son los valores independientes de los datos y un campo obligatorio que se debe completar.

MATCH (): esta función de Excel Match devolverá la posición relativa de un valor de búsqueda en una fila, columna o tabla que coincida con el valor especificado en un orden especificado.

Lookup_value : este es el valor que debe coincidir con lookup_array
Lookup_array : este es el rango de búsqueda

[match_type]: puede ser 1,0, -1. El valor predeterminado sería 1. Para 1: la coincidencia encontrará el valor más grande que sea menor o igual al valor de búsqueda y el valor debe estar en orden ascendente. Para 0: Match encuentra el primer valor exactamente igual a lookup_value y no es necesario ordenarlo. Para -1: la coincidencia encontrará el valor más pequeño que sea mayor o igual que el valor de búsqueda y se debe ordenar en orden descendente.

OFFSET (): esta función de compensación devolverá una celda o un rango de celdas que tienen un número especificado de filas y columnas. La celda o rango de celdas dependerá de la altura y el ancho en las filas y columnas que especifiquemos.

Referencia : este es el punto de partida desde donde se realizará el recuento de filas y columnas.
Filas : número de filas para compensar por debajo de la celda de referencia inicial.
Columnas : número de columnas para desplazar a la derecha desde la celda de referencia inicial.
[altura] : la altura en filas de la referencia devuelta. Esto es opcional.
[ancho] : el ancho en columnas de la referencia devuelta. Esto es opcional.

Como hemos visto brevemente las fórmulas que vamos a utilizar para realizar la interpolación. Ahora realicemos la interpolación de la siguiente manera:

Escriba la fórmula en una celda que necesitamos para ver la temperatura para diferentes zonas horarias. Esto nos dice que tenemos que seleccionar la celda que necesita ser pronosticada y se usa la función de compensación y coincidencia para seleccionar los valores conocidos y xs conocidos.

PRONÓSTICO ($ F $ 5 - Seleccione la celda que tiene la zona horaria a pronosticar.

OFFSET ($ C $ 3: $ C $ 26, MATCH ($ F $ 5, $ B $ 3: $ B $ 26,1) -1,0,2) - Esto se usa para seleccionar el conocido_ys como referencia se toma la columna temporal porque estos son los valores dependientes. La función de coincidencia se utiliza para generar la posición del valor que necesitamos pronosticar y calcular el número de filas. Las columnas deben ser 0 porque queremos el valor dependiente en la misma columna que se seleccionó y la altura es 2, ya que necesitamos realizar el pronóstico basado en los últimos 2 valores.

OFFSET ($ B $ 3: $ B $ 26, MATCH ($ F $ 5, $ B $ 3: $ B $ 26,1) -1,0,2) - Esto se usa para seleccionar el conocido_xs como referencia se toma la columna de hora porque estos son valores independientes y el resto es el mismo que habíamos hecho para el recuento de filas.

Ahora dé alguna zona horaria en la celda que habíamos considerado pronosticar. Aquí el valor ingresado es 19.5 que es 7:30 PM y obtendremos la temperatura de 30 que se pronostica a partir de los valores de temperatura que se dan por hora.

Del mismo modo, podemos ver cifras temporales para diferentes zonas horarias de esta fórmula.

Ejemplo # 2

Realización de interpolación lineal para conocer las ventas de una organización en 2018

Supongamos que obtuvimos los detalles de ventas para una organización en 2018 como se muestra a continuación. Disponemos de datos en cuanto a días y sus ventas en acumulativo. Conseguimos ventas de 7844 unidades en los primeros 15 días del año, 16094 unidades en 50 días del año y así sucesivamente.

Podemos usar la misma fórmula que usamos en la interpolación para pronosticar el valor de las ventas para diferentes días que no se mencionó en los datos que consideramos. Aquí las ventas están en línea recta (lineal) como lo habíamos tomado acumulativamente.

Si queremos ver el número de ventas que hemos logrado en 215 días, podemos obtener el número de ventas pronosticado para 215 días como se muestra a continuación, considerando los datos de ventas proporcionados.

De manera similar, podemos averiguar el número de ventas en ese año pronosticando entre los puntos que se dan.

Cosas para recordar

Es el método menos preciso, pero es rápido y preciso si los valores de la tabla están poco espaciados.
Esto también se puede utilizar para estimar valores para un punto de datos geográficos, precipitaciones, niveles de ruido, etc.
Es muy fácil de usar y no muy preciso para funciones no lineales.
Además de la interpolación lineal de Excel, también tenemos diferentes tipos de métodos como la interpolación polinomial, la interpolación spline, etc.