利用Excel線性規劃設計飼料配方

ttsky

利用Excel線性規劃設計飼料配方

鄧君明 （中國海洋大學水產學院）
曾廣廳 （云南農業大學云南省動物營養與飼料重點實驗室）

    摘 要：借助Excel 2000“線性規劃求解”功能，結合飼料配方設計要求，以線性規劃求解建立數學模型，根據動物的飼養標準，能快速、簡單地求解飼料配方，在最低成本目標下實現對原料和營養成分的有效調控。
    關鍵詞：Excel；線性規劃；飼料配方

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

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

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

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

    圖1中B3至M16是aij值；N3至N16是對應于初始值時，線性方程組左側的值；O3至O16是bi值；B17至M17是Cj值；B18至M18是給定的初始值(初始值均給定為1)；N17是給定初始值時的目標函數值(S)，xj在表格中不占用任何單元格。N3至N17格中的公式是同行左側B列至M列與B18至M18對應格乘積之和。如在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格都輸入了相應的公式。

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

圖2 “規劃求解”對話框

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

圖3 “添加約束”對話框

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

圖4 “規劃求解選項”對話框

圖5 “規劃求解結果”對話框

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

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

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

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

tony2005428

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

pf_c2008

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

wangying0409

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

開心2009

實用，還是多發此貼為好，呵呵

zozangui

雜志發表過好久了，可以學習使用。

李春慶

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