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

経緯としては、以下の記事内で件数を増やしたときの予測数値を算出したいと思ったからです。

この記事は数年前に書いたものですが、最新のOffice365で再検証して書き直ししました。
この中で、速度実測した結果があります。
1万件で、12秒
2万件で、44秒
3万件で、106秒
件数に対して、指数的に増加しているように見受けられます。

そこで一般的な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(配列)
行数と列数が等しい数値配列 (正方行列) を指定します。

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

12 = a + b + c
44 = 4a + 2b + c
106 = 9a + 3b + c
これを行列で表現すると、

こういう事になるらしい。（らしいって（笑））

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

-1というのは逆行列で、このように記述するらしい。
これを見ると、確かにこれで計算できるのは理解できます。
そして、
逆行列を求めるには、MINVERSE関数を使えば良い。
行列の掛け算は、MMULT関数を使えば良い。
ここまでわかれば、あとはエクセルに数式を入れるだけですね。

MINVERSE関数を入れる

MMULT関数を入れる

上記で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分かかるという予測になります。

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

同じテーマの記事
新着記事
アクセスランク