利用Excel线性规划设计饲料配方.docx

利用Excel线性规划设计饲料配方.docx

《利用Excel线性规划设计饲料配方.docx》由会员分享，可在线阅读，更多相关《利用Excel线性规划设计饲料配方.docx（7页珍藏版）》请在冰点文库上搜索。

利用Excel线性规划设计饲料配方

利用Excel线性规划设计饲料配方

利用Excel线性规划设计饲料配方

线性规划,Excel,配方,饲料,设计

利用Excel线性规划设计饲料配方

邓君明（中国海洋大学水产学院）

曾广厅（云南农业大学云南省动物营养与饲料重点实验室）

  摘要：

借助Excel2000“线性规划求解”功能，结合饲料配方设计要求，以线性规划求解建立数学模型，根据动物的饲养标准，能快速、简单地求解饲料配方，在最低成本目标下实现对原料和营养成分的有效调控。

  关键词：

Excel；线性规划；饲料配方

  所谓饲料配方设计，就是应用一定的计算方法，根据原料的营养成分和配方的规格、要求，产生配方中各原料比例的一种运算过程。

线性规划法是目前应用最广泛的一种优化饲料配方技术。

线性规划最低成本配方的优化结果是产生一个满足约束条件的最低成本配方，它受原料的营养成分、约束条件值（配方营养素水平）、原料价格等的影响。

目前，大型专业饲料公司己使用计算机和专用的饲料配方软件进行配方。

但专用的饲料配方软件价格不菲，因此，在小型饲料厂和一般养殖场的应用并不普及。

现在有可能只要拥有安装了Excel2000的电脑就可以通过计算机运用“线性规划求解”求解饲料配方。

利用它的行和列的形式组成区域，在区域内记录数据信息，组成数据库，且可对数据进行统计和分析，获取有效信息。

同时，运用线性规划实现日粮配方，方法简单、快捷、准确、方便。

本文就利用Excel2000线性规划设计饲料配方作一阐述。

  1.饲料配方设计要求

  1.1优化配方设计目标

  动物生产的经济效益主要取决于饲料。

饲料品质的好坏，成本的高低直接影响动物生产的经济效益。

而饲料配方是配合饲料生产的核心，要优化配方设计，必须同时解决以下三个问题：

⑴营养需要问题，由营养学家研究修改制定，满足营养标准就是线性规划求解的主要约束条件之一，即营养性限制；⑵合理组合原料，不同原料的合理搭配，才能满足动物的营养需要，是约束条件之二，即原料性限制；⑶价格最低，在符合条件⑴、⑵的基础上，采用成本最低的原料配比就是求解的目标，即最低成本目标函数。

  1.2数学模型

  优选最佳饲料配方的数学原理是线性规划法，就是求某一目标函数在一定的约束条件下的最大值或最小值。

约束条件和目标函数均可用线性方程组或线性不等式表示。

线性规划最低成本配方优化的数学模型可表示为：

  目标函数：

MinS=C1X1+C2X2+∧+CnXn（求最小值）

  约束条件：

a11x1+a12x2+∧+a1nxn≥b1（或=，≤b1）

          a21x1+a22x2+∧+a2nxn≥b2（或=，≤b2）

          ┇┇┇┇

          am1x1+am2x2+∧+amnxn≥bm（或=，≤bm）

          xj≥0（j=1，2，…，n）

  其中：

x1，x2，∧，xn为决策变量，即各种原料在配方中的数量；

  aij（i=1，2，∧，m；j=1，2，∧，n）为技术系数，即各种原料相应的营养成分；

  b1，b2，∧，bn为约束值，即配方中应满足的各项营养指标或重量指标；

  C1，C2，∧，Cn为成本系数，即每种原料的价格系数；

  m为约束条件个数；

  为配方原料个数。

  1.3饲料配方

  用玉米、麸皮、豆粕、菜籽粕、进口鱼粉、磷酸氢钙、石粉、食盐、赖氨酸、蛋氨酸和预混料11种原料，以产蛋率<65%的蛋鸡为例，各种营养需要为约束条件，计算优化饲料配方。

  查出产蛋率<65%蛋鸡的饲养标准（见表1）与中国饲料成分及营养价值表2002年（第13版）（见表2）。

同时还需要查出当地同期各种饲料原料的市场价格（见表3），并对有些饲料原料的用量作出相应的限定（见表4）。

按图1的样式对应将表1、2、3、4中的内容填入Excel2000单元格内。

表1产蛋率<65%蛋鸡的饲养标准

      代谢能（MJ/kg）粗蛋白（%）钙（%）有效磷（%）赖氨酸（%）蛋氨酸（%）食盐（%）

饲养标准    11.50      14.0  3.2    0.30    0.62    0.31    0.37

表2各种饲料原料营养价值表

饲料成分代谢能（MJ/kg）粗蛋白（%）钙（%）有效磷（%）赖氨酸（%）蛋氨酸（%）

玉米      13.47    7.80  0.02  0.10    0.23    0.15

麸皮      6.82    15.70  0.11  0.30    0.58    0.13

豆粕      9.62    43.00  0.32  0.20    2.45    0.64

菜籽粕      7.41    38.60  0.65  0.33    1.30    0.63

进口鱼粉    12.18    62.50  3.96  3.05    5.12    1.66

磷酸氢钙    0.00    0.00  21.00  16.00    0.00    0.00

石粉      0.00    0.00  35.00  0.00    0.00    0.00

油脂      36.82    0.00  0.00  0.00    0.00    0.00

赖氨酸      0.00    0.00  0.00  0.00    78.80    0.00

蛋氨酸      0.00    0.00  0.00  0.00    0.00    98.00

表3各种饲料原料的市场价格

原料      玉米麸皮豆粕菜籽粕进口鱼粉磷酸氢钙  石粉  食盐  油脂赖氨酸蛋氨酸预混料

价格（元/kg）1.221.082.30  1.20  5.37    1.55  0.12  1.24  8.0017.50  31.50  5.50

表4几种饲料原料的约束条件

      麸皮菜籽粕进口鱼粉油脂  食盐预混料

下限（%）  0    0    2    2  0.3  2

上限（%）10    7    5    -  0.3  2

图1求解饲料配方所用线性模型在表格中的排列样式

图1中B3至M16是aij值；N3至N16是对应于初始值时，线性方程组左侧的值；O3至O16是bi值；B17至M17是Cj值；B18至M18是给定的初始值（初始值均给定为1）；N17是给定初始值时的目标函数值（S），xj在表格中不占用任何单元格。

N3至N17格中的公式是同行左侧B列至M列与B18至M18对应格乘积之和。

如在N3格中输入“=B3*$B$18+C3*$C$18+D3*$D$18+E3*$E$18+F3*$F$18+G3*$G$18+H3*$H$18+I3*$I$18+J3*$J$18+K3*$K$18+L3*$L$18+M3*$M$18”，然后把光标放在N3格右下角位置上，等光标变成“+”时往下拖动至N17上，这样从N3至N17格都输入了相应的公式。

  2.线性规划求解

  往Excel电子表格上输入完数据及公式以后，就可以利用其工具栏中“规划求解”功能开始求解，步骤如下：

  2.1选中“工具”菜单，选中“规划求解”命令，出现“规划求解参数”对话框（如图2）。

  2.2在[设置目标单元格]编辑框中，输入目标单元格的名称$N$17，然后点击[最小值]选项。

  2.3在[可变单元格]中，输入$B$18:

$M$18。

图2“规划求解”对话框

2.4在[约束]窗口中单击添加按钮，产生“添加约束”对话框（如图3），在[单元格引用位置]和[约束值]输入栏中输入约束条件。

如本例在[单元格引用位置]上先输入$N$3:

$N$8；然后选择下拉窗口中的“>=”；接着在[约束值]上输入$O$3:

$O$8，点击添加按钮，继续添加约束条件：

$N$9:

$N$10<=$O$9:

$N$10；$N$11>=$O$11；$N$12<=$O$12；$N$13>=$O$13；$N$14:

$N$15=$O$14:

$O$15。

输完约束条件以后关闭窗口，回到“规划求解参数”窗口。

图3“添加约束”对话框

2.5点击选项按钮，进入“规划求解选项”对话框（如图4），选中“采用线形模型”、“假定非负”、“正切函数”、“向前差分”、“牛顿法”，然后点击确定按钮，回到“规划求解参数”对话框。

  2.6单击求解按钮，开始计算，进入“规划求解结果”对话框（如图5），在该对话框中可以作以下的选择：

  2.6.1保存规划求解的结果；

  2.6.2恢复为原值。

  在本例中选择默认的选项“保存规划求解的结果”，在“报告”中根据需要选定计算结果报告（运算结果报告、敏感性报告、极限值报告）。

需要说明的是：

线性规划的求解结果要么有最优解，要么给出参考配方（无最优解时）。

所谓最优解就是满足所有约束条件（包括营养指标和配比限制条件）的最低成本配方。

参考配方是指最优解不存在时，仍然存在一个最接近理想的配方，它的成本是最低，但是所有的约束条件没有同时满足，但该参考配方仍然具有一定的参考价值，因为该结果往往是可以应用的。

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

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

  2.6.7打印规划求解结果

  通过以上步骤，就可求出一个蛋鸡料的最优配方（不一定饲养效果最佳）。

可见，利用Excel的“规划求解”可以筛选出最优的配方，这样给动物营养设计师们的工作带来极大的方便，既提高了工作效率，又提高了配方质量。

  3.运行结果分析

  3.1运算结果报告

  在此报告中，我们可以看到求得的饲料配方成本、最优配方以及约束条件满足状况，其中“型数值”指求解值与限定值之间的差距，在无法求得最优解时，我们据此可以适当调节约束条件。

通过线性规划求解，从运算结果报告中可知，本例配合饲料价格为1.58元/kg，饲料配方见表5。

表5蛋鸡料饲料配方表

      玉米麸皮豆粕菜籽粕鱼粉磷酸氢钙石粉油脂食盐赖氨酸蛋氨酸预混料

配方（%）66.10.0  11.4  7.0  2.0  0.8    8.2  2.0  0.3  0.0  0.1  2.0

  3.2敏感性报告

  从该报告中我们可以看到以下内容：

  递减成本，表示各个决策变量（饲料原料的添加量）的影子价格，它说明在饲料总量条件不变的情况下，某一种饲料原料用量在最优解的基础上增加1个单位时，目标成本增加的量。

  目标式系数及其允许的增量和减量，指在最优解保持不变的情况下，目标式系数（饲料原料价格）的变化范围，这反映了所获得配方对原料市场价格变化的适应能力。

如果原料价格变化在允许范围内，则不必更改配方。

  阴影价格，指约束条件的影子价格，表示在所获得最优解的基础上，当约束条件每增减1个单位时，所引起目标函数值（饲料最终价格）的增减量。

这可为进一步调整约束条件提供参考。

  约束限制值允许的增量和减量，指在保持最优解和其它条件不变的情况下，各个约束限制值的可变化范围，也就是指在此变化范围内表中所列出的约束条件的影子价格才能够成立。

  3.3权限值报告

  该报告列出了最终的饲料价格，以及保证该价格成立的饲料原料添加量的上下极限。

  4.讨论

  4.1在进行规划求解时，首先要确认在“工具”菜单中出现“规划求解”命令，如果没有则需要安装“规划求解”加载宏，单击“工具”菜单中，单击加载宏命令，如果在“加载宏”命令中，没有列出“规划求解”项，单击浏览，确定驱动器、目录、文件名或运行加载程序，然后在“加载宏”对话框中，选定“规划求解”复选框。

Excel在“规划求解”中使用的算法是由Leonlasdon，（UniversityofTexasatAustin）和AllanWaren（ClevelandStateUniversity）改进的通用非线性规划最佳化代码。

当“规划求解选项”对话框中的“采用线性模型”复选框被选定时，改变可变单元格的初始值不会影响最终数值和求解时间。

在“规划求解”中可以指定500个约束条件，对每一个可变单元格来说，分别有一个上、下限，还可另加100个附加约束条件，应用约束条件的单元格不应多于1000个。

  4.2若规划求解过程中出现无解时，说明所使用的原料要达到约束条件是不可能的，此时要注意分析可能存在的原因：

  4.2.1各营养指标的约束条件是否有冲突，如有些限定了蛋白质水平低，而限定的蛋氨酸水平又高，自相冲突；

  4.2.2原料种类用量的限定与营养指标值之间有冲突。

如粗纤维限定值较小，而糠饼麦麸原料限定用量却较高；

  4.2.3同一原料的上下约束范围过小，将限制营养指标值的达到；

  4.2.4有时可能由于“规划求解参数”选项中的“最长运算时间”、“迭代次数”、“精度”、“允许误差”等取值不当。

  4.3本模板还可对单位价格单元进行约束，表明对客户提出的具体要求从经济角度进行可行性分析。

即最终求解价格有限制要求，若增加该约束时出现无解，说明超过该价饲料厂要亏本生产。

  4.4该方法计算实验动物饲料的常规营养成分具有快速、准确的优点，只要输入配方的原粮品种、价格和配比，该系统将在瞬间一次给出计算结果，直接显示在计算机屏幕上。

当与营养标准相比较后，需要更改调整时，可随时变更配比，立即自动计算，获得新的调整结果。

同时，可根据需要手工调整饲料配方。

  5.小结

  利用Excel电子表格优化饲料配方非常简便、快捷，表中数值的排列灵活多变，可根据用户要求自行设置，且约束条件不受限制（一般情况下），可以使用于各种饲料厂和个人设计饲料配方，尤其对于小型饲料厂而言，采用Excel的“规划求解”方法就可使整个畜牧饲料配方操作变得简单快捷、结果准确、直观且成本较低。

  （参考文献略）