Excel設計和驗證豬濃縮飼料配方圖解

■鄒成義 尹思明 張 杰

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

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

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

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

1 建立Excel表格和計算公式

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

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

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

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

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

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

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

圖2 Excel原始工作表

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

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

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

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

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

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

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

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

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

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

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

2 規(guī)劃求解

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

2.1 約束條件

2.1.1 原料用量控制

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

圖3 加載宏對話框

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

圖5 目標單元格和可變單元格設置

圖6 添加約束條件對話框

圖7 原料用量約束條件設置

2.1.2 原料配比總和控制

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

圖8 原料配比總和約束條件設置

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

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

圖9 配方主要營養(yǎng)成分指標約束條件設置

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

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

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

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

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

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

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

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

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

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

選擇該項，直接點“確定”，即可保存有效的運算結(jié)果。

2.3.2 恢復為原值〔O〕

選擇該項，點“確定”，即廢棄新的計算結(jié)果，重新恢復到求解前的配方。

2.3.3 查看報告詳情

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

圖13 運算結(jié)果報告

3 配方驗證

利用已經(jīng)建立的Excel原始工作表(如圖2),也可以對正在使用的配方或過往配方進行驗證，檢驗其營養(yǎng)指標是否符合四川省地方標準《豬用濃縮飼料》（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%、維生素預混料0.05%、微量元素預混料0.50%。在Excel原始工作表中的配比B4～B15欄輸入以上數(shù)據(jù)，則自動生成E17～K17欄的營養(yǎng)成分計算值以及L17的配方價格(如圖14)。

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

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

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

4 小結(jié)

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