Microsoft Excel no cuenta actualmente con una función que nos permita obtener una interpolación lineal de una serie de datos. De modo que, para estimar el valor que tomaría una serie entre dos puntos es necesario hacerlo mediante las funciones de Excel o creando un macro. En el siguiente video se puede ver cómo crear una función para obtener la interpolación lineal en Excel sin necesidad de usar macros de VBA.
Cómo obtener la interpolación lineal entre dos puntos
Al conocer los valores para dos puntos de una serie de datos se puede estimar el resultado que se obtendrá entre ellos usando la fórmula de la interpelación lineal
y = y_1 + (x - x_1) \frac{y_2-y_1}{x_2-x_1}en donde x es el punto sobre el que se desea obtener la estimación, x_1 la posición del primer punto, y_1 el valor del primer punto, x_2 la posición del segundo e x_2 el valor que toma la serie en el segundo.
Obtener los anteriores en una seria
En el caso de que solamente tengamos dos puntos, aplicar la expresión anterior en Excel no es un problema. Pero no así cuando se tienen una serie de datos. En primer lugar es necesario obtener para un valor de x dado los valores anteriores (x_1) y posteriores de la serie (x_2). Algo que se puede conseguir con la función de Excel COINCIDIR(). Función que, por defecto, devuelve la posición del valor buscado o mayor que no supere este. Así, si tenemos en la columna A las posiciones de la serie se puede obtener el índice del valor inferior a x con
=COINCIDIR(x;A2:A10)
reemplazando x por la celda donde se indica el valor a buscar. Para obtener el valor siguiente, solamente se debería sumar 1 al resultado de esta función.
Ahora, una vez obtenida en índice de la posición inmediatamente anterior x, se puede obtener su valor mediante la función INDICE(). De este modo siguiendo con el ejemplo el valor de x_1 se puede obtener con
=INDICE(A2:A10; COINCIDIR(x;A2:A10))
Para el caso de y_1 solamente se debería cambiar la columna A en el primer parámetro de INDICE() por aquella donde se encuentre el valor. Si esta es B, se puede obtener con
=INDICE(B2:B10; COINCIDIR(x;A2:A10))
Por otro lado, para conseguir x_2 e y_2 solamente hay que sumar uno al resultado de COINCIDIR(). De este modo, x_2 se puede conseguir con
=INDICE(A2:A10; COINCIDIR(x;A2:A10)+1)
mientras que y_2 será con
=INDICE(B2:B10; COINCIDIR(x;A2:A10)+1)
Ahora solo nos queda aplicar la fórmula de la interpolación lineal en una nueva celda de la hoja de cálculo.
Problemas fuera de rango
Si asignamos un valor fuera del rango de la serie la función no es válida. O bien los valores de x_1 e x_2 serán el mismo, cuando x esté por debajo del primer valor de la serie, o no contaremos con un valor para x_2, cuando el valor de x esté por encima del último de la serie. Para solucionar este problema se puede recurrir a la función SI() con la que comprobaremos estos casos y daremos un resultado para ello. Por ejemplo, se podría hacer algo como
=SI(E1<A2;B2;SI(E1>A10;B10;E9))
en donde E1 es el valor a buscar, A2 es el primer valor de la serie, A10 el último y el resultado se encuentra en la celda E9. A diferencia de como se muestra en el video, para que nuestra trabajo con cualquier serie de datos se puede condicionar el resultado al último valor de una columna en Excel.
Conclusiones
En esta entrada hemos visto cómo implementar una función de interpolación lineal en Excel sin la necesidad de recurrir a VBA. Algo que puede ser de utilidad cuando necesitemos obtener estimaciones de valores que no tenemos en nuestra serie de datos.
A continuación, os dejo la hoja de cálculo que se ha creado en el video.
Deja una respuesta