便利な表計算ソフトExcelを更に便利にしてくれるソルバーというアドインがあります。
使い方の根本が分かれば簡単、でも何をやっているのか分からないのでとっつきにくい！というちょっとした難点が。
私はネトゲのステータス計算や教育費のための試算をソルバーを使ってやってきましたが、結局のところソルバーはザックリと分かればなんとなく使えるようになります(たぶん)
難しいことは単純に計算式やシートが難しくなるだけで、基本やっていることは同じ。
じゃあ何をやっているの？という根本を、数学苦手人間が苦手なりに自分でも理解できるように記事に残したいと思います。
ソルバーとは
ソルバーとはMicrosoft OfficeのExcelに付いているアドインの一種で最適化分析ツールです。
と言ったところではい、もうわからん用語が出てきて「あ゛あ゛あ゛あ゛あ゛」となりかけている方、ちょっと落ち着いてください。
ソルバーを使おうと思った時点でエクセル自体はPCにインストールされていると思われますが、アドインって何？最適化分析ツールってなに？って感じですよね。
アドインとはソフトの機能を拡張する後付ソフトみたいなものです。
といっても、Excelにはこのソルバーアドインが標準装備されているので、どこそこからインストールするなどということはありませんし、追加でお金を取られることはありません。
そして最適化分析ツールという小難しい名前がまた理解を妨げるものだなぁと個人的にも思うのですが、簡単に言うと「何か色々条件がたくさんあって、どうやったら一番いい状態にしたらいいのか手計算じゃすごい時間がかかって面倒なんだけど！！！」っていう問題をパソコンの機能にお任せしてやってもらいましょ～というツールです。
これぐらい小難しい言葉を廃して説明できるように頑張りますが、私自身も未だに絶賛数学苦手人間ですので、そこのところよろしくお願いいたします。
ソルバーのアドインをオンにする方法
さて、ソルバーアドインはExcelに標準装備されていると言いましたが、実は通常の状態ではどこにいるのか見えません。
というのもアドインというのはあくまで「拡張パック」であって、グラフ作成や並び替え機能みたいに標準では使えない状態にされています。
コレを使えるようにするにはオフになっているソルバーアドインをオンにしなければなりません。
まずエクセルを開いたら
↓
オプション
↓
(「Excelのオプション」の)アドイン
↓
一番下のプルダウンを『Excelアドイン』にして設定をクリック
するとアドインという小さいウィンドウが出てきます ※これはエクセル2007なので見た目が少し違うかもしれません
ここでソルバーアドインにチェックボックスを入れてOKを押すと、データタブの一番右に『ソルバー』という文字が出てきます。
これでソルバーが使えるようになりました。
ソルバーの使い方
それでは使い方ですが、何をやっているのかゆるーいイメージで紹介します。
実はソルバーはこんな感じの計算をしています
問題にもよりますが
↓
「試しに計算してみよう」
↓
「合計はこんな個数になったぞ！」
と、まぁこんな感じ。
これなら人間がやった方がExcelに打ちこむよりもずっと速いぞ？って思いましたよね。
ただ、ソルバーの場合にはこの
●何個がいいかな←リンゴ１個、バナナ２個、みかん・・・ 次はリンゴ２個、バナナ１個みかん・・・・
と、言うようにとにかく試さなければいけないバリエーションが多すぎる場合に効果を発揮します。
イメージで言うとこんな感じ
何通りの計算なのかは問題にもよりますが、ソルバーは瞬き一つでだいたい数百回ぐらい計算してくれます。
コレを手計算でやって、計算間違いをせずに全ての結果を比較して一番いい答えを出す・・・・と言うのはどだい無理。
ということでソルバーは便利だっつー話なんですね。
というのを踏まえたうえで、問題を軽く解いてみます。
つるかめ算です
鶴と亀が何匹かずついますが匹数は分かりません。
鶴と亀の脚の合計の本数は30本です。
鶴と亀が同じ匹数のとき、それぞれは何匹ずついるでしょうか？
手計算でも普通にでるんですけど、これが鶴と亀とタラバガニとマダコとヒトデと・・・・ってなったら？大変だなぁ・・・と思いながらとりあえず簡単な２種類でやってみます。
シートはこんな感じでです
E列 脚の本数：足の数×匹数の計算式が入っています
E6 合計本数：E4とE5の合計の数式が入っています
準備はこれでOK
ソルバー自体にはこんな感じでそれぞれ入力しています
目的セルとは「こんな数値にしたいなぁ」というところ。
変化セルとは「何個がいいのか何度も何度も代入しなければならないところ」です。
なお、今回は指定値で30にしていますが、最大だったり最小だったり、いろいろな使い方があります。
で、最後に重要なのが制約条件。
ソルバーは生き物の気持ちなど知ったこっちゃないって感じなので、条件さえ合っているならばと鶴と亀を0.5匹とかいう数字で出してくる場合もあります。
そのために「鶴と亀は整数にしてね」という指示を書き込みます。
もう一つ、鶴と亀は同数という今回の問題の特徴を書きこまなければならないので、それも入力。
そして最後に一番下の「解決」を押すと勝手に数字を当てはめて計算してくれて
こんな風に計算してくれるというわけです。
ソルバーの制約条件って何？
ソルバーの使い方の最後に出てきました「制約条件」
実はこれが意外と厄介なところ。
制約条件が間違っているとちゃんと計算してくれなかったりします
制約条件には４種類あります。
●整数：int
●バイナリ：bin
●All different：dif
比較演算子：＜＝、＝、＝＞
以上・以下・イコールの設定ができます。
未満・より大きいは設定できないので、設定方法に少し工夫が必要な場合もあるかもしれません。
ちなみに制約条件の中身には
●数値を手入力
●参照セルにさらに計算を加える
等、バリエーションがかなり効きます。
例えば先ほどの鶴と亀の匹数を、鶴が亀の二倍ということであれば、制約条件の中に亀の匹数セル×2を入れることも可能というわけです。
整数：int
先ほどの鶴と亀の計算でも使った「数値を整数にする」というものです。
個数を求める際などに良く使われます。
バイナリ：bin
0か1かどちらかの数字にするように制限を掛けることができます。
例えば「来る/来ない」やら「食べる/食べない」やら、計算の途中に入れることで個数を計算したり組み合わせを計算したりすることができます。
All different：dif
オールディファレント、つまり選択したセルに全て違う整数を当てはめるための制約条件です。
山田・佐々木・田中君の三人に、1,2,3のどれかの数字を当てはめる・・・なんてときに使います。
コレは組み分けをする際に使うことが多いようです。
ソルバーで組み合わせを調べる方法
組み合わせとは、どれとどれを組み合わせたら希望の状態になるのかな？という問題です。
考えるより手を動かした方が速いので問題ですが
マダイ：645円
いくら：966円
マアジ：269円
ヤリイカ：422円
サーモン：555円
ホタテ：748円
イワシ：86円
シシャモ：167円
マグロ：916円
ブリ：807円
どれとどれを買ってきたらいいのかな？って話です。
こんな風に入力していて、個数のセルは手入力、合計金額は=SUMPRODUC関数を使っています。
目的セル・指定値3000・変化セルを選び、制約条件にはバイナリ(買う→1にする、買わない→０にする)を選びます
これで解決をすると
こんな風にちゃんとどれを買えばいいのか答えを返してくれるわけです(ただしこの問題は解が二つある形になっているので、もしよければ詳しいページの方を見ながらやってみてください)
ソルバーで最小二乗法をする方法
学生の時にやってひーひー言いました最小二乗法です。
ぶっちゃけると、ものすごく難しい数式でもない限りはエクセルならグラフの標準機能「近似曲線の追加」を使った方が速いです。
が！「近似曲線のオプションにはない式の近似線を出さなきゃいけない・・・」とか「大学の講義でエクセルでR＾2値まで全部出さなきゃいけない・・・」とか言う場合に役に立てるんじゃないかと思います(主に後者？ｗ)
まずはExcelにデータを入力
架空のクラス30人の数Aと数Ⅰのテストの点数です。
C2セル：近似直線の傾きのパラメータa この時点では初期値として0を入力
C3セル：近似曲線の切片のパラメータb この時点では初期値として０を入力
B列・座席番号：この数値には意味はありません。
C列・数学Aの点数(実測X)：それぞれの数Aの点数をXと置きます。つまり横軸
D列・数学Ⅰの点数(実測Y)：それぞれの数Ⅰの点数をYと置きます。つまり縦軸
E列・推定Y：数式が入力されています【例：E7=$C$2*C7+$C$3】(C7はそれぞれの点数を代入していく)
傾きa、切片b、実測Xを使って計算した推定のYの値です(初期値が0なので今は全て０)。aとbをこの後推定するので、計算された値は推定のYとなります。
F列・残差平方：数式が入力されています。【例：F7=(D7-E7)^2】
実測のYと推定のYの差の二乗の値です(残差＝差、平方＝二乗)
G列・(実測Y-実測Yの平均)＾2：数式が入力されています【例：G7=(D7-AVERAGE($D$7:$D$36))^2】
R^2値を計算するための一時計算用の列です。
G2セル・残差平方和：数式が入力されています【G2=SUM(F7:F36)】
G3セル・R^2値：R^2値決定係数を計算するためのセルで、数式が入力されています【G3=1-SUM(F7:F36)/SUM(G7:G36)】
コレをソルバーにお願いします
目標値：最小値
変更セルの変更：C2とC3・パラメータのセル
【重要！】「制約のない変数を非負数にする」のチェックボックスを外す
そしてこれで解決を押すと・・・
こんな風に計算をしてくれるというわけです。
こんな七面倒くさいことをして何が良いかというと、数式の部分がいくら複雑になろうとも自分で計算することが出来るようになります。
というか、この状態を知っていれば、後は数式(E列の推定のYの部分)とパラメータの数さえ気を付ければ何でも計算が出来てしまう・・・・・それってすごくないですか。
凄いと思ったら多分統計系の素質があるんじゃないかと思います
最後に
ソルバーは数式とかパラメータとか分析とか、小難しい言葉がたくさん並んでいるので一見とっつきにくいですが、簡単に言えば「数字入れる、計算する、計算結果を比較する」を何度も何度もデジタルに反復作業をしてくれているだけです。
ただ、その反復作業のために人間側がいくつか指示を出しておかないとExcelだけだと上手く判断することができない(鶴や亀が1.5匹になっちゃったりする)
そう考えられれば、もう少しソルバーの使い勝手も良くなるかなと思います。
スポンサーリンク
コメント