重要
我們將於 2025 年 10 月 14 日終止對 Office 2016 和 Office 2019 的支援。 升級至 Microsoft 365 即可隨時隨地在任何裝置上工作，並繼續獲得支援。
本文討論使用 Solver，一個 Microsoft Excel 外掛程式，可用於假設分析，以判斷最佳產品組合。
我該如何判斷每月產品組合以最大化獲利？
公司通常需要確定每月生產的每款產品的數量。 最簡單的產品 組合 問題是如何決定每個月應該生產多少產品以最大化利潤。 產品組合通常必須遵守以下限制條件：
- 產品組合不能使用超過現有資源的資源。
- 每種產品的需求都有限。 我們無法在一個月內生產超過需求所需的產品，因為過剩的生產會被浪費 (例如易腐的藥品) 。
現在讓我們來解決以下產品組合問題的例子。 你可以在圖 27-1 所示的檔案 Prodmix.xlsx 中找到這個問題的解法。
假設我們為一家藥廠工作，該公司在工廠生產六種不同的產品。 每項產品的生產都需要勞動力和原料。 圖27-1的第4行顯示生產每磅產品所需的勞動時數，第5行顯示生產每項產品所需的原料磅數。 例如，生產一磅產品1需要六小時的勞動時間和3.2磅的原料。 每種藥品的每磅價格在第6行，單位成本在第7行，每磅利潤貢獻在第9行。 例如，產品二每磅售價為11.00美元，單成本為每磅5.70美元，並每磅帶來5.30美元的利潤。 每月每種藥品的需求量列在第8行。 例如，產品3的需求量為1041磅。 本月，有4500小時的勞動力和1600磅的原料可用。 這家公司如何最大化每月利潤？
如果我們對 Excel Solver 一無所知，我們會透過製作工作表來追蹤產品組合相關的利潤與資源使用。 接著我們會透過試錯調整產品組合，以優化利潤，同時不消耗超過現有的勞動力或原料，也不生產超出需求的藥物。 我們只在這個過程中使用Solver。 本質上，Solver 是一個能完美執行試錯搜尋的優化引擎。
解決產品組合問題的關鍵在於有效計算任何特定產品組合的資源使用與利潤。 我們可以用來進行計算的重要工具是 SUMPRODUCT 函數。 SUMPRODUCT 函數會將對應的值乘以格子範圍，並回傳這些值的總和。 SUMPRODUCT 評估中使用的每個儲存格範圍必須有相同的維度，這表示你可以在兩列或兩列使用 SUMPRODUCT，但不能同時使用一欄一列。
舉例來說，我們來計算資源使用量。 我們的勞動力使用計算為
(每磅藥品使用的勞動力 1) * (藥品 1 磅生產) +
(每磅藥品使用的勞動力 2) * (生產藥品 2 磅) + ...
(勞動力每磅藥品使用量 6) * (藥品產量 6 磅)
我們可以用更繁瑣的方式計算勞動使用，如 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4。 同樣地，原料使用量可計算為 D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5。 然而，將這些公式輸入六款產品的工作表非常耗時。 想像一下，如果你和一家在工廠生產50種產品的公司合作，會花多久時間。 計算勞動力與原料使用量的更簡單方法是從 D14 複製到 D15 的公式 SUMPRODUCT ($D$2：$I$2，D4：I4) 。 這個公式計算出 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (，這是我們的勞動使用) ，但輸入起來容易多了！ 注意我使用 $ 符號，範圍為 D2：I2，這樣當我複製公式時，仍能捕捉到第 2 列的產品混合。 D15格中的公式計算原料使用量。
同樣地，我們的利潤由以下方式決定
(藥品 每磅 1 利潤) * (藥品 1 磅生產) +
(藥品每磅2利潤) * (藥品2磅生產量) +...
(藥品 每磅 6 利潤) * (藥品 6 磅生產)
利潤可在 D12 格子中輕鬆計算，公式為 SUMPRODUCT (D9：I9，$D$2：$I$2) 。
我們現在可以辨識產品組合求解器模型的三個組成部分。
目標細胞。 我們的目標是最大化在 D12) 單元計算出的利潤 (。
換細胞。 每種產品的產量 (標示在 D2：I2)
限制。 我們有以下限制條件：
- 不要使用超過現有的勞動力或原料。 也就是說，D14：D15 格子中的值， (所用資源) 必須小於或等於 F14：F15 格子的值， (可用資源) 。
- 不要生產超過需求量的藥物。 也就是說，每) 產生的 D2：I2 (磅數值必須小於或等於 D8：I8) 格中列出的每種藥物的需求 (。
- 我們不能生產任何藥物的負量。
我會示範如何將目標儲存格、切換儲存格和限制設定輸入 Solver。 接著你只要點擊「解決」按鈕，就能找到一個能最大化利潤的產品組合！
開始時，點選「資料」標籤，並在分析組中點選「求解器」。
注意
如第26章「Excel Solver 優化入門」所述，安裝 Solver 需點擊 Microsoft Office 按鈕，接著是 Excel 選項，最後是外掛。在管理清單中，點選 Excel 附加元件，勾選求解器附加元件方塊，然後點選確定。
解算器參數對話框將會出現，如圖 27-2 所示。
點擊設定目標格子，然後選擇我們的利潤區塊 (區塊 D12) 。 點選「透過更換細胞」框，然後指向 D2：I2 範圍，該範圍包含每種藥物產生的重量。 對話框現在應該會像圖 27-3 一樣。
我們現在準備好為模型加入約束。 點擊新增按鈕。 你會看到「新增限制」對話框，如圖 27-4 所示。
要新增資源使用限制，點選格參考框，然後選擇範圍 D14：D15。 從中間列表中選擇 <= 。 點選限制方塊，然後選擇格子範圍 F14：F15。 新增限制對話框現在應該會像圖 27-5 一樣。
我們現在確保，當求解器嘗試變化單元的不同值時，只有同時滿足 D14<=F14 ( 所用勞動力的組合，且 D15<=F15 (所用原料小於或等於可用原料) ) 的組合才會被考慮。 點擊新增以輸入需求限制。 請如圖 27-6 所示填寫「新增限制」對話框。
加入這些限制確保當求解器嘗試不同組合以變化的儲存格值時，僅考慮符合以下參數的組合：
- D2<=D8 (藥物1的生產量小於或等於藥物1的需求)
- E2<=E8 (藥物2的產量小於或等於藥物2的需求)
- F2<=F8 (藥物3的生產量小於或等於藥物3的需求)
- G2<=G8 (藥物4的生產量小於或等於藥物4的需求)
- H2<=H8 (生產的藥物5的量小於或等於藥物5的需求)
- I2<=I8 (藥物6的生產量小於或等於藥物6的需求)
在「新增限制」對話框中點擊確定。 解算器視窗應該看起來像圖 27-7。
我們在求解器選項對話框中輸入變更格子必須非負數的限制。 點選求解器參數對話框中的選項按鈕。 請勾選假設線性模型的選項與假設非負的選項，如下一頁圖27-8所示。 按一下 [確定]。
勾選假設非負方框，確保求解器只考慮變化格的組合，且每個變化格子取值為非負值。 我們勾選了「假設線性模型」這個選項，因為產品組合問題是一種特殊類型的求解器問題，稱為 線性模型。 基本上，解算器模型在以下條件下是線性的：
- 目標儲存格的計算方法是將 變化儲存格) * (常數) 的項相加而成 (。
- 每個約束條件都符合「線性模型需求」。這表示每個約束的評估方式是將變化 單元) * (常數 () 的項相加，並將和與常數比較。
為什麼這個求解器問題是線性的？ 我們的目標單元 (利潤) 計算為
(藥品 每磅 1 利潤) * (藥品 1 磅生產) +
(藥品每磅2利潤) * (藥品2磅生產量) +...
(藥品 每磅 6 利潤) * (藥品 6 磅生產)
此計算遵循一種模式：透過將變化格子 ) * (常數) 相加， (形如 的項來推導出。
我們的勞動力限制是透過比較 (藥品1號藥品所用勞動力) * (藥品1磅生產量) + (藥品2磅) * (藥品2磅生產量) + ... (藥品6磅所用勞動力) * (藥品6磅生產 的勞動力) 可用勞動力。
因此，勞動限制的評估方式是將形如 (的項相加，將 變化的格子) * (常數) ，並將和與常數比較。 勞動限制與原料限制均符合線性模型的要求。
我們的需求約束形式為
(生產的藥物1) <= (藥物1的需求)
(產生的藥物2) <= (藥物2的需求)
§
(生產的藥物6) <= (藥物6的需求)
每個需求約束也符合線性模型的要求，因為每個需求是將變化格子 ) * (常數) ( 項相加，並將和與常數比較來評估。
既然我們的產品組合模型是線性模型，我們為什麼要在意呢？
- 若解算器模型為線性且選擇假設線性模型，求解器保證能找到解算器模型的最佳解。 若解算器模型非線性，求解器可能找到或不會找到最佳解。
- 若解算器模型為線性，且選擇假設線性模型，求解器會使用 (單純形法) 的高效演算法來尋找模型的最佳解。 若解算器模型為線性且未選擇假設線性模型，解算器會使用 (GRG2 方法) 的非常低效率演算法，且可能難以找到模型的最佳解。
在解算器選項對話框中點擊確定後，我們返回圖 27-7 所示的主求解器對話框。 當我們點擊「解決」時，Solver 會計算出 (產品組合模型中是否有) 的最佳解。 正如我在第26章所述，產品組合模型的最佳解是一組每) 產生的細胞值變化 (磅，以最大化所有可行解決方案的利潤。 同樣地，可行解是一組滿足所有限制條件且不斷變化的格子值。 圖27-9所示的變化單元值是一個可行的解決方案，因為所有生產水準皆為非負值，生產水準不超過需求，且資源使用量不超過可用資源。
下一頁圖27-10所示的變化格子值代表 一個不可行解 ，原因如下：
- 我們生產的藥物5比需求還多。
- 我們用的勞動力比現有的還多。
- 我們使用的原料比現有的還多。
點擊「解」後，求解器會迅速找到圖 27-11 所示的最佳解。 你需要選擇「保留求解器解」來保留工作表中的最佳解值。
我們的藥廠可以透過生產596.67磅藥物4、1084磅藥物5，以及不含其他藥物，最大化每月6,625.20美元的利潤！ 我們無法判斷是否能透過其他方式達到最高 6,625.20 美元的利潤。 我們唯一能確定的是，以我們有限的資源和需求，這個月不可能賺超過6,627.20美元。
解算器模型是否總是有解？
假設 必須 滿足每種產品的需求。 (請參閱檔案 Prodmix.xlsx.) 中的 「不可行解決方案 」工作表接著我們必須將需求約束從 D2：I2<=D8：I8 改為 D2：I2>=D8：I8。 要做到這點，打開求解器，選擇 D2：I2<=D8：I8 約束，然後點擊變更。 圖 27-12 中顯示的「變更約束」對話框會出現。
選擇 >=，然後點擊確定。 我們現在確保 Solver 只會考慮更改符合所有需求的儲存格值。 當你點擊「解決」時，你會看到「解答器找不到可行的解決方案」的訊息。這個訊息並不代表我們在模式上犯了錯，而是說以我們有限的資源，無法滿足所有產品的需求。 Solver 只是告訴我們，如果想滿足每種產品的需求，就需要增加更多人力、更多原料，或兩者兼有。
如果一個求解器模型產生的結果是「集合值不收斂」，這是什麼意思？
讓我們看看如果允許每種產品無限需求，且每種藥物生產負量會發生什麼。 (你可以在檔案 Prodmix.xlsx.) 的「 集合值不收斂 」工作表中看到這個求解器問題。要找到這種情況的最佳解，打開求解器，點擊選項按鈕，並清除假設非負的選項框。 在求解器參數對話框中，選擇需求限制 D2：I2<=D8：I8，然後點擊刪除以移除該限制。 當你點擊「解決」時，求解器會回傳「設定儲存格值不收斂」。此訊息表示若目標格子要 (最大化，如我們範例) ，則存在可行解，目標格值任意大。 (若目標細胞要最小化，「Set Cell Values Not Converge」的訊息表示存在可行的解決方案，目標細胞值可任意縮小。) 在我們的情況下，允許藥物的負面生產，實際上是「創造」資源，可用於生產任意大量其他藥物。 鑑於我們無限的需求，這讓我們能夠賺取無限利潤。 在真實情況下，我們不可能賺無限多的錢。 簡言之，如果你看到「Set Values Do Not Converge」，代表你的模型確實有錯誤。
問題
假設我們的藥廠能以比現有人工成本多1美元的價格購買最多500小時的勞動力。 我們該如何最大化利潤？
在晶片製造廠，四位技術人員 (A、B、C、D) 生產三款產品 (產品1、2及3) 。 本月，晶片製造商可銷售80件產品1、50件產品2，以及最多50件產品3。 技術員A只能製造產品1和3。 技術員B只能製造產品1和產品2。 技術員C只能製造產品3。 技術員D只能製造產品2。 每生產一個單位，產品帶來以下利潤：產品1,6美元;產品2,7美元;產品3則為10美元。 每位技術人員製造產品所需的時間 () 小時數如下：
產品 技術員A 技術員B 技術員C 技術員D 1 2 2.5 不行 不行 2 不行 3 不行 3.5 3 3 不行 4 不行 每位技術員每月最多可工作120小時。 晶片製造商如何最大化每月利潤？ 假設可以生產出少量單位。
一家電腦製造廠生產滑鼠、鍵盤和電子遊戲搖桿。 下表列出了每單位利潤、每單位勞動使用量、每月需求及每單位機器使用時間：
滑鼠 鍵盤 搖桿 利潤/單位 8美元 11元 9 美元 勞動使用/單位 0.2小時 0.3小時 0.24小時 機器時間/單位 0.04小時 0.055小時 0.04小時 每月需求 15,000 27,000 11,000 每個月總共提供13,000小時的勞動時數及3,000小時的機器時間。 製造商如何最大化從工廠帶來的每月利潤貢獻？
假設每種藥物至少需滿足200單位的需求，來解決我們的藥物範例。
Jason 製作鑽石手鍊、項鍊和耳環。 他希望每月最多工作160小時。 他有800盎司鑽石。 以下列出生產每種產品所需的利潤、勞動時間及鑽石數量。 如果每種產品的需求都是無限的，Jason 該如何最大化他的利潤？
產品 單位利潤 每單位勞動工時 每單位鑽石盎司數 手鐲 $300 .35 1.2 項鍊 $200 .15 .75 耳環 $100 .05 .5