Excel VBAで線形補間

はじめに

最近仕事でExcelを触る機会が増えました。いろいろなところからデータを集めてくるのはいいものの、それぞれ刻み幅や最大最小値が異なっていて厄介。線形でもいいから補間できておけば便利かなと思い重い腰を上げVBAのFunctionを組んでみました。
「スプラインで補間しろ」「Python使え」とも思うのですが、環境構築がほとんどいらず、簡単に実装できるので何かと重宝するのではないでしょうか？

線形補間する関数

関数LinearInterpolateは1次元配列xと2次元配列refを引数に取り、2次元配列を返します。

Option Base 1
Private Function LinearInterpolate(x As Variant, ref As Variant) As Variant
    '参照する定義域の処理
    Dim xref As Variant
    ReDim xref(UBound(ref))
    For i = 1 To UBound(xref)
        xref(i) = ref(i, 1)
    Next i
    
    '参照する値域の処理
    Dim yref As Variant
    ReDim yref(UBound(ref), UBound(ref, 2) - 1)
    For i = 1 To UBound(yref)
        For j = 1 To UBound(yref, 2)
            yref(i, j) = ref(i, j + 1)
        Next j
    Next i
    
    '結果の格納先
    Dim y As Variant
    ReDim y(UBound(x), UBound(yref, 2))
    
    '線形補間の計算
    For i = 1 To UBound(x)
        'xrefの中からxを超えない最大のインデックスを取得する
        On Error Resume Next
        match_i = WorksheetFunction.Match(x(i), xref, 1)
        On Error GoTo 0
        
        For j = 1 To UBound(y, 2)
            If x(i) < WorksheetFunction.Min(xref) Or WorksheetFunction.Max(xref) < x(i) Then
                'xがxrefの範囲外のときは処理しない
                GoTo next_x
            ElseIf x(i) = WorksheetFunction.Max(xref) Then
                'xがxrefの最大値のときは値をそのまま返す
                y(i, j) = yref(match_i, j)
            Else
                '線形補間を真面目に計算する
                x1 = xref(match_i)
                x2 = xref(match_i + 1)
                y1 = yref(match_i, j)
                y2 = yref(match_i + 1, j)
                
                y(i, j) = (y2 - y1) / (x2 - x1) * (x(i) - x1) + x1
                
            End If
        Next j
next_x:
    DoEvents '定期的にDoEventsしておくと応答なしを回避できる
    Next i
    
    '出力用の配列を定義
    Dim ret As Variant
    ReDim ret(UBound(x), UBound(y, 2) + 1)
    
    '出力用の配列に値を代入
    For i = 1 To UBound(x)
        ret(i, 1) = x(i)
        For j = 1 To UBound(y, 2)
            ret(i, j + 1) = y(i, j)
        Next j
    Next i
    
    LinearInterpolate = ret
End Function

xは求めたい独立変数
refは1列目に参照する独立変数、2列目以降の参照する従属変数が格納された2次元配列です。
戻り値の配列もrefと同様、1列目に求めた独立変数、2列目以降に求めた従属変数が格納されます。

仕様

例えば、
xとして

x
1
1.5
2

refとして

xref	y1ref	y2ref
1	1	3
2	2	4

というデータを入力した際、戻り値として次のような配列を返します。

x	y1	y2
1	1	3
1.5	1.5	3.5
2	2	4

xで指定された値がxref内に存在しない場合、yの値はEmptyのままになります。

refの列はy1,y2,y3,...と自由に増やすことができるので、シートを丸々配列に代入し、そのままこの関数に突っ込むという使い方ができます。
戻り値もrefと似た構造になるため、別シートにそのまま貼りつけることも容易です。