Interpolar en una tabla. Dos dimensiones

Ahora que ya sabemos localizar un valor mediante Coincidir e Indice y podemos interpolar linealmente en una dimensión, el siguiente paso es generalizar estos conceptos para realizar la interpolación en una tabla de dos dimensiones, esto es, para interpolar linealmente no entre dos puntos de una curva, sino entre cuatro puntos de una superficie, en este caso la interpolación lineal resultaría en un paraboloide hiperbólico entre las cuatro esquina conocidas.

gráfico de superficie con los puntos en Excel de una tabla de dos dimensiones

(por qué para este gráfico tampoco los ejes son proporcionales a los valores es un problema que Excel tendrá que solucionar en algún momento)

Lo primero que debemos localizar en nuestra tabla son los cuatro datos conocidos puntos entre los que se sitúa el punto que estamos buscando.

por ejemplo, si buscamos el punto (156, 4) en la siguiente tabla de datos, éste se encuentra entre el 3 y el 5, y entre el 100 y el 200.

Tabla con los datos en los que buscar el valor

Utilizando la función COINCIDIR y dado que los datos están ordenados de manera creciente, encontramos las posiciones en las que se sitúan los datos que nos interesan. Con la función INDICE y la posición, obtendremos el valor para cada posición.

posiciones y valores entre los que realizar la interpolación lineal con el parabolide hiperbólico

Ahora la interpolación la realizamos en dos pasos. En el primero, interpolamos en cada columna, esto es, en la primera columna, la de x=100, calculamos el valor que corresponde a y=4. Para la columna del x=200 interpolamos y calculamos el valor que corresponde a y=4

con esto obtendríamos los dos valores interpolados para y=4

 valores interpolados en cada columna para el valor de fila buscado

Por último, nos queda interpolar entre esos dos valores, para calcular el valor correspondiente a la x=156

Como de costumbre, adjunto el archivo con este ejempo.

Más:

Paso anterior: Interpolar en una dimensión

Fórmula para interpolar en dos dimensiones

¿sabes Excel, pero necesitas más?

Vista previaAdjuntoTamaño
InterpolarEnTabla2D.xls21 KB

Comentarios

Imagen de visitante

Codigo funcion VBA para interpolar en una tabla. Dos dimensiones

Attribute VB_Name = "DobleInterp"
'Option Explicit
Private Function DobleInterpolacion(x, y, matriz)
'Esta función realiza una interpolación lineal doble en una tabla para obtener z.
'conociendo x e y
'
' | y0 y y1
'---------------------------
'x0 | z00 z01
'x | z0 z z1
'x1 | z10 z11
'
'
'Primero interpola para cada una de las dos "y", que se dan, los valores
'z0 y z1, con los que se realiza una segunda interpolación que obtiene z
'z se devuelve como DobleInterpolacion

Dim OrdenX0 As Integer, OrdenX1 As Integer, OrdenY0 As Integer, OrdenY1 As Integer
' Dim x As Double, y As Double
'Busca la posición de X
If x < Application.WorksheetFunction.Min(Range(matriz(2, 1), matriz(matriz.Rows.Count, 1))) Or _
x > Application.WorksheetFunction.Max(Range(matriz(2, 1), matriz(matriz.Rows.Count, 1))) _
Then GoTo FueraRango
OrdenX0 = Application.WorksheetFunction.Match(x, Range(matriz(2, 1), matriz(matriz.Rows.Count, 1)), 1)
If OrdenX0 = matriz.Rows.Count - 1 Then OrdenX0 = OrdenX0 - 1
OrdenX1 = OrdenX0 + 1

'Busca la posición de Y
If y < Application.WorksheetFunction.Min(Range(matriz(1, 2), matriz(1, matriz.Columns.Count))) Or _
y > Application.WorksheetFunction.Max(Range(matriz(1, 2), matriz(1, matriz.Columns.Count))) _
Then GoTo FueraRango
OrdenY0 = Application.WorksheetFunction.Match(y, Range(matriz(1, 2), matriz(1, matriz.Columns.Count)), 1)
If OrdenY0 = matriz.Columns.Count - 1 Then OrdenY0 = OrdenY0 - 1
OrdenY1 = OrdenY0 + 1

Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double, z00 As Double, z10 As Double, z01 As Double, z11 As Double
'Obtiene los valores con los que realiza la doble interpolación
x0 = matriz(OrdenX0 + 1, 1)
x1 = matriz(OrdenX1 + 1, 1)
y0 = matriz(OrdenY0 + 1, 1)
y1 = matriz(OrdenY1 + 1, 1)
z00 = matriz(OrdenX0 + 1, OrdenY0 + 1)
z10 = matriz(OrdenX1 + 1, OrdenY0 + 1)
z01 = matriz(OrdenX0 + 1, OrdenY1 + 1)
z11 = matriz(OrdenX1 + 1, OrdenY1 + 1)

Dim z0 As Double, z1 As Double
'Interpola primero para el valor de x
z0 = z00 + (z10 - z00) / (x1 - x0) * (x - x0)
z1 = z01 + (z11 - z01) / (x1 - x0) * (x - x0)

'Interpola para el valor de y
If y1 = y0 Then
DobleInterpolacion = z0 'por si coincide y con alguno de los valores de la tabla
'para que no divida por cero
Else
DobleInterpolacion = z0 + (z1 - z0) / (y1 - y0) * (y - y0) 'Devuelve z
End If
Exit Function
FueraRango: 'haría falta extrapolar el valor de z
DobleInterpolacion = "#N/A"
End Function
Private Function DobleInterpolacionFila(C, F, matriz)
'Esta función realiza una interpolación lineal doble en una tabla para obtener E.
'conociendo E y F
'
' | C0 C C1
'---------------------------
'E0 | F00 F01
'E | F0 F F1
'E1 | F10 F11
'
'
'Primero interpola para cada una de las dos "C", que se dan, los valores
'F0 y F1, con los que se realiza una segunda interpolación que obtiene C
'C se devuelve como DobleInterpolacionFila
'
Dim OrdenC0, OrdenC1, OrdenE0, OrdenE1 As Integer
' Dim C As Double, F As Double
'Busca la posición de C
If C < Application.WorksheetFunction.Min(Range(matriz(1, 2), matriz(1, matriz.Columns.Count))) Or _
C > Application.WorksheetFunction.Max(Range(matriz(1, 2), matriz(1, matriz.Columns.Count))) _
Then GoTo FueraRangoDoble
OrdenC0 = Application.WorksheetFunction.Match(C, Range(matriz(1, 2), matriz(1, matriz.Rows.Count)), 1)
If OrdenC0 = matriz.Columns.Count - 1 Then OrdenC0 = OrdenC0 - 1
OrdenC1 = OrdenC0 + 1

Dim OrdenF00 As Integer, OrdenF01 As Integer, OrdenF10 As Integer, OrdenF11 As Integer
'Busca la posición de F00, F01, F10, y F11
If F < Application.WorksheetFunction.Min(Range(matriz(2, OrdenC0), matriz(matriz.Rows.Count, OrdenC1))) Or _
F > Application.WorksheetFunction.Max(Range(matriz(2, OrdenC0), matriz(matriz.Rows.Count, OrdenC1))) _
Then GoTo FueraRangoDoble
OrdenF00 = Application.WorksheetFunction.Match(F, Range(matriz(2, OrdenC0 + 1), matriz(matriz.Rows.Count, OrdenC0 + 1)), 1)
If OrdenF00 = matriz.Rows.Count - 1 Then OrdenF00 = OrdenF00 - 1
OrdenF10 = OrdenF00 + 1
OrdenF01 = Application.WorksheetFunction.Match(F, Range(matriz(2, OrdenC1 + 1), matriz(matriz.Rows.Count, OrdenC1 + 1)), 1)
If OrdenF01 = matriz.Rows.Count - 1 Then OrdenF01 = OrdenF01 - 1
OrdenF11 = OrdenF01 + 1

Dim C0 As Double, C1 As Double, E0 As Double, E1 As Double
Dim F00 As Double, F10 As Double, F01 As Double, F11 As Double
Dim E00 As Double, E10 As Double, E01 As Double, E11 As Double
'Obtiene los valores con los que realiza la doble interpolación
C0 = Application.WorksheetFunction.Index(Range(matriz(1, 2), matriz(1, matriz.Columns.Count)), OrdenC0)
C1 = Application.WorksheetFunction.Index(Range(matriz(1, 2), matriz(1, matriz.Columns.Count)), OrdenC1)
F00 = matriz(OrdenF00 + 1, OrdenC0 + 1).Value
F10 = matriz(OrdenF10 + 1, OrdenC0 + 1).Value
F01 = matriz(OrdenF01 + 1, OrdenC1 + 1).Value
F11 = matriz(OrdenF11 + 1, OrdenC1 + 1).Value
E00 = matriz(OrdenF00 + 1, 1).Value
E10 = matriz(OrdenF10 + 1, 1).Value
E01 = matriz(OrdenF01 + 1, 1).Value
E11 = matriz(OrdenF11 + 1, 1).Value
E0 = E00 + (E10 - E00) / (F10 - F00) * (F - F00)
E1 = E01 + (E11 - E01) / (F11 - F01) * (F - F01)
'Interpola para el valor de F
If E1 = E0 Then
DobleInterpolacionFila = E0
Else
DobleInterpolacionFila = E0 + (C - C0) * (E1 - E0) / (C1 - C0) 'Devuelve E
End If
Exit Function
FueraRangoDoble: 'haría falta extrapolar el valor de z
DobleInterpolacionFila = "#N/A"
End Function
Private Function DobleInterpolacionColumna(E, F, matriz)
'Esta función realiza una interpolación lineal doble en una tabla para obtener C.
'conociendo E y F
'
' | C0 C C1
'---------------------------
'E0 | F00 F01
'E | F0 F F1
'E1 | F10 F11
'
'
'Primero interpola para cada una de las dos "C", que se dan, los valores
'F0 y F1, con los que se realiza una segunda interpolación que obtiene C
'C se devuelve como DobleInterpolacionFila
'
Dim OrdenC0, OrdenC1, OrdenE0, OrdenE1 As Integer
'Busca la posición de E
If E < Application.WorksheetFunction.Min(Range(matriz(2, 1), matriz(matriz.Rows.Count, 1))) Or _
E > Application.WorksheetFunction.Max(Range(matriz(2, 1), matriz(matriz.Rows.Count, 1))) _
Then GoTo FueraRangoDoble
OrdenE0 = Application.WorksheetFunction.Match(E, Range(matriz(2, 1), matriz(matriz.Rows.Count, 1)), 1)
If OrdenE0 = matriz.Rows.Count - 1 Then OrdenE0 = OrdenE0 - 1
OrdenE1 = OrdenE0 + 1

Dim OrdenF00 As Integer, OrdenF01 As Integer, OrdenF10 As Integer, OrdenF11 As Integer
'Busca la posición de F00, F01, F10, y F11
If F < Application.WorksheetFunction.Min(Range(matriz(OrdenE0 + 1, 2), matriz(OrdenE1 + 1, matriz.Columns.Count))) Or _
F > Application.WorksheetFunction.Max(Range(matriz(OrdenE0 + 1, 2), matriz(OrdenE1 + 1, matriz.Columns.Count))) _
Then GoTo FueraRangoDoble
OrdenF00 = Application.WorksheetFunction.Match(F, _
Range(matriz(OrdenE0 + 1, 2), matriz(OrdenE0 + 1, matriz.Columns.Count)), 1)
If OrdenF00 = matriz.Columns.Count - 1 Then OrdenF00 = OrdenF00 - 1
OrdenF01 = OrdenF00 + 1
OrdenF10 = Application.WorksheetFunction.Match(F, _
Range(matriz(OrdenE1 + 1, 2), matriz(OrdenE1 + 1, matriz.Columns.Count)), 1)
If OrdenF10 = matriz.Columns.Count - 1 Then OrdenF10 = OrdenF10 - 1
OrdenF11 = OrdenF10 + 1

Dim C0 As Double, C1 As Double, E0 As Double, E1 As Double
Dim F00 As Double, F10 As Double, F01 As Double, F11 As Double
Dim C00 As Double, C10 As Double, C01 As Double, C11 As Double
'Obtiene los valores con los que realiza la doble interpolación
E0 = Application.WorksheetFunction.Index(Range(matriz(2, 1), matriz(matriz.Rows.Count, 1)), OrdenE0)
E1 = Application.WorksheetFunction.Index(Range(matriz(2, 1), matriz(matriz.Rows.Count, 1)), OrdenE1)
F00 = matriz(OrdenE0 + 1, OrdenF00 + 1).Value
F10 = matriz(OrdenE0 + 1, OrdenF10 + 1).Value
F01 = matriz(OrdenE1 + 1, OrdenF01 + 1).Value
F11 = matriz(OrdenE1 + 1, OrdenF11 + 1).Value
C00 = matriz(1, OrdenF00 + 1).Value
C10 = matriz(1, OrdenF10 + 1).Value
C01 = matriz(1, OrdenF01 + 1).Value
C11 = matriz(1, OrdenF11 + 1).Value
C0 = C00 + (C01 - C00) / (F01 - F00) * (F - F00)
C1 = C10 + (C11 - C10) / (F11 - F10) * (F - F10)
'Interpola para el valor de F
If C1 = C0 Then
DobleInterpolacionColumna = C0
Else
DobleInterpolacionColumna = C0 + (E - E0) * (C1 - C0) / (E1 - E0) 'Devuelve C
End If
Exit Function
FueraRangoDoble: 'haría falta extrapolar el valor de C
DobleInterpolacionColumna = "#N/A"
End Function

Imagen de visitante

Re: Codigo funcion VBA para interpolar en una tabla. Dos ...

como haga para que funcion vba en excel, ya lo coloque en un módulo pero no aparece ninguna función en la lista de funciones y tampoco lo he podido anexar como una macros, ayuda por favor

Imagen de pacomegia

Re: Codigo funcion VBA para interpolar en una tabla. Dos ...

La función está declarada como privada, por eso no aparece.

Si la quieres utilizar en la hoja, quita la palabra PRIVATE  de la primera línea de la función

envios gratis a todo el mundo
Imagen de visitante

Re: Interpolar en una tabla. Dos dimensiones

Eres un campeon, muchas gracias!!! Sabes como siempre, lo que me ayudas es a darle mas utilidad, a interrelacionarlo con mas comandos. Gracias!!!

Imagen de visitante

Re: Interpolar en una tabla. Dos dimensiones

Utilísimo, muchísimas gracias

Imagen de visitante

Re: Interpolar en una tabla. Dos dimensiones

Muchas gracias me ha sido de gran utilidad.

Imagen de visitante

Re: Interpolar en una tabla. Dos dimensiones

como hago para que funcione el vba para la doble interpolacion