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

ttsky

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

鄧君明 （中國海洋大學(xué)水產(chǎn)學(xué)院）
曾廣廳 （云南農(nóng)業(yè)大學(xué)云南省動物營養(yǎng)與飼料重點實驗室）

    摘 要：借助Excel 2000“線性規(guī)劃求解”功能，結(jié)合飼料配方設(shè)計要求，以線性規(guī)劃求解建立數(shù)學(xué)模型，根據(jù)動物的飼養(yǎng)標準，能快速、簡單地求解飼料配方，在最低成本目標下實現(xiàn)對原料和營養(yǎng)成分的有效調(diào)控。
    關(guān)鍵詞：Excel；線性規(guī)劃；飼料配方

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

    1. 飼料配方設(shè)計要求
    1.1優(yōu)化配方設(shè)計目標
    動物生產(chǎn)的經(jīng)濟效益主要取決于飼料。飼料品質(zhì)的好壞，成本的高低直接影響動物生產(chǎn)的經(jīng)濟效益。而飼料配方是配合飼料生產(chǎn)的核心，要優(yōu)化配方設(shè)計，必須同時解決以下三個問題：⑴營養(yǎng)需要問題，由營養(yǎng)學(xué)家研究修改制定，滿足營養(yǎng)標準就是線性規(guī)劃求解的主要約束條件之一，即營養(yǎng)性限制；⑵合理組合原料，不同原料的合理搭配，才能滿足動物的營養(yǎng)需要，是約束條件之二，即原料性限制；⑶價格最低，在符合條件⑴、⑵的基礎(chǔ)上，采用成本最低的原料配比就是求解的目標，即最低成本目標函數(shù)。
    1.2數(shù)學(xué)模型
    優(yōu)選最佳飼料配方的數(shù)學(xué)原理是線性規(guī)劃法，就是求某一目標函數(shù)在一定的約束條件下的最大值或最小值。約束條件和目標函數(shù)均可用線性方程組或線性不等式表示。線性規(guī)劃最低成本配方優(yōu)化的數(shù)學(xué)模型可表示為：
    目標函數(shù)：Min S=C1X1+C2X2+∧+CnXn(求最小值)
    約束條件：a11x1+a12x2+∧+a1nxn≥b1（或=，≤b1）
              a21x1+a22x2+∧+a2nxn≥b2（或=，≤b2）
                ┇ ┇ ┇ ┇
              am1x1+am2x2+∧+amnxn≥bm（或=，≤bm）
              xj≥0（j=1，2， …，n）
    其中： x1，x2，∧，xn為決策變量，即各種原料在配方中的數(shù)量；
    aij（i=1，2，∧，m；j=1，2，∧，n）為技術(shù)系數(shù)，即各種原料相應(yīng)的營養(yǎng)成分；
    b1，b2，∧，bn為約束值，即配方中應(yīng)滿足的各項營養(yǎng)指標或重量指標；
    C1，C2，∧，Cn為成本系數(shù)，即每種原料的價格系數(shù)；
    m為約束條件個數(shù)；
    為配方原料個數(shù)。
    1.3飼料配方
    用玉米、麩皮、豆粕、菜籽粕、進口魚粉、磷酸氫鈣、石粉、食鹽、賴氨酸、蛋氨酸和預(yù)混料11種原料，以產(chǎn)蛋率<65%的蛋雞為例，各種營養(yǎng)需要為約束條件，計算優(yōu)化飼料配方。
    查出產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標準(見表1)與中國飼料成分及營養(yǎng)價值表2002年(第13版)(見表2)。同時還需要查出當(dāng)?shù)赝诟鞣N飼料原料的市場價格(見表3)，并對有些飼料原料的用量作出相應(yīng)的限定(見表4)。按圖1的樣式對應(yīng)將表1、2、3、4中的內(nèi)容填入Excel 2000單元格內(nèi)。
表1 產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標準
          代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%) 食鹽(%)
飼養(yǎng)標準     11.50         14.0    3.2     0.30     0.62       0.31     0.37

表2 各種飼料原料營養(yǎng)價值表
飼料成分 代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%)
玉米         13.47       7.80    0.02    0.10      0.23      0.15
麩皮          6.82      15.70    0.11    0.30      0.58      0.13
豆粕          9.62      43.00    0.32    0.20      2.45      0.64
菜籽粕        7.41      38.60    0.65    0.33      1.30      0.63
進口魚粉     12.18      62.50    3.96    3.05      5.12      1.66
磷酸氫鈣      0.00       0.00   21.00   16.00      0.00      0.00
石粉          0.00       0.00   35.00    0.00      0.00      0.00
油脂         36.82       0.00    0.00    0.00      0.00      0.00
賴氨酸        0.00       0.00    0.00    0.00     78.80      0.00
蛋氨酸        0.00       0.00    0.00    0.00      0.00     98.00

表3 各種飼料原料的市場價格
原料        玉米 麩皮 豆粕 菜籽粕 進口魚粉 磷酸氫鈣  石粉  食鹽  油脂 賴氨酸 蛋氨酸 預(yù)混料
價格(元/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 幾種飼料原料的約束條件
        麩皮 菜籽粕 進口魚粉 油脂  食鹽 預(yù)混料
下限(%)  0     0       2      2    0.3    2
上限(%) 10     7       5      -    0.3    2

圖1 求解飼料配方所用線性模型在表格中的排列樣式

    圖1中B3至M16是aij值；N3至N16是對應(yīng)于初始值時，線性方程組左側(cè)的值；O3至O16是bi值；B17至M17是Cj值；B18至M18是給定的初始值(初始值均給定為1)；N17是給定初始值時的目標函數(shù)值(S)，xj在表格中不占用任何單元格。N3至N17格中的公式是同行左側(cè)B列至M列與B18至M18對應(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”，然后把光標放在N3格右下角位置上，等光標變成“+”時往下拖動至N17上，這樣從N3至N17格都輸入了相應(yīng)的公式。

    2. 線性規(guī)劃求解
    往Excel電子表格上輸入完數(shù)據(jù)及公式以后，就可以利用其工具欄中“規(guī)劃求解”功能開始求解，步驟如下：
    2.1選中“工具”菜單，選中“規(guī)劃求解”命令，出現(xiàn)“規(guī)劃求解參數(shù)”對話框(如圖2)。
    2.2在[設(shè)置目標單元格]編輯框中，輸入目標單元格的名稱$N$17，然后點擊[最小值]選項。
    2.3在[可變單元格]中，輸入$B$18:$M$18。

圖2 “規(guī)劃求解”對話框

    2.4在[約束]窗口中單擊添加按鈕，產(chǎn)生“添加約束”對話框(如圖3)，在[單元格引用位置]和[約束值]輸入欄中輸入約束條件。如本例在[單元格引用位置]上先輸入$N$3:$N$8；然后選擇下拉窗口中的“>=”；接著在[約束值]上輸入$O$3:$O$8，點擊添加按鈕，繼續(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 “添加約束”對話框

    2.5點擊選項按鈕，進入“規(guī)劃求解選項”對話框(如圖4)，選中“采用線形模型”、“假定非負”、“正切函數(shù)”、“向前差分”、“牛頓法”，然后點擊確定按鈕，回到“規(guī)劃求解參數(shù)”對話框。
    2.6單擊求解按鈕，開始計算，進入“規(guī)劃求解結(jié)果”對話框(如圖5)，在該對話框中可以作以下的選擇：
    2.6.1保存規(guī)劃求解的結(jié)果；
    2.6.2恢復(fù)為原值。
    在本例中選擇默認的選項“保存規(guī)劃求解的結(jié)果”，在“報告”中根據(jù)需要選定計算結(jié)果報告(運算結(jié)果報告、敏感性報告、極限值報告)。需要說明的是：線性規(guī)劃的求解結(jié)果要么有最優(yōu)解，要么給出參考配方(無最優(yōu)解時)。所謂最優(yōu)解就是滿足所有約束條件(包括營養(yǎng)指標和配比限制條件)的最低成本配方。參考配方是指最優(yōu)解不存在時，仍然存在一個最接近理想的配方，它的成本是最低，但是所有的約束條件沒有同時滿足，但該參考配方仍然具有一定的參考價值，因為該結(jié)果往往是可以應(yīng)用的。

圖4 “規(guī)劃求解選項”對話框

圖5 “規(guī)劃求解結(jié)果”對話框

    2.6.7打印規(guī)劃求解結(jié)果
    通過以上步驟，就可求出一個蛋雞料的最優(yōu)配方(不一定飼養(yǎng)效果最佳)?？梢?，利用Excel的“規(guī)劃求解”可以篩選出最優(yōu)的配方，這樣給動物營養(yǎng)設(shè)計師們的工作帶來極大的方便，既提高了工作效率，又提高了配方質(zhì)量。

    3. 運行結(jié)果分析
    3.1運算結(jié)果報告
    在此報告中，我們可以看到求得的飼料配方成本、最優(yōu)配方以及約束條件滿足狀況，其中“型數(shù)值”指求解值與限定值之間的差距，在無法求得最優(yōu)解時，我們據(jù)此可以適當(dāng)調(diào)節(jié)約束條件。通過線性規(guī)劃求解，從運算結(jié)果報告中可知，本例配合飼料價格為1.58元/kg，飼料配方見表5。
表5 蛋雞料飼料配方表
        玉米 麩皮 豆粕 菜籽粕 魚粉 磷酸氫鈣 石粉 油脂 食鹽 賴氨酸 蛋氨酸 預(yù)混料
配方(%) 66.1 0.0  11.4   7.0   2.0   0.8     8.2  2.0  0.3   0.0    0.1    2.0
    3.2敏感性報告
    從該報告中我們可以看到以下內(nèi)容：
    遞減成本，表示各個決策變量(飼料原料的添加量)的影子價格，它說明在飼料總量條件不變的情況下，某一種飼料原料用量在最優(yōu)解的基礎(chǔ)上增加1個單位時，目標成本增加的量。
    目標式系數(shù)及其允許的增量和減量，指在最優(yōu)解保持不變的情況下，目標式系數(shù)(飼料原料價格)的變化范圍，這反映了所獲得配方對原料市場價格變化的適應(yīng)能力。如果原料價格變化在允許范圍內(nèi)，則不必更改配方。
    陰影價格，指約束條件的影子價格，表示在所獲得最優(yōu)解的基礎(chǔ)上，當(dāng)約束條件每增減1個單位時，所引起目標函數(shù)值(飼料最終價格)的增減量。這可為進一步調(diào)整約束條件提供參考。
    約束限制值允許的增量和減量，指在保持最優(yōu)解和其它條件不變的情況下，各個約束限制值的可變化范圍，也就是指在此變化范圍內(nèi)表中所列出的約束條件的影子價格才能夠成立。
    3.3權(quán)限值報告
    該報告列出了最終的飼料價格，以及保證該價格成立的飼料原料添加量的上下極限。

    4. 討論
    4.1在進行規(guī)劃求解時，首先要確認在“工具”菜單中出現(xiàn)“規(guī)劃求解”命令，如果沒有則需要安裝“規(guī)劃求解”加載宏，單擊“工具”菜單中，單擊加載宏命令，如果在“加載宏”命令中，沒有列出“規(guī)劃求解”項，單擊瀏覽，確定驅(qū)動器、目錄、文件名或運行加載程序，然后在“加載宏”對話框中，選定“規(guī)劃求解”復(fù)選框。Excel在“規(guī)劃求解”中使用的算法是由Leon lasdon，(University of Texas at Austin)和Allan Waren(Cleveland State University)改進的通用非線性規(guī)劃最佳化代碼。當(dāng)“規(guī)劃求解選項”對話框中的“采用線性模型”復(fù)選框被選定時，改變可變單元格的初始值不會影響最終數(shù)值和求解時間。在“規(guī)劃求解”中可以指定500個約束條件，對每一個可變單元格來說，分別有一個上、下限，還可另加100個附加約束條件，應(yīng)用約束條件的單元格不應(yīng)多于1000個。
    4.2若規(guī)劃求解過程中出現(xiàn)無解時，說明所使用的原料要達到約束條件是不可能的，此時要注意分析可能存在的原因：
    4.2.1各營養(yǎng)指標的約束條件是否有沖突，如有些限定了蛋白質(zhì)水平低，而限定的蛋氨酸水平又高，自相沖突；
    4.2.2原料種類用量的限定與營養(yǎng)指標值之間有沖突。如粗纖維限定值較小，而糠餅麥麩原料限定用量卻較高；
    4.2.3同一原料的上下約束范圍過小，將限制營養(yǎng)指標值的達到；
    4.2.4有時可能由于“規(guī)劃求解參數(shù)”選項中的“最長運算時間”、“迭代次數(shù)”、“精度”、“允許誤差”等取值不當(dāng)。
    4.3本模板還可對單位價格單元進行約束，表明對客戶提出的具體要求從經(jīng)濟角度進行可行性分析。即最終求解價格有限制要求，若增加該約束時出現(xiàn)無解，說明超過該價飼料廠要虧本生產(chǎn)。
    4.4該方法計算實驗動物飼料的常規(guī)營養(yǎng)成分具有快速、準確的優(yōu)點，只要輸入配方的原糧品種、價格和配比，該系統(tǒng)將在瞬間一次給出計算結(jié)果，直接顯示在計算機屏幕上。當(dāng)與營養(yǎng)標準相比較后，需要更改調(diào)整時，可隨時變更配比，立即自動計算，獲得新的調(diào)整結(jié)果。同時，可根據(jù)需要手工調(diào)整飼料配方。

    5. 小結(jié)
    利用Excel電子表格優(yōu)化飼料配方非常簡便、快捷，表中數(shù)值的排列靈活多變，可根據(jù)用戶要求自行設(shè)置，且約束條件不受限制（一般情況下），可以使用于各種飼料廠和個人設(shè)計飼料配方，尤其對于小型飼料廠而言，采用Excel的“規(guī)劃求解”方法就可使整個畜牧飼料配方操作變得簡單快捷、結(jié)果準確、直觀且成本較低。
    （參考文獻略）

tony2005428

用excel做最低價格配方已經(jīng)是很普通的操作辦法了，不過此文還是可以參考的！
謝謝樓主！
:buyao:

pf_c2008

感謝樓主分享  :)3:  可以實際操作下

wangying0409

馬上實際操作一下。感謝樓主！

開心2009

實用，還是多發(fā)此貼為好，呵呵

zozangui

雜志發(fā)表過好久了，可以學(xué)習(xí)使用。

李春慶

此文從原理上講的比較明白，謝謝樓主。