查看: 1829|回復(fù): 0
打印 上一主題 下一主題

Excell配方設(shè)計(jì)原理與實(shí)踐

[復(fù)制鏈接]
跳轉(zhuǎn)到指定樓層
樓主
發(fā)表于 2015-10-6 23:22:58 | 只看該作者 回帖獎(jiǎng)勵(lì) |倒序?yàn)g覽 |閱讀模式
本帖最后由 謝慶華 于 2015-10-6 23:56 編輯

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

   
表3：敏感性報(bào)告 　　　　　
可變單元格　　　　　
　　　         終         遞減      目標(biāo)式       允許的        允許的
　單元格名字         值         成本        系數(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進(jìn)口魚粉 配方含量：%
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%預(yù)混料 配方含量：%
2.00
8.78
10
1E+30
8.77761483
約束　　　　　
　　　         終         陰影        約束        允許的        允許的
　單元格名字         值         價(jià)格       限制值        增量        減量
　$N$26合 計(jì) 配方含量：%
100
0
100
1.590326921
25.50567987
　$B$26合 計(jì) 代謝能:KC/kg
2750
0
2750
50
58.28314898
　$C$26合 計(jì) 粗蛋白：%  
16.5
1.7
16.5
0.5
2.625285545
　$D$26合 計(jì) 鈣：%  
3.30
0.80
3.3
0.3
0.597962922
　$E$26合 計(jì) 總磷：%  
0.60
65.45
0.6
0.1
0.004534933
　$F$26合 計(jì) 有效磷：%  
0.40
0.00
0.32
0.08
1E+30
　$G$26合 計(jì) 食鹽：%  
0.38
0.00
0.35
0.032
1E+30
　$H$26合 計(jì) 粗纖維：%  
2.41
0.00
0
2.412445866
1E+30
　$I$26合 計(jì) 亞油酸：%  
2.16
0.00
1.1
1.062394641
1E+30
　$J$26合 計(jì) 賴氨酸：%  
0.84
0.00
0.67
0.169150801
1E+30
　$K$26合 計(jì) 蛋氨酸：%  
0.48
0.00
0.32
0.163809728
1E+30
　$L$26合 計(jì) 蛋+胱：%  
0.69
0.00
0.58
0.107063462
1E+30
　$B$26合 計(jì) 代謝能:KC/kg
2750
0
2800
1E+30
50
　$C$26合 計(jì) 粗蛋白：%  
16.5
0.0
17
1E+30
0.5
　$D$26合 計(jì) 鈣：%  
3.30
0.00
3.6
1E+30
0.3
　$E$26合 計(jì) 總磷：%  
0.60
0.00
0.7
1E+30
0.1
　$F$26合 計(jì) 有效磷：%  
0.40
-56.11
0.4
0.004550733
0.08
　$G$26合 計(jì) 食鹽：%  
0.38
0.00
0.4
1E+30
0.018
　$H$26合 計(jì) 粗纖維：%  
2.41
0.00
3.5
1E+30
1.087554134


表4：極限值報(bào)告 　　　　
　目標(biāo)式　　　　　
單元格名字值　　　　
$M$26合 計(jì) 配方成本：元/100kg274.5　　　　
　變量　        下限      目標(biāo)式         上限      目標(biāo)式
單元格名字值        極限      結(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進(jìn)口魚粉 配方含量：%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%預(yù)混料 配方含量：%2.00
2.00
274.55
2.00
274.55
【運(yùn)算結(jié)果報(bào)告】
1、初值：規(guī)劃運(yùn)算前預(yù)設(shè)的各原料的含量，可設(shè)定為任意值。
2、終值：最終計(jì)算而得的各原料的含量（即規(guī)劃最優(yōu)解）。
3、數(shù)型值：即求解得到的約束單元格值與約束值之間的差值，取值于"0"和約束值之間。
4、約束單元格的狀態(tài)：當(dāng)約束單元格"達(dá)到極限值"時(shí)，數(shù)型值=0，說(shuō)明已經(jīng)沒(méi)有調(diào)整約束值的必要了；當(dāng)約束單元格"未達(dá)到極限值"時(shí)，數(shù)型值≠0，表明對(duì)約束值進(jìn)行調(diào)整，可以降低配方成本。
【敏感性報(bào)告】 這是規(guī)劃運(yùn)算結(jié)果報(bào)告中最具價(jià)值的部分
1、遞減成本：是指各個(gè)資源條件（即原料）成本的首次變動(dòng)幅度。如果某個(gè)資源條件成本的首次變動(dòng)幅度小于該值，則不會(huì)改變規(guī)劃最優(yōu)解的結(jié)果。在表3中，對(duì)于玉米、豆粕、豆油等原料的遞減成本為0，這表明為了不改變此規(guī)劃求解的結(jié)果，這幾個(gè)原料的價(jià)格最好不要變動(dòng)。但這在實(shí)踐中是不可能的，因此，在敏感性報(bào)告中遞減成本后面給出了"允許的增量" 和 "允許的減量" 。例如，對(duì)玉米而言，雖然它的遞減成本為0，但其"允許的減量"是接近無(wú)窮大的，表明即使玉米的價(jià)格下降也不會(huì)改變規(guī)劃最優(yōu)解結(jié)果。遞減成本是調(diào)整配方含量的依據(jù)。
2、目標(biāo)式系數(shù)：是指相應(yīng)原料的價(jià)格。
3、陰影價(jià)格：是指配方中各約束指標(biāo)每單位約束值得價(jià)格系數(shù)。 陰影價(jià)格是調(diào)整約束值的依據(jù)。陰影價(jià)格越大，表明該約束指標(biāo)對(duì)規(guī)劃求解結(jié)果的影響越明顯。
4、約束單元格"允許的增量"和"允許的減量"：是指在獲得最優(yōu)解的情況下，約束指標(biāo)值的變動(dòng)范圍。在此范圍內(nèi)調(diào)整約束值，對(duì)規(guī)劃最優(yōu)解結(jié)果沒(méi)有影響。
【極限值報(bào)告】
在這個(gè)報(bào)告中給出了需要通過(guò)規(guī)劃求解過(guò)程來(lái)確定的兩類單元格(目標(biāo)單元格和可變單元格)數(shù)值的極限值。
中國(guó)畜牧人網(wǎng)站微信公眾號(hào)
版權(quán)聲明：本文內(nèi)容來(lái)源互聯(lián)網(wǎng)，僅供畜牧人網(wǎng)友學(xué)習(xí)，文章及圖片版權(quán)歸原作者所有，如果有侵犯到您的權(quán)利，請(qǐng)及時(shí)聯(lián)系我們刪除（010-82893169-805）。
您需要登錄后才可以回帖 登錄 | 注冊(cè)

本版積分規(guī)則

發(fā)布主題 快速回復(fù) 返回列表 聯(lián)系我們

關(guān)于社區(qū)|廣告合作|聯(lián)系我們|幫助中心|小黑屋|手機(jī)版| 京公網(wǎng)安備 11010802025824號(hào)

北京宏牧偉業(yè)網(wǎng)絡(luò)科技有限公司 版權(quán)所有(京ICP備11016518號(hào)-1）

Powered by Discuz! X3.5  © 2001-2021 Comsenz Inc. GMT+8, 2025-1-30 12:17, 技術(shù)支持：溫州諸葛云網(wǎng)絡(luò)科技有限公司