《利用Excel设计饲料配方(16页).doc》由会员分享，可在线阅读，更多相关《利用Excel设计饲料配方(16页).doc（16页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、-利用Excel设计饲料配方-第 9 页毕 业 论 文 论文题目 利用Excel设计饲料配方学 院 动物科技学院 专 业 生物科学（动物方向） 年 级 四 年 级 姓 名 薛 琳 指导教师 吕 丽 华 职 称 副 教 授 （2008年5月）山西农业大学教务处制摘要在当今饲料行业竞争日益激烈的情况下，多数的饲料企业都已经使用电脑做配方。许多大型的饲料企业以及大规模的养殖场都早已经应用上专业配方软件。但因为专业饲料配方软件价格高昂，对于中小型饲料厂及一些养殖场难以接受。但是,Microsoft Excel却可以利用其“规划求解”功能简单有效地设计出饲料配方。本文即是论述如何运用Excel来设计配方
2、。具体则是讲述了如何为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 enter
3、prises 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 effi
4、ciently. 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
5、 of the animal feed formula that satisfied the needs.Keywords:feed；formulation; Excel; programming solve; husbandry目录1前言1饲料配方设计历史1用电脑设计配方1用Excel设计饲料配方11.4Excel “规划求解”工具简介12利用Microsoft Excel设计饲料配方22.1 Excel“规划求解”的安装2运用Excel“规划求解”工具设计饲料配方3建立饲料配方数据库3建立饲料配方规划表3建立合计行5建立配方营养水平行6输入规划求解参数6规划求解选项7利用“规划求解”工具优
6、化饲料配方8实验结论93讨论9对使用Excel“规划求解”工具设计与优化饲料配方的分析与总结93.1.1 Excel“规划求解”工具设计与优化饲料配方的优点93.1.2 Excel设计最佳饲料配方的关键10对配方成本进行约束10约束条件10 Excel设计配方的调整及优化10约束条件的确定10从不同角度调整配方以求优化配方103.2.3调整高能量原料的用量10调整两种营养特性相似原料的配比11利用Excel设计浓缩饲料配方114结束语11设计饲料配方总结11对饲料配方软件行业的展望11参考文献13致谢14利用Excel设计饲料配方 1前言在当今饲料行业，饲料企业间竞争空前激烈，而这种竞争中又主
7、要是饲料配方的竞争。只有依靠优质的配方，以节约成本，提高性价比，才能使饲料企业生存发展壮大。所以饲料配方设计在饲料公司的生产经营中占了举足轻重的地位。为了节约饲料生产成本、提高配方设计的效率与准确性，很多饲料厂都早已放弃手工配方设计，而采用电脑配方。在大型饲料企业，主要是使用专业的配方设计软件。而其他的中小型饲料企业及一些规模养殖场因为资金问题，则宜于采用Microsoft Excel的“规划求解”功能设计其配方。1.1饲料配方设计历史1875，John Barwell在美国依利诺州建立世界上第一家饲料加工厂。到20世纪20年代，饲料配方设计方法有：对角线法、联立方程法、试差法等等手工方法。1
8、964年，随着电脑的流行，开始有了电脑设计饲料配方1。1.2用电脑设计配方电脑配方具有巨大优势，它能全面考虑营养、成本和效益，还可提供大量的参考信息，最重要的是，它节约了大量的人力物力，大为解放了配方师。如今著名的配方设计软件有国外的Format、Brill、Mixit和国产的Refs、CMIX等。但这些专业配方软件都价格相对较高，对于中国众多的中小型饲料厂及一些规模养殖场不太适用2。1.3用Excel设计饲料配方Microsoft Excel 2000及其后续版本的“规划求解”功能可以很好地解决中小型饲料厂及一些规模养殖场因为价格高昂不愿购买专业配方设计软件问题。线性规划是应用数学中解决资源
9、合理调配问题的一个分支，它是通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值3。Excel“工具”菜单|“加载宏”选项中有“规划求解”一项，可以解决各种线性规划任务。用Excel线性规划对饲料配方任务求解时，不必要使用饲料配方专业软件，仅需要在Excel界面下，通过鼠标或键盘的操作，即可得出饲料配方最低成本的最优解，而且约束条件不受限制，非常适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。1.4Excel “规划求解”工具简介利用MS Excel “规划求解”工具，可对Excel工作表上与目标单元格中的公式有直接或间接联系的一组单元格的数值进行调整，最终为目标单元格
10、中的公式找到优化的结果4。运用MS Excel“规划求解”工具时的一些术语：可变单元格：需要重新确定数值的自变量所在单元格。简言之，就是说规划求解中可修改其数值的单元格。在“规划求解”操作后，最优值就会代替了可变单元格中的初始值5。目标单元格：即公式结果（因变量）所在单元格。配方时，规划求解即是要求解其取预期的最优值时可变单元格的取值5。约束条件：在规划求解配方时根据想要得到的目标配方所提出的一些条件。绝对引用：随着公式的位置变化，所引用单元格位置不变化的一种引用5。2利用Microsoft Excel设计饲料配方“规划求解”是Office2000及其后续版本提供的一个加载宏。宏是MS Off
11、ice为了用户一些任务自动化而设计的一个功能。2.1 Excel“规划求解”的安装“规划求解”一般安装在“工具”菜单中，显示为“规划求解”选项。如果“工具”中无“规划求解”选项，通常是由于“加载宏”中没选该选项。在这种情形下，可先鼠标依次选择“工具”菜单|“加载宏”选项，出现图1所显示的对话框。在可用“加载宏”选项中选择“规划求解”，如图1所示。然后单击确定，在稍候片刻后一般即可加载成功“规划求解”工具6。如果在图2的可用加载宏中未有“规划求解”选项，说明有可能：1) 这台电脑在安装Office时没选择加载宏的“规划求解”。这时，可以重新运行Office安装文件，选择Excel选项，在加载宏区
12、段中选择“规划求解”，然后重新安装。安装完闭即应该可出现“规划求解”选项7。2) 所用MS Excel是盗版。建议使用正版。图1“加载宏”对话框2.2运用Excel“规划求解”工具设计饲料配方目标：设计一个海兰褐商品种商品蛋鸡产蛋高峰期配合饲料。要求维持产蛋率90%以上，饲养标准应达到代谢能12.14Mj/kg，粗蛋白16.00%，钙3.85%，有效磷0.48%，食盐0.37%8。要求配制100kg的配合饲料。现有7种饲料原料，即玉米、豆粕、麦麸、磷酸氢钙、石粉、食盐和添加剂，,它们的养分含量可从中国饲料数据库查到9。2.2.1建立饲料配方数据库把设计配方所用到的原始数据输入Excel工作表中
13、，输完后即图2所示。图2饲料配方设计的原始数据2.2.2建立饲料配方规划表饲料配方规划表，即“饲料配方优化计算”函数关系表。它是指建立目标单元格、可变单元格和约束条件间的数量对应关系。建立后即为图3所示。其中，配方成本（目标函数）单元格为H23。它是各种原料成本的总和，即H16：H22的加和。各种原料的成本是各自含量与其价格的乘积。各种原料的用量所在单元格B16:B21是可变单元格。而饲料添加剂在配合饲料中的用量一般是固定的，即它是非可变单元格。C16：G21为约束每件。图3饲料配方规划表具体建立步骤为：1) 先在B16：B22中输入一个经验配方。如图3中的配方。它是配方规划方案的初值。2)
14、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配方规划表中营养成
15、分自动填充5) H16=B16*B6。H16为要配制的100kg的配合饲料中玉米的成本。输入后，饲料配方与饲料原料玉米即建立了玉米用量与玉米成本之间的数量联系。6) 利用Excel自动填充功能，为饲料配方与各种饲料原料建立原料用量与所需成本间的数量对应关系。即图5。图5填充各种原料的成本2.2.3建立合计行第23行即为合计行。它的每一个单元格都是该单元格上面的各个单元格内数字的总和。具体建立过程：1) 单击B23，再单击编辑栏的插入函数按钮，弹出插入函数对话框。图6“插入函数”对话框2) 选择SUM函数，弹出函数选择对话框，如图7，之后点确定即可。这样，在B23中即显示出饲料配方中各种原料的总
16、和，在此为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输入规划求解参数在全部完成以上工作后，就可以输入规划求解参
17、数了。具体步骤为，选择“工具”菜单|“规划求解”选项，进入“规划求解参数”对话框。当输入参数完毕后，就会成为图9所示。图9“规划求解参数”对话框对“规划求解参数”对话框的操作如下：1) 在“设置目标单元格”后的框里面输入目标函数所在的单元格。在本文中为H23。再进行下一项操作时，“H23”会自动转换为“$H$23”,即由相对引用方式自动转换为绝对引用方式。以下皆同。2) 在“等于”项中，选择最小值。因为目标单元格是表示配方的总成本，成本以最小值为最优解。3) 在“可变单元格”框中输入可变单元格的名称，即B16：B21。因为添加剂一般是固定量，不参与优化，所以可变单元格不包括B22单元格。4)
18、在约束选项卡中，单击“添加”按钮。屏幕弹出窗口：“添加约束”对话框。如图10。图10“添加约束”对话框在上述对话框中，依次输入以下各项的约束条件。i. 各种原料的用量都必须大于或等于零：在“单元格引用位置”框中输入“B16：B21”，然后单击符号框旁边的“”，会列出“=”、“=”。然后在“约束值”框中输入0，如图11。再单击“”，然后会自动进入“规划求解参数”对话框的“约束”列表框。图11 添加约束:各种原料的用量都必须大于或等于零ii. 配方总重量约束：类似上述1）的步骤，在“单元格引用位置”框中输入“B23”,在“”的选项中选择“=”，“约束值”框中键入“100”,再单击“”。iii. 饲
19、养标准约束：“单元格引用位置”框中输入“C24：G24”，“”的选项中选择“=”，“约束值”框中录入“B3:F3”,再单击“”。输入完毕后，可检查是否输入正确，如否，可选定有误的约束项，再单击图9中的。屏幕将弹出“改变约束”对话框，在其中即可修改有误的约束项。图9即为约束条件添加完全后“规划求解参数”对话框所显示内容。从中，可以看出，在此配方共有3个约束条件，分别是：各种原料在饲料配方中的用量是非负的，配方总量约束为100，饲料配方各养分水平不能低于营料标准的要求12。2.2.6规划求解选项在图9的规划求解参数对话框中单击，即进入“规划求解选项”对话框。在“规划求解选项”对话框中，选中“采用线
20、性模型”、“假定非负”，如图12。图12“规划求解选项”对话框然后回车，又返回到“规划求解参数”对话框。2.2.7利用“规划求解”工具优化饲料配方检查输入的原始数据以及“规划求解参数”等的输入，确定无误后即可以使用“规划求解”工具求解最优饲料配方了。具体操作较为简单，只需要单击图9“规划求解参数”中的求解按钮即可。随即计算机开始自动运算。稍候片刻，计算机便可以弹出“规划求解结果”对话框，如图13所示。同时，在Excel表中显示出求解结果。如图14所示。图12“规划求解结果”对话框图13规划求解结果在“规划求解结果”对话框中，默认选中“保存规划求解结果”，单击“确定”按钮，就会出现图14结果报告
21、界面。从图14可以看到，利用Excel设计出的配方为下表1所示。而配方成本为100kg,或2.292456元/kg。表1 Excel设计配方结果原料名称饲料配方/kg玉米0712麦麸豆粕0712石粉磷酸氢钙食盐添加剂1合计100实验结论从图14、粗蛋白16%、钙3.85%、有效磷0.48%、食盐0.37%。而要求的饲养标准为：、粗蛋白16.00%、钙3.85%、有效磷0.48%、食盐0.37%，全部正好达到标准。而且，经过数学方法粗略检验，所设计的配方是在所给出的原料、原料价格条件下，满足饲养标准的最低成本的配方。从这个实验可以看出，利用Excel来设计饲料配方是非常便利的：它只需要在很短的时
22、间内，就能够根据所要求的饲养标准、原料养分和价格数据计算出最佳配方。充分体现出Excel设计饲料配方简单、经济、准确。所以，在中小型饲料企业以及一些规模养殖场中推广使用Excel设计配方是一件很有实际价值的事情，它省去了这些地方购买专业配方软件的资金，省去了他们去人工设计配方的难度，省去了他们购买现成配方的成本。3讨论对使用Excel“规划求解”工具设计与优化饲料配方的分析与总结3.1.1 Excel“规划求解”工具设计与优化饲料配方的优点利用MS Excel“规划求解”工具来优化饲料配方，大为减轻了设计饲料配方的工作量，明显地提高了设计饲料配方的效率，提高了配方设计的准确性。使用Excel“
23、规划求解”工具计算饲料配方极为快速，只要输入了配方的原料品种、价格、配比以及要求的约束条件，Excel可在几乎瞬间给出计算的结果，显示于计算机显示屏上。而且，利用该方法设计的饲料配方，可以随时方便地进行各种调整。比如调整饲养标准、原料成分或价格。可有助于适应市场变化的需要。用MS Excel“规划求解”工具优化饲料配方，与数学手工计算原理无异，但却可以高速高效率进行。反应了科技的强大力量。3.1.2 Excel设计最佳饲料配方的关键利用Excel设计最佳饲料配方的最关键之处不是输入公式求解之类，而是在于制作准确及时的数据库。只有数据库设计合理、准确、可信，才能依靠Excel的规划求解功能，设计
24、出理想的有较高经济价值的饲料配方13。3.1.3对配方成本进行约束比如可以依据饲料生产盈利的目标数额来计算出相应的饲料成本，从而作为一个配方成本约束条件进行规划求解，从而满足饲料公司的利润目标。3.1.4约束条件在“规划求解”中可以最多指定500个约束条件。这一般都可以满足饲料配方设计的需要。 Excel设计配方的调整及优化3.2.1约束条件的确定1) 饲料适口性：有怪味的原料必须限量使用。2) 原料可消化性：消化性不佳的原料应限制用量。3) 毒性原料：必须限定用量，如规定上限用量。4) 在饲料标准中以一个范围规定用量的原料，根据其特性选择取用高限还是低限，如钙可取低限，磷、食盐等也可用低限，
25、而对于粗纤维以及粗灰分应该取高限。这样取值，是为了利于保证能量和蛋白质等的优先满足。5) 组成饲料配方的原料种类越少越好，在一般情况下，饲料配方中使用的饲料原料种类不宜超过15种。6) 当使用动植物油脂时，因为油脂过多时不宜加工，而且会影响颗粒饲料质量，使配合饲料不易于保存，而是易于霉变。所以，一般油脂用量应控制在3%以内。7) 饲料配方的总质量一般是按1千克设计，这样有利于使用。8) 尽量少用等号约束，因为等号约束项易于导致无解情况。如必须要使用，可尝试运用同时使用上限、下限的两端约束。9) 限制大体积原料的用量，否则有可能无法满足畜禽的营养需要。3.2.2从不同角度调整配方以求优化配方当所
26、得到的配方某一种原料用量为零，而又不想让它为零时，可以尝试减少初步优化结果中用量多而且与该原料同类的其它原料的用量，从而使该原料用量大于零，否则，如果直接去约束该原料的用量，有可能致使无解情况的发生。3.2.3调整高能量原料的用量用动植物油脂易于增加能量水平，但用量由于上面讲到的油脂的特性不可以多用。而大量使用玉米生产浓缩料，又不能让养殖户认可。所以，只能是依靠其它的能量水平相对较高的原料，放宽对它们的约束条件。例如，可以尝试提高花生饼等的用量。3.2.4调整两种营养特性相似原料的配比原料营养特性相似情况下，如调整它们的配比合适，可以使它们的优缺点互补，从而提高其饲料性价比。利用Excel设计
27、浓缩饲料配方浓缩饲料即是全价配合饲料扣除能量饲料后的剩余部分。所以，设计浓缩饲料的配方可以有两种办法：1) 先设计出全价配合饲料配方，然后把能量饲料从其中扣除，余下的再折合成百分比含量，即成浓缩料的配方。2) 根据用户能量饲料的特性与数量，确定浓缩料与能量饲料的比例，结合饲料标准确定浓缩料中各种养分应达到的水平，即浓缩料的质量标准，最后根据这个标准设计出浓缩饲料配方。4结束语4.1Excel设计饲料配方总结用Excel设计饲料配方优点：简单、经济、快速、高效、便利。Excel作为目前世界上最常用的办公软件之一，其操作简单流畅，界面美观大方，易于为各种学历人士掌握其基本操作。而使用Excel去设
28、计饲料配方，其操作也并不复杂，与Excel常用操作方法并无多大本质区别。所以，利用Excel设计饲料配方是个大多数人都可以学习掌握的技能。事实上，许多的技术操作都是仅仅是一个熟能生巧的东西只要做得多了，自然就会变得精通，变为专家。对于运用Excel设计饲料配方更是如此，因为这个过程，并无需什么复杂的计算机操作，也没有什么深奥的原理让人必须理解。而对于饲料配方设计中，曾经的最困难部分规划求解最低成本配方，在如今，借助电脑，依赖程序，早已经不必要配方师亲自动手去计算、检查。于是，对于在应用数学中深奥的规划理论，在此，借助电脑的帮助，人脑早已经不必须去理解。配制配方，所要做的，就是输入正确有用的原始
29、数据，然后单击确定，就这么简单，这就是人类现在的处境电脑发达、人脑弱化。从这方面，再次看到了Microsoft Office的功能强大与丰富。4.2对饲料配方软件行业的展望相比较使用专业饲料配方软件，使用Microsoft Excel设计饲料配方比较简单明了，易于上手。而且最重要的是它的成本低廉，易于为广大的中小型饲料厂及养殖场接受。但是未来的饲料行业绝对会是大型饲料企业博弈的场所，而非是当今不计其数的中小型饲料厂混战,致使市场混乱，产品良莠不齐，社会资源巨大浪费。如此，在饲料配方设计上，也必定会是专业配方软件成为市场主流。因为大型饲料企业要在市场上竞争，就必须在配方设计的每一个细节上去小心地
30、进行成本和效果的核算、分析，以求最大限度地控制成本、提高性价比。而这只能依靠专业配方软件的帮助！参考文献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 -103Microsoft Excel2002电子表格线性规划奶牛日粮配方示
31、例.中国草食动物,2004,Vol.24,No.1:30-327Gene M.Pesti,Andrew F.Seila.The Use of an Electronic Spreadsheet to Solve Linear and Non-Linear Stochastic Feed Formulation Problems.Journal of applied poultry research,1999,Vol.8:110-121-191-26810韩良智.Ex-258-16913韩友文.饲养标准自动生成及用Excel的“规划求解”拟制奶牛饲料配方.饲料博览,2007,Vol.6:22-2
32、8致谢在此，我首先衷心地感谢我的毕业论文指导老师吕丽华老师，是在她的辛勤帮助和耐心指导下，才能有了这篇论文的形成。吕老师尽职尽责、尽心尽力，从始至终，对我论文写作给予了莫大的帮助。在吕老师的指导下，我才选好了我的论文课题利用Excel设计饲料配方，又是在她的热心帮助下，我通过在图书馆查阅书籍、在互联网上浏览网页搜集资料以及下载文档、在学校电子数据库中搜集数据，才找到了论文所需的全部资料与数据，又是在她的耐心指导下，我才通过这些资料与数据最终完成了这篇论文的写作。我还需要感谢学校与学院许多领导及老师的帮助和指点，是他们，让我才有了写作这篇论文的条件、时间与场所，也是通过他们，我才学会了论文的写作
33、方法与方式，也正是在他们四年的辛勤教育下，我才获得了许多的各方面的知识，从而有了写作这篇论文的能力。另外，我还感谢学校科技楼机房的帮助。是通过山西农业大学科技楼机房电脑中的Microsoft Office 2003我才有条件能够顺利完成这篇论文的写作。因为这篇论文的许多实验及数据是通过Microsoft Excel完成与处理的，而这种处理又必须要“规划求解”功能才可以完成。但是在一般的场所，因为版本或版权等的问题，都不可以加载“规划求解”工具，而只有在科技楼机房的Microsoft Excel2003中可以顺利完成这种加载，从而让我做出了全部的实验数据。再次向所有对这篇论文写作提供过帮助的所有人等表示我最诚挚的谢意！此致敬礼薛琳2008-5