利用Excel線性規(guī)劃設(shè)計(jì)飼料配方

1、利用Excel線性規(guī)劃設(shè)計(jì)飼料配方線性規(guī)劃, Excel, 配方, 飼料, 設(shè)計(jì)利用Excel線性規(guī)劃設(shè)計(jì)飼料配方鄧君明 （中國海洋大學(xué)水產(chǎn)學(xué)院）曾廣廳 （云南農(nóng)業(yè)大學(xué)云南省動(dòng)物營養(yǎng)與飼料重點(diǎn)實(shí)驗(yàn)室）    摘 要：借助Excel 2000“線性規(guī)劃求解”功能，結(jié)合飼料配方設(shè)計(jì)要求，以線性規(guī)劃求解建立數(shù)學(xué)模型，根據(jù)動(dòng)物的飼養(yǎng)標(biāo)準(zhǔn)，能快速、簡單地求解飼料配方，在最低成本目標(biāo)下實(shí)現(xiàn)對(duì)原料和營養(yǎng)成分的有效調(diào)控。    關(guān)鍵詞：Excel；線性規(guī)劃；飼料配方    所謂飼料配方設(shè)計(jì)，就是應(yīng)用一定的計(jì)算方法，根據(jù)原料的營養(yǎng)成分和配方的規(guī)格、要求

2、，產(chǎn)生配方中各原料比例的一種運(yùn)算過程。線性規(guī)劃法是目前應(yīng)用最廣泛的一種優(yōu)化飼料配方技術(shù)。線性規(guī)劃最低成本配方的優(yōu)化結(jié)果是產(chǎn)生一個(gè)滿足約束條件的最低成本配方，它受原料的營養(yǎng)成分、約束條件值(配方營養(yǎng)素水平)、原料價(jià)格等的影響。目前，大型專業(yè)飼料公司己使用計(jì)算機(jī)和專用的飼料配方軟件進(jìn)行配方。但專用的飼料配方軟件價(jià)格不菲，因此，在小型飼料廠和一般養(yǎng)殖場的應(yīng)用并不普及?，F(xiàn)在有可能只要擁有安裝了Excel 2000的電腦就可以通過計(jì)算機(jī)運(yùn)用“線性規(guī)劃求解”求解飼料配方。利用它的行和列的形式組成區(qū)域，在區(qū)域內(nèi)記錄數(shù)據(jù)信息，組成數(shù)據(jù)庫，且可對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)和分析，獲取有效信息。同時(shí)，運(yùn)用線性規(guī)劃實(shí)現(xiàn)日糧配方，

3、方法簡單、快捷、準(zhǔn)確、方便。本文就利用Excel 2000線性規(guī)劃設(shè)計(jì)飼料配方作一闡述。    1. 飼料配方設(shè)計(jì)要求    1.1優(yōu)化配方設(shè)計(jì)目標(biāo)    動(dòng)物生產(chǎn)的經(jīng)濟(jì)效益主要取決于飼料。飼料品質(zhì)的好壞，成本的高低直接影響動(dòng)物生產(chǎn)的經(jīng)濟(jì)效益。而飼料配方是配合飼料生產(chǎn)的核心，要優(yōu)化配方設(shè)計(jì)，必須同時(shí)解決以下三個(gè)問題：營養(yǎng)需要問題，由營養(yǎng)學(xué)家研究修改制定，滿足營養(yǎng)標(biāo)準(zhǔn)就是線性規(guī)劃求解的主要約束條件之一，即營養(yǎng)性限制；合理組合原料，不同原料的合理搭配，才能滿足動(dòng)物的營養(yǎng)需要，是約束條件之二，即原料性限制；價(jià)格最低，在符合條件、的基礎(chǔ)上，采

4、用成本最低的原料配比就是求解的目標(biāo)，即最低成本目標(biāo)函數(shù)。    1.2數(shù)學(xué)模型    優(yōu)選最佳飼料配方的數(shù)學(xué)原理是線性規(guī)劃法，就是求某一目標(biāo)函數(shù)在一定的約束條件下的最大值或最小值。約束條件和目標(biāo)函數(shù)均可用線性方程組或線性不等式表示。線性規(guī)劃最低成本配方優(yōu)化的數(shù)學(xué)模型可表示為：    目標(biāo)函數(shù)：Min S=C1X1+C2X2+CnXn(求最小值)    約束 條件：a11x1+a12x2+a1nxnb1（或=，b1）            

5、;  a21x1+a22x2+a2nxnb2（或=，b2）                              am1x1+am2x2+amnxnbm（或=，bm）              xj0（j=1，2， ，n）    其中： x1，x2，xn為

6、決策變量，即各種原料在配方中的數(shù)量；    aij（i=1，2，m；j=1，2，n）為技術(shù)系數(shù)，即各種原料相應(yīng)的營養(yǎng)成分；    b1，b2，bn為約束值，即配方中應(yīng)滿足的各項(xiàng)營養(yǎng)指標(biāo)或重量指標(biāo)；    C1，C2，Cn為成本系數(shù)，即每種原料的價(jià)格系數(shù)；    m為約束條件個(gè)數(shù)；    為配方原料個(gè)數(shù)。    1.3飼料配方    用玉米、麩皮、豆粕、菜籽粕、進(jìn)口魚粉、磷酸氫鈣、石粉、食鹽、賴氨酸、蛋氨酸和預(yù)混料11種原料，以產(chǎn)蛋率<65%的蛋雞為例

7、，各種營養(yǎng)需要為約束條件，計(jì)算優(yōu)化飼料配方。    查出產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標(biāo)準(zhǔn)(見表1)與中國飼料成分及營養(yǎng)價(jià)值表2002年(第13版)(見表2)。同時(shí)還需要查出當(dāng)?shù)赝诟鞣N飼料原料的市場價(jià)格(見表3)，并對(duì)有些飼料原料的用量作出相應(yīng)的限定(見表4)。按圖1的樣式對(duì)應(yīng)將表1、2、3、4中的內(nèi)容填入Excel 2000單元格內(nèi)。表1 產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標(biāo)準(zhǔn)          代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%) 食鹽(%)飼養(yǎng)標(biāo)準(zhǔn) 

8、   11.50         14.0    3.2     0.30     0.62       0.31     0.37表2 各種飼料原料營養(yǎng)價(jià)值表飼料成分 代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%)玉米        &#

9、160;13.47       7.80    0.02    0.10      0.23      0.15麩皮          6.82      15.70    0.11    0.30      0.58     &

10、#160;0.13豆粕          9.62      43.00    0.32    0.20      2.45      0.64菜籽粕        7.41      38.60    0.65    0.33 

11、     1.30      0.63進(jìn)口魚粉     12.18      62.50    3.96    3.05      5.12      1.66磷酸氫鈣      0.00       0.00   21.00&#

12、160;  16.00      0.00      0.00石粉          0.00       0.00   35.00    0.00      0.00      0.00油脂         36.82 

13、;      0.00    0.00    0.00      0.00      0.00賴氨酸        0.00       0.00    0.00    0.00     78.80      0.00蛋氨酸

14、60;       0.00       0.00    0.00    0.00      0.00     98.00表3 各種飼料原料的市場價(jià)格原料        玉米 麩皮 豆粕 菜籽粕 進(jìn)口魚粉 磷酸氫鈣  石粉  食鹽  油脂 賴氨酸 蛋氨酸 預(yù)混料價(jià)格(元

15、/kg) 1.22 1.08 2.30  1.20    5.37     1.55    0.12  1.24  8.00 17.50   31.50  5.50表4 幾種飼料原料的約束條件        麩皮 菜籽粕 進(jìn)口魚粉 油脂  食鹽 預(yù)混料下限(%)  0     0

16、       2      2    0.3    2上限(%) 10     7       5      -    0.3    2圖1 求解飼料配方所用線性模型在表格中的排列樣式圖1中B3至M16是aij值；N3至N16是對(duì)應(yīng)于初始值時(shí)，線性方程組左側(cè)的值；O3至O16是bi值；B17至M17是Cj值；B18至M

17、18是給定的初始值(初始值均給定為1)；N17是給定初始值時(shí)的目標(biāo)函數(shù)值(S)，xj在表格中不占用任何單元格。N3至N17格中的公式是同行左側(cè)B列至M列與B18至M18對(duì)應(yīng)格乘積之和。如在N3格中輸入“=B3*$B$18+ C3*$C$18+ D3*$D$18+ E3*$E$18+F3*$F$18+G3*$G$18+H3*$H$18+I3*$I$18+J3*$J$18+K3*$K$18+L3*$L$18+M3*$M$18”，然后把光標(biāo)放在N3格右下角位置上，等光標(biāo)變成“+”時(shí)往下拖動(dòng)至N17上，這樣從N3至N17格都輸入了相應(yīng)的公式。    2. 線性規(guī)劃求解 

18、  往Excel電子表格上輸入完數(shù)據(jù)及公式以后，就可以利用其工具欄中“規(guī)劃求解”功能開始求解，步驟如下：    2.1選中“工具”菜單，選中“規(guī)劃求解”命令，出現(xiàn)“規(guī)劃求解參數(shù)”對(duì)話框(如圖2)。    2.2在設(shè)置目標(biāo)單元格編輯框中，輸入目標(biāo)單元格的名稱$N$17，然后點(diǎn)擊最小值選項(xiàng)。    2.3在可變單元格中，輸入$B$18:$M$18。圖2 “規(guī)劃求解”對(duì)話框2.4在約束窗口中單擊添加按鈕，產(chǎn)生“添加約束”對(duì)話框(如圖3)，在單元格引用位置和約束值輸入欄中輸入約束條件。如本例在單元格引用位置上先輸入$N$3:$N$8

19、；然后選擇下拉窗口中的“>=”；接著在約束值上輸入$O$3:$O$8，點(diǎn)擊添加按鈕，繼續(xù)添加約束條件：$N$9:$N$10<=$O$9:$N$10；$N$11>=$O$11；$N$12<=$O$12；$N$13>=$O$13；$N$14:$N$15=$O$14:$O$15。輸完約束條件以后關(guān)閉窗口，回到“規(guī)劃求解參數(shù)”窗口。圖3 “添加約束”對(duì)話框2.5點(diǎn)擊選項(xiàng)按鈕，進(jìn)入“規(guī)劃求解選項(xiàng)”對(duì)話框(如圖4)，選中“采用線形模型”、“假定非負(fù)”、“正切函數(shù)”、“向前差分”、“牛頓法”，然后點(diǎn)擊確定按鈕，回到“規(guī)劃求解參數(shù)”對(duì)話框。    2.6單擊求

20、解按鈕，開始計(jì)算，進(jìn)入“規(guī)劃求解結(jié)果”對(duì)話框(如圖5)，在該對(duì)話框中可以作以下的選擇：    2.6.1保存規(guī)劃求解的結(jié)果；    2.6.2恢復(fù)為原值。    在本例中選擇默認(rèn)的選項(xiàng)“保存規(guī)劃求解的結(jié)果”，在“報(bào)告”中根據(jù)需要選定計(jì)算結(jié)果報(bào)告(運(yùn)算結(jié)果報(bào)告、敏感性報(bào)告、極限值報(bào)告)。需要說明的是：線性規(guī)劃的求解結(jié)果要么有最優(yōu)解，要么給出參考配方(無最優(yōu)解時(shí))。所謂最優(yōu)解就是滿足所有約束條件(包括營養(yǎng)指標(biāo)和配比限制條件)的最低成本配方。參考配方是指最優(yōu)解不存在時(shí)，仍然存在一個(gè)最接近理想的配方，它的成本是最低，但是所有的約束條件沒有同時(shí)

21、滿足，但該參考配方仍然具有一定的參考價(jià)值，因?yàn)樵摻Y(jié)果往往是可以應(yīng)用的。圖4 “規(guī)劃求解選項(xiàng)”對(duì)話框圖5 “規(guī)劃求解結(jié)果”對(duì)話框    2.6.7打印規(guī)劃求解結(jié)果    通過以上步驟，就可求出一個(gè)蛋雞料的最優(yōu)配方(不一定飼養(yǎng)效果最佳)?？梢?，利用Excel的“規(guī)劃求解”可以篩選出最優(yōu)的配方，這樣給動(dòng)物營養(yǎng)設(shè)計(jì)師們的工作帶來極大的方便，既提高了工作效率，又提高了配方質(zhì)量。    3. 運(yùn)行結(jié)果分析    3.1運(yùn)算結(jié)果報(bào)告    在此報(bào)告中，我們可以看到求得的飼料配方成本、最優(yōu)配方以及約束條件滿足狀

22、況，其中“型數(shù)值”指求解值與限定值之間的差距，在無法求得最優(yōu)解時(shí)，我們據(jù)此可以適當(dāng)調(diào)節(jié)約束條件。通過線性規(guī)劃求解，從運(yùn)算結(jié)果報(bào)告中可知，本例配合飼料價(jià)格為1.58元/kg，飼料配方見表5。表5 蛋雞料飼料配方表        玉米 麩皮 豆粕 菜籽粕 魚粉 磷酸氫鈣 石粉 油脂 食鹽 賴氨酸 蛋氨酸 預(yù)混料配方(%) 66.1 0.0  11.4   7.0   2.0   0.8     8.2 

23、0;2.0  0.3   0.0    0.1    2.0    3.2敏感性報(bào)告    從該報(bào)告中我們可以看到以下內(nèi)容：    遞減成本，表示各個(gè)決策變量(飼料原料的添加量)的影子價(jià)格，它說明在飼料總量條件不變的情況下，某一種飼料原料用量在最優(yōu)解的基礎(chǔ)上增加1個(gè)單位時(shí)，目標(biāo)成本增加的量。    目標(biāo)式系數(shù)及其允許的增量和減量，指在最優(yōu)解保持不變的情況下，目標(biāo)式系數(shù)(飼料原料價(jià)格)的變化范圍，這反映了所獲得配方對(duì)原料市場價(jià)格變化的適應(yīng)

24、能力。如果原料價(jià)格變化在允許范圍內(nèi)，則不必更改配方。    陰影價(jià)格，指約束條件的影子價(jià)格，表示在所獲得最優(yōu)解的基礎(chǔ)上，當(dāng)約束條件每增減1個(gè)單位時(shí)，所引起目標(biāo)函數(shù)值(飼料最終價(jià)格)的增減量。這可為進(jìn)一步調(diào)整約束條件提供參考。    約束限制值允許的增量和減量，指在保持最優(yōu)解和其它條件不變的情況下，各個(gè)約束限制值的可變化范圍，也就是指在此變化范圍內(nèi)表中所列出的約束條件的影子價(jià)格才能夠成立。    3.3權(quán)限值報(bào)告    該報(bào)告列出了最終的飼料價(jià)格，以及保證該價(jià)格成立的飼料原料添加量的上下極限。   

25、; 4. 討論    4.1在進(jìn)行規(guī)劃求解時(shí)，首先要確認(rèn)在“工具”菜單中出現(xiàn)“規(guī)劃求解”命令，如果沒有則需要安裝“規(guī)劃求解”加載宏，單擊“工具”菜單中，單擊加載宏命令，如果在“加載宏”命令中，沒有列出“規(guī)劃求解”項(xiàng)，單擊瀏覽，確定驅(qū)動(dòng)器、目錄、文件名或運(yùn)行加載程序，然后在“加載宏”對(duì)話框中，選定“規(guī)劃求解”復(fù)選框。Excel在“規(guī)劃求解”中使用的算法是由Leon lasdon，(University of Texas at Austin)和Allan Waren(Cleveland State University)改進(jìn)的通用非線性規(guī)劃最佳化代碼。當(dāng)“規(guī)劃求解選項(xiàng)”對(duì)話框中的“采用線性模型”復(fù)選框被選定時(shí)，改變可變單元格的初始值不會(huì)影響最終數(shù)值和求解時(shí)間。在“規(guī)劃