Cómo interpolar valores intermedios para datos arbitrarios en Excel

Question

Tengo tablas de datos como este ejemplo, nueve entradas en A1:B9 en este caso:

A    B
--   ---
1    2.9
2    5.06
3    7
4    8.84
5    10.87
6    13.24
7    16.22
8    20.25
9    36.7

Lo anterior representa nueve mediciones de una variable física creciente no lineal en B, voltaje por ejemplo, y A representa exactamente cada uno de los nueve minutos redondos en que se realizó la medición.

Quiero crear una segunda tabla, columnas E y F, con una cantidad de filas que sea el "siguiente entero" para el valor más alto en la columna B. En este caso, B9=36,7, por lo que tendrá 37 filas. La columna F1:F37 contendrá números enteros del 1 al 37, la columna E debe tener valores numéricos que correspondan a F, en la misma relación que entre las columnas A a B. En otras palabras, interpola los valores de la columna E correspondientes a los valores de la columna F.

Por ejemplo, A3=3 y B3=7. En este caso, F7=7 y E7=3 porque B ya incluyó el número entero 7 y tiene un valor coincidente en la columna A. Sin embargo, F8=8, que es un valor intermedio no contenido en la columna B. Entonces E8 estará entre 3 y 4, basados ​​en los datos originales, y deben interpolarse.

La idea es que al trazar un gráfico, A1:B9 tendrá la misma forma que E1:F37. En este ejemplo, expandiré la tabla de datos a 37 resultados enteros que habrían ocurrido en el transcurso de las mediciones originales y veré a qué hora (en la columna E, con decimales) habrían ocurrido esos valores.

lo que he probado

Al intentar resolver esto yo mismo, pude encontrar una fórmula que llevó mucho tiempo (tenga en cuenta que en mi intento, mis columnas E y F están al revés de lo que describí anteriormente).

1. Creé una columna (K) que contiene la diferencia entre los elementos de la columna B. K5 = B5-B4. Ese es el desplazamiento Y por cada incremento de X.
2. La columna E contendrá tantos números enteros secuenciales (37), comenzando en 1, como el siguiente valor entero del elemento más grande en B. En este caso, B9 contiene 36,7, por lo que 37.
3. En F1: F37 ingreso la siguiente fórmula.

La celda F1 contiene:

=IF(E1>$B$9,$A$9+(E1-$B$9)/$K$9,IF(E1>$B$8,$A$8+(E1-$B$8)
    /$K$9,IF(E1>$B$7,$A$7+(‌​E1-$B$7)/$K$8,IF(E1>$B$6,$A$6+(E1-$B$6)
    /$K$7,IF(E1>$B$5,$A$5+(E1-$B$5)/$K$6,IF(E1‌​>$B$4,$A$4+
    (E1-$B$4)/$K$5,IF(E1>$B$3,$A$3+(E1-$B$3)/$K$4,IF(E1>$B$2,$A$2+
    (E1-$B$2‌​)/$K$3,IF(E1>$B$1,$A$1+(E1-$B$1)/$K$2,E1/$K$1)))))))))  

Funciona bastante bien. Pero no es una fórmula automatizada; se deben ingresar tantos "SI" como elementos en las columnas A+B (X+Y). Probé gráficos de dispersión con líneas de A1:B9 y E1:F37 (invertidas para una secuencia X/Y correcta) y generaron exactamente la misma forma de curva, por lo que funciona.

Pero no es una solución eficaz porque requiere un proceso manual, tedioso y personalizado para cada conjunto de datos. Estoy buscando una manera de lograr esto de una manera más automatizada con funciones integradas en Excel, o al menos un enfoque más genérico usando fórmulas.

Answer 1

Respuesta corta

La interpolación se basa en una ecuación que relaciona los valores de X e Y. Si conoce la ecuación real, puede calcular directamente los valores intermedios que desee. Si no lo hace, interpola usando una aproximación. La calidad de la aproximación determina qué tan precisos serán sus valores intermedios. La interpolación lineal será tosca si se aproxima a una curva con un número limitado de puntos. Existen otros enfoques que le brindarán mejores resultados y herramientas de análisis integradas que harán la mayor parte del trabajo.

Respuesta larga

Está buscando una "fórmula general" o una solución que automatice la interpolación de valores intermedios. Puede utilizar la interpolación lineal para prácticamente cualquier dato, pero los resultados serán burdos si hay un número limitado de puntos de datos y una curvatura significativa en la forma de los datos. No existe una solución única si se desea precisión. La mejor solución para un conjunto de datos determinado dependerá de las características de los datos.

La ecuacion

No importa cómo lo hagas, la interpolación se logra usando una ecuación que define la relación entre X e Y. La ecuación será la real o una estimación. Si se trata de una estimación, existen varios enfoques diferentes que dependen de la naturaleza de los datos y de lo que se necesita lograr.

En tu otra pregunta, usaste datos basados ​​en la ecuación Y=2^X. Cuando tengas la ecuación real, podrás interpolar exactamente. Elija un nuevo valor para Xo Yy la ecuación le dará el otro valor. Si no conoce la ecuación real, necesita encontrar una que se aproxime a ella. Usaré esta respuesta para centrarme en los enfoques de interpolación. Por lo general, utilizan herramientas de análisis integradas que realizan la mayor parte del trabajo. Si necesita más detalles sobre la mecánica del uso de una herramienta específica o un enfoque más automatizado, podemos ampliarlo en otra respuesta.

Intenta encontrar la ecuación real.

La mejor solución es ver si puedes determinar cuál es la ecuación real. Si conoce el proceso que generó los datos, eso puede indicarle la naturaleza de la ecuación. Muchos procesos, cuando se encuentran en condiciones controladas, de modo que se trata de una única variable impulsora y sin ruido aleatorio, siguen una curva simple cuyo tipo de ecuación se conoce. Entonces, un primer paso es observar la forma de los datos y ver si son similares a alguno de ellos.

Una forma sencilla de hacerlo es graficar los datos y agregar una línea de tendencia. Excel tiene una serie de curvas comunes disponibles para intentar ajustarlas.

Intentemos esto con los 2^Ndatos de su otra pregunta. Si no reconociera el patrón numérico y probara el enfoque de línea de tendencia, vería los íconos de curvas de diferentes formas. La curva exponencial tiene la misma forma general y eso te daría esto:

Excel utiliza een lugar de 2como base, que es sólo una traducción (e ^0,693 es 2). Visualmente, puede ver que la línea de tendencia sigue exactamente los datos. La R ² también te lo dice. R ² es una medida estadística de cuánta variación en los datos se tiene en cuenta con la ecuación. El valor 1significa que la ecuación representa el 100 % de la variación, o un ajuste perfecto.

El ejemplo de esta pregunta también tiene una especie de forma exponencial. Si intenta el mismo enfoque, obtendrá este resultado:

Entonces estos datos no son exponenciales. Podemos probar un polinomio, que describe algunos procesos naturales y es capaz de imitar una variedad de curvas (hablaré más sobre esto más adelante):

Como aproximación al proceso detrás de los datos, no encaja muy bien. En el tercer orden (una ecuación que contiene potencias de X hasta X^3), tiene más puntos de inflexión importantes que los datos y aún no coincide. Entonces, la ecuación subyacente no parece una curva simple y común, lo que significa que será necesario aproximar la ecuación.

Interpolación linear

Este es el enfoque que usted describe en sus comentarios. Es sencillo, utiliza una fórmula simple y bastante fácil de automatizar. Puede ser adecuado si tienes muchos puntos y las líneas rectas entre ellos están lo suficientemente cerca. En muchas curvas, los segmentos cortos de algunas áreas estarán cerca de líneas rectas. Sin embargo, es una mala aproximación para una línea curva y sus resultados serán inexactos en áreas con una curvatura significativa. En su ejemplo, el área entre los valores X de 7 y 8 tendría mucha curvatura. En esta área, una línea recta comparada con la curva real se vería así:

Está buscando una solución general que se aplique a cualquier dato. Es posible que la interpolación lineal sea demasiado burda para algunos datos.

Regresión

La gente ha sugerido la regresión como enfoque, aquí y en otras publicaciones. Se puede hacer usando líneas de tendencia o sus funciones de hoja de cálculo subyacentes, o las herramientas de análisis (creo que podrían estar en el Kit de herramientas de análisis, lo que podría requerir cargar esa opción en Excel, es posible que no esté cargada de forma predeterminada).

La regresión intenta ajustar una curva a sus datos con el objetivo de minimizar el error total entre los datos y la curva. En su uso normal, no es la herramienta adecuada para esta tarea (es el método utilizado para ajustar las líneas de tendencia, y vio cómo se compara con lo que necesita).

• Está pensado para situaciones en las que el objetivo es modelar el proceso detrás de los datos. Se supone que los datos son inexactos y la regresión sugiere lo que realmente se supone que es. Es posible que la curva encontrada por regresión no pase por ninguno de los puntos de datos reales. En su caso, los datos se proporcionan y se supone que son exactos. La curva debe pasar por cada punto.

• La regresión intenta ajustar una única ecuación a todos los datos. No será eficaz si el proceso que creó los datos no se describe mediante los tipos de ecuaciones disponibles para probar. Con muchos puntos de datos, la interpolación lineal de cada segmento puede ser una mejor aproximación que una curva de regresión para todos los datos.

Sin embargo, en lugar de emplearla de la manera habitual, se puede "abusar" de la regresión como una solución alternativa para lo que desea, y normalmente funcionará. Cuando se intenta modelar un proceso se suele valorar la fórmula más sencilla (la navaja de Occam). Por otro lado, con una ecuación lo suficientemente compleja, puedes encajar cualquier cosa. Siempre puedes dibujar un garabato que pase por cada punto. Con Npuntos, puedes encontrar una N-1ecuación polinómica de orden que pasará por todos los puntos (peor de los casos).

Digo "normalmente" porque, en algunos casos, es una línea bastante tortuosa que sería inútil para tu propósito. Y tenga en cuenta que este enfoque en realidad no "modela" nada en el sentido de que la ecuación resultante prediga el comportamiento fuera del rango de los datos.

A continuación se muestra un análisis de sus datos mediante regresión polinómica con ecuaciones de orden sucesivamente superior (la primera captura de pantalla incluye los órdenes 3 a 5):

(Haga clic en la imagen para verla en tamaño legible). Tenga en cuenta que la herramienta de análisis incluye el tipo de interpolación que desea realizar; generó los valores intermedios. Para cada análisis, los a(n)valores son los coeficientes de la ecuación que encontró. a(0)es una constante, a(1)es el coeficiente del término X^1, etc. Muestra el valor R ² del ajuste. Debe ser virtual 1para estar lo suficientemente cerca para su propósito.

He resaltado los valores de datos originales con las mayores diferencias. En este rango de órdenes, el ajuste mejora un poco con cada orden sucesiva, pero los puntos específicos que se describen con mayor precisión pueden cambiar. Aquí hay un cuadro de esos tres:

Cuando llegamos al polinomio de sexto y séptimo orden, se ve así:

Si optáramos por un polinomio de octavo orden para tus 9 valores, sería perfecto, pero el de séptimo orden probablemente esté lo suficientemente cerca. Para tener una perspectiva, observe que la ecuación de séptimo orden tiene un R ² de 0,99999 y aún no es perfecta.

Usar la herramienta de análisis de regresión para encontrar un ajuste adecuado (en este caso, la ecuación de séptimo u octavo orden) produciría los valores intermedios que desea. Pero es una buena idea trazar el resultado y observar la curva para asegurarse de que no sea un garabato.

Splines

Si traza sus datos y selecciona la opción para líneas suaves, lo que Excel usa para producir eso son splines. De hecho, casi todas las aplicaciones de gráficos por computadora (incluidas las definiciones de fuentes) se basan en splines para curvas suaves y transiciones de curvas. Lleva el nombre de la regla flexible que alguna vez usaron los dibujantes para conectar puntos arbitrarios con una curva.

Las splines crean la curva para cada sección, una sección a la vez, considerando los puntos adyacentes. La curva pasa por cada punto y no hay cambios abruptos en ninguno de los lados del punto, como los que se obtienen al conectar los puntos con líneas rectas.

Las ecuaciones utilizadas para los splines no intentan modelar el proceso que produjo los datos; es estrictamente para verse bonita. Sin embargo, la mayoría de los procesos siguen algún tipo de curva suave y continua. Cuando se trabaja con un solo segmento de curva, muchas ecuaciones diferentes que producen curvas de forma generalmente similar producirán valores muy similares dentro del segmento. Entonces, en la mayoría de los casos, los splines producirán una buena aproximación a lo que desea (y naturalmente pasa por cada punto, a diferencia de la regresión, que debe forzarse a través de cada punto).

Nuevamente digo "la mayoría de los casos". Los splines funcionan muy bien para datos que son bastante uniformes y regulares, y siguen las "reglas" de una curva. Puede hacer algunas cosas inesperadas con datos inusuales. Por ejemplo, unpregunta anterior de SUSe trataba de esta extraña "caída" negativa en el gráfico que Excel produjo con los datos:

Los splines son un poco como gelatina. Imagina un gran trozo de gelatina y limitas lugares específicos donde los deseas. El resto de la gelatina se hinchará en los lugares necesarios. Una ecuación puede definir ciertos tipos de curvas. Si fuerzas la curva a través de puntos específicos, sucede lo mismo. Con splines, el efecto se limita a un abultamiento extraño o un segmento de curva de aspecto poco natural; las ecuaciones de regresión de alto orden pueden seguir un camino salvaje.

Así es como los splines representan la curva de tus datos:

Si se compara esto con las curvas de regresión de orden superior, los splines "responden" más a las variaciones locales.

Este análisis lo hice usando LibreOffice Calc, que tiene un complemento de análisis que incluye splines. Como puede ver, esto también produce para splines, los resultados interpolados que está buscando. No tengo acceso directo al kit de herramientas de análisis de Excel, por lo que no sé si Excel incluye splines. De lo contrario, LO Calc se ejecutará en Windows y es gratis.

Línea de fondo

Esto cubre los enfoques que puede utilizar para interpolar los valores intermedios. Podría ser que diferentes enfoques funcionen mejor con diferentes datos. O bien, sus requisitos podrían ser aproximados, rápidos y sencillos. Decide qué tipo de interpolación necesitas. Si necesita más detalles sobre cómo lograrlo, podemos abordar la mecánica en otra respuesta.

Answer 2

Al leer sus comentarios y revisiones de la pregunta, hay un par de cosas que desea hacer que en realidad no están cubiertas en mi respuesta anterior. Esta respuesta abordará esos elementos y he incluido un recorrido paso a paso de cómo realizaría todo el proceso de interpolación.

Datos inexactos

Usted describe el proceso que generó los datos como tomar lecturas en un intervalo de tiempo y los números se redondean. La ecuación es tan buena como los datos. En su análisis real, debe utilizar los números más precisos disponibles (tal vez simplemente estaba manteniendo su ejemplo simple al mostrar tiempos redondeados).

Sin embargo, los datos que muestra no se ajustan exactamente al tipo de curva que normalmente se ve en un proceso físico. Las curvas teóricas son generalmente suaves cuando hay una sola variable de conducción y no hay ruido. Si está utilizando un equipo muy preciso para activar una lectura en un intervalo preestablecido y para proporcionar una medición precisa, puede aceptar los resultados como precisos. Sin embargo, si cronometra y toma la lectura manualmente, los Xvalores pueden ser imprecisos incluso si las lecturas en sí mismas son precisas. Cambiar los valores individuales Xun poco hacia un lado u otro introducirá los tipos de pequeñas irregularidades que ve en la curva de sus datos (a menos que el ejemplo sean solo números que usted inventó para fines de ejemplo).

Si este es el caso, podría resultarle beneficioso utilizar la regresión para estimar el mejor ajuste.

Usando Y como X

En su problema, desea definir valores para Y(valores enteros del 1 al 37 en este ejemplo) y encontrar los valores X asociados. Eso fue bastante fácil de hacer en tu Y=2^Xproblema porque esa ecuación simple se puede revertir fácilmente X=log(Y)/log(2)y puedes calcular directamente cualquier valor que desees. Si la ecuación no es algo simple, a menudo no existe una forma práctica de invertirla. El enfoque de regresión "abusado" en mi respuesta anterior le brinda una ecuación de alto orden, pero es "unidireccional" y a menudo no es práctico resolverla para la ecuación inversa.

El enfoque más sencillo es simplemente invertir Xy Ydesde el principio. Esto le brinda una ecuación que puede usar con los valores enteros que introduce (el análisis le brinda los coeficientes de la ecuación como se describe en la respuesta anterior).

Nunca está de más ver si una simple curva funciona. Aquí están los datos invertidos y puede ver que no hay un ajuste útil:

Entonces, intente un ajuste polinómico. Sin embargo, este es un caso como el que describí en la respuesta anterior. Los valores del 1 al 8 encajan bien, pero el 9 provoca indigestión. Un polinomio de tercer orden te da un empujón:

Se vuelve progresivamente más "interesante" a medida que aumenta el orden de la ecuación. En el séptimo orden, obtienes esto:

Pasa casi exactamente por todos los puntos, pero la curva entre 8 y 9 no es útil. Una solución sería conformarse con la interpolación lineal entre 8 y 9. Sin embargo, en este caso, podría obtener mejores valores incorporando splines para el extremo superior. La opción splines proporciona un buen ajuste y una curva que tiene más sentido entre 8 y 9:

Desafortunadamente, las ecuaciones splines son un poco complicadas y no se proporcionan. Sin embargo, podría hacer la interpolación lineal en los valores intermedios proporcionados por el análisis, lo que debería acercarlo mucho a números que se ajusten a una curva razonable.

Extrapolación versus interpolación

En este ejemplo, su primer Yvalor es 2,9. Quiere generar valores para 1y 2que estén fuera del rango de datos. Eso requiere extrapolación en lugar de interpolación, que es un requisito muy diferente.

• Si conoce la ecuación, como en su Y=2^Xejemplo, puede calcular cualquier valor que desee.

• Si se sabe que el proceso que genera los datos sigue una curva simple y usted está seguro del ajuste, puede proyectar valores fuera del rango de datos e incluso obtener un intervalo de confianza significativo para el rango en el que los valores podrían estar realmente (basado en cuánta variación hay entre los datos y la curva dentro del rango de los datos).

• Si está ajustando a la fuerza una ecuación de alto orden a los datos, las proyecciones fuera del rango de los datos generalmente no tienen sentido.

• Si utiliza splines, no hay base para proyectar fuera del rango de datos.

Cualquier proyección que haga fuera del rango de sus datos será tan buena como la ecuación que use, y si no está usando una ecuación exacta, cuanto más se aleje de sus datos, más inexactos serán.

Al observar la curva logarítmica en el primer gráfico, puede ver que proyectaría un valor muy diferente al esperado.

Para las ecuaciones polinómicas, el coeficiente de potencia cero es una constante, y ese es el valor que se produciría para un Xvalor de 0. Ésta es una forma sencilla de ver hacia dónde iría la curva en esa dirección.

Tenga en cuenta que en el cuarto o quinto orden, los puntos del 1 al 8 son bastante precisos. Pero una vez que se sale del rango, las ecuaciones pueden comportarse de manera muy diferente.

Extrapolación utilizando datos limitados

Una forma de mejorar las cosas es ajustar solo los puntos en ese extremo e incluir tantos puntos sucesivos como sigan la forma de la curva en ese extremo. El punto 9 obviamente está descartado. Hay varias inflexiones en la curva antes de eso, una de ellas alrededor del punto 5 o 6, por lo que los puntos más altos siguen una curva diferente. Usando solo los puntos del 1 al 5, te acercas a un ajuste perfecto con un polinomio de tercer orden. Esa ecuación proyectaría un punto cero de 0,12095 (compárese con la tabla anterior) y para un Xvalor de 1, 0.3493.

¿Qué sucede si simplemente ajustas una línea recta a los primeros cinco puntos?

Eso proyecta un punto cero de -0,5138 y para un Xde 1, -0.0071.

Ese rango de posibles resultados indica el nivel de incertidumbre fuera del rango de sus datos. No hay respuesta correcta. Y esto estaba en el extremo "bien educado" de su curva. El Yvalor de un Xof 9es 36.7. Quieres ir a 37. Las splines sugieren que la curva es asintótica en 9. Proyectar una línea recta en los datos sin procesar produciría un valor un poco mayor 9(lo mismo ocurre con un polinomio de cuarto orden). Un polinomio de tercer orden sugiere un valor menor que 9(al igual que los de quinto y sexto orden). Un polinomio de séptimo orden sugiere un valor sustancialmente superior a 9. Entonces, cualquier cosa que esté fuera del rango de datos es una suposición, o cualquier cosa que quieras que sea.

Poniendolo todo junto

Entonces, veamos cómo sería la solución real. Asumiremos que ya intentó encontrar una ecuación exacta y probó curvas comunes usando una línea de tendencia. El siguiente paso sería probar la regresión porque eso te da la fórmula para la curva y puedes ingresar tus valores enteros.

No tengo acceso inmediato a Excel 2013 ni al kit de herramientas de análisis. Usaré LibreOffice Calc para ilustrar esto. No es idéntico, pero es lo suficientemente parecido como para poder seguirlo en Excel. En LO Calc, esta es en realidad una extensión gratuita que debe cargarse. Estoy usandoCorelPolyGUI, que se puede descargaraquí. Lo que recuerdo del kit de herramientas de análisis es que no incluía splines. Si ese sigue siendo el caso y quieres hacer esto en Excel, me encontré coneste complemento gratuito(que no he probado). Una alternativa sería utilizar LO Calc, que se ejecutará en Windows y es gratuito.

Aquí, ingresé los valores X e Y (invertidos) en las columnas A y B, y abrí el cuadro de diálogo de análisis. Al resaltar los valores de X y hacer clic en el botón X, se cargan los rangos de datos y seleccioné el polinomio.

En la siguiente pestaña, especifico que quiero usar 0grados 7(un polinomio de séptimo orden con todos los órdenes).

Para especificar la salida, selecciono C1 y hago clic en Columnas, y registra las columnas necesarias para la salida. Selecciono que quiero que genere los datos originales, los resultados calculados, y seleccioné que agregue tres puntos intermedios entre cada punto de datos original. Y le digo que quiero un gráfico de los resultados en un cuadro nuevo. Luego vaya al menú calcular y haga clic en calcular.

Y ahí está. Si observa los valores calculados, puede notar un problema. Se hará evidente en el siguiente paso.

Aquí, agregué los valores 1directos 37. En este punto, solo queremos ocuparnos de la interpolación, por lo que agregué una fórmula para calcular solo los valores 3hasta 36. La fórmula simplemente expande los coeficientes enumerados en los resultados (los valores a(n)). La fórmula en I2 es:

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

Esto es solo cada coeficiente multiplicado por la potencia asociada del valor X. Arrastra esto hacia abajo y tendrás los resultados. Bueno, no del todo; tienes que mirarlo para ver si pasa la prueba de cordura. Sabíamos que había un problema entre 8y 9, pero resulta ser la mitad de los valores que desea. Podríamos usar los valores desde 3hasta 20, pero no tiene sentido combinar tantos valores de otro método. Así que usemos splines para todo.

Abra nuevamente el cuadro de diálogo de análisis y cambie el método a "splines" en la pestaña de entrada (no se muestra aquí). Dale un nuevo rango de salida y dile que calcule. Eso es todo lo que se necesita.

Tenemos nuevos resultados con los que trabajar. Dividir el rango de datos en tantos segmentos mantiene cada segmento corto, por lo que la interpolación lineal debería ser bastante buena (mucho mejor que usarla en los datos originales).

El proceso de ajuste de curvas o interpolación implica la creación de puntos de datos; utilizando su propio criterio sobre cómo debería verse (o no debería) la curva (la regresión supone que incluso los datos originales son imprecisos).

Darle a estos datos una prueba de cordura muestra que incluso las splines producen una curva de conexión con un abultamiento; un valor supera ligeramente 9, lo que probablemente sea un artefacto más que un reflejo del proceso que estaba midiendo. En este caso, es más probable una curva asintótica en 9, por lo que le asigné arbitrariamente al punto alto un valor que es un pelo menor que 9si lo observara a simple vista. La suposición no es que mi valor sea preciso, sólo que es una mejora. Para esta ilustración, creé una nueva columna con los valores que se utilizarán.

Agregué una columna con tus números 1hasta 37. De la discusión anterior, no tenemos una base confiable para proyectar valores para 1y 2, así que los dejé en blanco. Porque 37, seguí el supuesto asintótico y lo hice 9. Los valores de 3paso 36se encuentran mediante interpolación lineal (y es una fórmula que puedes adaptar a otros datos). La fórmula en el tercer trimestre es:

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

La función TENDENCIA simplemente interpola cuando el rango es de dos puntos. La sintaxis es:

TREND(Y_range, X_range, X_value)  

La función OFFSET se utiliza para cada rango. En cada caso, utiliza la función COINCIDIR para encontrar la primera fila del rango que contiene el valor objetivo. Los -1valores se deben a que se trata de compensaciones en lugar de ubicaciones; una coincidencia en la primera fila es un desplazamiento 0de la fila de referencia. Y tenga en cuenta que la Ycolumna está compensada por 2, en este caso, porque agregué una columna adicional para ajustar manualmente un valor. Los parámetros OFFSET seleccionan la columna que contiene los valores Y o X y seleccionan una altura de rango de 2, que le proporciona los valores por debajo y por encima del objetivo.

El resultado:

El asistente de análisis hace el trabajo pesado y, ya sea que esté usando regresión polinómica o splines, solo se requiere una fórmula para generar el resultado.