利用Excel设计饲料配方

精品文档 毕 业 论 文 论文题目 利用Excel设计饲料配方学 院 动物科技学院 专 业 生物科学（动物方向） 年 级 四 年 级 姓 名 薛 琳 指导教师 吕 丽 华 职 称 副 教 授 （2008年5月）山西农业大学教务处制摘要在当今饲料行业竞争日益激烈的情况下，多数的饲料企业都已经使用电脑做配方。许多大型的饲料企业以及大规模的养殖场都早已经应用上专业配方软件。但因为专业饲料配方软件价格高昂，对于中小型饲料厂及一些养殖场难以接受。但是,Microsoft Excel却可以利用其“规划求解”功能简单有效地设计出饲料配方。本文即是论述如何运用Excel来设计配方。具体则是讲述了如何为Excel加载“规划求解”工具，如何输入设计饲料配方的原始数据，怎样加入优化配方的约束条件，以及求解最优解满足要求的最低成本饲料配方的方法等等一些问题。关键词：饲料；配方；Excel；规划求解；畜牧业Design the Feedstuff Formulation by ExcelABSTRACT：With the increasingly competition, many feedstuff enterprises have already used computers to design formulation. Many large feedstuff enterprises and large-scaled farms have already used professional formula software. The professional software is too expensive to afford it for the small scaled feed factory and some small farms. However, the Microsoft Excel makes use of Programming Solve function to design a feed formula easily and efficiently. This article studied how to use Excel to design feed formula. It showed how to add Programming Solve tool for the Excel, how to input the original data of feed formula, how to add the restricted conditions for the optimization of formula , and how to get the optimal solution the lowest cost of the animal feed formula that satisfied the needs.Keywords:feed；formulation; Excel; programming solve; husbandry目录1前言11.1饲料配方设计历史11.2用电脑设计配方11.3用Excel设计饲料配方11.4Excel “规划求解”工具简介12利用Microsoft Excel设计饲料配方22.1 Excel“规划求解”的安装22.2运用Excel“规划求解”工具设计饲料配方32.2.1建立饲料配方数据库32.2.2建立饲料配方规划表32.2.3建立合计行52.2.4建立配方营养水平行62.2.5输入规划求解参数62.2.6规划求解选项72.2.7利用“规划求解”工具优化饲料配方82.2.8实验结论93讨论93.1对使用Excel“规划求解”工具设计与优化饲料配方的分析与总结93.1.1 Excel“规划求解”工具设计与优化饲料配方的优点93.1.2 Excel设计最佳饲料配方的关键103.1.3对配方成本进行约束103.1.4约束条件103.2 Excel设计配方的调整及优化103.2.1约束条件的确定103.2.2从不同角度调整配方以求优化配方103.2.3调整高能量原料的用量103.2.4调整两种营养特性相似原料的配比113.3利用Excel设计浓缩饲料配方114结束语114.1Excel设计饲料配方总结114.2对饲料配方软件行业的展望11参考文献13致谢14.精品文档利用Excel设计饲料配方 1前言在当今饲料行业，饲料企业间竞争空前激烈，而这种竞争中又主要是饲料配方的竞争。只有依靠优质的配方，以节约成本，提高性价比，才能使饲料企业生存发展壮大。所以饲料配方设计在饲料公司的生产经营中占了举足轻重的地位。为了节约饲料生产成本、提高配方设计的效率与准确性，很多饲料厂都早已放弃手工配方设计，而采用电脑配方。在大型饲料企业，主要是使用专业的配方设计软件。而其他的中小型饲料企业及一些规模养殖场因为资金问题，则宜于采用Microsoft Excel的“规划求解”功能设计其配方。1.1饲料配方设计历史1875，John Barwell在美国依利诺州建立世界上第一家饲料加工厂。到20世纪20年代，饲料配方设计方法有：对角线法、联立方程法、试差法等等手工方法。1964年，随着电脑的流行，开始有了电脑设计饲料配方1。1.2用电脑设计配方电脑配方具有巨大优势，它能全面考虑营养、成本和效益，还可提供大量的参考信息，最重要的是，它节约了大量的人力物力，大为解放了配方师。如今著名的配方设计软件有国外的Format、Brill、Mixit和国产的Refs、CMIX等。但这些专业配方软件都价格相对较高，对于中国众多的中小型饲料厂及一些规模养殖场不太适用2。1.3用Excel设计饲料配方Microsoft Excel 2000及其后续版本的“规划求解”功能可以很好地解决中小型饲料厂及一些规模养殖场因为价格高昂不愿购买专业配方设计软件问题。线性规划是应用数学中解决资源合理调配问题的一个分支，它是通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值3。Excel“工具”菜单|“加载宏”选项中有“规划求解”一项，可以解决各种线性规划任务。用Excel线性规划对饲料配方任务求解时，不必要使用饲料配方专业软件，仅需要在Excel界面下，通过鼠标或键盘的操作，即可得出饲料配方最低成本的最优解，而且约束条件不受限制，非常适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。1.4Excel “规划求解”工具简介利用MS Excel “规划求解”工具，可对Excel工作表上与目标单元格中的公式有直接或间接联系的一组单元格的数值进行调整，最终为目标单元格中的公式找到优化的结果4。运用MS Excel“规划求解”工具时的一些术语：可变单元格：需要重新确定数值的自变量所在单元格。简言之，就是说规划求解中可修改其数值的单元格。在“规划求解”操作后，最优值就会代替了可变单元格中的初始值5。目标单元格：即公式结果（因变量）所在单元格。配方时，规划求解即是要求解其取预期的最优值时可变单元格的取值5。约束条件：在规划求解配方时根据想要得到的目标配方所提出的一些条件。绝对引用：随着公式的位置变化，所引用单元格位置不变化的一种引用5。2利用Microsoft Excel设计饲料配方“规划求解”是Office2000及其后续版本提供的一个加载宏。宏是MS Office为了用户一些任务自动化而设计的一个功能。2.1 Excel“规划求解”的安装“规划求解”一般安装在“工具”菜单中，显示为“规划求解”选项。如果“工具”中无“规划求解”选项，通常是由于“加载宏”中没选该选项。在这种情形下，可先鼠标依次选择“工具”菜单|“加载宏”选项，出现图1所显示的对话框。在可用“加载宏”选项中选择“规划求解”，如图1所示。然后单击确定，在稍候片刻后一般即可加载成功“规划求解”工具6。如果在图2的可用加载宏中未有“规划求解”选项，说明有可能：1) 这台电脑在安装Office时没选择加载宏的“规划求解”。这时，可以重新运行Office安装文件，选择Excel选项，在加载宏区段中选择“规划求解”，然后重新安装。安装完闭即应该可出现“规划求解”选项7。2) 所用MS Excel是盗版。建议使用正版。图1“加载宏”对话框2.2运用Excel“规划求解”工具设计饲料配方目标：设计一个海兰褐商品种商品蛋鸡产蛋高峰期配合饲料。要求维持产蛋率90%以上，饲养标准应达到代谢能12.14Mj/kg，粗蛋白16.00%，钙3.85%，有效磷0.48%，食盐0.37%8。要求配制100kg的配合饲料。现有7种饲料原料，即玉米、豆粕、麦麸、磷酸氢钙、石粉、食盐和添加剂，价格（元/千克）分别为：1.578、2.70、0.90、3.30、0.25、1.00、55.00,它们的养分含量可从中国饲料数据库查到9。2.2.1建立饲料配方数据库把设计配方所用到的原始数据输入Excel工作表中，输完后即图2所示。图2饲料配方设计的原始数据2.2.2建立饲料配方规划表饲料配方规划表，即“饲料配方优化计算”函数关系表。它是指建立目标单元格、可变单元格和约束条件间的数量对应关系。建立后即为图3所示。其中，配方成本（目标函数）单元格为H23。它是各种原料成本的总和，即H16：H22的加和。各种原料的成本是各自含量与其价格的乘积。各种原料的用量所在单元格B16:B21是可变单元格。而饲料添加剂在配合饲料中的用量一般是固定的，即它是非可变单元格。C16：G21为约束每件。图3饲料配方规划表具体建立步骤为：1) 先在B16：B22中输入一个经验配方。如图3中的配方。它是配方规划方案的初值。2) C16单元格，输入“=B16*C6”，确认。如此，在饲料配方与饲料原料玉米就建立了玉米用量与所提供的能量间的数量对应关系。3) 自动填充C17：C22的代谢能：单击C16，后把光标移动到C16的右下角，等光标变成“+”后，按住鼠标左键向下拖到C22，放开鼠标10。这样，C17：C22就如同C16一样建立了原料用量与所提供的能量之间的数量联系。4) D16：G22的输入，类似于C16：C22输入的过程。都是先把D16、E16、F16和G16中键入相应公式，然后利用Excel的自动填充功能再把D17：D22、E17：E22，F17：F22中自动填充入公式。填充过程如图4所示。图4配方规划表中营养成分自动填充5) H16=B16*B6。H16为要配制的100kg的配合饲料中玉米的成本。输入后，饲料配方与饲料原料玉米即建立了玉米用量与玉米成本之间的数量联系。6) 利用Excel自动填充功能，为饲料配方与各种饲料原料建立原料用量与所需成本间的数量对应关系。即图5。图5填充各种原料的成本2.2.3建立合计行第23行即为合计行。它的每一个单元格都是该单元格上面的各个单元格内数字的总和。具体建立过程：1) 单击B23，再单击编辑栏的插入函数按钮，弹出插入函数对话框。图6“插入函数”对话框2) 选择SUM函数，弹出函数选择对话框，如图7，之后点确定即可。这样，在B23中即显示出饲料配方中各种原料的总和，在此为100(kg)11。图7“函数参数”对话框3) 同上，利用自动填充功能，把C23：H23输入相应的合计公式，如图8。图8“合计行”自动填充2.2.4建立配方营养水平行一般饲料标准给出的都是每千克配合饲料中的各种养分的含量。在此，为便于操作，也把第23行合计数据计算为每单位的配合饲料中的养分的含量。即把23行每个单元格的数据都除以B23内的数字。具体操作为：B24=B23/B23,C24=C23/B23,D24=D23/B23E24=E23/B23,F24=F23/B23,G24=G23/B23,H24=H23/B232.2.5输入规划求解参数在全部完成以上工作后，就可以输入规划求解参数了。具体步骤为，选择“工具”菜单|“规划求解”选项，进入“规划求解参数”对话框。当输入参数完毕后，就会成为图9所示。图9“规划求解参数”对话框对“规划求解参数”对话框的操作如下：1) 在“设置目标单元格”后的框里面输入目标函数所在的单元格。在本文中为H23。再进行下一项操作时，“H23”会自动转换为“$H$23”,即由相对引用方式自动转换为绝对引用方式。以下皆同。2) 在“等于”项中，选择最小值。因为目标单元格是表示配方的总成本，成本以最小值为最优解。3) 在“可变单元格”框中输入可变单元格的名称，即B16：B21。因为添加剂一般是固定量，不参与优化，所以可变单元格不包括B22单元格。4) 在约束选项卡中，单击“添加”按钮。屏幕弹出窗口：“添加约束”对话框。如图10。图10“添加约束”对话框在上述对话框中，依次输入以下各项的约束条件。i. 各种原料的用量都必须大于或等于零：在“单元格引用位置”框中输入“B16：B21”，然后单击符号框旁边的“”，会列出“=”、“=”。然后在“约束值”框中输入0，如图11。再单击“”，然后会自动进入“规划求解参数”对话框的“约束”列表框。图11 添加约束:各种原料的用量都必须大于或等于零ii. 配方总重量约束：类似上述1）的步骤，在“单元格引用位置”框中输入“B23”,在“”的选项中选择“=”，“约束值”框中键入“100”,再单击“”。iii. 饲养标准约束：“单元格引用位置”框中输入“C24：G24”，“”的选项中选择“=”，“约束值”框中录入“B3:F3”,再单击“”。输入完毕后，可检查是否输入正确，如否，可选定有误的约束项，再单击图9中的。屏幕将弹出“改变约束”对话框，在其中即可修改有误的约束项。图9即为约束条件添加完全后“规划求解参数”对话框所显示内容。从中，可以看出，在此配方共有3个约束条件，分别是：各种原料在饲料配方中的用量是非负的，配方总量约束为100，饲料配方各养分水平不能低于营料标准的要求12。2.2.6规划求解选项在图9的规划求解参数对话框中单击，即进入“规划求解选项”对话框。在“规划求解选项”对话框中，选中“采用线性模型”、“假定非负”，如图12。图12“规划求解选项”对话框然后回车，又返回到“规划求解参数”对话框。2.2.7利用“规划求解”工具优化饲料配方检查输入的原始数据以及“规划求解参数”等的输入，确定无误后即可以使用“规划求解”工具求解最优饲料配方了。具体操作较为简单，只需要单击图9“规划求解参数”中的求解按钮即可。随即计算机开始自动运算。稍候片刻，计算机便可以弹出“规划求解结果”对话框，如图13所示。同时，在Excel表中显示出求解结果。如图14所示。图12“规划求解结果”对话框图13规划求解结果在“规划求解结果”对话框中，默认选中“保存规划求解结果”，单击“确定”按钮，就会出现图14结果报告界面。从图14可以看到，利用Excel设计出的配方为下表1所示。而配方成本为229.2456元/100kg,或2.292456元/kg。表1 Excel设计配方结果原料名称饲料配方/kg玉米071262.11533麦麸0.622146豆粕071224.41463石粉9.338211磷酸氢钙2.132139食盐0.377551添加剂1合计1002.2.8实验结论从图14可以看出，所设计的配方的养分含量分别为：代谢能12.14Mj/kg、粗蛋白16%、钙3.85%、有效磷0.48%、食盐0.37%。而要求的饲养标准为：代谢能12.14Mj/kg、粗蛋白16.00%、钙3.85%、有效磷0.48%、食盐0.37%，全部正好达到标准。而且，经过数学方法粗略检验，所设计的配方是在所给出的原料、原料价格条件下，满足饲养标准的最低成本的配方。从这个实验可以看出，利用Excel来设计饲料配方是非常便利的：它只需要在很短的时间内，就能够根据所要求的饲养标准、原料养分和价格数据计算出最佳配方。充分体现出Excel设计饲料配方简单、经济、准确。所以，在中小型饲料企业以及一些规模养殖场中推广使用Excel设计配方是一件很有实际价值的事情，它省去了这些地方购买专业配方软件的资金，省去了他们去人工设计配方的难度，省去了他们购买现成配方的成本。3讨论3.1对使用Excel“规划求解”工具设计与优化饲料配方的分析与总结3.1.1 Excel“规划求解”工具设计与优化饲料配方的优点利用MS Excel“规划求解”工具来优化饲料配方，大为减轻了设计饲料配方的工作量，明显地提高了设计饲料配方的效率，提高了配方设计的准确性。使用Excel“规划求解”工具计算饲料配方极为快速，只要输入了配方的原料品种、价格、配比以及要求的约束条件，Excel可在几乎瞬间给出计算的结果，显示于计算机显示屏上。而且，利用该方法设计的饲料配方，可以随时方便地进行各种调整。比如调整饲养标准、原料成分或价格。可有助于适应市场变化的需要。用MS Excel“规划求解”工具优化饲料配方，与数学手工计算原理无异，但却可以高速高效率进行。反应了科技的强大力量。3.1.2 Excel设计最佳饲料配方的关键利用Excel设计最佳饲料配方的最关键之处不是输入公式求解之类，而是在于制作准确及时的数据库。只有数据库设计合理、准确、可信，才能依靠Excel的规划求解功能，设计出理想的有较高经济价值的饲料配方13。3.1.3对配方成本进行约束比如可以依据饲料生产盈利的目标数额来计算出相应的饲料成本，从而作为一个配方成本约束条件进行规划求解，从而满足饲料公司的利润目标。3.1.4约束条件在“规划求解”中可以最多指定500个约束条件。这一般都可以满足饲料配方设计的需要。3.2 Excel设计配方的调整及优化3.2.1约束条件的确定1) 饲料适口性：有怪味的原料必须限量使用。2) 原料可消化性：消化性不佳的原料应限制用量。3) 毒性原料：必须限定用量，如规定上限用量。4) 在饲料标准中以一个范围规定用量的原料，根据其特性选择取用高限还是低限，如钙可取低限，磷、食盐等也可用低限，而对于粗纤维以及粗灰分应该取高限。这样取值，是为了利于保证能量和蛋白质等的优先满足。5) 组成饲料配方的原料种类越少越好，在一般情况下，饲料配方中使用的饲料原料种类不宜超过15种。6) 当使用动植物油脂时，因为油脂过多时不宜加工，而且会影响颗粒饲料质量，使配合饲料不易于保存，而是易于霉变。所以，一般油脂用量应控制在3%以内。7) 饲料配方的总质量一般是按1千克设计，这样有利于使用。8) 尽量少用等号约束，因为等号约束项易于导致无解情况。如必须要使用，可尝试运用同时使用上限、下限的两端约束。9) 限制大体积原料的用量，否则有可能无法满足畜禽的营养需要。3.2.2从不同角度调整配方以求优化配方当所得到的配方某一种原料用量为零，而又不想让它为零时，可以尝试减少初步优化结果中用量多而且与该原料同类的其它原料的用量，从而使该原料用量大于零，否则，如果直接去约束该原料的用量，有可能致使无解情况的发生。3.2.3调整高能量原料的用量用动植物油脂易于增加能量水平，但用量由于上面讲到的油脂的特性不可以多用。而大量使用玉米生产浓缩料，又不能让养殖户认可。所以，只能是依靠其它的能量水平相对较高的原料，放宽对它们的约束条件。例如，可以尝试提高花生饼等的用量。3.2.4调整两种营养特性相似原料的配比原料营养特性相似情况下，如调整它们的配比合适，可以使它们的优缺点互补，从而提高其饲料性价比。3.3利用Excel设计浓缩饲料配方浓缩饲料即是全价配合饲料扣除能量饲料后的剩余部分。所以，设计浓缩饲料的配方可以有两种办法：1) 先设计出全价配合饲料配方，然后把能量饲料从其中扣除，余下的再折合成百分比含量，即成浓缩料的配方。2) 根据用户能量饲料的特性与数量，确定浓缩料与能量饲料的比例，结合饲料标准确定浓缩料中各种养分应达到的水平，即浓缩料的质量标准，最后根据这个标准设计出浓缩饲料配方。4结束语4.1Excel设计饲料配方总结用Excel设计饲料配方优点：简单、经济、快速、高效、便利。Excel作为目前世界上最常用的办公软件之一，其操作简单流畅，界面美观大方，易于为各种学历人士掌握其基本操作。而使用Excel去设计饲料配方，其操作也并不复杂，与Excel常用操作方法并无多大本质区别。所以，利用Excel设计饲料配方是个大多数人都可以学习掌握的技能。事实上，许多的技术操作都是仅仅是一个熟能生巧的东西只要做得多了，自然就会变得精通，变为专家。对于运用Excel设计饲料配方更是如此，因为这个过程，并无需什么复杂的计算机操作，也没有什么深奥的原理让人必须理解。而对于饲料配方设计中，曾经的最困难部分规划求解最低成本配方，在如今，借助电脑，依赖程序，早已经不必要配方师亲自动手去计算、检查。于是，对于在应用数学中深奥的规划理论，在此，借助电脑的帮助，人脑早已经不必须去理解。配制配方，所要做的，就是输入正确有用的原始数据，然后单击确定，就这么简单，这就是人类现在的处境电脑发达、人脑弱化。从这方面，再次看到了Microsoft Office的功能强大与丰富。4.2对饲料配方软件行业的展望相比较使用专业饲料配方软件，使用Microsoft Excel设计饲料配方比较简单明了，易于上手。而且最重要的是它的成本低廉，易于为广大的中小型饲料厂及养殖场接受。但是未来的饲料行业绝对会是大型饲料企业博弈的场所，而非是当今不计其数的中小型饲料厂混战,致使市场混乱，产品良莠不齐，社会资源巨大浪费。如此，在饲料配方设计上，也必定会是专业配方软件成为市场主流。因为大型饲料企业要在市场上竞争，就必须在配方设计的每一个细节上去小心地进行成本和效果的核算、分析，以求最大限度地控制成本、提高性价比。而这只能依靠专业配方软件的帮助！参考文献1吴金龙.计算机饲料配方技术及常见问题分析.山东饲料,2004,Vol.10:18-242刘光磊,孙健,王宝维.计算机饲料配方技术.山东家禽,2004,Vol.12:38-393晏林.线性规划及其程序设计.文山师范高等专科学校学报,2002,Vol.14,No.1:61-644田玉民,袁晓春,何丽涛.应用Microsoft Excel自动设计饲料配方.湖南饲料,2005,Vol.2:16-205Excel Home Excel应用大全.北京:人民邮电出版社,2008.89-1036徐夕水,邓醒润,陈慧华,邱雅洁.利用Microsoft Excel2002电子表格线性规划奶牛日粮配方示例.中国草食动物,2004,Vol.24,No.1:30-327Gene M.Pesti,Andrew F.Seila.The Use of an Electro