畜牧人

標題: Excell配方設(shè)計原理與實踐 [打印本頁]
作者: 謝慶華    時間: 2015-10-6 23:22
標題: Excell配方設(shè)計原理與實踐
本帖最后由 謝慶華 于 2015-10-6 23:56 編輯

Excel飼料配方設(shè)計原理與實踐
                                                                          ------謝慶華 【華豐養(yǎng)殖咨詢網(wǎng)】www.huafengyangzhi.com   
一、Excel飼料配方設(shè)計的原理
      利用線型規(guī)劃方法進行飼料配方設(shè)計，要求飼料配方既能全面滿足養(yǎng)殖對象的各種營養(yǎng)需要，又要使成本達到最低。Microsoft Office Excel提供了強大的線型規(guī)劃工具（linear programing)，其模型如下：
a11x1 + a12x2+ a13x3 + ··· + a1nxn ≤ or = or ≥ b1
a21x1 + a22x2+ a23x3  + ··· + a2nxn ≤ or = or ≥ b2
∣            ∣           ∣             ∣                       ∣
∣            ∣           ∣             ∣                       ∣
am1x1 + am2x2+ am3x3  + ··· + amnxn ≤ or = or ≥ bm   
目標函數(shù)（配方成本）：f(x) = c1x1 + c2x2+ c3x3  + ··· + cnxn →最小值，
【注】1、≤ ，= ≥三種關(guān)系符號任選其一；
      2、Cj( j = 1,2,3,···n )為各飼料原料的單價；
      3、xi( i = 1,2,3,···n )為決策變量,即配方中各種原料的含量，且
         xj ≥ 0， x1+x2+···+xn = 100% ；
二、Excel設(shè)計飼料配方的原理與步驟：
1、設(shè)計Exce飼料配方規(guī)劃表格
表1：Excel規(guī)劃設(shè)計表
　　　　　　　　　　　
原料數(shù)據(jù)表代謝能粗蛋白鈣總磷有效磷食鹽粗纖維亞油酸賴氨酸蛋氨酸蛋+胱單價　
原料名稱KC/kg( % )( % )( % )( % )( % )( % )( % )( % )( % )( % )元/kg　
玉米322080.020.270.1201.62.20.17940.13650.252.46　
豆粕432390430.330.620.1805.90.512.33160.51331.052.7　
進口魚粉2960663.812.832.830.60.50.24.6981.57322.0313　
豆油837000000053.10005.6　
麥麩163015.50.110.920.2406.81.70.4640.10270.281.9　
石粉0037.6000000000.4　
磷酸輕鈣002118180000001.95　
2%預混料133214.700018.500512.512.510　
配方規(guī)劃表　　　　　　　　　　　　　
原料名稱代謝能:KC/kg粗蛋白：%  鈣：%  總磷：%  有效磷：%  食鹽：%  粗纖維：%  亞油酸：%  賴氨酸：%  蛋氨酸：%  蛋+胱：%  成本：元/100kg配方含量：%
玉米20025.00.010.170.070.000.991.370.110.080.16152.9262.16
豆粕435429.80.070.140.040.001.340.120.530.120.2461.2522.69
進口魚粉591.30.080.060.060.010.010.000.090.030.0426.002.00
豆油1040.00.000.000.000.000.000.660.000.000.006.941.24
麥麩170.20.000.010.000.000.070.020.000.000.001.941.02
石粉00.02.870.000.000.000.000.000.000.000.003.067.64
磷酸輕鈣00.00.260.230.230.000.000.000.000.000.002.441.25
2%預混料270.30.000.000.000.370.000.000.100.250.2520.002.00
合 計275016.53.300.600.400.382.412.160.840.480.69274.55100.00
飼養(yǎng)標準275016.53.30.60.320.3501.10.670.320.58標準下限
280017.03.60.70.40.43.5--------標準上限
【單元格計算公式】
1、配方營養(yǎng)濃度=∑原料的營養(yǎng)濃度×配方中該原料的含量，例如：
代謝能= 3220×62.16%+2390×22.69%+2960×2%+8370×1.24%+1630×1.02%+1332×2%= 2750
配方成本=2.46×62.16+2.7×22.69+13×+5.6×1.24+1.9×1.02+0.4×7.64+1.95×1.25+10×2=274.55
2、規(guī)劃求解：
（1）如果在工具菜單里沒找到“規(guī)劃求解”這一項，請按以下步驟操作：工具→加載宏→規(guī)劃求解；
（2）點擊目標單元格→工具→規(guī)劃求解→選項：采用線性規(guī)劃模型、假定非負→確定→填寫以下規(guī)劃求解參數(shù)：
①目標單元格：即配方成本最下方的單元格
②選擇最小值：即讓目標單元格的值達到最??；
③填寫約束條件：
A、配方含量Xi ≥0， Xi（合計）=100，X（進口魚粉）=2，X（2%復合預混料）=2 ；
B、約束條件設(shè)置：約束下限（飼養(yǎng)標準最低值） ≤ 約束單元格 ≤約束下限（飼養(yǎng)標準最高值），可變單元格中魚粉、預混料設(shè)為固定值，用"="表示。
3、點“求解”按紐，在右上角選擇運算結(jié)果報告、敏感性報告、極限值報告。
4、線性規(guī)劃結(jié)果分析：如下表2、表3、表4
表2：運算結(jié)果報告
目標單元格 (最小值)　　　　
　單元格名字      初值                終值　　
　$M$26合 計 配方成本：元/100kg
274.5
274.5
　　
可變單元格　　　　
　單元格名字      初值                終值　　
　$N$18玉米 配方含量：%
62.16
62.16
　　
　$N$19豆粕43 配方含量：%
22.69
22.69
　　
　$N$20進口魚粉 配方含量：%
2.00
2.00
　　
　$N$21豆油 配方含量：%
1.24
1.24
　　
　$N$22麥麩 配方含量：%
1.02
1.02
　　
　$N$23石粉 配方含量：%
7.64
7.64
　　
　$N$24磷酸輕鈣 配方含量：%
1.25
1.25
　　
　$N$252%預混料 配方含量：%
2.00
2.00
　　
約束　　　　
　單元格名字單元格值       公式   狀態(tài)        型數(shù)值
　$N$26合 計 配方含量：%
100
$N$26=100未到限制值
0
　$B$26合 計 代謝能:KC/kg
2750
$B$26>=$B$27到達限制值
0
　$C$26合 計 粗蛋白：%  
16.5
$C$26>=$C$27到達限制值
0.0
　$D$26合 計 鈣：%  
3.30
$D$26>=$D$27到達限制值
0.00
　$E$26合 計 總磷：%  
0.60
$E$26>=$E$27到達限制值
0.00
　$F$26合 計 有效磷：%  
0.40
$F$26>=$F$27未到限制值
0.08
　$G$26合 計 食鹽：%  
0.38
$G$26>=$G$27未到限制值
0.03
　$H$26合 計 粗纖維：%  
2.41
$H$26>=$H$27未到限制值
2.41
　$I$26合 計 亞油酸：%  
2.16
$I$26>=$I$27未到限制值
1.06
　$J$26合 計 賴氨酸：%  
0.84
$J$26>=$J$27未到限制值
0.17
　$K$26合 計 蛋氨酸：%  
0.48
$K$26>=$K$27未到限制值
0.16
　$L$26合 計 蛋+胱：%  
0.69
$L$26>=$L$27未到限制值
0.11
　$B$26合 計 代謝能:KC/kg
2750
$B$26<=$B$28未到限制值
50
　$C$26合 計 粗蛋白：%  
16.5
$C$26<=$C$28未到限制值
0.5
　$D$26合 計 鈣：%  
3.30
$D$26<=$D$28未到限制值
0.3
　$E$26合 計 總磷：%  
0.60
$E$26<=$E$28未到限制值
0.1
　$F$26合 計 有效磷：%  
0.40
$F$26<=$F$28到達限制值
0
　$G$26合 計 食鹽：%  
0.38
$G$26<=$G$28未到限制值
0.018
　$H$26合 計 粗纖維：%  
2.41
$H$26<=$H$28未到限制值
1.087554134
　$N$18玉米 配方含量：%
62.16
$N$18>=0未到限制值
62.16
　$N$19豆粕43 配方含量：%
22.69
$N$19>=0未到限制值
22.69
　$N$20進口魚粉 配方含量：%
2.00
$N$20>=0未到限制值
2.00
　$N$21豆油 配方含量：%
1.24
$N$21>=0未到限制值
1.24
　$N$22麥麩 配方含量：%
1.02
$N$22>=0未到限制值
1.02
　$N$23石粉 配方含量：%
7.64
$N$23>=0未到限制值
7.64
　$N$24磷酸輕鈣 配方含量：%
1.25
$N$24>=0未到限制值
1.25
　$N$252%預混料 配方含量：%
2.00
$N$25>=0未到限制值
2.00
　$N$20進口魚粉 配方含量：%
2.00
$N$20=2到達限制值
0
　$N$252%預混料 配方含量：%
2.00
$N$25=2未到限制值
0

   
表3：敏感性報告 　　　　　
可變單元格　　　　　
　　　         終         遞減      目標式       允許的        允許的
　單元格名字         值         成本        系數(shù)       增量        減量
　$N$18玉米 配方含量：%
62.16
0.00
2.46
0.122904115
1E+30
　$N$19豆粕43 配方含量：%
22.69
0.00
2.7
1.618948344
0.520625233
　$N$20進口魚粉 配方含量：%
2.00
9.55
13
1E+30
9.5520341
　$N$21豆油 配方含量：%
1.24
0.00
5.6
37.59259988
0.384575393
　$N$22麥麩 配方含量：%
1.02
0.00
1.9
0.941414723
0.250227373
　$N$23石粉 配方含量：%
7.64
0.00
0.4
73.82279118
0.301785931
　$N$24磷酸輕鈣 配方含量：%
1.25
0.00
1.95
8.573436827
34.76984556
　$N$252%預混料 配方含量：%
2.00
8.78
10
1E+30
8.77761483
約束　　　　　
　　　         終         陰影        約束        允許的        允許的
　單元格名字         值         價格       限制值        增量        減量
　$N$26合 計 配方含量：%
100
0
100
1.590326921
25.50567987
　$B$26合 計 代謝能:KC/kg
2750
0
2750
50
58.28314898
　$C$26合 計 粗蛋白：%  
16.5
1.7
16.5
0.5
2.625285545
　$D$26合 計 鈣：%  
3.30
0.80
3.3
0.3
0.597962922
　$E$26合 計 總磷：%  
0.60
65.45
0.6
0.1
0.004534933
　$F$26合 計 有效磷：%  
0.40
0.00
0.32
0.08
1E+30
　$G$26合 計 食鹽：%  
0.38
0.00
0.35
0.032
1E+30
　$H$26合 計 粗纖維：%  
2.41
0.00
0
2.412445866
1E+30
　$I$26合 計 亞油酸：%  
2.16
0.00
1.1
1.062394641
1E+30
　$J$26合 計 賴氨酸：%  
0.84
0.00
0.67
0.169150801
1E+30
　$K$26合 計 蛋氨酸：%  
0.48
0.00
0.32
0.163809728
1E+30
　$L$26合 計 蛋+胱：%  
0.69
0.00
0.58
0.107063462
1E+30
　$B$26合 計 代謝能:KC/kg
2750
0
2800
1E+30
50
　$C$26合 計 粗蛋白：%  
16.5
0.0
17
1E+30
0.5
　$D$26合 計 鈣：%  
3.30
0.00
3.6
1E+30
0.3
　$E$26合 計 總磷：%  
0.60
0.00
0.7
1E+30
0.1
　$F$26合 計 有效磷：%  
0.40
-56.11
0.4
0.004550733
0.08
　$G$26合 計 食鹽：%  
0.38
0.00
0.4
1E+30
0.018
　$H$26合 計 粗纖維：%  
2.41
0.00
3.5
1E+30
1.087554134


表4：極限值報告 　　　　
　目標式　　　　　
單元格名字值　　　　
$M$26合 計 配方成本：元/100kg274.5　　　　
　變量　        下限      目標式         上限      目標式
單元格名字值        極限      結(jié)果         極限       結(jié)果
$N$18玉米 配方含量：%62.16
62.16
274.55
62.16
274.55
$N$19豆粕43 配方含量：%22.69
22.69
274.55
22.69
274.55
$N$20進口魚粉 配方含量：%2.00
2.00
274.55
2.00
274.55
$N$21豆油 配方含量：%1.24
1.24
274.55
1.24
274.55
$N$22麥麩 配方含量：%1.02
1.02
274.55
1.02
274.55
$N$23石粉 配方含量：%7.64
7.64
274.55
7.64
274.55
$N$24磷酸輕鈣 配方含量：%1.25
1.25
274.55
1.25
274.55
$N$252%預混料 配方含量：%2.00
2.00
274.55
2.00
274.55
【運算結(jié)果報告】
1、初值：規(guī)劃運算前預設(shè)的各原料的含量，可設(shè)定為任意值。
2、終值：最終計算而得的各原料的含量（即規(guī)劃最優(yōu)解）。
3、數(shù)型值：即求解得到的約束單元格值與約束值之間的差值，取值于"0"和約束值之間。
4、約束單元格的狀態(tài)：當約束單元格"達到極限值"時，數(shù)型值=0，說明已經(jīng)沒有調(diào)整約束值的必要了；當約束單元格"未達到極限值"時，數(shù)型值≠0，表明對約束值進行調(diào)整，可以降低配方成本。
【敏感性報告】 這是規(guī)劃運算結(jié)果報告中最具價值的部分
1、遞減成本：是指各個資源條件（即原料）成本的首次變動幅度。如果某個資源條件成本的首次變動幅度小于該值，則不會改變規(guī)劃最優(yōu)解的結(jié)果。在表3中，對于玉米、豆粕、豆油等原料的遞減成本為0，這表明為了不改變此規(guī)劃求解的結(jié)果，這幾個原料的價格最好不要變動。但這在實踐中是不可能的，因此，在敏感性報告中遞減成本后面給出了"允許的增量" 和 "允許的減量" 。例如，對玉米而言，雖然它的遞減成本為0，但其"允許的減量"是接近無窮大的，表明即使玉米的價格下降也不會改變規(guī)劃最優(yōu)解結(jié)果。遞減成本是調(diào)整配方含量的依據(jù)。
2、目標式系數(shù)：是指相應(yīng)原料的價格。
3、陰影價格：是指配方中各約束指標每單位約束值得價格系數(shù)。 陰影價格是調(diào)整約束值的依據(jù)。陰影價格越大，表明該約束指標對規(guī)劃求解結(jié)果的影響越明顯。
4、約束單元格"允許的增量"和"允許的減量"：是指在獲得最優(yōu)解的情況下，約束指標值的變動范圍。在此范圍內(nèi)調(diào)整約束值，對規(guī)劃最優(yōu)解結(jié)果沒有影響。
【極限值報告】
在這個報告中給出了需要通過規(guī)劃求解過程來確定的兩類單元格(目標單元格和可變單元格)數(shù)值的極限值。




歡迎光臨 畜牧人 (http://www.livemosquitofree.com/) Powered by Discuz! X3.5