Cómo interpolar en Excel: 4 formas sencillas

La interpolación es el proceso de estimar puntos de datos dentro de un conjunto de datos existente. Como este es un blog de Excel, claramente la pregunta que queremos responder es: ¿podemos interpolar con Excel? Ésta es una pregunta común. De hecho, fue la siguiente pregunta de un lector la que me hizo profundizar en este tema por primera vez:

“Tengo una pregunta sobre Excel: ¿hay alguna forma de interpolar un valor de una tabla? Tengo X e Y que no están en la tabla, pero tienen datos correlacionados, por lo que quiero calcular el valor interpolado”.

Como ejemplo simple, si nos tomó 15 minutos caminar 1 milla el lunes y 1 hora caminar 4 millas el martes, podríamos estimar razonablemente que tomaría 30 minutos caminar 2 millas.

Esto no debe confundirse con la extrapolación, que estima valores fuera del conjunto de datos. Estimar que se necesitarían 2 horas para caminar 8 millas sería una extrapolación ya que la estimación está fuera de los valores conocidos.

Excel es una excelente herramienta para la interpolación, ya que, en última instancia, es una gran calculadora visual.

Tabla de contenido

• Opciones de interpolación con Excel
• Interpolación usando matemáticas simples
• Interpolación utilizando la función PRONÓSTICO
• Interpolación cuando es perfectamente lineal
• Interpolación cuando es aproximadamente lineal
• Interpolación cuando los datos no son lineales.
• Interpolar datos exponenciales
• Interpolación lineal interna
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0020 Interpolar con Excel.xlsx

Tener acceso

Opciones de interpolación con Excel

En términos de responder a la pregunta, existen varios escenarios que conducirían a diferentes soluciones.

En primer lugar, podríamos utilizar matemáticas simples. Esto funcionaría si los resultados fueran perfectamente lineales (es decir, los valores X e Y se mueven directamente sincronizados entre sí). Pero si no es así, podríamos tener un resultado ligeramente sesgado.

Alternativamente, podríamos usar la función PRONÓSTICO de Excel (o PRONÓSTICO.LINEAR en Excel 2016 y posteriores). Según su nombre, la función PRONÓSTICO parece una elección extraña. Parecería ser una función específica para la extrapolación; sin embargo, también es una de las mejores opciones para la interpolación lineal en Excel. PRONÓSTICO utiliza todos los valores del conjunto de datos para estimar el resultado; por lo tanto, es excelente para relaciones lineales, incluso si no están perfectamente correlacionadas.

Luego, otra idea: ¿qué pasa si la relación X e Y no es lineal en absoluto? ¿Cómo podríamos interpolar un valor cuando los datos son exponenciales?

Echemos un vistazo a todos estos escenarios.

Interpolación usando matemáticas simples

Las matemáticas simples funcionan bien cuando hay sólo dos pares de números o cuando la relación entre XY es perfectamente lineal.

A continuación se muestra un ejemplo básico (consulte la pestaña Ejemplo 1 en el archivo de descarga de soporte):

La fórmula en la celda E4 es:

=B2+(E2-A2)*(B3-B2)/(A3-A2)

Esto puede parecer un poco complicado para algunos, así que aquí hay una descripción general rápida de la fórmula.

=B2+(E2-A2)* (B3-B2)/(A3-A2)

La última sección (resaltada en verde arriba) calcula cuánto se mueve el valor de Y cada vez que el valor de X se mueve en 1. En nuestro ejemplo, Y se mueve en 1,67 por cada 1 de X.

=B2+ (E2-A2)* (B3-B2)/(A3-A2)

La segunda sección (en verde arriba) calcula qué tan lejos está la X interpolada de la primera X, luego la multiplica por el valor calculado anteriormente. Según nuestro ejemplo, el resultado se calcula como 17,5 (celda E2) menos 10 (celda A2), cuyo resultado luego se multiplica por 1,67. Todo esto equivale a 12,5.

= B2+ (E2-A2)*(B3-B2)/(A3-A2)

Finalmente, llegamos a la primera sección de la fórmula (en verde arriba), que suma el primer valor de Y. En nuestro ejemplo, esto proporciona el resultado final de 77,5 (65 + 12,5). Para cualquiera que recuerde las matemáticas de la escuela secundaria, la fórmula es la siguiente:

Aquí está el resultado superpuesto en un gráfico.

Incluso si no recuerdas la interpolación lineal de la escuela, la buena noticia es que Excel nos ha brindado una opción más sencilla, la función PRONÓSTICO.

Interpolación utilizando la función PRONÓSTICO

En la versión 2016 de Excel, se agregaron muchas funciones estadísticas nuevas. Para dejar espacio a estas nuevas funciones, FORECAST ha sido reemplazada por la función FORECAST.LINEAR. Aunque PRONÓSTICO todavía se mantiene en este momento con fines de compatibilidad con Excel 2013 y versiones anteriores.

Como FORECAST y FORECAST.LINEAR son efectivamente lo mismo, usaremos los términos indistintamente.

Interpolación cuando es perfectamente lineal

Ahora usemos PRONÓSTICO para interpolar un resultado.

Usando los mismos números del ejemplo anterior, la fórmula en la celda E6 es:

=PRONÓSTICO(E2,B2:B3,A2:A3)

La función PRONÓSTICO tiene la siguiente sintaxis:

=PRONÓSTICO(x,conocido_y,conocido_x)

Los tres argumentos de la función son:

• x : el punto de datos para el cual queremos predecir un valor
• known_y's : el rango de celdas o matriz de valores que contienen los valores Y conocidos
• known_x's : el rango de celdas o matriz de valores que contienen los valores X conocidos

Cuando se utiliza la función PRONÓSTICO, el resultado de la celda E6 también es 77,5 (al igual que en el enfoque matemático).

Para completar, el archivo de ejemplo también contiene el uso de la función FORECAST.LINEAR. Como era de esperar, el resultado es idéntico a la función PRONÓSTICO heredada.

Interpolación cuando es aproximadamente lineal

Pero… ¿y si nuestros datos no son perfectamente lineales? Mire el cuadro a continuación, los datos claramente tienen una relación lineal, pero no es perfecta. Mire la pestaña Ejemplo 2 en el archivo de respaldo.

En estas circunstancias, la función PRONÓSTICO es aún más útil, ya que no se limita a interpolar entre el primer y el último valor. Aquí están los datos utilizados en el gráfico.

La función PRONÓSTICO en la celda E4 interpola el valor Y basándose en el valor X de 17,5.

=PRONÓSTICO(E2,B2:B11,A2:A11)

En este escenario, PRONÓSTICO estima un valor basado en todos los puntos de datos disponibles, no solo el inicio y el final. El resultado de la función PRONÓSTICO en la celda E4 es 77,3 (redondeado a 1 decimal), que en la mayoría de las circunstancias sería más preciso que la simple interpolación lineal aplicada en el enfoque matemático.

Recuerde, la interpolación se utiliza para estimar valores. 77.3 puede no ser el resultado exacto, pero es una estimación razonable basada en la información que tenemos.

Una vez más, FORECAST.LINEAR calcula el mismo resultado.

Resumen de la función PRONÓSTICO

La siguiente imagen contiene un resumen de la función PRONÓSTICO.

Obtenga más información sobre las funciones FORECAST y FORECAST.LINEAR en este artículo: Función FORECAST y FORECAST.LINEAR (support.office.com)

Interpolación cuando los datos no son lineales.

Pero aquí hay una pregunta más complicada: ¿qué pasa si los datos no son lineales en absoluto? ¿Y que?

Mire la pestaña Ejemplo 3 en el archivo de respaldo. Aquí está nuestro nuevo escenario gráfico:

Si adoptáramos un enfoque lineal simple, obtendríamos un valor de 77,5, que, como puede ver a continuación, está bastante lejos de la curva. Utilizar la función PRONÓSTICO proporcionaría un resultado de 70,8, que es mejor, pero también muy alejado de la curva.

Hay dos opciones más para obtener una mejor estimación (1) interpolar datos exponenciales usando la función CRECIMIENTO (2) calcular una interpolación lineal interna

Interpolar datos exponenciales

La función CRECIMIENTO es similar a PRONÓSTICO pero se puede aplicar a datos con crecimiento exponencial.

El resultado de la función CRECIMIENTO en la celda E10 es 70,4. Una vez más, esto está más cerca de la línea, pero todavía un poco lejos.

La fórmula en la celda E10 es:

=CRECIMIENTO(B2:B11,A2:A11,E2)

La función CRECIMIENTO tiene la siguiente sintaxis:

=CRECIMIENTO(conocidos_y,[conocidos_x],[nuevos_x],[const])

Los cuatro argumentos en la función CRECIMIENTO son (solo tenga en cuenta que los argumentos no están en el mismo orden que la función PRONÓSTICO).

• known_y's : el rango de celdas o matriz de valores que contienen los valores Y conocidos
• [known_x's] – el rango de celdas o matriz de valores que contienen los valores X conocidos
• [new_x's] – el punto de datos para el cual queremos predecir un valor
• [Const] : una medida de verdadero/falso para indicar cómo debe calcularse la fórmula. Para nuestro escenario, podemos omitir este último argumento.

Los corchetes sirven para indicar qué argumentos son opcionales para que la función calcule un resultado. Necesitamos los valores conocidos_y, conocidos_x y nuevos_x, pero hemos ignorado el argumento constante.

Si bien el resultado de 70,4 es una aproximación más cercana, no debemos utilizar la función CRECIMIENTO con fe ciega. Pruebe sus interpolaciones para comprobar si son razonables.

Resumen de la función CRECIMIENTO

La siguiente imagen contiene un resumen de la función CRECIMIENTO.

Obtenga más información sobre la función CRECIMIENTO en este artículo: Función CRECIMIENTO (support.office.com)

Interpolación lineal interna

Una opción razonable puede ser encontrar el resultado por encima y por debajo del nuevo valor de X y luego aplicar una interpolación lineal entre esos dos puntos. Esto estaría bastante cerca.

En nuestro ejemplo, los valores a ambos lados de una X de 17,5 son:

• X:16 y 18
• Y: 66,3 y 68

Usando estos valores, ahora podemos hacer una interpolación lineal estándar.

Método rápido y de un solo uso

Si se tratara de una acción única, podríamos hacerlo rápidamente incluyendo solo las celdas esenciales en la fórmula.

=PRONÓSTICO(E2,B6:B7,A6:A7)

Sin embargo, tan pronto como cambiemos el valor interpolado, PRONÓSTICO puede calcular un resultado inexacto. Entonces, pasemos a ver un método flexible.

Acercamiento flexible

Para crear un enfoque flexible, utilizaremos las funciones ÍNDICE, COINCIDENCIA y PRONÓSTICO combinadas. Esto puede parecer complicado, pero no se preocupe, lo explicaremos lentamente. En última instancia, intentamos lograr el mismo resultado que el método de un solo uso anterior, pero ajustando automáticamente los rangos según el valor que se interpola.

NOTA: Para que este método funcione, es necesario que el rango de X conocidas se enumere en orden ascendente.

Función COINCIDIR

En primer lugar, utilizamos la función COINCIDIR para recuperar la posición del valor inferior a 17,5.

=COINCIDIR(E2,A2:A11,1)

Esta fórmula dice encontrar el valor en la celda E2 del rango de celdas A2-A11. El 1 al final de la fórmula le dice a la función COINCIDIR que deseamos utilizar una coincidencia aproximada (es decir, el valor más cercano debajo del valor de búsqueda). 16 es el valor más cercano a 17,5. Como 16 es el quinto elemento en las celdas A2-A11, COINCIDIR devuelve un valor de 5.

Función ÍNDICE

Habiendo identificado en la etapa anterior que la quinta posición contiene el valor siguiente, podemos usar la función ÍNDICE para determinar la referencia de celda para este valor.

ÍNDICE (A2: A11, COINCIDIR (E2, A2: A11,1))

Esto devolvería una referencia a la celda A6.

Para encontrar el valor anterior, podemos usar la misma función, pero sumando 1 a la función COINCIDIR.

ÍNDICE (A2: A11, COINCIDIR (E2, A2: A11,1) +1 )

La fórmula anterior devolvería una referencia a la celda A7.

Gama dinámica

Ahora las cosas empiezan a ponerse interesantes. Podemos combinar estas funciones con dos puntos (:) en el medio para crear un rango para los dos valores de X.

ÍNDICE (A2: A11, COINCIDIR (E2, A2: A11,1)) : ÍNDICE (A2: A11, COINCIDIR (E2, A2: A11,1) +1)

La primera función ÍNDICE devuelve la referencia a la celda A6 (el resultado de la sección resaltada en verde). La segunda función ÍNDICE devuelve la referencia a la celda A7 (el resultado de la sección resaltada en violeta). Estos están separados por dos puntos ( : ) (resaltados en rojo), para crear un rango – A6:A7

Podemos hacer lo mismo para crear un rango para los dos valores de Y. La única diferencia es que las funciones ÍNDICE observarán las celdas B2-B11.

ÍNDICE (B2: B11, COINCIDENCIA (E2, A2: A11,1)) : ÍNDICE (B2: B11, COINCIDENCIA (E2, A2: A11,1) +1)

Al utilizar las celdas B2-B11 en la función ÍNDICE, calculará un rango B6:B7.

PRONÓSTICO DEL PARTIDO ÍNDICE

Ahora tenemos nuestras dos gamas; los valores X A6:A7 y los valores Y B6:B7. Combinemos todo esto dentro de la función PRONÓSTICO.

=PRONÓSTICO (E2, ÍNDICE (B2: B11, PARTIDO (E2, A2: A11,1)): ÍNDICE (B2: B11, PARTIDO (E2, A2: A11,1) +1), ÍNDICE (A2: A11, PARTIDO (E2,A2:A11,1)):ÍNDICE(A2:A11,COINCIDIR(E2,A2:A11,1)+1))

Esa es una fórmula bastante grande, ¿verdad? Pero espero haber logrado explicarlo para que no dé demasiado miedo.

El resultado de la interpolación lineal interna utilizando la función PRONÓSTICO es 67,6 (con 1 decimal, como se muestra en la celda E14). Eche un vistazo al gráfico nuevamente; Verá que 67,6 es una estimación razonable basada en los datos disponibles.

ADVERTENCIA: En última instancia, esto sigue siendo un cálculo de interpolación lineal basado en los dos valores a cada lado del valor X. La distancia entre los valores superiores e inferiores tendrá un impacto directo en la precisión de la interpolación.

Conclusión

Inicialmente, lo que parecía una pregunta sencilla nos ha llevado a muchas soluciones potenciales para tres escenarios diferentes. La clave es que necesita conocer sus datos para seleccionar el método que proporcione los resultados más precisos.

En el proceso, hemos cubierto las funciones PRONÓSTICO y PRONÓSTICO.LINEAR y hemos visto que son útiles tanto para la interpolación como para la extrapolación.

Además, en esta publicación, hemos utilizado INDEX y MATCH para crear rangos dinámicos, que es una técnica muy poderosa para fórmulas avanzadas en Excel.

Si busca más información sobre técnicas de pronóstico en Excel, consulte Ingeniero Excel ( https://engineerexcel.com/blog ). Si bien es posible que no esté trabajando en un contexto de ingeniería, las técnicas son aplicables en muchas otras circunstancias.

---