excel建模教程

1、使用Excel規(guī)劃求解解線性規(guī)劃問(wèn)題線性規(guī)劃求解的基本知識(shí)線性規(guī)劃模型由3個(gè)基本部分組成： 決策變量(variable) 目標(biāo)函數(shù)(objective) 約束條件(constraint)示例：營(yíng)養(yǎng)配方問(wèn)題(問(wèn)題)某農(nóng)場(chǎng)每天至少使用800磅特殊飼料。這種特殊飼料由玉米和大豆粉配制而成，含有以下成份：超蚓海幡有嬲的豌費(fèi)用(美元/褫)事白質(zhì)聘玉米0.090.020.30大豆粉(1.360.90特殊飼料的營(yíng)養(yǎng)要求是至少30%的蛋白質(zhì)和至多5%的纖維。該農(nóng)場(chǎng)希望確定每天最小成本的飼料配制。（解答過(guò)程）因?yàn)轱暳嫌捎衩缀痛蠖狗叟渲贫?，所以模型的決策變量定義為:x1=每天混合飼料中玉米的重量（磅）x2=每天

2、混合飼料中大豆粉的重量（磅）目標(biāo)函數(shù)是使配制這種飼料的每天總成本最小，因此表示為：minz=0.3X1+0.9X2模型的約束條件是飼料的日需求量和對(duì)營(yíng)養(yǎng)成份的需求量，具體表示為:x1+x28000.09X1+0.6X20.3(x1+x2)0.02X1+0.06X28000.21X1-0.3x20 x1,x20可以使用圖解法確定最優(yōu)解。下面，我們介紹使用Excel的規(guī)劃求解加載項(xiàng)求解該模型使用Excel規(guī)劃求解解線性規(guī)劃問(wèn)題步驟1安裝Excel規(guī)劃求解加載項(xiàng)單擊“Office按鈕一一Excel選項(xiàng)一一加載項(xiàng)一一（Excel加載項(xiàng)）轉(zhuǎn)到，由現(xiàn)加載宏”對(duì)話框，如下圖所示。選擇規(guī)劃求解加載項(xiàng)工單擊確定

3、”。此時(shí)，在數(shù)據(jù)”選項(xiàng)卡中由現(xiàn)帶有規(guī)劃求解”按鈕的分析”組，如下圖所示步驟2設(shè)計(jì)電子表格使用Excel求解線性規(guī)劃問(wèn)題時(shí)，電子表格是輸入和輸由的載體，因此設(shè)計(jì)良好的電子表格，更加易于閱讀。本例的電子表格設(shè)計(jì)如下圖所示：KBCDEFGHI1營(yíng)養(yǎng)配方模型2單元格公式1P3T112D5 -G5*1BS2=B00D8 =GMB412+U3jCtl27淺白膜含量21。,30仁0D12 =D5e纖雉含量0.03|,06009:二110.第出:,1E2E二2解決方亮1.其中，輸入數(shù)據(jù)的單元格使用了陰影格式，即B5:C8和F6:F8;變量和目標(biāo)函數(shù)單元格為B12:D12加上了粗線邊框；D5:D8中輸入了約束公

4、式，公式如上圖中的右上角所示，其相應(yīng)的代數(shù)表達(dá)式見(jiàn)上文。技巧：也可以在單元格D5中輸入公式：=SUMPRODUCT(B5:C5,$B$12:$C$12)然后將其復(fù)制到下方相應(yīng)的單元格中。步驟3應(yīng)用規(guī)劃求解工具單擊數(shù)據(jù)一一分析一一規(guī)劃求解:由現(xiàn)如下圖所示的規(guī)劃求解參數(shù)”對(duì)話框，設(shè)計(jì)相應(yīng)的參數(shù)并且單擊添加”按鈕，添加相應(yīng)的約束，如下圖所示。確定取消法；林加幫助如=mt=bin注意，上圖所示的約束中，添加了非負(fù)限制，即$B$12:$C$12=0o還可以在規(guī)劃求解參數(shù)”對(duì)話框中，單擊選項(xiàng)”按鈕，在由現(xiàn)的規(guī)劃求解選項(xiàng)”對(duì)話框中（如下圖所示）添加非負(fù)約束，即選擇采用線性模型”和假定非負(fù)”前的復(fù)選框，其余的默認(rèn)值可以保持不變。當(dāng)然，如果精度太高，可以調(diào)低精度，也獲得滿意的結(jié)果。ABCDEFJ12營(yíng)養(yǎng)配方模型數(shù)據(jù)二3zlx2玉米的重量大豆粉的重量總計(jì)限制5目標(biāo)0.30.9437.656日需求量11aoo.oo=EDO7蛋白質(zhì)含量0.21-0.30.00=091011=0-o輸出;xlZ12解決方案1470.59)329.41437.65為了增強(qiáng)可讀性，還可以使用有描述性的Excel名稱來(lái)代替單元格字母如果問(wèn)題沒(méi)有可行解，規(guī)劃求解將會(huì)顯示明確的信息規(guī)劃求解找不到有用的解如果最優(yōu)目標(biāo)值是無(wú)界的，規(guī)劃