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

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

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

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

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

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

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

8、的約束下限、約束等值、約束上限，分別按對象動物對營養指標的要求來確定，供約束操作時直觀選定。2.7 （M），（N）,（O）,各列與第29-31各行對應類似，設置日糧配方解（Xi）的約束條件限量值。2.8 （L）列存放飼草DM占全日糧DM比例的有關計算結果。飼草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列用于按“歸一”方法延伸計算精料補充料配方。第R、S、X、

9、Y，列對日糧配方作出原料成本分攤情況分析。（圖3）3. 規劃求解的具體操作過程：3.1 調用 加栽宏 的 規劃求解 ： 鼠標點選工具 菜單中的 加栽宏 ，在對話框中選 規劃求解 ，確定（圖 4）。已調用過規劃求解時，可直接由工具菜單點選規劃求解。圖4工具菜單,加載宏對話框和規劃求解選定3.2 工具 菜單中激活規劃求解 ：彈出規劃求解參數對話框（圖 5-1），鼠標單擊 D26單元格，選定目標單元格，顯示為加 $ 的單元格的絕對地址$D$26；選定 等于行內的 最小值 ；鼠標單擊 選項 按鈕，彈出 規劃求解選項 對話框（圖 5-2），只選定假定非負一項，其余為默認， 確定 返回規劃求解參數對話框。

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

11、31 圖 6 添加約束對話框 圖 7 規劃求解結果對話框3.5 約束設定完畢，返回規劃求解選項對話框，再用鼠標左鍵單擊 求解，則出現對話框（圖 7），顯示規劃求解找到一解，可滿足所有的約束及最優狀況 ，（圖 7）單擊確定 ，配方結果即可顯示在配方工作表上，同時也反饋到標準生成工作表中。如鼠標選取對話框中的三份報告，則可同時生成運算結果、敏感性、和極限值三份報告。如果顯示規劃求解找不到有用的解 ，則應單擊 取消 ，重新檢查并修改約束條件的設定。圖8 配方的原料分攤成本分析3.6 在日糧配方基礎上可進一步延伸計算飼料成本的分攤情況（圖 8），供經營管理人員分析參考。3.7 如要求配方以書面文字存檔

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