1、使用規(guī)劃求解確定最佳產(chǎn)品組合什么是 Excel 規(guī)劃求解工具？當(dāng)您想要尋尋找做某某件事的的最佳方方法時(shí)，使使用的就就是規(guī)劃劃求解?；蚧蛘撸?guī)的的說法就就是，當(dāng)當(dāng)您想要要在電子子表格的的某些單單元格中中得到優(yōu)優(yōu)化（最最大化或或最小化化）某個(gè)個(gè)目標(biāo)的的值時(shí)，使使用的就就是規(guī)劃劃求解。優(yōu)化模型包包括三部部分：目目標(biāo)單元元格、可可變單元元格和約約束。目標(biāo)單元格格代表目目的或目目標(biāo)。例例如，最最大化每每月利潤潤。可變單元格格是電子子表格中中我們可可以進(jìn)行行更改或或調(diào)整以以優(yōu)化目目標(biāo)單元元格的單單元格。例例如，每每月每種種產(chǎn)品的的產(chǎn)量。約束是您置置于可變變單元格格中的限限制條件件。例如如，使用用的資

2、源源不能超超標(biāo)，并并且不能能生產(chǎn)過過剩的產(chǎn)產(chǎn)品。如何確定哪哪種產(chǎn)品品組合可可以使利利潤最大大化？公司通常需需要確定定每月（或或每周）生生產(chǎn)計(jì)劃劃，列出出每種產(chǎn)產(chǎn)品必須須生產(chǎn)的的數(shù)量。具具體來說說就是，產(chǎn)品組合問題涉及如何確定在每月應(yīng)該生產(chǎn)的每種產(chǎn)品的數(shù)量以使利潤最大化。產(chǎn)品組合通常必須滿足以下約束：產(chǎn)品組合使使用的資資源不能能超標(biāo)。對(duì)每種產(chǎn)品品的需求求都是有有限的。我我們每月月生產(chǎn)的的產(chǎn)品不不能超過過需求的的數(shù)量，因因?yàn)樯a(chǎn)產(chǎn)過剩就就是浪費(fèi)費(fèi)（例如如，易變變質(zhì)的藥藥品）。讓我們來解解決以下下產(chǎn)品組組合示例例問題。您您可以在在 prrodmmix.xlss 文件件中找到到該問題題的解決決方案（該

3、該文件包包含在 HYPERLINK /zh-cn/assistance/HA011245962052.aspx#SampleFile l SampleFile 示示例文件件下載中中），如如圖 11 所示示。圖 1：產(chǎn)產(chǎn)品組合合示例。假定我們?cè)谠谝患裔t(yī)醫(yī)藥公司司工作，這這家公司司可以在在他們的的工廠生生產(chǎn)六種種產(chǎn)品。生生產(chǎn)每種種產(chǎn)品都都需要人人工和原原材料。 圖 1 的的第 44 行顯顯示了生生產(chǎn)一磅磅的每種種產(chǎn)品所所需的人人工小時(shí)時(shí)數(shù)，第第 5 行顯示示了生產(chǎn)產(chǎn)一磅的每每種產(chǎn)品品所需的的原材料料的磅數(shù)數(shù)。例如如，生產(chǎn)產(chǎn)一磅的產(chǎn)產(chǎn)品 11 需要要 6 小時(shí)人人工和 3.22 磅原原材料。 第 6

4、 行行顯示了了每種藥藥品每磅磅的價(jià)格格，第 7 行行顯示了了每磅的的成本，第第 9 行顯示示每磅可可帶來的的利潤。例例如，產(chǎn)產(chǎn)品 22 的價(jià)價(jià)格是每每磅 111.000 美美元，每每磅的單單位成本本是 55.700 美元元，每磅磅的利潤潤就是 5.330 美美元。 第 8 行行顯示了了該月對(duì)對(duì)每種藥藥品的需需求。例例如，對(duì)對(duì)產(chǎn)品 3 的的需求為為 10041 磅。 該月可提供供 45500 人工工工時(shí)和 16000 磅磅的原材材料。該該公司如如何最大大化它每每月的利利潤？如果我們對(duì)對(duì)規(guī)劃求求解一無無所知，我我們會(huì)通通過構(gòu)建建一個(gè)電電子表格格，然后后在其中中跟蹤每每種產(chǎn)品品組合以以及與該該產(chǎn)品組

5、組合相關(guān)關(guān)聯(lián)的資資源用量量來處理理這一問問題。然然后我們們 會(huì)反反復(fù)試驗(yàn)驗(yàn)、不斷斷地變化化產(chǎn)品組組合以優(yōu)優(yōu)化利潤潤，同時(shí)時(shí)確保使使用的人人工或原原材料不不會(huì)超標(biāo)標(biāo)，并確確保不會(huì)會(huì)生產(chǎn)出出過剩藥藥品。在在此過程程中，我我們只在在反復(fù)試試驗(yàn)階段段中使用用了 規(guī)規(guī)劃求解解。從根根本上來來說，規(guī)規(guī)劃求解解是一個(gè)個(gè)可以完完美地執(zhí)執(zhí)行反復(fù)復(fù)試驗(yàn)搜搜索的優(yōu)優(yōu)化引擎擎。解決產(chǎn)品組組合問題題的關(guān)鍵鍵是有效效地計(jì)算算與任一一給定產(chǎn)產(chǎn)品組合合相關(guān)聯(lián)聯(lián)的資源源用量和和利潤。SSUMPPRODDUCTT 函數(shù)數(shù)是我們們可以用用來執(zhí)行行此計(jì)算算的一個(gè)個(gè)重要工工具。SSUMPPRODDUCTT 函數(shù)數(shù)將單元元格區(qū)域域中相應(yīng)

6、應(yīng)的值相相乘并返返回這些些值的總總和。SSUMPPRODDUCTT 評(píng)估估中使用用的每個(gè)個(gè)單元格格區(qū)域都都必須具具有相同同的維度度，這意意味著您您可以對(duì)對(duì)兩行或或兩列使使用 SSUMPPRODDUCTT，而不不是對(duì)一一列或一一行。作為如何在在產(chǎn)品組組合示例例中使用用 SUUMPRRODUUCT 函數(shù)的的示例，讓讓我們嘗嘗試計(jì)算算一下我我們的資資源用量量。通過過以下計(jì)計(jì)算方式式可以得得出人工工用量：（每磅藥品品 1 使用的的人工）*（生產(chǎn)的藥藥品 11 的磅磅數(shù)）+（每磅藥品品 2 使用的的人工）*（生產(chǎn)的藥藥品 22 的磅磅數(shù)）+.（每磅藥品品 6 使用的的人工）*（生產(chǎn)的藥藥品 66 的磅磅

7、數(shù)）在我們的電電子表格格中，我我們可能能會(huì)通過過 D22*D44+E22*E44+F22*F44+G22*G44+H22*H44+I22*I44 來計(jì)計(jì)算人工工用量（非非常繁鎖鎖）。類類似地，原原材料用用量可以以通過 D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5 計(jì)算。在在電子表表格中對(duì)對(duì)六種產(chǎn)產(chǎn)品分別別輸入這這些公式式是很浪浪費(fèi)時(shí)間間的。想想像一下下，如果果您正在在對(duì)一家家其工廠廠生產(chǎn) 50 種產(chǎn)品品的公司司執(zhí)行這這樣的計(jì)計(jì)算，會(huì)會(huì)花費(fèi)多多長時(shí)間間？ 計(jì)算人工和和原材料料用量的的一種更更為簡(jiǎn)單單的方法法是將 D144 中的的公式復(fù)復(fù)制到 D155 中： SUMPRR

8、ODUUCT($D$2:$I$22,D44:I44)該公式會(huì)計(jì)計(jì)算 DD2*DD4+EE2*EE4+FF2*FF4+GG2*GG4+HH2*HH4+II2*II4（這這是我們們的人工工用量），這這要比手手動(dòng)輸入入簡(jiǎn)單得得多！ 請(qǐng)注意，我我對(duì)區(qū)域域 D22:I22 使用用了 $ 符號(hào)號(hào)，以便便在我復(fù)復(fù)制公式式時(shí)，我我仍然可可以從第第 2 行中取取下產(chǎn)品品組合。單單元格 D155 中的的公式用用于計(jì)算算原材料料用量。類似地，通通過以下下計(jì)算方方式可以以得出我我們的利利潤：（每磅藥品品 1 的利潤潤）*（生產(chǎn)的藥藥品 11 的磅磅數(shù)）+（每磅藥品品 2 的利潤潤）*（生產(chǎn)的藥藥品 22 的磅磅數(shù)）+

9、.（每磅藥品品 6 的利潤潤）*（生產(chǎn)的藥藥品 66 的磅磅數(shù)）。在單元格 D122 中使使用以下下公式可可以很容容易計(jì)算算出利潤潤： SUMPRRODUUCT(D9:I9,$D$2:$I$22)現(xiàn)在我們可可以標(biāo)識(shí)識(shí)出產(chǎn)品品組合規(guī)規(guī)劃求解解模型的的三個(gè)組組成部分分：目標(biāo)單元格格可變單元格格約束我們的目標(biāo)標(biāo)是使利利潤（在在單元格格 D112 中中計(jì)算）最最大化。生產(chǎn)的每種種產(chǎn)品的的磅數(shù)（在在單元格格區(qū)域 D2:I2 中列出出）。使用的人工工和原材材料不能能超標(biāo)。也也就是說說，單元元格 DD14:D155（所用用資源）必必須小于于或等于于單元格格 F114:FF15 中的值值（可用用資源）。生產(chǎn)的

10、藥品品不能超超過需求求數(shù)量。也也就是說說，單元元格 DD2:II2（生生產(chǎn)的每每種藥品品的磅數(shù)數(shù)）必須須小于或或等于對(duì)對(duì)每種藥藥品的需需求（在在單元格格 D88:I88 中列列出）。我們不能生生產(chǎn)任何何產(chǎn)量為為負(fù)的藥藥品。何將此模型型輸入到到規(guī)劃求求解中？現(xiàn)在，我將將向你們們演示如如何將目目標(biāo)單元元格、可可變單元元格和約約束輸入入規(guī)劃求求解。然然后，你你們只需需單擊“求求解”按按鈕即可可，規(guī)劃劃求解將將會(huì)找出出可使利利潤最大大化的產(chǎn)產(chǎn)品組合合。要開始操作作，請(qǐng)選選擇“工工具”菜菜單上的的“規(guī)劃劃求解”。（有關(guān)安裝規(guī)劃求解的說明，請(qǐng)參閱 HYPERLINK /search/redir.aspx?

11、AssetID=HA011245952052&CTT=5&Origin=HA011245962052 使用 Excel 規(guī)劃求解工具進(jìn)行優(yōu)化的說明。） 即會(huì)出現(xiàn)“規(guī)規(guī)劃求解解參數(shù)” 對(duì)話框。要輸入目標(biāo)標(biāo)單元格格，請(qǐng)?jiān)谠凇霸O(shè)置置目標(biāo)單單元格”框中單擊，然后選擇利潤單元格（單元格 D12）。要輸入可變單元格，請(qǐng)?jiān)凇翱勺儐卧瘛笨蛑袉螕簦缓笾赶騾^(qū)域 D2:I2，該區(qū)域包含生產(chǎn)的每種藥品的磅數(shù)。該對(duì)話框現(xiàn)在看起來應(yīng)如下圖所示。現(xiàn)在我們已已經(jīng)可以以向模型型中添加加約束了了。單擊擊“添加加”按鈕鈕，您可可以看到到“添加加約束”對(duì)話框。要添加資源源用量約約束，請(qǐng)請(qǐng)?jiān)跇?biāo)記記為“單單元格引引用位置置”的框框中

12、單擊擊，然后后選擇區(qū)區(qū)域 DD14:D155。從對(duì)對(duì)話框中中部的列列表中選選擇“=”。在在標(biāo)記為為“約束束值”的的框中單單擊，然然后選擇擇單元格格區(qū)域 F144:F115。現(xiàn)在我們已已經(jīng)確保保當(dāng)規(guī)劃劃求解嘗嘗試對(duì)可可變單元元格使用用不同的的值時(shí)，規(guī)規(guī)劃求解解將只考考慮同時(shí)時(shí)滿足 D144 = F114（所所用人工工小于或或等于可可用人工工）和 D155 = F115（所所用原材材料小于于或等于于可用原原材料）的的組合。 現(xiàn)在，在“添添加約束束”對(duì)話話框中單單擊“添添加”，以以輸入需需求約束束。只需需如下圖圖所示填填充“添添加約束束”對(duì)話話框即可可。添加這些約約束可以以確保當(dāng)當(dāng)規(guī)劃求求解嘗試試對(duì)

13、可變變單元格格值使用用不同的的組合時(shí)時(shí)，規(guī)劃劃求解將將只考慮慮滿足以以下條件件的組合合：D2 = D88（藥品品 1 的產(chǎn)量量小于或或等于對(duì)對(duì)藥品 1 的的需求量量）E2 = E88（藥品品 2 的產(chǎn)量量小于或或等于對(duì)對(duì)藥品 2 的的需求量量）F2 = F88（藥品品 3 的產(chǎn)量量小于或或等于對(duì)對(duì)藥品 3 的的需求量量）G2 = G88（藥品品 4 的產(chǎn)量量小于或或等于對(duì)對(duì)藥品 4 的的需求量量）H2 = H88（藥品品 5 的產(chǎn)量量小于或或等于對(duì)對(duì)藥品 5 的的需求量量）I2 = I88（藥品品 6 的產(chǎn)量量小于或或等于對(duì)對(duì)藥品 6 的的需求量量）單擊“添加加約束”對(duì)話框中的“確定”?！耙?guī)劃

14、求解參數(shù)”對(duì)話框應(yīng)如下圖所示。在“規(guī)劃求求解選項(xiàng)項(xiàng)”對(duì)話話框中輸輸入所有有可變單單元格都都為非負(fù)負(fù)值的約約束，通通過單擊擊“規(guī)劃劃求解參參數(shù)”對(duì)對(duì)話框中中的“選選項(xiàng)”按按鈕可打打開該對(duì)對(duì)話框。 選擇“采用用線性模模型”和和“假定定非負(fù)”選項(xiàng)，然后單擊“確定”。 HYPERLINK javascript:ToggleDiv(divExpCollAsst_1) HYPERLINK javascript:ToggleDiv(divExpCollAsst_1) 為什么要選選擇這些些選項(xiàng)？選擇“假定定非負(fù)”選項(xiàng)可確保規(guī)劃求解只考慮每個(gè)可變單元格都采用非負(fù)值的可變單元格組合。 選擇“采用用線性模模型”的的

15、原因是是產(chǎn)品組組合問題題是一種種稱為線線性模型型的特殊殊規(guī)劃求求解問題題。基本本上，在在以下情情況下，規(guī)規(guī)劃求解解模型都都是線性性模型：目標(biāo)單元格格是通過過將表單單的條件件（可變變單元格格）*（約約束）相相加進(jìn)行行計(jì)算的的。每種約束都都滿足線線性模型型要求。這這意味著著每種約約束都是是通過將將表單的的條件（可可變單元元格）*（約束束）相加加，然后后將這些些總和與與某個(gè)常常量進(jìn)行行比較來來評(píng)估的的。這個(gè)規(guī)劃求求解問題題為什么么是線性性的？ 我們的目標(biāo)標(biāo)單元格格（利潤潤）計(jì)算算方式為為： （每磅藥品品 1 的利潤潤）*（生產(chǎn)的藥藥品 11 的磅磅數(shù)）+（每磅藥品品 2 的利潤潤）*（生產(chǎn)的藥藥品

16、22 的磅磅數(shù)）+.（每磅藥品品 6 的利潤潤）*（生產(chǎn)的藥藥品 66 的磅磅數(shù)）這種計(jì)算方方式遵循循一種模模式，即即目標(biāo)單單元格的的值是通通過將表表單的各各個(gè)條件件（可變變單元格格）*（約約束）相相加得出出的.我們的人工工約束是是通過將將可用人人工與通通過以下下公式得得出的值值進(jìn)行比比較來評(píng)評(píng)估的： （每磅藥品品 1 使用的的人工）*（生產(chǎn)的藥藥品 11 的磅磅數(shù)）+ （每磅藥品品 2 使用的的人工）*（生產(chǎn)的藥藥品 22 的磅磅數(shù)）+ .（每磅藥品品 6 使用的的人工）*（生產(chǎn)的藥藥品 66 的磅磅數(shù)）因此，人工工約束是是通過將將表格的的各個(gè)條條件（可可變單元元格）*（約束束）相加加，然后

17、后將這類類總和與與某個(gè)常常量進(jìn)行行比較來來評(píng)估的的。人工工約束和和原材料料約束都都滿足線線性模型型要求。我們的需求求約束采采用以下下形式：（藥品 11 的產(chǎn)產(chǎn)量）=（藥藥品 11 的需需求量）（藥品 22 的產(chǎn)產(chǎn)量）=（藥藥品 22 的需需求量）.（藥品 66 的產(chǎn)產(chǎn)量）=（藥藥品 66 的需需求量）每種需求約約束還都都符合線線性模型型要求，因因?yàn)槊糠N種約束都都是通過過將表單單的各個(gè)個(gè)條件（可可變單元元格）*（約束束）相加加，然后后將這些些總和與與某個(gè)常常量進(jìn)行行比較來來評(píng)估的的。已經(jīng)表明我我們的產(chǎn)產(chǎn)品組合合模型是是線性模模型，我我們還要要關(guān)心什什么？如果規(guī)劃求求解模型型是線性性的，并并且我們

18、們選擇“采采用線性性模型”，則規(guī)劃求解保證可以找到規(guī)劃求解模型的最佳解決方案。如果規(guī)劃求解模型不是線性的，則規(guī)劃求解可能可以找到最佳解決方案，也可能找不到。如果規(guī)劃求求解模型型是線性性的，并并且我們們選擇“采采用線性性模型”，規(guī)劃求解將使用一種非常高效的算法（單工方法）來找到該模型的最佳解決方案。如果規(guī)劃求解模型是線性的，并且我們不選擇“采用線性模型”，規(guī)劃求解將使用一種非常低效的算法（GRG2 方法），并且可能很難找到該模型的最佳解決方案。單擊“規(guī)劃劃求解選選項(xiàng)”對(duì)對(duì)話框中中的“確確定”后后，我們們將返回回到主“規(guī)規(guī)劃求解解”對(duì)話話框。當(dāng)當(dāng)我們單單擊“求求解”時(shí)時(shí)，規(guī)劃劃求解將將為我們們的產(chǎn)

19、品品組合模模型計(jì)算算出一個(gè)個(gè)最佳解解決方案案（如果果有）。產(chǎn)品組合模模型問題題的最佳佳解決方方案是所所有可行行解決方方案集中中可使利利潤最大大化的一一組可變變單元格格值（生生產(chǎn)的每每種藥品品的磅數(shù)數(shù)）。同同樣，可可行解決決方案是是一組滿滿足所有有約束的的可變單單元格值值。圖 2 中中顯示的的可變單單元格值值就是一一個(gè)可行行解決方方案，因因?yàn)樗杏挟a(chǎn)品級(jí)級(jí)別都是是非負(fù)值值，產(chǎn)品品級(jí)別都都沒有超超出需求求，而且且資源用用量也沒沒有超出出可用資資源。圖 2：符符合約束束的可行行的產(chǎn)品品組合問問題解決決方案。由于以下原原因，圖圖 3 中顯示示的可變變單元格格值代表表一個(gè)不不可行的的解決方方案：生產(chǎn)的產(chǎn)

20、品品 5 的數(shù)量量大于需需求數(shù)量量。使用的人工工大于可可用人工工。使用的原材材料大于于可用原原材料。圖 3：不不符合我我們定義義的約束束的不可可行的產(chǎn)產(chǎn)品組合合問題解解決方案案。單擊“求解解”后，規(guī)規(guī)劃求解解會(huì)迅速速找出最最佳解決決方案，如如圖 44 所示示。您需需要選擇擇“保存存規(guī)劃求求解解決決方案”以將最佳解決方案值保留在電子表格中。圖 4：產(chǎn)產(chǎn)品組合合問題的的最佳解解決方案案。通過生產(chǎn) 5966.677 磅的的藥品 4、110844 磅的的藥品 5 而而不生產(chǎn)產(chǎn)任何其其他藥品品，我們們的醫(yī)藥藥公司每每月可獲獲得最高高利潤 6,6625.20 美元！我們無無法確定定通過其其他方法法是否可可

21、以獲得得 6,6255.200 美元元的最高高利潤。但但我們可可以確定定，在我我們有限限的資源源和需求求條件下下，這個(gè)個(gè)月的利利潤根本本不可能能超出 6,6625.20 美元。規(guī)劃求解模模型總是是有解決決方案嗎嗎？假定必須滿滿足對(duì)每每種產(chǎn)品品的需求求，那么么我們就就必須將將我們的的需求約約束從 D2:I2 = D88:I88。要更更改此約約束，請(qǐng)請(qǐng) 打開規(guī)劃求求解。 單擊“D22:I22 =”，然然后單擊擊“確定定”。現(xiàn)在我們可可以確保保規(guī)劃求求解將只只考慮符符合所有有需求的的可變單單元格值值。當(dāng)您單擊“求求解”時(shí)時(shí)，您將將會(huì)看到到“規(guī)劃劃求解找找不到可可行的解解決方案案”消息息。該消消息意味

22、味著使用用我們有有限的資資源，無無法滿足足對(duì)所有有產(chǎn)品的的需求。我我們的模模型并沒沒有錯(cuò)！規(guī)劃求求解只是是要告訴訴我們，如如果我們們想要滿滿足對(duì)每每種產(chǎn)品品的需求求，我們們就需要要增加更更多的人人工、更更多的原原材料或或兩者都都要增加加。如果設(shè)置目目標(biāo)單元元格的值值未收斂斂，意味味著什么么？讓我們看看看如果我我們?cè)试S許對(duì)每種種藥品無無限制的的需求，并并且允許許每種藥藥品的產(chǎn)產(chǎn)量為負(fù)負(fù)，會(huì)發(fā)發(fā)生什么么情況。要要找出針針對(duì)這種種情況的的最佳解解決方案案，請(qǐng)執(zhí)執(zhí)行以下下操作：打開規(guī)劃求求解。單擊“選項(xiàng)項(xiàng)”按鈕鈕，然后后清除“假假定非負(fù)負(fù)”復(fù)選選框。在“規(guī)劃求求解參數(shù)數(shù)”對(duì)話話框中，單單擊需求求約束“

23、DD2:II2 = DD8:II8”，然然后單擊擊“刪除除”以刪刪除該約約束。當(dāng)您單擊“求求解”時(shí)時(shí)，規(guī)劃劃求解將將返回“設(shè)置目目標(biāo)單元元格的的值未收收斂”消消息。該該消息意意味著如如果要最最大化目目標(biāo)單元元格（像像我們的的示例中中一樣），會(huì)會(huì)存在具具有任意意大的目目標(biāo)單元元格值的的可行解解決方案案。（如如果要最最小化目目標(biāo)單元元格，該該消息則則意味著著存在具具有任意意小的目目標(biāo)單元元格值的的可行解解決方案案。） 在這種情況況下，通通過允許許藥品的的產(chǎn)量為為負(fù)，我我們實(shí)際際上“創(chuàng)創(chuàng)造”了了可用于于生產(chǎn)任任意大數(shù)數(shù)量的其其他藥品品的資源源。假設(shè)設(shè)我們的的需求沒沒有限制制，這就就使得我我們可以以創(chuàng)

24、造無無限的利利潤。而而現(xiàn)實(shí)中中，我們們是不可可能創(chuàng)造造無限利利潤的。簡(jiǎn)簡(jiǎn)而言之之，如果果您看到到了“設(shè)置目目標(biāo)單元元格的的值未收收斂”，就就表示您您的模型型有錯(cuò)誤誤。自我測(cè)試s25_11.xlls 至至 s225_55.xlls 文文件中提提供了這這些問題題的解決決方案，這這些文件件包含在在 HYPERLINK /zh-cn/assistance/HA011245962052.aspx#SampleFile l SampleFile 示例文文件下載載中。假定我們的的醫(yī)藥公公司可以以按每小小時(shí) 11 美元元的價(jià)格格購買了了 5000 小小時(shí)的人人工。它它們將如如何利用用這次機(jī)機(jī)會(huì)？在一家芯片片制造廠廠，有四四位技術(shù)術(shù)員（AA、B、CC 和 D）生生產(chǎn)三種種產(chǎn)品（產(chǎn)產(chǎn)品 11、2 和 33）。芯芯片制造造商每月月可以銷銷售 880