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

        

Excelを用いた科学技術計算が書籍になりました 好評につき27年11月重版9刷!


 丸善出版事業部HPよりExcelデータをダウンロードできます
好評発売中 丸善出版 出版 \2200

amazon:Excelで操る! ここまでできる科学技術計算

楽天:Excelで操る! ここまでできる科学技術計算


マトリックスのすすめ



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



連立方程式の問題

 
 下記の3元連立1次方程式を考えよう。
 (変数は x1, x2, x3)

   5x1 + 3x2 +  x3 = 3
   4x1 + 5x2 + 2x3 = 4
    x1 + 3x2 + 6x3 = 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-
1 × A = E より

   E × x = A-1 × b

   x = A-1 × b

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



EXCELのVBAで連立方程式を解く

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


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


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




A ガウス・ザイデル法の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



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

'
' ガウス・ザイデル法により連立方程式のを解く。
' (有)ゴッドフット企画
'
Sub Matrix()
'
' マトリックス A
Dim a(1 To 3, 1 To 3) As Double
'
' ベクトル b
Dim b(1 To 3) As Double
'
' 解 x
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
'
' ベクトルbをセルから読み込む
'
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



C VBAをマクロに登録


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


                図2 Microsoft Visual Basic のウィンドウ



D VBAマクロの実行


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


             図3 VBAマクロの実行結果

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


 



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

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



EXCELを用いた科学技術計算

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




〒673−0036 兵庫県明石市松江62−14
(有)ゴッドフット企画



感想をお聞かせ下さい



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




JR福知山線 脱線事故シミュレーション

脱線事故シミュレーションも連立方程式を解く
(Yahoo Japan 掲載)


Copyright (C) GODFOOT 2007 All Rights Reserved.