利用Excel線性規劃優化設計山羊飼料配方

(江蘇沿江地區農業科學研究所，江蘇如皋226541)

設計和采用科學而實用的畜禽飼料配方是合理利用當地飼料資源，提高養殖生產水平，保證畜禽健康，獲得較高經濟效益的重要保證。飼料配方的設計方法很多，常用的有傳統手算法和現代計算機運算法。手算法包括試差法、對角線法和代數法等［1］，往往需要經過反復多次計算才能找到滿意結果，且無法得出效益最佳配方。電子計算機運算法包括線性規劃法、目標規劃法和模糊線性規劃法等［2-4］，其中開發最早、運用最普遍的是線性規劃法。線性規劃法將動物對營養物質的最適需要量和飼料原料的營養成分及價格作為已知條件，將滿足動物營養需要及飼料原料控制范圍作為約束條件，并將最小飼料成本作為配方設計的目標函數，根據線性規劃原理計算出既滿足營養要求又價格最低的飼料配方。筆者以青貯玉米秸稈、大豆秸粉、玉米、麥麩、豆粕、豆渣、大豆皮、預混料、磷酸氫鈣、食鹽等作為飼料來源，以20 kg肉羊日增重0.2 kg育肥為例，闡述利用Excel 2003規劃求解功能優化設計飼料配方方法與步驟。

1 材料與方法

1.1 數據收集

1.1.1 查詢肉羊飼養標準。依據《中華人民共和國農業行業標準——肉羊飼養標準(NY/T816-2004)》查得體重20 kg日增重0.2 kg育肥山羊每日營養需要量:消化能8.29 MJ，粗蛋白87 g，Ca 8.5 g，P 5.6 g 和食鹽 3.8 g。然后，再根據日采食量(0.76 kg/d)計算出各營養組分在育肥山羊飼料中的需求比例消化能10.91 MJ/kg、粗蛋白11.45%，Ca 1.12%，P 0.74%，食鹽0.50%。

1.1.2 查詢飼料原料營養組成及價格。依據《肉羊飼養標準》中的“中國羊常用飼料成分及營養價值表”以及“中國飼料成分及營養價值表(第24版)”和“羊常用飼料的營養成分［5］”查詢各飼料原料的營養組成，并根據各飼料原料市場單價以及干物質含量折算成干物質價格(表3)。

表1 飼料原料營養價值及干物質價格

1.2 電腦設置與操作

1.2.1 設置電腦“規劃求解”功能。打開Excel 2003電子表格操作軟件，點擊菜單欄“工具”，在下拉菜單中選擇“加載宏…”，在打開的加載宏復選框中勾選“規劃求解”，點“確定”。

1.2.2 輸入數據。如圖1設置工作表格式，并將育肥山羊飼料營養需求量和表1數據分別輸入相應單元格。其中，B4至K18為各種原料相應的營養含量或占比;B19至K19是初始配方重量，均給定為1;B20至K20是各原料的干物質價格;B23至K23為各原料的干物質含量;在N4至N8分別輸入相應的營養需求標準。

圖1 工作表格式及優化結果

1.2.3 設置約束條件。根據營養需求標準和山羊養殖知識，設置的約束條件下限、標準(固定值)以及上限如圖1所示。

1.2.4 輸入計算公式。點擊“L4”，輸入“=B4*$B$21+C4*$C$21+D4*$D$21+E4*$E$21+F4*$F$21+G4*$G$21+H4*$H$21+I4*$I$21+J4*$J$21+K4*$K$21”，點“√”，移動光標至“L4”單元格右下角，當光標形狀變成“+”時，拖動光標至“L20”;點擊“B22”，輸入“=B21*100”，點“√”，拖動“+”字光標至“K22”;同樣，點擊“B24”，輸入“=B22/B23”，點“√”，拖動“+”字光標至“K24”;點擊“B25”，輸入“=B24/$L$24*100”，點“√”，拖動“+”字光標至“K25”;點擊“B26”，輸入“=B24*0.76”，點“√”，拖動“+”字光標至“K26”，其中“0.76”為山羊每日干物質采食量;“L21”是同行左側數據的總和，點擊“L21”，再點擊工具欄的“∑”，用空“+”字光標選擇 B21至 K21區域，點“√”;同樣，“L22”、“L24”、“L25”、“L26”均為同行左側數據的總和，可用相同方法設置。

1.2.5 規劃求解。

(1)點擊“工具”菜單，選中“規劃求解”命令，彈出“規劃求解參數”對話框，選擇［設置目標單元格］編輯框，點擊工作表“L20”作為目標單元格，編輯框自動輸入“$L$20”，然后點擊［最小值］選項(圖2)。

(2)選擇［可變單元格］，用鼠標拖選工作表B21至K21區域，自動輸入“$B$21:$K$21”。

(3)選擇［約束］窗口，點擊“添加”按鈕，彈出“添加約束”對話框(圖3)，點擊工作表“L4”，［單元格引用位置］輸入欄自動輸入“$L$4”，選擇下拉窗口中“＞=”，點擊工作表“M4”，［約束值］輸入欄自動輸入“$M$4”，點擊“添加”按鈕，繼續添加約束條件:$L$5＞=$M$5;$L$6＞=$M$6;$L$7＞=$M$7;$L$8=$N$8;$L$9＞=$M$9;$L$9＜=$O$9;$L$10＞=$M$10;$L$10＜=$O$10;$L$11＞=$M$11;$L$11＜=$O$11;$L$12＞=$M$12;$L$12＜=$O$12;$L$13＞=$M$13;$L$13＜=$O$13;$L$14＞=$M$14;$L$14＜=$O$14;$L$15＞=$M$15;$L$15＜=$O$15;$L$16=$N$16;$L$17＞=$M$17;$L$17＜=$O$17;$L$18＞=$M$18;$L$18＜=$O$18;$L$19＞=$M$19。輸入完畢，點擊“確定”按鈕，回到“規劃求解參數”對話框。

圖2 “規劃求解參數”對話框

圖3 “添加約束”對話框

(4)點擊選項按鈕，進入“規劃求解選項”對話框(圖4)，選中“采用線形模型”、“假定非負”、“正切函數”、“向前差分”、“牛頓法”，其他均取默認值，然后點擊“確定”按鈕，回到“規劃求解參數”對話框。

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

(5)單擊求解按鈕，開始計算，當計算結束時彈出“規劃求解結果”對話框(圖5)，點擊“確定”按鈕，完成計算。也可在點擊“確定”按鈕前，在［報告］窗口，選擇“運算結果報告”、“敏感性報告”、“極限值報告”，這樣計算機會自動在工作表前顯示這3個報告(圖1)，這些報告對分析配方結果以及進一步調整約束條件具有重要參考價值。

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

2 結果與分析

根據圖1優化結果得到表2。其中，“干物質配方”系剔除含水量后各原料的配方，為理論配方，不能直接應用;“鮮重配方”為飼喂狀態下各原料含有水分的配方，可直接應用;“全日糧配制”為每只羊每天所需飼料組合，在具體應用時僅需乘以羊群數量即可配制整個羊群的全日糧。

圖1工作表中“L20”為目標單元格，顯示“2.34”，表示在該例約束條件及原料市場價格下該理論配方的價格最低，為2.34元/kg。

表2 最優飼料配方

3 討論

(1)有時由于原料結構不充分以及約束條件過度限制與營養指標不協調等原因，導致電腦無法給出最優解，此時電腦會給出一個近似解，可根據近似解對原料結構或約束條件進行適當調整，再進行計算，直至最終得到最優解。

(2)設置約束條件涉及動物營養專業，需要具備一定的營養學知識或養殖經驗。

(3)利用Excel線性規劃求解功能不僅適用于優化設計山羊育肥飼料配方，而且適用于其他畜禽飼料配方設計，該方法簡便、快捷，結果準確，適合廣大養殖專業戶應用。

［1］權凱，趙金艷.肉羊養殖實用新技術［M］.北京:金盾出版社，2013:183-186.

［2］鄒成義.現代動物飼料配方設計要點［J］.飼料工業，2012，33(17):57-59.

［3］黃漢英，熊先安，魏明新.計算機優化飼料配方軟件的研制［J］.糧食與飼料工業，1999(4):1-4.

［4］巴恒星，楊穎，孫瑤，等.應用Lpsolve建立模型在動物飼料配方設計中的研究［J］.飼料工業，2010，31(9):55-57.

［5］王志武，閆益波，李童.肉羊標準化規模養殖技術［M］.北京:中國農業科學技術出版社，2013:122-124.