問題の定式化
あなたが働いている会社に XNUMX つの倉庫があり、そこから商品がモスクワ中に点在する XNUMX つの店舗に送られるとします。
各店舗は、私たちが知っている一定量の商品を販売できます。 各倉庫には限られた容量があります。 タスクは、総輸送コストを最小限に抑えるために、商品をどの倉庫からどの店舗に配送するかを合理的に選択することです。
最適化を開始する前に、Excel シート (状況を説明する数学的モデル) に簡単なテーブルをコンパイルする必要があります。
次のことが理解されています。
- 薄い黄色の表 (C4:G6) は、各倉庫から各店舗への XNUMX つのアイテムの配送コストを示しています。
- 紫色のセル (C15:G14) は、各店舗が販売するために必要な商品の数量を示しています。
- 赤いセル (J10:J13) は、各倉庫の容量 (倉庫が保持できる商品の最大量) を表示します。
- 黄色 (C13:G13) と青色 (H10:H13) のセルは、それぞれ緑色のセルの行と列の合計です。
- 合計送料 (J18) は、商品数とそれに対応する送料の積の合計として計算されます。計算には、ここで関数が使用されます。 SUMPRODUCT (和積).
したがって、私たちのタスクは、緑色のセルの最適値の選択に限定されます。 そして、ラインの合計金額 (青色のセル) が倉庫の容量 (赤色のセル) を超えないようにすると同時に、各店舗が販売に必要な商品の数量 (各店舗の金額) を受け取るようにします。黄色のセルは要件にできるだけ近づける必要があります (紫色のセル)。
ソリューション
数学では、リソースの最適な配分を選択するこのような問題は、長い間定式化され、説明されてきました。 そしてもちろん、それらを解決する方法は、鈍い列挙 (非常に長い) ではなく、非常に少数の反復で長い間開発されてきました。 Excel は、アドインを使用してそのような機能をユーザーに提供します。 検索ソリューション (ソルバー) タブから 且つ （日にち）:
タブの場合 且つ お使いの Excel にはそのようなコマンドはありません。大丈夫です。つまり、アドインがまだ接続されていないということです。 有効にするには、開いてください File、その後、選択し 計測パラメータ – アドオン – 私たちについて
(オプション — アドイン — 移動). 開いたウィンドウで、必要な行の横にあるチェックボックスをオンにします 検索ソリューション (ソルバー).
アドオンを実行しましょう:
このウィンドウでは、次のパラメータを設定する必要があります。
- ターゲット関数の最適化 (t を設定お金 細胞） – ここでは、最適化の最終的な主な目標を示す必要があります。つまり、ピンクのボックスに合計送料 (J18) が表示されます。 ターゲット セルは、最小化 (この例のように費用の場合)、最大化 (たとえば、利益の場合)、または特定の値 (たとえば、割り当てられた予算に正確に収まるようにする) を試みることができます。
- 可変セルの変更 (By 変化 セル） – ここでは、緑色のセル (C10: G12) を示します。結果を達成したい値を変化させることにより、最小配送コストを実現します。
- 制限と一致 (件名 〜へ 制約) – 最適化の際に考慮しなければならない制限のリスト。 リストに制限を追加するには、 ボタンをクリックします。 Add (追加） 表示されるウィンドウに条件を入力します。 この場合、これが需要制約になります。
および倉庫の最大容量の制限:
物理的要因 (倉庫の容量と輸送手段、予算と時間の制約など) に関連する明らかな制限に加えて、「Excel 特有の」制限を追加する必要がある場合があります。 たとえば、Excel は、商品を店舗から倉庫に戻すことを提案することで、配送コストを「最適化」するよう簡単に手配できます。つまり、コストはマイナスになります。つまり、利益を上げることができます。 🙂
これを防ぐには、チェックボックスを有効のままにしておくことをお勧めします。 無制限の変数を非負にする または、そのような瞬間を制限のリストに明示的に登録することさえあります。
必要なパラメータをすべて設定すると、ウィンドウは次のようになります。
[解法を選択] ドロップダウン リストでは、XNUMX つのオプションの選択を解くための適切な数学的方法をさらに選択する必要があります。
- シンプレックス法 線形問題、つまり出力が入力に線形的に依存する問題を解決するためのシンプルで高速な方法です。
- 一般的な下降勾配法 (OGG) – 入力データと出力データの間に複雑な非線形依存関係がある非線形問題の場合 (たとえば、売上高と広告費の依存関係)。
- 解決策の進化的探索 – 生物学的進化の原理に基づく比較的新しい最適化手法 (こんにちは、ダーウィン)。 この方法は、最初の XNUMX つの方法よりも何倍も長く機能しますが、ほとんどすべての問題 (非線形、離散) を解決できます。
私たちのタスクは明確に直線的です: 1 個配達 – 40 ルーブルを使い、2 個配達 – 80 ルーブルを使いました。 などがあるため、シンプレックス法が最適です。
計算用のデータが入力されたので、 ボタンを押します。 解決策を見つけます (解決する）最適化を開始します。 セルと制約が多数変化する深刻なケースでは、解決策を見つけるのに長い時間がかかる場合がありますが (特に進化的方法の場合)、Excel のタスクは問題になりません。数分で次の結果が得られます。 :
倉庫の容量を超えず、各店舗に必要な数の商品に対するすべての要求を満たしながら、供給量が店舗間でどれほど興味深いことに分配されたかに注意してください。
見つかったソリューションが適切な場合は、それを保存するか、元の値にロールバックして、他のパラメーターで再試行できます。 選択したパラメーターの組み合わせを次のように保存することもできます。 <span class="notranslate">シナリオ</span>. ユーザーの要求に応じて、Excel は XNUMX つのタイプを構築できます。 レポート 別のシートで解決されている問題について：結果に関するレポート、ソリューションの数学的安定性に関するレポート、およびソリューションの限界（制限）に関するレポート。ただし、ほとんどの場合、それらは専門家のみが関心を持っています.
ただし、Excel が適切な解決策を見つけられない場合があります。 この例で、倉庫の総容量を超える量の店舗の要件を示すと、そのようなケースをシミュレートすることができます。 次に、最適化を実行するときに、Excel は解にできるだけ近づけようとし、解が見つからないというメッセージを表示します。 それでも、この場合でも、多くの有用な情報があります。特に、ビジネス プロセスの「弱点」を確認し、改善すべき領域を理解することができます。
もちろん、考慮されている例は比較的単純ですが、より複雑な問題を解決するために簡単に拡張できます。 例えば：
- 財源配分の最適化 プロジェクトの事業計画または予算の支出項目ごと。 この場合の制約は、プロジェクトの資金調達額とタイミングであり、最適化の目標は、利益を最大化し、プロジェクトのコストを最小化することです。
- 従業員のスケジューリングの最適化 企業の賃金基金を最小限に抑えるために。 この場合の制限は、雇用スケジュールと人員配置表の要件に従って、各従業員の希望になります。
- 投資投資の最適化 – 利益を最大化するため、または（より重要な場合は）リスクを最小化するために、複数の銀行、証券、または企業の株式間で資金を正しく分配する必要性。
いずれにせよアドオン 検索ソリューション (ソルバー) は非常に強力で美しい Excel ツールであり、注目に値します。現代のビジネスで直面しなければならない多くの困難な状況で役立つからです。