\r\n\r\n
計算に必要なデータがすべて揃っていれば、Excelはとても有能です。
でも、未知の変数を解決してくれたらいいと思いませんか?
プラグインのGoal SeekとSolverを使えば、それが可能になります。その方法をお教えします。Goal Seekを使って個々のセルを解く方法と、Solverを使ってより複雑な方程式を解く方法の完全ガイドをお読みください。
ターゲット検索は、Excelの「もしも分析」メニューの「データ」タブに組み込まれています。
この例では、非常に単純な数字のセットを使用します。3四半期分の売上と年間目標があります。Goal Seekを使って、第4四半期に目標を達成するために必要な数字を計算することができるのです。
ご覧の通り、これまでの累計販売台数は114,706台です。年末までに25万台売りたいなら、第4四半期に何台売る必要があるか?エクセルの目標検索が教えてくれる。
ここでは、ターゲット検索の使い方を順を追って説明します。
この例では、135,294個が解答となります。もちろん、年間目標からランニング・トータルを差し引けばわかることです。しかし、ターゲットサーチは、すでにデータがあるユニットに対しても使用することができます。これはさらに便利です。
Excelは以前のデータを上書きしてしまいますので、ご注意ください。データのコピーでGoal Seekを実行するのは良いアイデアです。また、コピーしたデータにはGoal Seekで生成したものであることをメモしておくとよいでしょう。現在の正確なデータと混同しないようにしたいものです。
というわけで、Goal Seekは便利なExcelの機能ですが、それほど印象に残るものではありません。もっと面白いツール、Solverアドインを見てみよう。
要するに、ソルバーはゴールシークの多変量版のようなものだ。1つのターゲット変数を受け入れ、望む答えが得られるまで、他の多くの変数を調整するのです。
数値の最大値、最小値、正確な数値を求めることができる。
制約の中で動作するため、変数を変更できない、または指定された範囲内でしか変更できない場合、ソルバーはこれを考慮します。
これは、Excelで複数の未知の変数を解くのに最適な方法です。しかし、これを見つけ、使うのは簡単ではありません。
ソルバーの外部プログラムの読み込みについて見てから、Excel 2016でのソルバーの使い方にジャンプしましょう。
Excelのデフォルトでは、ソルバーはありません。外部プログラムなので、他の強力なExcel関数と同様、最初にロードする必要があります。幸いなことに、それはすでにあなたのコンピュータの中にあるのです。
次に、[管理: Excelアドイン]の[次へ]をクリックします。
このドロップダウンリストに「Excelアドイン」以外が表示されている場合は、変更する必要があります。
結果ウィンドウにいくつかのオプションが表示されますので、「Solver add-in」の隣のボックスがチェックされていることを確認して、「OK」をクリックします。
これで、「データ」タブの「解析」グループに「ソルバー」ボタンが表示されるようになりました。
すでにデータ解析ツールキットを使用している場合は、データ解析ボタンが表示されます。そうでない場合は、ソルバーが自動的に表示されます。
さて、外部プログラムを読み込んだところで、その使い方をみてみましょう。
ソルバーの操作には、ターゲット、変数セル、制約の3つの部分があります。
また、レポートを作成することもできますが、これは後述のソルバーの例で簡単に説明します。
ある変数が200より大きくなければならないことをExcelに伝えることができます。別の変数値を試すと、Excelはその特定の変数を使用しません。
制約を追加するには、制約の一覧の横にある「追加」ボタンをクリックします。新しいウィンドウが表示されます。セル参照フィールドで拘束するセルを選択し、オペレータを選択します。
以下のオペレーターが使用可能です。
AllDifferentはちょっとわかりにくいですね。これは、セル参照用に選択した領域内の各セルが異なる番号でなければならないことを指定するものです。しかし、1からセル数までの間でなければならないことも指定されている。つまり、3つのセルがあれば、1、2、3という数字が得られる(ただし、この順番である必要はない)
最後に、制約に値を追加します。
ここで覚えておきたいのは、セル参照として複数のセルを選択できることです。例えば、6つの変数の値を10より大きくしたい場合、それらをすべて選択し、11以上でなければならないとソルバーに伝えることができる。 各セルに制約を加える必要はない。
また、ソルバーのメインウィンドウにあるチェックボックスを使って、制約が指定されていない値がすべて非負であることを確認することができます。変数がマイナスになる場合はチェックを外してください。
この仕組みを理解するために、Solverプラグインを使って簡単な計算をしてみましょう。
この中で、5つの仕事があり、それぞれ給料が違う。また、理論上の労働者が1週間にこれらの仕事を行う時間数もわかっています。ソルバープラグインを使って、ある変数を一定の制約内に収めながら、総支給額を最大化する方法を見つけることができます。
使用する制約条件は以下の通りです。
ソルバーを使う前に、このように制約を書き出すと便利かもしれません。
以下は、Solverでの設定方法です。
まず、現在の労働時間を含む元のテーブルを上書きしないように、テーブルのコピーを作成したことに注意してください。
次に、「より大きい」「より小さい」制約の値が、先に述べた値より大きいか小さいかに注意してください。それは、「より大きい」「より小さい」というオプションがないからです。より大きいか等しいか、より小さいか等しいかだけである。
解答」をクリックして、どうなるか見てみましょう。
解決策が見つかったのです!ウィンドウの左側にあるように、収入が130ドル増えました。すべての制約が満たされました。
新しい値を保持するために、「ソルバー解を保持する」がチェックされていることを確認し、「OK」をクリックします。
ただし、より詳細な情報が必要な場合は、ウィンドウの右側から「レポート」を選択することができます。必要なレポートをすべて選択し、サマリーを作成するかどうかをExcelに指示し(私はこれを推奨します)、「OK」をクリックします。
レポートはワークブックの新しいワークシートに作成され、Solverプラグインが答えを得るプロセスに関する情報を提供します。
私たちの場合、レポートが盛り上がらないし、面白い情報もあまりない。しかし、より複雑な解答式を実行する場合、これらの新しいワークシートに有用なレポート情報を見つけることができるかもしれません。各レポートの横にある「+」ボタンをクリックするだけで、より詳細な情報を得ることができます。
統計学に詳しくない人は、ソルバーの高度なオプションを無視して、そのまま実行することができます。でも、複雑な計算をたくさんするのであれば、調べたほうがいいかもしれません。
最もわかりやすい解決策は
GRG非線形、simplex LP、進化的アルゴリズムのいずれかを選択することができます。より良い説明をするためには、統計学や回帰の知識が必要です。
その他の設定は、「オプション」ボタンをクリックするだけです。Excelに整数最適化を指示したり、計算時間の制約を設定したり(大規模なデータセットに有効)、GRGや進化型ソルバー手法の計算方法を調整したりすることができます。
繰り返しになりますが、この意味がわからなくてもご安心ください。どの解法を使うかについては、エンジニアエクセルの記事に詳しいので、そちらを参照してください。最大限の精度を求めるなら、エボリューションがいいかもしれません。時間がかかるので注意が必要です。
Excelで未知の変数を解く基本を理解したことで、スプレッドシートによる計算の新しい世界が開かれます。
ゴールシークは計算を高速化することで時間短縮に貢献し、ソルバーはExcelの計算能力を飛躍的に向上させることができます。
使いこなせば使いこなすほど、便利になっていきます。
スプレッドシートにゴールシークやソリューションを使っていますか?その他、ベストアンサーを得るためのヒントがあれば教えてください。以下のコメントであなたの考えを共有してください。