




版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認領(lǐng)
文檔簡介
1、利用Excel線性規(guī)劃設(shè)計飼料配方線性規(guī)劃, Excel, 配方, 飼料, 設(shè)計利用Excel線性規(guī)劃設(shè)計飼料配方鄧君明 (中國海洋大學(xué)水產(chǎn)學(xué)院)曾廣廳 (云南農(nóng)業(yè)大學(xué)云南省動物營養(yǎng)與飼料重點實驗室) 摘 要:借助Excel 2000“線性規(guī)劃求解”功能,結(jié)合飼料配方設(shè)計要求,以線性規(guī)劃求解建立數(shù)學(xué)模型,根據(jù)動物的飼養(yǎng)標準,能快速、簡單地求解飼料配方,在最低成本目標下實現(xiàn)對原料和營養(yǎng)成分的有效調(diào)控。 關(guān)鍵詞:Excel;線性規(guī)劃;飼料配方 所謂飼料配方設(shè)計,就是應(yīng)用一定的計算方法,根據(jù)原料的營養(yǎng)成分和配方的規(guī)格、要求
2、,產(chǎn)生配方中各原料比例的一種運算過程。線性規(guī)劃法是目前應(yīng)用最廣泛的一種優(yōu)化飼料配方技術(shù)。線性規(guī)劃最低成本配方的優(yōu)化結(jié)果是產(chǎn)生一個滿足約束條件的最低成本配方,它受原料的營養(yǎng)成分、約束條件值(配方營養(yǎng)素水平)、原料價格等的影響。目前,大型專業(yè)飼料公司己使用計算機和專用的飼料配方軟件進行配方。但專用的飼料配方軟件價格不菲,因此,在小型飼料廠和一般養(yǎng)殖場的應(yīng)用并不普及?,F(xiàn)在有可能只要擁有安裝了Excel 2000的電腦就可以通過計算機運用“線性規(guī)劃求解”求解飼料配方。利用它的行和列的形式組成區(qū)域,在區(qū)域內(nèi)記錄數(shù)據(jù)信息,組成數(shù)據(jù)庫,且可對數(shù)據(jù)進行統(tǒng)計和分析,獲取有效信息。同時,運用線性規(guī)劃實現(xiàn)日糧配方,
3、方法簡單、快捷、準確、方便。本文就利用Excel 2000線性規(guī)劃設(shè)計飼料配方作一闡述。 1. 飼料配方設(shè)計要求 1.1優(yōu)化配方設(shè)計目標 動物生產(chǎn)的經(jīng)濟效益主要取決于飼料。飼料品質(zhì)的好壞,成本的高低直接影響動物生產(chǎn)的經(jīng)濟效益。而飼料配方是配合飼料生產(chǎn)的核心,要優(yōu)化配方設(shè)計,必須同時解決以下三個問題:營養(yǎng)需要問題,由營養(yǎng)學(xué)家研究修改制定,滿足營養(yǎng)標準就是線性規(guī)劃求解的主要約束條件之一,即營養(yǎng)性限制;合理組合原料,不同原料的合理搭配,才能滿足動物的營養(yǎng)需要,是約束條件之二,即原料性限制;價格最低,在符合條件、的基礎(chǔ)上,采
4、用成本最低的原料配比就是求解的目標,即最低成本目標函數(shù)。 1.2數(shù)學(xué)模型 優(yōu)選最佳飼料配方的數(shù)學(xué)原理是線性規(guī)劃法,就是求某一目標函數(shù)在一定的約束條件下的最大值或最小值。約束條件和目標函數(shù)均可用線性方程組或線性不等式表示。線性規(guī)劃最低成本配方優(yōu)化的數(shù)學(xué)模型可表示為: 目標函數(shù):Min S=C1X1+C2X2+CnXn(求最小值) 約束 條件:a11x1+a12x2+a1nxnb1(或=,b1)
5、; a21x1+a22x2+a2nxnb2(或=,b2) am1x1+am2x2+amnxnbm(或=,bm) xj0(j=1,2, ,n) 其中: x1,x2,xn為
6、決策變量,即各種原料在配方中的數(shù)量; aij(i=1,2,m;j=1,2,n)為技術(shù)系數(shù),即各種原料相應(yīng)的營養(yǎng)成分; b1,b2,bn為約束值,即配方中應(yīng)滿足的各項營養(yǎng)指標或重量指標; C1,C2,Cn為成本系數(shù),即每種原料的價格系數(shù); m為約束條件個數(shù); 為配方原料個數(shù)。 1.3飼料配方 用玉米、麩皮、豆粕、菜籽粕、進口魚粉、磷酸氫鈣、石粉、食鹽、賴氨酸、蛋氨酸和預(yù)混料11種原料,以產(chǎn)蛋率<65%的蛋雞為例
7、,各種營養(yǎng)需要為約束條件,計算優(yōu)化飼料配方。 查出產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標準(見表1)與中國飼料成分及營養(yǎng)價值表2002年(第13版)(見表2)。同時還需要查出當?shù)赝诟鞣N飼料原料的市場價格(見表3),并對有些飼料原料的用量作出相應(yīng)的限定(見表4)。按圖1的樣式對應(yīng)將表1、2、3、4中的內(nèi)容填入Excel 2000單元格內(nèi)。表1 產(chǎn)蛋率<65%蛋雞的飼養(yǎng)標準 代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%) 食鹽(%)飼養(yǎng)標準
8、 11.50 14.0 3.2 0.30 0.62 0.31 0.37表2 各種飼料原料營養(yǎng)價值表飼料成分 代謝能(MJ/kg) 粗蛋白(%) 鈣(%) 有效磷(%) 賴氨酸(%) 蛋氨酸(%)玉米
9、160;13.47 7.80 0.02 0.10 0.23 0.15麩皮 6.82 15.70 0.11 0.30 0.58 &
10、#160;0.13豆粕 9.62 43.00 0.32 0.20 2.45 0.64菜籽粕 7.41 38.60 0.65 0.33
11、 1.30 0.63進口魚粉 12.18 62.50 3.96 3.05 5.12 1.66磷酸氫鈣 0.00 0.00 21.00
12、160; 16.00 0.00 0.00石粉 0.00 0.00 35.00 0.00 0.00 0.00油脂 36.82
13、; 0.00 0.00 0.00 0.00 0.00賴氨酸 0.00 0.00 0.00 0.00 78.80 0.00蛋氨酸
14、60; 0.00 0.00 0.00 0.00 0.00 98.00表3 各種飼料原料的市場價格原料 玉米 麩皮 豆粕 菜籽粕 進口魚粉 磷酸氫鈣 石粉 食鹽 油脂 賴氨酸 蛋氨酸 預(yù)混料價格(元
15、/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 幾種飼料原料的約束條件 麩皮 菜籽粕 進口魚粉 油脂 食鹽 預(yù)混料下限(%) 0 0
16、 2 2 0.3 2上限(%) 10 7 5 - 0.3 2圖1 求解飼料配方所用線性模型在表格中的排列樣式圖1中B3至M16是aij值;N3至N16是對應(yīng)于初始值時,線性方程組左側(cè)的值;O3至O16是bi值;B17至M17是Cj值;B18至M
17、18是給定的初始值(初始值均給定為1);N17是給定初始值時的目標函數(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”,然后把光標放在N3格右下角位置上,等光標變成“+”時往下拖動至N17上,這樣從N3至N17格都輸入了相應(yīng)的公式。 2. 線性規(guī)劃求解
18、 往Excel電子表格上輸入完數(shù)據(jù)及公式以后,就可以利用其工具欄中“規(guī)劃求解”功能開始求解,步驟如下: 2.1選中“工具”菜單,選中“規(guī)劃求解”命令,出現(xiàn)“規(guī)劃求解參數(shù)”對話框(如圖2)。 2.2在設(shè)置目標單元格編輯框中,輸入目標單元格的名稱$N$17,然后點擊最小值選項。 2.3在可變單元格中,輸入$B$18:$M$18。圖2 “規(guī)劃求解”對話框2.4在約束窗口中單擊添加按鈕,產(chǎn)生“添加約束”對話框(如圖3),在單元格引用位置和約束值輸入欄中輸入約束條件。如本例在單元格引用位置上先輸入$N$3:$N$8
19、;然后選擇下拉窗口中的“>=”;接著在約束值上輸入$O$3:$O$8,點擊添加按鈕,繼續(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 “添加約束”對話框2.5點擊選項按鈕,進入“規(guī)劃求解選項”對話框(如圖4),選中“采用線形模型”、“假定非負”、“正切函數(shù)”、“向前差分”、“牛頓法”,然后點擊確定按鈕,回到“規(guī)劃求解參數(shù)”對話框。 2.6單擊求
20、解按鈕,開始計算,進入“規(guī)劃求解結(jié)果”對話框(如圖5),在該對話框中可以作以下的選擇: 2.6.1保存規(guī)劃求解的結(jié)果; 2.6.2恢復(fù)為原值。 在本例中選擇默認的選項“保存規(guī)劃求解的結(jié)果”,在“報告”中根據(jù)需要選定計算結(jié)果報告(運算結(jié)果報告、敏感性報告、極限值報告)。需要說明的是:線性規(guī)劃的求解結(jié)果要么有最優(yōu)解,要么給出參考配方(無最優(yōu)解時)。所謂最優(yōu)解就是滿足所有約束條件(包括營養(yǎng)指標和配比限制條件)的最低成本配方。參考配方是指最優(yōu)解不存在時,仍然存在一個最接近理想的配方,它的成本是最低,但是所有的約束條件沒有同時
21、滿足,但該參考配方仍然具有一定的參考價值,因為該結(jié)果往往是可以應(yīng)用的。圖4 “規(guī)劃求解選項”對話框圖5 “規(guī)劃求解結(jié)果”對話框 2.6.7打印規(guī)劃求解結(jié)果 通過以上步驟,就可求出一個蛋雞料的最優(yōu)配方(不一定飼養(yǎng)效果最佳)。可見,利用Excel的“規(guī)劃求解”可以篩選出最優(yōu)的配方,這樣給動物營養(yǎng)設(shè)計師們的工作帶來極大的方便,既提高了工作效率,又提高了配方質(zhì)量。 3. 運行結(jié)果分析 3.1運算結(jié)果報告 在此報告中,我們可以看到求得的飼料配方成本、最優(yōu)配方以及約束條件滿足狀
22、況,其中“型數(shù)值”指求解值與限定值之間的差距,在無法求得最優(yōu)解時,我們據(jù)此可以適當調(diào)節(jié)約束條件。通過線性規(guī)劃求解,從運算結(jié)果報告中可知,本例配合飼料價格為1.58元/kg,飼料配方見表5。表5 蛋雞料飼料配方表 玉米 麩皮 豆粕 菜籽粕 魚粉 磷酸氫鈣 石粉 油脂 食鹽 賴氨酸 蛋氨酸 預(yù)混料配方(%) 66.1 0.0 11.4 7.0 2.0 0.8 8.2
23、0;2.0 0.3 0.0 0.1 2.0 3.2敏感性報告 從該報告中我們可以看到以下內(nèi)容: 遞減成本,表示各個決策變量(飼料原料的添加量)的影子價格,它說明在飼料總量條件不變的情況下,某一種飼料原料用量在最優(yōu)解的基礎(chǔ)上增加1個單位時,目標成本增加的量。 目標式系數(shù)及其允許的增量和減量,指在最優(yōu)解保持不變的情況下,目標式系數(shù)(飼料原料價格)的變化范圍,這反映了所獲得配方對原料市場價格變化的適應(yīng)
24、能力。如果原料價格變化在允許范圍內(nèi),則不必更改配方。 陰影價格,指約束條件的影子價格,表示在所獲得最優(yōu)解的基礎(chǔ)上,當約束條件每增減1個單位時,所引起目標函數(shù)值(飼料最終價格)的增減量。這可為進一步調(diào)整約束條件提供參考。 約束限制值允許的增量和減量,指在保持最優(yōu)解和其它條件不變的情況下,各個約束限制值的可變化范圍,也就是指在此變化范圍內(nèi)表中所列出的約束條件的影子價格才能夠成立。 3.3權(quán)限值報告 該報告列出了最終的飼料價格,以及保證該價格成立的飼料原料添加量的上下極限。
25、; 4. 討論 4.1在進行規(guī)劃求解時,首先要確認在“工具”菜單中出現(xiàn)“規(guī)劃求解”命令,如果沒有則需要安裝“規(guī)劃求解”加載宏,單擊“工具”菜單中,單擊加載宏命令,如果在“加載宏”命令中,沒有列出“規(guī)劃求解”項,單擊瀏覽,確定驅(qū)動器、目錄、文件名或運行加載程序,然后在“加載宏”對話框中,選定“規(guī)劃求解”復(fù)選框。Excel在“規(guī)劃求解”中使用的算法是由Leon lasdon,(University of Texas at Austin)和Allan Waren(Cleveland State University)改進的通用非線性規(guī)劃最佳化代碼。當“規(guī)劃求解選項”對話框中的“采用線性模型”復(fù)選框被選定時,改變可變單元格的初始值不會影響最終數(shù)值和求解時間。在“規(guī)劃
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負責。
- 6. 下載文件中如有侵權(quán)或不適當內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 2025年廣東省中考模擬歷史試題(原卷版+解析版)
- 當前世界經(jīng)濟形勢1468792390
- 九年紀上語文知識點梳理
- 2025年黨員領(lǐng)導(dǎo)干部廉政法規(guī)知識考試題庫及答案(共130題)
- 體育體測檢討書
- FAMILYDAY員工家庭日活動
- 醫(yī)藥航空運輸服務(wù)協(xié)議
- 氫能項目可行性研究報告
- 項目監(jiān)控工程
- 聰明屋智能家居系統(tǒng)
- 2025年合肥共達職業(yè)技術(shù)學(xué)院單招職業(yè)技能測試題庫附答案
- 2025美國急性冠脈綜合征(ACS)患者管理指南解讀課件
- 足球迷互動活動策劃與執(zhí)行策略
- 2025年寧夏工商職業(yè)技術(shù)學(xué)院單招職業(yè)適應(yīng)性測試題庫帶答案
- ESC+2024+心房顫動(房顫)管理指南解讀
- 2019地質(zhì)災(zāi)害防治工程工程量清單計價規(guī)范
- 2022-2024年江蘇中考英語試題匯編:任務(wù)型閱讀填空和閱讀回答問題(教師)
- 游戲跨文化傳播-洞察分析
- 河北石家莊市市屬國有企業(yè)招聘筆試沖刺題2025
- 2025-2030年中國鐵合金冶煉行業(yè)競爭格局展望及投資策略分析報告
-
評論
0/150
提交評論