[PR] 印鑑

ExcelのVBAを用いた連立方程式の計算

Excelを用いた科学技術計算が第2版になりました　30年10月！

amazon：Excelで操る! ここまでできる科学技術計算第2版

楽天： Excelで操る! ここまでできる科学技術計算 [ 神足　史人 ]

マトリックスのすすめ

　連立方程式をEXCELのVB（ビジュアルベーシック）をつかって、解いて見よう。
解法はガウス・ザイデル法を採用します。
一般的に、マトリックスの次元が大きくなると、吐出法よりはガウス・ザイデル法のほうが、精度が良いようです。

連立方程式の問題

　
　下記の３元連立１次方程式を考えよう。
　（変数は x₁, x₂, x₃)

　　　5x₁ + 3x₂ + 　x₃ = 3
　　　4x₁ + 5x₂ + 2x₃ = 4
　　　　x₁ + 3x₂ + 6x₃ = 6

とおき、マトリックスで表記すると、極めて簡単に　

　　A　×x = b　　　・・・・・　(1)

となる。

たった1日で即戦力になるExcelの教科書

入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス)

Excel VBAのプログラミングのツボとコツがゼッタイにわかる本

Excelで学ぶ統計解析―統計学理論をExcelでシミュレーションすれば、視覚的に理解できる

エクセルこれだけ関数事典90―ハンディで見やすい!

Excelでマスターするビジネスデータ分析実践の極意

連立方程式の解

　連立方程式は下記マトリックス式で表記できる。

　　　A　×x = b

両辺に逆マトリックス　A^-1をかける。

　　　A^-1　× ( A × x ) = A^-1 × b

　　　(A^-1　× A ) × x = A^-1 × b

　　A^-¹ × A = E より

　　　E × x = A^-1 × b

　　　x = A^-1 × b

これで解 x が計算できる。

ＥＸＣＥＬのVBAで連立方程式を解く

① マトリックス( A )、ベクトル( b ) の入力

　元来EXCELは行列（マトリックス）でできている。
　図１のように、セルにマトリックス、ベクトルエリアを確保し、値を入力する。

　　　　　図１　マトリックスとベクトルを入力するエクセルシート

②　ガウス・ザイデル法のVBAプログラム

Option Explicit
'
' 逆マトリックス計算(ガウス・ザイデル法)
'
Function Minvd(a() As Double, ByRef n As Long, ByRef epsl As Double) As Integer
' a() : マトリックス　（出力は逆マトリックス）
' n :マトリックスの次元
' epsl : ε誤差判定（非常に小さい値）
' Minvd : 0; 正常終了　>0;　解なし
Dim noseg()
Dim nn As Long
Dim i As Long
Dim p As Double
Dim ip As Long
Dim nw As Long
Dim j As Long
Dim w As Double

Dim jj As Long

Minvd = 0

If n <= 0 Then

Minvd = 1

Exit Function

End If

If n = 1 Then

a(1, 1) = 1# / a(1, 1)

Minvd = 0

Exit Function

End If

ReDim noseg(1 To n)

For nn = 1 To n

noseg(nn) = nn

Next

For nn = 1 To n

p = 0#

For i = nn To n

If p < Abs(a(i, 1)) Then

p = Abs(a(i, 1))

ip = i

End If

Next

If p < epsl Then

Minvd = 2

Exit Function

End If

nw = noseg(ip)
noseg(ip) = noseg(nn)
noseg(nn) = nw

For j = 1 To n

w = a(ip, j)
a(ip, j) = a(nn, j)
a(nn, j) = w

Next

w = a(nn, 1)

For j = 2 To n

a(nn, j - 1) = a(nn, j) / w

Next

a(nn, n) = 1# / w

For i = 1 To n

If i <> nn Then

w = a(i, 1)

For j = 2 To n

a(i, j - 1) = a(i, j) - w * a(nn, j - 1)

Next

a(i, n) = -w * a(nn, n)

End If

Next

Next

For nn = 1 To n

For j = nn To n

jj = j
If noseg(j) = nn Then Exit For

Next

noseg(jj) = noseg(nn)

For i = 1 To n

w = a(i, j)
a(i, j) = a(i, nn)
a(i, nn) = w

Next

Next

Minvd = 0

End Function

③　連立方程式を解くVBAメインプログラム

'
'　ガウス・ザイデル法により連立方程式のを解く。
' (有)ゴッドフット企画
'
Sub Matrix()
'
' マトリックス A
Dim a(1 To 3, 1 To 3) As Double
'
'　ベクトル　b
Dim b(1 To 3) As Double
'
' 解　ｘ
Dim x(1 To 3) As Double
'
Dim i As Long
Dim j As Long
Dim ic As Long
' マトリックスの次元
Dim n As Long
'
Dim eps As Double
'
' マトリックスAの要素をセルから読み込む
'
n = 3

For i = 1 To n

For j = 1 To n

a(i, j) = Cells(i + 3, j + 2)

Next

Next
'
' ベクトルｂをセルから読み込む
'
For i = 1 To n

b(i) = Cells(i + 7, 3)

Next
'
' ガウス・ザイゼル法による逆マトリックス計算
'
eps = 0.0000000001

ic = Minvd(a, n, eps)
'
' 解　x = A-1× bを求める
'
For i = 1 To n

x(i) = 0#

For j = 1 To n

x(i) = x(i) + a(i, j) * b(j)

Next

Next
'
' 解をシートに出力する
'
For i = 1 To n

Cells(i + 11, 3) = x(i)

Next

End Sub

④　VBAをマクロに登録

　EXCELのツールバーで、　ツール　ー　マクロ　ー　Visual Basic Editor を選択すると、
Microsoft Visual Basic のウィンドウが現れる。
現れたウインドウのツールバーで、　挿入　ー　標準モジュールを選択すると、空のプログラム
エリアが現れる。
図２のように、そのエリヤに上記プログラムをコピーする。（標準モジュールを２個作る）

　　　　　　　　　　　　　　　　図２　Microsoft Visual Basic のウィンドウ

⑤　VBAマクロの実行

Microsoft Visual Basic のウィンドウを閉じ、ツールバーで、
ツール　－　マクロ　－　マクロを選択し、Matrixを実行すると、図３のように即座に解が求まる。

　　　　　　　　　　　　　図３　VBAマクロの実行結果

正解かどうか、検算しましょう。

連立方程式をエクセル(EXCEL)で解いてみよう

 エクセルのソルバーを用い連立方程式を解いてみよう

EXCELを用いた科学技術計算

 EXCELのソルバーを用いた科学技術計算

〒６７３－００３６　兵庫県明石市松江６２－１４
(有)ゴッドフット企画

感想をお聞かせ下さい

連立方程式（鶴と亀の足の数を数えるアホはいない）