EXCEL规划求解法在猪日粮配方中的应用

马巧娥，闫红军

（杨凌职业技术学院a.信息工程分院；b.动物工程分院，陕西杨凌712100）

饲料是养猪生产的物质基础。在养猪生产中，饲料成本占到生产总成本的70%左右，对养猪企业经济效益具有决定性作用。饲料配方的优劣，不仅对不同生长阶段猪的生长发育和生产潜力的发挥具有重要作用，还决定着猪肉产品的安全和养猪企业的可持续发展。因此，运用科学方法设计猪的日粮配方，使其在满足不同阶段猪饲养标准和营养需要量的前提下，降低生产成本，获得更理想的经济效益，一直是养猪企业研究的热点问题［1－2］。

在实际生产中饲料配方设计方法虽然很多，但是各种不同方法的优缺点不同。对角线法、试差法等传统的手工配方设计计算繁琐，费时费力，结果准确性差，成本不好控制；饲料配方软件虽然可以使计算工作量大幅度下降，准确性提高，也便于控制成本，但价格昂贵，要求使用者必须掌握相关的计算机数据库知识，初学者不易掌握。因此，在养猪企业日粮优化配方设计中找出既计算简便、结果准确同时又符合生产实际、也易于控制成本的方法为大家所关心［3－4］。本文基于计算机办公软件教学研究与应用实际，以EXCEL规划求解功能设计猪日粮配方，为动物饲料配方设计提供应用模板。

1 EXCEL规划求解函数安装

在Microsoft Windows XP操作系统的电脑中安装Microsoft Office System 2003时，选择“自定义安装”中的“全部安装”，且“从本机运行所有程序”，只需从“工具”菜单→“加载宏”命令→“加载宏”对话框中选中“规划求解”复选框，然后单击“确定”按钮，就已经安装好“规划求解”功能了。在操作时，只需要单击“工具”菜单，点击“规划求解”命令就可以使用。

2 构建线性规划求解的数学模型

饲料配方设计问题可表示为线性规划的优化问题，并可用线性规划计算满足营养需要量下的最低成本配方。线性规划最低成本配方优化有下列几个基本的假定：（1）只有一个目标函数，一般情况下是求解配方的最低成本。该目标函数是决策变量的线性函数。（2）决策变量是配方中各原料的用量。营养需要量可转化为决策变量的线性函数，每个线性函数为一个约束条件。所有线性函数构成系统的约束条件集。（3）最优配方是在满足约束条件下的最低成本配方。

3 EXCEL 规划求解法在猪日粮配方中的应用实例

3.1 配方原料选择

按体重20～60kg阶段生长肥育猪营养需要量选择饲料原料，玉米、麸皮、豆粕、菜籽粕、进口鱼粉、4%预混料，要求饲料成本最低。

3.2 查阅饲养标准与各原料营养成分

查阅《中华人民共和国瘦肉型生长肥育猪饲养标准2004》可得生长育肥猪（20～60kg）基础日粮所要求的各种营养指标；查阅《饲料营养成分及价值表》得出所选饲料原料的营养成分；然后把所有的数据及原料的价格录入EXCEL表格。

3.3 建立EXCEL饲料配方工作表

建立EXCEL饲料配方工作表，输入饲养标准、原料营养成分（复合预混料的养分含量查阅产品说明）与价格，见图1。

3.4 设置目标单元格、可变单元格和约束条件

设置初始配方比例都为100%，即将“100.0%”输入表1中的“配方比例”行对应的所有原料单元格。

图1 输入所用各种饲料原料的营养成分Fig.1 Nutrient contents of various feedstuff

计算“合计含量”列各行左侧各单元格的合计数。计算方法为：选中H2 单元格，输入“＝B2＊＄B＄9＋C2＊＄C＄9＋D2＊＄D＄9＋E2＊＄E＄9＋F2＊＄F＄9＋G2＊＄G＄9”，上步结束后，点击H2单元格按住垂直拖至H9［5］。

根据经验设定各原料在配方中比例的上下限和营养指标的上下限。各原料比例的上下限一定要用百分比表示，并按照你所希望配方保留的小数点位数输入小数点后的位数。

点击【工具】菜单下的【规划求解】，弹出【规划求解参数】框，在框内第二行点选【最小值】后，将光标移入第一行【设置目标单元格】右侧空格内，再单击选定最小值目标单元格（本例为“H8”），则刚才【设置目标单元格】右侧空格内即弹出“＄H＄8”。

点击【规划求解参数】对话框第一行“＄H＄8”右侧的小方框，即弹出只有一行的【规划求解参数】框，框内显示“＄H＄8”，再点击右侧的小方框，即回原弹出【规划求解参数】框。

选定可变单元格。先将光标置【可变单元格】下的空框内，点选B9并按住平移至H9，则【可变单元格】下的空框内即自行弹出“＄B＄9：＄H＄9”，即设定配方比例的可变单元格，见图2。

图2 设定可变单元格Fig.2 Setting of variable table cells

设置约束条件。设定各原料的配方比例小于等于配方比例最大值：点击“规划求解参数”对话框中的【添加】按钮即弹出“添加约束”框，光标置“单元格引用位置”下空框内，点选B9单元格并按住平移至H9，则“单元格引用位置”下空框内即弹出“＄B＄9：＄H＄9”，在同行右侧选定“≤”，将光标置“约束值”下框内，点选B11单元格并按住平移至H11，则“约束值”下空框内即弹出“＄B＄11：＄H＄11”，表示引用单元格值小于等于对应的约束单元格值，即＄B＄9：＄H＄9≤＄B＄11：＄H＄11，见图3。

图3 添加约束条件Fig.3 Adding of constraint condition

设定各原料的配方比例大于等于比例最小值：再次点击“规划求解参数”对话框中的【添加】按钮即弹出“添加约束”框，光标置“单元格引用位置”下空框内，点选B9单元格并按住平移至H9，则“单元格引用位置”下空框内即弹出“＄B＄9：＄H＄9”，在同行右侧选定“≥”，将光标置“约束值”下框内，点选B10单元格并按住平移至H10，则“约束值”下空框内即弹出“＄B＄10：＄H＄10”，表示引用单元格值大于等于对应的约束单元格值，即＄B＄9：＄H＄9≥＄B＄10：＄H＄10。

设定配方合计含量小于等于饲养标准要求最大值：第三次点击“规划求解参数”对话框中的【添加】按钮即弹出“添加约束”框，光标置“单元格引用位置”下空框内，点选H2单元格并按住平移至H7，则“单元格引用位置”下空框内即弹出“＄H＄2：＄H＄7”，在同行右侧选定“≤”，将光标置“约束值”下框内，点选K2单元格并按住平移至K7，则“约束值”下空框内即弹出“＄K＄2：＄K＄7”，表示引用单元格值小于等于对应的约束单元格值，即＄H＄2：＄H＄7≤＄K＄2：＄K＄7。

设定配方合计含量大于等于饲养标准要求最小值：第四次点击“规划求解参数”对话框中的【添加】按钮即弹出“添加约束”框，光标置“单元格引用位置”下空框内，点选H2单元格并按住平移至H7，则“单元格引用位置”下空框内即弹出“＄H＄2：＄H＄7”，在同行右侧点选“≥”，将光标置“约束值”下框内，点选J2单元格并按住平移至J7，则“约束值”下空框内即弹出“＄J＄2：＄J＄7”，表示引用单元格值大于等于对应的约束单元格值，即＄H＄2：＄H＄7≥＄J＄2：＄J＄7。

点击“添加约束”对话框中的【确定】按钮后，在第五行"约束"下空白处逐次显示出添加的约束内容，见图4。

图4 显示添加的约束条件Fig.4 Display of constraint condition added

3.5 规划求解

点击“规划求解参数”对话框中的【求解】按钮，弹出“规划求解结果”对话框，选择“保存规划求解结果”，点击【确定】按钮，则EXCEL 表中配方比例行显示出各原料的配方比例，保存结果待用，见图5。

图5 显示求解结果Fig.5 Display of results

3.6 “无解”分析与处理

分析可能出现“无解”的原因主要有：饲料原料营养成分含量之间相互矛盾；动物营养需要量定的过高，而选择的饲料原料品质太差；约束条件过高，而且互相冲突；饲料原料种类过多或过少［6－8］。认真研究配方，必要时作适当调整，以更加适应当地生产和市场的实际情况，更加符合设计目标。

4 小结

应用EXCEL规划求解功能设计饲料配方，不但可以节约购买专用饲料配方软件的费用，并且可以随原料和市场的变化方便地更新数据和约束条件，简化了手工配方复杂、繁重的计算任务，节省时间，可以适应现代小型养猪场和饲料厂的饲料配方要求，具有一定的实用价值和推广价值。该方法需要设计者具有扎实的动物营养专业知识和一定的实践经验。

［1］宋亚攀，王继文，孙丽萍.利用Excel2003"线性规划"程序优化设计饲料配方［J］.饲料研究，2006（1）：62－66.

［2］王学敏，任守文.利用Excel表格自配猪饲料配方［J］.猪业科学，2011（10）：76－78.

［3］张宏福.动物营养参数与饲养标准［M］.北京：中国农业出版社，2010.

［4］刘 琴，陈志远，屈 刚.利用Excel设计饲料配方［J］.当代畜牧，2006（8）：13－15.

［5］黄志文，李当.用EXCEL 进行饲料配方［J］.计算机与农业，2001（1）：24－25.

［6］郑 蕉，涂传清.Excel在多目标规划求解和灵敏度分析中的应用［J］.中国管理信息化：综合版，2006（9）：45－47.

［7］孙爱萍，王瑞梅.如何利用Excel求解线性规划问题及其灵敏度分析［J］.办公自动化，2009（22）：44－51.

［8］龙际飞，赵胜军，刘叶枝，等.利用Excel线性规划设计优化畜禽饲料配方［J］.黑龙江畜牧兽医，2008（2）：55－57.