エクセル雑感
エクセルで連立方程式を解く(MINVERSE,MMULT)

ExcelマクロVBAとエクセル関数についての私的雑感
最終更新日:2020-02-07

エクセルで連立方程式を解く(MINVERSE,MMULT)


とつぜん連立方程式を解くことになりました。
なぜ連立方程式を解くことになったのか、そして、どうやって答えを導き出したのか・・・
これらを自身の覚え書きの意味も含めて記事にしておきます。
ただし、行列の難しい説明は抜かして、どうやって解決したかの経緯の説明が中心になります。


連立方程式を解くことになった経緯

経緯としては、以下の記事内で件数を増やしたときの予測数値を算出したいと思ったからです。
Dictionary(ディクショナリー)のパフォーマンスについて
Dictionary(ディクショナリー)は辞書機能で、連想配列とも呼ばれます。この辞書は、重複は許されず、キーとデータの2つが存在します、今回はこのDictionaryのパフォーマンス(処理速度)を検証します。Dictionaryの基本的な使い方については、こちらを参照してください。
この記事は数年前に書いたものですが、最新のOffice365で再検証して書き直ししました。
この中で、速度実測した結果があります。
1万件で、12秒
2万件で、44秒
3万件で、106秒
件数に対して、指数的に増加しているように見受けられます。

VBA マクロ 連立方程式 MINVERSE MMULT

そこで一般的な2次方程式に当てはめてみることにしました。

y = ax^2 + bx + c

この式に、1万件を1として当てはめてみると、

12 = a + b + c
44 = 4a + 2b + c
106 = 9a + 3b + c

このようになります。
つまり、この連立3元1次方程式を解いてa,b,cを求めれば良いということです。

もともとの記事を書いたときには、紙と鉛筆で計算したのだろうと思います。
そして今回もとりあえず計算してみたが・・・出た答えはどうみても正しくない。
途中で計算を間違えたようです。
やり直しても良いのですが、せっかく目の前に超優秀な助手がいるのですから、お手伝いしてもらう事にしました。
そうです、エクセルで連立方程式を解くことにしました。
しかし、ここで解決しなければならない問題があります。

私は、エクセルで連立方程式を解いたことが無い・・・

解らないことはグーグル大先生に聞くしかありません。
参考に読んだ記事はいくつもあります。
ググればいっぱい出てきますので、興味があれば検索してみてください。
たくさん出てきましたが、どこもほぼ同じ、
MINVERSE関数とMMULT関数を使えば簡単に求められる
なるほど、そうですか、
連立方程式を行列にして解けは良いのですね。
そして、その時に使う関数が、MINVERSE関数とMMULT関数なのですね。

MINVERSE関数

MINVERSE 関数は、配列に格納されている行列の逆行列を返します。

MINVERSE(配列)

行数と列数が等しい数値配列 (正方行列) を指定します。

MMULT関数

MMULT関数は、2つの配列の行列積を返します。

MMULT(配列1, 配列2)

配列1および配列2には、行列積を求める2つの配列を指定します。
計算結果は、行数が配列1と同じで、列数が配列2と同じ配列になります。

連立方程式を行列にする

12 = a + b + c
44 = 4a + 2b + c
106 = 9a + 3b + c

これを行列で表現すると、

VBA マクロ 連立方程式 MINVERSE MMULT

こういう事になるらしい。(らしいって(笑))
行列積は以下のようになります。

VBA マクロ 連立方程式 MINVERSE MMULT

つまり、これの逆順で連立方程式を行列にします。

a,b,cの係数を抜き出してそのまま並べれば良いだけです。
こうなればなんとなく分かってきました、a,b,cを求めるには、

VBA マクロ 連立方程式 MINVERSE MMULT

-1というのは逆行列で、このように記述するらしい。
これを見ると、確かにこれで計算できるのは理解できます。
そして、

逆行列を求めるには、MINVERSE関数を使えば良い。
行列の掛け算は、MMULT関数を使えば良い。
ここまでわかれば、あとはエクセルに数式を入れるだけですね。

MINVERSE関数とMMULT関数をエクセルに入れる

MINVERSE関数を入れる

VBA マクロ 連立方程式 MINVERSE MMULT

VBA マクロ 連立方程式 MINVERSE MMULT

上記はスピルしていますので、スピルしないエクセルの場合は、
A5:C7を選択して、
=MINVERSE(A1:C3)
これを、Ctrl+Shift+Entreで配列数式として入れてください。
{=MINVERSE(A1:C3)}

A5:C7がA1:C3の逆行列という事になります。
出てきた数値が何を意味するか・・・とりあえずあまり考えずに進みます。

MMULT関数を入れる

VBA マクロ 連立方程式 MINVERSE MMULT

VBA マクロ 連立方程式 MINVERSE MMULT

上記はスピルしていますので、スピルしないエクセルの場合は、
G5:G7を選択して、
=MMULT(A5#,K1:K3)
これを、Ctrl+Shift+Entreで配列数式として入れてください。
{=MMULT(A5#,K1:K3)}

MMULT関数の注意点
MMULT(配列1, 配列2)
計算結果は、行数が配列1と同じで列数が配列2と同じ配列になります。
したがって、引数の順番を入れ替えてはダメです。

結果を検算してみる

上記でa,b,cが求められました。
a = 15
b = -13
c = 10
これを使って2次方程式を計算してみます。

y = 15x^2 - 13x + 10
x = 1 → y = 12
x = 2 → y = 44
x = 3 → y = 106

計算結果は正しいようです。
では、x = 10とした場合は、
x = 10 → y = 1380
これは秒数ですので、23分です。
つまり先の記事の例では、10万件で実行した場合23分かかるという予測になります。

エクセルで連立方程式を解くの最後に

ここまでの説明で意図的にスルーしたのですが、逆行列の求め方が難しい・・・
興味のある方は検索してみてください、高校・大学数学として解説しているページが多数でてきます。
簡易的にでも説明ができないかと思ったのですが、とてもそんなレベルでは説明できそうにありません。
と言いますか、そもそも私がちゃんと理解できていない・・・

という事で、今回はあくのでエクセルで連立方程式を解く場合の手順を淡々と説明してみました。



同じテーマ「エクセル雑感」の記事

offset 検索ワードで非常に多いので「offsetまとめ」
Excelのリンクの管理について
プログラミングとは
「ネ申Excel」問題 への同意と反論
「Excel3ステップ理論」3階層システムの応用
「ポケモンを確実に見つける方法」をExcelで数学してみた
「VBAによる解析シリーズその2 カッコ」をやってみた
ツイッターで出されたVBAのお題をやってみた
エクセルで「もういくつ寝るとお正月」
エクセルで連立方程式を解く(MINVERSE,MMULT)
「VBAで導関数を求めよ」ツイッターのお題をやってみた


新着記事NEW ・・・新着記事一覧を見る

「VBAで導関数を求めよ」ツイッターのお題をやってみた|エクセル雑感(2月9日)
Property {Get|Let|Set} ステートメント|VBA入門(2月9日)
フィボナッチ数列(再帰呼び出し)|VBA技術解説(2月8日)
エクセルで連立方程式を解く(MINVERSE,MMULT)|エクセル雑感(2月7日)
スピルって速いの?スピルの速度について|VBA技術解説(2月4日)
スピルでVBAの何が変わったか|VBA技術解説(2月3日)
スピルについて|エクセル入門(2月3日)
エクセルで「もういくつ寝るとお正月」|エクセル雑感(1月29日)
VBAの少数以下の演算誤差について|VBA技術解説(1月29日)
VBAで写真の撮影日時や音楽動画の長さを取得する|VBA技術解説(1月23日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ