データが完全な場合、Excel は強力なツールです。しかし、未知の変数を解くことができれば便利ではないでしょうか?
ゴールシークとソルバー アドインを使用すれば、それが可能です。その方法をご紹介します。ゴールシークを使用して単一のセルを解く方法、またはソルバーを使用してより複雑な方程式を解く方法に関する完全なガイドをお読みください。
Excel でゴールシークを使用する方法
ゴールシークはすでに Excel に組み込まれています。データタブのデータ分析メニューにあります:
この例では、非常に単純な数値のセットを使用します。四半期の売上高と年間目標があります。ゴールシークを使用して、目標を達成するために第 4 四半期にどのくらいの数値が必要かを把握できます。
ご覧のとおり、現在の売上総数は 114,706 ユニットです。年末までに 250,000 ユニットを販売したい場合、第 4 四半期に何ユニット販売する必要がありますか? Excel のゴールシークが教えてくれます。
ゴールシークを使用する方法をステップバイステップでご紹介します:
- データ > データ分析 > ゴールシークをクリックします。ゴールシーク ウィンドウがポップアップ表示されます。
- 方程式の「等しい」部分を設定するセルフィールドに入力します。これは、Excel が最適化しようとする数値です。この例では、セルの A5 の売上高の累計です。
- 値を設定フィールドに目標値を入力します。合計 250,000 ユニットの販売を目指しているため、このフィールドに「250,000」を入力します。
- Excel に、変更するセルフィールドでどの変数を解くかを伝えます。第 4 四半期の売上高がどのようになる必要があるかを確認します。そのため、Excel にセルの D2 を解くように伝えます。実行する準備ができたら、次のようになります:
- OKをクリックして目標を解きます。適切な場合は、OKをクリックするだけです。ゴールシークで解が見つかった場合、Excel がお知らせします。
- もう一度OKをクリックすると、変更するセルで選択したセルに方程式を解く値が表示されます。
この例では、解は 135,294 ユニットです。もちろん、累計から年間目標を引くだけで見つけることができました。しかし、ゴールシークはすでにデータが入力されているセルでも使用できます。そして、それがより便利です。
Excel は以前のデータを上書きすることに注意してください。データのコピーでゴールシークを実行することをお勧めします。また、コピーしたデータにゴールシークを使用して生成されたというメモを残すのもよいでしょう。現在の正確なデータと混同したくありません。
ゴールシークは最も便利な Excel 関数の 1 つですが、それほど印象的ではありません。一度に 1 つのセルでのみ使用できます。複数のセルで Excel のゴールシークを使用したい場合は、はるかに強力なツールが必要です。幸いなことに、そのようなツールが Excel に付属しています。ソルバー アドインを見てみましょう。
Excel のソルバーは何をしますか?
簡単に言えば、ソルバーはゴールシークの多変数バージョンのようなものです。複数のセルで Excel のゴールシークを使用する方法を考えているなら、これがその方法です。1 つの目標変数を取り、目的の答えが得られるまで他の多くの変数を調整します。
数値の最大値、数値の最小値、または正確な数値を解くことができます。また、制約条件内で動作するため、1 つの変数を変更できない場合や、指定された範囲内でのみ変更できる場合、ソルバーはそれを考慮します。
これは、Excel で複数の未知の変数を解くのに最適な方法です。しかし、それを見つけて使用するのは簡単ではありません。ソルバー アドインの読み込みを見てから、現在の Microsoft 365 バージョンの Excel でソルバーを使用する方法に飛び込みましょう。
ソルバー アドインを読み込む方法
Excel にはデフォルトでソルバーはありません。これはアドインなので、最初に読み込む必要があります。幸いなことに、それはすでにあなたのコンピュータにあります。
ファイル > ( その他... >) オプション > アドインに進みます。次に、管理: Excel アドインの横にある移動をクリックします。このドロップダウンに「Excel アドイン」以外のものが表示されている場合は、変更する必要があります:
結果ウィンドウには、いくつかのオプションが表示されます。ソルバー アドインの横のボックスがオンになっていることを確認し、OKをクリックします。
データタブの分析グループにソルバーボタンが表示されます:
データ分析ツールパックをすでに使用している場合は、データ分析ボタンが表示されます。そうでない場合は、ソルバーのみが表示されます。アドインを読み込んだので、その使用方法を見てみましょう。
Excel でソルバーを使用する方法
ソルバーのアクションには、目的、変数セル、制約の 3 つの部分があります。各手順について説明します。
- データ > ソルバーをクリックします。以下にソルバー パラメーター ウィンドウが表示されます。(ソルバー ボタンが表示されない場合は、前のセクションでソルバー アドインを読み込む方法を参照してください。)
- セルの目的を設定し、Excel に目標を伝えます。目的はソルバー ウィンドウの上部にあり、目的セルと最大化、最小化、または特定の値の選択肢の 2 つの部分で構成されています。
最大を選択すると、Excel は目的セルで可能な限り最大の数字を取得するように変数を調整します。最小は逆です。ソルバーは目的の数を最小化します。値では、ソルバーが探す特定の数字を指定できます。 - Excel が変更できる変数セルを選択します。変数セルは変更する変数セルフィールドで設定されます。フィールドの横にある矢印をクリックし、クリックしてドラッグして、ソルバーが操作するセルを選択します。これらはすべて変更できるセルです。セルを変更したくない場合は、選択しないでください。
- 複数の変数または個々の変数に制約を設定します。最後に、制約について説明します。これが、ソルバーが本当に強力なところです。変数セルのいずれかを任意の数字に変更するのではなく、満たす必要がある制約を指定できます。詳細については、以下の制約を設定する方法に関するセクションを参照してください。
- この情報がすべて揃ったら、ソルバーをクリックして回答を取得します。Excel は新しい変数を追加してデータを更新します (これが、最初にデータのコピーを作成することをお勧めする理由です)。
レポートを生成することもできます。これについては、以下のソルバーの例で簡単に説明します。
ソルバーで制約を設定する方法
1 つの変数が 200 より大きくなければならないことを Excel に伝えることができます。さまざまな変数値を試すとき、Excel はその特定の変数で 201 を下回ることはありません。
制約を追加するには、制約リストの横にある追加ボタンをクリックします。新しいウィンドウが表示されます。セル参照フィールドで制約するセル (またはセル) を選択し、演算子を選択します。
使用可能な演算子は次のとおりです:
- <= (未満または等しい)
- = (等しい)
- => (以上または等しい)
- int (整数でなければならない)
- bin (1 または 0 でなければならない)
- AllDifferent
AllDifferentは少しわかりにくいものです。セル参照で選択した範囲内のすべてのセルが異なる数字でなければならないことを指定します。しかし、それらは 1 からセルの数までの間でなければならないことも指定します。
したがって、3 つのセルがある場合、1、2、3 の数字になります (必ずしもその順序であるとは限りません)。最後に、制約の値を追加します。
複数のセルをセル参照で選択できることを覚えておくことが重要です。たとえば、6 つの変数の値が 10 を超えるようにしたい場合、それらすべてを選択して、11 以上でなければならないことをソルバーに伝えることができます。各セルに制約を追加する必要はありません。
また、メインのソルバー ウィンドウのチェックボックスを使用して、制約を指定しなかったすべての値が非負であることを確認することもできます。変数をマイナスにしたい場合は、このチェックボックスをオフにします。
ソルバーの例
これがどのように機能するかを確認するために、ソルバー アドインを使用して簡単な計算を行います。以下は、開始するデータです:
ここでは、それぞれ異なる料金を支払う 5 つの異なる仕事があります。また、特定の週に理論上の労働者がそれぞれの仕事で働いた時間も示しています。
ソルバー アドインを使用して、特定の変数を一定の制約内に収めながら、合計給与を最大化する方法を見つけることができます。使用する制約は次のとおりです:
- どの仕事も4 時間を下回ることはできません。
- 仕事 4 は12 時間以上でなければなりません。
- 仕事 5 は11 時間未満でなければなりません。
- 総労働時間は40 時間でなければなりません。
ソルバーを使用する前に、この制約をこのように書き出すと役立ちます。ソルバーで設定する方法は次のとおりです:
まず、テーブルのコピーを作成したことに注意してください。これにより、現在の稼働時間を含む元のテーブルが上書きされません。
そして 2 つ目に、より大きく、より小さく制約の値が、私が上で述べたものより1 高いか低いかであることに注目してください。より大きく、より小さくオプションがないからです。より大きくまたは等しく、より小さくまたは等しくしかありません。
ソルバーをクリックして、何が起こるか見てみましょう。
ソルバーは解を見つけました! 上のウィンドウの左側で見られるように、収益は 152 ドル増加しました。そして、すべての制約が満たされました。
新しい値を維持するには、ソルバーの解を保持がオンになっていることを確認し、OKをクリックします。詳細情報が必要な場合は、ウィンドウの右側にあるレポートを選択できます。必要なレポートをすべて選択し、Excel にアウトラインを作成するかを指示し (推奨します)、OKをクリックします。
レポートはワークブックの新しいシートに生成され、ソルバー アドインが答えを得るために実行したプロセスに関する情報が提供されます。
この場合、レポートはあまりエキサイティングではなく、そこには多くの興味深い情報は含まれていません。しかし、より複雑なソルバー方程式を実行すると、これらの新しいワークシートに役立つレポート情報が見つかる場合があります。詳細情報を確認するには、レポートの横にある+ボタンをクリックするだけです:
ソルバーの詳細オプション
統計についてあまり詳しくない場合は、ソルバーの詳細オプションを無視して、そのまま実行できます。しかし、大規模で複雑な計算を実行している場合は、それらを確認する必要があります。
最も明白なのは、解法です:
GRG 非線形、Simplex LP、進化の 3 つから選択できます。Excel は、それぞれを使用する必要がある場合について簡単な説明を提供します。より良い説明には、Excel の統計と回帰に関する知識が必要です。
追加の設定を調整するには、オプションボタンをクリックします。整数最適性について Excel に指示し、計算時間の制約を設定し (大量のデータセットに役立ちます)、GRG と進化の解法で計算する方法を調整できます。
繰り返しになりますが、これが何を意味するのかわからない場合は、心配しないでください。使用する解法について詳しく知りたい場合は、Engineer Excel に、それを説明した優れた記事があります。最大の精度が必要な場合は、進化が適している可能性があります。時間がかかることに注意してください。
ゴールシークとソルバー: Excel を次のレベルへ
Excel で未知の変数を解くことの基礎がわかったら、スプレッドシート計算のまったく新しい世界が開かれます。ゴールシークは、一部の計算をより高速に行うことで時間を節約するのに役立ち、ソルバーは Excel の計算能力に大きなパワーを追加します。
それらに慣れるだけです。使用すればするほど、より便利になります。
コメントする