エクセルで連立方程式を解く（MINVERSE,MMULT）
とつぜん連立方程式を解くことになりました。
なぜ連立方程式を解くことになったのか、そして、どうやって答えを導き出したのか・・・
これらを自身の覚え書きの意味も含めて記事にしておきます。
ただし、行列の難しい説明は抜かして、どうやって解決したかの経緯の説明が中心になります。
連立方程式を解くことになった経緯
経緯としては、以下の記事内で件数を増やしたときの予測数値を算出したいと思ったからです。
Dictionary（ディクショナリー）のパフォーマンスについて
Dictionary（ディクショナリー）は辞書機能で、連想配列とも呼ばれます。この辞書は、重複は許されず、キーとデータの２つが存在します、今回はこのDictionaryのパフォーマンス（処理速度）を検証します。Dictionaryの基本的な使い方については、こちらを参照してください。
この記事は数年前に書いたものですが、最新の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を求めれば良いということです。
もともとの記事を書いたときには、紙と鉛筆で計算したのだろうと思います。
そして今回もとりあえず計算してみたが・・・出た答えはどうみても正しくない。
途中で計算を間違えたようです。
やり直しても良いのですが、せっかく目の前に超優秀な助手がいるのですから、お手伝いしてもらう事にしました。
そうです、エクセルで連立方程式を解くことにしました。
しかし、ここで解決しなければならない問題があります。
私は、エクセルで連立方程式を解いたことが無い・・・
解らないことはグーグル大先生に聞くしかありません。
この中で、速度実測した結果があります。
1万件で、12秒
2万件で、44秒
3万件で、106秒
件数に対して、指数的に増加しているように見受けられます。
44 = 4a + 2b + c
106 = 9a + 3b + c
つまり、この連立3元1次方程式を解いてa,b,cを求めれば良いということです。
そして今回もとりあえず計算してみたが・・・出た答えはどうみても正しくない。
途中で計算を間違えたようです。
やり直しても良いのですが、せっかく目の前に超優秀な助手がいるのですから、お手伝いしてもらう事にしました。
そうです、エクセルで連立方程式を解くことにしました。
しかし、ここで解決しなければならない問題があります。
私は、エクセルで連立方程式を解いたことが無い・・・
参考に読んだ記事はいくつもあります。
ググればいっぱい出てきますので、興味があれば検索してみてください。
ググればいっぱい出てきますので、興味があれば検索してみてください。
たくさん出てきましたが、どこもほぼ同じ、
MINVERSE関数とMMULT関数を使えば簡単に求められる
なるほど、そうですか、
連立方程式を行列にして解けは良いのですね。
そして、その時に使う関数が、MINVERSE関数とMMULT関数なのですね。
MINVERSE関数とMMULT関数を使えば簡単に求められる
なるほど、そうですか、
連立方程式を行列にして解けは良いのですね。
そして、その時に使う関数が、MINVERSE関数とMMULT関数なのですね。
MINVERSE関数
MINVERSE 関数は、配列に格納されている行列の逆行列を返します。
MINVERSE(配列)
行数と列数が等しい数値配列 (正方行列) を指定します。
MMULT関数
MMULT関数は、2つの配列の行列積を返します。
MMULT(配列1, 配列2)
配列1および配列2には、行列積を求める2つの配列を指定します。
計算結果は、行数が配列1と同じで、列数が配列2と同じ配列になります。
計算結果は、行数が配列1と同じで、列数が配列2と同じ配列になります。
連立方程式を行列にする
12 = a + b + c
44 = 4a + 2b + c
106 = 9a + 3b + c
これを行列で表現すると、
こういう事になるらしい。（らしいって（笑））
44 = 4a + 2b + c
106 = 9a + 3b + c
行列積は以下のようになります。
つまり、これの逆順で連立方程式を行列にします。
a,b,cの係数を抜き出してそのまま並べれば良いだけです。
こうなればなんとなく分かってきました、a,b,cを求めるには、
これを見ると、確かにこれで計算できるのは理解できます。
そして、
逆行列を求めるには、MINVERSE関数を使えば良い。
行列の掛け算は、MMULT関数を使えば良い。
ここまでわかれば、あとはエクセルに数式を入れるだけですね。
MINVERSE関数とMMULT関数をエクセルに入れる
MINVERSE関数を入れる
A5:C7を選択して、
=MINVERSE(A1:C3)
これを、Ctrl+Shift+Entreで配列数式として入れてください。
{=MINVERSE(A1:C3)}
出てきた数値が何を意味するか・・・とりあえずあまり考えずに進みます。
MMULT関数を入れる
G5:G7を選択して、
=MMULT(A5#,K1:K3)
これを、Ctrl+Shift+Entreで配列数式として入れてください。
{=MMULT(A5#,K1:K3)}
MMULT(配列1, 配列2)
計算結果は、行数が配列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分かかるという予測になります。
a = 15
b = -13
c = 10
これを使って2次方程式を計算してみます。
x = 1 → y = 12
x = 2 → y = 44
x = 3 → y = 106
では、x = 10とした場合は、
x = 10 → y = 1380
これは秒数ですので、23分です。
つまり先の記事の例では、10万件で実行した場合23分かかるという予測になります。
エクセルで連立方程式を解くの最後に
ここまでの説明で意図的にスルーしたのですが、逆行列の求め方が難しい・・・
興味のある方は検索してみてください、高校・大学数学として解説しているページが多数でてきます。
簡易的にでも説明ができないかと思ったのですが、とてもそんなレベルでは説明できそうにありません。
と言いますか、そもそも私がちゃんと理解できていない・・・
という事で、今回はあくのでエクセルで連立方程式を解く場合の手順を淡々と説明してみました。
興味のある方は検索してみてください、高校・大学数学として解説しているページが多数でてきます。
簡易的にでも説明ができないかと思ったのですが、とてもそんなレベルでは説明できそうにありません。
と言いますか、そもそも私がちゃんと理解できていない・・・
同じテーマ「エクセル雑感」の記事
VBAあるある Step-1 入れ忘れて、全く動かず… VBAあるある Withの中で、ドット(.)を忘れて、ドット疲れる。VBAあるある 独り言が多くなる 「あれ」「なんで」「ちがう」「そうか」「なるほど」ここまでは普通、さらに進むと 「ゴメン」「悪かった」「許して」独り言も、ここまでくれば一人前 VBAあるある…
Excelマクロを書くのに○○力は必用か？マクロを書くのに英語力は必要かどうか？ はっきり言って、必要です…ガーン それでは話が終わってしまいますね、最低限の、英語というか英単語は知らないとキツイと思います。しかし、TOEICで高得点をとるような英語力は必要ないです。何より、私はTOEICは受けた事がないし、受けても、惨憺たる結果は見えています。
「offsetまとめ」検索ワードで非常に多いoffsetサイト内の比率としては、VBAのOffsetプロパティが非常に多く使われています、シートのOFFSET関数も、参照範囲の可変化で必須となっています。offsetを抜きにして、Excelは語れないというほど重要です。OFFSET関数Offsetプロパティの主要記事の一覧です。
Excelのリンクの管理についてExcelファイルの入っているフォルダごと移動した時に、リンク切れを起こして困っていることが多いようです、そもそもExcelのリンクは、相対参照なのか絶対参照なのかといった根本から考えておかないと、このような事態になってしまいます。他のブックを参照する数式を入れたとき、その参照は、相対参照・絶対参照のどちらで格納さ…
プログラミングとはコンピューター(PC)は、こと計算については天才的な能力を持つが、その他の事については一般人の常識が通用しない子供のような存在です、人間で言うなら、サヴァン症候群でしょう、サヴァン症候群のPCに対して何かをしてもらいたい時に、それを文章で伝えるものがプログラムです。従って、プログラミングでは、あなたの常識は一切通用…
「ネ申Excel」問題 への同意と反論「ネ申Excel」、私は流行に疎いので、ネ申の意味が最初分からなかった、どうやら、「神Excel」のことのようです、大学教授が書いた、Excelの問題点と解決策の提案がかかれた論文です、この論文に対する、私なりの同意と反論です。Excelを論ずるのに、なぜネ申などという言葉を使ったのか、私には少々理解しがたい部分も…
「Excel3ステップ理論」3階層システムの応用エクセルでシステムを作成する時に、念頭に置くべき3階層の考え方になります、本来はExcelに限った事ではなく、システム作成では常に3階層を考えて設計しますが、さらに考えを推し進めて3ステップとして考えます、つまり、「Excel3ステップ理論」です。データを入力するシートで、さらに集計し、さらに印刷までやろうとしたら、
「ポケモンを確実に見つける方法」をExcelで数学してみたポケモンGO関連の記事をみていたら、【ポケモンGO】かくれているポケモンを確実に見つける方法、という記事を読んだ。普通にそうだなという感想なのですが、もっとまじめに数学したら、もっと良い方法があるのではないかと。元記事はあちこちで見た気がするが、多分これでしょ 「【ポケモンGO】かくれているポケモンを確実に見つける…
エクセルで「もういくつ寝るとお正月」今日は2020年1月31日です。早いもので1月が終わろうとしています。つまり、2020年も12分の1がすでに終わろうとしています。この調子では、あっという間に今年（2020年）も終わってしまいそうです。
エクセルで連立方程式を解く（MINVERSE,MMULT）VBAが消えてしまった！マクロが壊れて動かない！
2020年4月15日に配信されたWindows10用セキュリティ更新プログラムの不具合で、なんと、VBAが全て消えてしまうという事が発生しています。今後もあり得る事なので、このような場合の対策について記しておきます。VBAが消えてしまうとはどんな状態なのか エクセルファイルを開くと、以下のようなメッセージが出力され…
新着記事NEW ・・・新着記事一覧を見る
イータ縮小ラムダ（eta reduced lambda）｜エクセル入門(2023-11-20)
PIVOTBY関数（縦軸と横軸でグループ化して集計）｜エクセル入門(2023-11-19)
GROUPBY関数（縦軸でグループ化して集計）｜エクセル入門(2023-11-18)
PY関数（Pythonコードをセル内で実行）｜エクセル入門(2023-11-17)
画像「セルに配置」のVBAについて（365の新機能）｜VBA技術解説(2023-11-13)
スピルのゴーストの範囲を選択するVBA｜ツイッター出題回答 (2023-10-31)
エクセル試験3：月間の所定労働時間｜エクセル練習問題(2023-10-04)
エクセル試験2：所得税の計算｜エクセル練習問題(2023-10-04)
エクセル試験1：曜日別の平均客単価｜エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い｜ツイッター出題回答 (2023-09-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.変数宣言のDimとデータ型｜VBA入門
6.条件分岐（IF）｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。