どうも、shirokumaです。
たまにはエンジニア的なお話も。
とあるデータをExcelでサクッとFittingすることがあると思います。
例としてはこんな感じ。
赤線のような線形近似では不十分で、青線のような多項式近似を使うことがあるでしょう。
そしてこの式を取得したい、と。
図のオプションで式の表示は可能ですが、処理量が多いとVBAマクロで済ませたいところだと思います。
線形近似の例は多いですが、多項式はあまり例がなかったので書いておきます。
わかる人には説明は不要なレベルですので、さっそくサンプルコード。
今回はx,yのサンプルデータは10点としたので、配列の大きさが10になっています。
Dim Xary(9) As Double
Dim Xary2(1, 9) As Double
Dim Yary(9) As Double
Xary(x) = ….(任意のデータを配列で作成)
Yary(y) = ….(任意のデータを配列で作成)
For cnt = 0 To 9
Xary2(0, cnt) = Xary(cnt)
Xary2(1, cnt) = Xary(cnt) ^ 2
Next
’線形近似
a = WorksheetFunction.Index(WorksheetFunction.LinEst(Yary, Xary, True, False), 1, 1)
b = WorksheetFunction.Index(WorksheetFunction.LinEst(Yary, Xary, True, False), 1, 2)
’多項式近似（二次）
aa = WorksheetFunction.Index(WorksheetFunction.LinEst(Yary, Xary2, True, False), 1, 1)
bb = WorksheetFunction.Index(WorksheetFunction.LinEst(Yary, Xary2, True, False), 1, 2)
cc = WorksheetFunction.Index(WorksheetFunction.LinEst(Yary, Xary2, True, False), 1, 3)
End Sub
では解説。
一つ目のポイントはサンプルデータはXary、Yaryという配列にいれてしまうことです。
VBAはとにかく配列に入れた方が何かと処理が早いし使い勝手がいいです（初心者談）
線形近似の場合は、
サンプルのようにLinEst関数にXary,Yaryを入れて係数を計算させます。0を通るようにしたい場合はTrue->Falseに。こちらはネットにも例が多くあるので詳細は割愛。
二次式にした場合は、
Xary2という二次元配列を準備するというのが二つ目のポイント。
(9,1)ではなく、(1,9)なのがミソです。
次にXary2の(0,x)にはXaryと同じデータを、Xary2の(1,x)にはXaryのデータを2乗した値を格納します。
そして、線形近似と同じような形式でXaryの代わりにXary2を代入するだけです。
LinEst関数は複数の結果を返すので、Index関数で値をひとつずつ取り出してやります。一番最後の1,2,3というのが、それぞれ(aa)x^2+(bb)x+ccの係数aa,bb,ccに対応します。例えば0を通るようにFalseにした場合にはcc=0が返ってきます。
あらかじめExcelで用意されている関数の中まで調べるモチベーションはないので厳密に説明はできませんが、自分の使用する範囲では特にエラーなく正しい値が返ってきているようです。
“If it walks like a duck and quacks like a duck, it must be a duck”
（もしもそれがアヒルのように歩き、鳴くのなら、それはアヒルに違いない）
が、担保はできませんので参考にする際には自己責任でお願いします。
ちなみにExcelシート上で直接やるなら下記のような式で出来ます。
=INDEX(LINEST( Yrange , Xrange^{1,2} , TRUE,FALSE),0,1)
こちらはネットにはよく落ちてますが、このXrange^{1,2}というのは、
Xrangeと（Xrange）^2の行列のようです。
つまり、
yの値がD3:D12に、xの値がB3:B12に、x^2の値がC3:C12にあるとすれば、
=INDEX(LINEST(D3:D12,B3:C12,TRUE,FALSE),0,1)
これで同じ結果が得られるというわけです。
そんなわけで、VBAでもrange関数で作ればいけるかな、っという発想でした。
少しマニアックなところですが、参考になれば幸い。
メリークリスマス。