■鄒成義 尹思明 張 杰

（1.四川省畜牧科學(xué)研究院，四川成都 610066；2.重慶市渝北區(qū)畜牧水產(chǎn)站，重慶 401120；3.四川省畜科飼料有限公司，四川成都 610066）

計(jì)算機(jī)在日常生產(chǎn)和工作中的應(yīng)用越來(lái)越普及。常用辦公軟件Microsoft Office Excel自身提供的“加載宏”中有“規(guī)劃求解”一項(xiàng)，可以解決各種線性規(guī)劃任務(wù)。根據(jù)豬濃縮飼料產(chǎn)品標(biāo)準(zhǔn)、飼料原料營(yíng)養(yǎng)成分、市場(chǎng)價(jià)格，以及配方設(shè)計(jì)的要求和經(jīng)驗(yàn)，建立Excel原始工作表，將有關(guān)參數(shù)填于表中，設(shè)置有關(guān)約束條件后，即可直接規(guī)劃求解濃縮飼料配方。該法具有數(shù)據(jù)表示直觀、計(jì)算方便、數(shù)據(jù)修改容易等優(yōu)點(diǎn)，無(wú)需使用專用飼料配方軟件，在Excel界面下，通過(guò)鼠標(biāo)或鍵盤操作即可計(jì)算出最低成本的濃縮飼料配方，非常適用于飼料廠優(yōu)化飼料配方設(shè)計(jì)。

濃縮飼料是由蛋白質(zhì)飼料、礦物元素、維生素、營(yíng)養(yǎng)性和非營(yíng)養(yǎng)性添加劑等按一定比例配制的均勻混合物。濃縮飼料的配比（即其占配合飼料的使用比例）通常為15%～40%。配比太低，用戶需配合的飼料種類增加，不僅影響成本，而且產(chǎn)品質(zhì)量難以控制；配比太高，就失去“濃縮”的意義。濃縮飼料配方設(shè)計(jì)，其營(yíng)養(yǎng)成分指標(biāo)可參照濃縮飼料產(chǎn)品的國(guó)家標(biāo)準(zhǔn)、地方標(biāo)準(zhǔn)和企業(yè)標(biāo)準(zhǔn)。就豬的濃縮飼料產(chǎn)品標(biāo)準(zhǔn)而言，還未見(jiàn)有相應(yīng)的國(guó)家標(biāo)準(zhǔn)，現(xiàn)行的地方標(biāo)準(zhǔn)主要有四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）[1]和陜西省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB61/T 393—2009）[2]。對(duì)飼料原料選擇和營(yíng)養(yǎng)價(jià)值的確定與配合飼料基本相同，但濃縮料經(jīng)過(guò)高度濃縮后，一些影響外觀特性或內(nèi)在質(zhì)量穩(wěn)定的因素也會(huì)顯現(xiàn)，因此，要對(duì)相關(guān)原料的適宜用量給予高度重視。

本文參照四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006），以體重60 kg以上的肥育豬濃縮飼料配方為例，展示如何用大豆粕、菜籽粕、魚粉、碳酸鈣、磷酸氫鈣、L-賴氨酸鹽酸鹽、食鹽、抗氧化劑、防霉劑、氯化膽堿、維生素預(yù)混料和純微量元素預(yù)混料，設(shè)計(jì)最低成本的濃縮飼料配方，供讀者參考。

1 建立Excel表格和計(jì)算公式

首先，確定濃縮飼料的使用比例，本例中確定為20%。其次，按照相應(yīng)標(biāo)準(zhǔn)中對(duì)各養(yǎng)分指標(biāo)的水平要求，計(jì)算出濃縮料配方。本例中四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）規(guī)定肥育豬濃縮飼料主要營(yíng)養(yǎng)成分指標(biāo)如下：粗蛋白質(zhì)≥30.0%、粗纖維≤13.5%、粗灰分≤18.0%、鈣1.5%～4.0%、總磷≥1.1%、食鹽1.5%～3.0%、賴氨酸≥1.6%。

1.1 建立所用飼料原料主要營(yíng)養(yǎng)成分Excel表

建立所用飼料原料主要營(yíng)養(yǎng)成分Excel表（如圖1所示），表內(nèi)營(yíng)養(yǎng)成分?jǐn)?shù)據(jù)來(lái)自《中國(guó)飼料成分及營(yíng)養(yǎng)價(jià)值表》[3-4]、相關(guān)原料標(biāo)準(zhǔn)和產(chǎn)品信息，也可通過(guò)消化試驗(yàn)和實(shí)驗(yàn)室化學(xué)分析實(shí)測(cè)。

圖1 飼料原料主要營(yíng)養(yǎng)成分Excel表

1.2 建立配方制作Excel原始工作表及計(jì)算公式

在飼料原料主要營(yíng)養(yǎng)成分表基礎(chǔ)上，建立Ex?cel原始工作表（如圖2所示）。

在圖2中，A4～A15欄是配方設(shè)計(jì)所用各種原材料的名稱。B4～B15欄是擬設(shè)計(jì)配方所用各種原材料的計(jì)算配比值（%）。選定B2～B16欄，將其設(shè)置成藍(lán)色（或其它色）、加粗字體，以便計(jì)算出配比結(jié)果后醒目觀察。

圖2 Excel原始工作表

B16欄是規(guī)劃求解后，由程序自動(dòng)計(jì)算出的每種原料用量（配比）的總和。在B16輸入公式“=SUM(B4∶B15)”。

C4～C15欄是規(guī)定設(shè)計(jì)配方時(shí)所用各種原材料的最低用量（%）。這要根據(jù)經(jīng)驗(yàn)估計(jì)，或者是配方中硬性規(guī)定必須使用的量，如抗氧化劑、防霉劑、氯化膽堿、維生素預(yù)混料、微量元素預(yù)混料等。對(duì)肥育豬濃縮飼料而言，這幾種原料的常用量如下：抗氧化劑0.05%、防霉劑0.10%、氯化膽堿0.50%、維生素預(yù)混料0.05%、微量元素預(yù)混料0.50%。食鹽按四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》中規(guī)定的低限值添加。

D4～D15欄是規(guī)定設(shè)計(jì)配方時(shí)所用各種原材料的最大用量（%）。這需要根據(jù)原料的特點(diǎn)（如菜籽粕的抗?fàn)I養(yǎng)因子）和市場(chǎng)供給狀況，限制某些原料的最高用量?；蛘呤桥浞街杏残砸?guī)定必須使用的量，或依據(jù)經(jīng)驗(yàn)估計(jì)。本例確定濃縮飼料的使用比例為20%，則大豆粕、菜籽粕、魚粉的量是按照配合飼料中的最大用量乘以5得出的；假設(shè)濃縮飼料中的鈣、磷、賴氨酸分別全部由碳酸鈣、磷酸氫鈣和L-賴氨酸鹽酸鹽提供，則按照四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）規(guī)定的指標(biāo)：鈣1.5%～4.0%、總磷≥1.1%、賴氨酸≥1.6%，分別計(jì)算出碳酸鈣、磷酸氫鈣和L-賴氨酸鹽酸鹽的最大用量為：10.40%、6.00%和2.04%。對(duì)以下原料的最大用量進(jìn)行硬性規(guī)定：抗氧化劑0.10%、防霉劑0.20%、氯化膽堿0.50%、維生素預(yù)混料0.05%、微量元素預(yù)混料0.50%。食鹽按四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》中規(guī)定的高限值添加。

E2～K2欄是《豬用濃縮飼料》（DB51/T 269—2006）產(chǎn)品標(biāo)準(zhǔn)中的主要營(yíng)養(yǎng)指標(biāo)項(xiàng)目，可根據(jù)需要進(jìn)行修改或增減。

E4～K15區(qū)域欄是所用飼料原料營(yíng)養(yǎng)成分的含量，數(shù)據(jù)來(lái)源于《中國(guó)飼料成分及營(yíng)養(yǎng)價(jià)值表》、相關(guān)原料標(biāo)準(zhǔn)和產(chǎn)品信息或?qū)崪y(cè)數(shù)據(jù)。

E17～K17欄是規(guī)劃求解后，根據(jù)每種原料的用量計(jì)算出的飼料配方中營(yíng)養(yǎng)成分含量的合計(jì)值。單擊單元格E17，輸入公式“=SUMPROD?UCT($B4∶$B15,E$4∶E$15)/100”，按Enter 確認(rèn)輸入，最后利用自動(dòng)句柄填充功能（即把鼠標(biāo)放到E17單元格的右下角，當(dāng)其變成黑色實(shí)心十字時(shí)向右拖動(dòng)即可），將輸入的公式填充到F17至K17。選定E17至K17欄，將其設(shè)置成藍(lán)色（或其它色）、加粗字體，以便計(jì)算出配比結(jié)果后醒目觀察。

L4～L15欄是所用飼料原料的價(jià)格(元/kg),為購(gòu)買的實(shí)際價(jià)格。L17欄是規(guī)劃求解后,根據(jù)每種原料的用量(配比)和價(jià)格,由程序自動(dòng)計(jì)算出的飼料總成本(元/kg),也是此規(guī)劃求解的目標(biāo)函數(shù)值。在L17單元格輸入公式“=SUMPRODUCT(B4∶B15,L4∶L15)/100”，或把鼠標(biāo)放到K17單元格的右下角，當(dāng)其變成黑色實(shí)心十字時(shí)向右拖動(dòng)即可。

選定L4至L17欄，點(diǎn)鼠標(biāo)右鍵，點(diǎn)“設(shè)置單元格格式（F）”，再點(diǎn)“數(shù)字”，然后點(diǎn)“貨幣”，在“貨幣符號(hào)（國(guó)家/地區(qū)）（S）”欄內(nèi)，選擇相應(yīng)貨幣符號(hào)，點(diǎn)“確定”。選定L17欄，將其設(shè)置成藍(lán)色（或其它色）、加粗字體，以便計(jì)算出配比結(jié)果后醒目觀察。

E18～K18欄是所設(shè)計(jì)濃縮飼料配方所選用四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）所規(guī)定指標(biāo)下限。選定E18～K18欄，將其設(shè)置成紅色（或其它色）、加粗字體，以便與配方計(jì)算出的營(yíng)養(yǎng)成分對(duì)比時(shí)醒目觀察。

E19～K19欄是所設(shè)計(jì)濃縮飼料配方所選用四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）所規(guī)定指標(biāo)上限。選定E19～K19欄，將其設(shè)置成紅色（或其它色）、加粗字體，以便與配方計(jì)算出的營(yíng)養(yǎng)成分對(duì)比時(shí)醒目觀察。

E20～K20欄是所設(shè)計(jì)配方的主要營(yíng)養(yǎng)成分計(jì)算值與所選用標(biāo)準(zhǔn)的差，主要是便于直觀查看配方設(shè)計(jì)是否合理，便于設(shè)定約束條件，進(jìn)一步規(guī)劃求解直到滿意為止。單擊單元格E20，輸入公式“=E17-E18”，按Enter確認(rèn)輸入，最后利用自動(dòng)句柄填充功能，將輸入的公式填充到F20～K20欄，并注意檢查公式是否正確。其中F20欄和G20欄是對(duì)應(yīng)的17欄分別與19欄相減。

2 規(guī)劃求解

以Excel 2003為例，在選項(xiàng)窗口點(diǎn)擊“工具”，選擇“加載宏”，出現(xiàn)“加載宏”對(duì)話框（如圖3所示），選擇“規(guī)劃求解”，點(diǎn)“確定”。然后再回到Ex?cel的最上面，點(diǎn)擊“工具”，選擇“規(guī)劃求解”，出現(xiàn)“規(guī)劃求解參數(shù)”對(duì)話框（如圖4所示），當(dāng)光標(biāo)在“設(shè)置目標(biāo)單元格”內(nèi)閃爍時(shí)，將鼠標(biāo)移到L17欄，也就是飼料配方的“最低成本”單元格，即“$L$17”，單擊，在下面一欄選擇“最小值”，在“可變單元格”（即所選12種原料的配比）內(nèi)輸入b4∶b15，形成如圖5所示的對(duì)話框，點(diǎn)擊“添加”按鈕，出現(xiàn)如圖6所示的“添加約束”條件對(duì)話框。

2.1 約束條件

2.1.1 原料用量控制

在“單元格引用位置”內(nèi)輸入b4∶b15，中間按鈕選擇“＜ =”，在“約束值〔C〕”框內(nèi)輸入d4∶d15，亦即對(duì)原料的最大用量進(jìn)行限制，點(diǎn)“確定”；然后，點(diǎn)“添加”，再次出現(xiàn)添加約束條件對(duì)話框，在“單元格引用位置”內(nèi)輸入b4∶b15，中間按鈕選擇“＞ =”，在“約束值〔C〕”框內(nèi)輸入c4∶c15，亦即對(duì)原料的最小用量進(jìn)行限制，點(diǎn)“確定”，形成如圖7所示的對(duì)話框。

圖3 加載宏對(duì)話框

圖4 規(guī)劃求解參數(shù)對(duì)話框

圖5 目標(biāo)單元格和可變單元格設(shè)置

圖6 添加約束條件對(duì)話框

圖7 原料用量約束條件設(shè)置

2.1.2 原料配比總和控制

點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入b16，中間按鈕選擇“=”，在“約束值〔C〕”框內(nèi)輸入100，點(diǎn)“確定”，亦即所有原料配比的總和為100%，如圖8所示。

圖8 原料配比總和約束條件設(shè)置

2.1.3 配方主要營(yíng)養(yǎng)成分指標(biāo)E17～K17區(qū)域的控制

對(duì)粗蛋白質(zhì)、粗纖維、粗灰分、鈣、總磷、食鹽、賴氨酸指標(biāo)進(jìn)行約束。粗蛋白質(zhì)，點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入e17，中間按鈕選擇“＞ =”，在“約束值〔C〕”框內(nèi)輸入e18，點(diǎn)“確定”，亦即對(duì)粗蛋白質(zhì)指標(biāo)進(jìn)行約束；粗纖維，點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入f17，中間按鈕選擇“＜ =”，在“約束值〔C〕”框內(nèi)輸入f19，點(diǎn)“確定”，亦即對(duì)粗纖維指標(biāo)進(jìn)行約束；粗灰分，點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入g17，中間按鈕選擇“＜ =”，在“約束值〔C〕”框內(nèi)輸入g19，點(diǎn)“確定”，亦即對(duì)灰分指標(biāo)進(jìn)行約束；鈣，點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入h17，中間按鈕選擇“＜ =”，在“約束值〔C〕”框內(nèi)，輸入h19，點(diǎn)“確定”，再點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入h17，中間按鈕選擇“＞ =”，在“約束值〔C〕”框內(nèi)輸入h18，點(diǎn)“確定”，亦即對(duì)鈣指標(biāo)進(jìn)行約束；總磷，點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入i17，中間按鈕選擇“＞=”，在“約束值〔C〕”框內(nèi)輸入i18，點(diǎn)“確定”，亦即對(duì)總磷指標(biāo)進(jìn)行約束；食鹽，點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入j17，中間按鈕選擇“＜ =”，在“約束值〔C〕”框內(nèi)，輸入j19，點(diǎn)“確定”，再點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入j17，中間按鈕選擇“＞=”，在“約束值〔C〕”框內(nèi)輸入j18，點(diǎn)“確定”，亦即對(duì)食鹽指標(biāo)進(jìn)行約束；賴氨酸，點(diǎn)“添加”，在“單元格引用位置”內(nèi)輸入k17，中間按鈕選擇“＞ =”，在“約束值〔C〕”框內(nèi)輸入k18，點(diǎn)“確定”，亦即對(duì)賴氨酸指標(biāo)進(jìn)行約束（見(jiàn)圖9）。至此，規(guī)劃求解的約束參數(shù)設(shè)置完畢。

圖9 配方主要營(yíng)養(yǎng)成分指標(biāo)約束條件設(shè)置

2.2 規(guī)劃求解及結(jié)果運(yùn)行

規(guī)劃求解參數(shù)設(shè)置完畢后，單擊“規(guī)劃求解參數(shù)”對(duì)話框中的“求解”按鈕，計(jì)算機(jī)將立即出現(xiàn)“規(guī)劃求解結(jié)果”框（見(jiàn)圖10），即“規(guī)劃求解找到一解，可滿足所有的約束及最優(yōu)狀況”。最優(yōu)解，即為既滿足營(yíng)養(yǎng)指標(biāo)限制條件，又使成本最低的飼料配方。選擇“保存規(guī)劃求解結(jié)果〔K〕”，單擊“確定”按鈕即可，最終出現(xiàn)如圖11所示的配方結(jié)果報(bào)告。其中，B4～B15欄為各種飼料原料的配比（%）；E17～K17分別是該配方的粗蛋白質(zhì)、粗纖維、粗灰分、鈣、總磷、食鹽、賴氨酸指標(biāo)；L17是配方價(jià)格（元/kg）。

最終得到60 kg以上肥育豬濃縮飼料配方為：大豆粕55.75%、菜籽粕30.00%、碳酸鈣7.92%、磷酸氫鈣2.48%、食鹽2.65%、抗氧化劑0.05%、防霉劑0.10%、氯化膽堿0.50%、維生素預(yù)混料0.05%、微量元素預(yù)混料0.50%。魚粉和L-賴氨酸鹽酸鹽未選擇，說(shuō)明通過(guò)優(yōu)化配方，其余原料提供的營(yíng)養(yǎng)成分能夠滿足各種營(yíng)養(yǎng)指標(biāo)的限制條件。配方營(yíng)養(yǎng)成分指標(biāo)：粗蛋白質(zhì)36.22%、粗纖維6.83%、粗灰分18.00%、鈣4.00%、總磷1.10%、食鹽2.65%、賴氨酸1.88%。配方價(jià)格3.46元/kg。

從E20～K20欄可以看出，配方的所有營(yíng)養(yǎng)指標(biāo)均滿足四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）對(duì)肥育豬的要求，只是賴氨酸略高（可接受范圍）、粗灰分和總磷剛好處于臨界值。當(dāng)然，這只是一個(gè)演算過(guò)程，實(shí)際生產(chǎn)配方要通過(guò)指標(biāo)的修訂不斷完善。

圖10 規(guī)劃求解結(jié)果

圖11 配方結(jié)果報(bào)告

有時(shí)，約束條件太苛刻，就可能會(huì)出現(xiàn)“規(guī)劃求解找不到有用的解”，如本例中將粗纖維的下限指標(biāo)設(shè)置為8.00%，就會(huì)出現(xiàn)如圖12所示的情況，點(diǎn)“確定”，可根據(jù)經(jīng)驗(yàn)重新對(duì)約束條件進(jìn)行調(diào)整，反復(fù)幾次后，即可使規(guī)劃求解成功。

圖12 規(guī)劃求解結(jié)果2——找不到有用的解

2.3 規(guī)劃求解結(jié)果選擇

2.3.1 保存規(guī)劃求解結(jié)果〔K〕

選擇該項(xiàng)，直接點(diǎn)“確定”，即可保存有效的運(yùn)算結(jié)果。

2.3.2 恢復(fù)為原值〔O〕

選擇該項(xiàng)，點(diǎn)“確定”，即廢棄新的計(jì)算結(jié)果，重新恢復(fù)到求解前的配方。

2.3.3 查看報(bào)告詳情

在“報(bào)告〔R〕”中，可選擇運(yùn)算結(jié)果報(bào)告、敏感性報(bào)告和極限值報(bào)告，點(diǎn)“確定”，在屏幕最左下方，分別生成“運(yùn)算結(jié)果報(bào)告1”、“敏感性報(bào)告1”和“極限值報(bào)告1”，再點(diǎn)擊各個(gè)報(bào)告，查看詳情。其中，運(yùn)算結(jié)果報(bào)告（見(jiàn)圖13）反映約束條件滿足狀況，“型數(shù)值”指求解值與限定值之間的差距，在無(wú)法求得最優(yōu)解時(shí)，可據(jù)此適當(dāng)調(diào)節(jié)約束條件。

圖13 運(yùn)算結(jié)果報(bào)告

3 配方驗(yàn)證

利用已經(jīng)建立的Excel原始工作表(如圖2),也可以對(duì)正在使用的配方或過(guò)往配方進(jìn)行驗(yàn)證，檢驗(yàn)其營(yíng)養(yǎng)指標(biāo)是否符合四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）的規(guī)定。例如，現(xiàn)行配方如下：大豆粕49.00%、菜籽粕35.00%、魚粉2.00%、L-賴氨酸鹽酸鹽0.10%、碳酸鈣6.00%、磷酸氫鈣4.00%、食鹽2.70%、抗氧化劑0.05%、防霉劑0.10%、氯化膽堿0.50%、維生素預(yù)混料0.05%、微量元素預(yù)混料0.50%。在Excel原始工作表中的配比B4～B15欄輸入以上數(shù)據(jù)，則自動(dòng)生成E17～K17欄的營(yíng)養(yǎng)成分計(jì)算值以及L17的配方價(jià)格(如圖14)。

圖14 配方驗(yàn)證結(jié)果

將E17～K17欄與相對(duì)應(yīng)的18、19欄比較，就可直觀看出現(xiàn)行配方的營(yíng)養(yǎng)指標(biāo)是否滿足四川省地方標(biāo)準(zhǔn)《豬用濃縮飼料》（DB51/T 269—2006）的要求。若不符合標(biāo)準(zhǔn)要求，或在“配比”一欄輸入數(shù)據(jù)進(jìn)行簡(jiǎn)單調(diào)整，或按照前面步驟進(jìn)行規(guī)劃求解出滿足標(biāo)準(zhǔn)的最低成本配方。

本例為計(jì)算方便，只列出12種原料，實(shí)際生產(chǎn)中使用的原料還很多，可按照上述方法列表。另外，部分中小型飼料廠和規(guī)模養(yǎng)豬場(chǎng)使用商業(yè)復(fù)合預(yù)混料產(chǎn)品，包含氨基酸、維生素、微量元素、載體和稀釋劑等，在建立Excel原始工作表時(shí)，應(yīng)列出其所含的粗蛋白質(zhì)、鈣、磷、氨基酸等指標(biāo)。

4 小結(jié)

利用Excel自帶的“規(guī)劃求解”功能，設(shè)計(jì)最低成本豬濃縮飼料配方和驗(yàn)證現(xiàn)行配方，具有方法簡(jiǎn)單、快捷、準(zhǔn)確、方便等特點(diǎn)，電子表格中的數(shù)據(jù)排列靈活多變，可自行設(shè)置。非常適用于各種中小型飼料廠、規(guī)模養(yǎng)豬場(chǎng)進(jìn)行優(yōu)化飼料配方設(shè)計(jì)。