Generar tabla Excel VBA interpolando
Buenas tardes,
Veréis, actualmente dispongo de una tabla con 3 columnas y más de 600 filas.
La primera columna son instantes temporales que, por diversos motivos, no van a intervalos iguales, es decir, por ejemplo:
Tiempo / Valor1 / Valor2
1 / 23 / 12
4 / 87 / -34
5 / 12 / -6
9 / -98 / 0
... / 0 / 33
10000 / 14 / 69
Mi objetivo es generar una tabla de 3x10.000 a partir de esta tabla de 3x650 interpolando los valores de Valor1 y Valor2 para los puntos temporales en los que son desconocidos.
Llevo 1 par de días dándole vueltas y no consigo nada de nada...
Veréis, actualmente dispongo de una tabla con 3 columnas y más de 600 filas.
La primera columna son instantes temporales que, por diversos motivos, no van a intervalos iguales, es decir, por ejemplo:
Tiempo / Valor1 / Valor2
1 / 23 / 12
4 / 87 / -34
5 / 12 / -6
9 / -98 / 0
... / 0 / 33
10000 / 14 / 69
Mi objetivo es generar una tabla de 3x10.000 a partir de esta tabla de 3x650 interpolando los valores de Valor1 y Valor2 para los puntos temporales en los que son desconocidos.
Llevo 1 par de días dándole vueltas y no consigo nada de nada...
Respuesta de Isaac Reyes
1
¿Cómo exactamente obtendrías los datos de Valor1 y Valor2?
Pues son datos conocidos, en mi caso velocidad de vehículo y demanda energética asociada (realmente son más columnas, pero esas 3 son las únicas independientes entre sí).
Así pues serían: Tiempo/Velocidad/DemandaEnergética, conocidas para 650 instantes de tiempo entre los segundos 1 y 10000.
Y querría generar otra tabla en la que los instantes fueran del 1 al 10000 introduciendo los 650 conocidos y el resto interpolaciones entre los conocidos. Por ejemplo, si conozco los instantes 1, 3 y 9 (tabla de 3 filas) generar la correspondiente tabla de 9 filas en las que introduciría el instante conocido 1, el instante 2 como interpolación entre 1 y 3, el instante conocido 3, el instante 4 como interpolación entre 3 y 9, el instante 5 como interpolación entre 3 y 9, etc.
¿Más claro? :)
Gracias.
Así pues serían: Tiempo/Velocidad/DemandaEnergética, conocidas para 650 instantes de tiempo entre los segundos 1 y 10000.
Y querría generar otra tabla en la que los instantes fueran del 1 al 10000 introduciendo los 650 conocidos y el resto interpolaciones entre los conocidos. Por ejemplo, si conozco los instantes 1, 3 y 9 (tabla de 3 filas) generar la correspondiente tabla de 9 filas en las que introduciría el instante conocido 1, el instante 2 como interpolación entre 1 y 3, el instante conocido 3, el instante 4 como interpolación entre 3 y 9, el instante 5 como interpolación entre 3 y 9, etc.
¿Más claro? :)
Gracias.
Perdón, pero la palabra interpolación no me dice mucho ¿Cómo quedaría esta tabla con 9 filas?
Tiempo / Valor1 / Valor2
1 / 23 / 12
4 / 87 / -34
5 / 12 / -6
9 / -98 / 0
Tiempo / Valor1 / Valor2
1 / 23 / 12
4 / 87 / -34
5 / 12 / -6
9 / -98 / 0
No te preocupes :P
Interpolar es poner una estimación intermedia entre 2 valores conocidos. Por ejemplo, si yo sé que en el instante 3 el coche va a 30Km/h y que en el instante 6 el coche va a 60Km/h, pues suponer que en el instante 4 va a 40Km/h y en el instante 5 a 50Km/h.
La fórmula es lo de menos, sería algo así como:
i * (valorfinal-valorinicial)/numero de intervalos + valor inicial
donde i sería el intervalo que vamos a suponer dentro de ese rango e iría desde 1 hasta el número de intervalos-1.
En resumen, debería hacer 1 programa que:
F = índice de filas de la tabla conocida (del 1 al 650)
j = índice de la nueva tabla creada (del 1 al 10500)
For cada fila F desde el Origen hasta Fin de los datos:
1.- Leer la celda siguiente
2.- Calcular la diferencia temporal con el valor de la celda actual => n
3.- n = nº de tramos de 1 segundo entre ambas celdas
For i=1 to n-1
4.- Escribir el tiempo de la celda anterior + 1 en la celda fila j, columna tiempo
<span style="white-space: pre;"> </span> 5.- Escribir en las otras columnas el valor interpolado (si n=4, hacer que en i=1, el valor sea 1/4 de la resta de valores conocidos + el primero de ellos, en i=2 2/4 de esa cantidad, i=3 3/$, y en i=4 sería el valor conocido)
5.- Incrementar el valor de la fila, es decir j = j+1
<span style="white-space: pre;"> </span>Next i
Next fila F
El problema es que apenas acabo de empezar a manejar VBA y este tema me corre bastante prisa y no termino de atinar más que a hacer el pseudocódigo éste que te he puesto...
Muchas gracias.
Interpolar es poner una estimación intermedia entre 2 valores conocidos. Por ejemplo, si yo sé que en el instante 3 el coche va a 30Km/h y que en el instante 6 el coche va a 60Km/h, pues suponer que en el instante 4 va a 40Km/h y en el instante 5 a 50Km/h.
La fórmula es lo de menos, sería algo así como:
i * (valorfinal-valorinicial)/numero de intervalos + valor inicial
donde i sería el intervalo que vamos a suponer dentro de ese rango e iría desde 1 hasta el número de intervalos-1.
En resumen, debería hacer 1 programa que:
F = índice de filas de la tabla conocida (del 1 al 650)
j = índice de la nueva tabla creada (del 1 al 10500)
For cada fila F desde el Origen hasta Fin de los datos:
1.- Leer la celda siguiente
2.- Calcular la diferencia temporal con el valor de la celda actual => n
3.- n = nº de tramos de 1 segundo entre ambas celdas
For i=1 to n-1
4.- Escribir el tiempo de la celda anterior + 1 en la celda fila j, columna tiempo
<span style="white-space: pre;"> </span> 5.- Escribir en las otras columnas el valor interpolado (si n=4, hacer que en i=1, el valor sea 1/4 de la resta de valores conocidos + el primero de ellos, en i=2 2/4 de esa cantidad, i=3 3/$, y en i=4 sería el valor conocido)
5.- Incrementar el valor de la fila, es decir j = j+1
<span style="white-space: pre;"> </span>Next i
Next fila F
El problema es que apenas acabo de empezar a manejar VBA y este tema me corre bastante prisa y no termino de atinar más que a hacer el pseudocódigo éste que te he puesto...
Muchas gracias.
Euler:
Le puse hartas variables para que fuera más explicativo y además hace el procedimiento para un solo valor, pero si vas leyendo los comentarios, lo podrás manejar a tu antojo.
Espero que te sirva.
Saludos.
Public Sub Interpolar()
Dim dblFilaIni As Double 'Fila de inicio de la tabla de origen
Dim dblFilaFin As Double 'Ultima fila de la tabla origen
Dim dblFilaDestino As Double 'Fila actual de la tabla destino
Dim i As Double, j As Double 'Indices
Dim hOrigen As Worksheet, hDestino As Worksheet 'Hojas de origen y destino
Dim dblDif As Double 'Diferencia de tiempo entre la celda actual y la siguiente
Dim dblCont As Double 'Secuencial para cubrir los valores faltantes
Dim dblInc As Double 'Incremento para la interpolación
'Designar hojas de origen y destino
Set hOrigen = Hoja1
Set hDestino = Hoja2
'Fijar la primera y ultima fila de la tabla de origen
dblFilaIni = 2 'Suponiendo que comienza en la fila 2
dblFilaFin = hOrigen.Range("A" & hOrigen.Rows.Count).End(xlUp).Row 'Toma el índice de la última fila que tiene algún valor en la columna A
'Fijar la fila en que se comenzarán a poner los datos en la hoja destino
dblFilaDestino = 3
dblCont = 1 'Inicializar el contador
'Recorrer la tabla existente
For i = dblFilaIni To dblFilaFin - 1
'calcular el número de intervalos faltantes
dblDif = (hOrigen.Cells(i + 1, 1) - hOrigen.Cells(i, 1))
'CAlcular el incremento para la interpolación
dblInc = (hOrigen.Cells(i + 1, 2) - hOrigen.Cells(i, 2)) / dblDif
hDestino.Cells(dblFilaDestino, 4).Value = hOrigen.Cells(i, 2)
'Repetir la cantidad de veces que difiere el dato actual del siguiente - 1
For j = 0 To dblDif - 1
'Aquí asignas el valor de la secuencia...
'Almacenar dato en la columna 2 de la hoja destino
hDestino.Cells(dblFilaDestino, 2).Value = hOrigen.Cells(i, 2) + (j * dblInc)
'almacenar el correlativo
hDestino.Cells(dblFilaDestino, 1).Value = dblCont
'Incrementar
dblFilaDestino = dblFilaDestino + 1
dblCont = dblCont + 1
Next
Next
'Agregar los ultimos valores
hDestino.Cells(dblFilaDestino, 1).Value = dblCont
hDestino.Cells(dblFilaDestino, 2).Value = hOrigen.Cells(i, 2)
End Sub
Le puse hartas variables para que fuera más explicativo y además hace el procedimiento para un solo valor, pero si vas leyendo los comentarios, lo podrás manejar a tu antojo.
Espero que te sirva.
Saludos.
Public Sub Interpolar()
Dim dblFilaIni As Double 'Fila de inicio de la tabla de origen
Dim dblFilaFin As Double 'Ultima fila de la tabla origen
Dim dblFilaDestino As Double 'Fila actual de la tabla destino
Dim i As Double, j As Double 'Indices
Dim hOrigen As Worksheet, hDestino As Worksheet 'Hojas de origen y destino
Dim dblDif As Double 'Diferencia de tiempo entre la celda actual y la siguiente
Dim dblCont As Double 'Secuencial para cubrir los valores faltantes
Dim dblInc As Double 'Incremento para la interpolación
'Designar hojas de origen y destino
Set hOrigen = Hoja1
Set hDestino = Hoja2
'Fijar la primera y ultima fila de la tabla de origen
dblFilaIni = 2 'Suponiendo que comienza en la fila 2
dblFilaFin = hOrigen.Range("A" & hOrigen.Rows.Count).End(xlUp).Row 'Toma el índice de la última fila que tiene algún valor en la columna A
'Fijar la fila en que se comenzarán a poner los datos en la hoja destino
dblFilaDestino = 3
dblCont = 1 'Inicializar el contador
'Recorrer la tabla existente
For i = dblFilaIni To dblFilaFin - 1
'calcular el número de intervalos faltantes
dblDif = (hOrigen.Cells(i + 1, 1) - hOrigen.Cells(i, 1))
'CAlcular el incremento para la interpolación
dblInc = (hOrigen.Cells(i + 1, 2) - hOrigen.Cells(i, 2)) / dblDif
hDestino.Cells(dblFilaDestino, 4).Value = hOrigen.Cells(i, 2)
'Repetir la cantidad de veces que difiere el dato actual del siguiente - 1
For j = 0 To dblDif - 1
'Aquí asignas el valor de la secuencia...
'Almacenar dato en la columna 2 de la hoja destino
hDestino.Cells(dblFilaDestino, 2).Value = hOrigen.Cells(i, 2) + (j * dblInc)
'almacenar el correlativo
hDestino.Cells(dblFilaDestino, 1).Value = dblCont
'Incrementar
dblFilaDestino = dblFilaDestino + 1
dblCont = dblCont + 1
Next
Next
'Agregar los ultimos valores
hDestino.Cells(dblFilaDestino, 1).Value = dblCont
hDestino.Cells(dblFilaDestino, 2).Value = hOrigen.Cells(i, 2)
End Sub
