奶牛饲养标准自动生成及用Excel 的“规划求解”拟制奶牛饲料配方韩友文（东北农业大学 动物科技学院 哈尔滨 150030）现今，作为信息技术载体的计算机，已普及到企业、机关、学校、研究机构各个单位。
饲料和养殖行业甩开纸笔进行日粮和饲料配方计算已成现实。
软件市场也有多种饲料配方专用软件可供选购使用。
本文要介绍的是不花一分钱买软件，用每一计算机都装有的MS-Office 中Excel 的“规划求解”-“加载宏”，完成奶牛的日粮和精料配方的拟制，并达到最低成本要求。
大家知道，奶牛作为畜牧养殖的重要草食动物，不同于精料型自由采食的猪和禽类，基本上是个体计量单喂。
奶牛日粮中的饲草和其他非精饲料组分（青贮料、根茎类、糟渣类）非常重要，且要求占日粮的一定比例范围。
奶牛日粮中的精饲料部分，通常配成精料补充料形式。
在每次挤奶同时分别喂给。
这些，无疑都给拟制最低成本奶牛日粮配方带来计算和操作上的某些复杂性。
做配方，遇到第一个问题是：确定对象奶牛的饲养标准。
查标准表采点加合计算，既烦琐，又不连续。
我们根据标准提供的参数和相关数据，转化成数学模型，再用VBA 编程。
作到了输入奶牛的必要参数，就能准确、快速、自动生成配方对象奶牛的饲养标准。
（图1）“规划求解”的数学模型与数学中的线性规划模型相同。
其基本数学表达式如下：配方解--结构变量 X i ≥ 0 (i = 1,2,……,m-1,m) 最低成本—目标函数 Z = ∑=m i CiXi 1＝→min配方要求—约束条件 ∑=mi aijXi 1 （≥, ＝, ≤） b i (j = 1,2,……,n-1,n)引申的约束条件可能有： X i ≥ X i 下 ；X i ＝ X i 等 ；X i ≤ X i 上 (饲料原料约束量) b g / b h （≥, ≤）K 下 , K 上 (例：蛋白/能量 比)DM Xi r i ∑=1/∑=mi XiDM 1 （≥ ，≤）R 下 , R 上 (例：饲草DM 占日粮DM 的比例)上述式中的符号和参数： X i 结构变量，即待求的配方解，相当于配方中个原料组分占总量的比例（% 或 g/kg ）。
m 参与配方组成的饲料原料数； n 为饲料配方所定的各种指标数。
Z 目标函数，取最小值（＝→min ），相当于日粮或配方的饲料原料成本（￥/kg ）。
C i 第i 种饲料原料的市场价格（￥/kg ）。
a ij 第i 种饲料原料的第j 种属性数据，相当于饲料原料的营养指标含量(%，g/kg ，MJ/kg ，Mcal/kg ，)。
K R 饲养标准规定的有关比例参数； r 为饲草和非精饲料原料的种类数。
下标：下，上，等，相当于下限、上限、等值；g，h，则为下标j的特定值。
有关线性规划的数学原理和计算方法，各种书刊均有详尽讲述，此处不赘。
下面仅就在Excel平台上对奶牛日粮和饲料配方的规划求解，按计算机操作及屏幕显示，分步加以介绍。
1.构建标准生成工作表1.1 在标准生成工作表左上方，根据VBA模块要求，左侧一列设置奶牛各项参数。
相邻列为参数输入域，按照配方对象奶牛条件逐一键入参数值。
右侧二列是参数单位和范围的规定提示。
图1标准生成工作表1.2 表下方有一个“确定”椭圆命令按钮，当参数键入检查无误后，鼠标左击进入VBA程序计算，生成的标准自动填入G列相应单元格中，同时也填入配方规划工作表的27行对应位置中。
1.3 生成标准列的右侧是配方规划结果反馈，列出供直观对比检查。
1.4 奶牛营养需要数学模型和VBA程序模块，这里就不一一介绍了。
2.构建配方规划工作表2.1 激活配方规划工作表，在（A）列输入饲料名，（B）列放变量名, （C）列的C4:C23为可变单元格，留作存放配方解（Xi），D列存放饲料原料的单价。
余下（E-K）各列存放饲料营养指标数据。
L列为计算粗料占日粮比例用（DM）。
2.2 工作表的第2行输入对应各列的题头和营养指标英文缩写名，分别是：Price = 单价，DM = 干物质，NE L = 产奶净能，CP = 粗蛋白，Ca = 钙，P = 磷，NaCl = 食盐，Premix = 预混料。
第3行放其对应的单位。
为了后续工作方便和个人习惯，单位可以变换：如％换成g/kg，同时数据小数点相应移位。
第4行起到第23行，（D）列输入各饲料原料的单价；L列用于存放日粮粗/精比例；（F-K）列则对应为各营养指标含量。
图2配方规划工作表2.3 D26即目标单元格，备放日粮的最低成本，由（C）列配方解与（D）列对应单元格的饲料原料价格相乘，累加，并进行单位换算而得, D26 =SUMPRODUCT($C$4:$C$23,D4:D23)。
2.4 F26-L26 各单元格存放日粮的营养指标值(b i)，可选定 D26用鼠标＋符左键拖过各单元格方式拷贝公式，再分别因饲料与日粮的同名指标单位不同而校正换算生成。
例如：F26 = SUMPRODUCT($C$4:$C$23,F4:F23)/100; G26 = SUMPRODUCT($C$4:$C$23,G4:G23)*10。
余类推。
2.5 第27行存放配方对象奶牛的饲养标准，其格式与饲料营养指标格式对应一致。
2.6 第29-31行设置针对各项营养指标的约束值，约束条件有三种：大于等于（>=）、等于（=）、小于等于（<=），各行各项指标的约束下限、约束等值、约束上限，分别按对象动物对营养指标的要求来确定，供约束操作时直观选定。
2.7 （M），（N）,（O）,各列与第29-31各行对应类似，设置日粮配方解（X i）的约束条件限量值。
2.8 （L）列存放饲草DM占全日粮DM比例的有关计算结果。
饲草DM单元格L8 =SUMPRODUCT($C$4:$C$10,D4:D10)/100；日粮DM单元格F20 =SUMPRODUCT($C$4:$C$23,D4:D23)/100；饲草DM/日粮DM单元格E26 = L8/L20*100。
图3饲料配方的原料成本分析2.9V、W、Q列用于按“归一”方法延伸计算精料补充料配方。
第R、S、X、Y，列对日粮配方作出原料成本分摊情况分析。
（图3）3. 规划求解的具体操作过程：3.1调用加栽宏的规划求解：鼠标点选工具菜单中的加栽宏，在对话框中选规划求解，确定（图 4）。
已调用过规划求解时，可直接由工具菜单点选规划求解。
图 4 工具菜单,加载宏对话框和规划求解选定3.2 工具菜单中激活规划求解：弹出规划求解参数对话框（图 5-1），鼠标单击 D26 单元格，选定目标单元格，显示为加 $ 的单元格的绝对地址 $D$26 ；选定等于行内的最小值；鼠标单击选项按钮，弹出规划求解选项对话框（图 5-2），只选定假定非负一项，其余为默认，确定返回规划求解参数对话框。
图 5 规划求解参数及规划求解选项对话框3.3 在可变单元格框内，用鼠标左键拉过C4-C23填入,确定结构变量的输出单元格，即配方结果的输出位置，显示出 $C$4:$C$23 。
3.4 鼠标单击约束栏的添加按钮，弹出添加约束对话框（图 6），各项营养指标和各种饲料原料的限量约束，因营养要求和原料性质以及库存数量而定，可按预定的约束条件用鼠标点击设定。
本例的约束设置有： C11:C14 >= M11:M14 ; C19 >= M19 ; C4:C10 >= M4:M10 ; E26 <= E31 ;E26:J26 >= E27:J27 ; L26 <= L31图 6 添加约束对话框图 7 规划求解结果对话框3.5约束设定完毕，返回规划求解选项对话框，再用鼠标左键单击求解，则出现对话框（图 7），显示规划求解找到一解，可满足所有的约束及最优状况，（图 7）单击确定，配方结果即可显示在配方工作表上，同时也反馈到标准生成工作表中。
如鼠标选取对话框中的三份报告，则可同时生成运算结果、敏感性、和极限值三份报告。
如果显示规划求解找不到有用的解，则应单击取消，重新检查并修改约束条件的设定。
图8 配方的原料分摊成本分析3.6 在日粮配方基础上可进一步延伸计算饲料成本的分摊情况（图 8），供经营管理人员分析参考。
3.7如要求配方以书面文字存档时，则每完成一个配方，要将结果拷贝到另一打印输出工作表内，以便打印成文存档。
拷贝时具体注意是：配方解各单元格有的存放公式，应在编辑或右键菜单中选中选择性粘贴再选定数值。
而配方营养指标值单元格（D26:L26）存放的全是公式，又是横向排列，所以选择性粘贴时还要加选转置（图 9）。
图9 选择性粘贴对话框图10 配方的延伸计算：浓缩料和预混料3.7 同时拟制精料补充料配方，则可以很方便地按“归一”的方式计算出配方来。
同理，可推及除去玉米组分的浓缩料和预混料配方的拟制（图 10）。
图 11备用饲料数据库及备用饲养标准库示例4. 几点注意4.1可在本工作表下方建立“饲料数据备用库”和“饲养标准备用库”，便于调换饲料原料和饲养标准，进行另外的配方拟制工作（图 11）；4.2奶牛饲养标准不必再查表计算，可由自带的按数学模型编就的VBA模块自动计算产生。
4.3一般数据取小数点后2位，即够用，多取则会增大列宽，一屏显示受限，不便观察和操作。
4.4计算和拷贝过程，引用绝对地址单元格时，行、列前都要加 $ ，可选中后按F4 键切换选取。
4.5是日粮配方解的自然重量总和，不必设置约束。
4.6参考文献李小迪（2001）Excel 2002学用 DIY 北京科学出版社Sharon Podlin(2000)中文Excel 2000 编程24学时教程潇湘工作室译北京机械工业出版社韩友文（1999）巧用MS-Excel软件计算最低成本饲料配方饲料博览No2 p16-20韩友文许丽（2002）奶牛营养需要量自动生成的BASIC程序第四届全国饲料营养学术研讨会论文集p60-61。