利用Excel设计饲料配方

利用Excel设计饲料配方付廷斌甘肃农业大学动物科学技术学院，甘肃兰州（730070）E-mail：futingbin@摘要：本文通过对配方设计原理和Excel“规划求解”的介绍，讲述了如何通过Excel“规划求解”工具设计最低成本配方的具体细节，并分析了Excel做饲料配方的优缺点及解决方案。

关键词：线性规划，规划求解，饲料配方，约束条件1.引言1875年，John Barwell在美国伊利诺斯州沃基根市创建了Blatchford’s全球第一家饲料厂，生产犊牛饲料，它的建立标志着世界饲料工业的开始。

到20世纪20年代，饲料配方设计方法有：对角线法、联立方程法、试差法等等手工方法。

1964年，随着电脑的流行，为了节约饲料生产成本、提高配方设计的效率与准确性，很多饲料厂都已放弃手工配方设计，开始采用电脑设计饲料配方[1]。

电脑配方具有巨大优势，它能全面考虑营养、成本和效益，控制饲料适口性，还可提供大量的参考信息，最重要的是，它节约了大量的人力物力，很大程度上解放了配方师。

如今著名的配方设计软件有国外的Format、Brill、Mixit和国产的Refs、CMIX等。

但这些专业配方软件都价格相对较高，只适合于大型饲料企业，对于中国众多的中小型饲料厂及一些规模养殖场不太适用[2]，只能采用Microsoft Excel的“规划求解”功能设计其配方。

2.线性规划及“规划求解”简介为了提高畜牧生产的经济效益，要求饲料配方既能满足养殖对象的各种需要，又要成本最低。

这一任务已非手工运算所能胜任。

电子计算机的普及及其在畜牧业中的应用，就为实现这一目标提供了可能。

2.1 线性规划简介线性规划是应用数学的方法来解决资源合理调配问题的一个分支[3]，它是通过满足一定的线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值，使预定的目标达到最优。

应用线性规划方法，借助电子计算机计算最低成本饲料配方是近代饲料工业的一项新技术。

其特点是用线性规划方法根据饲料原料特点、价格、所含各种营养物质数量以及饲喂对象对各种营养物质的需要量，用电子计算机计算出配方中各种饲料的用量。

2.2 Excel “规划求解”工具简介“规划求解”是一组命令的组成部分，这些命令有时也称作假设分析工具[4]。

借助“规划求解”，可求得工作表上某个单元格中公式的最优值。

“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。

在创建模型过程中，可以对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格，还可通过更改其他单元格来确定某个单元格的最大值或最小值。

3.利用Microsoft Excel设计饲料配方利用Microsoft Excel设计饲料配方主要包括5个过程，即前期准备、函数的输入、约束条件的确定与输入、规划求解过程、调整与优化过程。

在这5个过程中前期准备是最为关键的过程，只有依靠准确、可信的数据才能利用Excel的“规划求解”功能，设计出理想的有较高经济价值的饲料配方[5]。

3.1 前期准备在进行配方设计之前我们首先应该做以下一些准备：1）确保计算机安装有Excel以及“规划求解”宏；选择“工具”菜单|“加载宏”选项，进入“加载宏”对话框确认是否安装有“规划求解”。

2）准确的饲养标准；更具当地情况选择适当的饲养标准，并适当的划定一定的浮动范围（上线和下线）。

3）饲料成分及营养价值表4）根据本地情况确定饲料原料及价格；避免人畜争粮，在北方不宜选用小麦做饲料原料。

制作界面并将所准备的数据按照“原料在列成分在行价格靠右结果在下”的原则录入，所录入的数据和原数据要一致。

图1 饲料配方结果图3.2 函数的输入函数的出现使人们不再编写公式，能够自动产生结果外,还能使比较复杂的问题简单化，降低工作强度。

在此次配方设计中我们主要牵扯到SUMPRODUCT()函数和SUM()函数，SUMPRODUCT()函数是用来求解相应数据或区域乘积的和，而SUM()函数是用来计算单元格区域中所有数值的和。

在饲料配方任务线性求解中，一般是目标函数(S，即饲料配方成本)取最小值，而待解变量X，即每种饲料在配方中的比例是非负的。

线性规划模型如下：目标函数(S)=C1X1+C2X2+…………CjXj实际配方(B1) = A11X1+A12X2+……A1jXj实际配方(B2) = A21X1+A22X2+……A2jXj………………实际配方(Bi)=Ai1X1+Ai2X2+………AijXj上式中Cj是原料价格(元/kg)，Xj是各种饲料原料所占百分比，S是饲料配方成本。

Aij 是第j种饲料第i种营养成分的含量。

由上面原理可知M16(配方成本)单元格公式为：=M3*K3+M4*K4+M5*K5+M6*K6+M7*K7+M8*K8+M9*K9+M10*K10+M11*K11+M12 *K12+M13*K13可以通过函数写成：=SUMPRODUCT(K3: K13, M3: M13)同理可以求出B16（实际配方B1）单元格公式为：=SUMPRODUCT(B3:B13,$K$3:$K$13) 上式中$K$3:$K$13为绝对引用，在拖动填充时这里固定不变，而B3:B13随着拖动的列数的不同而变化，这样我们就可以通过拖动填充序列至C16：I16单元格中。

通过公式：原料用量合计=各个原料比例之和=1，可以用SUM()函数来计算K16单元格的值。

即K16单元格= =SUM(K3:K13)。

至此，我们所需的函数全部输入完毕。

3.3 约束条件的确定与输入在实际生产中，我们要根据本地的实际情况和经济条件以及饲料的特性决定饲料的使用量，在饲料配方软件中我们就要设置一些约束条件来控制。

约束条件的确定要遵循以下几条原则：1）饲料适口性：有怪味的原料必须限量使用。

2）原料可消化性：消化性不佳的原料应限制用量。

3）毒性原料：必须限定用量，如规定上限用量。

4）在饲料标准中以一个范围规定用量的原料，根据其特性选择取用高限还是低限，如钙可取低限，磷、食盐等也可用低限，而对于粗纤维以及粗灰分应该取高限。

这样取值，是为了利于保证能量和蛋白质等的优先满足。

5）组成饲料配方的原料种类越少越好，在一般情况下，饲料配方中使用的饲料原料种类不宜超过15种。

6）当使用动植物油脂时，因为油脂过多时不宜加工，而且会影响颗粒饲料质量，使配合饲料不易于保存，而是易于霉变。

所以，一般油脂用量应控制在3%以内。

7）饲料配方的总质量一般是按1千克设计，这样有利于使用。

8）尽量少用等号约束，因为等号约束项易于导致无解情况。

如必须要使用，可尝试运用同时使用上限、下限的两端约束。

9）限制大体积原料的用量，否则有可能无法满足畜禽的营养需要。

根据实际情况并参照上面9条原则，将约束条件输入到J3:J13和L3:L13。

比如本次试验采用的是5%的复合预混料，在约束条件中我们就将最小值（Min）和最大值（Max）均设为5%，电脑运算时就强制使用5%计算；由于油脂过多时不宜加工，且不易保存，易于霉变，所以我们要将用量控制在一定的范围之内，我通过设置最小值（Min）为0%，最大值（Max）为1.5%来控制。

如图1所示。

3.4 规划求解在全部完成以上工作后，就可通过规划求解计算最低成本饲料配方了。

具体步骤为，选择“工具”菜单|“规划求解”选项，进入“规划求解参数”对话框，输入相应的参数。

当输入参数完毕后，就会成为图2所示。

图2 规划求解参数在“设置目标单元格”后的框里面输入目标函数所在的单元格。

在本文中为M16。

再进行下一项操作时，“M16”会自动转换为“$M$16”,即由相对引用方式自动转换为绝对引用方式。

以下皆同。

在“等于”项中，选择最小值。

因为目标单元格是表示配方的总成本，成本以最小值为最优解。

在“可变单元格”框中选择可变区域，即$K$3:$K$13。

约束条件通过“添加(A)”按钮添加，主要控制原料用量合计、实际配方与配方上线配方下线以及原料比例与最大值(Max)最小值(Min)的关系。

除此之外，我们还要通过“选项(0)”按钮修改部分参数，选择“采用线性模型(M)”和“假定非负(G)”,点击“确定”后返回到规划求解参数对话框。

图3 “规划求解选项”对话框检查输入的原始数据以及“规划求解参数”等的输入，确定无误后即可以使用“规划求解”工具求解最优饲料配方了。

3.5 调整与优化计算机运算的结果是根据人所输入的参数求得的最低成本的配方，有时是以降低饲料的适口性来换取最低成本，这样的方法在生产中往往不可取。

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

以及当所得到的配方某一种原料用量为零，而又不想让它为零时，可以尝试减少初步优化结果中用量多而且与该原料同类的其它原料的用量，从而使该原料用量大于零，否则，如果直接去约束该原料的用量，有可能致使无解情况的发生。

所以电子计算机配方，只能是最低成本配方而不能称为最佳配方。

鉴于这些原因，在利用计算机配制完配方后，我们还应利用试差发调整与优化饲料配方。

4.Excel设计饲料配方总结利用MS Excel“规划求解”工具来设计饲料配方，大为减轻了设计饲料配方的工作量，明显地提高了设计饲料配方的效率，提高了配方设计的准确性。

而且，利用该方法设计的饲料配方，可以随时方便地进行各种调整。

比如调整饲养标准、原料成分或价格。

和专业饲料配方软件相比Excel设计饲料配方比较简单明了，易于上手，最重要的是它的成本低廉，易于被广大的中小型饲料厂及养殖场接受。

在实际应用中Excel也表现出了它的不足，比如约束条件比较多时将导致无解，以及求得的配方以降低饲料的适口性为代价换取最低成本。

总之，用Excel设计饲料配方具有简单、经济、快速、高效、便利的优点，但也有不可避免的一些缺点，在实际生产中我们还应该根据具体情况做一些后续工作，比如用试差法调整与优化。

参考文献[1]吴金龙.计算机饲料配方技术及常见问题分析.山东饲料,2004,Vol.10:18-24[2]刘光磊,孙健,王宝维.计算机饲料配方技术.山东家禽,2004,Vol.12:38-39[3]韩友文,饲料与饲养学［M］.北京:中国农业出版社,1997[4]郝艳芬,李振宏,李辉.Excel2003 统计与分析［M］.北京:人民邮电出版社,2006[5]韩友文.饲养标准自动生成及用Excel的“规划求解”拟制奶牛饲料配方.饲料博览,2007,Vol.6:22-28Design the Feed Formulation by ExcelFu TingbinGansu Agricultural University, Lanzhou（730070）AbstractIn this paper,we introduced the principle of feed formulation design and the Programming Solve of Excel,and related how to with the Programming Solve of Excel tools design lowest cost feed formulation details，analysis merits and faults of do feed formulation solution.Keywords: Linear Programming, Programming Solve, Feed Formulation, Constraint Condition。