VBAによる最小二乗法の計算

y　=　ax　＋b　の係数a,bをExcelのマクロ処理であるVBAによって最小二乗法に従って計算する。VBAはOffice2000のExcelを使ってプログラミングした。 　Excelを開いて、ツ-ル→マクロ→VisualBasicEditerを開いて、図1のようにFormにコマンドボタンとTextBox、Labelを配置する。ExcelのSheet1に図２のように、3行目に項目を配置し、B列4行目から、xの値、C列4行目からyの値を入力する。 　準備が終わったら、VBAを実行し(図４)、データの個数を入力し、計算ボタンを押すと、計算結果が図3のように求められる。

VBAプログラム

Private Sub CommandButton1_Click() 　　　Dn = Val(TextBox1.Text) 'データ数 　　　'データにシーケンシャルNoを付ける 　　　Range("B4").Select 　　　For i = 1 To Dn 　　　　　　ActiveCell.Value = i 　　　　　　ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column).Select 　　　Next i 　　　' 　　　xsum = 0 　　　ysum = 0 　　　x2sum = 0 　　　xysum = 0 　　　Worksheets("Sheet1").Activate 　　　Range("C4").Select 　　　' 　　　For i = 1 To Dn 　　　　　　xx = ActiveCell.Value 　　　　　　' 　　　　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select 　　　　　　yy = ActiveCell.Value 　　　　　　' 　　　　　　x2 = xx * xx 　　　　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select 　　　　　　ActiveCell.Value = x2 　　　　　　' 　　　　　　xy = xx * yy 　　　　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select 　　　　　　ActiveCell.Value = xy 　　　　　　' 　　　　　　xsum = xsum + xx 　　　　　　ysum = ysum + yy 　　　　　　x2sum = x2sum + x2 　　　　　　xysum = xysum + xy 　　　　　　' 　　　　　　ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column - 3).Select 　　　Next i 　　　' 　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column - 1).Select 　　　ActiveCell.Value = "sum" 　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select 　　　ActiveCell.Value = xsum 　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select 　　　ActiveCell.Value = ysum 　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select 　　　ActiveCell.Value = x2sum 　　　ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select 　　　ActiveCell.Value = xysum 　　　' 　　　b = (ysum * x2sum - xysum * xsum) / (Dn * x2sum - (xsum * xsum)) 　　　a = (Dn * xysum - xsum * ysum) / (Dn * x2sum - (xsum * xsum)) 　　　' a, bの値を書く行を設定　 　　　ActiveSheet.Cells(ActiveCell.Row + 2, ActiveCell.Column - 3).Select 　　　' 　　　ActiveCell.Value = " a=" + Str(a) + " b=" + Str(b) 　　　 End Sub ＊上記プログラムはコピーしても空白部が全角のため動作しません。こちらのテキストファイルを使ってください。VBA.txt　　　　　　マクロ付きExcel　File

図1 図2

図3 計算結果　　*データの桁数の処理はしていません　"sum"はNoの最後の次の行に記述

■小数点以下の桁数設定

データ入力前に各セルの小数点以下3桁の桁数に設定。F4からF17のセルを設定するとき 　�@F4からF17jまでドラッグ 　　カーソルをこのセル上においてマウス右クリック。【セルの書式設定】を選択。 　�A【数値】選択 　�B【小数点以下の桁数】　3桁選択 　 　同様に他のセルも設定すれば、必要な有効数字で求めることが出来る。

図3

図4　マクロの実行