利用Excel線性規(guī)劃優(yōu)化設(shè)計山羊飼料配方

(江蘇沿江地區(qū)農(nóng)業(yè)科學(xué)研究所，江蘇如皋226541)

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

1 材料與方法

1.1 數(shù)據(jù)收集

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

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

表1 飼料原料營養(yǎng)價值及干物質(zhì)價格

1.2 電腦設(shè)置與操作

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

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

圖1 工作表格式及優(yōu)化結(jié)果

1.2.3 設(shè)置約束條件。根據(jù)營養(yǎng)需求標(biāo)準(zhǔn)和山羊養(yǎng)殖知識，設(shè)置的約束條件下限、標(biāo)準(zhǔn)(固定值)以及上限如圖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”，點“√”，移動光標(biāo)至“L4”單元格右下角，當(dāng)光標(biāo)形狀變成“+”時，拖動光標(biāo)至“L20”;點擊“B22”，輸入“=B21*100”，點“√”，拖動“+”字光標(biāo)至“K22”;同樣，點擊“B24”，輸入“=B22/B23”，點“√”，拖動“+”字光標(biāo)至“K24”;點擊“B25”，輸入“=B24/$L$24*100”，點“√”，拖動“+”字光標(biāo)至“K25”;點擊“B26”，輸入“=B24*0.76”，點“√”，拖動“+”字光標(biāo)至“K26”，其中“0.76”為山羊每日干物質(zhì)采食量;“L21”是同行左側(cè)數(shù)據(jù)的總和，點擊“L21”，再點擊工具欄的“∑”，用空“+”字光標(biāo)選擇 B21至 K21區(qū)域，點“√”;同樣，“L22”、“L24”、“L25”、“L26”均為同行左側(cè)數(shù)據(jù)的總和，可用相同方法設(shè)置。

1.2.5 規(guī)劃求解。

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

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

(3)選擇［約束］窗口，點擊“添加”按鈕，彈出“添加約束”對話框(圖3)，點擊工作表“L4”，［單元格引用位置］輸入欄自動輸入“$L$4”，選擇下拉窗口中“＞=”，點擊工作表“M4”，［約束值］輸入欄自動輸入“$M$4”，點擊“添加”按鈕，繼續(xù)添加約束條件:$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。輸入完畢，點擊“確定”按鈕，回到“規(guī)劃求解參數(shù)”對話框。

圖2 “規(guī)劃求解參數(shù)”對話框

圖3 “添加約束”對話框

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

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

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

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

2 結(jié)果與分析

根據(jù)圖1優(yōu)化結(jié)果得到表2。其中，“干物質(zhì)配方”系剔除含水量后各原料的配方，為理論配方，不能直接應(yīng)用;“鮮重配方”為飼喂?fàn)顟B(tài)下各原料含有水分的配方，可直接應(yīng)用;“全日糧配制”為每只羊每天所需飼料組合，在具體應(yīng)用時僅需乘以羊群數(shù)量即可配制整個羊群的全日糧。

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

表2 最優(yōu)飼料配方

3 討論

(1)有時由于原料結(jié)構(gòu)不充分以及約束條件過度限制與營養(yǎng)指標(biāo)不協(xié)調(diào)等原因，導(dǎo)致電腦無法給出最優(yōu)解，此時電腦會給出一個近似解，可根據(jù)近似解對原料結(jié)構(gòu)或約束條件進(jìn)行適當(dāng)調(diào)整，再進(jìn)行計算，直至最終得到最優(yōu)解。

(2)設(shè)置約束條件涉及動物營養(yǎng)專業(yè)，需要具備一定的營養(yǎng)學(xué)知識或養(yǎng)殖經(jīng)驗。

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

［1］權(quán)凱，趙金艷.肉羊養(yǎng)殖實用新技術(shù)［M］.北京:金盾出版社，2013:183-186.

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

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

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

［5］王志武，閆益波，李童.肉羊標(biāo)準(zhǔn)化規(guī)模養(yǎng)殖技術(shù)［M］.北京:中國農(nóng)業(yè)科學(xué)技術(shù)出版社，2013:122-124.