利用Excel線性規(guī)劃設(shè)計(jì)飼料配方

kun421186424

摘 要：借助Excel“線性規(guī)劃求解”功能，結(jié)合飼料配方設(shè)計(jì)要求，以線性規(guī)劃求解建立數(shù)學(xué)模型，根據(jù)動(dòng)物的飼養(yǎng)標(biāo)準(zhǔn)，能快速、簡單地求解飼料配方，在最低成本目標(biāo)下實(shí)現(xiàn)對原料和營養(yǎng)成分的有效調(diào)控。
    關(guān)鍵詞：Excel；線性規(guī)劃；飼料配方
    所謂飼料配方設(shè)計(jì)，就是應(yīng)用一定的計(jì)算方法，根據(jù)原料的營養(yǎng)成分和配方的規(guī)格、要求，產(chǎn)生配方中各原料比例的一種運(yùn)算過程。線性規(guī)劃法是目前應(yīng)用最廣泛的一種優(yōu)化飼料配方技術(shù)。線性規(guī)劃最低成本配方的優(yōu)化結(jié)果是產(chǎn)生一個(gè)滿足約束條件的最低成本配方，它受原料的營養(yǎng)成分、約束條件值(配方營養(yǎng)素水平)、原料價(jià)格等的影響。目前，大型專業(yè)飼料公司己使用計(jì)算機(jī)和專用的飼料配方軟件進(jìn)行配方。但專用的飼料配方軟件價(jià)格不菲，因此，在小型飼料廠和一般養(yǎng)殖場的應(yīng)用并不普及。現(xiàn)在有可能只要擁有安裝了Excel的電腦就可以通過計(jì)算機(jī)運(yùn)用“線性規(guī)劃求解”求解飼料配方。利用它的行和列的形式組成區(qū)域，在區(qū)域內(nèi)記錄數(shù)據(jù)信息，組成數(shù)據(jù)庫，且可對數(shù)據(jù)進(jìn)行統(tǒng)計(jì)和分析，獲取有效信息。同時(shí)，運(yùn)用線性規(guī)劃實(shí)現(xiàn)日糧配方，方法簡單、快捷、準(zhǔn)確、方便。本文就利用Excel線性規(guī)劃設(shè)計(jì)飼料配方作一闡述。
    1. 引言
1987年,John  Barwell 在美國伊利斯諾州沃基根市創(chuàng)建了Blatchford's全球第家飼料廠，生產(chǎn)犢牛飼料，它的建立標(biāo)志世界飼料工業(yè)的開始。到20世紀(jì)20年代，飼料配方設(shè)計(jì)方法有：對角線法、聯(lián)立方程法、試差法等等手工方法。1964年隨著電腦的流行。為了節(jié)約飼料生產(chǎn)成本，提高配方設(shè)計(jì)的效率與準(zhǔn)確性，很多飼料廠都已放棄手工配方設(shè)計(jì)，開始采用電腦設(shè)計(jì)飼料配方。電腦配方具有巨大的優(yōu)勢，它能全面考慮營養(yǎng)、成本和效益，控制飼料適口性，還可提供大量的參考信息，最重要的是，它節(jié)約了大量的人力和物力，很大程度上解放了配方師。如今著名的飼料配方軟件有國外的Format、Brill、Mixit和國產(chǎn)的Refs、CMIX等。但這些專業(yè)配方軟件價(jià)格都相對較高，只適合大型飼料企業(yè)，對于中國眾多的中小型的飼料廠及一些規(guī)模養(yǎng)殖場不太適用，只能采用Microsoft Excel的“規(guī)劃求解”功能設(shè)計(jì)其配方。
   2. 前期準(zhǔn)備
在進(jìn)行配方設(shè)計(jì)之前我們首先應(yīng)該做以下一些準(zhǔn)備：
       1）確保計(jì)算機(jī)安裝有EXCEL以及“規(guī)劃求解”宏：選擇“工具”菜單“加載宏”選項(xiàng)，進(jìn)入“加載宏”對話框確認(rèn)是否安裝有“規(guī)劃求解”。
       2）準(zhǔn)確的飼養(yǎng)標(biāo)準(zhǔn)：根據(jù)當(dāng)?shù)氐那闆r選擇適當(dāng)?shù)娘曫B(yǎng)標(biāo)準(zhǔn)，并適當(dāng)?shù)膭澏ㄒ欢ǖ母?dòng)范圍（上限和下限）。
       3）飼料成分及營養(yǎng)價(jià)值表
       4）根據(jù)本地的情況確定飼料原料及價(jià)格：避免人畜爭糧，在北方不易選用小麥做飼料原料。
    3. 飼料配方設(shè)計(jì)要求
    3.1優(yōu)化配方設(shè)計(jì)目標(biāo)
    動(dòng)物生產(chǎn)的經(jīng)濟(jì)效益主要取決于飼料。飼料品質(zhì)的好壞，成本的高低直接影響動(dòng)物生產(chǎn)的經(jīng)濟(jì)效益。而飼料配方是配合飼料生產(chǎn)的核心，要優(yōu)化配方設(shè)計(jì)，必須同時(shí)解決以下三個(gè)問題：⑴營養(yǎng)需要問題，由營養(yǎng)學(xué)家研究修改制定，滿足營養(yǎng)標(biāo)準(zhǔn)就是線性規(guī)劃求解的主要約束條件之一，即營養(yǎng)性限制；⑵合理組合原料，不同原料的合理搭配，才能滿足動(dòng)物的營養(yǎng)需要，是約束條件之二，即原料性限制；⑶價(jià)格最低，在符合條件⑴、⑵的基礎(chǔ)上，采用成本最低的原料配比就是求解的目標(biāo)，即最低成本目標(biāo)函數(shù)。
    3.2數(shù)學(xué)模型
    優(yōu)選最佳飼料配方的數(shù)學(xué)原理是線性規(guī)劃法，就是求某一目標(biāo)函數(shù)在一定的約束條件下的最大值或最小值。約束條件和目標(biāo)函數(shù)均可用線性方程組或線性不等式表示。線性規(guī)劃最低成本配方優(yōu)化的數(shù)學(xué)模型可表示為：
    目標(biāo)函數(shù)：Min S=C1X1+C2X2+∧+CnXn(求最小值)
    約束條件：a11x1+a12x2+∧+a1nxn≥b1（或=，≤b1）
              a21x1+a22x2+∧+a2nxn≥b2（或=，≤b2）
                ┇ ┇ ┇ ┇
              am1x1+am2x2+∧+amnxn≥bm（或=，≤bm）
              xj≥0（j=1，2， …，n）
    其中： x1，x2，∧，xn為決策變量，即各種原料在配方中的數(shù)量；
    aij（i=1，2，∧，m；j=1，2，∧，n）為技術(shù)系數(shù)，即各種原料相應(yīng)的營養(yǎng)成分；
    b1，b2，∧，bn為約束值，即配方中應(yīng)滿足的各項(xiàng)營養(yǎng)指標(biāo)或重量指標(biāo)；
    C1，C2，∧，Cn為成本系數(shù)，即每種原料的價(jià)格系數(shù)；
    m為約束條件個(gè)數(shù)；
    為配方原料個(gè)數(shù)。
    3.3飼料配方
    用玉米、麩皮、豆粕、菜籽粕、進(jìn)口魚粉、磷酸氫鈣、石粉、食鹽、賴氨酸、蛋氨酸和預(yù)混料11種原料，以產(chǎn)蛋率<65%的蛋雞為例，各種營養(yǎng)需要為約束條件，計(jì)算優(yōu)化飼料配方。
    查出產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標(biāo)準(zhǔn)(見表1)與中國飼料成分及營養(yǎng)價(jià)值表2002年(第13版)(見表2)。同時(shí)還需要查出當(dāng)?shù)赝诟鞣N飼料原料的市場價(jià)格(見表3)，并對有些飼料原料的用量作出相應(yīng)的限定(見表4)。按圖1的樣式對應(yīng)將表1、2、3、4中的內(nèi)容填入Excel 2000單元格內(nèi)。
表1 產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標(biāo)準(zhǔn)
          代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%) 食鹽(%)
飼養(yǎng)標(biāo)準(zhǔn)     11.50         14.0    3.2     0.30     0.62       0.31     0.37

表2 各種飼料原料營養(yǎng)價(jià)值表
飼料成分 代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%)
玉米         13.47       7.80    0.02    0.10      0.23      0.15
麩皮          6.82      15.70    0.11    0.30      0.58      0.13
豆粕          9.62      43.00    0.32    0.20      2.45      0.64
菜籽粕        7.41      38.60    0.65    0.33      1.30      0.63
進(jìn)口魚粉     12.18      62.50    3.96    3.05      5.12      1.66
磷酸氫鈣      0.00       0.00   21.00   16.00      0.00      0.00
石粉          0.00       0.00   35.00    0.00      0.00      0.00
油脂         36.82       0.00    0.00    0.00      0.00      0.00
賴氨酸        0.00       0.00    0.00    0.00     78.80      0.00
蛋氨酸        0.00       0.00    0.00    0.00      0.00     98.00

表3 各種飼料原料的市場價(jià)格
原料        玉米 麩皮 豆粕 菜籽粕 進(jìn)口魚粉 磷酸氫鈣  石粉  食鹽  油脂 賴氨酸 蛋氨酸 預(yù)混料
價(jià)格(元/kg) 1.22 1.08 2.30  1.20    5.37     1.55    0.12  1.24  8.00 17.50   31.50  5.50

表4 幾種飼料原料的約束條件
        麩皮 菜籽粕 進(jìn)口魚粉 油脂  食鹽 預(yù)混料
下限(%)  0     0       2      2    0.3    2
上限(%) 10     7       5      -    0.3    2

圖1 求解飼料配方所用線性模型在表格中的排列樣式

    圖1中B3至M16是aij值；N3至N16是對應(yīng)于初始值時(shí)，線性方程組左側(cè)的值；O3至O16是bi值；B17至M17是Cj值；B18至M18是給定的初始值(初始值均給定為1)；N17是給定初始值時(shí)的目標(biāo)函數(shù)值(S)，xj在表格中不占用任何單元格。N3至N17格中的公式是同行左側(cè)B列至M列與B18至M18對應(yīng)格乘積之和。如在N3格中輸入“=B3*$B$18+ C3*$C$18+ D3*$D$18+ E3*$E$18+F3*$F$18+G3*$G$18+H3*$H$18+I3*$I$18+J3*$J$18+K3*$K$18+L3*$L$18+M3*$M$18”，然后把光標(biāo)放在N3格右下角位置上，等光標(biāo)變成“+”時(shí)往下拖動(dòng)至N17上，這樣從N3至N17格都輸入了相應(yīng)的公式。

    4. 線性規(guī)劃求解
    往Excel電子表格上輸入完數(shù)據(jù)及公式以后，就可以利用其工具欄中“規(guī)劃求解”功能開始求解，步驟如下：
    4.1選中“工具”菜單，選中“規(guī)劃求解”命令，出現(xiàn)“規(guī)劃求解參數(shù)”對話框(如圖2)。
    4.2在[設(shè)置目標(biāo)單元格]編輯框中，輸入目標(biāo)單元格的名稱$N$17，然后點(diǎn)擊[最小值]選項(xiàng)。
    4.3在[可變單元格]中，輸入$B$18:$M$18。

圖2 “規(guī)劃求解”對話框

    4.4在[約束]窗口中單擊添加按鈕，產(chǎn)生“添加約束”對話框(如圖3)，在[單元格引用位置]和[約束值]輸入欄中輸入約束條件。如本例在[單元格引用位置]上先輸入$N$3:$N$8；然后選擇下拉窗口中的“>=”；接著在[約束值]上輸入$O$3:$O$8，點(diǎn)擊添加按鈕，繼續(xù)添加約束條件：$N$9:$N$10<=$O$9:$N$10；$N$11>=$O$11；$N$12<=$O$12；$N$13>=$O$13；$N$14:$N$15=$O$14:$O$15。輸完約束條件以后關(guān)閉窗口，回到“規(guī)劃求解參數(shù)”窗口。

圖3 “添加約束”對話框

    4.5點(diǎn)擊選項(xiàng)按鈕，進(jìn)入“規(guī)劃求解選項(xiàng)”對話框(如圖4)，選中“采用線形模型”、“假定非負(fù)”、“正切函數(shù)”、“向前差分”、“牛頓法”，然后點(diǎn)擊確定按鈕，回到“規(guī)劃求解參數(shù)”對話框。
    4.6單擊求解按鈕，開始計(jì)算，進(jìn)入“規(guī)劃求解結(jié)果”對話框(如圖5)，在該對話框中可以作以下的選擇：
    4.6.1保存規(guī)劃求解的結(jié)果；
    4.6.2恢復(fù)為原值。
    在本例中選擇默認(rèn)的選項(xiàng)“保存規(guī)劃求解的結(jié)果”，在“報(bào)告”中根據(jù)需要選定計(jì)算結(jié)果報(bào)告(運(yùn)算結(jié)果報(bào)告、敏感性報(bào)告、極限值報(bào)告)。需要說明的是：線性規(guī)劃的求解結(jié)果要么有最優(yōu)解，要么給出參考配方(無最優(yōu)解時(shí))。所謂最優(yōu)解就是滿足所有約束條件(包括營養(yǎng)指標(biāo)和配比限制條件)的最低成本配方。參考配方是指最優(yōu)解不存在時(shí)，仍然存在一個(gè)最接近理想的配方，它的成本是最低，但是所有的約束條件沒有同時(shí)滿足，但該參考配方仍然具有一定的參考價(jià)值，因?yàn)樵摻Y(jié)果往往是可以應(yīng)用的。

圖4 “規(guī)劃求解選項(xiàng)”對話框

圖5 “規(guī)劃求解結(jié)果”對話框

    4.6.7打印規(guī)劃求解結(jié)果
    通過以上步驟，就可求出一個(gè)蛋雞料的最優(yōu)配方(不一定飼養(yǎng)效果最佳)。可見，利用Excel的“規(guī)劃求解”可以篩選出最優(yōu)的配方，這樣給動(dòng)物營養(yǎng)設(shè)計(jì)師們的工作帶來極大的方便，既提高了工作效率，又提高了配方質(zhì)量。

    5. 運(yùn)行結(jié)果分析
    5.1運(yùn)算結(jié)果報(bào)告
    在此報(bào)告中，我們可以看到求得的飼料配方成本、最優(yōu)配方以及約束條件滿足狀況，其中“型數(shù)值”指求解值與限定值之間的差距，在無法求得最優(yōu)解時(shí)，我們據(jù)此可以適當(dāng)調(diào)節(jié)約束條件。通過線性規(guī)劃求解，從運(yùn)算結(jié)果報(bào)告中可知，本例配合飼料價(jià)格為1.58元/kg，飼料配方見表5。
表5 蛋雞料飼料配方表
        玉米 麩皮 豆粕 菜籽粕 魚粉 磷酸氫鈣 石粉 油脂 食鹽 賴氨酸 蛋氨酸 預(yù)混料
配方(%) 66.1 0.0  11.4   7.0   2.0   0.8     8.2  2.0  0.3   0.0    0.1    2.0
    5.2敏感性報(bào)告
    從該報(bào)告中我們可以看到以下內(nèi)容：
    遞減成本，表示各個(gè)決策變量(飼料原料的添加量)的影子價(jià)格，它說明在飼料總量條件不變的情況下，某一種飼料原料用量在最優(yōu)解的基礎(chǔ)上增加1個(gè)單位時(shí)，目標(biāo)成本增加的量。
    目標(biāo)式系數(shù)及其允許的增量和減量，指在最優(yōu)解保持不變的情況下，目標(biāo)式系數(shù)(飼料原料價(jià)格)的變化范圍，這反映了所獲得配方對原料市場價(jià)格變化的適應(yīng)能力。如果原料價(jià)格變化在允許范圍內(nèi)，則不必更改配方。
    陰影價(jià)格，指約束條件的影子價(jià)格，表示在所獲得最優(yōu)解的基礎(chǔ)上，當(dāng)約束條件每增減1個(gè)單位時(shí)，所引起目標(biāo)函數(shù)值(飼料最終價(jià)格)的增減量。這可為進(jìn)一步調(diào)整約束條件提供參考。
    約束限制值允許的增量和減量，指在保持最優(yōu)解和其它條件不變的情況下，各個(gè)約束限制值的可變化范圍，也就是指在此變化范圍內(nèi)表中所列出的約束條件的影子價(jià)格才能夠成立。
    5.3權(quán)限值報(bào)告
    該報(bào)告列出了最終的飼料價(jià)格，以及保證該價(jià)格成立的飼料原料添加量的上下極限。

    6. 討論
    6.1在進(jìn)行規(guī)劃求解時(shí)，首先要確認(rèn)在“工具”菜單中出現(xiàn)“規(guī)劃求解”命令，如果沒有則需要安裝“規(guī)劃求解”加載宏，單擊“工具”菜單中，單擊加載宏命令，如果在“加載宏”命令中，沒有列出“規(guī)劃求解”項(xiàng)，單擊瀏覽，確定驅(qū)動(dòng)器、目錄、文件名或運(yùn)行加載程序，然后在“加載宏”對話框中，選定“規(guī)劃求解”復(fù)選框。Excel在“規(guī)劃求解”中使用的算法是由Leon lasdon，(University of Texas at Austin)和Allan Waren(Cleveland State University)改進(jìn)的通用非線性規(guī)劃最佳化代碼。當(dāng)“規(guī)劃求解選項(xiàng)”對話框中的“采用線性模型”復(fù)選框被選定時(shí)，改變可變單元格的初始值不會影響最終數(shù)值和求解時(shí)間。在“規(guī)劃求解”中可以指定500個(gè)約束條件，對每一個(gè)可變單元格來說，分別有一個(gè)上、下限，還可另加100個(gè)附加約束條件，應(yīng)用約束條件的單元格不應(yīng)多于1000個(gè)。
    6.2若規(guī)劃求解過程中出現(xiàn)無解時(shí)，說明所使用的原料要達(dá)到約束條件是不可能的，此時(shí)要注意分析可能存在的原因：
    6.2.1各營養(yǎng)指標(biāo)的約束條件是否有沖突，如有些限定了蛋白質(zhì)水平低，而限定的蛋氨酸水平又高，自相沖突；
    6.2.2原料種類用量的限定與營養(yǎng)指標(biāo)值之間有沖突。如粗纖維限定值較小，而糠餅麥麩原料限定用量卻較高；
    6.2.3同一原料的上下約束范圍過小，將限制營養(yǎng)指標(biāo)值的達(dá)到；
    6.2.4有時(shí)可能由于“規(guī)劃求解參數(shù)”選項(xiàng)中的“最長運(yùn)算時(shí)間”、“迭代次數(shù)”、“精度”、“允許誤差”等取值不當(dāng)。
    6.3本模板還可對單位價(jià)格單元進(jìn)行約束，表明對客戶提出的具體要求從經(jīng)濟(jì)角度進(jìn)行可行性分析。即最終求解價(jià)格有限制要求，若增加該約束時(shí)出現(xiàn)無解，說明超過該價(jià)飼料廠要虧本生產(chǎn)。
    6.4該方法計(jì)算實(shí)驗(yàn)動(dòng)物飼料的常規(guī)營養(yǎng)成分具有快速、準(zhǔn)確的優(yōu)點(diǎn)，只要輸入配方的原糧品種、價(jià)格和配比，該系統(tǒng)將在瞬間一次給出計(jì)算結(jié)果，直接顯示在計(jì)算機(jī)屏幕上。當(dāng)與營養(yǎng)標(biāo)準(zhǔn)相比較后，需要更改調(diào)整時(shí)，可隨時(shí)變更配比，立即自動(dòng)計(jì)算，獲得新的調(diào)整結(jié)果。同時(shí)，可根據(jù)需要手工調(diào)整飼料配方。

    7. 小結(jié)
    利用Excel電子表格優(yōu)化飼料配方非常簡便、快捷，表中數(shù)值的排列靈活多變，可根據(jù)用戶要求自行設(shè)置，且約束條件不受限制（一般情況下），可以使用于各種飼料廠和個(gè)人設(shè)計(jì)飼料配方，尤其對于小型飼料廠而言，采用Excel的“規(guī)劃求解”方法就可使整個(gè)畜牧飼料配方操作變得簡單快捷、結(jié)果準(zhǔn)確、直觀且成本較低。

佳和人

是好東東，但好像很復(fù)雜啊，不會弄。有沒有做好的表格共享啊？

taihe1911

還是有做好的學(xué)習(xí)起來更快點(diǎn)。