Excel - Bilineare interpolatie functie

Bilineaire interpolatie (interpolatie binnen een tweedimensionale tabel) kan met reguliere MS Excel functies. Maar dit levert lange formules op die lastig leesbaar en kopieerbaar zijn. Je kunt ook een gebruikersgedefinieerde functie (UDF) interpolateXY implementeren. De VBA code van die functie staat onderaan deze pagina. (Als je enkel X of Y wilt interpoleren binnen een 1-dimensionale tabel, zie dan Lineaire interpolatie.)

Download demobestand (33kB - 4128 keer gedownload)

Uitleg bij de berekening van bovenstaand voorbeeld

Visualisatie van bovenstaande datatabel

Berekening
X = 451 ligt tussen X = 400 en X = 500. X-fractie = (451 - 400)/(500 - 400) = 0.51
Y = 75 ligt tussen Y = 60 en Y = 80. Y-fractie = (75 - 60)/(80 - 60) = 0.75

De waarden van de 4 datapunten waartussen geïnterpoleerd moet worden (X,Y):
     1. (400,60) = 149
     2. (500,60) = 163
     3. (400,80) = 169
     4. (500,80) = 187

Interpolatie X:
     1.&2. → 149 + 0.51 × (163 - 149) = 156.14
     3.&4. → 169 + 0.51 × (187 - 169) = 178.18

Interpolatie Y:
     156.14 + 0.75 × (178.18 - 156.14) = 172.67 hetgeen de einduitkomst is.

VBA code ter plaatsing in een module

In MS Excel, druk Alt+F11 om de Visual Basic Editor te openen (Windows). Via hoofdmenu: Invoegen → Module. Kopieer onderstaande code naar de module.

Public Function InterpolateXY(ByRef X As Range, ByRef Y As Range, ByRef XRange As Range, ByRef YRange As Range, ByRef ValueTable As Range) As Double
    Dim iCol As Long
    Dim iRow As Long
    Dim C1 As Double
    Dim C2 As Double
    Dim C3 As Double
    Dim C4 As Double
    Dim F1 As Double
    Dim F2 As Double
    Dim C5 As Double
    Dim C6 As Double
    
    'search X
    iCol = 1
    Do While X >= XRange(1, iCol).Value And (iCol < XRange.Columns.Count)
        iCol = iCol + 1
    Loop
    
    'search Y
    iRow = 1
    Do While Y >= YRange(iRow, 1).Value And (iRow < YRange.Rows.Count)
        iRow = iRow + 1
    Loop

'if X or Y fall outside the table, the function result is an error value
    If (X < XRange(1, 1).Value) Or (X > XRange(1, iCol).Value) Or (Y < YRange(1, 1).Value) Or (Y > YRange(iRow, 1).Value) Then
        InterpolateXY = (1 / 0)
        Exit Function
    End If
    
    '4 data table points 'around' X and Y
    C1 = ValueTable.Cells(iRow - 1, iCol - 1).Value
    C2 = ValueTable.Cells(iRow - 1, iCol).Value
    C3 = ValueTable.Cells(iRow, iCol - 1).Value
    C4 = ValueTable.Cells(iRow, iCol).Value

'X and Y fractions
    F1 = (X - XRange.Cells(1, iCol - 1).Value) / (XRange.Cells(1, iCol).Value - XRange.Cells(1, iCol - 1).Value)
    F2 = (Y - YRange.Cells(iRow - 1, 1).Value) / (YRange.Cells(iRow, 1).Value - YRange.Cells(iRow - 1, 1).Value)

'2 intermediate results after interpolation on X
    C5 = C1 + F1 * (C2 - C1)
    C6 = C3 + F1 * (C4 - C3)
    
    'final result after interpolation on Y
    InterpolateXY = C5 + F2 * (C6 - C5)
    
End Function