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

利用Excel线性规划设计饲料配方线性规划, Excel, 配方, 饲料, 设计利用Excel线性规划设计饲料配方邓君明（中国海洋大学水产学院）曾广厅（云南农业大学云南省动物营养与饲料重点实验室）摘要：借助Excel 2000“线性规划求解”功能，结合饲料配方设计要求，以线性规划求解建立数学模型，根据动物的饲养标准，能快速、简单地求解饲料配方，在最低成本目标下实现对原料和营养成分的有效调控。

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

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

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

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

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

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

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

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

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

1. 饲料配方设计要求1.1优化配方设计目标动物生产的经济效益主要取决于饲料。

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

而饲料配方是配合饲料生产的核心，要优化配方设计，必须同时解决以下三个问题：⑴营养需要问题，由营养学家研究修改制定，满足营养标准就是线性规划求解的主要约束条件之一，即营养性限制；⑵合理组合原料，不同原料的合理搭配，才能满足动物的营养需要，是约束条件之二，即原料性限制；⑶价格最低，在符合条件⑴、⑵的基础上，采用成本最低的原料配比就是求解的目标，即最低成本目标函数。

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

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

线性规划最低成本配方优化的数学模型可表示为：目标函数：Min S=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中的内容填入Excel 2000单元格内。

表1 产蛋率<65%蛋鸡的饲养标准代谢能(MJ/kg) 粗蛋白(%) 钙(%) 有效磷(%) 赖氨酸(%) 蛋氨酸(%) 食盐(%)饲养标准11.50 14.0 3.2 0.30 0.620.31 0.37表2 各种饲料原料营养价值表饲料成分代谢能(MJ/kg) 粗蛋白(%) 钙(%) 有效磷(%) 赖氨酸(%) 蛋氨酸(%)玉米13.47 7.80 0.02 0.10 0.230.15麸皮 6.82 15.70 0.11 0.30 0.580.13豆粕 9.62 43.00 0.32 0.20 2.450.64菜籽粕7.41 38.60 0.65 0.33 1.300.63进口鱼粉12.18 62.50 3.96 3.05 5.12 1.6 6磷酸氢钙0.00 0.00 21.00 16.00 0.00 0.0 0石粉 0.00 0.00 35.00 0.00 0.000.00油脂36.82 0.00 0.00 0.00 0.000.00赖氨酸0.00 0.00 0.00 0.00 78.800.00蛋氨酸0.00 0.00 0.00 0.00 0.0098.00表3 各种饲料原料的市场价格原料玉米麸皮豆粕菜籽粕进口鱼粉磷酸氢钙石粉食盐油脂赖氨酸蛋氨酸预混料价格(元/kg) 1.22 1.08 2.30 1.20 5.37 1.55 0.12 1.24 8.00 17.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 “规划求解选项”对话框解结果”对话框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.12.03.2敏感性报告从该报告中我们可以看到以下内容：递减成本，表示各个决策变量(饲料原料的添加量)的影子价格，它说明在饲料总量条件不变的情况下，某一种饲料原料用量在最优解的基础上增加1个单位时，目标成本增加的量。

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

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

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

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

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

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

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

Excel在“规划求解”中使用的算法是由Leon lasdon，(University of Texas at Austin)和Allan Waren(Cleveland State University)改进的通用非线性规划最佳化代码。