畜牧人

標題: excel做配方的原理——線性規(guī)劃 [打印本頁]

作者: futingbin 時間: 2008-3-16 11:32
標題: excel做配方的原理——線性規(guī)劃
Excel在配方中的應(yīng)用--線性規(guī)劃.rar

線性規(guī)劃是解決資源合理調(diào)配的應(yīng)用數(shù)學(xué)分支，它通過滿足線性等式或不等式的約束條件來求解線性目標函數(shù)的最大值或最小值。 Office的組件之一Excel自身提供的“加載宏”中有“規(guī)劃求解”一項，可以解決各種線性規(guī)劃任務(wù)。用線性規(guī)劃對飼料配方任務(wù)求解時，不需用飼料配方專用軟件，而是在Excel界面下，通過鼠標或鍵盤操作，即可得出最低成本的飼料配方最優(yōu)解，且約束條件不受限制，適用于各種中小型飼料廠、規(guī)模養(yǎng)殖場進行優(yōu)化飼料配方計算。本文將如何利用Excel制作飼料配方優(yōu)化系統(tǒng)作一詳細介紹。
　　　 1　編制飼料配方優(yōu)化線性模型工作表
在飼料配方任務(wù)線性求解中，一般是目標函數(shù)(S，即飼料配方成本)取最小值，而待解變量x，即每種飼料在配方中的比例是非負的。線性規(guī)劃模型如下：
求目標函數(shù)(S)=c1x1+c2x2+…cjxj+…cnxn的最小值。
約束條件
a11x1+a12x2+...a1nxn☆b1
a21x1+a22x2+...a2nxn☆b2
.........
am1x1+am2x2+...amnxn☆bm
x1>0,x2>0...xn>0
　　　　上式中cj是原料價格(元/kg)，xj是各種飼料原料，S是飼料配方成本，aij是j種飼料第i種營養(yǎng)成分的含量或?qū)︼暳显线M行約束的系數(shù)1，☆表示>，=，<。bj是飼養(yǎng)標準或受約束飼料的值。i=1，2...m，j=1，2... n。
　　　　選定一個工作表，并命名為“線性模型”，將以上模型排列在Excel工作表中，如表1。
表1：飼料配方優(yōu)化線性模型工作表
　
　　　　表1中B3:N24是aij值，Q3:Q24是bj值，B25:N25是cj值，B26:N26是給定原料的初始值，O3:O24是對應(yīng)于初始值時線性方程組左側(cè)的值，O25是給定原料初始值時的目標函數(shù)值(S)。在O3中輸入公式：=$B$26*B3+$C$26*C3+ $D$26*D3+…$M$26*M3+$N$26*N3。選中O3，并向下復(fù)制公式至O25。

　　　2　制作飼料配方結(jié)果工作表
　　　選定一個工作表，并命名為“飼料配方”，制表如表2。
表2：飼料配方結(jié)果工作表
　
　　　　在A4中輸入公式：=規(guī)劃模型B2， A5：=規(guī)劃模型C2，同理在A6:A16輸入公式。在B4中輸入公式：=規(guī)劃模型B26*100，B5：=規(guī)劃模型C26*100，同理在B6:B16中輸入公式。在C4中輸入公式：=規(guī)劃模型B25，C5：=規(guī)劃模型C25，同理在C6:C16中輸入公式。在D4中輸入公式：=B4*C4，并向下復(fù)制公式至D16。在F4中輸入公式：=規(guī)劃模型O3，并向下復(fù)制公式至F15。在B17中輸入函數(shù)：=SUM（B4:B16），在D17中輸入函數(shù)：=SUM（D4:D16），在F18中輸入函數(shù)：=NOW()。

　　　 3　編制飼養(yǎng)標準工作表
　　　　選定一個工作表，并命名為“飼養(yǎng)標準”，制表如表3。
在制作中，飼養(yǎng)標準與線性模型中的營養(yǎng)指標順序應(yīng)一致，缺項指標對飼養(yǎng)標準來說是此項指標不作約束，對飼料數(shù)據(jù)來講是無估測值，可作零處理。放置飼養(yǎng)標準“命令按鈕”，并指定宏。在“飼養(yǎng)標準”工作表界面外的任一工作表下，單擊“工具”菜單，選中“宏”中的“錄制宏”命令，在對話框中“宏名”中輸入“肉雞4周齡以下國標”。單擊“飼養(yǎng)標準”工作表，選中B3:B14單元格區(qū)域，單擊“復(fù)制”命令，選中“規(guī)劃模型”工作表中的Q3:Q14單元格區(qū)域，單擊“粘貼”命令；選中“飼養(yǎng)標準”工作表的B2單元格，單擊“復(fù)制”命令，選中“飼料配方”工作表中的C1單元格，單擊“粘貼”命令；選中“飼養(yǎng)標準”工作表的A2單元格，單擊“復(fù)制”命令，選中“飼料配方”工作表中的C2單元格，單擊“粘貼”命令；單擊“停止錄制”按鈕。選中“飼養(yǎng)標準”工作表，單擊“窗體工具欄”中的“按鈕”，在B15單元格劃方框，在出現(xiàn)對話框中選中“肉雞4周齡以下國標”單擊“確定”，選中“按鈕1”，輸入“肉雞0—4周齡”。同理，可制作其他飼養(yǎng)標準的“命令按鈕”。
表3:飼養(yǎng)標準工作表
　
　　　　4　配方求解
　　　　選中“工具”菜單中的“規(guī)劃求解”命令（如果“規(guī)劃求解”命令沒有出現(xiàn)在“工具”菜單上，請運行“安裝”程序來加載“規(guī)劃求解”。安裝完畢，通過“工具”菜單中的“加載宏”命令，在“加載宏”對話框中選擇并啟動它），產(chǎn)生“規(guī)劃求解參數(shù)”對話框；單擊“目標單元格”編輯框，選中“規(guī)劃模型”工作表的O25單元格；單擊“最小值”選項；單擊“可變單元格”選項，選中“規(guī)劃模型”工作表的B26:N26單元格區(qū)域；在“約束”窗口單擊“增加”按鈕，產(chǎn)生“增加約束”對話框，單擊“單元格引用位置”選項，選中O3:O9單元格區(qū)域，選中“≥”，單擊“約束值”選項，選中Q3:Q9單元格區(qū)域，單擊“確定”按鈕，完成約束條件O3:O9 ≥Q3:Q9的添加，同理添加約束條件O10:O17≤Q10:Q17，O18:O21≥Q18:Q21, O22:O24= Q22:Q24。單擊“選項”按鈕，進入“規(guī)劃求解項目”對話框，選中“采用線性模型”、“假定非負”和“正切函數(shù)”，其余條件可按默認值，單擊“確定”按鈕，回到“規(guī)劃求解參數(shù)”對話框；單擊“求解”按鈕，開始計算。稍等片刻出現(xiàn)“規(guī)劃求解結(jié)果”對話框，若有解，單擊“保存”選項，再單擊“飼料配方”工作表，即可得出結(jié)果。若無解，修改約束條件，重新求解。若想對配方結(jié)果進一步了解，可在“規(guī)劃求解結(jié)果”對話框中，選中“報告”中的項目，如“運算結(jié)果報告”，運算后就產(chǎn)生一張新工作表“運算結(jié)果報告”。
　　　　5　配方優(yōu)化系統(tǒng)的使用
　　　　 5.1　調(diào)整數(shù)據(jù)　選中“線性規(guī)劃”工作表，在B5:N25單元格區(qū)域中調(diào)整好飼料原料單價。在B15:Q23中根據(jù)限量原料情況填入數(shù)據(jù)，例如限制麩皮用量小于等于10%，則在麩皮列B15中填入1，在Q15中填入0.1；限制預(yù)混料用量為1%，則在預(yù)混料列N23中填入1，在Q23中填入0.01，其他類推。
　　　　 5.2　選擇飼養(yǎng)標準　選中“飼養(yǎng)標準”工作表單擊相應(yīng)飼養(yǎng)標準命令按鈕即可。
　　　　 5.3　計算配方　選中“線性規(guī)劃”工作表，單擊“工具”菜單中的“規(guī)劃求解”命令，然后在“規(guī)劃求解參數(shù)”對話框中單擊“求解”，此時出現(xiàn)“規(guī)劃求解結(jié)果”對話框，再單擊“確定即可。如果無解或?qū)λ闩浞讲粷M意，可修改飼養(yǎng)標準以及調(diào)整原料上下限量后重新運算。

[ 本帖最后由 futingbin 于 2008-3-16 11:36 編輯 ]

作者: futingbin 時間: 2008-3-16 11:33
Excel在配方中的應(yīng)用--線性規(guī)劃.rar

上面是一些資料。
當時我們上飼料與飼養(yǎng)學(xué)是我自學(xué)excel在配方中的應(yīng)用是找的。部分是我自己做的。
希望對大家有用的哦。

[ 本帖最后由 futingbin 于 2008-3-16 11:36 編輯 ]

作者: jqshi 時間: 2008-3-16 16:04
我怎么覺得好像有點是在期刊上見過的！

作者: gx1ll2 時間: 2008-3-16 16:45
講得如此詳細，支持樓主。

作者: futingbin 時間: 2008-3-17 22:13

原帖由 jqshi 于 2008-3-16 16:04 發(fā)表
我怎么覺得好像有點是在期刊上見過的！

對。網(wǎng)上通用的哦。
就和韓老師編的飼料與飼養(yǎng)學(xué)上講的也一樣。

作者: ztjun518 時間: 2008-3-18 08:31
希望大家積極討論?。?！

作者: guaiguaiyu 時間: 2008-3-18 08:44
就是，我現(xiàn)在用的是人家已經(jīng)編好的。下載了，以后慢慢學(xué)習(xí)自己弄

作者: zch2623 時間: 2008-3-26 16:53
謝謝,不過程中的第二步和第三步什么意思不明白?能否說明白一下啊

作者: water 時間: 2008-3-26 17:25
以前學(xué)過，但搞忘記了，現(xiàn)在總算補上了！

作者: 2008東北人 時間: 2009-6-11 21:37
好資料。。。。。。。。。。。

作者: kenny-ling 時間: 2012-11-4 16:28
流水！看不明白！！

作者: 金世杰 時間: 2012-12-11 15:44
我支持了?。。?

歡迎光臨畜牧人 (http://ffers.com.cn/)