利用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 规划求解

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