Micros oft office Exce l 2010規(guī)劃求解在飼料配方設(shè)計(jì)中的應(yīng)用

◆作者：閔遙謝秋萍向思琳翟俊磊王美艷王長(zhǎng)康高玉云

◆單位：1.福建農(nóng)林大學(xué)動(dòng)物科學(xué)學(xué)院2.福建省綠色食品發(fā)展中心

自上世紀(jì)90年代以來，我國(guó)畜牧業(yè)迅猛發(fā)展。而養(yǎng)殖成本的60%～70%都來自飼料，因此設(shè)計(jì)出一個(gè)較優(yōu)的飼料配方直接關(guān)系到飼料廠和養(yǎng)殖戶的經(jīng)濟(jì)效益。飼料配方的設(shè)計(jì)不單單考慮飼料的成本，還要考慮動(dòng)物的營(yíng)養(yǎng)需要、動(dòng)物的生理特性、資源的合理利用及原料的適口性等，才可設(shè)計(jì)出價(jià)格低廉、效果良好、效益高的飼料配方（張乃峰等，2010）。在飼料配方的設(shè)計(jì)上，市場(chǎng)上專業(yè)的飼料配方軟件價(jià)格高昂，大多數(shù)軟件使用不夠便捷，因此沒能得到廣泛普及，傳統(tǒng)的人工計(jì)算法（如對(duì)角線法、試差法、代數(shù)法）計(jì)算程序繁瑣而效率低下（王學(xué)敏等，2011）。如今，計(jì)算機(jī)在畜牧業(yè)中的統(tǒng)計(jì)分析、飼料生產(chǎn)配方中的優(yōu)化設(shè)計(jì)、生產(chǎn)和經(jīng)營(yíng)管理的優(yōu)化設(shè)計(jì)、飼料廠的自動(dòng)化生產(chǎn)中的地位舉足輕重。而大多數(shù)計(jì)算機(jī)自帶office功能，普通飼料廠及養(yǎng)殖戶只要查詢動(dòng)物的營(yíng)養(yǎng)需要，各飼料原料的營(yíng)養(yǎng)價(jià)值及使用量，通過Microsoft office Excel線性規(guī)劃求解便足夠滿足生產(chǎn)需求，線性規(guī)劃的科學(xué)、高效、實(shí)用、準(zhǔn)確、便捷等優(yōu)點(diǎn)也足以媲美專業(yè)的飼料配方軟件。下文將以蛋雞的日糧配方來簡(jiǎn)單描述線性規(guī)劃的“最小值”及“目標(biāo)值”的求解方法。

1 軟件安裝

在 Windows 7、8或 10系統(tǒng)安裝 Microsoft office Excel 2010，在使用Excel進(jìn)行規(guī)劃求解之前需要加載線性規(guī)劃求解功能。具體步驟為：第一步，打開Excel；第二步，單擊功能菜單左上角“文件”；第三步，在彈出的菜單欄單擊“選項(xiàng)”；第四步，在彈出的頁面單擊“加載項(xiàng)”；第五步，在轉(zhuǎn)換的頁面底端單擊“轉(zhuǎn)到”；第六步，在彈出的窗口勾選“規(guī)劃求解加載項(xiàng)”并單擊“確定”。上述六步即可完成規(guī)劃求解的加載。如圖1。

完成加載后即可在Excel功能菜單的“數(shù)據(jù)”中找到規(guī)劃求解功能。如圖2。

2 建立線性規(guī)劃求解數(shù)學(xué)模型

線性規(guī)劃（Linear programming,LP）是運(yùn)籌學(xué)中一個(gè)主要研究線性約束條件下線性目標(biāo)函數(shù)的極值問題，輔助人們進(jìn)行科學(xué)管理的數(shù)學(xué)方法。實(shí)際問題中建模一般步驟為：根據(jù)影響所要達(dá)到目的的因素找到?jīng)Q策變量；由決策變量和所在達(dá)到目的之間的函數(shù)關(guān)系確定目標(biāo)函數(shù)；再由決策變量所受的限制條件確定決策變量所要滿足的約束條件（張孝珍等，2016）。

本文將根據(jù)蛋雞的飼養(yǎng)標(biāo)準(zhǔn)及飼料原料營(yíng)養(yǎng)價(jià)值擬定一個(gè)蛋雞的飼料配方（僅供說明使用），參考《中華人民共和國(guó)農(nóng)業(yè)行業(yè)標(biāo)準(zhǔn)—雞飼養(yǎng)標(biāo)準(zhǔn)（NY/T 33-2004）》及 2004年第 15版飼料原料數(shù)據(jù)庫(kù)將動(dòng)物營(yíng)養(yǎng)需要量、飼料原料和飼料原料營(yíng)養(yǎng)成分?jǐn)?shù)據(jù)錄入表格。

圖1 線性規(guī)劃的加載

圖2 完成加載后的界面

圖3 數(shù)據(jù)的錄入

2.1 數(shù)據(jù)錄入

建立Excel表格，分別在A1輸入“飼料名稱”；在 A2～A12 輸入各種飼料原料“名稱”；A13輸入“合計(jì)”；A14輸入“飼養(yǎng)標(biāo)準(zhǔn)”；A15輸入“與標(biāo)準(zhǔn)的差”；A16輸入“最低成本”；在B1輸入“配比”；在C1輸入“最小用量”，C2～C12輸入的最小用量必須大于等于零；在D1輸入“最大用量”，D2～D12 輸入的最大用量要根據(jù)原料的特性及實(shí)踐經(jīng)驗(yàn)確定；在E1～L1分別輸入各種飼料原料的營(yíng)養(yǎng)成分“名稱”；E2～L12輸入飼料原料營(yíng)養(yǎng)成分含量，以此類推在各飼料原料的對(duì)應(yīng)區(qū)域輸入飼料原料營(yíng)養(yǎng)成分含量；在M1輸入“價(jià)格”，M2～M12輸入的飼料原料的市場(chǎng)單價(jià)；根據(jù)當(dāng)?shù)貙?shí)際情況隨時(shí)調(diào)整。如圖3。

2.2 公式的輸入

①SUMPRODUCT()函數(shù)表示Excel表格中前面一列與某一列各對(duì)應(yīng)值之間積的和，即SUMPRODUCT(A1∶ An，C1∶Cn)=A1*C1+A2*C2+A3*C3+…… +An*Cn，本文中用SUMPRODUCT()函數(shù)來計(jì)算飼料中各種原料中某種營(yíng)養(yǎng)成分與飼料中相應(yīng)的原料配比的乘積之和。公式中所用的符合“$”表示絕對(duì)引用，是為了輸入的方便，為了在拉動(dòng)公式時(shí)使公式中的單元格引用不發(fā)生變化（張孝珍等，2016）。在以下單元格內(nèi)分別輸入公式（裴少軍，2008）。

E13輸入SUMPRODUCT(E2:E16,$B2:$B12)/100；

F13輸入SUMPRODUCT(F2:F16,$B2:$B12)/100；

G13輸入SUMPRODUCT(G2:G16,$B2:$B12)/100；

H13輸入SUMPRODUCT(H2:H16,$B2:$B12)/100；

I13輸入SUMPRODUCT(I2:I16,$B2:$B12)/100；

J13輸入SUMPRODUCT(J2:J16,$B2:$B12)/100；

K13輸入SUMPRODUCT(K2:K16,$B2:$B12)/100；

L13輸入SUMPRODUCT(L2:L16,$B2:$B12)/100；

M13輸入SUMPRODUCT(M2:M16,$B2:$B12)/100；

②B13表示各飼料配比的總和，在B13單元格輸入SUM(B2:B12)。

③E15～M15表示規(guī)劃求解的合計(jì)部分與飼養(yǎng)標(biāo)準(zhǔn)的差值，在E15單元格輸入E14-E13，按Enter鍵后，選中該單元格將鼠標(biāo)移動(dòng)到單元格右側(cè)，向右拉動(dòng)出現(xiàn)的“+”字光標(biāo)，即可自動(dòng)填充到F15到M15。

④B16表示該飼料配方的最低成本，在B16單元格輸SUMPRODUCT(B2:B12,M2:M12)/100

圖4 求最小值約束條件

圖5 求目標(biāo)值約束條件

3 進(jìn)行規(guī)劃求解

3.1 求“最小值”

通常情況下我們需要降低飼料配方的成本，即把“最低成本”設(shè)置為目標(biāo)單元格，選中“B16”單元格進(jìn)行規(guī)劃求解，隨之進(jìn)行各規(guī)劃求解參數(shù)的設(shè)置。

3.1.1設(shè)置目標(biāo)單元格

在我們選擇“B16”單元格之后，打開規(guī)劃求解功能時(shí)，系統(tǒng)默認(rèn)“B16”為目標(biāo)單元格，也可以在打開規(guī)劃求解功能后再選擇目標(biāo)單元格。然后根據(jù)自己的要求選擇“最大值”、“最小值”，或者根據(jù)自己需求設(shè)置“目標(biāo)值”。本文首先需要求飼料配方的最低成本，即選“最小值”。

3.1.2設(shè)置可變單元格

“可變單元格”為各飼料配比，即在可變單元格選項(xiàng)輸入“$B$2:$B$12”，同樣可以直接用鼠標(biāo)在Excel中直接拖動(dòng)選擇。

3.1.3設(shè)置約束條件

在遵守約束選項(xiàng)右側(cè)點(diǎn)擊“添加”，在出現(xiàn)的文本框分別輸入“$B$14=100 、$B$2:$B$12＜=$D$2:$D$12、$B$2:$B$12＞=$C$2:$C$12、$E$13:$G$13＞=$E$14:$G$14、$I$13:$G$13＞=$I$14:$J$14、$L$13＞=$L$14（其中磷及蛋氨酸在本配方中非必須限制條件，沒有進(jìn)行約束。）。分別代表的意思為配比總和要達(dá)到100%、各組飼料原料的配比要小于等于各飼料原料的最大添加量而大于等于最小添加量、各營(yíng)養(yǎng)指標(biāo)要大于等于飼養(yǎng)標(biāo)準(zhǔn)。所有約束條件輸入結(jié)束其它選項(xiàng)保持默認(rèn)。如圖4。

圖6 可能結(jié)果一

圖7 可能結(jié)果二

3.2 求“目標(biāo)值”

在某些情況下，我們資源充足，用量不大，只想設(shè)計(jì)較好的飼料配方，例如試驗(yàn)用配方。此時(shí)我們可以選擇“配方合計(jì)B13”為目標(biāo)單元格，打開線性規(guī)劃功能。

3.2.1設(shè)置目標(biāo)單元格

與上述方法相似，我們?cè)O(shè)置“B13”為目標(biāo)單元格，選擇“目標(biāo)值”并將其設(shè)置為100，即保證飼料配方滿足100%。

3.2.2設(shè)置可變單元格

可變單元格的設(shè)置與求“最小值”一樣，不用再作改動(dòng)。即“$B$2:$B$12”。

3.2.3設(shè)置約束條件

可約束條件還是滿足各組飼料原料的配比要小于等于各飼料原料的最大添加量而大于等于最小添加量、各營(yíng)養(yǎng)指標(biāo)要大于等于飼養(yǎng)標(biāo)準(zhǔn)。去掉配比總和要達(dá)到100%這一約束條件，因?yàn)榇舜嗡划?dāng)做規(guī)劃求解的“目標(biāo)值”。即$B$2:$B$12＜=$D$2:$D$12、$B$2:$B$12＞=$C$2:$C$12、$E$13:$G$13＞=$E$14:$G$14、$I$13:$G$13＞=$I$14:$J$14、$L$13＞=$L$14。如圖 5。

4 結(jié)果與分析

點(diǎn)擊“求解”，在彈出的對(duì)話窗口中單擊“確定”，運(yùn)算結(jié)果分為兩種情況（張發(fā)榮等，2015）。

第一種情況，結(jié)果顯示“規(guī)劃求解找不到有用的解”，這種情況下代表規(guī)劃求解找不到所有約束的點(diǎn)，即沒有最優(yōu)解，此時(shí)得出的結(jié)果是比較靠近最優(yōu)解的一個(gè)值。在實(shí)際應(yīng)用當(dāng)中，通過觀察其結(jié)果調(diào)整更改約束的條件，或者調(diào)整飼料原料即可應(yīng)用于生產(chǎn)，亦可直接使用。在本例中我們只要降低部分飼料原料的最低添加量即可找到最優(yōu)解。如圖6。

第二種情況，結(jié)果顯示“規(guī)劃求解找到一解，可滿足所有的約束條件及最優(yōu)狀況”。這種情況下得出的結(jié)果即是最優(yōu)解。如圖7。

圖8 最低成本結(jié)果

4.1 求“最小值”結(jié)果

在求得“最小值”的結(jié)果中，麩皮的單價(jià)低于玉米，但是規(guī)劃求解的結(jié)果不建議使用麩皮，可能的原因是玉米的代謝能比麩皮高了6.74MJ/kg，近兩倍，這樣麩皮的綜合價(jià)值低于玉米。當(dāng)下中美貿(mào)易摩擦導(dǎo)致的大豆價(jià)格上漲，因此我們可以酌情減少豆粕用量，而提高其他粕的含量?；ㄉ傻膬r(jià)格略高于豆粕，但花生粕營(yíng)養(yǎng)價(jià)值較高，棉籽粕、菜籽粕的營(yíng)養(yǎng)價(jià)值低于豆粕和花生粕，我們可以在允許范圍內(nèi)（花生粕易感染黃曲霉毒素，因而飼用量受到一定限制）調(diào)整花生粕的最大添加量，規(guī)劃求解會(huì)盡可能選擇花生粕替代豆粕。這樣最低成本從原來的2.113元/kg降到了2.106元/kg。調(diào)整好配方后選擇“保存規(guī)劃求解結(jié)果”，單擊“確定”按鈕，即可在目標(biāo)單元格顯示最優(yōu)解的結(jié)果，其結(jié)果既滿足動(dòng)物營(yíng)養(yǎng)需要，又使成本最低，可以直接投入生產(chǎn)中使用。如圖8。

有最優(yōu)解的情況下，如需制作報(bào)告大綱，可以在點(diǎn)擊“確定”之前選擇想要制作的報(bào)告并勾選“制作報(bào)告大綱”，確定之后運(yùn)算結(jié)果報(bào)告會(huì)顯示在頁腳，也可以為結(jié)果命名。報(bào)告分為三部分內(nèi)容，分別是“運(yùn)算結(jié)果報(bào)告”、“敏感性報(bào)告”、“極限值報(bào)告”。其中，“運(yùn)行結(jié)果報(bào)告”表示求出的最優(yōu)解與設(shè)置的約束條件之間的差值；“敏感性報(bào)告”中的“拉格朗日乘數(shù)”即為影子價(jià)格，表示當(dāng)現(xiàn)有的解保持最優(yōu)的時(shí)候，約束條件的右端項(xiàng)每增加或降低一個(gè)單位時(shí)，目標(biāo)函數(shù)增加或降低的改變量；“極限值報(bào)告”為所設(shè)置的目標(biāo)函數(shù)的值，即最優(yōu)飼料配方的價(jià)格（宋亞攀,等，2006）。

4.2 求“目標(biāo)值”結(jié)果

同樣，當(dāng)我們按照類似步驟求“目標(biāo)值”時(shí)。以實(shí)驗(yàn)配方為例，為了盡可能避免飼料原料對(duì)實(shí)驗(yàn)的干擾，我們盡量讓配方單一。因此我們可以在原配方的基礎(chǔ)上將麩皮及雜粕的最大添加量均調(diào)整為0。制作報(bào)告大綱同上求“最小值”的方法。

5 討論

5.1 配方設(shè)計(jì)的注意事項(xiàng)

在設(shè)置約束條件時(shí)，不能對(duì)所有營(yíng)養(yǎng)物質(zhì)的合計(jì)用量進(jìn)行限定，即大于等于飼養(yǎng)標(biāo)準(zhǔn)，否則對(duì)這個(gè)配方來說，線性規(guī)劃有可能找不到唯一解，同時(shí)也要考慮約束條件是否太少。所以根據(jù)以上原則，我們?cè)谂浞街兄粚?duì)部分營(yíng)養(yǎng)物質(zhì)合計(jì)進(jìn)行限定，其中磷和蛋氨酸總含量沒有進(jìn)行約束。大家在進(jìn)行配方設(shè)計(jì)的時(shí)候也應(yīng)當(dāng)根據(jù)實(shí)際情況進(jìn)行設(shè)置，不斷修改約束條件，直到得出理想的結(jié)果。

配方設(shè)計(jì)的過程中，不能單一的考慮營(yíng)養(yǎng)水平，還要考慮各飼料原料之間的相互作用，具有拮抗的飼料原料要避免使用；飼料原料的抗?fàn)I養(yǎng)因子及有害物質(zhì)也會(huì)影響配方的效果（許家玉，2012）；飼料原料的適口性影響動(dòng)物的采食量，適口性差的飼料原料要酌情使用。

5.2 配方的調(diào)整

線性規(guī)劃約束條件的設(shè)置需要有一定的專業(yè)知識(shí)和依據(jù)，一些剛性的約束條件必須滿足，例如必須滿足飼養(yǎng)標(biāo)準(zhǔn)、必須滿足配方百分比為100%等。在設(shè)計(jì)的過程中，飼料原料的最大用量和最小用量為非剛性條件，通過在行業(yè)內(nèi)的相關(guān)平臺(tái)查詢之后，根據(jù)飼料原料的價(jià)格及營(yíng)養(yǎng)價(jià)值人為改動(dòng)。

在實(shí)際生產(chǎn)中，我們需要最低成本，即求“最小值”，配方的飼料原料可以復(fù)雜多樣，這樣在規(guī)劃求解時(shí)，擁有更多的選擇性，在能滿足飼養(yǎng)標(biāo)準(zhǔn)的前提下，我們可以盡量選擇單價(jià)更低的飼料原料。在得出最低成本之后，如果與預(yù)期值仍有差距，依然可以更改飼料原料的最大添加量、最小添加量來調(diào)整配方以滿足自己的預(yù)期值。

而試驗(yàn)用配方則恰好相反，因用量不大，價(jià)格因素不是首要因素，我們?cè)谠O(shè)置飼料配比的“目標(biāo)值”為100后，應(yīng)該力求配方簡(jiǎn)單、優(yōu)質(zhì)，飼料原料盡可能的少。原料越少，影響因素越少，試驗(yàn)添加的藥品或者添加劑等越能明確地反映作用效果。因此，我們可以根據(jù)已有配方刪減部分原料進(jìn)行試驗(yàn)配方設(shè)計(jì)，滿足飼養(yǎng)標(biāo)準(zhǔn)即可。

5.3 線性規(guī)劃的優(yōu)缺點(diǎn)

Excel中有許多隱含功能，線性規(guī)劃這一基本功能常常被我們忽視，掌握這一功能可以為中小飼料廠及個(gè)體養(yǎng)殖戶減少開支，而且通過簡(jiǎn)單的學(xué)習(xí)即可輕松上手，不存在很強(qiáng)的專業(yè)性。相對(duì)于專業(yè)的飼料配方軟件，它免費(fèi)使用而且操作簡(jiǎn)單、可操作性強(qiáng)；而相對(duì)于傳統(tǒng)的人工計(jì)算高效快捷，人工計(jì)算一般為試差法、對(duì)角線法和代數(shù)法等（權(quán)凱等，2013），需要反復(fù)多次計(jì)算，并且無法得到最佳配方設(shè)計(jì)（顧國(guó)華等，2014）。

在“規(guī)劃求解”中也有缺點(diǎn)，例如需要提前對(duì)其進(jìn)行數(shù)據(jù)的錄入，公式及約束條件的編輯等，同時(shí)約束條件的數(shù)量有限，最多500個(gè)，另外附加條件還可以再添加100個(gè)（宋亞攀等，2006），但是這足夠滿足絕大多數(shù)用戶的需求。諸多的飼料原料的最大添加量、最小添加量在找不到最優(yōu)解的情況下，要根據(jù)求解的結(jié)果人工調(diào)整；飼料原料也需要根據(jù)市場(chǎng)行情尋找替換物。