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

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

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

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

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

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

線性規(guī)劃可以合理配置資源，因而廣泛使用在飼料配方計(jì)算領(lǐng)域，它可以在多個(gè)約束條件下給出最低成本的配方。

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

根據(jù)影響所要達(dá)到目的的因素找到?jīng)Q策變量；由決策變量和所要達(dá)到目的之間的函數(shù)關(guān)系確定目標(biāo)函數(shù)；由決策變量所受的限制條件確定決策變量所要滿足的約束條件；根據(jù)上述決策變量、目標(biāo)函數(shù)和約束條件建立線性規(guī)劃數(shù)學(xué)模型；利用Excel進(jìn)行規(guī)劃求解，得出最優(yōu)配方。

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

2.1 調(diào)出Excel的規(guī)劃求解模塊 打開Excel，打開文件菜單，點(diǎn)擊“選項(xiàng)”，點(diǎn)擊“加載項(xiàng)”，選定“規(guī)劃求解加載項(xiàng)”，然后點(diǎn)擊“轉(zhuǎn)到”，就會(huì)彈出選項(xiàng)卡（圖1），在“ 規(guī)劃求解加載項(xiàng)”，點(diǎn)擊“ 確定”，然后在“ 數(shù)據(jù)”中會(huì)出現(xiàn)“ 分析”“ 規(guī)劃求解”（ 圖 2）。 至此，就已調(diào)出規(guī)劃求解模塊。

圖1

圖2

2.2 建立配方模型 在肉牛日糧配方中，決策變量為各個(gè)營(yíng)養(yǎng)素；目標(biāo)函數(shù)為成本函數(shù)；約束條件為飼養(yǎng)標(biāo)準(zhǔn)和各個(gè)原料使用的上下限。具體操作如下：

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

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

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

2.3規(guī)劃求解 點(diǎn)擊菜單“數(shù)據(jù)”——“規(guī)劃求解”，彈出“規(guī)劃求解”選項(xiàng)框（圖4）。然后在選項(xiàng)框設(shè)置求解條件。進(jìn)行如下操作：

圖3

圖4

圖5

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

2.3.2 添加約束條件 日糧配方的約束條件主要是原料限量和飼養(yǎng)標(biāo)準(zhǔn)上下限，以及其他原料之間的相關(guān)性。肉牛日糧原料之間的相關(guān)性主要考察粗精比例，我們把粗精比例設(shè)置為大于“60∶40”即1.5。分別將原料上下限、飼養(yǎng)標(biāo)準(zhǔn)上下限加入約束條件。因?yàn)轱暳吓浞綖檎龜?shù)，所以要勾上“使無約束變量為非負(fù)數(shù)”。

2.2.3 選擇求解方法 其方法有多種，選擇“非線性GRG”，選項(xiàng)中一律默認(rèn)。設(shè)置完畢如圖5所示。點(diǎn)擊“求解”，即可得到最優(yōu)解，如圖6所示。生成的配方在日糧列。

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

圖6