トピックス プログラム、シミュレーション

用途に応じて使い分ける

投稿日:

Excelのソルバーは結構解析に使えますよという話は「一番起きそうな場所」「もっともらしいのは?」「Excelのソルバー使ったことありますか?」などで紹介してきました。このソルバーには使い分けが必要な事項があります。今日は、そこについて深堀したいと思います。資料をご覧ください。

資料はこちら → ソルバー

p.1 x1、x2の変数に対して特性値yが左の表の相関を示したとします。グラフに描いたものが真中上の図です。通常重回帰分析を実施します。分析ツールの「回帰分析」を用いれば、「y=a1 x1+a2 x2+b」の係数簡単に算出可能です。実行結果は下方のの中です。

p.2 これをソルバーで計算したい場合、3種類の方法の中からどれを選定するかです。「GRG非線形」「シンプレックスLP」「エボリューショナリー」があります。滑らかな非線形、線形、滑らかでない非線形と説明が書かれていますが、何のことかわかりません。 GRG非線形を選択すると、オプションで選択する事項があります。 「マルチスタート」と「中央」を選択しておくと無難です。いくつも極小値が存在する場合、マルチスタートにしておかないと、最小値でない極小値で留まってしまう可能性が出て来ます。これを防ぐため、マルチスタート設定にします。前進中央微分係数を比較すると、中央の方がその点における接線により近いことが図から明白なので、中央を選択します。

p.3 GRG非線形を用いてp.1の重回帰分析を実施する手順を記載しました。番号順に進んでください。

p.4 実行結果が、分析ツールの重回帰解析結果と同等な結果が得られました。分析ツールを使わずにどうして面倒な手順を説明したかの訳は、三角関数や指数関数などへの応用が可能であるからです。

p.5 「シンプレックスLP」の応用事例です。線形問題に利用可能です。制約条件条件の下で目的関数の最適値を求める事例です。Z=3x+2y最大となるを求めます。 Excel黄色のセルが求めたい値です。①からの手順に沿って実施ください。 今回は④シンプレックスLPを選定します。実行するとx=16、y=8、Z=64と算出されました。 右図の線が接する箇所が最適解になります。

p.6 「エボリューショナリー」の応用事例として「魔法陣」を説明します。魔法陣は、縦、横及び斜めの和が全て等しいから順にマス目の数まで入れる。3×3の場合は、1~9までの数字が入ります。全ての値は異なり、正の整数です。一般的に、n×nの魔法陣では、1行の数の和は、n・(n+1)/2と表されます。

p.7 左上のような表を作成します。「15」となっているセルには、(一行の和)-(矢印方向の数値の和)を計算する式が入っています。今回は3×3の魔法陣なので、変数n=9, n2=9, 一行の和はn(n2+1)/2=3(32+1)/2=15となります。絶対値差の合計水色の枠内の合計値です。ソルバーで、この値が最小になる魔法陣の数値を求めます。制約条件は追加のボタンをクリックすると右上のようなポップアップが現れますので、魔法陣のすべてのセルを選びn2=9以下となるように設定して、追加ボタンをクリックします。次は、魔法陣全てのセルを選び、中央の等不等号ヨコのボタンをクリックし、「dif」を選ぶと「Alldiffrent」が表示されますので追加ボタンをクリックします。全てのセルの数字が異なるという意味です。「int」を選ぶと「整数」と表示されますので、追加します。同様に全てのセル1以上を追加します。空欄のまま2回ぐらい追加をクリックします。次は「エボリューショナリー」を選択して「解決」をクリックすると左下のような実行結果が得られます。 4×4の魔法陣も同様な手順で実行してみてください。

滑らかである滑らかでないという意味は、数学でいうところの連続と連続でないという意味なのでしょうか? 魔法陣の場合は確かに連続ではないので、滑らかでない範疇にはいるのですね。

いかがでしたか、ソルバーについて少し理解が深まりましたか? また新たな知見が得られましたら、お伝えします。

今回のExcelファイルです → ソルバー

-トピックス, プログラム、シミュレーション

Copyright© 進化するガラクタ , 2020 All Rights Reserved Powered by STINGER.