項(xiàng)目八 制定生產(chǎn)規(guī)劃方案

項(xiàng)目八制定生產(chǎn)規(guī)劃方案【學(xué)習(xí)目標(biāo)】了解什么是規(guī)劃求解熟悉規(guī)劃求解的操作過程能夠利用規(guī)劃求解解決最值問題任務(wù)利用Excel中所提供的“規(guī)劃求解”功能來解決玩具廠的最值問題。這個(gè)項(xiàng)目主要分解為四個(gè)基本任務(wù)，它們分別是：加載規(guī)劃求解應(yīng)用規(guī)劃求解修改項(xiàng)目約束條件建立分析報(bào)告假設(shè)某玩具生產(chǎn)廠，在購入了新配件后，準(zhǔn)備用來生產(chǎn)語音娃娃、電動(dòng)小汽車和電動(dòng)機(jī)器人三種不同類型的玩具。其中生產(chǎn)語音娃娃需要的成本為45元，花費(fèi)的時(shí)間為28分鐘，可獲利32元；生產(chǎn)電動(dòng)小汽車的成本為55元，花費(fèi)的時(shí)間為30分鐘，可獲利35元；生產(chǎn)電動(dòng)機(jī)器人需要的成本為60元，花費(fèi)的時(shí)間為35分鐘，可獲利38元。由于資金限制，規(guī)定每天投入到生產(chǎn)之中的金額不得超過1千元，并且每天最多工作10小時(shí)。為了獲取最大利潤(rùn)，玩具廠每天將如何分配這3款玩具的生產(chǎn)。任務(wù)一

加載規(guī)劃求解規(guī)劃求解是一個(gè)功能強(qiáng)大的分析工具，可以解決方程組、線性規(guī)劃以及運(yùn)籌學(xué)等領(lǐng)域的問題。特別是在實(shí)際應(yīng)用中常涉及到很多優(yōu)化問題，比如求最小成本、最大利潤(rùn)、線性回歸和非線性回歸等問題。一般來說，在第一次安裝Excel時(shí)并不安裝規(guī)劃求解。因此，我們需要先加載它才可以使用。任務(wù)一

加載規(guī)劃求解【步驟解析】打開

按鈕，在下拉菜單中單擊

按鈕，彈出【Excel選項(xiàng)】對(duì)話框，選擇該對(duì)話框左側(cè)列表中的【加載項(xiàng)】選項(xiàng)，如圖8.2所示。在對(duì)話框下方的【管理】框中，選擇“Excel加載項(xiàng)”，然后單擊按鈕，彈出【加載宏】對(duì)話框。在【可用加載宏】列表框中，選中【規(guī)劃求解加載項(xiàng)】復(fù)選框，如圖8.3所示。詳細(xì)介紹操作步驟單擊

按鈕，便完成了加載，此時(shí)在【數(shù)據(jù)】選項(xiàng)卡的【分析】組中可以找到

按鈕，如圖8.4所示。任務(wù)二

應(yīng)用規(guī)劃求解（一）

分析約束條件根據(jù)本章開頭所介紹的玩具廠的實(shí)際生產(chǎn)情況分析，將所給出的前提條件進(jìn)行整理，便得到了以下幾個(gè)約束條件：成本約束條件：45*X+55*Y+60*Z≤10000生產(chǎn)時(shí)間約束條件：

25*X+30*Y+35*Z≤10*60生產(chǎn)數(shù)量約束條件：X≥1，Y≥1，Z≥1，且都為整數(shù)其中，X、Y、Z分別對(duì)應(yīng)語音娃娃、電動(dòng)小汽車和電動(dòng)機(jī)器人的產(chǎn)量。任務(wù)二

應(yīng)用規(guī)劃求解（二）

建立工作表在分析完約束條件后，可以建立一張如圖8.5所示的工作表。任務(wù)二

應(yīng)用規(guī)劃求解【步驟解析】在單元格D7中設(shè)置“實(shí)際生產(chǎn)成本”公式，實(shí)際成本等于每款的生產(chǎn)成本乘以其產(chǎn)量，輸入：=B3*E3+B4*E4+B5*E5，按Enter鍵確認(rèn)即可，如圖8.6所示。在單元格D8中設(shè)置“實(shí)際生產(chǎn)時(shí)間”公式，實(shí)際時(shí)間等于每款的生產(chǎn)時(shí)間乘以其產(chǎn)量，輸入：=C3*E3+C4*E4+C5*E5，按Enter鍵確認(rèn)即可，如圖8.7所示。在單元格B9中設(shè)置“每天銷售利潤(rùn)”公式，每天銷售利潤(rùn)等于每款的利潤(rùn)乘以其產(chǎn)量，輸入：=D3*E3+D4*E4+D5*E5，按Enter鍵確認(rèn)即可，如圖8.8所示。任務(wù)二

應(yīng)用規(guī)劃求解（三）

進(jìn)行規(guī)劃求解【步驟解析】

選擇【數(shù)據(jù)】選項(xiàng)卡中的【分析】組，然后單擊該組中的

按鈕，彈出如圖8.9所示的對(duì)話框。在其中的【設(shè)置目標(biāo)單元格】中鍵入“$B$9”，表明每天的銷售利潤(rùn)為規(guī)劃求解的目標(biāo)值；在緊接著下方的單選項(xiàng)中選中“最大值”選項(xiàng)；在【可變單元格】中鍵入“$E$3:$E$5”，表明三款玩具的產(chǎn)量是規(guī)劃求解所要求取的變量值。如圖8.10所示。任務(wù)二

應(yīng)用規(guī)劃求解在對(duì)話框的【約束】列表框中，添加成本約束條件，即“實(shí)際成本”小于等于“生產(chǎn)成本限制”。單擊【約束】列表框右側(cè)的

按鈕，彈出【添加約束】對(duì)話框，在其中的【單元格引用位置】文本編輯框中鍵入“$D$7”，然后在其右側(cè)的符號(hào)下拉列表中選擇“≤”項(xiàng)，在【約束值】文本編輯框中鍵入“$B$7”，然后單擊

按鈕即可，如圖8.11所示。在【添加約束】對(duì)話框中添加時(shí)間約束條件，即“實(shí)際時(shí)間”小于等于“生產(chǎn)時(shí)間限制”。在對(duì)話框中的【單元格引用位置】文本編輯框中鍵入“$D$8”，然后在其右側(cè)的符號(hào)下拉列表中選擇“≤”項(xiàng)，在【約束值】文本編輯框中鍵入“$B$8”，然后單擊

按鈕即可，如圖8.12所示。任務(wù)二

應(yīng)用規(guī)劃求解在【添加約束】對(duì)話框中添加數(shù)量約束條件，即“生產(chǎn)數(shù)量”大于等于“1”。在對(duì)話框中的【單元格引用位置】文本編輯框中鍵入“$E$3:$E$5”，然后在其右側(cè)的符號(hào)下拉列表中選擇“≥”項(xiàng)，在【約束值】文本編輯框中鍵入“1”，然后單擊

按鈕即可，如圖8.13所示。在【添加約束】對(duì)話框中繼續(xù)添加數(shù)量約束條件，即“生產(chǎn)數(shù)量”均為“整數(shù)”。在對(duì)話框中的【單元格引用位置】文本編輯框中鍵入“$E$3:$E$5”，然后在其右側(cè)的符號(hào)下拉列表中選擇“int”項(xiàng)，在【約束值】文本編輯框中顯示“整數(shù)”，然后單擊

按鈕即可，如圖8.14所示。任務(wù)二

應(yīng)用規(guī)劃求解返回【規(guī)劃求解參數(shù)】對(duì)話框，此時(shí)在【約束】列表框中顯示了所有的約束條件，如圖8.15所示，然后單擊

按鈕。彈出【規(guī)劃求解結(jié)果】對(duì)話框，此時(shí)結(jié)果也顯示到工作表中。選中【保存規(guī)劃求解結(jié)果】單選項(xiàng)，單擊

按鈕即可，如圖8.16所示。返回工作表，在“產(chǎn)量”項(xiàng)中顯示出生產(chǎn)三款玩具的個(gè)數(shù)分別為：15、4、1，每天的銷售利潤(rùn)為￥658.00，此時(shí)實(shí)際生產(chǎn)成本為￥955.00，實(shí)際生產(chǎn)時(shí)間為575分鐘，如圖8.17所示。任務(wù)三

修改項(xiàng)目約束條件現(xiàn)在玩具廠接到一批訂購電動(dòng)小汽車的訂單，要求廠家每天生產(chǎn)的電動(dòng)機(jī)器人數(shù)目必須大于8個(gè)。此時(shí)需要對(duì)先前所建立的約束條件進(jìn)行修改，具體操作步驟如下所示?！静襟E解析】在【規(guī)劃求解參數(shù)】對(duì)話框的【約束】列表框中選中“$E$3:$E$5>=1”約束條件，然后單擊按鈕，如圖8.18所示。彈出【改變約束】對(duì)話框，在【單元格引用位置】文本編輯框中鍵入“$E$3:$E$4”，然后單擊

按鈕，如圖8.19所示。任務(wù)三

修改項(xiàng)目約束條件返回【規(guī)劃求解參數(shù)】對(duì)話框，單擊

按鈕，在彈出的【添加約束】對(duì)話框中設(shè)置新的約束條件“$E$5>=8”，如圖8.20所示，然后單擊

按鈕。返回【規(guī)劃求解參數(shù)】對(duì)話框，單擊

按鈕，然后在彈出的【規(guī)劃求解結(jié)果】對(duì)話框中選中【保存規(guī)劃求解結(jié)果】單選項(xiàng)，單擊

按鈕即可，計(jì)算結(jié)果如圖8.21所示。任務(wù)四建立分析報(bào)告（一）

建立運(yùn)算結(jié)果報(bào)告【步驟解析】在【規(guī)劃求解結(jié)果】對(duì)話框中的【報(bào)告】列表框中，選中“運(yùn)算結(jié)果報(bào)告”，然后單擊

按鈕，如圖8.23所示。任務(wù)四建立分析報(bào)告（二）

建立敏感性報(bào)告【步驟解析】在【規(guī)劃求解結(jié)果】對(duì)話框中的【報(bào)告】列表框中，選中“敏感性報(bào)告”，然后單擊

按鈕，如圖8.25所示。此時(shí)系統(tǒng)會(huì)彈出對(duì)話框，提醒用戶由于采用了整數(shù)約束，不能產(chǎn)生敏感性報(bào)告和極限值報(bào)告，如圖8.26所示。然后打開【規(guī)劃求解參數(shù)】對(duì)話框，選中“$E$3:$E$5=整數(shù)”約束項(xiàng)，單擊

按鈕，將這條約束刪除。任務(wù)四建立分析報(bào)告再次執(zhí)行步驟(1)的操作，系統(tǒng)將生成一張“敏感性報(bào)告”工作表，該表中顯示了可變單元格和約束值的細(xì)微變化，如圖8.27所示。含有整數(shù)約束條件的求解模型，是不能產(chǎn)生“敏感性報(bào)告”和“極限值報(bào)告”的。任務(wù)四建立分析報(bào)告（三）

建立極限值報(bào)告【步驟解析】在