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

1、利用 Excel 线性规划设计饲料配方线性规划 , Excel, 配方, 饲料, 设计利用 Excel 线性规划设计饲料配方邓君明 （中国海洋大学水产学院）曾广厅 （ 云南农业大学云南省动物营养与饲料重点实验室）摘 要：借助 Excel 2000 “线性规划求解”功能，结合饲料配方设计要求，以线性规划 求解建立数学模型，根据动物的饲养标准，能快速、简单地求解饲料配方，在最低成本目 标下实现对原料和营养成分的有效调控。关键词： Excel ；线性规划；饲料配方所谓饲料配方设计，就是应用一定的计算方法，根据原料的营养成分和配方的规格、要 求，产生配方中各原料比例的一种运算过程。线性规划法是目前应用

2、最广泛的一种优化饲 料配方技术。线性规划最低成本配方的优化结果是产生一个满足约束条件的最低成本配方， 它受原料的营养成分、约束条件值 （配方营养素水平 ）、原料价格等的影响。目前，大型专业 饲料公司己使用计算机和专用的饲料配方软件进行配方。但专用的饲料配方软件价格不菲， 因此，在小型饲料厂和一般养殖场的应用并不普及。 现在有可能只要拥有安装了 Excel 2000Excel的电脑就可以通过计算机运用“线性规划求解”求解饲料配方。利用它的行和列的形式组 成区域，在区域内记录数据信息，组成数据库，且可对数据进行统计和分析，获取有效信 息。同时，运用线性规划实现日粮配方，方法简单、快捷、准确、方便。

3、本文就利用 2000 线性规划设计饲料配方作一阐述。1. 饲料配方设计要求1.1 优化配方设计目标动物生产的经济效益主要取决于饲料。饲料品质的好坏，成本的高低直接影响动物生产 的经济效益。而饲料配方是配合饲料生产的核心，要优化配方设计，必须同时解决以下三 个问题：营养需要问题，由营养学家研究修改制定，满足营养标准就是线性规划求解的 主要约束条件之一，即营养性限制；合理组合原料，不同原料的合理搭配，才能满足动 物的营养需要，是约束条件之二，即原料性限制；价格最低，在符合条件、的基础 上，采用成本最低的原料配比就是求解的目标，即最低成本目标函数。1.2 数学模型优选最佳饲料配方的数学原理是线性规划

4、法，就是求某一目标函数在一定的约束条件下 的最大值或最小值。约束条件和目标函数均可用线性方程组或线性不等式表示。线性规划 最低成本配方优化的数学模型可表示为：目标函数：Min S=C1X1+C2X2+A+CnXn（求最小值）约束 条件：a11x1+a12x2+ A+a1 nxn >b1 （或=,wb1 ）a21x1+a22x2+ A+a2nxn >b2 （或=,wb2 ）IlliIlliIlliam1x1+am2x2+A+amnxn >bm （或=,<bm ）xj X) (j=1 , 2,，n)其中：x1 , x2 , A, xn为决策变量，即各种原料在配方中的数量；a

5、ij ( i=1， 2,A, m ； j=1，2，A，n)为技术系数，即各种原料相应的营养成分；b1 ， b2 ，A， bn 为约束值，即配方中应满足的各项营养指标或重量指标；C1 ， C2，A， Cn 为成本系数，即每种原料的价格系数；m 为约束条件个数；为配方原料个数。1.3 饲料配方 用玉米、麸皮、豆粕、菜籽粕、进口鱼粉、磷酸氢钙、石粉、食盐、赖氨酸、蛋氨酸和 预混料 11 种原料，以产蛋率 <65% 的蛋鸡为例，各种营养需要为约束条件，计算优化饲料 配方。查出产蛋率 <65% 蛋鸡的饲养标准 (见表 1)与中国饲料成分及营养价值表2002 年(第 13版)(见表 2)。同时

6、还需要查出当地同期各种饲料原料的市场价格 (见表 3)，并对有些饲料原 料的用量作出相应的限定 (见表 4)。按图 1 的样式对应将表 1 、2 、3 、4 中的内容填入 Excel 2000 单元格内。表 1 产蛋率 <65% 蛋鸡的饲养标准代谢能 (MJ/kg) 粗蛋白 (%) 钙(%) 有效磷 (%) 赖氨酸 (%) 蛋氨酸 (%) 食盐 (%)饲养标准 11.5014.03.20.300.620.310.37表 2 各种饲料原料营养价值表饲料成分 代谢能 (MJ/kg) 粗蛋白(%) 钙(%) 有效磷(%) 赖氨酸 (%) 蛋氨酸(%) 玉米 13.47 7.80 0.02 0.

7、10 0.23 0.15麸皮6.8215.700.110.300.580.13豆粕9.6243.000.320.202.450.64菜籽粕7.4138.600.650.331.300.63进口鱼粉12.1862.503.963.055.121.66磷酸氢钙0.000.0021.0016.000.000.00石粉0.000.0035.000.000.000.00油脂36.820.000.000.000.000.00赖氨酸0.000.000.000.0078.800.00蛋氨酸0.000.000.000.000.0098.00表 3 各种饲料原料的市场价格原料玉米 麸皮 豆粕菜籽粕进口鱼粉磷酸氢钙

8、 石粉 食盐 油脂 赖氨酸蛋氨酸预混料价格( 元 /kg) 1.221.082.30 1.205.371.550.12 1.248.0017.5031.50 5.50表 4 几种饲料原料的约束条件麸皮 菜籽粕 进口鱼粉油脂 食盐 预混料下限 (%) 0 02 2 0.3 2上限 (%) 10 7 50.3 2图1求解饲料配方所用线性模型在表格中的排列样式图1中B3至M16是aij值；N3至N16是对应于初始值时，线性方程组左侧的值；03至016是bi值；B17至M17是Cj值；B18至M18是给定的初始值（初始值均给定为1）；N17是给定初始值时的目标函数值 （S），xj在表格中不占用任何单元

9、格。 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选中“工具”菜单，选中“规划

10、求解”命令，出现“规划求解参数”对话框（如图2）。22在设置目标单元格编辑框中，输入目标单元格的名称$N$17，然后点击最小值选项。2.3在可变单元格中，输入$B$18:$M$18。图2 “规划求解”对话框2.4在约束窗口中单击添加按钮，产生“添加约束”对话框 （如图3），在单元格引用位置和约束值输入栏中输入约束条件。如本例在单元格引用位置上先输入$N$3:$N$8 ;然后选择下拉窗口中的“ >=”；接着在约束值上输入$0$3:$0$8，点击添加按钮，继续添加约束条件：$N$9:$N$10<=$0$9:$N$10； $N$11>=$0$11； $N$12<=$0$12；

11、$N$13>=$0$13; $N$14:$N$15=$0$14:$0$15。输完约束条件以后关闭窗口，回到“规划求解参数”窗口。图3 “添加约束”对话框2.5点击选项按钮，进入“规划求解选项”对话框 （如图4），选中“采用线形模型”、“假 定非负”、“正切函数”、“向前差分”、“牛顿法”，然后点击确定按钮，回到“规划 求解参数”对话框。2.6单击求解按钮，开始计算，进入“规划求解结果”对话框（如图5），在该对话框中可以作以下的选择：保存规划求解的结果；恢复为原值。在本例中选择默认的选项“保存规划求解的结果”，在“报告”中根据需要选定计算结果报告（运算结果报告、敏感性报告、极限值报告）。需

12、要说明的是：线性规划的求解结果要么有最优解，要么给出参考配方（无最优解时）。所谓最优解就是满足所有约束条件（包括营养指标和配比限制条件）的最低成本配方。参考配方是指最优解不存在时，仍然存在一个最接近理想的配方，它的成本是最低，但是所有的约束条件没有同时满足，但该参考配方仍 然具有一定的参考价值，因为该结果往往是可以应用的。规划求解选项最氏运豐时间：；迭代次数©糟度老）；允许決差： 收敘度过）.17采用銭性複型如P假定菲负站计怜正勿函数魁r二次方稈（Q）导数金向前差分心厂中心差分©5牛顿法®r共駆法图4 “规划求解选项”对话框解结果”对话框267打印规划求解结果通过

13、以上步骤，就可求出一个蛋鸡料的最优配方（不一定饲养效果最佳）。可见，利用Excel的“规划求解”可以筛选出最优的配方，这样给动物营养设计师们的工作带来极大的方便，既提高了工作效率，又提高了配方质量。3. 运行结果分析3.1 运算结果报告 在此报告中，我们可以看到求得的饲料配方成本、最优配方以及约束条件满足状况，其 中“型数值”指求解值与限定值之间的差距，在无法求得最优解时，我们据此可以适当调 节约束条件。 通过线性规划求解， 从运算结果报告中可知， 本例配合饲料价格为 1.58 元 /kg ， 饲料配方见表 5。表 5 蛋鸡料饲料配方表玉米 麸皮 豆粕 菜籽粕 鱼粉 磷酸氢钙 石粉 油脂 食盐

14、 赖氨酸 蛋氨酸 预混料 配方 （%） 66.1 0.0 11.4 7.0 2.0 0.8 8.2 2.0 0.3 0.0 0.1 2.03.2 敏感性报告从该报告中我们可以看到以下内容： 递减成本，表示各个决策变量 （饲料原料的添加量 ）的影子价格，它说明在饲料总量条件 不变的情况下，某一种饲料原料用量在最优解的基础上增加 1 个单位时，目标成本增加的 量。目标式系数及其允许的增量和减量， 指在最优解保持不变的情况下， 目标式系数 （饲料原 料价格 ）的变化范围，这反映了所获得配方对原料市场价格变化的适应能力。如果原料价格 变化在允许范围内，则不必更改配方。阴影价格，指约束条件的影子价格，表

15、示在所获得最优解的基础上，当约束条件每增减1 个单位时，所引起目标函数值 （ 饲料最终价格 ） 的增减量。这可为进一步调整约束条件提供约束限制值允许的增量和减量，指在保持最优解和其它条件不变的情况下，各个约束限 制值的可变化范围，也就是指在此变化范围内表中所列出的约束条件的影子价格才能够成立。3.3 权限值报告 该报告列出了最终的饲料价格，以及保证该价格成立的饲料原料添加量的上下极限。4. 讨论4.1 在进行规划求解时，首先要确认在“工具”菜单中出现“规划求解”命令，如果没 有则需要安装“规划求解”加载宏，单击“工具”菜单中，单击加载宏命令，如果在“加 载宏”命令中，没有列出“规划求解”项，单

16、击浏览，确定驱动器、目录、文件名或运行 加载程序，然后在“加载宏”对话框中，选定“规划求解”复选框。 Excel 在“规划求解” 中 使 用 的 算 法 是 由 Leon lasdon ， (University of Texas at Austin) 和 Allan Waren(Cleveland State University) 改进的通用非线性规划最佳化代码。当“规划求解选 项”对话框中的“采用线性模型”复选框被选定时，改变可变单元格的初始值不会影响最 终数值和求解时间。在“规划求解”中可以指定 500 个约束条件，对每一个可变单元格来 说，分别有一个上、下限，还可另加 100 个附加

17、约束条件，应用约束条件的单元格不应多 于 1000 个。4.2 若规划求解过程中出现无解时，说明所使用的原料要达到约束条件是不可能的，此 时要注意分析可能存在的原因：各营养指标的约束条件是否有冲突，如有些限定了蛋白质水平低，而限定的蛋氨 酸水平又高，自相冲突；4.2.2 原料种类用量的限定与营养指标值之间有冲突。如粗纤维限定值较小，而糠饼麦 麸原料限定用量却较高；4.2.3 同一原料的上下约束范围过小，将限制营养指标值的达到；4.2.4 有时可能由于“规划求解参数”选项中的“最长运算时间”、 “迭代次数”、 “精 度”、“允许误差”等取值不当。4.3 本模板还可对单位价格单元进行约束，表明对客户提出的具体要求从经济角度进行 可行性分析。即最终求解价格有限制要求，若增加该约束时出现无解，说明超过该价饲料 厂要亏本生产。4.4 该方法计算实验动物饲料的常规营养成分具有快速、准确的优点，只要