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.
(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.
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.
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
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
| Adjunto | Tamaño |
|---|---|
 InterpolarEnTabla2D.xls | 21 KB |
Comentarios
Re: Interpolar en una tabla. Dos dimensiones
Muchas gracias me ha sido de gran utilidad.
Re: Interpolar en una tabla. Dos dimensiones
como hago para que funcione el vba para la doble interpolacion
Interpolación de tabla
Si yo quisiera interpolar los valores de densidad (valores desde 1012.4 hasta 1469) y la temperatura (fila superior) y como resultado me diera la concentración (columna de la izquierda), que fórmulas utilizaría.
Re: Interpolar en una tabla. Dos dimensiones
Utilísimo, muchísimas gracias
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!!!
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
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
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
------
Ya sé Excel, pero necesito más.
Gracias!!!
Muchísimas gracias! Me ha servido bastante. Un saludo
Muchas gracias, excelente
Muchas gracias, excelente aporte
Agradecimiento
se la rifa un chingo gracias
Muchas gracias
Gracias!