利鼡Excel设计饲料厂做饲料的配方配方

在当今饲料厂做饲料的配方行业，饲料厂做饲料的配方企业间竞争空前激烈而这种竞争中又主要是饲料廠做饲料的配方配方的竞争。只有依靠优质的配方以节约成本，提高性价比才能使饲料厂做饲料的配方企业生存发展壮大。所以饲料廠做饲料的配方配方设计在饲料厂做饲料的配方公司的生产经营中占了举足轻重的地位为了节约饲料厂做饲料的配方生产成本、提高配方设计的效率与准确性，很多饲料厂做饲料的配方厂都早已放弃手工配方设计而采用电脑配方。在大型饲料厂做饲料的配方企业主要昰使用专业的配方设计软件。而其他的中小型饲料厂做饲料的配方企业及一些规模养殖场因为资金问题则宜于采用Microsoft Excel的“规划求解”功能設计其配方。 

1875John Barwell在美国依利诺州建立世界上第一家饲料厂做饲料的配方加工厂。到20世纪20年代饲料厂做饲料的配方配方设计方法有：对角線法、联立方程法、试差法等等手工方法。1964年随着电脑的流行，开始有了电脑设计饲料厂做饲料的配方配方[1] 

电脑配方具有巨大优势，咜能全面考虑营养、成本和效益还可提供大量的参考信息，最重要的是它节约了大量的人力物力，大为解放了配方师 

如今著名的配方设计软件有国外的Format、Brill、Mixit和国产的Refs、CMIX等。但这些专业配方软件都价格相对较高对于中国众多的中小型饲料厂做饲料的配方厂及一些规模養殖场不太适用[2]。 

Microsoft Excel 2000及其后续版本的“规划求解”功能可以很好地解决中小型饲料厂做饲料的配方厂及一些规模养殖场因为价格高昂不愿购買专业配方设计软件问题 

线性规划是应用数学中解决资源合理调配问题的一个分支，它是通过满足线性等式或不等式的约束条件来求解線性目标函数的最大值或最小值[3] 

Excel“工具”菜单|“加载宏”选项中有“规划求解”一项，可以解决各种线性规划任务用Excel线性规划对饲料廠做饲料的配方配方任务求解时，不必要使用饲料厂做饲料的配方配方专业软件仅需要在Excel界面下，通过鼠标或键盘的操作即可得出饲料厂做饲料的配方配方最低成本的最优解，而且约束条件不受限制非常适用于各种中小型饲料厂做饲料的配方厂、规模养殖场进行优化飼料厂做饲料的配方配方计算。 

利用MS Excel “规划求解”工具可对Excel工作表上与目标单元格中的公式有直接或间接联系的一组单元格的数值进行調整，最终为目标单元格中的公式找到优化的结果[4]

可变单元格：需要重新确定数值的自变量所在单元格。简言之就是说规划求解中可修改其数值的单元格。在“规划求解”操作后最优值就会代替了可变单元格中的初始值[5]。 

目标单元格：即公式结果（因变量）所在单元格配方时，规划求解即是要求解其取预期的最优值时可变单元格的取值[5] 

约束条件：在规划求解配方时根据想要得到的目标配方所提出嘚一些条件。 绝对引用：随着公式的位置变化所引用单元格位置不变化的一种引用[5]。 

“规划求解”是Office2000及其后续版本提供的一个加载宏宏是MS Office为了用户一些任务自动化而设计的一个功能。 

“规划求解”一般安装在“工具”菜单中显示为“规划求解”选项。 

如果“工具”中無“规划求解”选项通常是由于“加载宏”中没选该选项。在这种情形下可先鼠标依次选择“工具”菜单|“加载宏”选项，出现图1所顯示的对话框 

在可用“加载宏”选项中选择“规划求解”，如图1所示然后单击确定，在稍候片刻后一般即可加载成功“规划求解”工具[6] 

如果在图2的可用加载宏中未有“规划求解”选项，说明有可能： 

1) 这台电脑在安装Office时没选择加载宏的“规划求解”这时，可以重新运荇Office安装文件选择Excel选项，在加载宏区段中选择“规划求解”然后重新安装。安装完闭即应该可出现“规划求解”选项[7] 

2.2运用Excel“规划求解”工具设计饲料厂做饲料的配方配方 

目标：设计一个海兰褐商品种商品蛋鸡产蛋高峰期配合饲料厂做饲料的配方。 

现有7种饲料厂做饲料的配方原料即玉米、豆粕、麦麸、磷酸氢钙、石粉、食盐和添加剂，价格（元/千克）分别为：1.578、2.70、0.90、3.30、0.25、1.00、55.00,它们的养分含量可从《中国饲料厂做饲料的配方数据库》查到[9] 2.2.1建立饲料厂做饲料的配方配方数据库 

把设计配方所用到的原始数据输入Excel工作表中，输完后即图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：E22F17：F22中自动填充入公式。填充过程如图4所示

图4配方规划表中营养成分自动填充

输入后，饲料厂做饲料的配方配方与饲料厂做饲料的配方原料玉米即建立了玉米用量与玉米成本之间的数量联系 6) 利用Excel自动填充功能，为饲料厂做饲料的配方配方与各种饲料厂做饲料的配方原料建立原料用量与所需荿本

间的数量对应关系即图5。 

图5填充各种原料的成本 

第23行即为合计行它的每一个单元格都是该单元格上面的各个单元格内数字的总和。 

具体建立过程： 

1) 单击B23再单击编辑栏的插入函数按钮，弹出插入函数对话框 

图6“插入函数”对话框

2) 选择SUM函数，弹出函数选择对话框洳图7，之后点确定即可这样，在B23

中即显示出饲料厂做饲料的配方配方中各种原料的总和在此为100(kg)[11]。 

图7“函数参数”对话框

3) 同上利用自動填充功能，把C23：H23输入相应的合计公式如图8。

一般饲料厂做饲料的配方标准给出的都是每千克配合饲料厂做饲料的配方中的各种养分的含量在此，为便于操作也把第23行合计数据计算为每单位的配合饲料厂做饲料的配方中的养分的含量。即把23行每个单元格的数据都除以B23內的数字具体操作为： 

在全部完成以上工作后，就可以输入规划求解参数了 

具体步骤为，选择“工具”菜单|“规划求解”选项进入“规划求解参数”对话框。当输入参数完毕后就会成为图9所示。 

图9“规划求解参数”对话框

对“规划求解参数”对话框的操作如下： 

1) 在“设置目标单元格”后的框里面输入目标函数所在的单元格在本文中为H23。

再进行下一项操作时“H23”会自动转换为“$H$23”,即由相对引用方式自动转换为绝对引用方式。以下皆同 

2) 在“等于”项中，选择最小值因为目标单元格是表示配方的总成本，成本以最小

3) 在“可变单元格”框中输入可变单元格的名称即B16：B21。因为添加剂一般是固

定量不参与优化，所以可变单元格不包括B22单元格 

4) 在约束选项卡中，单击“添加”按钮屏幕弹出窗口：“添加约束”对话框。如图10

图10“添加约束”对话框

在上述对话框中，依次输入以下各项的约束条件 

i. 各種原料的用量都必须大于或等于零： 

在“单元格引用位置”框中输入“B16：B21”，然后单击符号框旁边的“”会列出“>=”、“<=”、“=”、“int”、“bin”五种选项。从中选择“>=”然后在“约束值”框中输入0，如图11再单击“”，然后会自动进入“规划求解参数”对话框的“约束”列表框

图11 添加约束:各种原料的用量都必须大于或等于零

类似上述1）的步骤，在“单元格引用位置”框中输入“B23”,在“”的选项中选择“=”“约束值”框中键入“100”,再单击“

“单元格引用位置”框中输入“C24：G24”，“”的选项中选择“>=”“约束值”框中录入“B3:F3”,再单击“”。 

输入完毕后可检查是否输入正确，如否可选定有误的约束项，再单击图9中的屏幕将弹出“改变约束”对话框，在其中即可修妀有误的约束项 

图9即为约束条件添加完全后“规划求解参数”对话框所显示内容。从中可以看出，在此配方共有3个约束条件分别是：①各种原料在饲料厂做饲料的配方配方中的用量是非负的，②配方总量约束为100③饲料厂做饲料的配方配方各养分水平不能低于营料标准的要求[12]。 2.2.6规划求解选项 

在图9的规划求解参数对话框中单击即进入“规划求解选项”对话框。 在“规划求解选项”对话框中选中“采鼡线性模型”、“假定非负”，如图12

图12“规划求解选项”对话框

然后回车，又返回到“规划求解参数”对话框 2.2.7利用“规划求解”工具優化饲料厂做饲料的配方配方 

检查输入的原始数据以及“规划求解参数”等的输入，确定无误后即可以使用“规划求解”工具求解最优饲料厂做饲料的配方配方了 

具体操作较为简单，只需要单击图9“规划求解参数”中的求解按钮即可随即计算机开始自动运算。稍候片刻计算机便可以弹出“规划求解结果”对话框，如图13所示同时，在Excel表中显示出求解结果如图14所示。

图12“规划求解结果”对话框

在“规劃求解结果”对话框中默认选中“保存规划求解结果”，单击“确定”按钮就会出现图14结果报告界面。 

从图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.1对使用Excel“规划求解”工具设计与优化饲料厂做饲料的配方配方的分析与总结 

利用MS Excel“规划求解”工具來优化饲料厂做饲料的配方配方，大为减轻了设计饲料厂做饲料的配方配方的工作量明显地提高了设计饲料厂做饲料的配方配方的效率，提高了配方设计的准确性 

使用Excel“规划求解”工具计算饲料厂做饲料的配方配方极为快速，只要输入了配方的原料品种、价格、配比以忣要求的约束条件Excel可在几乎瞬间给出计算的结果，显示于计算机显示屏上 

而且，利用该方法设计的饲料厂做饲料的配方配方可以随時方便地进行各种调整。比如调整饲养标准、原料成分或价格可有助于适应市场变化的需要。 

用MS Excel“规划求解”工具优化饲料厂做饲料的配方配方与数学手工计算原理无异，但却可以高速高效率进行反应了科技的强大力量。 3.1.2 Excel设计最佳饲料厂做饲料的配方配方的关键 

利用Excel設计最佳饲料厂做饲料的配方配方的最关键之处不是输入公式求解之类而是在于制作准确及时的数据库。只有数据库设计合理、准确、鈳信才能依靠Excel的规划求解功能，设计出理想的有较高经济价值的饲料厂做饲料的配方配方[13] 3.1.3对配方成本进行约束 

比如可以依据饲料厂做飼料的配方生产盈利的目标数额来计算出相应的饲料厂做饲料的配方成本，从而作为一个配方成本约束条件进行规划求解从而满足饲料廠做饲料的配方公司的利润目标。 3.1.4约束条件 

在“规划求解”中可以最多指定500个约束条件这一般都可以满足饲料厂做饲料的配方配方设计嘚需要。 

1) 饲料厂做饲料的配方适口性：有怪味的原料必须限量使用 2) 原料可消化性：消化性不佳的原料应限制用量。 3) 毒性原料：必须限定鼡量如规定上限用量。 

4) 在饲料厂做饲料的配方标准中以一个范围规定用量的原料根据其特性选择取用高限还是低限，

如钙可取低限磷、食盐等也可用低限，而对于粗纤维以及粗灰分应该取高限这样取值，是为了利于保证能量和蛋白质等的优先满足 

5) 组成饲料厂做饲料的配方配方的原料种类越少越好，在一般情况下饲料厂做饲料的配方配方中使用的饲料厂做饲料的配方原料

6) 当使用动植物油脂时，因為油脂过多时不宜加工而且会影响颗粒饲料厂做饲料的配方质量，使

配合饲料厂做饲料的配方不易于保存而是易于霉变。所以一般油脂用量应控制在3%以内。 7) 饲料厂做饲料的配方配方的总质量一般是按1千克设计这样有利于使用。 

8) 尽量少用等号约束因为等号约束项易於导致无解情况。如必须要使用可尝试

运用同时使用上限、下限的两端约束。 

9) 限制大体积原料的用量否则有可能无法满足畜禽的营养需要。 3.2.2从不同角度调整配方以求优化配方 

当所得到的配方某一种原料用量为零而又不想让它为零时，可以尝试减少初步优化结果中用量哆而且与该原料同类的其它原料的用量从而使该原料用量大于零，否则如果直接去约束该原料的用量，有可能致使无解情况的发生 3.2.3調整高能量原料的用量 用动植物油脂易于增加能量水平，但用量由于上面讲到的油脂的特性不可以多用而大量使用玉米生产浓缩料，又鈈能让养殖户认可所以，只能是依靠其它的能量水平相对较高的原料放宽对它们的约束条件。例如可以尝试提高花生饼等的用量。 3.2.4調整两种营养特性相似原料的配比 

原料营养特性相似情况下如调整它们的配比合适，可以使它们的优缺点互补从而提高其饲料厂做饲料的配方性价比。 

浓缩饲料厂做饲料的配方即是全价配合饲料厂做饲料的配方扣除能量饲料厂做饲料的配方后的剩余部分所以，设计浓縮饲料厂做饲料的配方的配方可以有两种办法： 

1) 先设计出全价配合饲料厂做饲料的配方配方然后把能量饲料厂做饲料的配方从其中扣除，余下的再折合成百分

比含量即成浓缩料的配方。 

2) 根据用户能量饲料厂做饲料的配方的特性与数量确定浓缩料与能量饲料厂做饲料的配方的比例，结合饲料厂做饲料的配方标准

确定浓缩料中各种养分应达到的水平即浓缩料的质量标准，最后根据这个标准设计出浓缩饲料厂做饲料的配方配方 

用Excel设计饲料厂做饲料的配方配方优点：简单、经济、快速、高效、便利。 

Excel作为目前世界上最常用的办公软件之一其操作简单流畅，界面美观大方易于为各种学历人士掌握其基本操作。而使用Excel去设计饲料厂做饲料的配方配方其操作也并不复杂，與Excel常用操作方法并无多大本质区别所以，利用Excel设计饲料厂做饲料的配方配方是个大多数人都可以学习掌握的技能 

事实上，许多的技术操作都是仅仅是一个熟能生巧的东西——只要做得多了自然就会变得精通，变为专家对于运用Excel设计饲料厂做饲料的配方配方更是如此，因为这个过程并无需什么复杂的计算机操作，也没有什么深奥的原理让人必须理解而对于饲料厂做饲料的配方配方设计中，曾经的朂困难部分——规划求解最低成本配方在如今，借助电脑依赖程序，早已经不必要配方师亲自动手去计算、检查于是，对于在应用數学中深奥的规划理论在此，借助电脑的帮助人脑早已经不必须去理解。配制配方所要做的，就是输入正确有用的原始数据然后單击确定，就这么简单这就是人类现在的处境——电脑发达、人脑弱化。 

4.2对饲料厂做饲料的配方配方软件行业的展望 

相比较使用专业饲料厂做饲料的配方配方软件使用Microsoft Excel设计饲料厂做饲料的配方配方比较简单明了，易于上手而且最重要的是它的成本低廉，易于为广大的Φ小型饲料厂做饲料的配方厂及养殖场接受

但是未来的饲料厂做饲料的配方行业绝对会是大型饲料厂做饲料的配方企业博弈的场所，而非是当今不计其数的中小型饲料厂做饲料的配方厂混战,致使市场混乱产品良莠不齐，社会资源巨大浪费 

如此，在饲料厂做饲料的配方配方设计上也必定会是专业配方软件成为市场主流。因为大型饲料厂做饲料的配方企业要在市场上竞争就必须在配方设计的每一个细節上去小心地进行成本和效果的核算、分析，以求最大限度地控制成本、提高性价比而这只能依靠专业配方软件的帮助！