Excelのソルバーは結構解析に使えますよという話は「一番起きそうな場所」「もっともらしいのは？」「Excelのソルバー使ったことありますか？」などで紹介してきました。このソルバーには使い分けが必要な事項があります。今日は、そこについて深堀したいと思います。資料をご覧ください。
資料はこちら → ソルバー
p.1 ｘ１、ｘ２の変数に対して特性値ｙが左の表の相関を示したとします。グラフに描いたものが真中上の図です。通常重回帰分析を実施します。分析ツールの「回帰分析」を用いれば、「y=a1 x1+a2 x2+b」の係数を簡単に算出可能です。実行結果は下方の赤枠の中です。
p.2 これをソルバーで計算したい場合、３種類の方法の中からどれを選定するかです。「GRG非線形」「シンプレックスLP」「エボリューショナリー」があります。滑らかな非線形、線形、滑らかでない非線形と説明が書かれていますが、何のことかわかりません。 GRG非線形を選択すると、オプションで選択する事項があります。 「マルチスタート」と「中央」を選択しておくと無難です。いくつも極小値が存在する場合、マルチスタートにしておかないと、最小値でない極小値で留まってしまう可能性が出て来ます。これを防ぐため、マルチスタート設定にします。前進と中央の微分係数を比較すると、中央の方がその点における接線により近いことが図から明白なので、中央を選択します。
p.3 GRG非線形を用いてp.1の重回帰分析を実施する手順を記載しました。番号順に進んでください。
p.4 実行結果が、分析ツールの重回帰解析結果と同等な結果が得られました。分析ツールを使わずにどうして面倒な手順を説明したかの訳は、三角関数や指数関数などへの応用が可能であるからです。
p.5 「シンプレックスLP」の応用事例です。線形問題に利用可能です。制約条件条件の下で目的関数の最適値を求める事例です。Z=3x+2yが最大となるｘとｙを求めます。 Excelの黄色のセルが求めたい値です。①からの手順に沿って実施ください。 今回は④シンプレックスLPを選定します。実行するとｘ＝16、ｙ＝８、Z=64と算出されました。 右図の赤線が接する箇所が最適解になります。
p.6 「エボリューショナリー」の応用事例として「魔法陣」を説明します。魔法陣は、縦、横及び斜めの和が全て等しい。１から順にマス目の数まで入れる。３×３の場合は、１～９までの数字が入ります。全ての値は異なり、正の整数です。一般的に、ｎ×ｎの魔法陣では、１行の数の和は、n・（ｎ２+1）/2と表されます。
p.7 左上のような表を作成します。「15」となっているセルには、（一行の和）－（矢印方向の数値の和）を計算する式が入っています。今回は３×３の魔法陣なので、変数n=９, n2=9， 一行の和はn(n2+1)/2=3(32+1)/2=15となります。絶対値差の合計は水色の枠内の合計値です。ソルバーで、この値が最小になる魔法陣の数値を求めます。制約条件は追加のボタンをクリックすると右上のようなポップアップが現れますので、魔法陣のすべてのセルを選び、n2＝９以下となるように設定して、追加ボタンをクリックします。次は、魔法陣全てのセルを選び、中央の等不等号ヨコのボタンをクリックし、「dif」を選ぶと「Alldiffrent」が表示されますので追加ボタンをクリックします。全てのセルの数字が異なるという意味です。「int」を選ぶと「整数」と表示されますので、追加します。同様に全てのセルが１以上を追加します。空欄のまま２回ぐらい追加をクリックします。次は「エボリューショナリー」を選択して「解決」をクリックすると左下のような実行結果が得られます。 ４×４の魔法陣も同様な手順で実行してみてください。
滑らかであると滑らかでないという意味は、数学でいうところの連続と連続でないという意味なのでしょうか？ 魔法陣の場合は確かに連続ではないので、滑らかでない範疇にはいるのですね。
いかがでしたか、ソルバーについて少し理解が深まりましたか？ また新たな知見が得られましたら、お伝えします。
今回のExcelファイルです → ソルバー