Excell配方設計原理與實踐

謝慶華

Excel飼料配方設計原理與實踐

                                                                          ------謝慶華 【華豐養(yǎng)殖咨詢網】www.huafengyangzhi.com   

一、Excel飼料配方設計的原理

      利用線型規(guī)劃方法進行飼料配方設計，要求飼料配方既能全面滿足養(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   

目標函數（配方成本）：f(x) = c1x1 + c2x2+ c3x3  + ··· + cnxn →最小值，
【注】1、≤ ，= ≥三種關系符號任選其一；
      2、Cj( j = 1,2,3,···n )為各飼料原料的單價；
      3、xi( i = 1,2,3,···n )為決策變量,即配方中各種原料的含量，且
         xj ≥ 0， x1+x2+···+xn = 100% ；
二、Excel設計飼料配方的原理與步驟：
1、設計Exce飼料配方規(guī)劃表格
表1：Excel規(guī)劃設計表

原料數據表	代謝能	粗蛋白	鈣	總磷	有效磷	食鹽	粗纖維	亞油酸	賴氨酸	蛋氨酸	蛋+胱	單價
原料名稱	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
進口魚粉	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%預混料	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
進口魚粉	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%預混料	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
合 計	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)標準	2750	16.5	3.3	0.6	0.32	0.35	0	1.1	0.67	0.32	0.58	標準下限
	2800	17.0	3.6	0.7	0.4	0.4	3.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ī)劃求解參數：
①目標單元格：即配方成本最下方的單元格
②選擇最小值：即讓目標單元格的值達到最?。?
③填寫約束條件：
A、配方含量Xi ≥0， Xi（合計）=100，X（進口魚粉）=2，X（2%復合預混料）=2 ；
B、約束條件設置：約束下限（飼養(yǎng)標準最低值） ≤ 約束單元格 ≤約束下限（飼養(yǎng)標準最高值），可變單元格中魚粉、預混料設為固定值，用"="表示。
3、點“求解”按紐，在右上角選擇運算結果報告、敏感性報告、極限值報告。
4、線性規(guī)劃結果分析：如下表2、表3、表4

表2：運算結果報告
目標單元格 (最小值)
	單元格	名字	初值	終值
	$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$25	2%預混料 配方含量：%	2.00	2.00
約束
	單元格	名字	單元格值	公式	狀態(tài)	型數值
	$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$25	2%預混料 配方含量：%	2.00	$N$25>=0	未到限制值	2.00
	$N$20	進口魚粉 配方含量：%	2.00	$N$20=2	到達限制值	0
	$N$25	2%預混料 配方含量：%	2.00	$N$25=2	未到限制值	0

   

表3：敏感性報告
可變單元格
			終	遞減	目標式	允許的	允許的
	單元格	名字	值	成本	系數	增量	減量
	$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$25	2%預混料 配方含量：%	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	合 計 配方成本：元/100kg	274.5
	變量		下限	目標式	上限	目標式
單元格	名字	值	極限	結果	極限	結果
$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$25	2%預混料 配方含量：%	2.00	2.00	274.55	2.00	274.55

【運算結果報告】
1、初值：規(guī)劃運算前預設的各原料的含量，可設定為任意值。
2、終值：最終計算而得的各原料的含量（即規(guī)劃最優(yōu)解）。
3、數型值：即求解得到的約束單元格值與約束值之間的差值，取值于"0"和約束值之間。
4、約束單元格的狀態(tài)：當約束單元格"達到極限值"時，數型值=0，說明已經沒有調整約束值的必要了；當約束單元格"未達到極限值"時，數型值≠0，表明對約束值進行調整，可以降低配方成本。
【敏感性報告】 這是規(guī)劃運算結果報告中最具價值的部分
1、遞減成本：是指各個資源條件（即原料）成本的首次變動幅度。如果某個資源條件成本的首次變動幅度小于該值，則不會改變規(guī)劃最優(yōu)解的結果。在表3中，對于玉米、豆粕、豆油等原料的遞減成本為0，這表明為了不改變此規(guī)劃求解的結果，這幾個原料的價格最好不要變動。但這在實踐中是不可能的，因此，在敏感性報告中遞減成本后面給出了"允許的增量" 和 "允許的減量" 。例如，對玉米而言，雖然它的遞減成本為0，但其"允許的減量"是接近無窮大的，表明即使玉米的價格下降也不會改變規(guī)劃最優(yōu)解結果。遞減成本是調整配方含量的依據。
2、目標式系數：是指相應原料的價格。
3、陰影價格：是指配方中各約束指標每單位約束值得價格系數。 陰影價格是調整約束值的依據。陰影價格越大，表明該約束指標對規(guī)劃求解結果的影響越明顯。
4、約束單元格"允許的增量"和"允許的減量"：是指在獲得最優(yōu)解的情況下，約束指標值的變動范圍。在此范圍內調整約束值，對規(guī)劃最優(yōu)解結果沒有影響。
【極限值報告】
在這個報告中給出了需要通過規(guī)劃求解過程來確定的兩類單元格(目標單元格和可變單元格)數值的極限值。