¿Cómo realizar una interpolación en una hoja de cálculo de Microsoft Excel? Guía paso a paso

El grado de alcance que tiene Excel, para estudiar relaciones conocidas y trabajar con funciones, es bastante amplio. Por lo que, muchas personas no conocen todo el poder que tiene esta herramienta de cálculo.

Una tarea, un poco complicada, es estimar valores basados en intervalos conocidos. Es decir, establecer con la mayor precisión posible, cuál es el número tomando como referencia valores ciertos.

Debido a esto existe la técnica de interpolación de datos, de la cual hablaremos en los siguientes párrafos. Te enseñaremos los pasos que harás para estimar valores de forma rápida y segura. No te pierdas de nada.

¿Qué es una interpolación de datos y para qué sirve en mi hoja de cálculo?

La interpolación de datos es una herramienta matemática que sirve para estimar datos se encuentran dentro de un rango conocido. Al saber los valores extremos se puede interpretar los que existen dentro de ese conjunto. Lo contrario a la interpolación se conoce como extrapolación y son los datos que se encuentran fuera del rango conocido.

Existen diferentes métodos para conocer los puntos internos. Por lo que los resultados serán estimaciones aproximadas que irán variando de acuerdo con el tipo de método. Entre los más conocidos se encuentran la interpolación lineal, la cuadrática o parabólica y las polinómicas.

Estos métodos son útiles en la práctica para estimar valores partiendo de datos conocidos. Por ejemplo, se puede estimar la cantidad de turistas que visitaron Barcelona en el año 2019 a partir de datos concretos que se obtuvieron en el año 2017. 

Por lo tanto, se puede aplicar a cualquier tipo de información. Ya sea la temperatura ambiental en una determinada zona de nuestro país o Europa, la densidad habitacional, alumnos matriculados en un establecimiento educativo y hasta cantidad de años de vida media discriminado por género de las personas.

Esto quiere decir que Microsoft Excel es una excelente herramienta para usarla y obtener estimaciones por medio de la interpolación de datos.

Tipos de interpolación ¿Cuáles son todas las que existen y se pueden realizar en Excel?

Los tipos de interpolación que puedes utilizar en Excel para estimar valores dentro de un conjunto conocido son los siguientes:

Lineal

Entre los diferentes tipos de interpolación que existen, el método más fácil es el lineal. Esto se debe a que las soluciones posibles de la variable X es una sola. Además, al unir dos puntos en una gráfica, ya se puede trazar la recta, por lo que encontrar los pares X₀ ; Y₀ y X₁ ; Y₁ tiene menos pasos.

Esto no significa que este método no sea efectivo ni tampoco que sea mucho mejor que los otros, ya que no se puede determinar con exactitud cuál es mejor si no se tiene en cuenta el comportamiento de la curva.

Para esta función se usa la fórmula:

• Y₀+{[(X-X₀)]/(X₁-X₀)]}*(Y₁-Y₀).

También se puede conocer la ecuación de la recta por medio de la siguiente función:

• EJE(conocido_y;conocido_x) para determinar la constante a.
• PENDIENTE(conocido_y;conocido_x) para establecer la pendiente, o sea la b.

Parabólica

La función parabólica o también conocida como cuadrática o polinomio de nivel 2, presenta un gráfico con un mínimo o máximo y cuenta con la fórmula de ecuación y=a+bx+cx2. La función es un polinomio que se utiliza para estimar valores es y= a + b(x-x₀) + c(x-x₀)(x-x₁).

Para trabajar en Excel se puede reemplazar los valores de la ecuación, o bien usar fórmulas como COINCIDIR e INDICE.

Polinomio Nivel 3

Se usa la función y = anxn + ... + a1x + ao ; con n=3. De esta manera se puede determinar que a es a0, a1, a2, a3 y x es  x0, x1, x2, x3.

Para usar el Excel y establecer el vector a se deberá crear esta fórmula:

• =ESTIMACION.LINEAL(y;x^(1;2;3;4))

Polinomio Nivel 4

Al igual que el polinomio de nivel 3 se pueden ejecutar la “cantidad n” de polinomios, por lo que el tratamiento se da igual desde el nivel 3 hasta el número n.

De esta manera podemos concluir que el método de grado 4 tiene los mismos pasos e idénticas características para resolver un valor dentro de un intervalo conocido que el de grado 3.

Pasos para interpolar datos o rangos en una hoja de cálculo de Microsoft Excel

A continuación, podrás encontrar los pasos que deberás realizar para interpolar datos dentro de un rango conocido por medio de Microsoft Excel. Te mostraremos una guía para una función lineal, una parabólica y para polinomios de nivel 3 y 4.

Vamos a verlos:

Lineal

Para usar una interpolación lineal en Excel deberás dividir la tarea en dos etapas.

Lo primero que tendrás que hacer son estos pasos:

• Dirígete a la pestaña “Fórmula” y luego busca en el menú “Biblioteca de funciones” la herramienta “Búsqueda y referencia”.
• Cuando pulses en la misma se abrirá una ventana, tendrás que elegir la función “COINCIDIR”. Ya que de esta manera encontrarás una posición relativa dentro de la matriz.
• Elige como valor buscado una celda en la cual quieres obtener el dato.
• A continuación, selecciona la matriz del eje X.
• En tipo de coincidencia tendrás que elegir el número 1, ya que si eliges 0 será ser exacto o si eliges número -1 tendrás un resultado de valor mayor.

El valor que obtendrás es igual a 1. Esto quiere decir que entre los valores de Y corresponden la estimación que busques.

Ahora deberás usar la fórmula INDICE para establecer los valores X₀;Y₀ y X₁;Y₁.

Para ello sigue estos pasos:

• Usa la fórmula INDICE(matriz; valor de Y cuando es el primer término).
• De esta manera podrás obtener los valores que buscas.
• Cuando ya tenga los cuatro valores tendrás que reemplazar en la fórmula Y₀+(((X-X₀))/(X₁-X₀)))*(Y₁-Y₀).
• Obtendrás el valor de Y. Eso significa que es el valor estimado cuando X está entre 2 valores.

Parabólica

Al buscar el tipo de ecuación y=a+bx+cx2 lo haremos por medio gráfico para encontrar el método parabólico. También lo podrás hacer tal cual lo sacamos con la función COINCIDIR e INDICE, pero queremos mostrarte un método más sencillo para casos simples.

Los pasos a seguir son los siguientes:

• Una vez que tengas ingresados los valores de X y de Y selecciona la matriz.
• Dirígete a “Insertar” y luego haz click en “Gráficos recomendados”.
• Busca la opción “Dispersión (X Y)” y pulsa en la misma.
• Elige el gráfico que más te convenga.
• Haz click derecho sobre la línea que quedó dibujada.
• Pulsa en la opción “Agregar línea de tendencia...”.
• Elige la función “Polinómica” y a continuación escribe “2” en el campo “Grado:”.
• Desplázate hacia abajo y activa las casillas “Señalar intersección”, “Presentar ecuación en el gráfico” y “Presentar el valor R cuadrado en el gráfico”. Esto hará que puedas obtener todos los datos que necesitas para encontrar los valores que quieras de Y.

Polinomio Nivel 3

Los pasos que deberás hacer son los mismos que te mostramos para la función parabólica. Una vez que tengas todos los datos cargados en una tabla de veras insertar un dibujo y luego establecer la línea de tendencia.

Para finalizar deberás activar las mismas casillas y así obtendrás la fórmula qué necesitas para obtener cualquier valor de Y.

Polinomio Nivel 4

Lo mismo que ocurre con el polinomio nivel 2, o parabólico, y nivel 3 tendrás que hacer los mismos pasos para una función de nivel 4.