「解決策を探す」は、指定された制限に基づいて、問題に対する最適な解決策を選択できる Excel アドインです。 この機能により、従業員のスケジュール、コストまたは投資の分配が可能になります。 この機能がどのように機能するかを知っておくと、時間と労力を節約できます。
解決策の検索とは
Excel の他のさまざまなオプションと組み合わせて、あまり一般的ではありませんが、非常に必要な機能である「ソリューションの検索」があります。 それを見つけるのは簡単ではありませんが、それを知り、それを使用することは、多くの問題の解決に役立ちます。 このオプションはデータを処理し、許可されたものから最適なソリューションを提供します。 この記事では、ソリューションの検索がどのように直接機能するかについて説明します。
「ソリューションの検索」機能をオンにする方法
有効性にもかかわらず、問題のオプションはツールバーやコンテキスト メニューの目立つ場所にはありません。 Excel で作業するほとんどのユーザーは、その存在を認識していません。 デフォルトでは、この機能は無効になっています。表示するには、次のアクションを実行します。
- 適切な名前をクリックして「ファイル」を開きます。
- 「設定」セクションをクリックします。
- 次に、「アドオン」サブセクションを選択します。 プログラムのすべてのアドオンがここに表示され、「管理」という碑文が下に表示されます。 その右側に、「Excel アドイン」を選択するポップアップ メニューがあります。 次に、「移動」をクリックします。
1 - 追加のウィンドウ「アドイン」がモニターに表示されます。 目的の機能の横にあるチェックボックスをオンにして、[OK] をクリックします。
- 目的の関数が [データ] セクションの右側のリボンに表示されます。
モデルについて
この情報は、「最適化モデル」の概念に慣れてきたばかりの人にとって非常に役立ちます。 「ソリューションの検索」を使用する前に、モデルの構築方法に関する資料を検討することをお勧めします。
- 検討中のオプションにより、投資に資金を割り当て、施設に積み込み、商品を供給し、または最善の解決策を見つける必要があるその他の行動を行うための最良の方法を特定することが可能になります。
- このような状況での「最適な方法」とは、収入を増やす、コストを削減する、品質を向上させるなどを意味します。
典型的な最適化タスク:
- リリースされた商品の販売による利益が最大になる生産計画の決定。
- 輸送コストが最小化される輸送マップの決定。
- 生産コストが削減されるように、さまざまなタイプの作業用に複数のマシンの分散を検索します。
- 作業完了までの最短時間の決定。
重要な！ タスクを形式化するには、サブジェクト領域の主要なパラメーターを反映するモデルを作成する必要があります。 Excel では、モデルは変数を使用する一連の数式です。 考慮されるオプションは、目的関数が指定された値より大きい (小さい) または等しいという指標を探します。
準備段階
リボンに関数を配置する前に、オプションがどのように機能するかを理解する必要があります。 たとえば、表に示されている商品の販売に関する情報があります。 タスクは、各アイテムに 4.5 万ルーブルの割引を割り当てることです。 パラメータは、ターゲットと呼ばれるセル内に表示されます。 それに基づいて、他のパラメータが計算されます。
私たちの仕事は、さまざまな製品の販売額に掛けられる割引を計算することです。 これらの 2 つの要素は、次のように記述された式によって接続されます。 =D13*$G$2. D13 には実装の合計数量が書き込まれ、$G$2 は目的の要素のアドレスです。
機能の使用と設定
数式の準備ができたら、関数自体を直接使用する必要があります。
- 「データ」セクションに切り替えて、「ソリューションを検索」をクリックする必要があります。
- 「オプション」が開き、必要な設定が設定されます。 「目的関数の最適化:」行で、割引の合計が表示されるセルを指定する必要があります。 座標を自分で指定することも、ドキュメントから選択することもできます。
- 次に、他のパラメータの設定に移動する必要があります。 「To:」セクションでは、上限と下限、または正確な数を設定できます。
- 次に、「変数の値を変更する:」フィールドに入力します。 ここでは、特定の値を含む目的のセルのデータが入力されます。 座標を個別に登録するか、ドキュメント内の対応するセルをクリックします。
- 次に、「制限に従って:」タブを編集し、適用されるデータの制限を設定します。 たとえば、小数や負の数は除外されます。
- その後、計算に制限を追加できるウィンドウが開きます。 最初の行には、セルまたは範囲全体の座標が含まれています。 タスクの条件に従って、目的のセルのデータが表示され、割引インジケーターが表示されます。 次に、比較符号が決定されます。 最終値がマイナス記号にならないように「以上」に設定されています。 この場合、3 行目に設定されている「Limit」は 0 です。 「追加」で制限を設けることも可能です。 次の手順は同じです。
- 上記の手順が完了すると、設定された制限が最大行に表示されます。 リストは大きくなる可能性があり、計算の複雑さに依存しますが、特定の状況では、1 つの条件で十分です。
- さらに、その他の高度な設定を選択することもできます。 右下には、これを可能にするオプション「オプション」があります。
- 設定では、「限界精度」と「解限界」を設定できます。 私たちの状況では、これらのオプションを使用する必要はありません。
- 設定が完了すると、機能自体が起動します - 「解決策を探す」をクリックします。
- プログラムが必要な計算を実行し、必要なセルで最終的な計算を発行した後。 次に、結果を含むウィンドウが開き、結果が保存/キャンセルされるか、新しいパラメーターに従って検索パラメーターが構成されます。 データが要件を満たすと、見つかった解が保存されます。 あらかじめ「ソリューション検索オプションダイアログボックスに戻る」にチェックを入れておくと、機能設定のウィンドウが開きます。
- 計算が間違っている可能性や、他の指標を取得するために初期データを変更する必要がある可能性があります。 このような状況では、設定ウィンドウを再度開き、情報を再確認する必要があります。
- データが正確な場合は、別の方法を使用できます。 これらの目的のために、現在のオプションをクリックして、表示されるリストから最適な方法を選択する必要があります。
- 非線形問題の一般化された勾配を使用して解を見つける。 デフォルトでは、このオプションが使用されますが、他のオプションを使用することもできます。
- シンプレックス法に基づいて線形問題の解を見つけます。
- 進化的探索を使用してタスクを完了します。
注意！ 上記のオプションがタスクに対処できなかった場合は、設定のデータをもう一度確認する必要があります。これは、このようなタスクの主な間違いであることが多いためです。
- 希望の割引を受け取ったら、それを適用して各アイテムの割引額を計算する必要があります。 この目的のために、列「割引額」の最初の要素が強調表示され、式が書き込まれます «=D2*$G$2» し、「Enter」を押します。 数式が隣接する行に拡張されたときに G2 が変化しないように、ドル記号が下に置かれます。
- これで初期アイテムの割引額が取得されます。 次に、カーソルをセルの隅に移動する必要があります。カーソルが「プラス」になったら、LMB を押すと、数式が必要な行に引き伸ばされます。
- その後、テーブルは最終的に準備が整います。
検索オプションの読み込み/保存
このオプションは、さまざまな制約オプションを適用するときに役立ちます。
- Solution Finder Options メニューで、Load/Save をクリックします。
- モデル領域の範囲を入力し、[保存] または [読み込み] をクリックします。
モデルを保存するとき、最適化モデルが配置される空の列の 1 つのセルへの参照が入力されます。 モデルのロード中に、最適化モデルを含む範囲全体への参照が入力されます。
重要な！ ソリューション オプション メニューの最後の設定を保存するために、ワークブックが保存されます。 その中の各シートには、独自の Solver アドイン オプションがあります。 さらに、個々のタスクを保存するために「読み込みまたは保存」ボタンをクリックすることで、シートに複数のタスクを設定することができます。
ソルバーの簡単な使用例
コンテナの質量が最大になるように、コンテナにコンテナを積み込む必要があります。 タンクの容積は 32 立方メートルです。 メートル。 充填された箱の重量は 20 kg、容積は 0,15 立方メートルです。 メートル。 ボックス – 80 kg および 0,5 cu. メートル。 コンテナの総数が110個以上である必要があります。 データは次のように編成されます。
モデル変数は緑色でマークされています。 目的関数は赤で強調表示されます。 制限: コンテナの最小数 (110 以上) および重量 (=SUMPRODUCT(B8:C8,B6:C6) – コンテナ内の総風袋重量。
類推すると、総量を次のように考えます。 =SUMPRODUCT(B7:C7,B8:C8). このような式は、コンテナの総量に制限を設定するために必要です。 次に、「ソリューションの検索」を介して、変数、式、およびインジケーター自体を持つ要素へのリンク (または特定のセルへのリンク) が入力されます。 もちろん、コンテナーの数は整数です (これも制限です)。 「解決策を見つける」を押すと、その結果、総質量が最大になり、すべての制限が考慮されたときに、そのような数のコンテナが見つかります。
ソリューションの検索に失敗しました
このような通知は、問題の関数が各制約を満たす変数スコアの組み合わせを見つけられなかった場合に表示されます。 シンプレックス法を使用する場合、解がない可能性は十分にあります。
非線形問題を解く方法が使用される場合、変数の初期指標から開始するすべての場合において、これは可能な解がそのようなパラメータからかけ離れていることを示します。 変数の他の初期インジケーターを使用して関数を実行すると、おそらく解決策があります。
たとえば、非線形メソッドを使用すると、テーブルの要素に変数が入力されず、関数は解を見つけられませんでした。 これは、解決策がないという意味ではありません。 ここで、特定の評価の結果を考慮して、受け取ったデータに近い変数を持つ他のデータが要素に入力されます。
どのような状況でも、最初にモデルに制約の競合がないかどうかを調べる必要があります。 多くの場合、これは比率または制限指標の不適切な選択と相互に関連しています。
上記の例では、最大容積インジケータは 16 立方メートルです。 32 ではなく m。これは、このような制限が最小座席数の指標と矛盾するためです。これは、16,5 立方メートルの数に対応するためです。 メートル。
まとめ
これに基づいて、Excel の「ソリューションの検索」オプションは、通常の方法では解決が困難または不可能な特定の問題を解決するのに役立ちます。 この方法を適用する際の難点は、最初はこのオプションが非表示になっていることです。そのため、ほとんどのユーザーはその存在に気づいていません。 さらに、この関数を習得して使用するのは非常に困難ですが、適切な調査を行うことで、大きな利点がもたらされ、計算が容易になります。