linearinterp - Interpolando puntos de datos en Excel

linearinterp excel

Question

Estoy seguro de que este es el tipo de problema que otros han resuelto muchas veces antes.

Un grupo de personas va a hacer mediciones (el uso de energía en el hogar para ser exactos). Todos ellos lo harán en diferentes momentos y en diferentes intervalos.

Entonces, lo que obtendré de cada persona es un conjunto de pares {fecha, valor} donde faltan fechas en el conjunto.

Lo que necesito es un conjunto completo de {fecha, valor} pares donde, para cada fecha dentro del rango, se conozca un valor (medido o calculado). Espero que una simple interpolación lineal sea suficiente para este proyecto.

Si asumo que debe hacerse en Excel. ¿Cuál es la mejor manera de interpolar en un conjunto de datos de este tipo (tengo un valor para cada día)?

Gracias.

NOTA: Cuando estos conjuntos de datos estén completos, determinaré la pendiente (es decir, el uso por día) y, a partir de eso, podremos comenzar a hacer comparaciones de casa a casa.

INFORMACIÓN ADICIONAL Después de las primeras sugerencias: No quiero averiguar manualmente dónde están los orificios en mi conjunto de mediciones (¡¡demasiados conjuntos de mediciones incompletos !!). Estoy buscando algo (existente) automático para hacer eso por mí. Así que si mi entrada es

{2009-06-01,  10}
{2009-06-03,  20}
{2009-06-06, 110}

Entonces espero obtener automáticamente

{2009-06-01,  10}
{2009-06-02,  15}
{2009-06-03,  20}
{2009-06-04,  50}
{2009-06-05,  80}
{2009-06-06, 110}

Sí, puedo escribir software que haga esto. Solo espero que alguien ya tenga una característica de software "listo para ejecutar" (Excel) para este problema (más bien genérico).

Answer 1

Hay dos funciones, LINEST y TREND , que puedes intentar ver que te dan los mejores resultados. Ambos toman conjuntos de Xs e Ys conocidos junto con un nuevo valor de X, y calculan un nuevo valor de Y. La diferencia es que LINEST realiza una regresión lineal simple, mientras que TREND intentará primero encontrar una curva que se ajuste a sus datos antes de hacer la regresión.

Answer 2

La forma más fácil de hacerlo es probablemente la siguiente:

1. Descargue el complemento de Excel aquí: Funciones adicionales de XlXtrFun ™ para Microsoft Excel

2. Utilice la función intepolate (). = Interpolar ($ A $ 1: $ A $ 3, $ B $ 1: $ B $ 3, D1, FALSO, FALSO)

Las columnas A y B deben contener su entrada, y la columna G debe contener todos sus valores de fecha. La fórmula entra en la columna E.

Answer 3

La respuesta anterior por YGA no controla los casos de final de rango donde el valor X deseado es el mismo que el valor X del rango de referencia. ¡Usando el ejemplo dado por YGA, la fórmula de excel devolvería # DIV / 0! error si se solicitó un valor interpolado en 9999. Esto es obviamente parte de la razón por la cual YGA agregó los puntos extremos de 9999 y -9999 al rango de datos de entrada, y luego asume que todos los valores pronosticados están entre estos dos números. Si dicho relleno no es deseado o no es posible, ¡otra forma de evitar un # DIV / 0! el error es verificar una coincidencia de valor de entrada exacta mediante la siguiente fórmula:

=IF(ISNA(MATCH(F3,inputs,0)),FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1)),OFFSET(inputs,MATCH(F3,inputs)-1,1,1,1))

donde F3 es el valor donde se desean resultados interpolados.

Nota: Habría agregado esto como un comentario a la publicación original de YGA, pero todavía no tengo suficientes puntos de reputación.

Answer 4

Me di cuenta de esto y me resistí a usar un complemento porque hace que sea difícil compartir la hoja con las personas que no tienen el complemento instalado.

Mi oficial diseñó una fórmula limpia que es relativamente compacta (al costar el uso de un poco de magia).

Cosas a tener en cuenta:

• La fórmula funciona por:

  • usar la función MATCH para encontrar la fila en el rango de inputs justo antes del valor que se está buscando (por ejemplo, 3 es el valor justo antes de 3.5)
  • usando OFFSET s para seleccionar el cuadrado de esa línea y la siguiente (en púrpura claro)
  • usando FORECAST para construir una interpolación lineal usando solo esos dos puntos, y obteniendo el resultado

• Esta fórmula no puede hacer extrapolaciones; asegúrese de que su valor de búsqueda se encuentre entre los puntos finales (hago esto en el siguiente ejemplo teniendo valores extremos).

No estoy seguro si esto es demasiado complicado para la gente; pero tenía la ventaja de ser muy portátil (y más simple que muchas soluciones alternativas).

Si desea copiar y pegar la fórmula, es:

=FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1

(las inputs son un rango con nombre)

Answer 5

Puede averiguar qué fórmula se ajusta mejor a sus datos, utilizando la función de "línea de tendencia" de Excel. Usando esa fórmula, puedes calcular y para cualquier x

1. Cree dispersión lineal (XY) para ella (Insertar => Dispersión);
2. Cree una línea de tendencia polinominal o de media móvil, marque "Mostrar ecuación en el gráfico" (haga clic con el botón derecho en serie => Agregar línea de tendencia);
3. Copia la ecuación en la celda y reemplaza las x con el valor x deseado

En la captura de pantalla a continuación, A12: A16 contiene x ''s, B12: B16 tiene y '' s, y C12 contiene la fórmula que calcula y para cualquier x .

Primero publiqué una respuesta here , pero luego encontré esta pregunta

Answer 6

Una buena forma gráfica de ver qué tan bien se ajustan sus resultados interpolados:

Tome su fecha, pares de valores y grafícelos usando el gráfico XY en Excel (no el gráfico de líneas). Haga clic derecho en la línea resultante en el gráfico y haga clic en ''Agregar línea de tendencia''. Hay muchas opciones diferentes para elegir qué tipo de ajuste de curva se utiliza. Luego puede ir a las propiedades de la línea de tendencia recién creada y mostrar la ecuación y el valor de R cuadrado.

Asegúrese de que al dar formato a la etiqueta de ecuación de línea de tendencia, configure el formato numérico para que tenga un alto grado de precisión, de modo que se muestren todos los dígitos significativos de las constantes de ecuación.

Answer 7

alternativamente.

=INDEX(yVals,MATCH(J7,xVals,1))+(J7-MATCH(J7,xVals,1))*(INDEX(yVals,MATCH(J7,xVals,1)+1)-INDEX(yVals,MATCH(J7,xVals,1)))/(INDEX(xVals,MATCH(J7,xVals,1)+1)-MATCH(J7,xVals,1))

donde j7 es el valor de x.

xvals es el rango de los valores de x yvals es el rango de los valores de y

Más fácil de poner esto en el código.