☰

如何使用Excel求解器來解決這個問題？

tag-icon • tag-icon microsoft-excel solver
如何使用Excel求解器來解決這個問題？

ABC Corporation 是一家電氣零件全球經銷商。該公司合約從歐洲採購零件，並將其運送到歐洲三個港口E1、E2、E3的倉庫。根據美國客戶的需求，各種零件被裝入貨櫃。每個港口每月可用的貨櫃數量有限。然後透過貨櫃船將貨櫃運往海外P1、P2、P3、P4港口。貨櫃通常從這些海港與卡車連接並拖運至 I1、I2 和 I3 的內陸港口。每個港口每月都有固定數量的貨運車。這些內陸港口有時被稱為「貨運村」或多式聯運樞紐，貨櫃在這裡被收集並從一種運輸方式轉移到另一種運輸方式（即從卡車到鐵路，反之亦然）。貨櫃從內陸港口運往ABC位於D1、D2、D3、D4、D5的配送中心。以下是該海外供應鏈上每個裝運點和目的地點之間的裝卸和運輸成本（美元/貨櫃）以及每個港口的可用貨櫃：

範例 Excel 工作表

紅色單元格表示特定路線不是可行的選擇，因此不能考慮。確定該海外供應鏈上從每個裝船點到每個目的地的最佳運輸方式，從而使總運輸成本最低以及每個階段產生的總運輸成本。

答案1

以下是為求解器設定問題的範例。您也許可以使其更加緊湊，但我設置它的方式很容易診斷任何問題。

  1. 在「資料」標籤中使用您的原始資料。
  2. 建立一個新選項卡，我稱之為“方程式”，以設定求解器問題。
  3. A 至 D 欄用於設定所有可能的路線組合。例如，第 1 行是標題；第2行是E1、P1、I1、D1；第2行是E1、P1、I1、D2； ETC。
  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. 第一列是沿著該路線行駛的單位（貨櫃）數量。該列將由求解器操作。我用 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. 至：是分鐘
  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。複製這五個並貼上下來。 I1，向下複製一組 D，I2，向下複製一組 D，依此類推，直到表格填滿。對於 $EP 字段，我結合了INDEX和MATCH來查找從 E（無論）到 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 字段，調整每個字段以引用正確的範圍。最後，「總成本」欄位只是將這三個部分加在一起。我按照升序排序，找到了最便宜的路線。所有錯誤（使用不可用路由的錯誤）都被傳送到底部。

如果您想要一個持續的系統，您想要追蹤行程每段目前有多少交通工具，以便您可以根據目前可用的路線選擇最便宜的路線，您可能需要查看資料庫解決方案。或者，您可以使用排序列表，從頂部開始，向下查找，直到找到每條線路目前可用的路線。

相關內容