Excel 2007線性規(guī)劃程序在設(shè)計生長肥育豬飼料配方中的應(yīng)用

摘 要：本文探討利用Excel 2007規(guī)劃求解功能設(shè)計最佳飼料配方的方法，實現(xiàn)隨飼養(yǎng)標(biāo)準(zhǔn)、飼料原料的種類及價格的變化而變化的最佳飼料配方設(shè)計。該方法克服手工算法營養(yǎng)指標(biāo)少、費時等局限性，全面合理平衡飼料營養(yǎng)、成本和經(jīng)濟效益的關(guān)系，最大限度降低飼料成本，非常適合在校學(xué)生和中小型飼料企業(yè)或規(guī)模化養(yǎng)殖場使用。

關(guān)鍵詞：Excel 2007；線性規(guī)劃求解；生長肥育豬；飼料配方

中圖分類號：S815.4 文獻標(biāo)識碼：A 文章編號：1001-0769（2015）06-0030-03

1 材料與方法

用《瘦肉型豬飼養(yǎng)標(biāo)準(zhǔn)（NY/T 65-2004）》和玉米、小麥麩、豆粕、棉籽粕、菜籽粕、魚粉、小蘇打、石粉、磷酸氫鈣、食鹽（加碘鹽）、植物油和1 %預(yù)混料等飼料原料，利用Excel 2007規(guī)劃求解功能優(yōu)化設(shè)計體重20 kg～35 kg生長肥育豬飼料配方。

2 建立Excel電子表格

查《瘦肉型豬飼養(yǎng)標(biāo)準(zhǔn)（NY/T 65-2004）》，得到體重20 kg～35 kg生長肥育豬基礎(chǔ)日糧各種營養(yǎng)指標(biāo)，并結(jié)合生產(chǎn)經(jīng)驗作以適當(dāng)修改；查《中國飼料成分及營養(yǎng)價值表（2004年第15版）》，得出所選飼料原料的營養(yǎng)成分，把所有的數(shù)據(jù)錄入Excel 2007表格（圖1）。

在A3：A14單元格分別輸入所選的12種飼料原料的名稱。

在A2：Q2單元格分別輸入飼料名稱、配比（%）、最小用量（%）、最大用量（%）、粗蛋白（%）、消化能（MJ/kg）、鈣（%）、總磷（%）、非植酸磷（%）、賴氨酸（%）、蛋氨酸（%）、蛋氨酸+胱氨酸（%））蘇氨酸（%）、色氨酸（%）、鈉（%）、氯（%）、價格（元/kg）。

在E3：P14單元格區(qū)域中分別輸入12種飼料原料的營養(yǎng)成分及營養(yǎng)價值。

在Q3：Q14單元格中分別輸入12種飼料原料的價格（價格是當(dāng)時當(dāng)?shù)氐氖袌鰞r，各地可根據(jù)當(dāng)?shù)貙嶋H情況隨時調(diào)整）。

在A16：P16單元格中依次輸入字符“飼養(yǎng)標(biāo)準(zhǔn)”、“體重20 kg～35 kg生長肥育豬的飼養(yǎng)標(biāo)準(zhǔn)”。

在A15單元格輸入字符“合計”，E15：P15單元格分別輸入計算配方營養(yǎng)價值的計算值（12種飼料原料所含營養(yǎng)價值與配比乘積的和）。例如，配方中粗蛋白的計算公式是“E15=SUMPRODUCT（B3：B14，E3：E14）/$B$15”（$B$15表示絕對引用單元格），其他營養(yǎng)指標(biāo)的計算以此類推。

在A17：P17單元格中輸入“與標(biāo)準(zhǔn)的差”，要在各相應(yīng)單元格中輸入“與標(biāo)準(zhǔn)的差”的計算公式。例如，粗蛋白與標(biāo)準(zhǔn)的差的計算公式是“E17=El5-El6”。

在Q15中輸入配方的最低成本的計算公式，表示配方的成本，成本計算公式是“Q15=SUMPRODUCT（B3：B14，Q3：Q14）/B15”。

在B3：B14單元格中分別輸入“配比”，即所設(shè)計的生長肥育豬飼料配方。

在C3：C14單元格和D3：D14單元格中分別輸入人為設(shè)定的各種飼料原料的最小用量和最大用量，對原料的使用量進行限制。

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

線性規(guī)劃法是采用運籌學(xué)的有關(guān)數(shù)學(xué)原理進行飼料配方優(yōu)化設(shè)計一種方法，該法將飼料配方中的有關(guān)因素和限制條件轉(zhuǎn)化為線性數(shù)學(xué)函數(shù)，求解一定約束條件下的目標(biāo)值（最大值或最小值）。利用線性規(guī)劃程序設(shè)計飼料配方的首要問題是建立數(shù)學(xué)模型，使誤差允許范圍內(nèi)滿足營養(yǎng)需要的配方成本最低，從而實現(xiàn)飼料生產(chǎn)成本的最大利潤。

3.1 約束條件

利用線性規(guī)劃設(shè)計飼料配方的約束條件有以下幾個方面：①營養(yǎng)需要量。配方的營養(yǎng)量必須滿足動物的飼養(yǎng)標(biāo)準(zhǔn)，允許有一定的誤差許可范圍，比如能量和蛋白的允許誤差范圍設(shè)為0.1 %。這需要把飼養(yǎng)標(biāo)準(zhǔn)和計算值分別列出來，并增加“與標(biāo)準(zhǔn)的差”這一行（表中第16行），作為約束條件。②原料用量限制。由于飼料原料自身的營養(yǎng)特點和價格決定了各種原料在配方中的比例有一定的限制。比如棉籽粕、菜籽粕等由于含有抗?fàn)I養(yǎng)因子，其用量一般不高于5 %，豬食鹽的用量為0.3 %～0.5 %。③總量限制。總量限制即各種飼料原料之和為一定值，一般是100 %。④多種原料配合時，所得的營養(yǎng)總量應(yīng)為各原料配比提供的相應(yīng)營養(yǎng)成分之和。⑤目標(biāo)函數(shù)是決策變量（配比）的線性函數(shù)。飼料配方目標(biāo)函數(shù)，一般指飼料配方成本的最小值，也可以是配方收益的最大值，最優(yōu)配方為不破壞約束條件的最低成本配方或最大收益配方。

3.2 數(shù)學(xué)模型

設(shè)Xj（X1，X2，X3，…，Xn）為參與配方的各種原料的用量，n為原料個數(shù)，m為約束條件（即營養(yǎng)指標(biāo)數(shù)），Aij（i=1，2，3，…，m；j=1，2，3，…，n）為各種原料相應(yīng)的營養(yǎng)價值（即n種飼料原料的m個營養(yǎng)指標(biāo)）；Bi（B1，B2，B3，…，Bm）為配方中相應(yīng)滿足的各種營養(yǎng)指標(biāo)或重量指標(biāo)的預(yù)定值；Cj（C1，C2，C3，…，Cn）為每種原料相應(yīng)的價格系數(shù)，W0為所有飼料原料用量之和（1，100，100 %等），Z為目標(biāo)值，則：

目標(biāo)函數(shù)：Min Z =∑（Cn×Xn）

約束條件：

∑（A1n×Xn）≥B1（or≤，=）

∑（A2n×Xn）≥B2（or≤，=）

……

∑（Amn×Xn）≥Bm（or≤，=）

∑（Xn）= W0=100

Xj≥0

3.3 線性規(guī)劃問題的解法

上述線性規(guī)劃飼料配方計算模型由于含有多個不等式，計算時應(yīng)建立標(biāo)準(zhǔn)模型：

MinZ =∑（Cn×Xn）

∑（A1n×Xn）=B1

∑（A2n×Xn）=B2

……

∑（Amn×Xn）=Bm

∑（Xn）= W0=100

Xj≥0

4 規(guī)劃求解

4.1 設(shè)置規(guī)劃求解參數(shù)

Excel 2007電子表格建好之后，即可規(guī)劃求解。單擊“工具”菜單中的“規(guī)劃求解”，彈出“規(guī)劃求解參數(shù)”對話框（圖2）。對規(guī)劃求解參數(shù)進行設(shè)置，“設(shè)置目標(biāo)單元格”為“目標(biāo)函數(shù)”的單元格（$Q$15），也就是飼料配方的“最低成本”單元格；設(shè)置“等于”項為最小值，表示對目標(biāo)函數(shù)求最小值；“可變單元格”也就是所選12種原料的配比即“$B$3：$B$14”。設(shè)置約束條件，單擊“規(guī)劃求解參數(shù)”對話框中的“添加”按鈕，彈出“添加約束”對話框（圖3），首先輸入第一個約束條件，在“單元格引用位置”鼠標(biāo)單擊，選擇單元格B3：B14，選擇“<=”，在“約束值”鼠標(biāo)單擊，選擇單元格D3：D14，鼠標(biāo)單擊“添加”按鈕，即飼料配比不能超過飼料最大用量。其它約束條件根據(jù)配方需求依次確定。第二個約束條件：$B$3：$B$14>=$C$3：$C$14，即飼料配比最小值；第三個約束條件：$E$15：$P$15>=$E$16：$P$16，即各營養(yǎng)指標(biāo)須滿足飼養(yǎng)標(biāo)準(zhǔn)；第四個約束條件：$B$15=100，即各飼料配比之和等于100。規(guī)劃求解參數(shù)全部設(shè)置完畢（圖2），單擊“求解”按鈕即可規(guī)劃求解（圖4）。

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

規(guī)劃求解結(jié)果可能會出現(xiàn)以下兩種情況：①規(guī)劃求解找到一解，可滿足所有的約束條件及最優(yōu)狀況（圖4），表示規(guī)劃求解成功。選擇“保存規(guī)劃求解結(jié)果”，單擊“確定”按鈕，即可在B3：B14單元格看到最優(yōu)解（既滿足營養(yǎng)需要和限制條件，又使成本最低的飼料配方）。②“規(guī)劃求解找不到有用的解”，系統(tǒng)將顯示一個與最優(yōu)解最接近的參考配方，有一定的參考價值。

5 結(jié)果和討論

5.1 Excel 2007規(guī)劃求解使飼料配方設(shè)計優(yōu)化

用Excel 2007規(guī)劃求解功能進行飼料配方設(shè)計是完全可行的。配方時需準(zhǔn)確認(rèn)真錄人數(shù)據(jù)、計算公式、約束條件等等。Excel 2007規(guī)劃求解將大大地提高工作效率，使復(fù)雜、繁瑣的數(shù)學(xué)問題簡單化。筆者進行了多次試驗，結(jié)果表明該方法可使配方成本降低0.125元/kg左右，每頭豬可以節(jié)省25.5元～40.5元成本。對萬頭規(guī)模的豬場來說，每年可以多賺40萬元左右。另外，根據(jù)飼料原料價格的波動，實時調(diào)整飼料配方。例如甘肅地區(qū)的玉米，2010年1月2 000元/t，2010年9月初價格為2 200元/t。根據(jù)經(jīng)驗，玉米的價格波動大于200元/t時，就需要對配方進行適當(dāng)?shù)恼{(diào)整，以降低成本。

5.2 使用SUM PRODUCT函數(shù)

使用SUMPRODUCT函數(shù)，可以使繁瑣、復(fù)雜的公式錄入大大簡化，并且便于增減原料。例如，計算消化能（DE）的公式“F15=（B3*F3+B4*F4+B5*F5+B6*F6+B7*F7+B8*F8+B9*F9+B10*F10+B11*F11+B12*F12+B13*F13+B14*F14）/B15”，可以簡化為：“F15=SUMPRODUCT（B3：B14，F(xiàn)3：F14）/B15”，SUMPRODUCT函數(shù)使復(fù)雜、繁瑣的公式錄入簡單化。

5.3 原料使用量的限制

根據(jù)飼養(yǎng)管理和飼料配方經(jīng)驗，熟悉各種飼料原料的營養(yǎng)特點，限制飼料原料用量最小值和最大值。如豬食鹽的用量一般在0.3 %～ 0.5 %，預(yù)混料為0.01 %～5 %，魚粉因價格原因一般不超過10 %，菜籽粕、棉籽粕等因含有抗?fàn)I養(yǎng)因子等用量不能過多。

5.4 軟件安裝

在缺省狀態(tài)下，Microsoft Office 2007沒有安裝“規(guī)劃求解”功能，需要雙擊Excel 2007圖標(biāo)，打開Excel 2007。在Excel 2007窗口，單擊“工具”菜單下“加載宏”項，打開對話框，選擇“規(guī)劃求解”復(fù)選框，然后“確定”。如果“規(guī)劃求解加載項”未在“可用加載宏”中列出，請單擊“瀏覽”找到“加載宏”，否則需重新安裝軟件。□□

參考文獻：（6篇，略）