如何利用Excel制作最低成本的肉牛日糧配方

易禮勝，甘 佳，唐 慧，王 巍，季 楊，梁小玉

（四川省畜牧科學研究院，四川 成都 610066）

Excel是微軟Office套件的組成部分，應用十分普遍，利用其規劃求解模塊可以制作最低成本的肉牛日糧配方，完全可以替代專業配方軟件。現在普遍安裝的 Office版本主要是Office2007、Office2010和Office2013，其原理和操作方法都差不多，本文以Office2010為例，介紹利用Excel2010制作最低成本肉牛日糧配方的技術。

1 用Excel制作最低成本日糧配方的原理

Excel內置有規劃求解模塊，可以進行線性規劃求解。線性規劃是輔助人們進行科學管理的一種數學方法，它所研究的正是在一定條件下，合理安排人力物力等資源，使經濟效果達到最好。一般說來，求線性目標函數在線性約束條件下的最大值或最小值問題，統稱為線性規劃問題。滿足線性約束條件的解叫作可行解，由所有可行解組成的集合叫作可行域。線性規劃問題數學模型的一般形式：列出約束條件及目標函數；畫出約束條件所表示的可行域；在可行域內求目標函數的最優解。

線性規劃可以合理配置資源，因而廣泛使用在飼料配方計算領域，它可以在多個約束條件下給出最低成本的配方。

2 用Excel配制最低成本日糧配方的步驟

根據影響所要達到目的的因素找到決策變量；由決策變量和所要達到目的之間的函數關系確定目標函數；由決策變量所受的限制條件確定決策變量所要滿足的約束條件；根據上述決策變量、目標函數和約束條件建立線性規劃數學模型；利用Excel進行規劃求解，得出最優配方。

肉牛日糧配方的決策變量是什么？如何確定目標函數？其約束條件是什么？下面我們通過一個簡單的配方來演示利用Excel配制最低成本肉牛日糧配方的過程。

2.1 調出Excel的規劃求解模塊 打開Excel，打開文件菜單，點擊“選項”，點擊“加載項”，選定“規劃求解加載項”，然后點擊“轉到”，就會彈出選項卡（圖1），在“ 規劃求解加載項”，點擊“ 確定”，然后在“ 數據”中會出現“ 分析”“ 規劃求解”（ 圖 2）。 至此，就已調出規劃求解模塊。

圖1

圖2

2.2 建立配方模型 在肉牛日糧配方中，決策變量為各個營養素；目標函數為成本函數；約束條件為飼養標準和各個原料使用的上下限。具體操作如下：

2.2.1 新建一個Excel文檔，將工作表改名為“肉牛配方”（如圖3所示）。按圖3做好表頭，然后查閱肉牛飼養標準，我們選取一個體重350kg、日增重目標為1.2kg的飼養標準，把相應數據填到第二行，在第三行輸入需要控制的營養上限，第四行暫時空白，待錄入原料數據后，輸入公式。從第五行開始，分別錄入原料數據，直至所有待選原料錄入完畢。

2.2.2 在上限、下限及價格列錄入相應數據，在日糧列一律錄入任意初始值，我們錄入“1”。在合計行日糧列錄入公式“ =SUM（ D5∶D15）”，行數要囊括所有原料行；在價格列錄入公式“=SUMPRODUCT（$D5∶$D15，E5∶E15）”，行數同上，然后拖住該單元格，向右填充，計算出所有營養素合計值。注意，干物質列如果不是百分數，則需要在合計公式中除以100。

2.2.3 如果要進一步控制精粗比例，可以進行如下操作：在任意空白格輸入“精料”，“粗飼料”直接錄入在表格下端，然后在其旁邊錄入公式“=SUMPRODUCT（ D10∶D15，F10∶F15）/100”“ =SUMPRODUCT（ D5∶D9，F5∶F9）/100”， 其行數涵蓋相應的精料和粗料行。在“粗精比例”處錄入公式“=E17/E16”，然后在其右邊單元格錄入需要控制的值。在“營養上限”行對應的單元格錄入營養上限需要控制的數據。表格建立好之后如圖3所示。

2.3規劃求解 點擊菜單“數據”——“規劃求解”，彈出“規劃求解”選項框（圖4）。然后在選項框設置求解條件。進行如下操作：

圖3

圖4

圖5

2.3.1 設置目標 我們以最低成本為目標，所以，目標單元格為“E4”，選擇“最小值”按鈕。設置可變單元格，我們需要的結果為配方，因此，可變單元格為“$D$5∶$D$15”。

2.3.2 添加約束條件 日糧配方的約束條件主要是原料限量和飼養標準上下限，以及其他原料之間的相關性。肉牛日糧原料之間的相關性主要考察粗精比例，我們把粗精比例設置為大于“60∶40”即1.5。分別將原料上下限、飼養標準上下限加入約束條件。因為飼料配方為正數，所以要勾上“使無約束變量為非負數”。

2.2.3 選擇求解方法 其方法有多種，選擇“非線性GRG”，選項中一律默認。設置完畢如圖5所示。點擊“求解”，即可得到最優解，如圖6所示。生成的配方在日糧列。

2.4 調整整理配方 初次計算出來的配方，不一定符合要求，有時可能無解，所以要根據實際情況調整原料上下限、營養素上下限及相關關系，最后通過整理小數等措施，得出我們需要的日糧配方。

圖6