EXCEL規(guī)劃求解法在豬日糧配方中的應(yīng)用

馬巧娥，閆紅軍

（楊凌職業(yè)技術(shù)學(xué)院a.信息工程分院；b.動(dòng)物工程分院，陜西楊凌712100）

飼料是養(yǎng)豬生產(chǎn)的物質(zhì)基礎(chǔ)。在養(yǎng)豬生產(chǎn)中，飼料成本占到生產(chǎn)總成本的70%左右，對養(yǎng)豬企業(yè)經(jīng)濟(jì)效益具有決定性作用。飼料配方的優(yōu)劣，不僅對不同生長階段豬的生長發(fā)育和生產(chǎn)潛力的發(fā)揮具有重要作用，還決定著豬肉產(chǎn)品的安全和養(yǎng)豬企業(yè)的可持續(xù)發(fā)展。因此，運(yùn)用科學(xué)方法設(shè)計(jì)豬的日糧配方，使其在滿足不同階段豬飼養(yǎng)標(biāo)準(zhǔn)和營養(yǎng)需要量的前提下，降低生產(chǎn)成本，獲得更理想的經(jīng)濟(jì)效益，一直是養(yǎng)豬企業(yè)研究的熱點(diǎn)問題［1－2］。

在實(shí)際生產(chǎn)中飼料配方設(shè)計(jì)方法雖然很多，但是各種不同方法的優(yōu)缺點(diǎn)不同。對角線法、試差法等傳統(tǒng)的手工配方設(shè)計(jì)計(jì)算繁瑣，費(fèi)時(shí)費(fèi)力，結(jié)果準(zhǔn)確性差，成本不好控制；飼料配方軟件雖然可以使計(jì)算工作量大幅度下降，準(zhǔn)確性提高，也便于控制成本，但價(jià)格昂貴，要求使用者必須掌握相關(guān)的計(jì)算機(jī)數(shù)據(jù)庫知識(shí)，初學(xué)者不易掌握。因此，在養(yǎng)豬企業(yè)日糧優(yōu)化配方設(shè)計(jì)中找出既計(jì)算簡便、結(jié)果準(zhǔn)確同時(shí)又符合生產(chǎn)實(shí)際、也易于控制成本的方法為大家所關(guān)心［3－4］。本文基于計(jì)算機(jī)辦公軟件教學(xué)研究與應(yīng)用實(shí)際，以EXCEL規(guī)劃求解功能設(shè)計(jì)豬日糧配方，為動(dòng)物飼料配方設(shè)計(jì)提供應(yīng)用模板。

1 EXCEL規(guī)劃求解函數(shù)安裝

在Microsoft Windows XP操作系統(tǒng)的電腦中安裝Microsoft Office System 2003時(shí)，選擇“自定義安裝”中的“全部安裝”，且“從本機(jī)運(yùn)行所有程序”，只需從“工具”菜單→“加載宏”命令→“加載宏”對話框中選中“規(guī)劃求解”復(fù)選框，然后單擊“確定”按鈕，就已經(jīng)安裝好“規(guī)劃求解”功能了。在操作時(shí)，只需要單擊“工具”菜單，點(diǎn)擊“規(guī)劃求解”命令就可以使用。

2 構(gòu)建線性規(guī)劃求解的數(shù)學(xué)模型

飼料配方設(shè)計(jì)問題可表示為線性規(guī)劃的優(yōu)化問題，并可用線性規(guī)劃計(jì)算滿足營養(yǎng)需要量下的最低成本配方。線性規(guī)劃最低成本配方優(yōu)化有下列幾個(gè)基本的假定：（1）只有一個(gè)目標(biāo)函數(shù)，一般情況下是求解配方的最低成本。該目標(biāo)函數(shù)是決策變量的線性函數(shù)。（2）決策變量是配方中各原料的用量。營養(yǎng)需要量可轉(zhuǎn)化為決策變量的線性函數(shù)，每個(gè)線性函數(shù)為一個(gè)約束條件。所有線性函數(shù)構(gòu)成系統(tǒng)的約束條件集。（3）最優(yōu)配方是在滿足約束條件下的最低成本配方。

3 EXCEL 規(guī)劃求解法在豬日糧配方中的應(yīng)用實(shí)例

3.1 配方原料選擇

按體重20～60kg階段生長肥育豬營養(yǎng)需要量選擇飼料原料，玉米、麩皮、豆粕、菜籽粕、進(jìn)口魚粉、4%預(yù)混料，要求飼料成本最低。

3.2 查閱飼養(yǎng)標(biāo)準(zhǔn)與各原料營養(yǎng)成分

查閱《中華人民共和國瘦肉型生長肥育豬飼養(yǎng)標(biāo)準(zhǔn)2004》可得生長育肥豬（20～60kg）基礎(chǔ)日糧所要求的各種營養(yǎng)指標(biāo)；查閱《飼料營養(yǎng)成分及價(jià)值表》得出所選飼料原料的營養(yǎng)成分；然后把所有的數(shù)據(jù)及原料的價(jià)格錄入EXCEL表格。

3.3 建立EXCEL飼料配方工作表

建立EXCEL飼料配方工作表，輸入飼養(yǎng)標(biāo)準(zhǔn)、原料營養(yǎng)成分（復(fù)合預(yù)混料的養(yǎng)分含量查閱產(chǎn)品說明）與價(jià)格，見圖1。

3.4 設(shè)置目標(biāo)單元格、可變單元格和約束條件

設(shè)置初始配方比例都為100%，即將“100.0%”輸入表1中的“配方比例”行對應(yīng)的所有原料單元格。

圖1 輸入所用各種飼料原料的營養(yǎng)成分Fig.1 Nutrient contents of various feedstuff

計(jì)算“合計(jì)含量”列各行左側(cè)各單元格的合計(jì)數(shù)。計(jì)算方法為：選中H2 單元格，輸入“＝B2＊＄B＄9＋C2＊＄C＄9＋D2＊＄D＄9＋E2＊＄E＄9＋F2＊＄F＄9＋G2＊＄G＄9”，上步結(jié)束后，點(diǎn)擊H2單元格按住垂直拖至H9［5］。

根據(jù)經(jīng)驗(yàn)設(shè)定各原料在配方中比例的上下限和營養(yǎng)指標(biāo)的上下限。各原料比例的上下限一定要用百分比表示，并按照你所希望配方保留的小數(shù)點(diǎn)位數(shù)輸入小數(shù)點(diǎn)后的位數(shù)。

點(diǎn)擊【工具】菜單下的【規(guī)劃求解】，彈出【規(guī)劃求解參數(shù)】框，在框內(nèi)第二行點(diǎn)選【最小值】后，將光標(biāo)移入第一行【設(shè)置目標(biāo)單元格】右側(cè)空格內(nèi)，再單擊選定最小值目標(biāo)單元格（本例為“H8”），則剛才【設(shè)置目標(biāo)單元格】右側(cè)空格內(nèi)即彈出“＄H＄8”。

點(diǎn)擊【規(guī)劃求解參數(shù)】對話框第一行“＄H＄8”右側(cè)的小方框，即彈出只有一行的【規(guī)劃求解參數(shù)】框，框內(nèi)顯示“＄H＄8”，再點(diǎn)擊右側(cè)的小方框，即回原彈出【規(guī)劃求解參數(shù)】框。

選定可變單元格。先將光標(biāo)置【可變單元格】下的空框內(nèi)，點(diǎn)選B9并按住平移至H9，則【可變單元格】下的空框內(nèi)即自行彈出“＄B＄9：＄H＄9”，即設(shè)定配方比例的可變單元格，見圖2。

圖2 設(shè)定可變單元格Fig.2 Setting of variable table cells

設(shè)置約束條件。設(shè)定各原料的配方比例小于等于配方比例最大值：點(diǎn)擊“規(guī)劃求解參數(shù)”對話框中的【添加】按鈕即彈出“添加約束”框，光標(biāo)置“單元格引用位置”下空框內(nèi)，點(diǎn)選B9單元格并按住平移至H9，則“單元格引用位置”下空框內(nèi)即彈出“＄B＄9：＄H＄9”，在同行右側(cè)選定“≤”，將光標(biāo)置“約束值”下框內(nèi)，點(diǎn)選B11單元格并按住平移至H11，則“約束值”下空框內(nèi)即彈出“＄B＄11：＄H＄11”，表示引用單元格值小于等于對應(yīng)的約束單元格值，即＄B＄9：＄H＄9≤＄B＄11：＄H＄11，見圖3。

圖3 添加約束條件Fig.3 Adding of constraint condition

設(shè)定各原料的配方比例大于等于比例最小值：再次點(diǎn)擊“規(guī)劃求解參數(shù)”對話框中的【添加】按鈕即彈出“添加約束”框，光標(biāo)置“單元格引用位置”下空框內(nèi)，點(diǎn)選B9單元格并按住平移至H9，則“單元格引用位置”下空框內(nèi)即彈出“＄B＄9：＄H＄9”，在同行右側(cè)選定“≥”，將光標(biāo)置“約束值”下框內(nèi)，點(diǎn)選B10單元格并按住平移至H10，則“約束值”下空框內(nèi)即彈出“＄B＄10：＄H＄10”，表示引用單元格值大于等于對應(yīng)的約束單元格值，即＄B＄9：＄H＄9≥＄B＄10：＄H＄10。

設(shè)定配方合計(jì)含量小于等于飼養(yǎng)標(biāo)準(zhǔn)要求最大值：第三次點(diǎn)擊“規(guī)劃求解參數(shù)”對話框中的【添加】按鈕即彈出“添加約束”框，光標(biāo)置“單元格引用位置”下空框內(nèi)，點(diǎn)選H2單元格并按住平移至H7，則“單元格引用位置”下空框內(nèi)即彈出“＄H＄2：＄H＄7”，在同行右側(cè)選定“≤”，將光標(biāo)置“約束值”下框內(nèi)，點(diǎn)選K2單元格并按住平移至K7，則“約束值”下空框內(nèi)即彈出“＄K＄2：＄K＄7”，表示引用單元格值小于等于對應(yīng)的約束單元格值，即＄H＄2：＄H＄7≤＄K＄2：＄K＄7。

設(shè)定配方合計(jì)含量大于等于飼養(yǎng)標(biāo)準(zhǔn)要求最小值：第四次點(diǎn)擊“規(guī)劃求解參數(shù)”對話框中的【添加】按鈕即彈出“添加約束”框，光標(biāo)置“單元格引用位置”下空框內(nèi)，點(diǎn)選H2單元格并按住平移至H7，則“單元格引用位置”下空框內(nèi)即彈出“＄H＄2：＄H＄7”，在同行右側(cè)點(diǎn)選“≥”，將光標(biāo)置“約束值”下框內(nèi)，點(diǎn)選J2單元格并按住平移至J7，則“約束值”下空框內(nèi)即彈出“＄J＄2：＄J＄7”，表示引用單元格值大于等于對應(yīng)的約束單元格值，即＄H＄2：＄H＄7≥＄J＄2：＄J＄7。

點(diǎn)擊“添加約束”對話框中的【確定】按鈕后，在第五行"約束"下空白處逐次顯示出添加的約束內(nèi)容，見圖4。

圖4 顯示添加的約束條件Fig.4 Display of constraint condition added

3.5 規(guī)劃求解

點(diǎn)擊“規(guī)劃求解參數(shù)”對話框中的【求解】按鈕，彈出“規(guī)劃求解結(jié)果”對話框，選擇“保存規(guī)劃求解結(jié)果”，點(diǎn)擊【確定】按鈕，則EXCEL 表中配方比例行顯示出各原料的配方比例，保存結(jié)果待用，見圖5。

圖5 顯示求解結(jié)果Fig.5 Display of results

3.6 “無解”分析與處理

分析可能出現(xiàn)“無解”的原因主要有：飼料原料營養(yǎng)成分含量之間相互矛盾；動(dòng)物營養(yǎng)需要量定的過高，而選擇的飼料原料品質(zhì)太差；約束條件過高，而且互相沖突；飼料原料種類過多或過少［6－8］。認(rèn)真研究配方，必要時(shí)作適當(dāng)調(diào)整，以更加適應(yīng)當(dāng)?shù)厣a(chǎn)和市場的實(shí)際情況，更加符合設(shè)計(jì)目標(biāo)。

4 小結(jié)

應(yīng)用EXCEL規(guī)劃求解功能設(shè)計(jì)飼料配方，不但可以節(jié)約購買專用飼料配方軟件的費(fèi)用，并且可以隨原料和市場的變化方便地更新數(shù)據(jù)和約束條件，簡化了手工配方復(fù)雜、繁重的計(jì)算任務(wù)，節(jié)省時(shí)間，可以適應(yīng)現(xiàn)代小型養(yǎng)豬場和飼料廠的飼料配方要求，具有一定的實(shí)用價(jià)值和推廣價(jià)值。該方法需要設(shè)計(jì)者具有扎實(shí)的動(dòng)物營養(yǎng)專業(yè)知識(shí)和一定的實(shí)踐經(jīng)驗(yàn)。

［1］宋亞攀，王繼文，孫麗萍.利用Excel2003"線性規(guī)劃"程序優(yōu)化設(shè)計(jì)飼料配方［J］.飼料研究，2006（1）：62－66.

［2］王學(xué)敏，任守文.利用Excel表格自配豬飼料配方［J］.豬業(yè)科學(xué)，2011（10）：76－78.

［3］張宏福.動(dòng)物營養(yǎng)參數(shù)與飼養(yǎng)標(biāo)準(zhǔn)［M］.北京：中國農(nóng)業(yè)出版社，2010.

［4］劉 琴，陳志遠(yuǎn)，屈 剛.利用Excel設(shè)計(jì)飼料配方［J］.當(dāng)代畜牧，2006（8）：13－15.

［5］黃志文，李當(dāng).用EXCEL 進(jìn)行飼料配方［J］.計(jì)算機(jī)與農(nóng)業(yè)，2001（1）：24－25.

［6］鄭 蕉，涂傳清.Excel在多目標(biāo)規(guī)劃求解和靈敏度分析中的應(yīng)用［J］.中國管理信息化：綜合版，2006（9）：45－47.

［7］孫愛萍，王瑞梅.如何利用Excel求解線性規(guī)劃問題及其靈敏度分析［J］.辦公自動(dòng)化，2009（22）：44－51.

［8］龍際飛，趙勝軍，劉葉枝，等.利用Excel線性規(guī)劃設(shè)計(jì)優(yōu)化畜禽飼料配方［J］.黑龍江畜牧獸醫(yī)，2008（2）：55－57.