Excel 2007 线性规划程序在设计生长肥育猪饲料配方中的应用

     摘  要：本文探讨利用 Excel 2007 规划求解功能设计最佳饲料配方的方法，实现随饲养标准、饲料原料的种类及价格的变化而变化的最佳饲料配方设计。该方法克服手工算法营养指标少、费时等局限性，全面合理平衡饲料营养、成本和经济效益的关系，最大限度降低饲料成本，非常适合在校学生和中小型饲料企业或规模化养殖场使用。

     1  材料与方法
    
     用 《瘦肉型猪饲养标准(NY/T 65-2004)》 和玉米、 小麦麸、豆粕、棉籽粕、菜籽粕、鱼粉、小苏打、石粉、磷酸氢钙、食盐 ( 加碘盐 )、植物油和 1 ％预混料等饲料原料，利用Excel 2007 规划求解功能优化设计体重 20 kg ～ 35 kg 生长肥育猪饲料配方。

     2  建立 Excel 电子表格

     查《瘦肉型猪饲养标准 (NY/T 65-2004)》，得到体重20 kg ～ 35 kg 生长肥育猪基础日粮各种营养指标，并结合生产经验作以适当修改；查《中国饲料成分及营养价值表 (2004 年第 15 版 )》，得出所选饲料原料的营养成分，把所有的数据录入 Excel 2007 表格 ( 图 1)。
         
     在A3:A14单元格分别输入所选的12种饲料原料的名称。在 A2:Q2 单元格分别输入饲料名称、配比 (％ )、最小用量(％)、 最大用量(％)、 粗蛋白(％)、 消化能(MJ/kg)、 钙(％)、总磷 (％ )、非植酸磷 (％ )、赖氨酸 (％ )、蛋氨酸 (％ )、蛋氨酸 + 胱氨酸 (％ )) 苏氨酸 (％ )、色氨酸 (％ )、钠 (％ )、氯 (％ )、价格 ( 元 /kg)。在 E3:P14 单元格区域中分别输入 12 种饲料原料的营养成分及营养价值。

     在 Q3:Q14 单元格中分别输入 12 种饲料原料的价格( 价格是当时当地的市场价，各地可根据当地实际情况随时调整 )。在 A16:P16 单元格中依次输入字符“饲养标准”、“体重 20 kg ～ 35 kg 生长肥育猪的饲养标准”。

     在 A15 单元格输入字符“合计”，E15:P15 单元格分别输入计算配方营养价值的计算值 (12 种饲料原料所含营养价值与配比乘积的和 )。例如，配方中粗蛋白的计算公式是“E15=SUMPRODUCT(B3:B14,E3:E14)/$B$15”($B$15表示绝对引用单元格 )，其他营养指标的计算以此类推。

     在 A17:P17 单元格中输入“与标准的差”，要在各相应单元格中输入“与标准的差”的计算公式。例如，粗蛋白与标准的差的计算公式是“E17=El5-El6”。在Q15中输入配方的最低成本的计算公式， 表示配方的成本， 成本计算公式是 “Q15=SUMPRODUCT(B3:B14,Q3:Q14)/B15”。

     在 B3:B14 单元格中分别输入“配比”，即所设计的生长肥育猪饲料配方。

     在 C3:C14 单元格和 D3:D14 单元格中分别输入人为设定的各种饲料原料的最小用量和最大用量，对原料的使用量进行限制。

     3  建立规划求解数学模型

     线性规划法是采用运筹学的有关数学原理进行饲料配方优化设计一种方法，该法将饲料配方中的有关因素和限制条件转化为线性数学函数，求解一定约束条件下的目标值 ( 最大值或最小值 )。利用线性规划程序设计饲料配方的首要问题是建立数学模型，使误差允许范围内满足营养需要的配方成本最低， 从而实现饲料生产成本的最大利润。

     3.1 约束条件

     利用线性规划设计饲料配方的约束条件有以下几个方面 ： ①营养需要量。 配方的营养量必须满足动物的饲养标准，允许有一定的误差许可范围，比如能量和蛋白的允许误差范围设为 0.1 ％。这需要把饲养标准和计算值分别列出来，并增加“与标准的差”这一行 ( 表中第 16 行 )，作为约束条件。②原料用量限制。由于饲料原料自身的营养特点和价格决定了各种原料在配方中的比例有一定的限制。比如棉籽粕、菜籽粕等由于含有抗营养因子，其用量一般不高于 5 ％，猪食盐的用量为 0.3 ％～ 0.5 ％。③总量限制。总量限制即各种饲料原料之和为一定值，一般是 100 ％。④多种原料配合时，所得的营养总量应为各原料配比提供的相应营养成分之和。⑤目标函数是决策变量 ( 配比 ) 的线性函数。饲料配方目标函数，一般指饲料配方成本的最小值，也可以是配方收益的最大值，最优配方为不破坏约束条件的最低成本配方或最大收益配方。

     3.2 数学模型

     设Xj(X1， X2， X3， …， Xn)为参与配方的各种原料的用量，n为原料个数， m为约束条件(即营养指标数)， Aij(i=1， 2，3，…，m；j=1，2，3，…，n) 为各种原料相应的营养价值 ( 即 n 种饲料原料的 m 个营养指标 )；Bi(B1，B2，B3，…，Bm) 为配方中相应满足的各种营养指标或重量指标的预定值；Cj(C1，C2，C3，…，Cn) 为每种原料相应的价格系数，W0 为所有饲料原料用量之和 (1，100，100 ％等 )，Z 为目标值，则：

     目标函数：Min Z = ∑ (Cn×Xn)

     约束条件：

     ∑ (A1n×Xn) ≥ B1(or ≤，=)

     ∑ (A2n×Xn) ≥ B2(or ≤，=)

     ……

     ∑ (Amn×Xn) ≥ Bm(or ≤，=)

     ∑ (Xn)= W0=100

     Xj ≥ 0

     3.3 线性规划问题的解法

     上述线性规划饲料配方计算模型由于含有多个不等式，计算时应建立标准模型：

     MinZ = ∑ (Cn×Xn)

     ∑ (A1n×Xn)=B1

     ∑ (A2n×Xn)=B2

     ……

     ∑ (Amn×Xn)=Bm

     ∑ (Xn)= W0=100

     Xj ≥ 0

     4  规划求解

     4.1 设置规划求解参数

     Excel 2007电子表格建好之后， 即可规划求解。 单击 “工具”菜单中的“规划求解”，弹出“规划求解参数”对话框 ( 图 2)。对规划求解参数进行设置，“设置目标单元格”为“目标函数”的单元格 ($Q$15)，也就是饲料配方的“最低成本”单元格；设置“等于”项为最小值，表示对目标函数求最小值；“可变单元格”也就是所选 12 种原料的配比即“$B$3:$B$14”。设置约束条件，单击“规划求解参数”对话框中的“添加”按钮，弹出“添加约束”对话框 ( 图 3)，首先输入第一个约束条件，在“单元格引用位置”鼠标单击，选择单元格 B3:B14，选择“<=”，在“约束值”鼠标单击，选择单元格 D3:D14，鼠标单击“添加”按钮，即饲料配比不能超过饲料最大用量。其它约束条件根据配方需求依次确定。第二个约束条件：$B$3:$B$14＞ =$C$3:$C$14，即饲料配比最小值；第三个约束条件：$E$15:$P$15 ＞ =$E$16:$P$16，即各营养指标须满足饲养标准；第四个约束条件：$B$15=100，即各饲料配比之和等于100。 规划求解参数全部设置完毕(图2)， 单击 “求解”按钮即可规划求解 ( 图 4)。
              
     4.2 规划求解结果

     规划求解结果可能会出现以下两种情况：①规划求解找到一解，可满足所有的约束条件及最优状况 ( 图 4)，表示规划求解成功。 选择 “保存规划求解结果” ， 单击 “确定”按钮，即可在 B3:B14 单元格看到最优解 ( 既满足营养需要和限制条件，又使成本最低的饲料配方 )。②“规划求解找不到有用的解”，系统将显示一个与最优解最接近的参考配方，有一定的参考价值。

     5  结果和讨论

     5.1 Excel 2007 规划求解使饲料配方设计优化

     用 Excel 2007 规划求解功能进行饲料配方设计是完全可行的。配方时需准确认真录人数据、计算公式、约束条件等等。Excel 2007 规划求解将大大地提高工作效率，使复杂、繁琐的数学问题简单化。

     笔者进行了多次试验，结果表明该方法可使配方成本降低 0.125 元 /kg 左右，每头猪可以节省25.5元～40.5元成本。 对万头规模的猪场来说，每年可以多赚 40 万元左右。另外，根据饲料原料价格的波动，实时调整饲料配方。例如甘肃地区的玉米，2010 年 1 月 2 000 元 /t，2010 年9 月初价格为 2 200 元 /t。根据经验，玉米的价格波动大于 200 元／ t 时，就需要对配方进行适当的调整，以降低成本。

     5.2 使用 SUM PRODUCT 函数

     使 用 SUMPRODUCT 函 数， 可 以 使 繁 琐、复杂的公式录入大大简化，并且便于增减原料。 例 如， 计 算 消 化 能 (DE) 的 公 式“F15=(B3*F3+B4*F4+B5*F5+B6*F6+B7*F7+B8*F8+B9*F9+B10*F10+B11*F11+B12*F12+B13*F13+B14*F14)/B15”， 可 以 简 化 为：“F15=SUMPRODUCT(B3:B14,F3:F14)/B15”，SUMPRODUCT 函数使复杂、繁琐的公式录入简单化。

     5.3 原料使用量的限制

     根据饲养管理和饲料配方经验，熟悉各种饲料原料的营养特点，限制饲料原料用量最小值和最大值。如猪食盐的用量一般在 0.3 ％～0.5 ％，预混料为 0.01 ％～ 5 ％，鱼粉因价格原因一般不超过 10 ％，菜籽粕、棉籽粕等因含有抗营养因子等用量不能过多。

     5.4 软件安装

     在缺省状态下，Microsoft Office 2007 没有安装“规划求解”功能，需要双击 Excel  2007图标，打开 Excel 2007。在 Excel 2007 窗口，单击“工具”菜单下“加载宏”项，打开对话框，选择 “规划求解” 复选框， 然后 “确定” 。 如果 “规划求解加载项”未在“可用加载宏”中列出，请单击“浏览”找到“加载宏”，否则需重新安装软件。

     （甘肃畜牧工程职业技术学院  张发荣、张玲清、田宗祥）

上一篇： 罗正：不同种类益生菌的特点

下一篇： 海南首笔罗非鱼养殖收入保险落地