Excel單變量與規(guī)劃求解模擬運(yùn)算表

會(huì)計(jì)學(xué)1Excel單變量與規(guī)劃求解模擬運(yùn)算表2第1頁/共9頁3二、規(guī)劃求解利用Excel2000提供的規(guī)劃求解可以解解決諸如線性規(guī)劃、運(yùn)輸問題、食譜問題、飼料配方問題、人事安排等，只要與生產(chǎn)、制造、分配、投資、財(cái)務(wù)、工程等有關(guān)的求最大利潤，最小費(fèi)用等問題均可使用規(guī)劃求解法找到答案。例1：某生產(chǎn)單位生產(chǎn)混合飼料，規(guī)定所含各種營養(yǎng)成份為：A（蛋白質(zhì)）至少有15％，B（脂肪）至少含有4.5％，C（淀粉）至少含有30％，D（纖維素）不得超過10％。所能提供的原料有四種，甲（花生餅），每噸單價(jià)500元，含有各種營養(yǎng)成份分別為A25％、B2％、C10％、D2％；乙原料（花生秧），每噸單價(jià)為50元，四種營養(yǎng)成份的含量分別為A8％、B1％、C5％、D40％；丙原料（骨粉），每噸單價(jià)350元，含四種營養(yǎng)成份分別為A20％、B8％、C1％、D0.5％；丁原料（玉米），每噸單價(jià)為450元，含四種營養(yǎng)成份分別為A7％、B5％、C40％、D6％。求在符合飼料營養(yǎng)成份要求的前提下，如何配合這四種飼料，使飼料配合的費(fèi)用最低。

第2頁/共9頁4數(shù)學(xué)模型根據(jù)以上資料，設(shè)四種原料的配比比例分別為X1、X2、X3、X4，可列線性規(guī)劃模型如下：

25%*X1＋8%*X2＋20%*X3＋7%*X4≥15%（蛋白質(zhì)約束條件）

2%*X1＋1%*X2＋8%*X3＋5%*X4≥4.5%(脂肪約束條件)

10%*X1＋5%*X2＋1%*X3＋40%*X4≥30%（淀粉約束條件）

2%*X1＋40%*X2＋0.5%*X3＋6%*X4≤10%（纖維素約束條件）

X1≥0，X2≥0，X3≥0，X4≥0（非負(fù)條件）

Z=500X1＋50X2＋350X3＋450X4（目標(biāo)函數(shù)值最?。〦XCEL操作演示第3頁/共9頁5練習(xí):規(guī)劃求解設(shè)某工廠生產(chǎn)甲乙兩種產(chǎn)品，生產(chǎn)甲產(chǎn)品需要A、B、C三種原料的量分別為8、5、4，可獲利潤為9。生產(chǎn)乙產(chǎn)品需要A、B、C三種原料的量分別為6、5、9，可獲利潤為12?，F(xiàn)在工廠中A、B、C三種原料的量分別為360、250、350，求生產(chǎn)甲、乙兩種產(chǎn)品各多少才能使利潤最大？最大利潤是多少？數(shù)學(xué)模型8X1+6X2<=3605X1+5X2<=2504X1+9X2<=350X1>=0X2>=0Z=9X1+12X2取最大值第4頁/共9頁6三、解方程式組x1+x2+2x3+3x4=1

3x1-x2-x3-2x4=-4

2x1+3x2-x3-x4=-6

x1+2x2+3x3-x4=-4

X1=-1X2=-1X3=0X4=1第5頁/共9頁7四、模擬運(yùn)算表Excel作為一個(gè)電子表格其作用不僅僅是數(shù)據(jù)的電子化存儲(chǔ)及排序和檢索，它還有另外一項(xiàng)很重要的功能，那就是數(shù)據(jù)分析功能，這里用得最多的就是模擬運(yùn)算表。例如：要制作一個(gè)數(shù)據(jù)分析表，要求顯示公式中某個(gè)值的變化將如何影響公式的結(jié)果。模擬運(yùn)算表提供了一種快捷手段，它可以通過一步操作計(jì)算出多種情況下的值；同時(shí)它還是一種有效的方法，可以查看和比較由工作表中不同變化所引起的各種結(jié)果。例.銀行存款年利率為5%,存入10000元,存10年后本息合計(jì)多少元?

本息合計(jì):10000*(1+0.05)10如果存入金額從10000每次增加1000元變動(dòng)到20000,存10年后本息合計(jì)各是多少元?第6頁/共9頁81、單變量模擬運(yùn)算表單變量模擬運(yùn)算表的結(jié)構(gòu)特點(diǎn)是，其輸入數(shù)值被排列在一列中（列引用）或一行中（行引用）。單變量模擬運(yùn)算表中使用的公式必須引用輸入單元格。注意：在一列或一行中，鍵入要替換工作表上的輸入單元格的數(shù)值序列。如果輸入數(shù)值被排成一列，請(qǐng)?jiān)诘谝粋€(gè)數(shù)值的同一行且處于數(shù)值列右側(cè)的單元格中，鍵入所需的公式。如果輸入數(shù)值被排成一行，請(qǐng)?jiān)诘谝粋€(gè)數(shù)值的同一列且處于數(shù)值行下方的單元格內(nèi)，鍵入所需的公式。選定包含公式和需要被替換的數(shù)值的單元格區(qū)域。在“數(shù)據(jù)”菜單中，單擊“模擬運(yùn)算表”命令。為單變量模擬運(yùn)算表單改變公式，可以自動(dòng)改變運(yùn)算結(jié)果將模擬運(yùn)算表的計(jì)算結(jié)果轉(zhuǎn)換為常量第7頁/共9頁92、雙變量模擬運(yùn)算表例如：對(duì)于公式z=3x+4y+1：要求當(dāng)x等于從1到4間的所有整數(shù)，而y為1到7間所有整數(shù)時(shí)所有z的值，可以使用模擬運(yùn)算表完成。1、首先排好x與