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

謝慶華 · 發(fā)表于 2015-10-6 23:22:58

本帖最后由謝慶華于 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
玉米	3220	8	0.02	0.27	0.12	0	1.6	2.2	0.1794	0.1365	0.25	2.46
豆粕43	2390	43	0.33	0.62	0.18	0	5.9	0.51	2.3316	0.5133	1.05	2.7
進(jìn)口魚(yú)粉	2960	66	3.81	2.83	2.83	0.6	0.5	0.2	4.698	1.5732	2.03	13
豆油	8370	0	0	0	0	0	0	53.1	0	0	0	5.6
麥麩	1630	15.5	0.11	0.92	0.24	0	6.8	1.7	0.464	0.1027	0.28	1.9
石粉	0	0	37.6	0	0	0	0	0	0	0	0	0.4
磷酸輕鈣	0	0	21	18	18	0	0	0	0	0	0	1.95
2%預(yù)混料	1332	14.7	0	0	0	18.5	0	0	5	12.5	12.5	10
配方規(guī)劃表
原料名稱	代謝能:KC/kg	粗蛋白：%	鈣：%	總磷：%	有效磷：%	食鹽：%	粗纖維：%	亞油酸：%	賴氨酸：%	蛋氨酸：%	蛋+胱：%	成本：元/100kg	配方含量：%
玉米	2002	5.0	0.01	0.17	0.07	0.00	0.99	1.37	0.11	0.08	0.16	152.92	62.16
豆粕43	542	9.8	0.07	0.14	0.04	0.00	1.34	0.12	0.53	0.12	0.24	61.25	22.69
進(jìn)口魚(yú)粉	59	1.3	0.08	0.06	0.06	0.01	0.01	0.00	0.09	0.03	0.04	26.00	2.00
豆油	104	0.0	0.00	0.00	0.00	0.00	0.00	0.66	0.00	0.00	0.00	6.94	1.24
麥麩	17	0.2	0.00	0.01	0.00	0.00	0.07	0.02	0.00	0.00	0.00	1.94	1.02
石粉	0	0.0	2.87	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3.06	7.64
磷酸輕鈣	0	0.0	0.26	0.23	0.23	0.00	0.00	0.00	0.00	0.00	0.00	2.44	1.25
2%預(yù)混料	27	0.3	0.00	0.00	0.00	0.37	0.00	0.00	0.10	0.25	0.25	20.00	2.00
合計(jì)	2750	16.5	3.30	0.60	0.40	0.38	2.41	2.16	0.84	0.48	0.69	274.55	100.00
飼養(yǎng)標(biāo)準(zhǔn)	2750	16.5	3.3	0.6	0.32	0.35	0	1.1	0.67	0.32	0.58	標(biāo)準(zhǔn)下限
飼養(yǎng)標(biāo)準(zhǔn)	2800	17.0	3.6	0.7	0.4	0.4	3.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ù)→確定→填寫(xiě)以下規(guī)劃求解參數(shù)：
①目標(biāo)單元格：即配方成本最下方的單元格
②選擇最小值：即讓目標(biāo)單元格的值達(dá)到最小；
③填寫(xiě)約束條件：
A、配方含量Xi ≥0， Xi（合計(jì)）=100，X（進(jìn)口魚(yú)粉）=2，X（2%復(fù)合預(yù)混料）=2 ；
B、約束條件設(shè)置：約束下限（飼養(yǎng)標(biāo)準(zhǔn)最低值） ≤ 約束單元格 ≤約束下限（飼養(yǎng)標(biāo)準(zhǔn)最高值），可變單元格中魚(yú)粉、預(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)口魚(yú)粉配方含量：%	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$25	2%預(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)口魚(yú)粉配方含量：%	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$25	2%預(yù)混料配方含量：%	2.00	$N$25>=0	未到限制值	2.00
	$N$20	進(jìn)口魚(yú)粉配方含量：%	2.00	$N$20=2	到達(dá)限制值	0
	$N$25	2%預(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)口魚(yú)粉配方含量：%	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$25	2%預(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ì) 配方成本：元/100kg	274.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)口魚(yú)粉配方含量：%	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$25	2%預(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ù)值的極限值。

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

相關(guān)帖子