再谈利用Excel自配饲料配方

2012-10-23李雨来

猪业科学 2012年2期

李雨来

（河北省玉田县畜牧水产局，河北唐山 064100）

阅读了贵刊在2011第10期刊登了王学敏、任守文二位老师的《利用Excel表格自配猪饲料配方》一文后，收益很大，但Excel的功能很强大，我认为有地方可以略加改动一下，便可以省去好多公式的输入，还有可以利用Excel的规划求解功能，使我们的饲料成本最小化，以便降低饲料成本，以追求更大的效益，下面谈一下自已的看法，请大家指教。

1 关于文中公式的看法

在文中第四点操作一节中，在C11单元格中输入“=(B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7+B8*C8)/100”中，我们可以利用sumpoduct函数“计算对应数组或区域乘积的和”功能来实现上述功能，以省去输入公式的麻烦，上式可改成sumproduct(B2∶ B8,C2∶ C8)/100，此外，“100”可用C11代替，这样我们可以直接计划下料单的营养成份，而不必一定为100，比如我们可为500 kg/批，就可以直接计算了。对于以后D11---J11单元格中的公式也可以用Excel中公式复制功能进行复制，而不必一个个输入，但上述公式要稍作改动，即sumproduct($B$2:$B$8,C$2:C$8)/100,在B前后加上$表示对单元格的绝对引用，在C后加一个$表示相对引用，这样在复制公式的时候前面的$B$2∶$B$8就保持不变，而后面就会对应变成C$2∶C$8、D$2∶D$8……J2∶J8，上述操作非常省事，只要将鼠标放到要复制单元格的右下角，当其变成黑色实心十字时向右拖动即可。

2 利用Excel的规划求解功能，使我们的饲料成本最小化

我们以文中的原料（将骨粉替换成石粉，因为现在很少有用骨粉）为例，我们可按以下方案设计表格：

1）在营养成份一行中我们可以输入公式＝sumproduct(B$2∶B$10,$m$2∶$m$10)/100,后面几个单元格直接复制就可以了，在单价中输入公式sumproduct(M2∶M10,J2∶J10)，其次我们可以根据营养标准确定一下我们的配方允许的营养含量的最大值与最小值分别填入最大值与最小值2行中（如图1所示）。

2）根据自已以往的经验在上限、下限2列中填定每种原料在所制作配方中允许的最大值与最小值，填写完成后如图2所示。

3）填写完成后，我们就可以利用excel进行规划求解了，首先（以excel2003为例），我们点上面工具栏下拉菜单中的规划求解项(如图3所示)，出现对话框，我们就可以填选定条件，对于上面的例子，我们选目标单元格为单价所在单元格（J11），选项选最小值，可变单元格选M2——M11，约束条件点添加如下条件：营养成份中B11—I11＜＝B12—I12，完成后点确定；再依次添加B11—I11＞＝ B13—I13；配比列中M2—M11＜＝K2—K11；M2—M11＞＝L2—L11，如图4所示。