Excel模擬運算表

1、EXCELEXCEL基礎及應基礎及應用用湖北生物科技職業(yè)學院湖北生物科技職業(yè)學院劉英劉英 Excel Excel數據分析工具及應用數據分析工具及應用 數據分析工具的安裝與卸載數據分析工具的安裝與卸載 模擬運算表模擬運算表 方案分析方案分析 1234 規(guī)劃求解規(guī)劃求解 1 1 數據分析工具的安裝與卸載數據分析工具的安裝與卸載1.1 1.1 安裝分析工具庫安裝分析工具庫“分析工具庫分析工具庫”是一個加載宏文件，文件名是一個加載宏文件，文件名為為Analys32.xll（加載宏文件是可以自動（加載宏文件是可以自動執(zhí)行一系列復雜任務的操作命令的組合）。執(zhí)行一系列復雜任務的操作命令的組合）。但這一工作在

2、安裝但這一工作在安裝Microsoft Office組件組件時已經加載。時已經加載。v安裝步驟如下安裝步驟如下打開打開Excel工作簿，選擇菜單欄中【工具】工作簿，選擇菜單欄中【工具】菜單中的【加載宏】，如圖菜單中的【加載宏】，如圖5-1所示。所示。 圖圖1 1加載宏加載宏在打開在打開“加載宏加載宏”對話框，對話框如圖對話框，對話框如圖5-2所示，單擊【分析工具庫】復選框，框前所示，單擊【分析工具庫】復選框，框前即出現即出現號，表明已被選定，選擇【確定】。號，表明已被選定，選擇【確定】。 圖圖2選擇分析工具庫選擇分析工具庫1.2 1.2 卸載分析工具庫卸載分析工具庫v當不需要使用分析工具庫時，

3、可以通過設置當不需要使用分析工具庫時，可以通過設置來卸載分析工具庫。來卸載分析工具庫。v操作步驟：操作步驟：打開打開Excel工作簿，選擇菜單欄中【工具】工作簿，選擇菜單欄中【工具】菜單中的【加載宏】。菜單中的【加載宏】。在打開在打開“加載宏加載宏”對話框，對話框如圖對話框，對話框如圖4所所示，單擊【分析工具庫】復選框，將框前出示，單擊【分析工具庫】復選框，將框前出現現號取消。號取消。選擇取消后，單擊【確定】按鈕，即可自動選擇取消后，單擊【確定】按鈕，即可自動卸載分析工具庫。卸載分析工具庫。此時在此時在“工具工具”菜單中的菜單中的“數據分析數據分析”選項自動隱藏。選項自動隱藏。圖圖5-45-4

4、卸載分析工具庫卸載分析工具庫5.2 模擬運算表模擬運算表v什么是模擬運算表？什么是模擬運算表？是對工作表中是對工作表中一個單元格區(qū)域內的數據一個單元格區(qū)域內的數據進行進行模擬運算，測試使用模擬運算，測試使用一個一個或或兩個變量兩個變量的的公式公式中變量對運算結果的影響。中變量對運算結果的影響。模擬運算表的類型模擬運算表的類型基于一個輸入變量的表，用這個輸入變量基于一個輸入變量的表，用這個輸入變量測試它對多個公式的影響；測試它對多個公式的影響；單變量模單變量模擬運算表擬運算表基于兩個輸入變量的表，用這兩個變量測基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影響試它們對于單個公式的影響雙

5、變量模雙變量模擬運算表擬運算表2.1 單變量模擬運算表單變量模擬運算表 【例【例5-1】某人想買房，向銀行貸款某人想買房，向銀行貸款30萬，還萬，還款年限為款年限為10年，目前年利率假設為年，目前年利率假設為4%，采，采取每月等額還款的方式。取每月等額還款的方式。 請幫助該買房人計算在現有利率下每月的償請幫助該買房人計算在現有利率下每月的償還額。還額。 請幫助該買房人計算不同利率條件下的每月請幫助該買房人計算不同利率條件下的每月償還額。償還額。 請幫助該買房人計算出還款期限是請幫助該買房人計算出還款期限是5年、年、10年、年、15年、年、20年、年、25年和年和30年時，每月等額年時，每月等額

6、還款的數額。還款的數額。 單變量模擬運算表主要是用來分析當其他因素單變量模擬運算表主要是用來分析當其他因素不變時，一個參數的變化對目標結果的影響。不變時，一個參數的變化對目標結果的影響。 v問題問題求解求解：可利用：可利用PMT函數計算。函數計算。 (1) 建立一新工作簿，并選擇一張工作表，建立一新工作簿，并選擇一張工作表，將住房貸款有關的基本數據輸入該工作表將住房貸款有關的基本數據輸入該工作表（B4:D8）單元格區(qū)域所示，）單元格區(qū)域所示，(2) 在在D9單元格中輸入公式單元格中輸入公式“=PMT(D5/12,D8,D4)”，按下【，按下【Enter】鍵，計算出所要的結果。鍵，計算出所要的結

7、果。圖圖5-55-5住房貸款還款的住房貸款還款的ExcelExcel分析圖分析圖 第一個參數是月利第一個參數是月利率率第二個參數是還款第二個參數是還款期期第三個參數是貸款第三個參數是貸款金額金額v問題問題求解求解：可利用模擬運算表計算：可利用模擬運算表計算不同利率下的月償還額。不同利率下的月償還額。 輸入貸款期各種可能的不同利率數據，輸入貸款期各種可能的不同利率數據，如圖如圖5-6（F4:F16）單元格區(qū)域所示。）單元格區(qū)域所示。圖圖5-65-6住房貸款還款的住房貸款還款的ExcelExcel模擬運算表分析圖模擬運算表分析圖 v選擇包含公式和需要進行模擬運算目標單選擇包含公式和需要進行模擬運算

8、目標單元區(qū)域（元區(qū)域（F3:G16），如圖），如圖5-7所示。所示。圖圖5-75-7住房貸款還款的住房貸款還款的ExcelExcel模擬運算表分析圖模擬運算表分析圖v單擊【數據】單擊【數據】【模擬運算表】命令，彈出【模擬運算表】命令，彈出如圖如圖5-8所示的對話框。在所示的對話框。在“輸入引用列的輸入引用列的單元格單元格”中輸入中輸入$D$5，再單擊【確定】按鈕，再單擊【確定】按鈕，圖圖5-85-8住房貸款還款的住房貸款還款的ExcelExcel模擬運算表分析圖模擬運算表分析圖圖圖5-95-9模擬運算表工具使用示意圖模擬運算表工具使用示意圖各分析值自動填入分析表中，各分析值自動填入分析表中，如

9、圖如圖5-9（ G4：G16 ）單元）單元格區(qū)域所示。格區(qū)域所示。2.2 雙變量模擬運算表雙變量模擬運算表 v對于問題對于問題分析不同的利率和不同的貸款期分析不同的利率和不同的貸款期限對貸款的償還額的影響，這時需要使用雙限對貸款的償還額的影響，這時需要使用雙變量模擬運算表。變量模擬運算表。v問題問題求解求解：v 選擇某個單元格區(qū)域作為模擬運算表存放區(qū)選擇某個單元格區(qū)域作為模擬運算表存放區(qū)域，域，在該區(qū)域的最左列輸入假設的利率變化在該區(qū)域的最左列輸入假設的利率變化范圍數據；在該區(qū)域的第一行輸入可能的貸范圍數據；在該區(qū)域的第一行輸入可能的貸款年限數據。如圖款年限數據。如圖5-10（B11:G11）

10、單元格）單元格區(qū)域所示。區(qū)域所示。 圖圖5-105-10模擬運算表工具使用示意圖模擬運算表工具使用示意圖在模擬運算表區(qū)域的左上角單元格輸入計算月在模擬運算表區(qū)域的左上角單元格輸入計算月償還額的計算公式。選定整個模擬運算表區(qū)域。償還額的計算公式。選定整個模擬運算表區(qū)域。如圖如圖5-11（A11:G24）單元格區(qū)域所示。）單元格區(qū)域所示。 圖圖5-115-11模擬運算表工具使用示意圖模擬運算表工具使用示意圖 v單擊數據菜單中的模擬運算表命令。在模單擊數據菜單中的模擬運算表命令。在模擬運算表對話框（如圖擬運算表對話框（如圖5-12）的輸入引用）的輸入引用行的單元格框中輸入行的單元格框中輸入“$C$6

11、”（年份）；（年份）；在輸入引用列的單元格框中輸入在輸入引用列的單元格框中輸入“$C$5”（年利率）（年利率）,單擊【確定】。單擊【確定】。 v 雙變量模擬運算表的計算結果如圖雙變量模擬運算表的計算結果如圖513所示。其中所示。其中B12：G24單元格區(qū)域的計算公單元格區(qū)域的計算公式為式為“=表表(C6,C5) ”，表示其是一個以，表示其是一個以C6為行變量，為行變量，C5為列變量的模擬運算表。為列變量的模擬運算表。單變量模擬運算表單變量模擬運算表v假設有一個制衣廠，生產的褲子單價假設有一個制衣廠，生產的褲子單價100元，元，其中單位人工費其中單位人工費10元，元， 單位材料費單位材料費15元

12、，元， 單位產品制造過程中所耗費的水電費單位產品制造過程中所耗費的水電費10元元,可可做表如下：做表如下：單變量模擬運算表單變量模擬運算表vB2=B3+B4+B5, vB7=B1B2vB8=B9+B10+B11vB14=B13B7vB15=B13B1vB16=B8+B13B2vB17=B15B16v邊際貢獻總值邊際貢獻總值= =單位單位邊際貢獻邊際貢獻 銷量，銷量，盈虧平衡量盈虧平衡量= =固定成固定成本本/ /單位邊際貢獻，單位邊際貢獻， 即即B19=B8B7單變量模擬運算表單變量模擬運算表v其中：其中： C1到到F1的標題手的標題手工填入，工填入，v輸入輸入C2=0 和和C3=1500時的

13、值時的值v選擇選擇C1到到F3，然，然后按工具欄上的后按工具欄上的 圖表向導。圖表向導。 選擇選擇XY散點圖，散點圖，v子圖表類型選擇子圖表類型選擇 無數據點折線散無數據點折線散點圖，點圖， 然后按一然后按一步步2.3 單變量求解單變量求解v什么是單變量求解？什么是單變量求解？單變量求解就是求解只有一個變量的方程單變量求解就是求解只有一個變量的方程的根，方程可以是線性方程，也可以是非的根，方程可以是線性方程，也可以是非線性方程。線性方程。 【例【例5-2】某公司想向銀行貸款某公司想向銀行貸款900萬元人民幣，萬元人民幣，貸款利率是貸款利率是8%，貸款限期為，貸款限期為5年，如果公司年，如果公司

14、每年可償還每年可償還100萬元，該公司最多可貸款多萬元，該公司最多可貸款多少金額？少金額？(1) 在工作表中輸入原始數據，如圖在工作表中輸入原始數據，如圖5-15； 圖圖5-155-15貸款總額計算貸款總額計算(2) 建立可變數公式；建立可變數公式；在單元格在單元格B2中輸入公式中輸入公式=PMT(B1,B3,B4)”；(3) 設置求解公式：菜單【工具】設置求解公式：菜單【工具】單變量求單變量求解解對話框中輸入：目標單元格、目標值、對話框中輸入：目標單元格、目標值、可變單元格，如圖可變單元格，如圖5-16所示。所示。圖圖5-165-16【單變量求解】對話框【單變量求解】對話框 3 方案分析方案

15、分析 v方案，就是已命名的一組輸入值，這組輸入方案，就是已命名的一組輸入值，這組輸入值保存在工作表中，并可用來自動替換某個值保存在工作表中，并可用來自動替換某個計算模型的輸入值，用來預測模型的輸出結計算模型的輸入值，用來預測模型的輸出結果。對于同一解題方案的模型參數，可以創(chuàng)果。對于同一解題方案的模型參數，可以創(chuàng)建多組不同的參數值，得到各組不同的結論，建多組不同的參數值，得到各組不同的結論，每組參數和結論都是一個方案。每組參數和結論都是一個方案。 【例【例5-3】某化工企業(yè)生產產品某化工企業(yè)生產產品 乙烯、乙烯、 丙烯、丙烯、丁二烯，在丁二烯，在2008年的銷售額分別為年的銷售額分別為400萬元

16、、萬元、600萬元和萬元和300萬元，銷售成本分別為萬元，銷售成本分別為300萬萬元、元、380萬元和萬元和220萬元。根據市場情況推萬元。根據市場情況推測，測，2009年產品的銷售情況有好、一般和年產品的銷售情況有好、一般和差三種情況，每種情況下的銷售額及銷售成差三種情況，每種情況下的銷售額及銷售成本的增長率如圖本的增長率如圖5-17所示。所示。圖圖5-175-17產品銷售資料及預計增長率產品銷售資料及預計增長率 3.1 建立方案建立方案（1）設計方案計算分析，如圖）設計方案計算分析，如圖5-17所示，選擇單元所示，選擇單元格格H7并輸入公式并輸入公式“=SUMPRODUCT(B3:B5,1

17、+H4:H6)-SUMPRODUCT(C3:C5,1+I4:I6)”。（2）對不同的產品進行命名，以便分析。分別將單）對不同的產品進行命名，以便分析。分別將單元格元格H4的名字為的名字為“乙烯銷售額增長率乙烯銷售額增長率”，單元格，單元格I4的名字為的名字為“乙烯銷售成本增長率乙烯銷售成本增長率”，單元格，單元格H5的名字為的名字為“丙烯銷售額增長率丙烯銷售額增長率”，單元格，單元格I5的名的名字為字為“丙烯銷售成本增長率丙烯銷售成本增長率”，單元格，單元格H6的名字的名字為為“丁二烯銷售額增長率丁二烯銷售額增長率”，單元格，單元格I6的名字為的名字為“丁二烯銷售成本增長率丁二烯銷售成本增長率

18、”，單元格，單元格H7的名字為的名字為“總銷售利潤總銷售利潤”。（3）單擊【工具】菜單，選擇【方案】項，系統）單擊【工具】菜單，選擇【方案】項，系統彈出【方案管理器】對話框，如圖彈出【方案管理器】對話框，如圖5-18所示，單擊所示，單擊【添加】按鈕，系統彈出【添加方案】對話框，如【添加】按鈕，系統彈出【添加方案】對話框，如圖圖5-19所示。所示。 圖圖5-185-18【方案管理器】對話框【方案管理器】對話框 圖圖5-195-19【添加方案】對話框【添加方案】對話框（4）在【添加方案】對話框中，【方案名】）在【添加方案】對話框中，【方案名】編輯框中輸入編輯框中輸入“方案方案1好好”，【可變單元格

19、】，【可變單元格】編輯框中輸入編輯框中輸入“$H$4:$I$6”，單擊【確定】，單擊【確定】按鈕，系統彈出【方案變量值】對話框，按鈕，系統彈出【方案變量值】對話框，如圖如圖5-20所示。所示。圖圖5-205-20【方案變量值】對話框【方案變量值】對話框（5）在【方案變量值】對話）在【方案變量值】對話框中輸入每個可變單元格的框中輸入每個可變單元格的值（這里要按行輸入），完值（這里要按行輸入），完畢后單擊【添加】按鈕，系畢后單擊【添加】按鈕，系統會彈出如圖統會彈出如圖5-19所示的所示的【添加方案】對話框，對第【添加方案】對話框，對第2個方案進行輸入；待所有個方案進行輸入；待所有方案輸入完畢后，單

20、擊【方方案輸入完畢后，單擊【方案變量值】對話框中的【確案變量值】對話框中的【確定】按鈕，系統返回到【方定】按鈕，系統返回到【方案管理器】對話框，如圖案管理器】對話框，如圖5-21所示。此時，可單擊所示。此時，可單擊【關閉】按鈕，回到工作表?！娟P閉】按鈕，回到工作表。圖圖5-21【方案管理器】對話框【方案管理器】對話框3.2 瀏覽方案瀏覽方案（1）打開原工作表，并激活工作表。）打開原工作表，并激活工作表。（2）單擊【工具】菜單，選擇【方案】項，）單擊【工具】菜單，選擇【方案】項，系統彈出【方案管理器】對話框，如圖系統彈出【方案管理器】對話框，如圖5-21所示，選擇要想查看的方案，單擊【顯示】所示

21、，選擇要想查看的方案，單擊【顯示】按鈕，則系統就自動顯示出該方案的執(zhí)行結按鈕，則系統就自動顯示出該方案的執(zhí)行結果，如圖果，如圖5-17所示。所示。5.3.3 編輯方案編輯方案v對做好的方案進行修改，只需在圖對做好的方案進行修改，只需在圖5-21所示的所示的【方案管理器】對話框中選中需要修改的方案，【方案管理器】對話框中選中需要修改的方案，單擊【編輯】按鈕，系統彈出如圖單擊【編輯】按鈕，系統彈出如圖5-20所示的對所示的對話框，進行相應的修改即可。話框，進行相應的修改即可。v若要刪除某一方案，則在圖若要刪除某一方案，則在圖5-21所示的【方案管所示的【方案管理器】對話框中選中需要刪除的方案，單擊

22、【刪理器】對話框中選中需要刪除的方案，單擊【刪除】按鈕。除】按鈕。v若要增加方案，則在圖若要增加方案，則在圖5-21所示的【方案管理器】所示的【方案管理器】對話框中單擊【添加】按鈕，然后在圖對話框中單擊【添加】按鈕，然后在圖5-19所示所示的對話框填寫相關的項目。的對話框填寫相關的項目。3.4 建立方案摘要建立方案摘要（1）在圖）在圖5-21所示的【方案管理器】對話框中單擊所示的【方案管理器】對話框中單擊【摘要】按鈕，彈出【方案摘要】對話框，如圖【摘要】按鈕，彈出【方案摘要】對話框，如圖5-22所示，在【結果類型】中選擇所示，在【結果類型】中選擇“方案摘要方案摘要”項，在【結果單元格】中輸入項

23、，在【結果單元格】中輸入“H7”，然后單擊，然后單擊【確定】按鈕，則系統在當前工作簿中自動建立【確定】按鈕，則系統在當前工作簿中自動建立一個名為一個名為“方案總結方案總結”的工作表，如圖的工作表，如圖5-23所示。所示。 圖圖5-225-22【方案總結】對話框【方案總結】對話框 圖圖5-235-23方案摘要方案摘要4 規(guī)劃求解規(guī)劃求解v在經濟管理中，經常會遇到各種規(guī)劃問題，在經濟管理中，經常會遇到各種規(guī)劃問題，例如：人力資源的調度、產品生產的安排、例如：人力資源的調度、產品生產的安排、運輸線路的規(guī)劃、生產材料的搭配、采購批運輸線路的規(guī)劃、生產材料的搭配、采購批次的確定等。這類問題有一個共同要求

24、，那次的確定等。這類問題有一個共同要求，那就是：如何合理利用各種有限的資源實現最就是：如何合理利用各種有限的資源實現最佳的經濟效益，也就是達到利潤最大、成本佳的經濟效益，也就是達到利潤最大、成本最低、費用最省等目標。最低、費用最省等目標。規(guī)劃求解具有如下三個特點：規(guī)劃求解具有如下三個特點：（1）所求問題都有單一的目標，如求生產的）所求問題都有單一的目標，如求生產的最低成本，求運輸的最佳路線，求產品的最最低成本，求運輸的最佳路線，求產品的最大盈利，求產品周期的最短時間以及求其他大盈利，求產品周期的最短時間以及求其他目標函數的最佳值等。目標函數的最佳值等。（2）總是有明確的不等式約束條件。比如庫）

25、總是有明確的不等式約束條件。比如庫存不能低于一定的數量，否則造成原料短缺存不能低于一定的數量，否則造成原料短缺或產品缺貨；生產產品不能超過一定額度，或產品缺貨；生產產品不能超過一定額度，否則會造成商品積壓等。否則會造成商品積壓等。（3）問題都有直接或間接影響約束條件的一）問題都有直接或間接影響約束條件的一組輸入值。組輸入值。4.1 建立規(guī)劃求解模型建立規(guī)劃求解模型vExcel規(guī)劃求解問題的基本構成規(guī)劃求解問題的基本構成 （1）決策變量（）決策變量（variable）一個或一組可變單元格，可變單元格稱為決策變一個或一組可變單元格，可變單元格稱為決策變量，一組決策變量代表一個規(guī)劃求解的方案量，一組

26、決策變量代表一個規(guī)劃求解的方案（2）目標函數）目標函數目標函數表示規(guī)劃求解要達到的最終目標，是規(guī)目標函數表示規(guī)劃求解要達到的最終目標，是規(guī)劃求解的關鍵。它是規(guī)劃求解中可變量的函數劃求解的關鍵。它是規(guī)劃求解中可變量的函數 （3）約束條件）約束條件約束條件是實現目標的限制條件。約束條件是實現目標的限制條件。 建立規(guī)劃求解模型：建立規(guī)劃求解模型：第第1步步建立求解工作表建立求解工作表(輸入原始數據及相應的各公式輸入原始數據及相應的各公式)第第2步步設置求解參數設置求解參數選擇選擇“工具工具” “規(guī)劃求解規(guī)劃求解”菜單，設置以下求解的各項參數：菜單，設置以下求解的各項參數：v設置目標單元格：輸入目標函

27、數所在單元格設置目標單元格：輸入目標函數所在單元格(為總余額單元為總余額單元格格)v設置目標：設置目標：最大值最大值、最小值最小值或值的數值或值的數值(最大利潤最大利潤,即最大值即最大值)v設置可變單元格：它的確定決定結果（為生產數量）設置可變單元格：它的確定決定結果（為生產數量）v設置約束條件：單擊【添加】按鈕設置約束條件：單擊【添加】按鈕輸入約束條件輸入約束條件按添按添加加依次輸入所有約束條件依次輸入所有約束條件確定確定第第3步步保存求解結果保存求解結果在規(guī)劃求解對話框中按在規(guī)劃求解對話框中按“求解求解”在規(guī)劃求解結果對話框中在規(guī)劃求解結果對話框中按按“保存規(guī)劃求解結果保存規(guī)劃求解結果”4

28、.2 求解優(yōu)化問題求解優(yōu)化問題【例【例5-4】某農場每天至少使用某農場每天至少使用800磅特殊飼磅特殊飼料。這種特殊飼料由玉米和大豆粉配制而成，料。這種特殊飼料由玉米和大豆粉配制而成，含有以下成份：含有以下成份：特殊飼料的營養(yǎng)要求是至少特殊飼料的營養(yǎng)要求是至少30%的蛋白質的蛋白質和至多和至多5%的纖維。該農場希望確定每天的纖維。該農場希望確定每天最小成本最小成本的飼料配制。的飼料配制。 v求解過程：求解過程：步驟步驟1：根據問題建立數學模型：根據問題建立數學模型因為飼料由玉米和大豆粉配制而成，所以模型的因為飼料由玉米和大豆粉配制而成，所以模型的決策變量定義為：決策變量定義為： x=每天混合飼

29、料中玉米的重量（磅）每天混合飼料中玉米的重量（磅）y=每天混合飼料中大豆粉的重量（磅）每天混合飼料中大豆粉的重量（磅）目標函數是使配制這種飼料的每天總成本最小，目標函數是使配制這種飼料的每天總成本最小，因此表示為：因此表示為：z=0.3x+0.9y模型的約束條件是飼料的日需求量和對營養(yǎng)模型的約束條件是飼料的日需求量和對營養(yǎng)成份的需求量，具體表示為：成份的需求量，具體表示為：x+y800 /每天所需飼料每天所需飼料0.09x+0.6y0.3(x+y) /蛋白質蛋白質0.02x+0.06y0.05(x+y) /纖維纖維將上述不等式化簡后，完整的模型為：將上述不等式化簡后，完整的模型為：min z=0.31+0.92s.t. x+y8000.21x-0.3y00.