Excel在肉牛日粮配方设计中的应用.doc

Excel在肉牛日粮配方设计中的应用卢建雄 玛依努尔摘 要 本论文通过收集当前市场和养殖企业各种饲料的用量、饲料价格、约束条件等信息, 再针对肉牛营养生理特点、营养指标、饲料特性和饲养标准,充分利用这些实验数据，采用Excel建立的线性规划和目标函数数学模型,通过Excel电子表格的数据共享、规划求解及函数功能，能简单快速地计算出为达到某一营养要求所需要的各种原料的配比及最低成本配方，建立使用Excel设计肉牛饲料配方的体系。关键词 Excel，线性规划，肉牛，日粮配方ABSTRACTIn this paper by collecting current markets and cultivate business of the amount of feed, feed prices, formula derived from the unit cost of product price information, and nutrition physiological characteristics, nutrition indicators, feed characteristics and feeding standards for cattle, make use of these basic data and objectives data, using linear programming and objectives mathematical model by established Excel, through the statements Excel data sharing and Function Solver, fast and simple to calculate the nutritional requirements to achieve a particular need of the kinds of raw material and the lowest cost ratio formula, designed to establish the use of cattle feed formulas Excel system. Key Words : Excel, Linear Programming, Beef Cattle, Feed Formula引 言根据采食习性，将动物分为肉食动物（如狗，狼，虎等），杂食动物（如猪，禽，鼠，猫等）和草食动物（如牛，羊，马，兔等）。草食动物主要依赖植物满足自身的营养需要，并为人类提供肉、奶等生活资料，草食动物中反刍动物肉牛因其特有的复胃结构，可以消化利用大量农作物秸秆等副产品，不与人类争粮食，是我国畜牧业发展的重要方向1。肉牛生产在我国畜牧业经济中占有重要地位，是我国广大牧区、半农半牧区和少数民族地区经济发展的主要支柱之一。近年来随着国家经济的迅速发展和人民生活水平的不断提高以及人们膳食结构的改变，对牛肉产品的需求量越来越大，这大力推动了我国肉牛生产的发展。但是由于历史及地区经济发展不平衡的种种原因，如个体生产性能和饲料转化率比较低, 饲料配制不合理等原因，严重阻碍了西部地区建设小康社会的步伐。为了提高饲料利用率、降低饲养成本、 养殖企业能合理利用本地饲料资源、充分发挥肉牛的生产性能，以求获得肉牛生产的较高经济效益，促进西部地区畜牧业的快速发展 2。本研究的目的就是要设计出既能全面满足肉牛生长发育和充分发挥生产性能所需的各种营养物质的日粮配方计算体系，又要满足生产成本最低的要求,即要求设计出满足每天每头牛能采食足够数量且品质优良的价格最低的各种饲料配方3。因为肉牛饲料配方的质量优劣不仅影响到配合饲料的优劣, 而且关系到养殖企业的经济效益、畜产品质量、畜牧生产水平和人民奔小康的步伐，所以设计科学、合理、经济性能优越的饲料配方已愈来愈受到饲料加工业、养殖业以及国家的关注4。在步入信息时代的今天, 饲料企业只有充分利用配方软件所收集和处理的信息来指导企业的运作和方向,才能处于不败之地。由此可见, 饲料配方软件不仅是企业技术的象征, 更是当今饲料企业的灵魂与核心5。使用Excel设计肉牛饲料配方软件，可以简化计算配方的繁琐过程 ,为饲料工作者实际应用时提供参考。借助于运筹学优化饲料配方技术国内始于1984 年,由张子仪等采用Basic 语言编制的线性规划程序运行于PC1500 型计算机上,由用户输入数据6。随着个人计算机普及应用,到90 年代初国内相继出现若干运行于微机上的通用饲料配方优化软件。这些软件优化手段仍只有线性规划,但是软件已包含了常用的饲料营养成分和饲养标准数据,用户只需修改数据就可计算配方。1992 年由许万根等研制的CMIX 配方系统在国内率先引入了目标规划技术。同时国外配方软件也被引进,如美国的Brill 系统和REFS 系统。这些系统虽然各具特色,但是就优化算法看仍只有线性规划和目标规划两种。由于动物对营养成分的需要量具有一定的不确定性, 2001年艾景军等人对动物饲料配方优化问题进行了模糊线性规划建模，增加了灵敏度分析模型和预混料制作工具，以期找到一种更符合动物生长发育规律的优化手段，使用模糊线性规划可进一步降低饲料配方成本,灵敏度分析技术可为饲料原料采购和产品定位提供决策支持。1 Excel设计日粮配方的原理与数学模型1.1利用Excel线性规划设计日粮配方的原理设计日粮配方的有效方法是线性规划。线性规划的英文是Linear Programming，简称LP，是运筹学的一个重要分支，通俗的解释是指当一个问题可能有一个以上的解时，一定有一个解最好（成本最低，利润最大，安排最合理），即求某一目标函数在一定的约束条件下的最大值或最小值。对求这个解得方法很多，但用得最多的是线性规划，线性规划是把现实经济系统高度概括，抽象所形成的数学模型，是把无限的可能方案精简为对有限方案分析求解的过程，线形规划是把所要解决的问题归结成为控制一组因素，在一组限定条件下，寻求一个函数极值的问题。要控制的因素一般用变量表示，称为决策变量，所要满足的限定条件用变量之间关系的代数式表示，称为约束条件，所要寻求的函数极值用变量函数表示，称为目标函数，其中模型中的约束条件要用线形等式或不等式表示，目标函数要用线形函数表示7 。1.2建立线性规划最低成本配方优化的数学模型采用m种原料设计含n种营养成份的配合饲料配方，假设m种原料中的n种营养成份含量分别为a11,a12,a13,a14,a15,，a1n；a21,a22,a23,a24,a25,，a2n;a31,a32,a33,a34,a35,a3n;；am1,am2,am3,am4,am5,；amn;动物饲养标准中对应的n种营养成份含量分别为b1,b2,b3,b4,b5,，bn；m种原料价格分别为c1,c2,c3,c4,c5,，cm；m种原料用量百分比分别为x1,x2,x3,x4,，xm。则可建立约束条件有： a11x1+a21x2+am1xmb1（或=，b1）a12x1+a22x2+am2xmb2（或=，b2） a1nx1+a2nx2+amnxmbm（或=，bn）xj0（j=1，2， ，m）xj100(j=1，2， ，m)目标函数为：MinZ=C1X1+C2X2+CmXm(求最小值) 其中： x1，x2，xm为决策变量，即各种原料在配方中的用量百分比； aij（i=1，2，m；j=1，2，n）为技术系数，即各种原料中相应的营养成分含量； b1，b2，bn为约束值，即饲养标准中相应的营养成分含量； C1，C2，Cm为成本系数，即各种原料的价格系数； m为配方中原料个数； n为原料中营养成分的种类10 。2 Excel肉牛日粮配方计算系统的设计与结构2.1肉牛日粮配方设计系统的组成2.1.1饲料营养成分表在Excel中建立一个饲料营养成分表，该工作表包括2个部分：肉牛拟用饲料营养成分表和肉牛常用饲料营养成分表。在表下部输入肉牛拟用的各种饲料名称、饲料成分及营养价值；配方计算时，根据当地饲料利用状况及牛的消化生理特点，将表下部合适的饲料种类，复制并粘贴到表上部（本例选用了10种饲料，4种饲料成分，预混料应当始终在表的底部）。表1 拟用饲料营养成分表表2 常用饲料营养成分表2.1.2营养需要量表在Excel工作簿中建立一个营养需要量工作表，该工作表为饲养标准（本文为2004版中华人民共和国农业部肉牛饲养标准）11。表3 营养需要量表2.1.3日粮配方计算表表4 精、粗饲料计算表日粮配方计算表由2部分组成，即粗饲料计算表和精料计算表。可根据牛的实际生产水平和精、粗饲料生产及储贮备情况确定精、粗饲料的种类和用量；按动物营养需要，不能由粗饲料满足的营养部分由精料补充。2.2肉牛日粮配方计算步骤2.2.1最低成本混合精料配方计算往Excel电子表格上输入完以上数据及公式以后，如果安装了规划求解，就可利用其功能开始求解，步骤如下：点击“精料成本（元）”单元格选中菜单栏“工具”项从下拉菜单中选中“规划求解”命令，出现“规划求解参数”对话框(如图1)在设置目标单元格编辑框中输入目标单元格的名称$C$25点击最小值选项在可变单元格编辑框中选择$C$16:$C$23单元格 在约束窗口中单击添加按钮，产生“添加约束”对话框(如图2) 在单元格引用位置和约束值输入栏中输入约束条件。本例在单元格引用位置上先输入$C$16选择下拉窗口中的“=” 在约束值上输入055$C$24点击添加按钮，继续添加约束条件：$C$16:$C$23=0；$C$17=025$C$24；$C$21=005；$C$22=02输完约束条件以后关闭窗口，回到“规划求解参数”窗口。图1“规划求解参数”对话框图2 “添加约束”对话框图3“规划求解选项”对话框点击选项按钮，进入“规划求解选项”对话框(如图3)选中“采用线形模型”点击确定按钮，回到“规划求解参数”对话框单击求解按钮开始计算进入“规划求解结果”对话框(如图4)，在该对话框中可以作以下的选择：保存规划求解的结果和恢复为原值。在本例中选择默认的选项“保存规划求解的结果”，在“报告”中根据需要选定计算结果报告(运算结果报告、敏感性报告、极限值报告)。图4 “规划求解结果”对话框2.3肉牛日粮配方打印和输出通过以上步骤，就可求出一个肉牛饲料的最优配方(不一定饲养效果最佳)。可见，利用Excel的“规划求解”可以筛选出最优的配方，这样给饲料配制者的工作带来极大的方便，既提高了配方质量，又提高了工作效率。需要说明的是：线性规划的求解结果要么有最优解，要么给出参考配方。所谓最优解就是满足所有约束条件的最低成本配方。参考配方是指最优解不存在时，仍然存在一个最接近理想的配方，它的成本是最低，但是所有的约束条件没有同时满足，但该参考配方仍然具有一定的参考价值，因为该结果往往是可以应用的12。3.性能分析3.1性能分析3.1.1运算结果报告在此报告中，我们可以看到求得的饲料配方成本、最优配方以及约束条件满足状况，其中“型数值”指求解值与限定值之间的差距，在无法求得最优解时，我们据此可以适当调节约束条件。通过线性规划求解，从运算结果报告中可知，本例配合饲料价格为元/kg。3.1.2敏感性报告 递减成本，表示各个决策变量(饲料原料的用量)的影子价格，它说明在饲料总量条件不变的情况下，某一种饲料原料用量在最优解的基础上增加1个单位时，目标成本增加的量。目标式系数及其允许的增量和减量，指在最优解保持不变的情况下，目标式系数(饲料原料价格)的变化范围，这反映了所获得配方对原料市场价格变化的适应能力。如果原料价格变化在允许范围内，则不必更改配方。阴影价格，指约束条件的影子价格，表示在所获得最优解的基础上，当约束条件每增减1个单位时，所引起目标函数值(饲料最终价格)的增减量。这可为进一步调整约束条件提供参考。约束限制值允许的增量和减量，指在保持最优解和其它条件不变的情况下，各个约束限制值的可变化范围，也就是指在此变化范围内表中所列出的约束条件的影子价格才能够成立13。5.分析讨论在进行规划求解时，首先要确认在“工具”菜单中出现“规划求解”命令，如果没有则需要安装“规划求解”加载宏，单击“工具”菜单中，单击加载宏命令。如果在“加载宏”命令中，没有列出“规划求解”项，单击浏览，确定驱动器、目录或文件名加载程序，然后在“加载宏”对话框中，选定“规划求解”复选框，即可开始使用“规划求解”命令了。当“规划求解选项”对话框中的“采用线性模型”复选框被选定时，改变可变单元格的初始值是不会影响最终数值和求解时间。在“规划求解”中可以指定500个约束条件，对每一个可变单元格来说，分别有一个上、下限，还可另加100个附加约束条件，应用约束条件的单元格不应多于1000个。5.4若规划求解过程中出现无解时，说明所使用的原料要达到约束条件是不可能的，此时要注意分析可能存在的原因：一.各营养指标的约束条件是否有冲突；二.原料种类用量的限定与营养指标值之间有冲突；三.同一原料的上下约束范围过小，将限制营养指标值的达到；四.有时可能由于“规划求解参数”选项中的“最长运算时间”、“迭代次数”、“精度”、“允许误差”等取值不当。为此，要正确处理无解情况，根据经验合理选择饲料原料种类，并且合理运用饲养标准。目标函数不仅受营养标准、原料配比的限制，还受到肉牛消化生理、货源的限制，这些应尽可能的列入约束条件中，使配方更符合实际需求。用手工法设计和专用计算机软件设计全价日粮配方的优缺点分析肉牛日粮配方设计一般采用手工计算和专用计算机软件计算的方法。手工设计法有交叉法，代数法和试差法等，均较繁琐, 不仅计算量大, 准确率低，尤其不能得到最低成本配方。目前, 大型专业饲料公司已使用专用的配方软件设计日粮配方。但是，专用的饲料配方软件价格不菲, 加大了饲料生产的成本,在西部地区小型饲料厂和一般养殖场中应用并不普及。然而Microsoft Office 办公自动化Excel软件是个人电脑上使用最广泛的软件工具, 其模块Excel 电子表格是功能很强的一种计算工具，只要约束条件设置适当, 利用它行和列组成的区域，在区域内输入数据信息，组成数据库，便可利用求解功能对数据进行统计和分析，获取有效信息，因此，利用Excel计算功能设计肉牛常用日粮配方是完全可能的。它与手工计算相比有明显的优点：（1）设计者不必掌握高深的数学原理，只须懂得基本营养原理即可，全部计算工作由计算机自动完成，一般只需5到15秒（2）各种营养成分含量更接近饲养标准的要求，甚至可以全部等于饲养标准（3）可以在众多配方（成分相同，但是用料比例不同）中，选择成本最低的配方供使用7。因此，运用计算机设计全价日粮配方，可以克服手工法设计配方时指标的局限性，简化设计人员的计算过程，全面合理平衡饲料营养、成本和经济效益的关系，最大限度降低饲料成本