試談使用規(guī)劃求解確定最佳產品組合

1、使用規(guī)劃求解確定最佳產品組合什么是 Excel 規(guī)劃求解工具？當您想要查找做某件事的最佳方法時，使用的確實是規(guī)劃求解。或者，更正規(guī)的講法確實是，當您想要在電子表格的某些單元格中得到優(yōu)化（最大化或最小化）某個目標的值時，使用的確實是規(guī)劃求解。優(yōu)化模型包括三部分：目標單元格、可變單元格和約束。目標單元格代表目的或目標。例如，最大化每月利潤。可變單元格是電子表格中我們能夠進行更改或調整以優(yōu)化目標單元格的單元格。例如，每月每種產品的產量。約束是您置于可變單元格中的限制條件。例如，使用的資源不能超標，同時不能生產過剩的產品。如何確定哪種產品組合能夠使利潤最大化？公司通常需要確定每月（或每周）生產打算，列

2、出每種產品必須生產的數(shù)量。具體來講確實是，產品組合問題涉及如何確定在每月應該生產的每種產品的數(shù)量以使利潤最大化。產品組合通常必須滿足以下約束：產品組合使用的資源不能超標。對每種產品的需求差不多上有限的。我們每月生產的產品不能超過需求的數(shù)量，因為生產過剩確實是白費（例如，易變質的藥品）。讓我們來解決以下產品組合示例問題。您能夠在 prodmix.xls 文件中找到該問題的解決方案（該文件包含在中），如圖 1 所示。圖 1：產品組合示例。假定我們在一家醫(yī)藥公司工作，這家公司能夠在他們的工廠生產六種產品。生產每種產品都需要人工和原材料。 圖 1 的第 4 行顯示了生產一磅的每種產品所需的人工小時數(shù)，

3、第 5 行顯示了生產一磅的每種產品所需的原材料的磅數(shù)。例如，生產一磅的產品 1 需要 6 小時人工和 3.2 磅原材料。 第 6 行顯示了每種藥品每磅的價格，第 7 行顯示了每磅的成本，第 9 行顯示每磅可帶來的利潤。例如，產品 2 的價格是每磅 11.00 美元，每磅的單位成本是 5.70 美元，每磅的利潤確實是 5.30 美元。 第 8 行顯示了該月對每種藥品的需求。例如，對產品 3 的需求為 1041 磅。 該月可提供 4500 人工工時和 1600 磅的原材料。該公司如何最大化它每月的利潤？假如我們對規(guī)劃求解一無所知，我們會通過構建一個電子表格，然后在其中跟蹤每種產品組合以及與該產品組

4、合相關聯(lián)的資源用量來處理這一問題。然后我們 會反復試驗、不斷地變化產品組合以優(yōu)化利潤，同時確保使用的人工或原材料可不能超標，并確保可不能生產出過剩藥品。在此過程中，我們只在反復試驗時期中使用了 規(guī)劃求解。從全然上來講，規(guī)劃求解是一個能夠完美地執(zhí)行反復試驗搜索的優(yōu)化引擎。解決產品組合問題的關鍵是有效地計算與任一給定產品組合相關聯(lián)的資源用量和利潤。SUMPRODUCT 函數(shù)是我們能夠用來執(zhí)行此計算的一個重要工具。SUMPRODUCT 函數(shù)將單元格區(qū)域中相應的值相乘并返回這些值的總和。SUMPRODUCT 評估中使用的每個單元格區(qū)域都必須具有相同的維度，這意味著您能夠對兩行或兩列使用 SUMPROD

5、UCT，而不是對一列或一行。作為如何在產品組合示例中使用 SUMPRODUCT 函數(shù)的示例，讓我們嘗試計算一下我們的資源用量。通過以下計算方式能夠得出人工用量：（每磅藥品 1 使用的人工）*（生產的藥品 1 的磅數(shù)）+（每磅藥品 2 使用的人工）*（生產的藥品 2 的磅數(shù)）+.（每磅藥品 6 使用的人工）*（生產的藥品 6 的磅數(shù)）在我們的電子表格中，我們可能會通過 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 來計算人工用量（特不繁鎖）。類似地，原材料用量能夠通過 D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5 計算。在電子表格中對六種產品分

6、不輸入這些公式是專門白費時刻的。想像一下，假如您正在對一家其工廠生產 50 種產品的公司執(zhí)行如此的計算，會花費多長時刻？ 計算人工和原材料用量的一種更為簡單的方法是將 D14 中的公式復制到 D15 中： SUMPRODUCT($D$2:$I$2,D4:I4)該公式會計算 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4（這是我們的人工用量），這要比手動輸入簡單得多！ 請注意，我對區(qū)域 D2:I2 使用了 $ 符號，以便在我復制公式時，我仍然能夠從第 2 行中取下產品組合。單元格 D15 中的公式用于計算原材料用量。類似地，通過以下計算方式能夠得出我們的利潤：（每磅藥品

7、1 的利潤）*（生產的藥品 1 的磅數(shù)）+（每磅藥品 2 的利潤）*（生產的藥品 2 的磅數(shù)）+.（每磅藥品 6 的利潤）*（生產的藥品 6 的磅數(shù)）。在單元格 D12 中使用以下公式能夠專門容易計算出利潤： SUMPRODUCT(D9:I9,$D$2:$I$2)現(xiàn)在我們能夠標識出產品組合規(guī)劃求解模型的三個組成部分：目標單元格可變單元格約束我們的目標是使利潤（在單元格 D12 中計算）最大化。生產的每種產品的磅數(shù)（在單元格區(qū)域 D2:I2 中列出）。使用的人工和原材料不能超標。也確實是講，單元格 D14:D15（所用資源）必須小于或等于單元格 F14:F15 中的值（可用資源）。生產的藥品不能

8、超過需求數(shù)量。也確實是講，單元格 D2:I2（生產的每種藥品的磅數(shù)）必須小于或等于對每種藥品的需求（在單元格 D8:I8 中列出）。我們不能生產任何產量為負的藥品。何將此模型輸入到規(guī)劃求解中？現(xiàn)在，我將向你們演示如何將目標單元格、可變單元格和約束輸入規(guī)劃求解。然后，你們只需單擊“求解”按鈕即可，規(guī)劃求解將會找出可使利潤最大化的產品組合。要開始操作，請選擇“工具”菜單上的“規(guī)劃求解”。（有關安裝規(guī)劃求解的講明，請參閱。） 即會出現(xiàn)“規(guī)劃求解參數(shù)” 對話框。要輸入目標單元格，請在“設置目標單元格”框中單擊，然后選擇利潤單元格（單元格 D12）。要輸入可變單元格，請在“可變單元格”框中單擊，然后指向

9、區(qū)域 D2:I2，該區(qū)域包含生產的每種藥品的磅數(shù)。該對話框現(xiàn)在看起來應如下圖所示?，F(xiàn)在我們差不多能夠向模型中添加約束了。單擊“添加”按鈕，您能夠看到“添加約束”對話框。要添加資源用量約束，請在標記為“單元格引用位置”的框中單擊，然后選擇區(qū)域 D14:D15。從對話框中部的列表中選擇“=”。在標記為“約束值”的框中單擊，然后選擇單元格區(qū)域 F14:F15。現(xiàn)在我們差不多確保當規(guī)劃求解嘗試對可變單元格使用不同的值時，規(guī)劃求解將只考慮同時滿足 D14 = F14（所用人工小于或等于可用人工）和 D15 = F15（所用原材料小于或等于可用原材料）的組合。 現(xiàn)在，在“添加約束”對話框中單擊“添加”，以

10、輸入需求約束。只需如下圖所示填充“添加約束”對話框即可。添加這些約束能夠確保當規(guī)劃求解嘗試對可變單元格值使用不同的組合時，規(guī)劃求解將只考慮滿足以下條件的組合：D2 = D8（藥品 1 的產量小于或等于對藥品 1 的需求量）E2 = E8（藥品 2 的產量小于或等于對藥品 2 的需求量）F2 = F8（藥品 3 的產量小于或等于對藥品 3 的需求量）G2 = G8（藥品 4 的產量小于或等于對藥品 4 的需求量）H2 = H8（藥品 5 的產量小于或等于對藥品 5 的需求量）I2 = I8（藥品 6 的產量小于或等于對藥品 6 的需求量）單擊“添加約束”對話框中的“確定”?！耙?guī)劃求解參數(shù)”對話框

11、應如下圖所示。在“規(guī)劃求解選項”對話框中輸入所有可變單元格都為非負值的約束，通過單擊“規(guī)劃求解參數(shù)”對話框中的“選項”按鈕可打開該對話框。 選擇“采納線性模型”和“假定非負”選項，然后單擊“確定”。選擇“假定非負”選項可確保規(guī)劃求解只考慮每個可變單元格都采納非負值的可變單元格組合。 選擇“采納線性模型”的緣故是產品組合問題是一種稱為線性模型的專門規(guī)劃求解問題。差不多上，在以下情況下，規(guī)劃求解模型差不多上線性模型：目標單元格是通過將表單的條件（可變單元格）*（約束）相加進行計算的。每種約束都滿足線性模型要求。這意味著每種約束差不多上通過將表單的條件（可變單元格）*（約束）相加，然后將這些總和與某

12、個常量進行比較來評估的。那個規(guī)劃求解問題什么緣故是線性的？ 我們的目標單元格（利潤）計算方式為： （每磅藥品 1 的利潤）*（生產的藥品 1 的磅數(shù)）+（每磅藥品 2 的利潤）*（生產的藥品 2 的磅數(shù)）+.（每磅藥品 6 的利潤）*（生產的藥品 6 的磅數(shù)）這種計算方式遵循一種模式，即目標單元格的值是通過將表單的各個條件（可變單元格）*（約束）相加得出的.我們的人工約束是通過將可用人工與通過以下公式得出的值進行比較來評估的： （每磅藥品 1 使用的人工）*（生產的藥品 1 的磅數(shù)）+ （每磅藥品 2 使用的人工）*（生產的藥品 2 的磅數(shù)）+ .（每磅藥品 6 使用的人工）*（生產的藥品 6

13、 的磅數(shù)）因此，人工約束是通過將表格的各個條件（可變單元格）*（約束）相加，然后將這類總和與某個常量進行比較來評估的。人工約束和原材料約束都滿足線性模型要求。我們的需求約束采納以下形式：（藥品 1 的產量）=（藥品 1 的需求量）（藥品 2 的產量）=（藥品 2 的需求量）.（藥品 6 的產量）=（藥品 6 的需求量）每種需求約束還都符合線性模型要求，因為每種約束差不多上通過將表單的各個條件（可變單元格）*（約束）相加，然后將這些總和與某個常量進行比較來評估的。差不多表明我們的產品組合模型是線性模型，我們還要關懷什么？假如規(guī)劃求解模型是線性的，同時我們選擇“采納線性模型”，則規(guī)劃求解保證能夠找

14、到規(guī)劃求解模型的最佳解決方案。假如規(guī)劃求解模型不是線性的，則規(guī)劃求解可能能夠找到最佳解決方案，也可能找不到。假如規(guī)劃求解模型是線性的，同時我們選擇“采納線性模型”，規(guī)劃求解將使用一種特不高效的算法（單工方法）來找到該模型的最佳解決方案。假如規(guī)劃求解模型是線性的，同時我們不選擇“采納線性模型”，規(guī)劃求解將使用一種特不低效的算法（GRG2 方法），同時可能專門難找到該模型的最佳解決方案。選擇“假定非負”選項可確保規(guī)劃求解只考慮每個可變單元格都采納非負值的可變單元格組合。 選擇“采納線性模型”的緣故是產品組合問題是一種稱為線性模型的專門規(guī)劃求解問題。差不多上，在以下情況下，規(guī)劃求解模型差不多上線性模

15、型：目標單元格是通過將表單的條件（可變單元格）*（約束）相加進行計算的。每種約束都滿足線性模型要求。這意味著每種約束差不多上通過將表單的條件（可變單元格）*（約束）相加，然后將這些總和與某個常量進行比較來評估的。那個規(guī)劃求解問題什么緣故是線性的？ 我們的目標單元格（利潤）計算方式為： （每磅藥品 1 的利潤）*（生產的藥品 1 的磅數(shù)）+（每磅藥品 2 的利潤）*（生產的藥品 2 的磅數(shù)）+.（每磅藥品 6 的利潤）*（生產的藥品 6 的磅數(shù)）這種計算方式遵循一種模式，即目標單元格的值是通過將表單的各個條件（可變單元格）*（約束）相加得出的.我們的人工約束是通過將可用人工與通過以下公式得出的值

16、進行比較來評估的： （每磅藥品 1 使用的人工）*（生產的藥品 1 的磅數(shù)）+ （每磅藥品 2 使用的人工）*（生產的藥品 2 的磅數(shù)）+ .（每磅藥品 6 使用的人工）*（生產的藥品 6 的磅數(shù)）因此，人工約束是通過將表格的各個條件（可變單元格）*（約束）相加，然后將這類總和與某個常量進行比較來評估的。人工約束和原材料約束都滿足線性模型要求。我們的需求約束采納以下形式：（藥品 1 的產量）=（藥品 1 的需求量）（藥品 2 的產量）=（藥品 2 的需求量）.（藥品 6 的產量）=（藥品 6 的需求量）每種需求約束還都符合線性模型要求，因為每種約束差不多上通過將表單的各個條件（可變單元格）*（

17、約束）相加，然后將這些總和與某個常量進行比較來評估的。差不多表明我們的產品組合模型是線性模型，我們還要關懷什么？假如規(guī)劃求解模型是線性的，同時我們選擇“采納線性模型”，則規(guī)劃求解保證能夠找到規(guī)劃求解模型的最佳解決方案。假如規(guī)劃求解模型不是線性的，則規(guī)劃求解可能能夠找到最佳解決方案，也可能找不到。假如規(guī)劃求解模型是線性的，同時我們選擇“采納線性模型”，規(guī)劃求解將使用一種特不高效的算法（單工方法）來找到該模型的最佳解決方案。假如規(guī)劃求解模型是線性的，同時我們不選擇“采納線性模型”，規(guī)劃求解將使用一種特不低效的算法（GRG2 方法），同時可能專門難找到該模型的最佳解決方案。單擊“規(guī)劃求解選項”對話框

18、中的“確定”后，我們將返回到主“規(guī)劃求解”對話框。當我們單擊“求解”時，規(guī)劃求解將為我們的產品組合模型計算出一個最佳解決方案（假如有）。產品組合模型問題的最佳解決方案是所有可行解決方案集中可使利潤最大化的一組可變單元格值（生產的每種藥品的磅數(shù)）。同樣，可行解決方案是一組滿足所有約束的可變單元格值。圖 2 中顯示的可變單元格值確實是一個可行解決方案，因為所有產品級不差不多上非負值，產品級不都沒有超出需求，而且資源用量也沒有超出可用資源。圖 2：符合約束的可行的產品組合問題解決方案。由于以下緣故，圖 3 中顯示的可變單元格值代表一個不可行的解決方案：生產的產品 5 的數(shù)量大于需求數(shù)量。使用的人工大

19、于可用人工。使用的原材料大于可用原材料。圖 3：不符合我們定義的約束的不可行的產品組合問題解決方案。單擊“求解”后，規(guī)劃求解會迅速找出最佳解決方案，如圖 4 所示。您需要選擇“保存規(guī)劃求解解決方案”以將最佳解決方案值保留在電子表格中。圖 4：產品組合問題的最佳解決方案。通過生產 596.67 磅的藥品 4、1084 磅的藥品 5 而不生產任何其他藥品，我們的醫(yī)藥公司每月可獲得最高利潤 6,625.20 美元！我們無法確定通過其他方法是否能夠獲得 6,625.20 美元的最高利潤。但我們能夠確定，在我們有限的資源和需求條件下，那個月的利潤全然不可能超出 6,625.20 美元。規(guī)劃求解模型總是有

20、解決方案嗎？假定必須滿足對每種產品的需求，那么我們就必須將我們的需求約束從 D2:I2 = D8:I8。要更改此約束，請 打開規(guī)劃求解。 單擊“D2:I2 =”，然后單擊“確定”?，F(xiàn)在我們能夠確保規(guī)劃求解將只考慮符合所有需求的可變單元格值。當您單擊“求解”時，您將會看到“規(guī)劃求解找不到可行的解決方案”消息。該消息意味著使用我們有限的資源，無法滿足對所有產品的需求。我們的模型并沒有錯！規(guī)劃求解只是要告訴我們，假如我們想要滿足對每種產品的需求，我們就需要增加更多的人工、更多的原材料或兩者都要增加。假如設置目標單元格的值未收斂，意味著什么？讓我們看看假如我們同意對每種藥品無限制的需求，同時同意每種藥

21、品的產量為負，會發(fā)生什么情況。要找出針對這種情況的最佳解決方案，請執(zhí)行以下操作：打開規(guī)劃求解。單擊“選項”按鈕，然后清除“假定非負”復選框。在“規(guī)劃求解參數(shù)”對話框中，單擊需求約束“D2:I2 = D8:I8”，然后單擊“刪除”以刪除該約束。當您單擊“求解”時，規(guī)劃求解將返回“設置目標單元格的值未收斂”消息。該消息意味著假如要最大化目標單元格（像我們的示例中一樣），會存在具有任意大的目標單元格值的可行解決方案。（假如要最小化目標單元格，該消息則意味著存在具有任意小的目標單元格值的可行解決方案。） 在這種情況下，通過同意藥品的產量為負，我們實際上“制造”了可用于生產任意大數(shù)量的其他藥品的資源。假設我們的需求沒有限制，這就使得我們能夠制造無限的利潤。而現(xiàn)實中，我們是不可能制造無限利潤的。簡而言之，假如您看到了“設置目標單元格的值未收斂”，就表示您的模型有錯誤。自我測試s25_1.xls 至 s25_5.xls 文件中提供了這些問題的解決方案，這些文件包含在中。假定我們的醫(yī)藥公司能夠按每小時 1 美元的價格購買了 500 小時的人工。它們將如何利用這次機會？在一家芯片