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

Excel・VBA

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

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

使用するExcel関数

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

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

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

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

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

y=mx+b

となります。また、二つの独立変数(x1、x2)と従属変数(y)が与えられた場合は次のような式となります。

y=m1x1+m2x2+b

n個の独立変数(x1,x2・・・,xn)と従属変数(y)が与えられた場合は

y=m1x1+m2x2+・・・mnxn+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:係数mと定数 bのみを返す。 

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

LINEST関数による係数の求め方

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

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

値を抽出する:INDEX関数

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

INDEX関数の書式

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

《配列形式の引数》

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

《セル範囲形式の引数》

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

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

INDEX関数 セル範囲形式の設定例

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

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

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

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

下の図のようにエクセルシートの2列目に既知のY、3列目に既知のXがあり、これらをもとに多項式近似を行い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

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

サンプルマクロの結果

サンプルマクロの説明

最後にサンプルマクロの説明です。大まかには次の通りです。既知のY・Xの要素数と近似数に合わせて配列 yr ,xrを再定義し、配列へデータを格納し、エクセル関数(INDEX関数・LINEST関数)によって近似計算を行います。

サンプルマクロの説明

INDEX関数とLINEST関数の使用部分についてもう少し詳しく見ると次のようになっています。

INDEX関数とLINEST関数による多項式の係数取得

多項式近似の方法については以上です。

コメント

タイトルとURLをコピーしました