Excelマクロで最小二乗近似

Excelマクロ内で最小二乗法による近似多項式の各項の係数を求める方法を記録する。
Excelのワークシートで用意されている関数LinEstを用いる。

LinEst(既知のy, 既知のx, 定数項, 補正)

既知のy 計測値などを入力する。1行もしくは1列の配列。
既知のx 既知のyと同じ行、もしくは列方向に並んだデータ。^{1, 2}を付けることで1乗、2乗のベクトルを表す。
定数項 TRUE/FALSEで与える。TRUEは定数項を計算することを示す。FALSEは定数項を0とすることを示す。
補正 TRUEは計算結果の詳細を返す。確実度であるR^2値を得るためにTRUEを指定する。

本投稿では、2次の式で近似する例を示している。
既知のxに付加する文字列を^{1, 2, 3}などと変更することで、3次式で近似するなど対応する。

LinEst関数は、求めた回帰直線の係数を

m1 * x1 + m2 * x2 + ... + b

に対して

{mn, mn-1, ... , m2, m1, b}

で返すため、これをIndex関数により取り出す。
なお、R^2値については、返される配列の3行1列目に格納されるため、これもIndex関数で取り出すことができる。

近似多項式

    ' ax^2+bx+cの式で近似する
    Dim coef_a As Double
    Dim coef_b As Double
    Dim coef_c As Double
    Dim r2 As Double
    Dim xvalues As String
    Dim yvalues As String
    Dim result As String

    ' 解析対象データの構築
    xvalues = "a7:a23"
    yvalues = "c7:c23"

    ' 最小二乗近似("^{1,2}"を付加することで、1乗、2乗の項の定数配列を構築する)
    coef_a = WorksheetFunction.Index(WorksheetFunction.LinEst(Evaluate(yvalues), Evaluate(xvalues + "^{1,2}"), True, True), 1, 1)
    coef_b = WorksheetFunction.Index(WorksheetFunction.LinEst(Evaluate(yvalues), Evaluate(xvalues + "^{1,2}"), True, True), 1, 2)
    coef_c = WorksheetFunction.Index(WorksheetFunction.LinEst(Evaluate(yvalues), Evaluate(xvalues + "^{1,2}"), True, True), 1, 3)
    r2 = WorksheetFunction.Index(WorksheetFunction.LinEst(Evaluate(yvalues), Evaluate(xvalues + "^{1,2}"), True, True), 3, 1)

既知のxを与える部分、多項式の各項の係数を受ける部分を動的に変更させることで、任意の次数での近似多項式を求めることができる。