利用Excel线性规划优化设计山羊饲料配方

(江苏沿江地区农业科学研究所，江苏如皋226541)

设计和采用科学而实用的畜禽饲料配方是合理利用当地饲料资源，提高养殖生产水平，保证畜禽健康，获得较高经济效益的重要保证。饲料配方的设计方法很多，常用的有传统手算法和现代计算机运算法。手算法包括试差法、对角线法和代数法等［1］，往往需要经过反复多次计算才能找到满意结果，且无法得出效益最佳配方。电子计算机运算法包括线性规划法、目标规划法和模糊线性规划法等［2-4］，其中开发最早、运用最普遍的是线性规划法。线性规划法将动物对营养物质的最适需要量和饲料原料的营养成分及价格作为已知条件，将满足动物营养需要及饲料原料控制范围作为约束条件，并将最小饲料成本作为配方设计的目标函数，根据线性规划原理计算出既满足营养要求又价格最低的饲料配方。笔者以青贮玉米秸秆、大豆秸粉、玉米、麦麸、豆粕、豆渣、大豆皮、预混料、磷酸氢钙、食盐等作为饲料来源，以20 kg肉羊日增重0.2 kg育肥为例，阐述利用Excel 2003规划求解功能优化设计饲料配方方法与步骤。

1 材料与方法

1.1 数据收集

1.1.1 查询肉羊饲养标准。依据《中华人民共和国农业行业标准——肉羊饲养标准(NY/T816-2004)》查得体重20 kg日增重0.2 kg育肥山羊每日营养需要量:消化能8.29 MJ，粗蛋白87 g，Ca 8.5 g，P 5.6 g 和食盐 3.8 g。然后，再根据日采食量(0.76 kg/d)计算出各营养组分在育肥山羊饲料中的需求比例消化能10.91 MJ/kg、粗蛋白11.45%，Ca 1.12%，P 0.74%，食盐0.50%。

1.1.2 查询饲料原料营养组成及价格。依据《肉羊饲养标准》中的“中国羊常用饲料成分及营养价值表”以及“中国饲料成分及营养价值表(第24版)”和“羊常用饲料的营养成分［5］”查询各饲料原料的营养组成，并根据各饲料原料市场单价以及干物质含量折算成干物质价格(表3)。

表1 饲料原料营养价值及干物质价格

1.2 电脑设置与操作

1.2.1 设置电脑“规划求解”功能。打开Excel 2003电子表格操作软件，点击菜单栏“工具”，在下拉菜单中选择“加载宏…”，在打开的加载宏复选框中勾选“规划求解”，点“确定”。

1.2.2 输入数据。如图1设置工作表格式，并将育肥山羊饲料营养需求量和表1数据分别输入相应单元格。其中，B4至K18为各种原料相应的营养含量或占比;B19至K19是初始配方重量，均给定为1;B20至K20是各原料的干物质价格;B23至K23为各原料的干物质含量;在N4至N8分别输入相应的营养需求标准。

图1 工作表格式及优化结果

1.2.3 设置约束条件。根据营养需求标准和山羊养殖知识，设置的约束条件下限、标准(固定值)以及上限如图1所示。

1.2.4 输入计算公式。点击“L4”，输入“=B4*$B$21+C4*$C$21+D4*$D$21+E4*$E$21+F4*$F$21+G4*$G$21+H4*$H$21+I4*$I$21+J4*$J$21+K4*$K$21”，点“√”，移动光标至“L4”单元格右下角，当光标形状变成“+”时，拖动光标至“L20”;点击“B22”，输入“=B21*100”，点“√”，拖动“+”字光标至“K22”;同样，点击“B24”，输入“=B22/B23”，点“√”，拖动“+”字光标至“K24”;点击“B25”，输入“=B24/$L$24*100”，点“√”，拖动“+”字光标至“K25”;点击“B26”，输入“=B24*0.76”，点“√”，拖动“+”字光标至“K26”，其中“0.76”为山羊每日干物质采食量;“L21”是同行左侧数据的总和，点击“L21”，再点击工具栏的“∑”，用空“+”字光标选择 B21至 K21区域，点“√”;同样，“L22”、“L24”、“L25”、“L26”均为同行左侧数据的总和，可用相同方法设置。

1.2.5 规划求解。

(1)点击“工具”菜单，选中“规划求解”命令，弹出“规划求解参数”对话框，选择［设置目标单元格］编辑框，点击工作表“L20”作为目标单元格，编辑框自动输入“$L$20”，然后点击［最小值］选项(图2)。

(2)选择［可变单元格］，用鼠标拖选工作表B21至K21区域，自动输入“$B$21:$K$21”。

(3)选择［约束］窗口，点击“添加”按钮，弹出“添加约束”对话框(图3)，点击工作表“L4”，［单元格引用位置］输入栏自动输入“$L$4”，选择下拉窗口中“＞=”，点击工作表“M4”，［约束值］输入栏自动输入“$M$4”，点击“添加”按钮，继续添加约束条件:$L$5＞=$M$5;$L$6＞=$M$6;$L$7＞=$M$7;$L$8=$N$8;$L$9＞=$M$9;$L$9＜=$O$9;$L$10＞=$M$10;$L$10＜=$O$10;$L$11＞=$M$11;$L$11＜=$O$11;$L$12＞=$M$12;$L$12＜=$O$12;$L$13＞=$M$13;$L$13＜=$O$13;$L$14＞=$M$14;$L$14＜=$O$14;$L$15＞=$M$15;$L$15＜=$O$15;$L$16=$N$16;$L$17＞=$M$17;$L$17＜=$O$17;$L$18＞=$M$18;$L$18＜=$O$18;$L$19＞=$M$19。输入完毕，点击“确定”按钮，回到“规划求解参数”对话框。

图2 “规划求解参数”对话框

图3 “添加约束”对话框

(4)点击选项按钮，进入“规划求解选项”对话框(图4)，选中“采用线形模型”、“假定非负”、“正切函数”、“向前差分”、“牛顿法”，其他均取默认值，然后点击“确定”按钮，回到“规划求解参数”对话框。

图4 “规划求解选项”对话框

(5)单击求解按钮，开始计算，当计算结束时弹出“规划求解结果”对话框(图5)，点击“确定”按钮，完成计算。也可在点击“确定”按钮前，在［报告］窗口，选择“运算结果报告”、“敏感性报告”、“极限值报告”，这样计算机会自动在工作表前显示这3个报告(图1)，这些报告对分析配方结果以及进一步调整约束条件具有重要参考价值。

图5 “规划求解结果”对话框

2 结果与分析

根据图1优化结果得到表2。其中，“干物质配方”系剔除含水量后各原料的配方，为理论配方，不能直接应用;“鲜重配方”为饲喂状态下各原料含有水分的配方，可直接应用;“全日粮配制”为每只羊每天所需饲料组合，在具体应用时仅需乘以羊群数量即可配制整个羊群的全日粮。

图1工作表中“L20”为目标单元格，显示“2.34”，表示在该例约束条件及原料市场价格下该理论配方的价格最低，为2.34元/kg。

表2 最优饲料配方

3 讨论

(1)有时由于原料结构不充分以及约束条件过度限制与营养指标不协调等原因，导致电脑无法给出最优解，此时电脑会给出一个近似解，可根据近似解对原料结构或约束条件进行适当调整，再进行计算，直至最终得到最优解。

(2)设置约束条件涉及动物营养专业，需要具备一定的营养学知识或养殖经验。

(3)利用Excel线性规划求解功能不仅适用于优化设计山羊育肥饲料配方，而且适用于其他畜禽饲料配方设计，该方法简便、快捷，结果准确，适合广大养殖专业户应用。

［1］权凯，赵金艳.肉羊养殖实用新技术［M］.北京:金盾出版社，2013:183-186.

［2］邹成义.现代动物饲料配方设计要点［J］.饲料工业，2012，33(17):57-59.

［3］黄汉英，熊先安，魏明新.计算机优化饲料配方软件的研制［J］.粮食与饲料工业，1999(4):1-4.

［4］巴恒星，杨颖，孙瑶，等.应用Lpsolve建立模型在动物饲料配方设计中的研究［J］.饲料工业，2010，31(9):55-57.

［5］王志武，闫益波，李童.肉羊标准化规模养殖技术［M］.北京:中国农业科学技术出版社，2013:122-124.