利用Excel设计饲料配方.docx

1、利用Excel设计饲料配方利用Excel设计饲料配方毕 业 论 文 论文题目 利用Excel设计饲料配方 学 院 动物科技学院 专 业 生物科学(动物方向) 年 级 四 年 级 姓 名 薛 琳 指导教师 吕 丽 华 职 称 副 教 授 ,2008年5月， 山西农业大学教务处制 摘要 在当今饲料行业竞争日益激烈的情况下多数的饲料企业都已经使用电脑做配方。许多大型的饲料企业以及大规模的养殖场都早已经应用上专业配方软件。但因为专业饲料配方软件价格高昂对于中小型饲料厂及一些养殖场难以接受。但是,Microsoft Excel却可以利用其“规划求解”功能简单有效地设计出饲料配方。本文即是论述如何运用Ex

2、cel来设计配方。具体则是讲述了如何为Excel加载“规划求解”工具如何输入设计饲料配方的原始数据怎样加入优化配方的约束条件以及求解最优解满足要求的最低成本饲料配方的方法等等一些问题。 关键词:饲料,配方,Excel,规划求解,畜牧业 Design the Feedstuff Formulation by Excel ABSTRACT:With the increasingly competition, many feedstuff enterprises have already used computers to design formulation. Many large feedstu

3、ff 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

4、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 low

5、est cost of the animal feed formula that satisfied the needs. Keywords:feed,formulation; Excel; programming solve; husbandry 1前言 . 1 1.1饲料配方设计历史 . 1 1.2用电脑设计配方 . 1 1.3用Excel设计饲料配方 . 1 1.4Excel “规划求解”工具简介 . 1 2利用Microsoft Excel设计饲料配方 . 2 2.1 Excel“规划求解”的安装 . 2 2.2运用Excel“规划求解”工具设计饲料配方. 3 2.2.1建立饲料配方数

6、据库 . 3 2.2.2建立饲料配方规划表 . 3 2.2.3建立合计行 . 5 2.2.4建立配方营养水平行 . 6 2.2.5输入规划求解参数 . 6 2.2.6规划求解选项 . 7 2.2.7利用“规划求解”工具优化饲料配方 . 8 2.2.8实验结论 . 9 3讨论 . 9 3.1对使用Excel“规划求解”工具设计与优化饲料配方的分析与总结 . 9 3.1.1 Excel“规划求解”工具设计与优化饲料配方的优点 . 9 3.1.2 Excel设计最佳饲料配方的关键 . 10 3.1.3对配方成本进行约束 . 10 3.1.4约束条件 . 10 3.2 Excel设计配方的调整及优化

7、. 10 3.2.1约束条件的确定 . 10 3.2.2从不同角度调整配方以求优化配方 . 10 3.2.3调整高能量原料的用量 . 10 3.2.4调整两种营养特性相似原料的配比 .11 3.3利用Excel设计浓缩饲料配方 .11 4结束语 .11 4.1Excel设计饲料配方总结 .11 4.2对饲料配方软件行业的展望 .11 参考文献 . 13 致谢 . 14 薛琳:利用Excel设计饲料配方 利用Excel设计饲料配方 1前言 在当今饲料行业，饲料企业间竞争空前激烈，而这种竞争中又主要是饲料配方的竞争。只有依靠优质的配方，以节约成本，提高性价比，才能使饲料企业生存发展壮大。所以饲料配

8、方设计在饲料公司的生产经营中占了举足轻重的地位。为了节约饲料生产成本、提高配方设计的效率与准确性，很多饲料厂都早已放弃手工配方设计，而采用电脑配方。在大型饲料企业，主要是使用专业的配方设计软件。而其他的中小型饲料企业及一些规模养殖场因为资金问题，则宜于采用Microsoft Excel的“规划求解”功能设计其配方。 1.1饲料配方设计历史 1875，John Barwell在美国依利诺州建立世界上第一家饲料加工厂。到20世纪20年代，饲料配方设计方法有:对角线法、联立方程法、试差法等等手工方法。1964年，随着1电脑的流行，开始有了电脑设计饲料配方。 1.2用电脑设计配方 电脑配方具有巨大优势

9、，它能全面考虑营养、成本和效益，还可提供大量的参考信息，最重要的是，它节约了大量的人力物力，大为解放了配方师。 如今著名的配方设计软件有国外的Format、Brill、Mixit和国产的Refs、CMIX等。但这些专业配方软件都价格相对较高，对于中国众多的中小型饲料厂及一些规模养殖场不太2适用。 1.3用Excel设计饲料配方 Microsoft Excel 2000及其后续版本的“规划求解”功能可以很好地解决中小型饲料厂及一些规模养殖场因为价格高昂不愿购买专业配方设计软件问题。 线性规划是应用数学中解决资源合理调配问题的一个分支，它是通过满足线性等式或3不等式的约束条件来求解线性目标函数的最

10、大值或最小值。 Excel“工具”菜单|“加载宏”选项中有“规划求解”一项，可以解决各种线性规划任务。用Excel线性规划对饲料配方任务求解时，不必要使用饲料配方专业软件，仅需要在Excel界面下，通过鼠标或键盘的操作，即可得出饲料配方最低成本的最优解，而且约束条件不受限制，非常适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。 1.4Excel “规划求解”工具简介 利用MS Excel “规划求解”工具，可对Excel工作表上与目标单元格中的公式有直接或4间接联系的一组单元格的数值进行调整，最终为目标单元格中的公式找到优化的结果。 1 山西农业大学本科毕业论文,设计, 运用MS Ex

11、cel“规划求解”工具时的一些术语: 可变单元格:需要重新确定数值的自变量所在单元格。简言之，就是说规划求解中可5修改其数值的单元格。在“规划求解”操作后，最优值就会代替了可变单元格中的初始值。 目标单元格:即公式结果(因变量)所在单元格。配方时，规划求解即是要求解其取5预期的最优值时可变单元格的取值。 约束条件:在规划求解配方时根据想要得到的目标配方所提出的一些条件。 5绝对引用:随着公式的位置变化，所引用单元格位置不变化的一种引用。 2利用Microsoft Excel设计饲料配方 “规划求解”是Office2000及其后续版本提供的一个加载宏。宏是MS Office为了用户一些任务自动化

12、而设计的一个功能。 2.1 Excel“规划求解”的安装 “规划求解”一般安装在“工具”菜单中，显示为“规划求解”选项。 如果“工具”中无“规划求解”选项，通常是由于“加载宏”中没选该选项。在这种情形下，可先鼠标依次选择“工具”菜单|“加载宏”选项，出现图1所显示的对话框。 在可用“加载宏”选项中选择“规划求解”，如图1所示。然后单击确定，在稍候片刻后6一般即可加载成功“规划求解”工具。 如果在图2的可用加载宏中未有“规划求解”选项，说明有可能: ，可以重新运行Office1) 这台电脑在安装Office时没选择加载宏的“规划求解”。这时安装文件，选择Excel选项，在加载宏区段中选择“规划求

13、解”，然后重新安装。安7装完闭即应该可出现“规划求解”选项。 2) 所用MS Excel是盗版。建议使用正版。 图1“加载宏”对话框 2 薛琳:利用Excel设计饲料配方 2.2运用Excel“规划求解”工具设计饲料配方 目标:设计一个海兰褐商品种商品蛋鸡产蛋高峰期配合饲料。 要求维持产蛋率90%以上，饲养标准应达到代谢能12.14Mj/kg，粗蛋白16.00%，钙83.85%，有效磷0.48%，食盐0.37%。要求配制100kg的配合饲料。 现有7种饲料原料，即玉米、豆粕、麦麸、磷酸氢钙、石粉、食盐和添加剂，价格(元/千克)分别为:1.578、2.70、0.90、3.30、0.25、1.00

14、、55.00,它们的养分含量可从中国9饲料数据库查到。 2.2.1建立饲料配方数据库 把设计配方所用到的原始数据输入Excel工作表中，输完后即图2所示。 图2饲料配方设计的原始数据 2.2.2建立饲料配方规划表 饲料配方规划表，即“饲料配方优化计算”函数关系表。它是指建立目标单元格、可变单元格和约束条件间的数量对应关系。 建立后即为图3所示。 其中，配方成本(目标函数)单元格为H23。它是各种原料成本的总和，即H16:H22的加和。各种原料的成本是各自含量与其价格的乘积。各种原料的用量所在单元格B16:B21是可变单元格。而饲料添加剂在配合饲料中的用量一般是固定的，即它是非可变单元格。C16

15、:G21为约束每件。 3 山西农业大学本科毕业论文,设计, 图3饲料配方规划表 具体建立步骤为: 1) 先在B16:B22中输入一个经验配方。如图3中的配方。它是配方规划方案的初值。 2) C16单元格，输入“=B16*C6”，确认。如此，在饲料配方与饲料原料玉米就建立了玉米用量与所提供的能量间的数量对应关系。 3) 自动填充C17:C22的代谢能:单击C16，后把光标移动到C16的右下角，等光10标变成“+”后，按住鼠标左键向下拖到C22，放开鼠标。 这样，C17:C22就如同C16一样建立了原料用量与所提供的能量之间的数量联系。 4) D16:G22的输入，类似于C16:C22输入的过程。

16、都是先把D16、E16、F16和G16中键入相应公式，然后利用Excel的自动填充功能再把D17:D22、E17:E22，F17:F22中自动填充入公式。填充过程如图4所示。 图4配方规划表中营养成分自动填充 4 薛琳:利用Excel设计饲料配方 5) H16=B16*B6。H16为要配制的100kg的配合饲料中玉米的成本。 输入后，饲料配方与饲料原料玉米即建立了玉米用量与玉米成本之间的数量联系。 6) 利用Excel自动填充功能，为饲料配方与各种饲料原料建立原料用量与所需成本间的数量对应关系。即图5。 图5填充各种原料的成本 2.2.3建立合计行 第23行即为合计行。它的每一个单元格都是该单

17、元格上面的各个单元格内数字的总和。 具体建立过程: 1) 单击B23，再单击编辑栏的插入函数按钮，弹出插入函数对话框。 图6“插入函数”对话框 2) 选择SUM函数，弹出函数选择对话框，如图7，之后点确定即可。这样，在B2311中即显示出饲料配方中各种原料的总和，在此为100(kg)。 图7“函数参数”对话框 5 山西农业大学本科毕业论文,设计, 3) 同上，利用自动填充功能，把C23:H23输入相应的合计公式，如图8。 图8“合计行”自动填充 2.2.4建立配方营养水平行 一般饲料标准给出的都是每千克配合饲料中的各种养分的含量。在此，为便于操作，也把第23行合计数据计算为每单位的配合饲料中的

18、养分的含量。即把23行每个单元格的数据都除以B23内的数字。具体操作为: B24=B23/B23,C24=C23/B23,D24=D23/B23 E24=E23/B23,F24=F23/B23,G24=G23/B23,H24=H23/B23 2.2.5输入规划求解参数 在全部完成以上工作后，就可以输入规划求解参数了。 具体步骤为，选择“工具”菜单|“规划求解”选项，进入“规划求解参数”对话框。当输入参数完毕后，就会成为图9所示。 图9“规划求解参数”对话框 对“规划求解参数”对话框的操作如下: 1) 在“设置目标单元格”后的框里面输入目标函数所在的单元格。在本文中为H23。再进行下一项操作时，

19、“H23”会自动转换为“$H$23”,即由相对引用方式自动转换为绝对引用方式。以下皆同。 2) 在“等于”项中，选择最小值。因为目标单元格是表示配方的总成本，成本以最小值为最优解。 3) 在“可变单元格”框中输入可变单元格的名称，即B16:B21。因为添加剂一般是固定量，不参与优化，所以可变单元格不包括B22单元格。 4) 在约束选项卡中，单击“添加”按钮。屏幕弹出窗口:“添加约束”对话框。如图10。 6 薛琳:利用Excel设计饲料配方 图10“添加约束”对话框 在上述对话框中，依次输入以下各项的约束条件。 i. 各种原料的用量都必须大于或等于零: 在“单元格引用位置”框中输入“B16:B21”，然后单击符号框旁边的“”，会列出“=”、“=”。然后在“约束值”框中输入0，如图11。再单击“”，然后会自动“规划求解参数”对话框的