奶牛標(biāo)準(zhǔn)和飼料配方

1、奶牛飼養(yǎng)標(biāo)準(zhǔn)自動(dòng)生成及用Excel的“規(guī)劃求解”擬制奶牛飼料配方 韓友文（東北農(nóng)業(yè)大學(xué) 動(dòng)物科技學(xué)院 哈爾濱 150030）現(xiàn)今，作為信息技術(shù)載體的計(jì)算機(jī)，已普及到企業(yè)、機(jī)關(guān)、學(xué)校、研究機(jī)構(gòu)各個(gè)單位。飼料和養(yǎng)殖行業(yè)甩開紙筆進(jìn)行日糧和飼料配方計(jì)算已成現(xiàn)實(shí)。軟件市場(chǎng)也有多種飼料配方專用軟件可供選購(gòu)使用。本文要介紹的是不花一分錢買軟件，用每一計(jì)算機(jī)都裝有的MS-Office中Excel的“規(guī)劃求解”-“加載宏”，完成奶牛的日糧和精料配方的擬制，并達(dá)到最低成本要求。大家知道，奶牛作為畜牧養(yǎng)殖的重要草食動(dòng)物，不同于精料型自由采食的豬和禽類，基本上是個(gè)體計(jì)量單喂。奶牛日糧中的飼草和其他非精飼料組分（青貯料

2、、根莖類、糟渣類）非常重要，且要求占日糧的一定比例范圍。奶牛日糧中的精飼料部分，通常配成精料補(bǔ)充料形式。在每次擠奶同時(shí)分別喂給。這些，無(wú)疑都給擬制最低成本奶牛日糧配方帶來(lái)計(jì)算和操作上的某些復(fù)雜性。做配方，遇到第一個(gè)問(wèn)題是：確定對(duì)象奶牛的飼養(yǎng)標(biāo)準(zhǔn)。查標(biāo)準(zhǔn)表采點(diǎn)加合計(jì)算，既煩瑣，又不連續(xù)。我們根據(jù)標(biāo)準(zhǔn)提供的參數(shù)和相關(guān)數(shù)據(jù)，轉(zhuǎn)化成數(shù)學(xué)模型，再用VBA編程。作到了輸入奶牛的必要參數(shù)，就能準(zhǔn)確、快速、自動(dòng)生成配方對(duì)象奶牛的飼養(yǎng)標(biāo)準(zhǔn)。（圖1）“規(guī)劃求解”的數(shù)學(xué)模型與數(shù)學(xué)中的線性規(guī)劃模型相同。其基本數(shù)學(xué)表達(dá)式如下：配方解-結(jié)構(gòu)變量 Xi 0 (i = 1,2,m-1,m)最低成本目標(biāo)函數(shù) Z = è

3、;min配方要求約束條件 （, , ） bi (j = 1,2,n-1,n)引申的約束條件可能有： Xi Xi下 ；Xi Xi等 ；Xi Xi上 (飼料原料約束量)bg / bh（, ）K下, K上 (例：蛋白/能量 比)/ （ ，）R下 , R上 (例：飼草DM占日糧DM的比例)上述式中的符號(hào)和參數(shù)：Xi結(jié)構(gòu)變量，即待求的配方解，相當(dāng)于配方中個(gè)原料組分占總量的比例（% 或 g/kg）。m 參與配方組成的飼料原料數(shù)； n 為飼料配方所定的各種指標(biāo)數(shù)。Z 目標(biāo)函數(shù)，取最小值（èmin），相當(dāng)于日糧或配方的飼料原料成本（￥/kg）。Ci 第i 種飼料原料的市場(chǎng)價(jià)格（￥/kg）。aij 第

4、i 種飼料原料的第j 種屬性數(shù)據(jù)，相當(dāng)于飼料原料的營(yíng)養(yǎng)指標(biāo)含量(%，g/kg，MJ/kg，Mcal/kg，)。K R 飼養(yǎng)標(biāo)準(zhǔn)規(guī)定的有關(guān)比例參數(shù)； r 為飼草和非精飼料原料的種類數(shù)。下標(biāo)：下，上，等，相當(dāng)于下限、上限、等值；g，h，則為下標(biāo)j的特定值。有關(guān)線性規(guī)劃的數(shù)學(xué)原理和計(jì)算方法，各種書刊均有詳盡講述，此處不贅。下面僅就在Excel平臺(tái)上對(duì)奶牛日糧和飼料配方的規(guī)劃求解，按計(jì)算機(jī)操作及屏幕顯示，分步加以介紹。1. 構(gòu)建標(biāo)準(zhǔn)生成工作表1.1 在標(biāo)準(zhǔn)生成工作表左上方，根據(jù)VBA模塊要求，左側(cè)一列設(shè)置奶牛各項(xiàng)參數(shù)。相鄰列為參數(shù)輸入域，按照配方對(duì)象奶牛條件逐一鍵入?yún)?shù)值。右側(cè)二列是參數(shù)單位和范圍的規(guī)

5、定提示。圖1 標(biāo)準(zhǔn)生成工作表1.2 表下方有一個(gè)“確定”橢圓命令按鈕，當(dāng)參數(shù)鍵入檢查無(wú)誤后，鼠標(biāo)左擊進(jìn)入VBA程序計(jì)算，生成的標(biāo)準(zhǔn)自動(dòng)填入G列相應(yīng)單元格中，同時(shí)也填入配方規(guī)劃工作表的27行對(duì)應(yīng)位置中。1.3 生成標(biāo)準(zhǔn)列的右側(cè)是配方規(guī)劃結(jié)果反饋，列出供直觀對(duì)比檢查。1.4 奶牛營(yíng)養(yǎng)需要數(shù)學(xué)模型和VBA程序模塊，這里就不一一介紹了。2. 構(gòu)建配方規(guī)劃工作表2.1 激活配方規(guī)劃工作表，在（A）列輸入飼料名，（B）列放變量名, （C）列的C4:C23為可變單元格，留作存放配方解（Xi），D列存放飼料原料的單價(jià)。余下（E-K）各列存放飼料營(yíng)養(yǎng)指標(biāo)數(shù)據(jù)。L列為計(jì)算粗料占日糧比例用（DM）。2.2 工作表的

6、第2行輸入對(duì)應(yīng)各列的題頭和營(yíng)養(yǎng)指標(biāo)英文縮寫名，分別是：Price = 單價(jià)，DM = 干物質(zhì)，NEL = 產(chǎn)奶凈能，CP = 粗蛋白，Ca = 鈣，P = 磷，NaCl = 食鹽，Premix = 預(yù)混料。第3行放其對(duì)應(yīng)的單位。為了后續(xù)工作方便和個(gè)人習(xí)慣，單位可以變換：如換成g/kg，同時(shí)數(shù)據(jù)小數(shù)點(diǎn)相應(yīng)移位。第4行起到第23行，（D）列輸入各飼料原料的單價(jià)；L列用于存放日糧粗/精比例；（F-K）列則對(duì)應(yīng)為各營(yíng)養(yǎng)指標(biāo)含量。圖2配方規(guī)劃工作表2.3 D26即目標(biāo)單元格，備放日糧的最低成本，由（C）列配方解與（D）列對(duì)應(yīng)單元格的飼料原料價(jià)格相乘，累加，并進(jìn)行單位換算而得, D26 =SUMPRODU

7、CT($C$4:$C$23,D4:D23)。2.4 F26-L26各單元格存放日糧的營(yíng)養(yǎng)指標(biāo)值(bi)，可選定D26用鼠標(biāo)符左鍵拖過(guò)各單元格方式拷貝公式，再分別因飼料與日糧的同名指標(biāo)單位不同而校正換算生成。例如： F26 = SUMPRODUCT($C$4:$C$23,F4:F23)/100; G26 = SUMPRODUCT($C$4:$C$23,G4:G23)*10。余類推。2.5 第27行存放配方對(duì)象奶牛的飼養(yǎng)標(biāo)準(zhǔn)，其格式與飼料營(yíng)養(yǎng)指標(biāo)格式對(duì)應(yīng)一致。2.6 第29-31行設(shè)置針對(duì)各項(xiàng)營(yíng)養(yǎng)指標(biāo)的約束值，約束條件有三種：大于等于（>=）、等于（=）、小于等于（<=），各行各項(xiàng)指標(biāo)

8、的約束下限、約束等值、約束上限，分別按對(duì)象動(dòng)物對(duì)營(yíng)養(yǎng)指標(biāo)的要求來(lái)確定，供約束操作時(shí)直觀選定。2.7 （M），（N）,（O）,各列與第29-31各行對(duì)應(yīng)類似，設(shè)置日糧配方解（Xi）的約束條件限量值。2.8 （L）列存放飼草DM占全日糧DM比例的有關(guān)計(jì)算結(jié)果。飼草DM單元格L8 =SUMPRODUCT($C$4:$C$10,D4:D10)/100；日糧DM單元格F20 =SUMPRODUCT($C$4:$C$23,D4:D23)/100；飼草DM/日糧DM單元格E26 = L8/L20*100。圖3 飼料配方的原料成本分析2.9 V、W、Q列用于按“歸一”方法延伸計(jì)算精料補(bǔ)充料配方。第R、S、X、

9、Y，列對(duì)日糧配方作出原料成本分?jǐn)偳闆r分析。（圖3）3. 規(guī)劃求解的具體操作過(guò)程：3.1 調(diào)用 加栽宏 的 規(guī)劃求解 ： 鼠標(biāo)點(diǎn)選工具 菜單中的 加栽宏 ，在對(duì)話框中選 規(guī)劃求解 ，確定（圖 4）。已調(diào)用過(guò)規(guī)劃求解時(shí)，可直接由工具菜單點(diǎn)選規(guī)劃求解。圖4工具菜單,加載宏對(duì)話框和規(guī)劃求解選定3.2 工具 菜單中激活規(guī)劃求解 ：彈出規(guī)劃求解參數(shù)對(duì)話框（圖 5-1），鼠標(biāo)單擊 D26單元格，選定目標(biāo)單元格，顯示為加 $ 的單元格的絕對(duì)地址$D$26；選定 等于行內(nèi)的 最小值 ；鼠標(biāo)單擊 選項(xiàng) 按鈕，彈出 規(guī)劃求解選項(xiàng) 對(duì)話框（圖 5-2），只選定假定非負(fù)一項(xiàng)，其余為默認(rèn)， 確定 返回規(guī)劃求解參數(shù)對(duì)話框。

10、圖 5 規(guī)劃求解參數(shù)及規(guī)劃求解選項(xiàng)對(duì)話框3.3 在 可變單元格框內(nèi)，用鼠標(biāo)左鍵拉過(guò)C4-C23填入,確定結(jié)構(gòu)變量的輸出單元格，即配方結(jié)果的輸出位置，顯示出$C$4:$C$23 。3.4 鼠標(biāo)單擊約束欄的 添加按鈕，彈出添加約束對(duì)話框（圖 6），各項(xiàng)營(yíng)養(yǎng)指標(biāo)和各種飼料原料的限量約束，因營(yíng)養(yǎng)要求和原料性質(zhì)以及庫(kù)存數(shù)量而定，可按預(yù)定的約束條件用鼠標(biāo)點(diǎn)擊設(shè)定。本例的約束設(shè)置有： C11:C14 >= M11:M14 ; C19 >= M19 ; C4:C10 >= M4:M10 ; E26 <= E31 ; E26:J26 >= E27:J27 ; L26 <= L

11、31 圖 6 添加約束對(duì)話框 圖 7 規(guī)劃求解結(jié)果對(duì)話框3.5 約束設(shè)定完畢，返回規(guī)劃求解選項(xiàng)對(duì)話框，再用鼠標(biāo)左鍵單擊 求解，則出現(xiàn)對(duì)話框（圖 7），顯示規(guī)劃求解找到一解，可滿足所有的約束及最優(yōu)狀況 ，（圖 7）單擊確定 ，配方結(jié)果即可顯示在配方工作表上，同時(shí)也反饋到標(biāo)準(zhǔn)生成工作表中。如鼠標(biāo)選取對(duì)話框中的三份報(bào)告，則可同時(shí)生成運(yùn)算結(jié)果、敏感性、和極限值三份報(bào)告。如果顯示規(guī)劃求解找不到有用的解 ，則應(yīng)單擊 取消 ，重新檢查并修改約束條件的設(shè)定。圖8 配方的原料分?jǐn)偝杀痉治?.6 在日糧配方基礎(chǔ)上可進(jìn)一步延伸計(jì)算飼料成本的分?jǐn)偳闆r（圖 8），供經(jīng)營(yíng)管理人員分析參考。3.7 如要求配方以書面文字存檔

12、時(shí)，則每完成一個(gè)配方，要將結(jié)果拷貝到另一打印輸出工作表內(nèi)，以便打印成文存檔?？截悤r(shí)具體注意是：配方解各單元格有的存放公式，應(yīng)在編輯或右鍵菜單中選中選擇性粘貼 再選定 數(shù)值 。而配方營(yíng)養(yǎng)指標(biāo)值單元格（D26:L26）存放的全是公式，又是橫向排列，所以選擇性粘貼時(shí)還要加選 轉(zhuǎn)置 （圖 9）。圖9 選擇性粘貼對(duì)話框圖10 配方的延伸計(jì)算：濃縮料和預(yù)混料3.7 同時(shí)擬制精料補(bǔ)充料配方，則可以很方便地按“歸一”的方式計(jì)算出配方來(lái)。同理，可推及除去玉米組分的濃縮料和預(yù)混料配方的擬制（圖 10）。圖 11備用飼料數(shù)據(jù)庫(kù)及備用飼養(yǎng)標(biāo)準(zhǔn)庫(kù)示例4. 幾點(diǎn)注意4.1 可在本工作表下方建立 “飼料數(shù)據(jù)備用庫(kù)”和“飼養(yǎng)標(biāo)準(zhǔn)備用庫(kù)”，便于調(diào)換飼料原料和飼養(yǎng)標(biāo)準(zhǔn)，進(jìn)行另外的配方擬制工作（圖 11）；4.2奶牛飼養(yǎng)標(biāo)準(zhǔn)不必再查表計(jì)算，可由自帶的按數(shù)學(xué)模型編就的VBA模塊自動(dòng)計(jì)算產(chǎn)生。4.3 一般數(shù)據(jù)取小數(shù)點(diǎn)后2位，即夠用，多取則會(huì)增大列寬，一屏顯示受限，不便觀察和操作。4.4 計(jì)算和拷貝過(guò)程，引用絕對(duì)地址單元格時(shí)，行、列前都要加 $ ，可選中后按F4 鍵切換選取。4.5 單元格 C26 = SUM($C$4:$C$23),是日糧配方解的自然重量總和，不必設(shè)置約束。4.6 配方規(guī)劃結(jié)果生成的三份報(bào)告，有必要時(shí)可察看靈敏性報(bào)告中有關(guān)內(nèi)容作為參考。參