回帰分析により実験データの近似曲線を求める (難問)
前問では 回帰直線(近似直線) を求めた。本問では、次図Ｃ列の 10個の実験 データ(Ｙ軸)から、回帰曲線(近似曲線) y=a+bx+cx^2 を求めることを考え る。 Ｂ列に新たに x^2 の列を挿入している。 前問と同様、事前に Excel 上 で、[ツール] - [アドイン] をクリックし、「分析ツール」「分析ツール - VBA」 の二ヶ所にチェックを入れて、'Microsoft Office 2000' の CD-ROM からインストールしておく。 図１
bottom top
Excel のアドイン機能による方法
---------------------------------
Excel - [ツール] - [分析ツール] - [回帰分析] とクリックして選択する。
次に、[入力元] の 「入力Ｙ範囲」 で 上図の Ｃ列のＹ軸データを選択し、
「入力Ｘ範囲」 で Ａ列のＸ軸データとＢ列を一緒にドラッグし選択すると
新規シートに次の表が自動的に追加される。 図２に見える 切片 の係数が
回帰曲線 y=a+bx+cx^2 の定数項 a で、Ｘ値１ の係数が 一次項 b、また
Ｘ値２ の係数が 二次項 c である。
図２
bottom top Excel VBA による方法 ---------------------- 次のコードは、下記URL を参考にした。このコードを実行すると、上図１の データ行の下の行のセル範囲 A14〜B16 に、一次項・二次項と定数項の値を 算出する。 Private Sub test1() Dim matX As Variant Dim matY As Variant Dim matC As Variant Dim Dn As Long Dim Lr As Long Dim Ri As Long 'データ設定 With Sheet1 Lr = .Range("A1").End(xlDown).Row matX = .Range("A2:A" & Lr).Value matY = .Range("C2:C" & Lr).Value End With Dn = Lr - 1 '二次項・定数項の設定 ReDim Preserve matX(1 To Dn, 1 To 3) For Ri = 1 To Dn matX(Ri, 2) = matX(Ri, 1) * matX(Ri, 1) matX(Ri, 3) = 1 Next '関数 matC = CalcCoefficients(matX, matY) '出力 With Sheet1 .Range("B14:B16").Value = matC .Range("A14").Value = "一次項" .Range("A15").Value = "二次項" .Range("A16").Value = "定数項" End With End Sub '回帰計算の定義関数 Function CalcCoefficients(matX As Variant, _ matY As Variant) As Variant Dim mat1 As Variant Dim mat2 As Variant Dim mat3 As Variant Dim mat4 As Variant Dim mat5 As Variant '回帰計算 With WorksheetFunction mat1 = .Transpose(matX) '転置行列 mat2 = .MMult(mat1, matX) '行列の積 mat3 = .MMult(mat1, matY) '行列の積 mat4 = .MInverse(mat2) '逆行列 mat5 = .MMult(mat4, mat3) '行列の積 End With CalcCoefficients = mat5 End Function この結果、回帰曲線(近似曲線) は、次の通り求まる。 y = 4.783333333 + 0.953787879x - 0.018939394x^2 ----------------------------------------------------- (補足) 回帰分析により 近似曲線(近似直線) の係数を求めるための統計学的 な条件として、ｎ個の正規母集団からの任意標本 (x1, y1), (x2, y2), ... ... (xn, yn) を抽出したときに、個々の正規分布に従う確率変数 Yi の標本 値 yi の平均が、この曲線(直線)上にあると仮定している。 また、分散は各 正規分布について同一であると仮定している。 《参考書籍》 統計数学入門 (数学ライブラリー18) 本間鶴千代著, 森北出版 《参考URL》 MOUG(モーグ) Microsoft Office Users Group Q&A 掲示板 http://www2.moug.net/cgi-bin/mdboard.cgi?exvba+ID0001 back top