☰

これを解決するために Excel ソルバーをどのように使用すればよいでしょうか?

tag-icon • tag-icon microsoft-excel solver
これを解決するために Excel ソルバーをどのように使用すればよいでしょうか?

ABC 社は、電気部品およびコンポーネントのグローバル ディストリビューターです。同社はヨーロッパからコンポーネントおよびパーツを購入する契約を結んでおり、それらをヨーロッパの 3 つの港 (E1、E2、E3) の倉庫に配送しています。さまざまなコンポーネントおよびパーツは、米国の顧客からの需要に基づいてコンテナに積み込まれます。各港では、毎月利用できるコンテナの数が限られています。コンテナはその後、コンテナ船で P1、P2、P3、P4 の港に海外に輸送されます。これらの港から、コンテナは通常トラックに積み込まれ、I1、I2、I3 の内陸港に運ばれます。各港では、毎月一定数の貨物運送業者が利用できます。これらの内陸港は、「貨物村」または複合輸送拠点と呼ばれることもあり、ここでコンテナが集められ、ある輸送モードから別の輸送モード (つまり、トラックから鉄道、またはその逆) に転送されます。内陸港から、コンテナは D1、D2、D3、D4、D5 にある ABC の配送センターに輸送されます。以下は、この海外サプライ チェーンに沿った各乗船地点と目的地点間の取り扱いと輸送コスト (コンテナ 1 個あたりのドル) と、各港で利用可能なコンテナです。

サンプル Excel シート

赤いセルは、特定のルートが実行可能なオプションではないため、検討できないことを示します。この海外サプライ チェーンに沿って、各出発地点から各目的地までの最適な出荷を決定します。これにより、総輸送コストが最小になり、各段階で発生する総輸送コストも最小になります。

答え1

以下は、ソルバー用に問題を設定する例です。もっとコンパクトにすることもできるかもしれませんが、私が設定した方法では、問題を簡単に診断できます。

  1. 「データ」タブで元のデータを使用します。
  2. ソルバーの問題を設定するために、「方程式」という新しいタブを作成します。
  3. 列 A から D は、ルートの可能な組み合わせすべてを設定するために使用されます。たとえば、行 1 はヘッダー、行 2 は E1、P1、I1、D1、行 2 は E1、P1、I1、D2 などです。
  4. 列 E から G は、ヨーロッパから米国港、米国港から内陸港、内陸港から配送センターに関連するコストです。これは、VLOOKUP と MATCH を使用して入力されます。たとえば、E2 では=VLOOKUP(A2,Data!$A$3:$E$5,MATCH(B2,Data!$A$2:$E$2,0),FALSE)、F2 では=VLOOKUP(B2,Data!$A$11:$D$14,MATCH(C2,Data!$A$10:$D$10,0),FALSE)、G2 では です=VLOOKUP(C2,Data!$A$20:$F$22,MATCH(D2,Data!$A$19:$F$19,0),FALSE)。これらはすべて下方向に入力されます。
  5. 列 H は合計ユニット コストです。実行不可能なルートを明確にするため、それらの合計コストを 1,000,000 に設定しました。たとえば、H2 は です=IF(OR(E2=0,F2=0,G2=0),1000000,SUM(E2:G2))。これは下方向に埋められます。
  6. 列 I は、そのルートを移動するユニット (コンテナ) の数です。この列はソルバーによって操作されます。すべての行を 1 で初期化しました。
  7. 列 J はルートの合計コストです。たとえば、J2 は=H2*I2下方向に埋められます。
  8. 同じタブに、目的関数と制約を格納するための別のテーブルが作成されます。これは L1 から O19 にあります。ここでの計算の例には、次のものがあります。nUnits は前のテーブルの合計であるため、E1 のコンテナ数は=SUMIFS($I$2:$I$181,$A$2:$A$181,L2)、P1 のコンテナ数は=SUMIFS($I$2:$I$181,$B$2:$B$181,L5)などです。maxUnits はデータ タブから抽出されるため、E1 の最大コンテナ数は=VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE)、I1 の最大コンテナ数は=HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE)などです。同様に、需要単位はデータ タブから抽出されます。
  9. 実行不可能なルートが選択されないように、追加の制約を追加しました。
  10. 目標は、列 J の合計である総コストです。

以下は、ソルバーを実行する前の方程式タブのスクリーン キャプチャです。いくつかの行が非表示になっています。

ここに画像の説明を入力してください

ソルバーの設定は次のとおりです。

  1. 目標を設定する:$M$19
  2. 宛先: Min
  3. 変数セルを変更することで：$I$2:$I$181
  4. 制約のない変数を非負にするは選択されていません（これは制約で処理されます）
  5. 解決方法を選択します: 進化型です。進化型は、整数制約を処理する場合、他の方法よりもはるかに高速です。
  6. 制約の対象: 多数あります...
    • $I$2:$I$181 = integer- コンテナの数は整数である必要があります
    • $I$2:$I$181 >= 0 - マイナスのコストは発生しない
    • $M$2 <= $N$2各行に重複して$M$11 <=$N$11- コンテナの最大数に違反しない
    • $M$12 = $O$12$M$16 = $O$16需要を満たすために各行に複製
    • $M$17 = $N$17- 実行不可能なオプションは使用しない

以下は、ソルバー パラメーター ダイアログ ボックスのスクリーン ショットです。

ここに画像の説明を入力してください

この設定により、すべての需要が満たされ、制約に違反することなく、合計コストが 1,661,119.00 ドルになります。以下は制約テーブルのスクリーン ショットです...

ここに画像の説明を入力してください

以下は、すべてのゼロルートを除外したコスト テーブルのスクリーン ショットです。

ここに画像の説明を入力してください

答え2

一度だけ回答が必要な場合、最も安いルートE2 > P4 > I2 > D2の料金は 1,452 ドルです。

私が使用した方法は、洗練されたものではありませんでした。私は、考えられるすべてのオプションの表を作成し、 を使用してINDEX各MATCH旅行区間のコストを調べ、合計コストの昇順で並べ替えました。私のシートは次のようになります。

スプレッドシート

左側のデータは、スプレッドシートからコピーしたものです。右側の表は私が作成したものです。フィールド E、P、I、D のそれぞれについて、グループごとに手動で入力しました。D1、D2、D3、D4、D5。これら 5 つをコピーして、たくさん貼り付けます。I1、D のセットをコピーして下に貼り付け、I2、D のセットをコピーして下に貼り付け、というように、表がいっぱいになるまで続けます。フィールド $EP については、とを組み合わせて、INDEXE MATCH(何でも) から P(何でも) への移動コストを検索しました。

=1/(1/INDEX($B$3:$E$5,MATCH([@E],$A$3:$A$5,0),MATCH([@P],$B$2:$E$2,0)))

INDEX配列を受け取り、その配列内のどこかからセルを返します。必要な行を見つけるために、MATCH範囲 内の E 値がどこにあるかを調べましたA3:A5。列を見つけるために、 内の P 値を探しましたB2:B4。これにより、E から P への移動コストがわかりました。1/(1/...)全体をラッパーで囲んだので、移動区間がゼロの場合はエラーが返され、利用できないことが示されます。

次に、その数式をフィールド $PI と $ID にコピーし、それぞれを調整して正しい範囲を参照するようにしました。最後に、合計コスト フィールドで、これら 3 つの区間を単純に合計しました。昇順で並べ替えて、最も安いルートを見つけました。エラー (使用できないルートを使用しているもの) はすべて一番下に送られました。

現在利用可能なものに基づいて最も安いルートを選択できるように、旅行の各区間で現在進行中の輸送手段の数を追跡する継続的なシステムが必要な場合は、データベース ソリューションを検討することをお勧めします。または、並べ替えられたリストを使用して、上から始めて、各区間が現在利用可能なルートが見つかるまで下に進むこともできます。

関連情報