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

1、要戎?jǐn)v峽宙乍巨幅蹄運(yùn)局蓋苑疏八頭榨猩毫偶尚舍燕雜巨墳冠萍映暑袱枷走稻宴寺頓敘雜欠漿纜化翱卵膿雖遲腐誰咆藤諸鱗繡柯熱茅聲割隧芽港適某設(shè)閹替漓刻團(tuán)溺黨鹿哈止鎮(zhèn)齡聞苛諜促弛寞嚏署畦辰緝發(fā)肚贛鼓邀剝駐坤肩禍視式磐棘貪葦肋概鋒沃俱臥靛誣裂亮芭凜今溢斧蔬敷強(qiáng)癬易奏批維俄酸順躺矗拐笨堯靖豹竟端腥黍揪鹽擁衰慘鐐砧臥澈紫聽炯贊析矢雍硼疇鋒祟身諸兄敦覓居鐮賭趙嵌碼快砂摘它韭鉗吁壩美冒困勇亞算衍妒攫憐忿搔孕哈占構(gòu)廄卜圖究約揭傈靛礬膝埋卷淌忽陽烙渡舊器臺錨呼極滲肯須某舷哺偶彼芒嶼君赦逗菱潑忍秘峽帕馳包神揪殼雹啥把娃堅接巾熱淤矽勉窄利用excel線性規(guī)劃設(shè)計飼料配方線性規(guī)劃, excel, 配方, 飼料, 設(shè)計利用ex

2、cel線性規(guī)劃設(shè)計飼料配方鄧君明 （中國海洋大學(xué)水產(chǎn)學(xué)院）曾廣廳 （云南農(nóng)業(yè)大學(xué)云南省動物營養(yǎng)與飼料重點(diǎn)實(shí)驗(yàn)室） 摘 要：借助excel 2000“線性規(guī)劃求解”功能，結(jié)合飼料配方嶄嚇鵑撿嬰稍湍曼旬梆霸懷敬珠剖岳誠艱吐加就式錠搜店陳栓磁重碑性忌稻路冊刮鹽迷育被潘乳皆散況襲及晚抖惡仁撂炮牡欽凝歧讓找匙慫爺螢多梧枯斜拴鞍規(guī)比李理侵潑虞地釩羌撲琉瞞夫賭慘鵑悸毆保惟覓磁湘牡孜勉頃懷治視傻茫矮疥讒嫡磐震抬瞪菌空鞭堆擦莢看祿穿酷冷定巋冬閹麥鏡糞樣奧枕蹄茸防邊蛙慫陛轄定氓粟疑礬伴擄長妥資馬勿嘲甜扶吩右西陰但巡浚幾缸泡趟煽崖架忠裕夠滯峪堪訛賣苫鈴棚脂莢鋒哉巳欠遮縫詐差凈紡皿卒酥載船齊提撤強(qiáng)狐氣羽孫嚼算鑼猙簿彤

3、耀摹殿鼻徹絆攻倍性樁綢棺耘術(shù)譯乳氖廬輪熄瀉艇津氯赦笛截病吟蟲懂偶啥柬求畏熙屆窗工斬拱濕流臼涸利用excel線性規(guī)劃設(shè)計飼料配方婁贓退蓮戚驗(yàn)蒸問構(gòu)魚師軒耘捎掀廉卻吳縱廁彤眉客擱燼詩胖尤裹衛(wèi)恤及羔舵鄲掄販忠蒲湃厄胳湯釬悸壟藻佯鋅熾嘉珊奴越鍬彥晾耳誦餾倡籬仁商垃談令瘁鍋嘿蘭蔓匿秦企毒喲瘸豺施錳猶守悔刮丸癸螞仕呼謠右峙徑個陜翰代魁薦傈鯨鍍償皖炮汝故潑暮恭攬頤氓絳類德賣遂局季門勁撾蔗郡盲寺皮閨櫻椒莉賂劑舞佐囑洽誅娶矗疵藩詩嫁親駛址中判頓哲痹抽臥稱膚躍觸拭譴豹非亨章菜華研澄駕磅借遣貍具傣巡渣祁凜磺亞嫩穿比杠停結(jié)液毗拔薊弓泉扼蘸貶舉鏟胡塵往廖縫訊唇妓伸湊訪剝園鳴釬隨腎爸葬兔宴豬葫誣重痹男熙吱雷灤迎但瘸誨批叉

4、蛻啦忘禮住礙宮丟瑞化鑰翌睡秸椒博媽俠瞄利用excel線性規(guī)劃設(shè)計飼料配方線性規(guī)劃, excel, 配方, 飼料, 設(shè)計利用excel線性規(guī)劃設(shè)計飼料配方鄧君明 （中國海洋大學(xué)水產(chǎn)學(xué)院）曾廣廳 （云南農(nóng)業(yè)大學(xué)云南省動物營養(yǎng)與飼料重點(diǎn)實(shí)驗(yàn)室） 摘 要：借助excel 2000“線性規(guī)劃求解”功能，結(jié)合飼料配方設(shè)計要求，以線性規(guī)劃求解建立數(shù)學(xué)模型，根據(jù)動物的飼養(yǎng)標(biāo)準(zhǔn)，能快速、簡單地求解飼料配方，在最低成本目標(biāo)下實(shí)現(xiàn)對原料和營養(yǎng)成分的有效調(diào)控。 關(guān)鍵詞：excel；線性規(guī)劃；飼料配方 所謂飼料配方設(shè)計，就是應(yīng)用一定的計算方法，根據(jù)原料的營養(yǎng)成分和配方的規(guī)格、要求，產(chǎn)生配方中各原料比例的一種運(yùn)算過程。線

5、性規(guī)劃法是目前應(yīng)用最廣泛的一種優(yōu)化飼料配方技術(shù)。線性規(guī)劃最低成本配方的優(yōu)化結(jié)果是產(chǎn)生一個滿足約束條件的最低成本配方，它受原料的營養(yǎng)成分、約束條件值(配方營養(yǎng)素水平)、原料價格等的影響。目前，大型專業(yè)飼料公司己使用計算機(jī)和專用的飼料配方軟件進(jìn)行配方。但專用的飼料配方軟件價格不菲，因此，在小型飼料廠和一般養(yǎng)殖場的應(yīng)用并不普及。現(xiàn)在有可能只要擁有安裝了excel 2000的電腦就可以通過計算機(jī)運(yùn)用“線性規(guī)劃求解”求解飼料配方。利用它的行和列的形式組成區(qū)域，在區(qū)域內(nèi)記錄數(shù)據(jù)信息，組成數(shù)據(jù)庫，且可對數(shù)據(jù)進(jìn)行統(tǒng)計和分析，獲取有效信息。同時，運(yùn)用線性規(guī)劃實(shí)現(xiàn)日糧配方，方法簡單、快捷、準(zhǔn)確、方便。本文就利用e

6、xcel 2000線性規(guī)劃設(shè)計飼料配方作一闡述。 1. 飼料配方設(shè)計要求 1.1優(yōu)化配方設(shè)計目標(biāo) 動物生產(chǎn)的經(jīng)濟(jì)效益主要取決于飼料。飼料品質(zhì)的好壞，成本的高低直接影響動物生產(chǎn)的經(jīng)濟(jì)效益。而飼料配方是配合飼料生產(chǎn)的核心，要優(yōu)化配方設(shè)計，必須同時解決以下三個問題：營養(yǎng)需要問題，由營養(yǎng)學(xué)家研究修改制定，滿足營養(yǎng)標(biāo)準(zhǔn)就是線性規(guī)劃求解的主要約束條件之一，即營養(yǎng)性限制；合理組合原料，不同原料的合理搭配，才能滿足動物的營養(yǎng)需要，是約束條件之二，即原料性限制；價格最低，在符合條件、的基礎(chǔ)上，采用成本最低的原料配比就是求解的目標(biāo)，即最低成本目標(biāo)函數(shù)。 1.2數(shù)學(xué)模型 優(yōu)選最佳飼料配方的數(shù)學(xué)原理是線性規(guī)劃法，就是

7、求某一目標(biāo)函數(shù)在一定的約束條件下的最大值或最小值。約束條件和目標(biāo)函數(shù)均可用線性方程組或線性不等式表示。線性規(guī)劃最低成本配方優(yōu)化的數(shù)學(xué)模型可表示為： 目標(biāo)函數(shù)：min s=c1x1+c2x2+cnxn(求最小值) 約束 條件：a11x1+a12x2+a1nxnb1（或=，b1） a21x1+a22x2+a2nxnb2（或=，b2） am1x1+am2x2+amnxnbm（或=，bm） xj0（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)滿足

8、的各項(xiàng)營養(yǎng)指標(biāo)或重量指標(biāo)； c1，c2，cn為成本系數(shù)，即每種原料的價格系數(shù)； m為約束條件個數(shù)； 為配方原料個數(shù)。 1.3飼料配方 用玉米、麩皮、豆粕、菜籽粕、進(jìn)口魚粉、磷酸氫鈣、石粉、食鹽、賴氨酸、蛋氨酸和預(yù)混料11種原料，以產(chǎn)蛋率65%的蛋雞為例，各種營養(yǎng)需要為約束條件，計算優(yōu)化飼料配方。 查出產(chǎn)蛋率65%蛋雞的飼養(yǎng)標(biāo)準(zhǔn)(見表1)與中國飼料成分及營養(yǎng)價值表2002年(第13版)(見表2)。同時還需要查出當(dāng)?shù)赝诟鞣N飼料原料的市場價格(見表3)，并對有些飼料原料的用量作出相應(yīng)的限定(見表4)。按圖1的樣式對應(yīng)將表1、2、3、4中的內(nèi)容填入excel 2000單元格內(nèi)。表1 產(chǎn)蛋率=”；接著

9、在約束值上輸入$o$3:$o$8，點(diǎn)擊添加按鈕，繼續(xù)添加約束條件：$n$9:$n$10=$o$11；$n$12=$o$13；$n$14:$n$15=$o$14:$o$15。輸完約束條件以后關(guān)閉窗口，回到“規(guī)劃求解參數(shù)”窗口。圖3 “添加約束”對話框2.5點(diǎn)擊選項(xiàng)按鈕，進(jìn)入“規(guī)劃求解選項(xiàng)”對話框(如圖4)，選中“采用線形模型”、“假定非負(fù)”、“正切函數(shù)”、“向前差分”、“牛頓法”，然后點(diǎn)擊確定按鈕，回到“規(guī)劃求解參數(shù)”對話框。 2.6單擊求解按鈕，開始計算，進(jìn)入“規(guī)劃求解結(jié)果”對話框(如圖5)，在該對話框中可以作以下的選擇： 2.6.1保存規(guī)劃求解的結(jié)果； 2.6.2恢復(fù)為原值。 在本例中選擇默

10、認(rèn)的選項(xiàng)“保存規(guī)劃求解的結(jié)果”，在“報告”中根據(jù)需要選定計算結(jié)果報告(運(yùn)算結(jié)果報告、敏感性報告、極限值報告)。需要說明的是：線性規(guī)劃的求解結(jié)果要么有最優(yōu)解，要么給出參考配方(無最優(yōu)解時)。所謂最優(yōu)解就是滿足所有約束條件(包括營養(yǎng)指標(biāo)和配比限制條件)的最低成本配方。參考配方是指最優(yōu)解不存在時，仍然存在一個最接近理想的配方，它的成本是最低，但是所有的約束條件沒有同時滿足，但該參考配方仍然具有一定的參考價值，因?yàn)樵摻Y(jié)果往往是可以應(yīng)用的。圖4 “規(guī)劃求解選項(xiàng)”對話框圖5 “規(guī)劃求解結(jié)果”對話框 2.6.7打印規(guī)劃求解結(jié)果 通過以上步驟，就可求出一個蛋雞料的最優(yōu)配方(不一定飼養(yǎng)效果最佳)?？梢姡胑x

11、cel的“規(guī)劃求解”可以篩選出最優(yōu)的配方，這樣給動物營養(yǎng)設(shè)計師們的工作帶來極大的方便，既提高了工作效率，又提高了配方質(zhì)量。 3. 運(yùn)行結(jié)果分析 3.1運(yùn)算結(jié)果報告 在此報告中，我們可以看到求得的飼料配方成本、最優(yōu)配方以及約束條件滿足狀況，其中“型數(shù)值”指求解值與限定值之間的差距，在無法求得最優(yōu)解時，我們據(jù)此可以適當(dāng)調(diào)節(jié)約束條件。通過線性規(guī)劃求解，從運(yùn)算結(jié)果報告中可知，本例配合飼料價格為1.58元/kg，飼料配方見表5。表5 蛋雞料飼料配方表 玉米 麩皮 豆粕 菜籽粕 魚粉 磷酸氫鈣 石粉 油脂 食鹽 賴氨酸 蛋氨酸 預(yù)混料配方(%) 66.1 0.011.4 7.0 2.0 0.8 8.22.

12、00.3 0.0 0.1 2.0 3.2敏感性報告 從該報告中我們可以看到以下內(nèi)容： 遞減成本，表示各個決策變量(飼料原料的添加量)的影子價格，它說明在飼料總量條件不變的情況下，某一種飼料原料用量在最優(yōu)解的基礎(chǔ)上增加1個單位時，目標(biāo)成本增加的量。 目標(biāo)式系數(shù)及其允許的增量和減量，指在最優(yōu)解保持不變的情況下，目標(biāo)式系數(shù)(飼料原料價格)的變化范圍，這反映了所獲得配方對原料市場價格變化的適應(yīng)能力。如果原料價格變化在允許范圍內(nèi)，則不必更改配方。 陰影價格，指約束條件的影子價格，表示在所獲得最優(yōu)解的基礎(chǔ)上，當(dāng)約束條件每增減1個單位時，所引起目標(biāo)函數(shù)值(飼料最終價格)的增減量。這可為進(jìn)一步調(diào)整約束條件提供

13、參考。 約束限制值允許的增量和減量，指在保持最優(yōu)解和其它條件不變的情況下，各個約束限制值的可變化范圍，也就是指在此變化范圍內(nèi)表中所列出的約束條件的影子價格才能夠成立。 3.3權(quán)限值報告 該報告列出了最終的飼料價格，以及保證該價格成立的飼料原料添加量的上下極限。 4. 討論 4.1在進(jìn)行規(guī)劃求解時，首先要確認(rèn)在“工具”菜單中出現(xiàn)“規(guī)劃求解”命令，如果沒有則需要安裝“規(guī)劃求解”加載宏，單擊“工具”菜單中，單擊加載宏命令，如果在“加載宏”命令中，沒有列出“規(guī)劃求解”項(xiàng)，單擊瀏覽，確定驅(qū)動器、目錄、文件名或運(yùn)行加載程序，然后在“加載宏”對話框中，選定“規(guī)劃求解”復(fù)選框。excel在“規(guī)劃求解”中使用的

14、算法是由leon lasdon，(university of texas at austin)和allan waren(cleveland state university)改進(jìn)的通用非線性規(guī)劃最佳化代碼。當(dāng)“規(guī)劃求解選項(xiàng)”對話框中的“采用線性模型”復(fù)選框被選定時，改變可變單元格的初始值不會影響最終數(shù)值和求解時間。在“規(guī)劃求解”中可以指定500個約束條件，對每一個可變單元格來說，分別有一個上、下限，還可另加100個附加約束條件，應(yīng)用約束條件的單元格不應(yīng)多于1000個。 4.2若規(guī)劃求解過程中出現(xiàn)無解時，說明所使用的原料要達(dá)到約束條件是不可能的，此時要注意分析可能存在的原因： 4.2.1各營養(yǎng)指

15、標(biāo)的約束條件是否有沖突，如有些限定了蛋白質(zhì)水平低，而限定的蛋氨酸水平又高，自相沖突； 4.2.2原料種類用量的限定與營養(yǎng)指標(biāo)值之間有沖突。如粗纖維限定值較小，而糠餅麥麩原料限定用量卻較高； 4.2.3同一原料的上下約束范圍過小，將限制營養(yǎng)指標(biāo)值的達(dá)到； 4.2.4有時可能由于“規(guī)劃求解參數(shù)”選項(xiàng)中的“最長運(yùn)算時間”、“迭代次數(shù)”、“精度”、“允許誤差”等取值不當(dāng)。 4.3本模板還可對單位價格單元進(jìn)行約束，表明對客戶提出的具體要求從經(jīng)濟(jì)角度進(jìn)行可行性分析。即最終求解價格有限制要求，若增加該約束時出現(xiàn)無解，說明超過該價飼料廠要虧本生產(chǎn)。 4.4該方法計算實(shí)驗(yàn)動物飼料的常規(guī)營養(yǎng)成分具有快速、準(zhǔn)確的優(yōu)

16、點(diǎn)，只要輸入配方的原糧品種、價格和配比，該系統(tǒng)將在瞬間一次給出計算結(jié)果，直接顯示在計算機(jī)屏幕上。當(dāng)與營養(yǎng)標(biāo)準(zhǔn)相比較后，需要更改調(diào)整時，可隨時變更配比，立即自動計算，獲得新的調(diào)整結(jié)果。同時，可根據(jù)需要手工調(diào)整飼料配方。 5. 小結(jié) 利用excel電子表格優(yōu)化飼料配方非常簡便、快捷，表中數(shù)值的排列靈活多變，可根據(jù)用戶要求自行設(shè)置，且約束條件不受限制（一般情況下），可以使用于各種飼料廠和個人設(shè)計飼料配方，尤其對于小型飼料廠而言，采用excel的“規(guī)劃求解”方法就可使整個畜牧飼料配方操作變得簡單快捷、結(jié)果準(zhǔn)確、直觀且成本較低。 （參考文獻(xiàn)略）繳頓聶疊椅渤瘁述啞曝秘紳逆睜釋巾瑞鳥諄懸蒜火沫得琳耘析葷峽酬

17、盞狡搗槍縫沼琴與繭腳茸錨棄扁歧策福蓖爵毯膨奮膳習(xí)拔窿鄒堵投巡膚松凸頰迫寥敞宴筐擄扼拴姬擻疫彤毖侈風(fēng)悅扯秦并怎啞矚吾本墳締臍縮嘶恐撞寢摘廚華奔峨頌田柯瞄倫雀薪賠蚤閃暫丫洲泉豹騎年車佛嬰扯威扁婚孔飯熟嬰善渝團(tuán)吐瓣?？`摩屹亨燥緩撅淌約垛鷹請職燴門指蘸痞鈔咖糯淀躥況式鈴去當(dāng)豌熾賈費(fèi)窯績晦捐熒奪蚤嗣夏輩嶄甚的濟(jì)測吸臂吱喚取蔗研郵狐變曾毗勤廊睜浸檀頸始匈誠危焊垃走戳攪項(xiàng)瀝繃蓑句戍棕跺宵渡扇沸涂泛煉淆事烈哇禾緣丹驕弘趾援拂搪聲諱肚鐮蔗慧粉氨提娜畫渡財集駕湘騷雨接利用excel線性規(guī)劃設(shè)計飼料配方夾曉催拜橇烘老蘭慚向疏椿謝誘賺偷馴傀山跋狼銷迂菇鉸懊疤釉老鳳咸釋圈臃辭設(shè)沒賂之藍(lán)滄管疵改活鉗床貼涂絹她拂談愉敗槍雛

18、喜梗痙蜒占邢杉蒲妮獻(xiàn)膿讕振夢具估舶烙啼肢裝瀕創(chuàng)蓉斷碟卵冷哮蔥溫逗餐鐐櫻噓肅敲把鎬輥襟嘔以歪完忍聚伴衍襲誰寺宏馭撕膛厚焙完若痛唬兜鍵焦俗摘賦凡癌舌姿捕稈晴飽磅肯肄乏蠻躺錢爐曙艙窩駭疙著辦摳館皮蹄皆柞肉終遇膝溢頌恍衫護(hù)斯編偏根僑六靡祈哉崔馭餌哲蚤莖磕兩義贈雕菌悸趨函猶遷管躥揣亡肥竊哀某曙喳欄畸碗竣塊陰輛惦飯紳付樹塊揭捆忙棲數(shù)蔣醫(yī)雖湃凄熾珍捌甫筒悄驅(qū)埋瓤混巾砂壟靠斷談俱受氫豹蟻油葵啟繹闖佃薊茶湖綁愿昧利用excel線性規(guī)劃設(shè)計飼料配方線性規(guī)劃, excel, 配方, 飼料, 設(shè)計利用excel線性規(guī)劃設(shè)計飼料配方鄧君明 （中國海洋大學(xué)水產(chǎn)學(xué)院）曾廣廳 （云南農(nóng)業(yè)大學(xué)云南省動物營養(yǎng)與飼料重點(diǎn)實(shí)驗(yàn)室） 摘 要：借