【VBA・Excel】多項式近似の方法

Excel関数を利用した多項式近似の方法を紹介します。

【今回の内容】
・使用するExcel関数（LINEST関数・INDEX関数）
・多項式近似のサンプルコード

使用するExcel関数
1. データの近似を行う：LINEST関数
  1. LINEST関数の書式
2. 値を抽出する：INDEX関数
  1. INDEX関数の書式
多項式近似のサンプルコード
1. 多項式近似サンプルコード
  1. サンプルコード
2. サンプルマクロの説明

使用するExcel関数

データを近似し、その係数・切片を取得するには２つのExcel関数を組み合わせます。データの近似にはLINEST関数、値の抽出にはINDEX関数を使用します。まずはそれぞれの関数の特性について確認します。

データの近似を行う：LINEST関数

LINEST関数は、独立変数とその従属変数を最小二乗法により線形近似を行い、係数などを返す関数です。なお、LINEST関数で扱う独立変数・従属変数・関数によって返される値は一次元配列です。

※一次元配列とは1行N列[1×N]の配列のこと。
（ただしLINEST関数の引数には、N行１列[N×1]でも可）

LINEST関数が表す直線の方程式について、例えば一つの独立変数（x）と、従属変数（y）が与えられた場合は

y=mx+b

となります。また、二つの独立変数（x₁、x₂）と従属変数（y）が与えられた場合は次のような式となります。

y=m₁x₁+m₂x₂+b

n個の独立変数（x₁,x₂・・・,x_n）と従属変数（y）が与えられた場合は

y=m₁x₁+m₂x₂+・・・m_nx_n+b

となります。

LINEST関数の書式

【書式】
　 LINEST(既知の y , [既知の x ] , [定数] , [補正])

引数	省略可否	内容
既知のy	不可	y系列の値。
既知のx	可	x系列の値。省略時は、y系列と同じサイズ（データ数）で{1,2,3,…n}という配列が与えられる。
定数	可	TRUE　：定数bの値も計算。　 y=mx+bの方程式となる。
定数	可	FALSE ：定数bを0として扱う。y=mxの方程式となる。
補正	可	TRUE：回帰直線の補正項を係数・定数と併せて返す。返される配列は {mn,mn-1,…,m1,b;sen,sen-1, 　…,se1,seb;r2,sey;F,df;ssreg,ssresid} となる。
補正	可	FALSE：係数ｍと定数 bのみを返す。

エクセルでのLINEST関数の使い方は、次の通りです。配列の値を出力するには、係数を出力させるにはあらかじめ出力範囲を選択し、セルへ数式を入力後に「SHIFT」「CTRL」「ENTER」の３つのボタンを同時に押します。

LINEST関数の配列要素については、次の通りです。

要素	説明
mn	配列Xnの係数。
b	定数。引数[定数]=FALSEの時は b=0となる。
sen	係数mnの標準誤差
seb	定数bの標準誤差。引数[定数]=FALSEの時は seb= #N/A となる。
r2	決定の係数。0 ～ 1の値をとる。r2=1の時、YとＸは完全な相関がある。
sey	ｙに対する標準誤差
F	F 補正項または F 観測値。
df	自由度。
ssreg	回帰平方和。
ssresid	残余平方和。

値を抽出する：INDEX関数

INDEX関数は、対象のセル範囲（もしくは対象配列）の中から指定された行、列の位置にあるセル（配列）の値を返す関数です。

INDEX関数の書式

【書式】
　《配列形式》
　　INDEX(配列, 行番号, [列番号])
　《セル範囲形式》
　　INDEX(セル参照範囲, 行番号, [列番号], [領域番号])

《配列形式の引数》

引数	省略可否	内容
配列	不可	・検索対象の配列を指定する。・行番号と列番号を指定すると、指定した行と列が交差する位置にある値が返される。・複数行および複数列で構成される配列に対して１）行番号のみを指定⇒指定した行全体が返される。２）列番号のみを指定⇒指定した列全体が返される。
行番号	不可（条件により可）	値を返す行を指定する。配列が一行のみの場合は、省略することが可能。
列番号	可（条件により不可）	値を返す列を指定する。行番号を省略した場合は、省略不可。

《セル範囲形式の引数》

引数	省略可否	内容
セル参照範囲	不可	・検索対象のセル範囲を指定する。・行番号と列番号を指定すると、指定した行と列が交差する位置にある値が返される。・複数の範囲を対象とする場合は、引数のセル参照範囲をカッコ()で囲み、その中に範囲をカンマ区切りで入力する。
行番号	不可（条件により可）	値を返す行を指定する。参照する行が一行のみの場合は、省略することが可能。
列番号	可（条件により不可）	値を返す列を指定する。行番号を省略した場合は、省略不可。
領域番号	可	・複数の参照範囲を指定した場合に使用する。・指定した順に、1,2,3…と領域番号が割り振られている。・省略した場合は、領域番号１が参照される。

セル範囲形式でのINDEX関数の設定例として、下の表のＡ店、B店、Ｃ店の客数と売上をセル参照範囲としてB店の3月の売り上げを表示させる場合の設定です。

上記に従い、任意のセルで = INDEX((C4:D15,G4:H15,K4:L15),3,2,2)と入力すればB店の3月の売上の値がセルに反映されます。

多項式近似のサンプルコード

前述の関数を使用して、次の与えられたデータに対して多項式近似を行うサンプルコードです。

多項式近似サンプルコード

下の図のようにエクセルシートの2列目に既知のＹ、3列目に既知のＸがあり、これらをもとに多項式近似を行い4列目に近似結果を出力するマクロです。

6列目の「近似の次数」は、近似を行う次数の設定で「定数の計算（する or しない）」は”する”を設定の場合は定数項を計算し、”しない”を指定すると定数項を0として計算します。

サンプルコード

Sub 近似計算()
 Dim cfi As String
 Dim cs As String
 Dim col As Long
 Dim ro As Long
 Dim hc As Long
 Dim xr() As Double
 Dim yr() As Double
 Dim zisu As Integer
 Dim n As Integer
 Dim dummy As Double
 Dim num As Long
　
 cfi = ThisWorkbook.Name
 cs = ActiveSheet.Name
 col = 7
 ro = 2
 dummy = 0
　
 With Workbooks(cfi).Worksheets(cs)
  zisu = .Cells(3, 6)
  num = pplication.WorksheetFunction.CountA _
　(Range(.Cells(col, 　ro), .Cells(col, ro).End(xlDown)))
 End With
　
 ReDim yr(1 To num, 1 To 1)
 For i = 1 To num
  yr(i, 1) = Workbooks(cfi).Worksheets(cs).Cells(col, ro)
　col = col + 1
 Next
　
 col = 7
 ReDim xr(1 To num, 1 To zisu)
 For i = 1 To num
  For ii = 1 To zisu
　 xr(i, ii) = Workbooks(cfi).Worksheets(cs).Cells(col, ro + 1)^ii
　Next
　col = col + 1
 Next
　
 col = 7
 hc = 1
 With Workbooks(cfi).Worksheets(cs)
  Do Until .Cells(col, ro) = ""
　 n = zisu
　 For i = 1 To zisu
　  dummy = dummy + Application.WorksheetFunction _
　　.Index _(Application.WorksheetFunction.LinEst _
　　(yr, xr), 1, n) * xr(hc, 　1) ^ i
　　n = n - 1
　 Next
　 If .Cells(4, 6) = "する" Then
　  dummy = dummy + Application.WorksheetFunction. _
　　Index(Application.WorksheetFunction.LinEst _
　　(yr, xr), 1, zisu + 1, 1)
　 End If
　 .Cells(col, ro + 2) = dummy
　 col = col + 1
　 dummy = 0
　 hc = hc + 1
　Loop
 End With

End Sub

上記を実行した結果は下のようになります。