Excell配方设计原理与实践

谢庆华

Excel饲料配方设计原理与实践

                                                                          ------谢庆华 【华丰养殖咨询网】www.huafengyangzhi.com   

一、Excel饲料配方设计的原理

      利用线型规划方法进行饲料配方设计，要求饲料配方既能全面满足养殖对象的各种营养需要，又要使成本达到最低。Microsoft Office Excel提供了强大的线型规划工具（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饲料配方规划表格
表1：Excel规划设计表

原料数据表	代谢能	粗蛋白	钙	总磷	有效磷	食盐	粗纤维	亚油酸	赖氨酸	蛋氨酸	蛋+胱	单价
原料名称	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
配方规划表
原料名称	代谢能: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
饲养标准	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、配方营养浓度=∑原料的营养浓度×配方中该原料的含量，例如：

代谢能= 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、规划求解：
（1）如果在工具菜单里没找到“规划求解”这一项，请按以下步骤操作：工具→加载宏→规划求解；
（2）点击目标单元格→工具→规划求解→选项：采用线性规划模型、假定非负→确定→填写以下规划求解参数：
①目标单元格：即配方成本最下方的单元格
②选择最小值：即让目标单元格的值达到最小；
③填写约束条件：
A、配方含量Xi ≥0， Xi（合计）=100，X（进口鱼粉）=2，X（2%复合预混料）=2 ；
B、约束条件设置：约束下限（饲养标准最低值） ≤ 约束单元格 ≤约束下限（饲养标准最高值），可变单元格中鱼粉、预混料设为固定值，用"="表示。
3、点“求解”按纽，在右上角选择运算结果报告、敏感性报告、极限值报告。
4、线性规划结果分析：如下表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
约束
	单元格	名字	单元格值	公式	状态	型数值
	$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、初值：规划运算前预设的各原料的含量，可设定为任意值。
2、终值：最终计算而得的各原料的含量（即规划最优解）。
3、数型值：即求解得到的约束单元格值与约束值之间的差值，取值于"0"和约束值之间。
4、约束单元格的状态：当约束单元格"达到极限值"时，数型值=0，说明已经没有调整约束值的必要了；当约束单元格"未达到极限值"时，数型值≠0，表明对约束值进行调整，可以降低配方成本。
【敏感性报告】 这是规划运算结果报告中最具价值的部分
1、递减成本：是指各个资源条件（即原料）成本的首次变动幅度。如果某个资源条件成本的首次变动幅度小于该值，则不会改变规划最优解的结果。在表3中，对于玉米、豆粕、豆油等原料的递减成本为0，这表明为了不改变此规划求解的结果，这几个原料的价格最好不要变动。但这在实践中是不可能的，因此，在敏感性报告中递减成本后面给出了"允许的增量" 和 "允许的减量" 。例如，对玉米而言，虽然它的递减成本为0，但其"允许的减量"是接近无穷大的，表明即使玉米的价格下降也不会改变规划最优解结果。递减成本是调整配方含量的依据。
2、目标式系数：是指相应原料的价格。
3、阴影价格：是指配方中各约束指标每单位约束值得价格系数。 阴影价格是调整约束值的依据。阴影价格越大，表明该约束指标对规划求解结果的影响越明显。
4、约束单元格"允许的增量"和"允许的减量"：是指在获得最优解的情况下，约束指标值的变动范围。在此范围内调整约束值，对规划最优解结果没有影响。
【极限值报告】
在这个报告中给出了需要通过规划求解过程来确定的两类单元格(目标单元格和可变单元格)数值的极限值。